MySQL语句优化（二）：优化索引选择

MySQL数据库联盟 2019-03-08

532

一、SQL语句及执行计划

表结构

CREATE TABLE `t_10` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=803446 DEFAULT CHARSET=utf8;

SQL语句

mysql> select * from t_10 where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
Empty set (0.34 sec)

SQL语句执行计划

mysql> explain select * from t_10 where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+------------------------------------+
|  1 | SIMPLE      | t_10  | NULL       | range | a,b           | b    | 5       | NULL | 329688 |     2.18 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+------------------------------------+

从执行计划可以看出，该SQL走了扫描行数更多的b索引

二、原因分析

由于语句查询到结果后又需要对b字段再进行排序，而b字段本身有索引，已经是有序的，如果选择索引b的话，不需要再做排序，因此优化器走了b字段的索引（实际导致查询扫描行数更多耗时更久了）

三、SQL改写

改写后的SQL语句

mysql> select * from t_10 where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;
Empty set (0.01 sec)

改写后的执行计划

mysql> explain  select * from t_10 where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t_10  | NULL       | range | a,b           | a    | 5       | NULL | 14398 |    50.00 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+----------------------------------------------------+

显然，查询走了索引a，执行时间大大降低，并且扫描行数也减少了很多。

原因分析：当优化器发现a、b两个字段都需要排序时，会重点关注扫描行数，由于选择a索引扫描行数相对b索引少很多，因此走了索引a。

本文分享自微信公众号 - MySQL数据库联盟，如有侵权，请联系 service001@enmotech.com 删除。

mysql

最后修改时间：2019-12-20 11:33:18

文章转载自MySQL数据库联盟，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL语句优化（二）：优化索引选择

评论