预估数据表中索引中唯一值数据。此值与实际数据条数越接近,走索引的几率就越高。如果需要更新此值则需要执行ANALYZE TABLE 或适用于MyISAM引擎的myisamchk -a。
为什么是预估数据表中索引中的唯一值数据?
因为在生产环境中,索引更新操作是非常频繁的,如果每次索引发生操作时,就进行Cardinality的统计,会给数据带来较大的压力。如果一张表的数据比较大,如50G,那么统计一次Cardinality信息则会耗时比较长,这在生产环境是不能接受的。因此在数据库对于Cardinality统计通过采样方法进行的。
那些操作会触发Cardinality统计?
Cardinality统计更新发生在两个操作中:insert、update。根据前面介绍Cardinality统计不是每次都进行的,因此InnoDB引擎对于更新Cardinality的策略为:
- 表中的1/16数据已发生变化
- start_modified_counter>2 000 000 000
第二种策略为对表中某一行数据频繁进行更新操作,实际数据条数并没有发生变化,则InnoDB会生成start_modified_counter计数器,用来统计操作次数,如果次数大于2 000 000 000,则会触发Cardinality统计。
InnoDB引擎内部是怎样进行Cardinality统计和更新的?
同样采用采样的方法,默认情况下InnoDB会对8个叶子节点的信息进行统计,过程如下:
- 取得B+Tree所有叶子节点的数量,记为A。
- 随机取得B+Tree索引的8个叶子节点。统计每个叶子节点的不同记录的条数,即为P1,P2,...,P8。
- 根据采样计算出Cardinality的预估值:Cardinality=(P1+P2+...+P8)*A/8。
上述这个语句在InnoDB1.2之前是可以看到Cardinality值变化的,如下图:
然后再次执行show index Cardinality的值发生变化,这会并没有对这张表进行insert/update等操作,如下图:
如果你的数据量比较少,内部子节点就8个以内,那每次查看的Cardinality的值是没变化的。
上文主要介绍show index会触发Cardinality的值统计,其ANALYZE TABLE、SHOW TABLE STATUS以及访问INFOMATION_SCHEMA架构下的TABLES或STATISTICS也会导致Cardinality的统计,若表中数据比较大,执行上述操作会比较耗时,也会对数据库造成一定的压力,因此在InnoDB1.2中对SHOW TABLE STATUS、INFOMATION_SCHEMA访问时,默认是不统计Cardinality信息,如果要开启需要设置以下部分参数:
如果要更新采样页的数量,需要修改一下参数:
参考文献
[1] 姜承尧(David Jiang)著,MySQL技术内幕:InnoDB存储引擎(第2版);机械工业出版社,2013
[2] Baron Scbwartz等 著,王小东等 译;高性能MySQL(High Performance MySQL);电子工业出版社,2010