MySQL笔记 | 9.ORDER BY是如何工作的？

前言

每次业务功能中总有按时间排序，或者按其他字段排序的需求，一想到排序我们就会想到MySQL中的order by，那在使用的过程中，我们是否会存在不合理的使用导致查询的速度下降呢？

正文

为了避免全表查询，我们通常是在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

1. 全字段排序（单路）

步骤：

1. 初始化sort_buffer，将t.passenger_uuid和order_no放入

2. 从索引中找到满足的条件的主键ID,获取整行数据，存入sort_buffer中

3. 对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

总结：
我们发现与全字段排序不同之处：因为内存不足，rowid排序中存入sort_buffer的字段可能就是值存入order_no和Id，遍历排序结果，按照id取出其他的字段。

MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问

3. 实践

通过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行

1. 创建临时表，建立字段word和id

2. rank()随机数和word字段，存入临时表，扫描10000行

3. 初始化sort_buffer从临时表中读取数据，在sort_buffer中进行排序

4. 取出limit 3,依次从临时表读取word，访问了表的三行数据，扫描3行。

order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。

优化器优先考虑，排序的行越小越好。

4. 什么时候会转成磁盘临时表？

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就会转换成磁盘临时表