前言
大家好，我是小郭，每次业务功能中总有按操作时间排序，或者按其他字段排序的需求，一想到排序我们就会想到MySQL中的ORDER BY，那在使用的过程中，我们是否会存在不合理的使用导致查询的速度下降呢？
从Using filesort文件排序角度来看排序
为什么一个简单的ORDER BY却有这么大的魔力？
explain select t.passenger_uuid ,t.order_no from yy_order t order by t.order_no limit 1000;
explain分析结果
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 458420 | 100 | Using filesort |
filesort文件排序方式，利用trace工具可以分析
1. 全字段排序（单路）
步骤：
- 初始化sort_buffer，将t.passenger_uuid和order_no放入
- 从索引中找到满足的条件的主键ID,获取整行数据，存入sort_buffer中
- 对sort_buffer中的数据按照字段做快熟排序
在什么情况下会使用外部排序呢？
当排序的数量大于sort_buffer_size时候才会使用外部排序，需要临时表的辅助。
2. rowid排序（双路）
新的算法放入 sort_buffer 的字段，只有要排序的列（order_no）和主键 id。
遍历结果取前1000行数据，再按id查询所需要的字段
查询排序长度参数
show variables like '%max_length_for_sort_data%';
| Variable_name | Value |
| max_length_for_sort_data | 1024 |
字段长度之和小于max_length_for_sort_data 单路排序全字段放入内存中排序 否则 双路排序 rowid和排序字段放入sort_buffer
与全字段排序，不同的是因为内存不足，存入sort_buffer的字段可能就是值存入order_no和Id，遍历排序结果，按照id取出其他的字段。
从这里体现MySQL的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。
实践
通过order by 随机值触发临时表
-- 根据随机值进行排序 mysql> explain select word from words order by rand() limit 3; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | 1 | SIMPLE | words | NULL | ALL | NULL | NULL | NULL | NULL | 9980 | 100.00 | Using temporary; Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ 1 row in set, 1 warning (0.00 sec)
通过Extra可以看出使用了临时表
# Time: 2021-04-28T06:56:04.664998Z # User@Host: root[root] @ [172.17.0.1] Id: 10 # Query_time: 0.003869 Lock_time: 0.000053 Rows_sent: 3 Rows_examined: 10003 SET timestamp=1619592964; /* ApplicationName=DataGrip 2019.2.5 */ select word from words order by rand() limit 3;
一共扫描了10003行
- 创建临时表，建立字段word和id
- rank()随机数和word字段，存入临时表，扫描10000行
- 初始化sort_buffer从临时表中读取数据，在sort_buffer中进行排序
- 取出limit 3,依次从临时表读取word，访问了表的三行数据，扫描3行。
order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。
优化器优先考虑，排序的行越小越好。
什么时候会转成磁盘临时表？
mysql> show variables like '%tmp_table_size%'; +----------------+----------+ | Variable_name | Value | +----------------+----------+ | tmp_table_size | 16777216 | +----------------+----------+ 1 row in set (0.01 sec)
参数:tmp_table_size,默认16M,大于16M就会转换成磁盘临时表
总结
我们发现与全字段排序不同之处：因为内存不足，rowid排序中存入sort_buffer的字段可能就是值存入order_no和Id，遍历排序结果，按照id取出其他的字段。
MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问
