MySQL的order by如何避免"未命中索引"(推荐)

目录

• 一、测试数据导入
• 二、Explain查看索引使用情况
• 2-1、Using index
• 2-2、Using where,Using index
• 2-3、NULL
• 2-4、Using where
• 2-5、Using index condition
• 三、Order By的使用示例
• 3-1、原表索引数据
• 3-2、不含where语句的示例
• 3-3、包含where条件的
• 小结

  不少同学私信我说，用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);

二、Explain查看索引使用情况

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

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

2-1、Using index

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

2-2、Using where,Using index

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

2-3、NULL

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

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

2-4、Using where

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

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

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

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

2-5、Using index condition

• 查询的列不全在索引中，where条件中是一个前导列的范围查询；
• 查询列不完全被索引覆盖，但查询条件可以使用到索引；

三、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)

  该表中有一个主键索引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)

示例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)

示例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          python| idx_title_name_price | 267     | NULL |    8 |   100.00 | Backward index scan; Using index |
+----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

示例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)

示例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），最慢
mysq编程客栈l> 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)

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)

示例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`;
+----+-------------+-------+------------+------+-----javascript-----------------+----------------------+---------+-------+------+----------+-------------+
| 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)

示例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)

特性9：

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

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

mysql> -- 用上了索引，由于多了`sex`字段，在索引查询后需要再回表查询。
mysql&ghttp://www.devze.comt; 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)

小结

  假设联合索引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;

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;

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; -- 对于排序来说，多个相等条件也是范围查询 

到此这篇关于MySQL的order by该如何避免“未命中索引“的文章就介绍到这了,更多相关mysql order by未命中索引内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！