两个核心参数

sort_buffer_size 决定内排，外排。内排就是走内存，外排就是采用归并排序走磁盘。

max_length_for_sort_data 决定 全字段排序还是，rowid排序。

全字段排序

字段都放到 sort_buffer 中，排序后就会直接从内存里面返回查询结果了

Rowid排序

内存放rowid与排序字段，排序后，再从库中找数据，拼接返回。

 

优化手段覆盖索引

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据.

例子

SELECT order_id,pay_date FROM orders_detail WHERE order_id='1001' ORDER BY pay_date asc

用到了filesort，也就是需要排序。

调整索引

ALTER TABLE `orders_detail` DROP INDEX `order_id`,ADD INDEX `order_id` (`order_id`, `pay_date`);

之后

explain
SELECT order_id,pay_date FROM orders_detail WHERE order_id='1001' ORDER BY pay_date asc

没有用到filesort，因为复合索引，字段后是有序的

特殊情况 Order by+ Limit

Limit可能用到优先队列排序算法

例子：

  1. 开启优化追踪
SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=off;
SET optimizer_trace_offset=-30, optimizer_trace_limit=30;
  1. 查看字段索引
SHOW INDEX FROM oc_order_online WHERE COLUMN_NAME='order_name';

结果显示没有索引

  1. 执行order by+limit 查询语句
select * from `oc_order_online` order by `order_name` limit 20
  1. 查询优化追踪信息
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30

对应结果如下：

查询将红框中数据，粘贴到json.cn查看格式化数据，有如下片段

filesort_priority_queue_optimization  中的chosen：true表示使用了优先队列排序。

 

总结

  1. sort_buffer_size 决定内排，外排
  2. max_length_for_sort_data 决定 全字段排序还是，rowid排序
  3. 覆盖索引是一种优化手段
  4. Limit可能涉及优先队列排序