MYSQL45讲学习笔记

10.MYSQL为什么有时候会选错索引

前言：如果您阅读本文之后，有觉得不妥当的地方，请您留下宝贵的意见。

前面我们学习过，优化器的工作是选择合适的索引，使执行器可以以最小的代价执行SQL语句。但是有时候MYSQL因为种种原因，也会选错索引。其实就是MYSQL的bug，不同的版本有不同的处理方式。

1.MYSQL索引的基数-cardinality

MYSQL通过采样的方式来获取索引的区分度，也叫做索引的基数。如果一列的值域范围很广，那么我们称这一列的索引区分度很高，如果一列的值域范围很窄，（比如：性别字段，值域只有：男、女、未知），那么我们称这一列的索引区分度很高。

索引的区分度可以用来判断在此列建索引是否合理，区分度越高的列，越适合建索引。

索引的区分度并不是很准确（也就是cardinality的值），因为MYSQL采用采样统计的方式来确定cardinality的值。如果走全表扫描，那么cardinality的值必然精确很多，但是那样代价太高。

cardinality的值是这样计算的：InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

而数据表是持续更新的，索引的基数也会随之变化。当变更的行树超过1/M的时候，会重新统计索引信息。

在MYSQL中，有两种存储索引基数的方式，可以设置参数innodb_stats_persistend。

当值为ON时，表示索引基数持久化存储，此时N为20，M为10；

当值为OFF时，表示存储在内存，此时N为8，M为16。

猜测：此处参数说明：当使用持久化存储时，索引基数相对会更准确，更新也会更频繁，

当使用内存存储时，因为要尽量小的消耗内存，索引基数相对来讲会更粗糙，更新次数较少。

2.MYSQL为何会选错索引

MYSQL选错索引的原因有很多，可能是MYSQL索引信息统计错误，也可能是MYSQL考虑到回表操作的性能损失，也有可能是因为排序原因。

比如：select * from where (a between 1000 and 2000) and (b between 10000 and 50000) order by b limit 1; 因为要根据b排序，所以MYSQL可能会选择索引b。

3.如何解决MYSQL选错索引

1.如果是MYSQL索引信息统计错误，则可以使用 analyze table table_name 命令来解决。

2.可以使用 force index(index_name) 来强制使用某索引。

例如： select * from  t force idnex(a) ; t为表名，a为索引名

3.可以选择新建合适的索引，或者将错误的不重要的索引删除。

4.可以修改SQL语句，诱导MYSQL使用正确的索引。

小结：

本节讲述了MYSQL选错索引的部分原因，以及解决MYSQL选错索引的方法，同时讲解了MYSQL索引统计信息的原理，也就是cardinality的值是如何计算得到的。