MySQL 会选错索引吗?
我们在分析慢查询语句时,可能遇到过 MySQL 选错了索引, 似乎表现的不够智能。
简单来说,就是在一张有多个索引的表上执行一条查询语句,明明应该走a索引的,但在实际执行过程中偏偏走了b索引,那是什么原因导致 MySQL 选错了索引?
背景知识
索引:相当于目录,能够加快查询效率
优化器:MySQL架构中的重要组成,作用是从不同执行方案(计划)挑选出其认为最优的来执行
执行计划:根据SQL语句将要达成的目标,制定出来相应的执行过程
优化器的选择
从上述问题及背景知识中不难推断出,优化器选择错了执行计划,导致执行过程的额外消耗。
那么,影响优化器的方案选择的因素是什么?
扫描行数、是否排序、是否使用临时表等。先着重分析一下扫描行数。
扫描行数
扫描行数代表了执行过程需要访问的磁盘次数,当然是越少越好。
优化器是怎么判断(预估)扫描行数的?
- 根据统计信息来估算记录数
- 这个统计信息就是索引的区分度。一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,我们称之为“基数”(
cardinality
)。也就是说,这个基数越大,索引的区分度越好。
MySQL 怎样得到这个基数的呢?
- 采样统计,显然这个值也只是估算值,并不精确。
- InnoDB 默认会选择 N 个数据页,统计这些页上的不同值,得到一个平均值,然后乘以这个索引的页数,就得到了这个索引的基数。
- 统计出的基数也不是固定不变的,随着更新的数据行数达到一定比例,又会触发一次统计
从上面的描述不难看出,优化器预估的扫描行数不仅不准确,还可能存在巨大偏差,这可能就是它错选索引的关键
解决方案
由于扫描行数差异过大导致的慢查询应该如何解决?
-
考虑使用 force index 语法,强制指定采用的索引。
这种方法简单粗暴,但不常用。因为这种情况导致的慢查询少见而且一般出现在生产中,使用 force index 需要修改语句源码,然后经过测试发布,不具备敏捷性。
-
考虑修改语句,引导 MySQL 使用期望的索引。
这种方式要具体情况具体分析,不具备通用性,也不推荐。
-
考虑新建或删除索引
如果索引非必要,常常删除误用的索引来达到目的
注意,本文只是提供一种分析处理慢查询语句的思路,各种现象千奇百怪,还要具体情况具体分析,不能生搬硬套。