MySQL中Cardinality问题总结
1. 怎么查看索引是否具有高可用性?
答:使用以下语句,查看结果的Cardinality一栏,如果显示的是100,那就说明该字段存在100个不重复的项,这时根据实际情况决定是否保留该索引。但是,Cardinality的结果却并不是非常精确的,只能当其是一个预估值,在索引的操作很频繁时(insert和update时),统计Cardinality的值是耗时的,因此该统计是利用采样的方式进行的。
SHOW INDEX FROM TABLE_NAME;
2. InnoDB存储引擎内部对Cardinality信息更新发生在什么时候?
答:存在以下两种策略:
第一种:(计数变化)自从上次统计Cardinality值后,表中的1/16
的数据已经发生过变化,这是需要更新Cardinality信息;
第二种:(内容变化,计数不变)如果对表中某一行数据频繁地进行更新操作,这时表中的数据实际并没有增加,实际发生变化的还是这一行数据,则第一种更新策略就无法适用这种情况,因此在InnoDB存储引擎内部有一个计数器start_modified_counter
,用来表示发生变化的次数,当start_modified_counter>2 000 000 000
时,则同样更新Cardinality信息。
3. InnoDB存储引擎内部对于Cardinality信息的统计和更新操作是什么样的?
答:先获取叶子节点的数量,记为N,再随机选择8个叶子节点,每个叶子节点统计不同记录的个数C1、C2、C3、…,通过计算获取预估值,Cardinality=((C1 + C2 + … + C8) / 8) * N。
思考一下,既然是使用随机采样的方式进行统计,那么就肯定无法保证每次获取的Cardinality值都保持相同,除非是叶子节点小于等于8,那么来9验证一下,以下分别是两次查询的结果:
4. 其他注意事项
a. 问题4中,提到的随机选择8个叶子节点,在InnoDB1.2版本之前,是可以通过参数 innodb_stats_sample_pages进行手动设置的;
b. 对于数据库中出现的null值,可以通过innodb_stats_method=nulls_equal
将所有的null值当作相等的记录,通过innodb_stats_method=nulls_unequal
将所有的null值当作不同的记录,通过innodb_stats_method=nulls_ignored
将所有的null值设置为忽略null值记录;
c. 当执行sql语句ANALYZE TABLE、 SHOW TABLE STATUS、 SHOW INDEX和访问INFORMATION_SCHEMA架构下的表TABLES和 STATISTICS时会导致InnoDB存储引擎去重新计算索引的 Cardinality值,如果数据量大,且索引较多,那可能要等需要耐心等候一下了。