索引选择性与cardinality

时间:2021-11-04 10:03:16

索引选择性

索引选择性是索引基数(cardinality)与表中数据行数(n_row_in_table)的比值,即

索引选择性=索引基数/数据行

其中cardinality是索引中不重复记录的预估值。

不是所有的查询条件出现的列都需要添加索引。对于什么时候添加B+树索引。一般的经验是,在访问表中很少一部分时使用B+树索引才有意义。对于性别字段、地区字段、类型字段,他们可取值范围很小,称为低选择性。如

SELECT * FROM student WHERE sex='M'

按性别进行查询时,可取值一般只有M、F。因此SQL语句得到的结果可能是该表50%的数据(加入男女比例1:1)这时添加B+树索引是完全没有必要的。相反,如果某个字段的取值范围很广,几乎没有重复,属于高选择性。则此时使用B+树的索引是最合适的。例如对于用户名字段,基本上在一个应用中不允许重名的出现。

通过SHOW INDEX结果可以看到列Cardinality。Cardinality非常关键,表示索引中不重复记录的预估值。需要注意的是Cardinality是一个预估值,而不是一个准确值。基本上用户也不可能得到一个准确的值。在实际应用中,索引选择性应尽可能的接近1,如果非常小,那用户需要考虑是否还有必要创建这个索引。故在访问高选择性属性的字段并从表中取出很少一部分数据时,对于字段添加B+树索引是非常有必要的。

cardinality是怎么预估的?


上面提到cardinality是索引中不重复记录的预估值,那么它是怎么实现的呢?由于Mysql的B+索引在每个存储引擎中实现的都不一样,所以cardinality干脆放到存储引擎层面实现的!

对于innodb来说,达到以下2点就会重新计算cardinality

  • 如果表中1/16的数据发生变化
  • 如果stat_modified_counter>200 000 0000

这是为什么呢?因为真实环境中,索引的更新可能非常频繁,比如一个表中数据的插入,更新,删除等,每次都去统计cardinality会带来很大的负担;另外如果是一个大表,统计一次可能非常耗时。基于此,采用基于上面2个条件的"抽样"统计的方式。

那上面2种有什么区别呢?

  • 如果表中1/16数据发生变化则会更新;
  • 第2种情况比较特别,如果某一千数据频繁更新,但是数据并没有增加,则第一种无法适用,所以设置stat_modified_counter为发生变化的次数;如果次数达到200 000 0000,也会更新统计值。

那具体是如何采样统计的呢?

  1. 获取B+树叶子节点的数据,记为A
  2. 随机获得B+树索引中8个叶子节点。统计每个页不同记录的个数,分别记为P1,P2...P8
  3. 计算cardinality = (P1+P2+...P8)A/8从而得出索引中不同记录的数量。

从上面可以发现,有2个问题

  • 由于是随机采样的方式,所以会出现,连续2次统计,数量都不同。只有在表数据非常少,叶子节点不多于8个时,每次采样都是取到相同的页,统计值才会相同。
  • 由于统计值是基于上面2个条件去更新的,可能出现系统运行了一段时间之后,数据发生了很大变化,统计值偏差比较大了,那么索引的效率会下降。

那对于问题2,该怎么处理呢?

手动更新统计值

如果系统运行一段时间之后,我们可以通过执行下面的sql,重新计算cardinality值

analyze table tb_name;

show table status;

show index from tb_name

不过,如果表很大,重新统计可能会非常耗时间,建议对于核心表,在非高峰时段操作。

总结

  1. cardinality代表的是此列中存储的唯一值的个数,如果此列为primary key 则值为记录的行数,如果是复合索引就是唯一组合的个数。
  2. cardinality只是个估计值,并不准确。
  3. cardinality将会作为mysql优化器对语句执行计划进行判定时依据。如果唯一性太小,那么优化器会认为,这个索引对语句没有太大帮助,而不使用索引。
  4. cardinality值越大,就意味着,使用索引能排除越多的数据,执行也更为高效。
  5. cardinality不会自动更新,需要通过analyze table来进行更新。
  6. cardinality的大小影响join时是否选用这个index的判断。
  7. 初建index时,MyISAM的表cardinality的值为null,InnoDB的表cardinality的值大概为行数。
  8. MyISAM与InnoDB对于cardinality的计算方式不同