MySQL order by原理以及优化？这篇来给你逐步解析

沙龙晃荡 | 去哪儿、陌陌、ThoughtWorks在主动化运维中的实践！10.28不见不散！

MySQL order by道理以及优化？这篇来给你慢慢解析

一简介

偏向于营业的 (MySQL)DBA 或者营业的开辟者来说，order by 排序是一个常见的营业功能，将结不雅根据指定的字段排序，知足前端展示的需求。然而排序操作也是经常出现慢萌芽排行榜的座上宾。本文将大年夜道理和实际案例优化，order by 应用限制等几个方面来慢慢懂得 order by 排序。

以下种类的萌芽是可以应用到索引 idx_kp1_kp2 的

二道理

在懂得 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)

在 MySQL 中, 决定应用老式排序算法照样改进版排序算法是经由过程参数 max_length_for_sort_data 来决定的。当所有返回字段的最大年夜长度小于这个参数值时, MySQL 就会选择改进后的排序算法, 反之, 则选择老式的算法。所以, 如不雅有充分的内存让 MySQL 存放须要返回的非排序字段, 就可以加大年夜这个参数的值来让 MySQL 选择应用改进版的排序算法。

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 优化排序

create table t1( 
  id int not null primary key , 
  key_part1 int(10) not null, 
  key_part2 varchar(10) not null default '', 
  key_part3 
  key idx_kp1_kp2(key_part1,key_part2,key_part4)， 
  key idx_kp3(id) 
) engine=innodb default charset=utf8

惯例排序方法除了排序本身，还须要额外两次 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 优先队列排序

[3] 淘宝 MySQL 月报

为了获得最终的排序结不雅，无论如何，我们都须要将所有知足前提的记录进行排序才能返回。那么相对于优化排序方法，是否还有优化空间呢?5.6 版本针对 Order by limit M，N 语句，在空间层面做了优化，参加了一种新的排序方法: 优先队列，这种方法采取堆排序实现。堆排序算法特点正好可以解 limit M，N 这类排序的问题，固然仍然须要所有元素介入排序，然则只须要 M+N 个元组的 sort buffer 空间即可，对于 M，N 很小的场景，根本不会因为 sort buffer 不敷而导致须要临时文件进行归并排序的问题。对于升序，采取大年夜顶堆，最终堆中的元素构成了最小的 N 个元素，对于降序，采取小顶堆，最终堆中的元素构成了最大年夜的 N 的元素。

1/3 1 2 3 下一页尾页

　　推荐阅读

　　iOS自定义启动界面附代码

沙龙晃荡 | 去哪儿、陌陌、ThoughtWorks在主动化运维中的实践！10.28不见不散！简介：比来在重构app，原app用的是xcode自带的启动图设置。但相对来说自定义启动图可扩大性更强一点，今天花>>>详细阅读

本文标题：MySQL order by原理以及优化？这篇来给你逐步解析

地址：http://www.17bianji.com/lsqh/38202.html

1/2 1