16“order by”是怎么工作笔记

最新推荐文章于 2024-09-26 21:04:50 发布

陈镇坤27

最新推荐文章于 2024-09-26 21:04:50 发布

阅读量258

点赞数

分类专栏： MySQL——源《丁磊MySQL45讲》文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/weixin_45528105/article/details/121391990

版权

MySQL——源《丁磊MySQL45讲》专栏收录该内容

28 篇文章 1 订阅

订阅专栏

order by工作原理

author：陈镇坤27

创建时间：2021年11月18日02:25:53

编辑时间：2021年11月18日16:29:43、2021年11月19日14:45:56、2021年12月9日11:31:28

转载请注明出处

文章目录

order by工作原理

——————————————————————————————.

问：查询语句进行排序，会用到哪几种排序规则？

答：如果排序字段有索引，则直接查出即可，否则执行全字段排序或rowId排序。

全字段排序

问：什么是全字段排序？什么情况发生。

答：SQL查询语句的排序，需要查询的所有字段参与。

当查询语句需要排序，排序字段上没有索引可以充分利用并且查询的数据行长度不超过设置值时，进行全字段排序。

问：怎么判断是一个查询语句是全字段排序。

答：排序无法利用上索引，并且查询的行长度不超过设置的值时，会是全字段排序。

查询全字段排序允许的最长行数据长度命令为：

show variables like  '%max_length_for_sort_data%';

问：全字段排序是如何执行的？

答：MySQL会给每个查询线程在server层分配一块用于排序的内存，称为“sort_buffer”（默认256k大小）。

首先，在server层初始化sort_buffer，并预存需要查询的列字段。

然后，每次从主键索引树中获取行数据时，将行对应目标字段值存放到sort_buffer当中。

之后，符合条件的索引树的行数据检索完毕后，开始对sort_buffer进行快速排序（采用快速排序算法）

最后将排序结果的前N行返回给客户端。

问：怎么查看全字段排序的详细过程？

答：

-- 先开启优化追踪表
set optimizer_trace = 'enabled=on'


-- 执行查询命令
XXX

-- 查询优化追踪表
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

下图截选日志打印的部分结果：

在这里插入图片描述

其中，examined_rows表示需要检查的总数据量（不是指sort_buffer有多少数据），number_or_temp_files表示外部文件数量，主要通是当参与排序的数据大小超过sort_buffer_size，要借助磁盘进行归并排序——拆分多个文件，各文件进行排序，再全部整合起来排序。

PS:

show variables like '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size        | 262144  |
+-------------------------+---------+

可以检索Innodb_rows_read来判断Innodb扫描的行数（注意：由于OPTIMIZER_TRACE查询本身是从表（磁盘临时表）中获取数据，加上internal_tmp_disk_storage_engine默认引擎是Innodb，那得到的结果会+1，解决方法是将默认临时表引擎设置为MyINSAM引擎）

select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

rowid排序

问：什么是rowId排序？

答：MySQL在sort_buffer中初始化参与排序的字段和主键Id，然后在引擎返回的行数据中，只取排序的字段和主键值存入sort_buffer，等所有满足条件的数据存入完毕后，再对排序字段进行快速排序，并返回语句需要（limit）的Id数据，然后依次根据Id再查主键索引树，并将id对应的数据直接返回给客户端（此时不需要再将数据缓存到内存）。