ORDER BY调优的核心原理，原则是利用索引的有序性跳过排序环节

关于ORDER BY语句的一些尝试

我们使用employees表进行尝试，索引情况如下

指定sql先走某个索引再走某个索引 orderby走索引_mysql


在执行计划的结果中，Extra里如果存在，Using filesort则表示，排序没有使用到索引。

explain
select *
from employees
order by first_name,last_name;

结果

指定sql先走某个索引再走某个索引 orderby走索引_字段_02


并没有用到索引，发生了全表扫描

explain
select *
from employees
order by first_name,last_name
limit 10;

结果

指定sql先走某个索引再走某个索引 orderby走索引_指定sql先走某个索引再走某个索引_03


这次的查询就用到了索引。为什么一次是ALL，一次是index呢？

因为第一次相当于对整张表进行排序的，排序是基于成本计算的，在优化器发现全表扫描开销更低时，会直接使用全表扫描。而第二次是仅仅对前10条数据进行排序，扫描索引的成本要小于扫面全表，所以用到了索引。

----------------------------------------------------------------------------------------------------------------->

explain
select *
from employees
where first_name = 'Bader'
order by last_name;

结果

指定sql先走某个索引再走某个索引 orderby走索引_指定sql先走某个索引再走某个索引_04


这句SQL是用到了索引排序的，当执行查询时，查找出来的数据为[‘Bader’，last_name[i]，emp_no]，因为索引是有序的，'Bader’是确定的，那么数据已经按照last_name排好序了，就跳过了排序的环节。

----------------------------------------------------------------------------------------------------------------->

explain
select *
from employees
where first_name < 'Bader'
order by first_name;

结果

指定sql先走某个索引再走某个索引 orderby走索引_字段_05


根据执行结果是使用了索引的，因为在执行查询语句时，查找出来的数据为[first_name，last_name，emp_no]，这一部分数据已经是按照first_name排好序的，所以不需要再次进行排序了。

----------------------------------------------------------------------------------------------------------------->

explain
select *
from employees
where first_name = 'Bader'
    and last_name > 'Peng'
order by last_name;

结果

指定sql先走某个索引再走某个索引 orderby走索引_mysql_06


跟上面的同理，因为在执行查询语句时，查找出来的数据为[Bader，last_name[i]，emp_no]，这一部分数据已经是按照last_name排好序的，所以不需要再次进行排序了。

----------------------------------------------------------------------------------------------------------------->

explain
select *
from employees
order by first_name,emp_no limit 10;

结果

指定sql先走某个索引再走某个索引 orderby走索引_指定sql先走某个索引再走某个索引_07


根据执行结果 ，该语句没有用到索引，因为两个排序字段存在于不同的两个索引中，会先按first_name进行排序，再将相同first_name的数据按照emp_no进行排序。

----------------------------------------------------------------------------------------------------------------->

explain
select *
from employees
order by first_name desc ,last_name asc limit 10;

结果

指定sql先走某个索引再走某个索引 orderby走索引_指定sql先走某个索引再走某个索引_08


因为索引中的两个字段，在进行排序中的升降序不一致，所以无法使用索引。

----------------------------------------------------------------------------------------------------------------->

explain
select *
from employees
where first_name < 'Bader'
order by last_name limit 10;

结果

指定sql先走某个索引再走某个索引 orderby走索引_算法_09


根据结果得知，在进行查询时使用了索引，但在排序时使用的是Using filesort。说明排序时没有用到索引。组合索引中part1范围查询，使用part2进行排序是无法使用索引排序的。

排序模式

Using filesort排序原理，目前MySQL使用了三种排序模式

模式一：rowid排序（常规排序）

排序过程

  1. 从表中获取满足where条件的数据。
  2. 对于每条记录，将记录的主键及排序字段（id,order_column）取出放入sort buffer（由sort_buffer_size控制大小）。
  3. 如果sort buffer能存放所有满足条件的（id,order_column），则进行排序；否则，当sort buffer存满后，会将sort buffer中的数据排序并存放到临时文件中。
  • 在没有产生临时文件时，在内存中使用快速排序算法
  • 如果产生了临时文件，则需要利用归并排序算法，从而保证记录有序
  1. 扫描排序好的（id,order_column）数据，并利用id去取select需要返回的其他字段。
  2. 返回结果集。

