InnoDB(5.2) Cardinality值

什么是Cardinality值

官方解释

首先，要知道的一点是，并不是在所有的查询条件中出现的列都需要添加索引值，对于什么时候添加B+树索引，一般的经验是，在访问表中很少一部分时的数据使用B+树索引才会有意义（也就是说，索引的列要有很高的筛选性，筛选出绝大部分的数据才是有效的，要有高选择性）

举个栗子

对于性别字段、地区字段、类型字段等可取值的范围很小，筛选出来的数据占明显一部分的总数据，那么这些就被称为低选择性。

按性别去进行查询时，可取值的范围就是男或者女，因此查询出的结果很有可能占该表的50%的数据（假设男女比例为1：1），这时给性别列添加B+树索引是完全没有必要的（因为低选择性，会导致走更多的页去找数据，倘若每张页都有几条匹配的数据，那么为什么不用全表扫描呢？这样甚至减少了IO次数，不用读取索引页），而且MySQL在较低选择性时也是默认不会走索引的。

那么如何去判断索引是否是高选择性呢？可以通过SHOW INDEX结果中的Cardinary列来观察，这个Cardinary值是非常关键的，它表示的是索引中不重复记录数量的预估值，这里要注意的是，这只是一个预估值，而不是一个准确值，实际上，获取准确值也是很难实现的。在实际应用中，Cardinary/n_rows_intable应尽可能地区接近1（用不重复记录数量除以表的行数，如果结果为1，不就代表着每一行的数据都是不重复的咯），如果非常小，就代表Cardinary小，也就是不重复记录数量比较少，重复数据多，就要考虑要不要去建索引了

通过下面的SQL语句可以查询Cardinality值

SHOW INDEX FROM 表;

InnoDB存储引擎对于Cardinality统计

MySQL中有不同的存储引擎，而每种引擎对于B+树索引的实现是不同的，对Cardinary的统计是放在存储引擎层去进行的，也就是不同存储引擎的Cardinary的计算是不同的

Cardinary的统计是通过采样（Sample）的方法来完成的，因为在生产环境中，索引的更新操作比较频繁，如果每次索引在发生操作时就对其进行Cardinary统计，会给数据库带来很大的负担**（频繁统计会加大数据库压力）。而且假如数据量很大，统计一次Cardinary的信息所需要的时间可能非常长，这是不能接受的（统计时间长）**

首先，在InnoDB存储引擎中，Cardinary统计信息的自动更新（即自动重新计算）是发生在DML语句中的（也就是Insert、Update、Delete都可能会触发，这是要根据限制条件的）

下面我们通过参数去看Cardinality是怎么变化的

show variables like "innodb_stats%";

InnoDB对于Cardinality信息的更新是有两种策略的，分别为持久化与非持久化

当innodb_stats_persistent为ON时，表示将统计信息持久化，也就是是开启持久化，为OFF时，就代表使用废旧话策略

持久化策略

持久化策略是将优化器统计信息保存在硬盘中的（数据保存在mysql.innodb_index_stats和mysql.innodb_table_stats表中,在information_schema.statistics和information_schema.tables也有记录），MySQL服务器重启的时候就会加载信息进来，同时使Cardinality在加载时不发生变化来提高计划的稳定性，让优化器更有可能为给定的一致查询做出一致的判断

总的来说，好处就是，当重启MySQL服务器时，可以减少重新计算每个索引的Cardinality值

对于使用了持久化进行更新Cardinality信息的话，就涉及到另一个参数innodb_stats_auto_recalc，这个参数定义了统计信息自动更新的规则，如下

• 当表上的记录修改超过10%时，会对统计信息重新计算
• 两次统计收集信息的时间间隔要超过10S
• 收集信息的动作是异步的，在执行完比较大的DML操作后，可能会过一段时间才要重新收集统计信息(因为有统计收集时间延迟)，如果想要及时信息的话，使用analyze命令去收集

非持久化策略

非持久化策略是原先低版本的MySQL就有的。

非持久化策略是将数据保存在information_schema.statistics和information_schema.tables中

在非持久化策略，更新Cardinality信息的策略为

• 表中十六分之一的数据发生过变化
• stat_modified_counter > 2000000000（200亿）时，发生变化

第一种情况考虑的是，自上次统计Cardinality信息之后，如果有十六分之一的数据已经发生过变化，这时就需要更新Cardinality值，第二种情况针对的是，假如就对几行数据进行修改，在十六分之一以内的数据不断进行修改，这样不就永远无法触及到第一种情况，所以要加一个stat_modified_counter计数器，这个计数器表示发生变化的次数，当超过一定值时，也要更新信息

采样计算

前面提到过，计算Cardinality的值是使用采样计算的，因为当数据量很大的时候，如果全部计算会很慢很慢

持久化的采样计算

采样的页数对应的参数是innodb_stats_persistent_sample_pages（默认为20），采样的过程如下

• 取得B+树索引中叶子节点的数量，记为A
• 随机取得B+树索引中的节点（数量由采样参数决定）
• 统计每个页不同记录的个数，记录为p1、p2、p3。。。p20
• 根据采样信息给出Cardinality的预估值（p1+…+p20）* A/8

非持久化的采样计算

计算过程与持久化的采样计算过程一样，只不过采样的页数不一样

对应的是innodb_stats_transient_sample_pages（代替了innodb_stats_sample_pages）

其他参数

• innodb_stats_on_metadata：是指在通过命令SHOW TABLE、SHOW INDEX这些访问information_schema架构下的表TABLES和STATISTICS时（非持久化策略与持久化策略在这两个表都有保存数据记录），是否需要重新计算索引的Cardinality值
• innodb_stats_include_delete_marked：当未提交事务中存在删除操作时，会把对记录进行标记，这个参数表示计算Cardinality值时，是否要将标记删除的这些数据也算上去
• innodb_stats_method：这个参数是表示如何对待索引中的NULL值，默认为nulls_equal，表示null是一样的（回看MySQL,null并不一定等于null），还有另外的两个参数为nulls_unequal（null不等于null），和nulls_ignnored（对于null值就忽略，计算Cardinality不进行统计）