索引选择性与cardinality

时间：2021-11-04 10:03:16

索引选择性

索引选择性是索引基数（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，也会更新统计值。

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

  1. 获取B+树叶子节点的数据，记为A
  2. 随机获得B+树索引中8个叶子节点。统计每个页不同记录的个数，分别记为P1,P2...P8
  3. 计算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

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

总结

  1. cardinality代表的是此列中存储的唯一值的个数，如果此列为primary key 则值为记录的行数，如果是复合索引就是唯一组合的个数。
  2. cardinality只是个估计值，并不准确。
  3. cardinality将会作为mysql优化器对语句执行计划进行判定时依据。如果唯一性太小，那么优化器会认为，这个索引对语句没有太大帮助，而不使用索引。
  4. cardinality值越大，就意味着，使用索引能排除越多的数据，执行也更为高效。
  5. cardinality不会自动更新，需要通过analyze table来进行更新。
  6. cardinality的大小影响join时是否选用这个index的判断。
  7. 初建index时，MyISAM的表cardinality的值为null，InnoDB的表cardinality的值大概为行数。
  8. MyISAM与InnoDB对于cardinality的计算方式不同
标签：

相关文章