MySQL的order by该如何避免“未命中索引“_mysql order by 命中索引问题

  不少同学私信我说，用Explain查看Order By语句执行计划时经常发现用不上索引，难道花好多时间和资源创建的联合索引都摆烂了？今天我把几个同学遇到的情况整理出来，做一个Order By使用索引的坑点分享。希望对你有用。

  要学会如何使用，你先要搞清楚：1、怎么看SQL是否用上了索引；2、怎么写SQL能避开出错点。

  对了，如果对Explain查看索引命中情况比较了解的同学可以直接跳转第三部分。

一、测试数据导入

-- ----------------------------
-- Table structure for t_lol
-- ----------------------------
DROP TABLE IF EXISTS `t_lol`;
CREATE TABLE `t_lol`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `hero_title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `hero_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `price` int(0) NULL DEFAULT NULL,
  `sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_title_name_price`(`hero_title`, `hero_name`, `price`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_lol
-- ----------------------------
INSERT INTO `t_lol` VALUES (1, '刀锋之影', '泰隆', 6300, NULL);
INSERT INTO `t_lol` VALUES (2, '迅捷斥候', '提莫', 6300, NULL);
INSERT INTO `t_lol` VALUES (3, '光辉女郎', '拉克丝', 1350, NULL);
INSERT INTO `t_lol` VALUES (4, '发条魔灵', '奥莉安娜', 6300, NULL);
INSERT INTO `t_lol` VALUES (5, '至高之拳', '李青', 6300, NULL);
INSERT INTO `t_lol` VALUES (6, '无极剑圣', '易', 450, NULL);
INSERT INTO `t_lol` VALUES (7, '疾风剑豪', '亚索', 6300, NULL);
INSERT INTO `t_lol` VALUES (8, '女枪', '好运', 1350, NULL);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

二、Explain查看索引使用情况

  查看Explain执行计划是我们开发人员必须掌握的一个技能，下一篇我会整理Explain执行计划的详细查看方法。

  本篇文章是查看索引使用情况，我们通过key列、Extra列判断足矣。key列即展示使用到的索引，下面重点看一下当使用到索引即key列有值时，Extra列展示的相关信息都代表啥。

2-1、Using index

  构成了覆盖索引，where筛选条件也符合索引的最左前缀原则。

2-2、Using where,Using index

1. 查询的列被索引覆盖，并且where筛选条件是索引列之一但是不是索引的前导列，无法直接通过索引查找来查询到符合条件的数据。
2. 查询的列被索引覆盖，并且where筛选条件是索引列前导列的一个范围，同样意味着无法直接通过索引查找查询到符合条件的数据。

2-3、NULL

  既没有Using index，也没有Using where,Using index，也没有using where。

  查询的列未被索引覆盖，并且where筛选条件是索引的前导列。意味着可能用到了索引（我们可以根据key列判断是否用上索引），但是部分字段未被索引覆盖，必须通过回表来实现。

2-4、Using where

1. 查询的列未被索引覆盖，where筛选条件非索引的前导列；

2. 查询的列未被索引覆盖，where筛选条件非索引列；

  using where 意味着通过表扫描的方式进行where条件的过滤，也就是没找到可用的索引。

  当然也有特例，如果优化器判断索引扫描+回表的代价相比全表扫描的代价更大，则主动放弃索引的使用。

如果explain中type列值为all，说明MySQL认为全表扫描是一种比较低的代价。

2-5、Using index condition

1. 查询的列不全在索引中，where条件中是一个前导列的范围查询；

2. 查询列不完全被索引覆盖，但查询条件可以使用到索引；

三、Order By的使用示例

3-1、原表索引数据

mysql> show index from t_lol;
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_lol |          0 | PRIMARY              |            1 | id          | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| t_lol |          1 | idx_title_name_price |            1 | hero_title  | A         |           8 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t_lol |          1 | idx_title_name_price |            2 | hero_name   | A         |           8 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t_lol |          1 | idx_title_name_price |            3 | price       | A         |           8 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

  该表中有一个主键索引PRIMARY和一个联合索引idx_title_name_price(hero_title, hero_name, price)

3-2、不含where语句的示例

示例1：

  直接select联合索引三列，如下，可构造覆盖索引，不回表直接返回索引文件中的数据。

mysql> -- 使用了覆盖索引
mysql> EXPLAIN SELECT `hero_title`, `hero_name`, `price` from t_lol;
+----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key                  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_lol | NULL       | index | NULL          | idx_title_name_price | 267     | NULL |    8 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8

示例2：

  加上ORDER BY hero_title，功能和示例1完全相同，因为BTree索引有序，省去了自左向右各索引列的排序工作。

mysql> -- 同上，使用了覆盖索引（由于B树索引类型有序，省去了排序）
mysql> EXPLAIN SELECT `hero_title`, `hero_name`, `price` from t_lol ORDER BY hero_title;
+----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key                  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_lol | NULL       | index | NULL          | idx_title_name_price | 267     | NULL |    8 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8

示例3：

  使用了覆盖索引，MySQL 8.0新特性-倒叙索引 desc index。

mysql> -- 使用了覆盖索引，MySQL 8.0新特性-倒叙索引 desc index
mysql> EXPLAIN SELECT `hero_title`, `hero_name`, `price` from t_lol ORDER BY hero_title desc;
+----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key                  | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | t_lol | NULL       | index | NULL          | idx_title_name_price | 267     | NULL |    8 |   100.00 | Backward index scan; Using index |
+----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9

示例4：

  仅使用了ORDER BY price，联合索引左侧两列未使用，违反了最左原则，无法通过索引进行检索，但由于查询的各列构成覆盖索引，所以不用回表，可以直接拿索引文件中的数据进行二次重排序 → Using index; Using filesort

mysql> -- 违反了最左原则，直接ORDER BY col3；
mysql> EXPLAIN SELECT `hero_title`, `hero_name`, `price` from t_lol ORDER BY price;
+----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key                  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | t_lol | NULL       | index | NULL          | idx_title_name_price | 267     | NULL |    8 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8

示例5：

  多查了一列sex，由于sex字段是不包含在idx_title_name_price索引中所以无法使用该索引，当然，如果是select * 就更容易出现该情况。因此会走全表扫描+临时表排序（Using filesort），即Extra: Using filesort。

  这里我们很容易误解。因为我也感觉如果仅通过索引排序，即使select cols中使用到索引以外的列，仅用索引来排序再回表查也当是没问题才对，但使用时发现并不行。当舔狗的机会都没有？

  但！需要注意的是，如果where中有hero_title条件，便可以使用到索引了！那么说来，如果场景允许的话，我们是否可以构造一个如hero_title is not null的条件或force index强制使用索引等方式，来让我们的SQL硬用到索引的排序功能呢？emmm，好一个硬用方式。`

mysql> -- 未用到索引；因为多查了一列`sex`，当然，如果是select * 就更不用说了，无法构成覆盖索引，因此回表进行全表扫描+临 时表排序（Using filesort），最慢
mysql> EXPLAIN SELECT `hero_title`, `hero_name`, `price`,`sex` from t_lol ORDER BY hero_title;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t_lol | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8

3-3、包含where条件的

示例6：

  当出现where和order by的条件为联合索引(a,b,c)中的(a,c)；

  根据最左原则，只使用到了联合索引的hero_title列索引，后面两列被中断了，ORDER BY price无法使用到索引，故后面的排序只能通过后建临时表的方式来排序，即Extra:Using index; Using filesort

mysql> EXPLAIN SELECT `hero_title`, `hero_name`, `price` from t_lol where `hero_title` = '女枪' ORDER BY price;
+----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra                       |
+----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------------+
|  1 | SIMPLE      | t_lol | NULL       | ref  | idx_title_name_price | idx_title_name_price | 131     | const |    1 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7

示例7：

  当出现where和order by的条件为联合索引(a,b,c)中的(a,b)；能否使用索引？

  可以，实现了Using index覆盖索引，这里是触发了5.6推出的索引下推的特性，又根据最左原则使用到了联合索引(hero_title,hero_name)。

mysql> -- Using index覆盖索引，这里是触发了索引下推的特性
mysql> EXPLAIN SELECT `hero_title`, `hero_name`, `price` from t_lol where `hero_title` = '女枪' ORDER BY `hero_name`;
+----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t_lol | NULL       | ref  | idx_title_name_price | idx_title_name_price | 131     | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8

示例8：

  当出现where和order by的条件为联合索引(a,b,c)中的(a,b)；但where条件a查询使用了范围查询，b能否使用索引？

  我们根据最左原则知道，如果查询条件出现范围查询（如between、<、>等），索引使用即中断，后续条件无法再使用索引。这里同样，ORDER BY hero_name由于被中断无法使用索引，索引下推也无法使用。因此需要 Using filesort自行排序。

mysql> -- 未构成覆盖索引，这里无法触发索引下推特性，因为' > '将索引使用截断了。因此需要 Using filesort自行排序
mysql> EXPLAIN SELECT `hero_title`, `hero_name`, `price` from t_lol where `hero_title` > '女枪' ORDER BY `hero_name`;
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | t_lol | NULL       | range | idx_title_name_price | idx_title_name_price | 131     | NULL |    4 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8

特性9：

  当select [cols…]查询了联合索引(a,b,c)外的列（常见的select *）会如何？

  如下，用上了索引idx_title_name_price，但由于多了sex字段，在索引查询后需要再回表查询。

mysql> -- 用上了索引，由于多了`sex`字段，在索引查询后需要再回表查询。
mysql> EXPLAIN SELECT `hero_title`, `hero_name`, `price`,`sex` from t_lol where `hero_title` = '女枪' ORDER BY `hero_name`;
+----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_lol | NULL       | ref  | idx_title_name_price | idx_title_name_price | 131     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8

小结

  假设联合索引index(a,b,c)，总结一些条件命中索引的情况；

1、仅有 order by 条件，使用索引，基于最左前缀原则

order by a;
order by a,b;
order by a,b,c;
order by a asc,b asc,c asc;
order by a desc,b desc,c desc;
1
2
3
4
5

2、条件包含where和order by，使用索引

where a= 'chenhh' order by b,c;
where a= 'chenhh' and b= 'chenhh' order by c;
where a= 'chenhh' and b> 'chenhh' order by b,c;
1
2
3

3、order by无法通过索引进行排序的情况

order by a asc,b desc, c desc;
where g=const order by b,c;
where a=const order by c;
where a=const order by a,d; -- d不是索引一部分
where a in (....) order by b,c; -- 对于排序来说，多个相等条件也是范围查询 
1
2
3
4
5