MySQL中,一张表可以建立多个索引,而SQL具体使用哪个索引是由MySQL来确定的。
但是,有时候,一条本来可以执行的很快的语句,却由于MySQL选错了索引,而导致执行速度变得很慢。
看个例子(这个例子对应的是我们平常不断地删除历史数据和新增数据的场景):
- 建表,表中有a,b两个字段,并分别加上索引
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB ;
- 然后,我们往表 t 中插入 10 万行记录,取值按整数递增,即:(1,1,1),(2,2,2),(3,3,3) 直到 (100000,100000,100000)。
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
- 接下来,我们分析一条 SQL 语句:
mysql> select * from t where a between 10000 and 20000;
-
从上面可以看出,key这个字段是’a’,表示优化器选择了索引a
-
然后,我们再来做如下操作
- sessionA开启了一个事务
- 随后,session B把数据都删除之后,调度存储过程idata,插入10万行数据
- 这个时候,session B的查询语句
select * from t where a between 10000 and 20000
就不会选择索引a了。我们可以通过慢查询日志(slow log)来查看一下具体的执行情况。 - 为了说明优化器选择的结果是否正确,增加了一个对照,即:使用 force index(a) 来让优化器强制使用索引 a
set long_query_time=0; //是将慢查询日志的阈值设置为 0,表示这个线程接下来的语句都会被记录入慢查询日志中;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/
- 如下图,是执行上面操作之后的慢查询日志。可以看出:
- Q1 扫描了 10 万行,显然是走了全表扫描,执行时间是 40 毫秒
- Q2 扫描了10001 行,执行了 21 毫秒
- 也就是说,我们在没有使用 force index 的时候,MySQL 用错了索引,导致了更长的执行时间。
为什么MySQL会选错索引呢?
优化器的逻辑
选择索引是优化器的工作。而优化器选择索引的目的,是找到一个最优的执行方案,并以最小的代价去执行语句。
- 在数据库里面,扫描行数是影响代价之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗CPU的资源越少
- 当然,扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表,是否排序等因素进行综合判断
我们这个简单的查询语句并没有涉及到临时表和排序,所以MySQL选错索引肯定是在判断扫描行数的时候出问题了。
所以,问题是:扫描行数是怎么判断的?
- MySQL在真正执行语句之前,并不能精确的知道满足这个条件的记录有多少条,而只能根据统计信息来估算记录值
- 这个统计信息就是索引的“区分度”。显然,一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,我们称之为“基数”。也就是说,这个基数越大,索引的区分度越好
我们可以使用 show index 方法,看到一个索引的基数。如下图 所示,就是表 t 的 show index 的结果 。虽然这个表的每一行的三个字段值都是一样的,但是在统计信息中,这三个索引的基数值(cardinality)并不同,而且其实都不准确。
那么,MySQL是怎样得到索引的基数的呢?
是通过采样统计的方法。
- 为什么要采样统计呢?因为把整张表取出来一行行统计,虽然可以得到精确的结果,但是代价太高了,所以只能选择采样统计。
- 采样统计的时候,InnoDB默认会选择N个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面树,就得到了这个索引的基数。
- 而数据表是持续更新的,索引统计信息也不是固定不变的。所以,当变更的数据行数超过了1/M的时候,就会自动触发重新做一次索引统计。
在MySQL中,有两种存储索引统计的方法,可以通过设置参数 innodb_stats_persistent的值来选择:
- 设置为 on 的时候,表示统计信息会持久化存储。这时,默认的 N 是 20,M 是 10。
- 设置为 off 的时候,表示统计信息只存储在内存中。这时,默认的 N 是 8,M 是 16。
由于是采样统计,所以不管N是20还是8,这个基数都是很容易不准的。
但,这还不会是全部。
从上图中可以看大,这次的索引统计值(cardinality)虽然不精确,但是大体上还是拆不多的,选错索引一定还有其他的原因。
其实索引统计只是一个输入,对于一个具体的语句来说,优化器还要判断,执行这个语句本身要扫描多少行。explain的rows这个字段表示的是索引扫描行数:
- Q1 的结果还是符合预期的,rows 的值是 104620;
- 但是 Q2 的 rows 值是37116,偏差就大了
- 结合图1(也就是下图,为第一个场景中的实验数据)用 explain 命令看到的 rows 是只有 10001 行,是这个偏差误导了优化器的判断。
问题是:为什么优化器放着37116行的执行计划不用,却选择了扫描行数是104620的执行计划呢?
- 因为,如果使用索引a,每次从索引a上拿到一个值,都要回到主键索引上查出整行数据,这个代价优化器也要算进去的(使用普通索引需要把回表的代价算进去)
- 而如果选择了扫描10万行,是直接在主键索引上扫描的,没有额外的代价。
优化器会估算这两个选择的代价,从结果来看,优化器认为直接扫描主键索引更快。
所以冤有头债有主,MySQL 选错索引,这件事儿还得归咎到没能准确地判断出扫描行数。
既然是统计信息不对,那就修正。analyze table t 命令,可以用来重新统计索引信息。我们来看一下执行效果。
这回对了。
所以在实践中,如果你发现explain的结果预估的rows值跟实际情况差距比较大,可以采用analyze命令来修正。
优化器不止看扫描行数
例子:
mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
从条件上看,这个查询没有符合条件的记录,因此会返回空集合。
问题是:优化器会选择哪个索引呢?
分析:
- 如果使用索引a来查询,那么就是扫描索引a的前1000个值,然后取到对应id,再到主键索引上查出每一行,然后根据字段b来过滤。显然,这要扫描1000行
- 如果使用索引b来查询,那么就是扫描索引b的最后50001个值,然后取到对应id,再到主键索引上查出每一行,然后根据字段b来过滤。显然,这要扫描50001行
那,是不是就应该选择索引a呢?
我们来看下explain,可以看到:
- 返回结果的key表示这次优化器选择了索引b,
- 而 rows 字段显示需要扫描的行数是 50198。
从上面可以推出: - 扫描行数的估计值依然不正确
- 这个例子中MySQL又选错了索引
纠正:force index(a)
可以看到,原本语句需要执行 2.23 秒,而使用 force index(a) 的时候,只用了 0.05秒,比优化器的选择快了 40 多倍。
索引选择异常和处理
如果索引选错了怎么办?
-
方法一: 采用force index 强行选择一个索引
- MySQL会根据词法解析的结果分析出可能可以使用的索引作为候选项,然后再候选列表中依次判断每个索引需要扫描多少行
- 如果force index指定的索引在候选索引列表中,就直接选择这个索引,不再评估其他索引的执行代价
- 问题是:一般来说,选错索引的情况还是比较少的,所以开发的时候一般不会先写上 force index。而是等到线上出现问题的时候,你才会再去修改 SQL 语句、加上 force index。但是修改之后还要测试和发布,对于生产系统来说,这个过程不够敏捷。
-
方法二:考虑修改语句,,引导 MySQL 使用我们期望的索引
- 比如,把“order by b limit 1” 改成 “order by b,a limit 1”
- 之前优化器选择使用索引 b,是因为它认为使用索引 b 可以避免排序(b 本身是索引,已经是有序的了,如果选择索引 b 的话,不需要再做排序,只需要遍历),所以即使扫描行数多,也判定为代价更小。
- 现在 order by b,a 这种写法,要求按照 b,a 排序,就意味着使用这两个索引都需要排序。因此,扫描行数成了影响决策的主要条件,于是此时优化器选了只需要扫描 1000 行的索引a。
- 当然,这种修改并不是通用的优化手段,只是刚好在这个语句里面有 limit 1,因此如果有满足条件的记录, order by b limit 1 和 order by b,a limit 1 都会返回 b 是最小的那一行,逻辑上一致,才可以这么做。
-
方法三:在有些场景下,我们可以新建一个更合适的索引,来提供给优化器做选择,或删掉误用的索引
总结
优化器存在选错索引的可能, 怎么解决呢:
- 对于由于索引统计信息不准确导致的问题,你可以用 analyze table 来解决。
- 而对于其他优化器误判的情况,你可以在应用端用 force index 来强行指定索引,也可以通过修改语句来引导优化器,还可以通过增加或者删除索引来绕过这个问题。