前言
每次业务功能中总有按时间排序,或者按其他字段排序的需求,一想到排序我们就会想到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. 全字段排序(单路)
步骤:
-
初始化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
总结:
我们发现与全字段排序不同之处:因为内存不足,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行
-
创建临时表,建立字段word和id
-
rank()随机数和word字段,存入临时表,扫描10000行
-
初始化sort_buffer从临时表中读取数据,在sort_buffer中进行排序
-
取出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就会转换成磁盘临时表