您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

深入探讨 MySQL 的 order by 优化

时间:2019-08-14 13:58:51  来源:  作者:

文章转载自yangyidba , 作者 杨奇龙

一 前言

为什么是再说呢?因为前面已经写过 《order by 原理以及优化》 ,介绍 order by 的基本原理以及优化。如果觉得对 order by 原理了解不透彻可以参考其他同行的文章《MySQL排序内部原理探秘》.本文是基于官网文档的二刷(基本翻译+测试验证),看完本文大部分开发同学可以了解到什么样的select + order by 语句可以使用索引,什么样的不能利用到索引排序

二 分析

2.1 官方标准介绍

对于select order by语句如何能够利用到索引,官方表述如下:

"The index can also be used even if the ORDER BY does not match the index exactly, as long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause."

翻译一下就是

即使ORDER BY语句不能精确匹配(组合)索引列也能使用索引,只要WHERE条件中的所有未使用的索引部分和所有额外的ORDER BY列为常数就行。如何理解这句话呢?我们通过具体用例来解释。

2.2 准备工作

深入探讨 MySQL 的 order by 优化
深入探讨 MySQL 的 order by 优化
深入探讨 MySQL 的 order by 优化

2.3 能够利用索引的例子分析

官方的文档 中介绍有7个例子可以使用索引进行排序。如果使用explain/desc工具查看执行计划中的extra中出现了Using filesort则说明sql没有用到排序优化。

案例一

SELECT * FROM t1 ORDER BY key_part1,key_part2,...;

深入探讨 MySQL 的 order by 优化

分析:

显然上述sql没有利用到索引排序. type=ALL Extra=Using filesort,因为where字句没有条件,优化器选择全表扫描和内存排序。

深入探讨 MySQL 的 order by 优化

分析:

从type=index,extra=Using index 可以看出当select 的字段包含在索引中时,能利用到索引排序功能,进行覆盖索引扫描。使用select * 则不能利用覆盖索引扫描且由于where语句没有具体条件MySQL选择了全表扫描且进行了排序操作。

案例二

SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;

使用组合索引中的一部分做等值查询 ,另一部分作为排序字段。更严谨的说法是where条件使用组合索引的左前缀等值查询,使用剩余字段进行order by排序。

深入探讨 MySQL 的 order by 优化

分析:

where 条件字句可以基于 shid 进行索引查找并且利用(shid,gid)中gid的有序性避免额外的排序工作。我们基于本例解释"即使ORDER BY语句不能精确匹配(组合)索引列也能使用索引,只要WHERE条件中的所有未使用的索引部分和所有额外的ORDER BY列为常数就行。"

该语句的order by gid 并未精确匹配到组合索引(shid,gid),where条件 shid利用了组合索引的最左前缀且为等值常量查询,对order by 而言shid就是额外的字段,没有出现在order by子句中却是组合索引的一部分。这样的条件既可以使用索引来排序。

案例三

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

深入探讨 MySQL 的 order by 优化

其实和案例一 类似,只是选择了倒序。该sql不能利用索引的有序性,需要server层进行排序。

案例四

SELECT * FROM t1 WHERE keypart1 = 1 ORDER BY keypart1 DESC, key_part2 DESC;

深入探讨 MySQL 的 order by 优化

本例和案例二类似,只是order by 字句中包含所有的组合索引列。

分析:

where shid=4 可以利用shid的索引定位数据记录,select * 有不在索引里面的字段,所以回表访问组合索引列之外的数据,利用了gid索引的有序性避免了排序工作。

案例五

SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC;

SELECT * FROM t1 WHERE key_part1 < constant ORDER BY key_part1 DESC;

深入探讨 MySQL 的 order by 优化

分析:

表总共24行,其中大于5的有16行,大于13的2行,导致MySQL优化器选择了不同的执行计划。这个测试说明和shid的区分度有关。

案例六

SELECT * FROM t1 WHERE key_part1 = constant1 AND key_part2 > constant2 ORDER BY key_part2;

利用组合索引前缀索引进行ref等值查询,其他字段进行范围查询,order by 非等值的字段。

深入探讨 MySQL 的 order by 优化

分析:

利用shid=6的进行索引查询记录到了MySQL的ICP特性,无排序操作。

2.4 不能利用索引排序的分析

案例一 order by语句使用了多个不同的索引

SELECT * FROM t1 ORDER BY key1, key2;

深入探讨 MySQL 的 order by 优化

分析:

因为sql使用了不同的索引列,在存储上顺序存在不一致的可能性,MySQL会选择排序操作。

特例因为所有的辅助索引里面都包含主键id,当where 字段加上order by字段沟通完整的索引时 ,可以避免filesort的。

深入探讨 MySQL 的 order by 优化

案例二当查询条件使用了与order by不同的其他的索引,且值为常量,但排序字段是另一个联合索引的非连续部分时

SELECT * FROM t1 WHERE key2=constant ORDER BY keypart1, keypart3;

深入探讨 MySQL 的 order by 优化

分析:

与案例一一致,key2 的顺序语句key1(key_part1)存储排序不一样的情况下,MySQL 都会选择filesort 。

案例三order by 语句使用了和组合索引默认不同的排序规则

SELECT * FROM t1 ORDER BY keypart1 DESC, keypart2 ASC;

官方文档中提示使用混合索引排序规则会导致额外排序,其实我们创建索引的时候可以做 (keypart1 DESC, keypart2 ASC)

案例四当where 条件中利用的索引与order by 索引不同时,与案例二有相似性。

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

深入探讨 MySQL 的 order by 优化

分析:

案例的sql 利用了idxtype 索引,但是order 使用了shid,gid 字段,没有包含在idxtype 索引里面,故不能利用idx_type索引排序。

