在数据库查询中，ORDER BY语句用于对查询结果进行排序。然而，如果使用不当，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);
二、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 | 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），最慢 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)
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`; +----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+ | 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> 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语句以避免索引未命中的方法。这些方法包括使用合适的索引、避免在ORDER BY子句中使用函数、使用LIMIT子句限制返回的行数等。这些优化技巧能够显著提高查询性能，减少数据库的负担。希望本文的内容能够为您的MySQL查询优化提供有力支持，让您的数据库操作更加高效。
本文来源于#陈哈哈，由@站地网 整理发布。如若内容造成侵权/违法违规/事实不符，请联系本站客服处理!
该文章观点仅代表作者本人，不代表本站立场。本站不承担相关法律责任。
如若转载，请注明出处：https://www.zhanid.com/biancheng/2237.html
