order by 原理以及优化

一 简介 偏向于业务的(MySQL)DBA或者业务的开发者来说,order by 排序是一个常见的业务功能,将结果根据指定的字段排序,满足前端展示的需求。然而排序操作也是经常出现慢查询排行榜的座上宾。本文将从原理和实际案例优化,order by 使用限制等几个方面来逐步了解order by 排序。

二 原理 在了解order by 排序的原理之前,强烈安利两篇关于排序算法的文章 《归并排序的实现》经典排序算法》。MySQL 支持两种排序算法,常规排序和优化,并且在MySQL 5.6版本中 针对order by limit M,N 做了特别的优化,这里列为第三种。 2.1 常规排序 a 从表t1中获取满足WHERE条件的记录 b 对于每条记录,将记录的主键+排序键(id,col2)取出放入sort buffer c 如果sort buffer可以存放所有满足条件的(id,col2)对,则进行排序;否则sort buffer满后,进行排序并固化到临时文件中。(排序算法采用的是快速排序算法) d 若排序中产生了临时文件,需要利用归并排序算法,保证临时文件中记录是有序的 e 循环执行上述过程,直到所有满足条件的记录全部参与排序 f 扫描排好序的(id,col2)对,并利用id去捞取SELECT需要返回的列(col1,col2,col3) g 将获取的结果集返回给用户。 从上述流程来看,是否使用文件排序主要看sort buffer是否能容下需要排序的(id,col2)对,这个buffer的大小由sort_buffer_size参数控制。此外一次排序需要两次IO,一次是捞(id,col2),第二次是捞(col1,col2,col3),由于返回的结果集是按col2排序,因此id是乱序的,通过乱序的id去捞(col1,col2,col3)时会产生大量的随机IO。对于第二次MySQL本身一个优化,即在捞之前首先将id排序,并放入缓冲区,这个缓存区大小由参数read_rnd_buffer_size控制,然后有序去捞记录,将随机IO转为顺序IO。 2.2 优化排序 常规排序方式除了排序本身,还需要额外两次IO。优化的排序方式相对于常规排序,减少了第二次IO。主要区别在于,放入sort buffer不是(id,col2),而是(col1,col2,col3)。由于sort buffer中包含了查询需要的所有字段,因此排序完成后可以直接返回,无需二次捞数据。这种方式的代价在于,同样大小的sort buffer,能存放的(col1,col2,col3)数目要小于(id,col2),如果sort buffer不够大,可能导致需要写临时文件,造成额外的IO。当然MySQL提供了参数max_length_for_sort_data,只有当排序元组小于max_length_for_sort_data时,才能利用优化排序方式,否则只能用常规排序方式。 2.3 优先队列排序 为了得到最终的排序结果,无论怎样,我们都需要将所有满足条件的记录进行排序才能返回。那么相对于优化排序方式,是否还有优化空间呢?5.6版本针对Order by limit M,N语句,在空间层面做了优化,加入了一种新的排序方式:优先队列,这种方式采用堆排序实现。堆排序算法特征正好可以解limit M,N 这类排序的问题,虽然仍然需要所有元素参与排序,但是只需要M+N个元组的sort buffer空间即可,对于M,N很小的场景,基本不会因为sort buffer不够而导致需要临时文件进行归并排序的问题。对于升序,采用大顶堆,最终堆中的元素组成了最小的N个元素,对于降序,采用小顶堆,最终堆中的元素组成了最大的N的元素。 三 优化 通过上面的原理分析,我们知道排序的本质是通过一定的算法(耗费cpu 运算,内存,临时文件IO)将结果集变成有序的结果集。如何优化呢?答案是分两个方面利用索引的有序性(MySQL的B+ 树索引是默认从小到大递增排序)减少排序,最好的方式是直接不排序。

  1. create table t1(
  2. id int not null primary key ,
  3. key_part1 int(10) not null,
  4. key_part2 varchar(10) not null default '',
  5. key_part3
  6. key idx_kp1_kp2(key_part1,key_part2,key_part4),
  7. key idx_kp3(id)
  8. ) engine=innodb default charset=utf8

以下种类的查询是可以利用到索引 idx_kp1_kp2的

  1. SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
  2. SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;
  3. SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
  4. SELECT * FROM t1 WHERE key_part1 = 1 ORDER BY key_part1 DESC, key_part2 DESC;
  5. SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC;
  6. SELECT * FROM t1 WHERE key_part1 < constant ORDER BY key_part1 DESC;
  7. SELECT * FROM t1 WHERE key_part1 = constant1 AND key_part2 > constant2 ORDER BY key_part2

温馨提示 ,各位看官要辩证的看待官方给的例子,自己多动手实践。 无法利用到索引排序的情况,其实我觉得这是本文的重点,对于广大开发同学而言,记住那种不能利用索引排序会更简单些。

  1. 1 最常见的情况 用来查找结果的索引(key2) 和 排序的索引(key1) 不一样,where a=x and b=y order by id;
  2. SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
  3. 2 排序字段在不同的索引中,无法使用索引排序
  4. SELECT * FROM t1 ORDER BY key1,key2;
  5. 3 排序字段顺序与索引中列顺序不一致,无法使用索引排序,比如索引是 key idx_kp1_kp2(key_part1,key_part2)
  6. SELECT * FROM t1 ORDER BY key_part2, key_part1;
  7. 4 order by中的升降序和索引中的默认升降不一致,无法使用索引排序
  8. SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
  9. 5 ey_part1是范围查询,key_part2无法使用索引排序
  10. SELECT * FROM t1 WHERE key_part1> constant ORDER BY key_part2;
  11. 5 rder by和group by 字段列不一致
  12. SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2 group by key_part4;
  13. 6 索引本身是无序存储的,比如hash 索引,不能利用索引的有序性。
  14. 7 order by字段只被索引了前缀 ,key idx_col(col(10))
  15. select * from t1 order by col ;
  16. 8 对于还有join的关联查询,排序字段并非全部来自于第一个表,使用explain 查看执行计划第一个表 type 值不是const 。

