MySQL的orderby该如何避免未命中索引

作者：helenheling2007895 | 来源：互联网 | 2023-10-10 12:14

在使用Explain查看OrderBy语句执行计划时经常发现用不上索引，难道花好多时间和资源创建的联合索引都摆烂了？今天我把遇到的情况整理出来ÿ

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

要学会如何使用&＃xff0c;你先要搞清楚&＃xff1a;

怎么看SQL是否用上了索引&＃xff1b;
怎么写SQL能避开出错点。

一、测试数据导入

#来源公众号&＃xff1a;【码农编程进阶笔记】 -- ---------------------------- -- Table structure for t_lol -- ---------------------------- DROP TABLE IF EXISTS &＃96;t_lol&＃96;; CREATE TABLE &＃96;t_lol&＃96; (&＃96;id&＃96; int(0) NOT NULL AUTO_INCREMENT,&＃96;hero_title&＃96; varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,&＃96;hero_name&＃96; varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,&＃96;price&＃96; int(0) NULL DEFAULT NULL,&＃96;sex&＃96; varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,PRIMARY KEY (&＃96;id&＃96;) USING BTREE,INDEX &＃96;idx_title_name_price&＃96;(&＃96;hero_title&＃96;, &＃96;hero_name&＃96;, &＃96;price&＃96;) USING BTREE ) ENGINE &＃61; InnoDB AUTO_INCREMENT &＃61; 11 CHARACTER SET &＃61; utf8mb4 COLLATE &＃61; utf8mb4_general_ci ROW_FORMAT &＃61; Dynamic;-- ---------------------------- -- Records of t_lol -- ---------------------------- INSERT INTO &＃96;t_lol&＃96; VALUES (1, &＃39;刀锋之影&＃39;, &＃39;泰隆&＃39;, 6300, NULL); INSERT INTO &＃96;t_lol&＃96; VALUES (2, &＃39;迅捷斥候&＃39;, &＃39;提莫&＃39;, 6300, NULL); INSERT INTO &＃96;t_lol&＃96; VALUES (3, &＃39;光辉女郎&＃39;, &＃39;拉克丝&＃39;, 1350, NULL); INSERT INTO &＃96;t_lol&＃96; VALUES (4, &＃39;发条魔灵&＃39;, &＃39;奥莉安娜&＃39;, 6300, NULL); INSERT INTO &＃96;t_lol&＃96; VALUES (5, &＃39;至高之拳&＃39;, &＃39;李青&＃39;, 6300, NULL); INSERT INTO &＃96;t_lol&＃96; VALUES (6, &＃39;无极剑圣&＃39;, &＃39;易&＃39;, 450, NULL); INSERT INTO &＃96;t_lol&＃96; VALUES (7, &＃39;疾风剑豪&＃39;, &＃39;亚索&＃39;, 6300, NULL); INSERT INTO &＃96;t_lol&＃96; VALUES (8, &＃39;女枪&＃39;, &＃39;好运&＃39;, 1350, NULL);

二、Explain查看索引使用情况

查看Explain执行计划是我们开发人员必须掌握的一个技能&＃xff0c;下一篇我会整理Explain执行计划的详细查看方法。来源公众号&＃xff1a;【码农编程进阶笔记】

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

2-1、Using index

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

2-2、Using where,Using index

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

2-3、NULL

既没有Using index&＃xff0c;也没有Using where,Using index&＃xff0c;也没有using where。

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

2-4、Using where

查询的列未被索引覆盖&＃xff0c;where筛选条件非索引的前导列&＃xff1b;
查询的列未被索引覆盖&＃xff0c;where筛选条件非索引列&＃xff1b;

using where 意味着通过表扫描的方式进行where条件的过滤&＃xff0c;也就是没找到可用的索引。来源公众号&＃xff1a;【码农编程进阶笔记】

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

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

2-5、Using index condition

查询的列不全在索引中&＃xff0c;where条件中是一个前导列的范围查询&＃xff1b;
查询列不完全被索引覆盖&＃xff0c;但查询条件可以使用到索引&＃xff1b;