排序特点

  • 看sort buffer是否能存放查询出来的所有的结果集，如果不满足，就会差生临时文件
  • 一次排序需要两次IO
  • 第一次，把查询出来的（id,order_column）结果集放入sort buffer中；第二次，通过id去获取需要返回的其他字段。由于返回结果是按照order_column进行排序的，所以主键id是乱序的，会存在随机IO的问题。之前文中提到，在用主键id取值前，会按照主键id进行排序，并放入一个缓存中，该缓存大小是由read_rnd_buffer_size控制，接着再去取记录，从而把随机IO转换成顺序IO。

模式二：全字段排序（优化排序）

排序过程

跟第一种模式相比，有几点不同

  • 直接取出表中需要的所有字段，放到sort buffer种
  • 由于sort buffer已经包含了查询需要的所有的字段，因此sort buffer种排序完成后直接返回结果集

全字段排序 vs rowid排序

  • 优点：性能的提升，无需两次IO，因为全字段排序已经将需要的所有字段存储到了sort buffer种，无需再次用主键id去表中获取
  • 缺点：由于全字段排序会将需要的所有的字段放入sort buffer中，所以占用空间比较大，如果sort buffer不够大，那么很容易产生临时文件

排序算法的选择

  • max_length_for_sort_data：当OEDER BY中出现的字段的总长度小于该值，使用全字段排序，反之则使用rowid排序。

模式三：打包字段排序

  • MySQL5.7引入
  • 与模式二工作原理一致，不同点在于会将字段紧密的排列在一起，而不是固定长度的空间。
  • 例如：一个字段定义为VARCHAR(32)，值为’yes’；在不打包的情况下占用32字节，打包的情况下2+3字节。

参数汇总

变量

作用

sort_buffer_size

指定sort buffer的大小

max_length_for_fort_data

当ORDER BY中出现字段的总长度小于该值时使用全字段排序，反之使用rowid排序

read_rnd_buffer_size

按照主键排序后存放的缓存区大小

使用optimizer_trace分析排序过程

explain展示的排序方式很有限，仅仅是Using filesort，如果我们想了解更多的细节就需要使用optimizer_trace进行分析了。
以下面语句为例：

select *
from employees
where first_name < 'Bader'
order by last_name;

执行

SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
SET optimizer_trace_offset=-30,optimizer_trace_limit=30;

开启OPTIMIZER_TRACE，执行示例SQL语句，再次执行

select * from information_schema.OPTIMIZER_TRACE where QUERY like '%Bader%';

获取分析结果，将trace字段的内容复制出来进行分析；
我们主要关注的是filesort_summary，

“filesort_summary”: {
 “memory_available”: 262144,
 “key_size”: 265,
 “row_size”: 399,
 “max_rows_per_buffer”: 502,
 “num_rows_estimate”: 927744,
 “num_rows_found”: 22287,
 “num_initial_chunks_spilled_to_disk”: 0,
 “peak_memory_used”: 204314,
 “sort_algorithm”: “std::sort”,
 “unpacked_addon_fields”: “using_priority_queue”,
 “sort_mode”: “<varlen_sort_key, additional_fields>”
 }

其相关字段解读如下：

  • memory_available：可用内存，其实就是fort_buffer_size设置的值
  • num_rows_found：有多少条数据参与排序，越小越好
  • num_initial_chunks_spilled_to_disk：产生了几个临时文件，0表示完全基于内存排序
  • sort_mode
  • <varlen_sort_key,rowid>：使用了rowid排序模式
  • <varlen_sort_key, additional_fields>：使用了全字段排序模式
  • <varlen_sort_key, packed_additional_fields>：使用了打包字段排序模式

如何调优ORDER BY

  • 利用索引，防止filesort发生
  • 如果发生了filesort，且无法避免，就要对filesort进行优化

如何调优filesort

  • 调大sort_buffer_size，减少/避免临时文件的产生，从而进行的归并操作
  • 当optimizer_trace的结果中 num_initial_chunks_spilled_to_disk的值较大时，需要调整
  • show status like ‘%sort_merge_passes%’;查看发生归并操作的次数
  • 调大read_rnd_buffer_size，让一次顺序IO返回更多的结果
  • 设置合理的max_length_for_sort_data的值