MySQL-排序优化
参考文献
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
数的设置1
2
3
4
5
6
7
8mysql> show variables like 'max_length_for_sort_data';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| max_length_for_sort_data | 4096 |
+--------------------------+-------+
1 row in set (0.01 sec)
-
回表排序模式
1 | CREATE TABLE emp ( |
- 当我们在执行
select * from emp order by title_id
的时候, 需要回表查询, 对于主键索引表,需要查询两次, 我们称之为"双路排序",主要处理流程- 加载数据的排序字段
title_id
和业务的row_id
(业务主键)到sort_buffer
的内存区域 - 对内存中的数据进行局部排序(快速排序)
- 把数据写入到临时文件中
- 清空
sort_buffer
中的数据 - 重新循环执行1-4的操作, 知道所有符合条件的记录都扫描完成, 会生成多个临时文件
- 对临时文件进行归并排序, 排序后只有
title_id, row_id
- 根据
row_id
从主键索引进行回表查询所有的记录 - 返回结果集,排序后的数据
- 加载数据的排序字段
不回表排序模式
- 当我们在执行
select title_id from employee order by title_id
的时候, 我们不需要进行回表查询, 对于主键索引表,只需要查询1次, 我们称之为"单路排序",主要处理流程- 加载数据的排序字段
title_id
和业务的row_id
(业务主键)和其他所有需要的字段(根据select
确定)到sort_buffer
的内存区域 - 对内存中的数据进行局部排序(快速排序)
- 把数据写入到临时文件中
- 清空
sort_buffer
中的数据 - 重新循环执行1-4的操作, 知道所有符合条件的记录都扫描完成, 会生成多个临时文件
- 对临时文件进行归并排序, 排序后有所有需要的字段
- 读取归并文件, 返回结果集
- 加载数据的排序字段
总结
-
对于排序内存区域的大小参数:
sort_buffer_size
默认认是262k1
2
3
4
5
6
7
8
9mysql> 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) -
排序究竟是走单路排序还是双路排序:
-
如果查询的字段, 只有主键和排序字段, 直接使用单路排序即可
-
如果查询的字段有其他字段, 则根据参数
max_length_for_sort_data
决定, 如果查询的字段长度不超过改参数值, 就使用单路排序,否则, 采用双路排序
-
单双路排序验证方式
- 单路排序:用
trace
工具可以看到sort_mode
信息里显示<sort_key, additional_fields>
或者< sort_key, packed_additional_fields>
; - 双路排序(回表查询):用
trace
工具可以看到sort_mode
信息里显示<sort_key, rowid>
排序的优化
-
尽量保证使用单路排序
-
查询列, 排序列都是索引的索引列
-
适当调整
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
进行排序.