InnoDB存储引擎的Cardinality统计

  内容主要来自《MySQL技术内幕》

  

   索引

   
   索引能帮助我们快速得到符合查询条件的数据，但是什么时候添加索引最合适呢？
   
   对于什么B+树索引来说，一般的经验是，在访问表中很少一部分的时候使用B+树索引才有意义。对于性别字段、地区字段等，这种字段的取值范围就很小，我们称为低选择性，给这种字段添加索引是完全没有必要的。相反如果某个字段的取值范围很广，几乎没有重复，即属于高选择性，则此时使用B+树索引是最合适的，例如学号、身份证号等。索引列中的数据都是高选择性的，这对数据库来说才有实际意义。
   

   Cardinality的作用

   
   Cardinality是一个非常关键的值，它表示索引中唯一值的数目的估值，类似redis中的特殊数据类型Hyperloglog的机制，Cardinality除以表的行数应该尽可能接近1，如果值非常小，那说明这个索引的选择性很低，此时因重新考虑该索引的存在是否合理。
   
   通俗点来讲这个值就是统计索引列中有多少条不同的数据，举个例子，你索引列有100条数据，Cardinality值也为100，此时两数相除得1，那说明这个字段建立的索引的可用性非常高。
   
   为什么说这个值重要呢？因为优化器会根据这个值来判断是否使用该索引。但是这个值并不是实时更新的，即使更新索引这个值也不一定会被更新，因为这样开销太大了，所以这个值的准确度不会非常高（在生产环境中，索引的更新操作可能是非常频繁的，如果每次索引更新就对其进行Cardinality的统计，那么将会给数据库带来很大的负担）。如果需要更新这个值，可以使用ANALYZE TABLE命令。
   
   另外，我们还需要考虑，如果一张表的数据非常大，那么统计一次Cardinality信息的时间可能非常长。这在真实生产环境下，也是不能接受的。因此数据库对Cardinality的统计都是通过随机采样的方法来完成的。
   
   在InnoDB存储引擎中，Cardinality统计信息的更新一般发生在INSERT（插入）和UPDATE（修改）中。上面也说了，这个值不可能频繁的更新，所以，InnoDB存储引擎对更新Cardinality信息的策略为：
   
   1.表中1/16的数据已发生过变化。
   
   2.stat_modified_counter>2 000 000 000（stat_modified_counter是个计数器，用来表示单行数据发生改变的次数，表中数据实际没有增加，只是对单行的数据进行了修改）
   

   Cardinality的采样机制

   
   上面也说了，Cardinality的采样机制是随机的，具体过程就是，先取得B+树索引的叶子节点的数量，记为A，然后随机取得B+树索引中8个叶子点，统计每个页不同记录的个数，记为P1，P2…P8，最后算出Cardinality的预估值：Cardinality=（P1，P2…P8）*A/8。
   
   通过上面的Cardinality的采样机制，也可以得出一个结论，每次得到的Cardinality值可能是不一样的。当然，只要表足够小，表的叶子节点小于等于8个。这时，即使是随机采样，每次的Cardinality值也是相同的。
   

   innodb_satas_sample_pages和innodb_stats_method控制参数

   
   innodb_satas_sample_pages参数是用来控制Cardinality每次更新时的采样数量的，默认为8。
   
   innodb_stats_method参数是用来判断索引中出来null值的策略，该参数一共有三个值可选，对应三种不同的策略：

   
      nulls_equal（默认）：表示将null视为相等的记录。
      nulls_unequal：表示将null值视为不同的记录。
      nulls_ignored：忽略null值记录。

   举个例子

一个页中的索引记录为null、null、1、2、2、3、3、3，默认设置下（innodb_stats_method参数的值为nulls_equal），将所有的null值视为相等的记录，那么该页的Cardinality为4；如果innodb_stats_method的参数为nulls_unequal（将null值视为不同的记录），则该页的Cardinality为5；如果innodb_stats_method的参数为nulls_ignored，即忽略所有的控制，，则该页的Cardinality为3。
   

  先就写这么多了。

  以上内容主要来自《MySQL技术内幕》和我的一些浅显的理解。

  这本书很nice（不是广告），感兴趣的小伙伴可以去淘宝的新华书店购买。

  我发言完毕 bye~