mysql原理和优化使用 > mysql索引详解以及如何优化
索引选择原理-​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,也会更新统计值。


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


获取B+树叶子节点的数据,记为A

随机获得B+树索引中8个叶子节点。统计每个页不同记录的个数,分别记为P1,P2...P8

计算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

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


选择性

现在又回到前面的例子,我们通过观察执行计划发现,不论cardinality大小,相对值大小,发现还是会走索引,那为什么要说对于相对值非常小的不建议建索引呢?这就涉及到一个选择性的问题


比如有一个用户表,有一列性别sex,现在要查询所以性别为male的用户(假定只有男人-male,女人-female,没有其它不明性别),可能的sql:


select * from user where sex = 'M';

对于这个sql,虽然sex上有索引,但是执行的时候,读取的数据可能会超过一半,甚至在极端情况下(比如程序员的网站),大部分数据都需要读取,所以还是会走全表扫描,这种数据称为低选择性。反之,如果是高选择性的,建议建索引 ,比如user表中用户,一般来说很少重复;

————————————————


原文链接:https://blog.csdn.net/weigeshikebi/article/details/80214965