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;多个相等条件也是范围查询

推荐阅读

ip
PHP中的MySQL函数库及其常用函数介绍

本文由编程笔记小编整理，介绍了PHP中的MySQL函数库及其常用函数，包括mysql_connect、mysql_error、mysql_select_db、mysql_query、mysql_affected_row、mysql_close等。希望对读者有一定的参考价值。 ... [详细]

蜡笔小新 2023-12-14 08:19:53
int
数据库的存储结构及其重要性

本文介绍了数据库的存储结构及其重要性，强调了关系数据库范例中将逻辑存储与物理存储分开的必要性。通过逻辑结构和物理结构的分离，可以实现对物理存储的重新组织和数据库的迁移，而应用程序不会察觉到任何更改。文章还展示了Oracle数据库的逻辑结构和物理结构，并介绍了表空间的概念和作用。 ... [详细]

蜡笔小新 2023-12-14 16:00:02
int
如何限制php数据库链接数和连接超时时间？

本文介绍了如何使用php限制数据库插入的条数并显示每次插入数据库之间的数据数目，以及避免重复提交的方法。同时还介绍了如何限制某一个数据库用户的并发连接数，以及设置数据库的连接数和连接超时时间的方法。最后提供了一些关于浏览器在线用户数和数据库连接数量比例的参考值。 ... [详细]

蜡笔小新 2023-12-14 14:06:10
int
eclipse学习（第三章：ssh中的Hibernate）——11.Hibernate的缓存（2级缓存，get和load）

本文介绍了eclipse学习中的第三章内容，主要讲解了ssh中的Hibernate的缓存，包括2级缓存和get方法、load方法的区别。文章还涉及了项目实践和相关知识点的讲解。 ... [详细]

蜡笔小新 2023-12-14 00:31:35
header
开发笔记：解决播放框架内容安全策略设置不起作用的问题

本文介绍了作者在开发过程中遇到的问题，即播放框架内容安全策略设置不起作用的错误。作者通过使用编译时依赖注入的方式解决了这个问题，并分享了解决方案。文章详细描述了问题的出现情况、错误输出内容以及解决方案的具体步骤。如果你也遇到了类似的问题，本文可能对你有一定的参考价值。 ... [详细]

蜡笔小新 2023-12-13 16:03:19
int
开发笔记：select from具体执行相关知识介绍及案例分析

本文由编程笔记小编整理，主要介绍了select from具体执行相关的知识，包括数据插入、查询最小rowID、查询每个重复名字的最小rowID、删除重复数据等操作，并提供了案例分析。希望对读者有一定的参考价值。 ... [详细]

蜡笔小新 2023-12-13 15:54:05
int
如何查看mysql的安装路径

本文介绍了通过mysql命令查看mysql的安装路径的方法，提供了相应的sql语句，并希望对读者有参考价值。 ... [详细]

蜡笔小新 2023-12-13 13:23:09
format
MySQL表分区的创建、增加和删除方法详解

本文详细介绍了MySQL表分区的创建、增加和删除方法，包括查看分区数据量和全库数据量的方法。欢迎大家阅读并给予点评。 ... [详细]

蜡笔小新 2023-12-13 12:26:11
int
用SQL语句怎么把已存在的列加上IDENTITY(1,1)属性

ALTERTABLE通过更改、添加、除去列和约束，或者通过启用或禁用约束和触发器来更改表的定义。语法ALTERTABLEtable{[ALTERCOLUMNcolu ... [详细]

蜡笔小新 2023-12-13 09:49:28
string
关于Linq to sql 实现模糊查询 string数组

前景：当UI一个查询条件为多项选择，或录入多个条件的时候，比如查询所有名称里面包含以下动态条件，需要模糊查询里面每一项时比如是这样一个数组条件：newstring[]{兴业银行, ... [详细]

蜡笔小新 2023-12-13 09:34:59
int
MyBatis多表查询与动态SQL使用

本文介绍了MyBatis多表查询与动态SQL的使用方法，包括一对一查询和一对多查询。同时还介绍了动态SQL的使用，包括if标签、trim标签、where标签、set标签和foreach标签的用法。文章还提供了相关的配置信息和示例代码。 ... [详细]

蜡笔小新 2023-12-12 17:12:51
int
MySQL外键1对多问题的解决方法及实例

本文介绍了解决MySQL外键1对多问题的方法，通过准备数据、创建表和设置外键关联等步骤，实现了用户分组和插入数据的功能。详细介绍了数据准备的过程和外键关联的设置，以及插入数据的示例。 ... [详细]

蜡笔小新 2023-12-12 15:55:25
int
数据库知识点复习及命令使用

本文主要复习了数据库的一些知识点，包括环境变量设置、表之间的引用关系等。同时介绍了一些常用的数据库命令及其使用方法，如创建数据库、查看已存在的数据库、切换数据库、创建表等操作。通过本文的学习，可以加深对数据库的理解和应用能力。 ... [详细]

蜡笔小新 2023-12-11 16:27:44
int
Oracle 11g物理Active Data Guard实时查询（Realtime query）特性

在Oracle11g以前版本中的的DataGuard物理备用数据库，可以以只读的方式打开数据库，但此时MediaRecovery利用日志进行数据同步的过 ... [详细]

蜡笔小新 2023-12-11 15:49:10
string
C#制作Java+Mysql+Tomcat环境安装程序，一键式安装教程

本文介绍了如何使用C#制作Java+Mysql+Tomcat环境安装程序，实现一键式安装。通过将JDK、Mysql、Tomcat三者制作成一个安装包，解决了客户在安装软件时的复杂配置和繁琐问题，便于管理软件版本和系统集成。具体步骤包括配置JDK环境变量和安装Mysql服务，其中使用了MySQL Server 5.5社区版和my.ini文件。安装方法为通过命令行将目录转到mysql的bin目录下，执行mysqld --install MySQL5命令。 ... [详细]

蜡笔小新 2023-12-12 19:29:55

helenheling2007895

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章