当无法避免排序操作时,又该如何来优化呢?很显然,优先选择using index的排序方式,在无法满足利用索引排序的情况下,尽可能让 MySQL 选择使用第二种单路算法来进行排序。这样可以减少大量的随机IO操作,很大幅度地提高排序的效率。 1 加大 max_length_for_sort_data 参数的设置 在 MySQL 中,决定使用老式排序算法还是改进版排序算法是通过参数max_length_for_sort_data来决定的。当所有返回字段的最大长度小于这个参数值时,MySQL 就会选择改进后的排序算法,反之,则选择老式的算法。所以,如果有充足的内存让MySQL 存放须要返回的非排序字段,就可以加大这个参数的值来让 MySQL 选择使用改进版的排序算法。 2 去掉不必要的返回字段 当内存不是很充裕时,不能简单地通过强行加大上面的参数来强迫 MySQL 去使用改进版的排序算法,否则可能会造成 MySQL 不得不将数据分成很多段,然后进行排序,这样可能会得不偿失。此时就须要去掉不必要的返回字段,让返回结果长度适应 max_length_for_sort_data 参数的限制。 同时也要规范MySQL开发规范,尽量避免大字段。当有select 查询列含有大字段blob或者text 的时候,MySQL 会选择常规排序。 " algorithm to use. It normally uses the modified algorithm except when or columns are involved, in which case it uses the original algorithm." 3 增大 sort_buffer_size 参数设置 这个值如果过小的话,再加上你一次返回的条数过多,那么很可能就会分很多次进行排序,然后最后将每次的排序结果再串联起来,这样就会更慢,增大 sort_buffer_size 并不是为了让 MySQL选择改进版的排序算法,而是为了让MySQL尽量减少在排序过程中对须要排序的数据进行分段,因为分段会造成 MySQL 不得不使用临时表来进行交换排序。但是这个值不是越大越好: 1 sort_buffer_size 是一个connection级参数,在每个connection第一次需要使用这个buffer的时候,一次性分配设置的内存。 2 sort_buffer_size 并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。 3 据说sort_buffer_size 超过256Kb的时候,就会使用mmap() 而不是 malloc() 来进行内存分配,导致效率降低。 四 参考文章 [1] MySQL order by 调优官方文档 [2] MySQL排序原理与案例分析 [3] 淘宝MySQL 月报 本文的原理分析部分 采取偷懒策略,直接从我的前同事 雁闲 的博客[2]摘抄了,强烈安利 雁闲的 博客 ( http://www.cnblogs.com/cchust/ ),MySQL 源码研究者,精通MySQL 业务和底层运维。

原文发表时间:2017-11-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏性能与架构

Mysql 索引与排序

image.png sql 中 order by 排序可能发生2种情况: 1)对应覆盖索引,直接在索引上查询时,就是有序的,不需要另外处理排序 2)没有使用到索...

33860
来自专栏机器学习从入门到成神

判断单链表是否有环的两种方法

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/sinat_35512245/articl...

1.4K10
来自专栏瓜大三哥

HLS Lesson17-数组优化:数组映射和重组

c程序中有多个比较小的数组,可以映射为一个大的数组,而这个大的数组会消耗Block RAM或者UltraRAM,从而降低资源的用量。分为横向映射和纵向映射。 1...

26290
来自专栏数据和云

DB2 Vs MySQL系列 | MySQL与DB2的数据类型对比

随着MySQL数据库的应用越来越广泛,DB2向MySQL数据库的迁移需求也越来越多。进行数据库之间迁移的时候,首先遇到的并且也是最基本最重要的就是两种数据库数据...

38860
来自专栏Python攻城狮

MySQL高级1.mysql高级3.内置函数4.时间与字符串的相互转换

要求:表的类型必须是innodb或bdb类型(表的默认类型就是innodb),才可以对此表使用事务

19410
来自专栏Python

表的数据类型

一 介绍 存储引擎决定了表的类型,而表内存放的数据也要有不同的类型,每种数据类型都有自己的宽度,但宽度是可选的 详细参考: http://www.runoob....

21870
来自专栏JavaEE

mybatis的association以及collection的用法association:一对一关联(has one)collection:一对多关联(has many)

64280
来自专栏Java帮帮-微信公众号-技术文章全总结

Oracle应用实战六——函数+集合

函数 字符函数 接收字符输入返回字符或者数值,dual是伪表 1. 把小写的字符转换成大小的字符 upper('smith') ? 2. 把大写字符变成小写字符...

31050
来自专栏技术专栏

hive 中 统计某字段json数组中每个value出现的次数

需要将json数组里的qd_title都提取出来转换成hive中的array数组。下面介绍两种方法

1.1K30
来自专栏机器学习入门

LWC 56:718. Maximum Length of Repeated Subarray

LWC 56:718. Maximum Length of Repeated Subarray 传送门:718. Maximum Length of Repea...

22060

扫码关注云+社区

领取腾讯云代金券