10 | MySQL为什么有时候会选错索引?
优化器的逻辑
- 选择索引是优化器的工作。
- 优化器选择索引的目的:是找到一个最优的执行方案,并用最小的代价去执行语句。
- 影响执行代价的因素:扫描行数, 是否使用临时表、是否排序等因素进行综合判断。
扫描行数是怎么判断的?
MySQL在真正开始执行语句之前,不能精确地知道满足这个条件的记录有多少条,只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,我们称之为“基数”(cardinality)。也就是说,这个基数越大,索引的区分度越好。
- 使用
show index
方法,看到一个索引的基数。
MySQL是怎样得到索引的基数的呢?
MySQL采样统计。 为什么要采样统计呢?因为把整张表取出来一行行统计,虽然可以得到精确的结果,但是代价太 高了,所以只能选择“采样统计”。
-
采样统计的时候,InnoDB默认会选择N个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。
-
而数据表是会持续更新的,索引统计信息也会更新。当变更的数据行数超过1/M的时候,会自动触发重新做一次索引统计。
在MySQL中,有两种存储索引统计的方式,通过设置参数innodb_stats_persistent
的值来选 择:
- 设置为on的时候,表示统计信息会持久化存储。这时,默认的N是20,M是10。
- 设置为off的时候,表示统计信息只存储在内存中。这时,默认的N是8,M是16。
索引统计值(cardinality列)虽然不够精确,但大体上还是差不多的,选错索引一定还有别的原因。
-
其实索引统计只是一个输入,对于一个具体的语句来说,优化器还要判断,执行这个语句本身要 扫描多少行。 会有偏差误导了优化器的判断。( 使用普通索引需要把回表的代价算进去 )
-
修正统计信息:
analyze table t
命令,可以用来重新统计索引信息。
- 你发现
explain
的结果预估的rows值跟实际情况差距比较大,可以采用这个方法来处理。- 如果只是索引统计不准确,通过
analyze
命令可以解决很多问题 。
索引选择异常和处理
-
采用
force index
强行选择一个索引。 -
修改语句,引导MySQL使用我们期望的索引。
-
新建一个更合适的索引, 或删掉误用的索引。
小结
-
索引统计的更新机制
-
优化器存在选错索引的可能性
-
索引统计信息不准确导致的问题,可以用
analyze table
来解决 -
优化器误判的情况:
-
force index来强行指定索引
-
修改语句来引导优化器
-
增加或者删除索引来绕过这个问题