MySQL-排序优化 | HoleLin's Blog

参考文献

`Order By`实现原理

利用有序索引获取有序数据
- 在使用explain分析查询的时候,利用有序索引获取有序数据显示Using index
文件排序
- 在使用explain分析查询的时候,利用有序索引获取有序数据显示Using filesort

文件排序算法

回表排序模式：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息,然后在sort buffer中进行排序.
不回表排序模式：是一次性取出满足条件行的所有字段,然后在sort buffer中进行排序.

MySQL主要通过比较我们所设定的系统参数max_length_for_sort_data的大小和Query语句所取出的字段类型大小总和来判定需要使用哪一种排序算法.

如果max_length_for_sort_data更大,则使用第二种优化后的算法,反之使用第一种算法.

所以如果希望ORDER BY操作的效率尽可能的高,一定要注意max_length_for_sort_data 数的设置

mysql>  show variables like 'max_length_for_sort_data';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| max_length_for_sort_data | 4096  |
+--------------------------+-------+
1 row in set (0.01 sec)

回表排序模式

CREATE TABLE emp (
	id INT NOT NULL auto_increment,
	NAME VARCHAR ( 20 ),
	title_id INT,
	PRIMARY KEY ( id ),
KEY ( title_id ) 
);

当我们在执行select * from emp order by title_id 的时候, 需要回表查询, 对于主键索引表,需要查询两次, 我们称之为"双路排序",主要处理流程
1. 加载数据的排序字段title_id和业务的row_id(业务主键)到sort_buffer的内存区域
2. 对内存中的数据进行局部排序(快速排序)
3. 把数据写入到临时文件中
4. 清空sort_buffer中的数据
5. 重新循环执行1-4的操作, 知道所有符合条件的记录都扫描完成, 会生成多个临时文件
6. 对临时文件进行归并排序, 排序后只有title_id, row_id
7. 根据row_id从主键索引进行回表查询所有的记录
8. 返回结果集,排序后的数据

不回表排序模式

当我们在执行select title_id from employee order by title_id 的时候, 我们不需要进行回表查询, 对于主键索引表,只需要查询1次, 我们称之为"单路排序",主要处理流程
1. 加载数据的排序字段title_id和业务的row_id(业务主键)和其他所有需要的字段(根据select确定)到sort_buffer的内存区域
2. 对内存中的数据进行局部排序(快速排序)
3. 把数据写入到临时文件中
4. 清空sort_buffer中的数据
5. 重新循环执行1-4的操作, 知道所有符合条件的记录都扫描完成, 会生成多个临时文件
6. 对临时文件进行归并排序, 排序后有所有需要的字段
7. 读取归并文件, 返回结果集

总结

对于排序内存区域的大小参数: sort_buffer_size 默认认是262k

mysql> show VARIABLES like '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size        | 262144  |
+-------------------------+---------+
3 rows in set (0.08 sec)

排序究竟是走单路排序还是双路排序:
1. 如果查询的字段, 只有主键和排序字段, 直接使用单路排序即可
2. 如果查询的字段有其他字段, 则根据参数max_length_for_sort_data 决定, 如果查询的字段长度不超过改参数值, 就使用单路排序,否则, 采用双路排序

单双路排序验证方式

单路排序:用trace工具可以看到sort_mode信息里显示<sort_key, additional_fields>或者< sort_key, packed_additional_fields>；
双路排序(回表查询):用trace工具可以看到sort_mode信息里显示<sort_key, rowid>

排序的优化

尽量保证使用单路排序
1. 查询列, 排序列都是索引的索引列
2. 适当调整max_length_for_sort_data 保证单路排序
去掉不必要的返回字段
适当增加sort_buffer的大小

`Order By`优化

避免 `Using filesort`

使用索引覆盖:要想在排序时使用索引,首先需要发生索引覆盖.索引覆盖指的是在查询时只使用索引,而不需要访问数据表中的数据,从而避免 Using filesort.因此,在进行ORDER BY优化时,需要根据具体的查询场景来创建适当的索引,以实现索引覆盖.
只有当ORDER BY中所有的列必须包含在相同的索引,并且索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列的排序方向（升序或者降序）一样才有,（混合使用ASC模式和DESC模式则不使用索引
WHERE语句与ORDER BY语句组合满足最左前缀
如果查询联接了多个表,只有在ORDER BY子句的所有列引用的是第一个表的列才可以

使用`Using filesort`的情况

WHERE语句与ORDER BY语句使用了不同的索引

1	SELECT * FROM orders WHERE customer_id = 100 ORDER BY order_date;

SELECT的行数过多且没有使用覆盖索引
1
SELECT * FROM orders ORDER BY order_date;
ORDER BY中的列不包含在相同的索引,也就是使用了不同的索引
1
SELECT * FROM orders WHERE customer_id = 100 ORDER BY order_date,name;

对索引列同时使用了ASC和DESC

1	SELECT * FROM orders WHERE customer_id = 100 ORDER BY order_date ASC, order_id DESC;

WHERE语句或者ORDER BY语句中索引列使用了表达式,包括函数表达式
1
SELECT * FROM orders WHERE YEAR(order_date) = 2022 ORDER BY order_id;

WHERE语句与ORDER BY语句组合满足最左前缀,但WHERE语句中使用了条件查询

1	SELECT * FROM orders WHERE customer_id = 100 AND order_date > '2022-01-01' ORDER BY order_id,name;

当使用LEFT JOIN,使用右边的表字段排序

会排序的代表性的运算

GROUP BY:如果使用了 GROUP BY 关键字对结果进行分组,MySQL 会对分组后的结果按照分组字段进行排序.
DISTINCT:如果使用了 DISTINCT 关键字去重,MySQL 会对去重后的结果进行排序.
UNION:如果使用了 UNION 连接多个查询结果集,MySQL 会对所有结果集进行排序.
ORDER BY:如果使用了 ORDER BY 关键字对结果进行排序,MySQL 会按照指定的排序字段进行排序.如果没有指定排序方式,MySQL 默认使用升序排序.
需要注意的是,如果查询结果没有明确指定排序顺序,MySQL 不保证查询结果的顺序.因此,如果需要按照特定顺序返回结果,必须使用 ORDER BY 进行排序.