索引选择性是索引基数 (cardinality) 与表中数据行数 (n_row_in_table) 的比值, 即
索引选择性 = 索引基数 / 数据行
其中 cardinality 是索引中不重复记录的预估值.
不是所有的查询条件出现的列都需要添加索引. 对于什么时候添加 B + 树索引. 一般的经验是, 在访问表中很少一部分时使用 B + 树索引才有意义. 对于性别字段, 地区字段, 类型字段, 他们可取值范围很小, 称为低选择性. 如
SELECT * FROM student WHERE sex='M'
按性别进行查询时, 可取值一般只有 M,F. 因此 SQL 语句得到的结果可能是该表 50% 的数据 (加入男女比例 1:1) 这时添加 B + 树索引是完全没有必要的. 相反, 如果某个字段的取值范围很广, 几乎没有重复, 属于高选择性. 则此时使用 B + 树的索引是最合适的. 例如对于用户名字段, 基本上在一个应用中不允许重名的出现.
通过 SHOW INDEX 结果可以看到列 Cardinality.Cardinality 非常关键, 表示索引中不重复记录的预估值. 需要注意的是 Cardinality 是一个预估值, 而不是一个准确值. 基本上用户也不可能得到一个准确的值. 在实际应用中, 索引选择性应尽可能的接近 1, 如果非常小, 那用户需要考虑是否还有必要创建这个索引. 故在访问高选择性属性的字段并从表中取出很少一部分数据时, 对于字段添加 B + 树索引是非常有必要的.
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 代表的是此列中存储的唯一值的个数, 如果此列为 primary key 则值为记录的行数, 如果是复合索引就是唯一组合的个数.
cardinality 只是个估计值, 并不准确.
cardinality 将会作为 MySQL 优化器对语句执行计划进行判定时依据. 如果唯一性太小, 那么优化器会认为, 这个索引对语句没有太大帮助, 而不使用索引.
cardinality 值越大, 就意味着, 使用索引能排除越多的数据, 执行也更为高效.
cardinality 不会自动更新, 需要通过 analyze table 来进行更新.
cardinality 的大小影响 join 时是否选用这个 index 的判断.
初建 index 时, MyISAM 的表 cardinality 的值为 null,InnoDB 的表 cardinality 的值大概为行数.
MyISAM 与 InnoDB 对于 cardinality 的计算方式不同
来源: http://www.bubuko.com/infodetail-2945788.html