案例五order by 字段使用了表达式

SELECT * FROM t1 ORDER BY ABS(key);

SELECT * FROM t1 ORDER BY -key;

深入探讨 MySQL 的 order by 优化
深入探讨 MySQL 的 order by 优化

分析:order by 的字段使用函数,和在where条件中使用函数索引一样 ,MySQL都无法利用到索引。

案例六

The query joins many tables,and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows.(This is the first table in the EXPLAIN output that does not have a const join type.)

当查询语句是多表连接,并且ORDER BY中的列并不是全部来自第1个用于搜索行的非常量表.(这是EXPLAIN输出中的没有使用const联接类型的第1个表)

深入探讨 MySQL 的 order by 优化

分析:

出现join的情况下不能利用索引其实有很多种,只要对a的访问不满足上面说的可以利用索引排序的情况都会导致额外的排序动作。但是当where + order 复合要求,order by 有包含了其他表的列就会导致额外的排序动作。

案例七sql中包含的order by 列与group by 列不一致 。

深入探讨 MySQL 的 order by 优化

group by 本身会进行排序的操作,我们可以显示的注让group by不进行额外的排序动作。

案例八索引本身不支持排序存储 比如,hash索引。

深入探讨 MySQL 的 order by 优化
深入探讨 MySQL 的 order by 优化

分析

hash 索引本身不支持排序存储,故不能利用到排序特性,将表转化为innodb再次查询,避免了filesort。

案例九order by的索引使用部分字符串 比如 key idx_name(name(2))

深入探讨 MySQL 的 order by 优化

三 老生常谈的优化策略

为了提高order by 查询的速度,尽可能的利用索引的有序性进行排序,如果不能利用索引排序的功能,那么我们只能退而求其次优化order by相关的缓存参数。

  1. 增加 sort_buffer_size 大小,建议sort_buffer_size要足够大能够避免磁盘排序和合并排序次数。
  2. 增加 read_rnd_buffer_size 大小。
  3. 使用合适的列大小存储具体的内容,比如对于city字段 varchar(20)比varchar(200)能获取更好的性能。
  4. 将tmpdir 目录指定到os上面有足够空间的具有比较高iops能力的存储上。


Tags:MySQL 优化   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
单表优化除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分,拆分会带来逻辑、部署、运维的各种复杂度,一般以整型值为主的表在千万级以下,字符串为主的表在五百万以下是...【详细内容】
2020-04-17  Tags: MySQL 优化  点击:(73)  评论:(0)  加入收藏
当MySQL单表记录数过大时,增加、删除、修改和查询的性能将急剧下降,您可以参考以下步骤进行优化。单表优化 除非你预计未来你的单表数据会不断的持续上涨,否则不要一开始就考虑...【详细内容】
2020-04-14  Tags: MySQL 优化  点击:(70)  评论:(0)  加入收藏
谁在消耗cpu?用户+系统+IO等待+软硬中断+空闲 祸首是谁?用户用户空间CPU消耗,各种逻辑运算正在进行大量tps函数/排序/类型转化/逻辑IO访问&hellip;用户空间消耗大量cpu,产生...【详细内容】
2020-03-09  Tags: MySQL 优化  点击:(144)  评论:(0)  加入收藏
千万级大表如何优化,这是一个很有技术含量的问题,通常我们的直觉思维都会跳转到拆分或者数据分区,在此我想做一些补充和梳理,想和大家做一些这方面的经验总结,也欢迎大家提出建议...【详细内容】
2020-02-11  Tags: MySQL 优化  点击:(60)  评论:(0)  加入收藏
概述优化SQL,是DBA常见的工作之一。如何高效、快速地优化一条语句,是每个DBA经常要面对的一个问题。对于一名DBA来说,掌握一门语言配合自己的工作是非常必要的。相对于shell的...【详细内容】
2019-10-09  Tags: MySQL 优化  点击:(607)  评论:(0)  加入收藏
mysql5.6之前处理客户端连接的方式会触发mysql 新建一个线程来处理新的连接,新建的线程会处理该连接所发送的所有 SQL 请求,即 one-thread-per-connection 的方式,其创建连接的堆栈为:...【详细内容】
2019-09-06  Tags: MySQL 优化  点击:(160)  评论:(0)  加入收藏
1 前言在进行 MySQL 的优化之前必须要了解的就是 MySQL 的查询过程,很多的查询优化工作实际上就是遵循一些原则让 MySQL 的优化器能够按照预想的合理方式运行而已。 2 优化...【详细内容】
2019-09-05  Tags: MySQL 优化  点击:(151)  评论:(0)  加入收藏
MySQL 对于很多 Linux 从业者而言,是一个非常棘手的问题,多数情况都是因为对数据库出现问题的情况和处理思路不清晰。在进行 MySQL 的优化之前必须要了解的就是 MySQL 的查询过程,很多的查询优化工作实际上就是遵循一些...【详细内容】
2019-09-03  Tags: MySQL 优化  点击:(157)  评论:(0)  加入收藏
数据千万级别之多,占用的存储空间也比较大,可想而知它不会存储在一块连续的物理空间上,而是链式存储在多个碎片的物理空间上。可能对于长字符串的比较,就用更多的时间查找与比...【详细内容】
2019-08-27  Tags: MySQL 优化  点击:(268)  评论:(0)  加入收藏
个人的观点,这种大表的优化,不一定上来就要分库分表,因为表一旦被拆分,开发、运维的复杂度会直线上升,而大多数公司是欠缺这种能力的。所以MySQL中几百万甚至小几千万的表,先考虑...【详细内容】
2019-08-26  Tags: MySQL 优化  点击:(907)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(7)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(28)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条