三、Order By的使用示例

3-1、原表索引数据

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

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

示例1&＃xff1a;直接select联合索引三列&＃xff0c;如下&＃xff0c;可构造覆盖索引&＃xff0c;不回表直接返回索引文件中的数据。

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

示例3&＃xff1a;使用了覆盖索引&＃xff0c;MySQL 8.0新特性-倒叙索引 desc index。

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

示例5&＃xff1a;多查了一列sex&＃xff0c;由于sex字段是不包含在idx_title_name_price索引中所以无法使用该索引&＃xff0c;当然&＃xff0c;如果是select * 就更容易出现该情况。因此会走全表扫描&＃43;临时表排序&＃xff08;Using filesort&＃xff09;&＃xff0c;即Extra: Using filesort。

这里我们很容易误解。因为我也感觉如果仅通过索引排序&＃xff0c;即使select cols中使用到索引以外的列&＃xff0c;仅用索引来排序再回表查也当是没问题才对&＃xff0c;但使用时发现并不行。当舔狗的机会都没有&＃xff1f;来源公众号&＃xff1a;【码农编程进阶笔记】

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

3-3、包含where条件的

示例6&＃xff1a;当出现where和order by的条件为联合索引(a,b,c)中的(a,c)&＃xff1b;

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

示例7&＃xff1a;当出现where和order by的条件为联合索引(a,b,c)中的(a,b)&＃xff1b;能否使用索引&＃xff1f;

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

示例8&＃xff1a;当出现where和order by的条件为联合索引(a,b,c)中的(a,b)&＃xff1b;但where条件a查询使用了范围查询&＃xff0c;b能否使用索引&＃xff1f;

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

#来源公众号&＃xff1a;【码农编程进阶笔记】 mysql> -- 未构成覆盖索引&＃xff0c;这里无法触发索引下推特性&＃xff0c;因为&＃39; > &＃39;将索引使用截断了。因此需要 Using filesort自行排序 mysql> EXPLAIN SELECT &＃96;hero_title&＃96;, &＃96;hero_name&＃96;, &＃96;price&＃96; from t_lol where &＃96;hero_title&＃96; > &＃39;女枪&＃39; ORDER BY &＃96;hero_name&＃96;; &＃43;----&＃43;-------------&＃43;-------&＃43;------------&＃43;-------&＃43;----------------------&＃43;----------------------&＃43;---------&＃43;------&＃43;------&＃43;----------&＃43;------------------------------------------&＃43; | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | &＃43;----&＃43;-------------&＃43;-------&＃43;------------&＃43;-------&＃43;----------------------&＃43;----------------------&＃43;---------&＃43;------&＃43;------&＃43;----------&＃43;------------------------------------------&＃43; | 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 | &＃43;----&＃43;-------------&＃43;-------&＃43;------------&＃43;-------&＃43;----------------------&＃43;----------------------&＃43;---------&＃43;------&＃43;------&＃43;----------&＃43;------------------------------------------&＃43; 1 row in set, 1 warning (0.00 sec)

特性9&＃xff1a;当select [cols…]查询了联合索引(a,b,c)外的列&＃xff08;常见的select *&＃xff09;会如何&＃xff1f;

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

小结

假设联合索引 index(a,b,c)&＃xff0c;总结一些条件命中索引的情况&＃xff1b;

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

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&＃xff0c;使用索引

where a&＃61; &＃39;chenhh&＃39; order by b,c; where a&＃61; &＃39;chenhh&＃39; and b&＃61; &＃39;chenhh&＃39; order by c; where a&＃61; &＃39;chenhh&＃39; and b> &＃39;chenhh&＃39; order by b,c;

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

order by a asc,b desc, c desc; where g&＃61;const order by b,c; where a&＃61;const order by c; where a&＃61;const order by a,d; -- d不是索引一部分 where a in (....) order by b,c; -- 对于排序来说&＃xff0c;多个相等条件也是范围查询