mysql中Order by 优化

最好做法：

利用索引避免排序，B+tree 索引本身的有序性，让mysql跳过排序过程。

排序方式

mysql排序模式1.-rowid排序（常规排序）

1.从表中获取满足where条件的记录

2.对于每条记录，将记录的主键及排序键(id、order_column)取出放入sort buffer(sort_buffer_size控制)

3.如果sort buffer能存放所有满足条件的(id、order_column)，则进行排序；否则sort buffer 满后，排序并写到临时文件。 排序算法：快速排序算法

4.若排序中产生了临时文件，需要利用归并排序算法，从而保证记录有序

5.循环执行上述过程，直到所有满足条件的记录全部参与排序

6.扫描排好序的(id、order_column)对，并利用id去取select需要返回的其它字段

7.返回结果集

rowid排序特点：

1).看sort buffer是否能存放结果集里面的所有(id、order_column)，如果不满足，就会产生临时文件
2)一次排序需要2次IO：
第二步中：把(id、order_column)扔到sort_buffer；
第六步中：通过id去获取需要返回的其它字段。由于返回结果是按照order_column排序的，所以id是乱序的，会存在随机io的问题。mysql内部针对这个情况做个优化：在用id取数据之前，会按照id排序并放到一个缓存里，这个缓存大小由read_rnd_buffer_size控制，接着再去取记录，从而把随机io转换成顺序IO

mysql排序模式2.-全字段排序（优化排序）

1.直接取出sql中需要的所有字段，放到sort buffer
2.由于sort buffer已经包含了查询需要的所有字段，因此在sort buffer中排序完成后可直接返回

全字段排序 VS rowid排序

1.优点：性能的提升，无需两次IO
2.缺点：一行数据占用的空间一般比rowid排序多；如果sort buffer比较小，容易导致临时文件

算法如何选择？

Max_length_for_sort_data（默认1024字节）：当order by sql中出现字段总长度小于该值，使用全字段排序，否则使用rowid排序

mysql排序模式3.-打包字段排序

1.mysql 5.7引入
2.全字段模式的优化，与全字段排序工作原理一样，但是将字段紧密的排列在一起，而不是使用固定长度空间
例子：varchar(255) 存储“yes”，不打包：255字节；打包：2 + 3字节（2个字节保存长度，3个字节保存”yes”）

OPTIMIZER_TRACE使用

1.开启OPTIMIZER_TRACE功能，并设置要展示的数据条目数：

set optimizer_trace="enabled=on" ,end_markers_in_json=on;
set optimizer_trace_offset=-30,optimizer_trace_limit=30;

2.发送你想要分析的sql语句，例如：

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

3.分析查询结果

select * from `information_schema`.optimizer_trace where query like '%Bader%';

得到如下结果：
分析trace结果：

Memory_available：可用内存，就是sort_buffer_size配置的值
Num_rows_found：有多少条数据参与排序，越小越好
Num_initial_chunks_spilled_to_disk：产生了几个临时文件，0表示完全基于内存排序
Sort_mode：packed_addition_fields代表打包排序；addition_fields 代表全字段排序

如何调优order by

1.利用索引，防止filesort的发生
2.如果发生了filesort，并且没办法避免，想办法优化filesort

调优方案：

1.调大sort_buffer_size，减少/避免临时文件、归并操作，怎么判定要调整这个值呢？

依据：
a)Optimizer trace中num_initial_chunks_spilled_to_disk的值，值大就可以调整
b)Sort_merge_passes变量的值，值越大，归并的次数就越大，就可以调整

2.调大read_rnd_buffer_size，让一次顺序IO返回的结果更多
3.设置合理的max_length_for_sort_data的值（慎重调整，不建议随意调整）
4.调小max_sort_length（排序时最多取多少字节）