189 8069 5689

【MySQL】orderby原理以及优化

一 简介
   偏向于业务的(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 会选择常规排序。
"The optimizer selects which filesort algorithm to use. It normally uses the modified algorithm except when BLOB or TEXT 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 超过2M的时候,就会使用mmap() 而不是 malloc() 来进行内存分配,导致效率降低。

四  参考文章
[1]MySQL order by 调优官方文档 
[2]MySQL排序原理与案例分析   
[3]淘宝MySQL 月报 
本文的原理分析部分 采取偷懒策略，直接从我的前同事 雁闲 的博客[2]摘抄了，强烈安利 雁闲的 博客 ，MySQL 源码研究者，精通MySQL 业务和底层运维。

当前名称：【MySQL】orderby原理以及优化
网页链接：http://jkwzsj.com/article/ppjjod.html

其他资讯