0


一篇文章搞懂MySQL的order by

准备工作!

1.本文章MySQL使用的是5.7,引擎使用的是innodb
2. 使用的表结构(user)

3.MySQL配置文件sort_buffer大小
在这里插入图片描述

1. 全字段排序

SQL语句

> explain select first_name, last_name,score,copy_id 
> from user 
> where first_name='王'> order by copy_id limit 10000;

执行结果
在这里插入图片描述
possible_keys: 可能用到的索引
key:实际用到的索引
Extra中的Using filesort 表示需要排序,

运行流程
首先我建立了一个first_name索引,所以在上图可以看到使用了first_name索引。
SQL语句执行流程:

MySQL 会给每个线程分配一块内存用于排序,称为 sort_buffer

1.初始化 sort_buffer,确定放入 first_name, last_name,score,copy_id 这四个字段;

2.从索引 first_name找到第一个满足first_name=‘王’ 条件的主键 id;

3.到主键 id 索引取出整行,first_name, last_name,score,copy_id 这四个字段,存入sort_buffer 中;

4.从索引 first_name取下一个记录的主键 id;

5.重复步骤 3、4 直到 first_name的值不满足查询条件为止;

6.对 sort_buffer 中的数据按照字段 copy_id 做快速排序;按照排序结果取前 10000 行返回给客户端。

sort_buffer

如上就是全字段排序,通过sort_buffer做块速排序。但是sort_buffer它也有一定的大小,就和上面一样,我把我的sort_buffer_size 设置成了32kb。

sort_buffer_size:就是 MySQL 为排序开辟的内存(sort_buffer)的大小。如果要排序的数据量小于 sort_buffer_size,排序就在内存中完成。但如果排序数据量太大,内存放不下,则不得不利用磁盘临时文件辅助排序。

如何确定是否使用了磁盘文件来进行排序呢?可以使用以下这几个命令

## 打开optimizer_trace,开启统计
set optimizer_trace = "enabled=on";
## 执行SQL语句
 select first_name, last_name,score,copy_id from user where first_name='王' order by copy_id limit 10000; 
## 查询输出的统计信息
select * from information_schema.optimizer_trace 

运行结果

“filesort_execution”: [
],
“filesort_summary”: {
“rows”: 5851, // 预计扫描行数
“examined_rows”: 5851, // 扫描行数
“number_of_tmp_files”: 7, // 临时文件数量
“sort_buffer_size”: 32728,
“sort_mode”: “<sort_key, packed_additional_fields>” //排序过程对字符串做了“紧凑”处理
}

number_of_tmp_files 表示的是,排序过程中使用的临时文件数。你一定奇怪,为什么需要 7 个文件?内存放不下时,就需要使用外部排序,外部排序一般使用归并排序算法。可以这么简单理解,MySQL 将需要排序的数据分成 7份,每一份单独排序后存在这些临时文件中。然后把这7个有序文件再合并成一个有序的大文件。

2. rowid 排序

在上面这个算法过程里面,只对原表的数据读了一遍,剩下的操作都是在 sort_buffer 和临时文件中执行的。但这个算法有一个问题,就是如果查询要返回的字段很多的话,那么 sort_buffer 里面要放的字段数太多,这样内存里能够同时放下的行数很少,要分成很多个临时文件,排序的性能会很差。

比如下面的这个一个SQL,select后面要查询很多字段。

select q,w,e,r,t,y,u,i,o,a,s,d,g,h,j,k
from test 
where A='22' order by b limit 10000;

此时如果采用全字段排序,就会导致sort_buffer_size中能够存放的数据行会很少,导致使用大量的临时文件来做归并排序。 所以这样的情况下,全字段排序对这样的查询没有什么优势。

max_length_for_sort_data

max_length_for_sort_data表示MySQL用于排序行数据的长度的一个参数,如果单行的长度超过这个值,MySQL 就认为单行太大,就换rowid 排序

怎么计算单行长度?
单行长度是根据你查询的数据大小
比如我的数据库表结构 first_name字段varchar(10)、 last_name字段varchar(10)
如果我 SQL查询first_name,last_name两个字段,那么我的行数据大小就是20.

// 获取max_length_for_sort_data大小
show variables like 'max_length_for_sort_data';// 设置max_length_for_sort_data大小
SET max_length_for_sort_data =10;

在上图中,我把max_length_for_sort_data 设置成10让它走rowid 排序,执行同样的SQL语句

## 打开optimizer_trace,开启统计
set optimizer_trace ="enabled=on";
## 执行SQL语句
 select first_name, last_name,score,copy_id from user where first_name='王' order by copy_id limit 10000; 
## 查询输出的统计信息
 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

“filesort_execution”: [
],
“filesort_summary”: {
“rows”: 5851, // 预计扫描行数
“examined_rows”: 5851, // 实际扫描行数
“number_of_tmp_files”: 3, // 临时文件数量
“sort_buffer_size”: 32768,
“sort_mode”: “<sort_key, rowid>” // 使用rowid进行排序
}

大家仔细点可以发现,同样是扫描5851行,这里的临时文件是3个,上面使用的临时文件是7个,为什么会这样?
SQL语句执行流程:

因为上面全字段排序sort_buffer确定的是first_name, last_name,score,copy_id四个字段,而这里rowid排序确定的是copy_id 和 id,大大的减少sort_buffer的大小。

1.初始化 sort_buffer,确定放入两个字段,即 copy_id 和 id;

2.从索引 first_name找到第一个满足 first_name='王’条件的主键 id;

3.到主键 id 索引取出整行,取 copy_id 、id 这两个字段,存入 sort_buffer 中;

4.从索引 first_name 取下一个记录的主键 id;

5.重复步骤 3、4 直到不满足 first_name='王’条件为止;

6.对 sort_buffer 中的数据按照字段 copy_id 进行排序;

但是同样的操作rowid 排序会多一次回表操作。

7.遍历排序结果,并按照 id 的值回到原表中取出first_name, last_name,score,copy_id四个字段返回给客户端。

3.使用order by一定就会排序吗

使用order by不一定会进行排序。如果我们查询出来的数据是有序的,那是不是就不用排序了呢?
首先我建立了一个联合索引 first_name(first_name, copy_id)

 alter table test add index first_name(first_name, copy_id);

大家也知道,MySQL的InnoDB引擎使用的数据结构是B+树,而B+树它支持范围搜索,并且它的叶子节点也是有序的。

EXPLAIN SELECT
    first_name,last_name,score,copy_id 
FROM  USER 
WHERE
    first_name ='王' 
ORDER BY copy_id LIMIT 10000;

执行流程

1.从索引 (first_name, copy_id) 找到第一个满足 first_name = '王’条件的主键 id;

2.到主键 id 索引取出整行,取 first_name,last_name,score,copy_id 四个字段的值,作为结果集的一部分直接返回;

3.从索引 (first_name, copy_id) 取下一个记录主键 id;

4.重复步骤 2、3,直到查到第 10000 条记录,或者是不满足 first_name = '王’条件时循环结束。

运行结果,未进行排序
在这里插入图片描述
如果想在进去一步优化,那就可以在使用到我们的覆盖索引

覆盖索引是指,索引上的信息足够满足查询请求,不需要再回到主键索引上去取数据。也就是不用在回表

EXPLAIN 
select first_name, copy_id 
from user 
where first_name='王' order by copy_id limit 10000;

执行流程

1.从索引 (first_name, copy_id) 找到第一个满足 first_name = '王’条件的主键 id;

2.从索引 (first_name, copy_id) 取下一个记录主键 id;

3.直到查到第 10000 条记录,或者是不满足 first_name = '王’是循环结束,并返回索引树上记录first_name和copy_id的值。

运行结果未进行排序,“Using index”使用了覆盖索引
在这里插入图片描述

总结:
1.如果 MySQL 实在是担心排序内存太小,会影响排序效率,才会采用 rowid 排序算法,这样排序过程中一次可以排序更多行,但是需要再回到原表去取数据。

2.如果 MySQL 认为内存足够大,会优先选择全字段排序,把需要的字段都放到 sort_buffer 中,这样排序后就会直接从内存里面返回查询结果了,不用再回到原表去取数据。

标签: mysql 数据库 sql

本文转载自: https://blog.csdn.net/qq_48157004/article/details/127376852
版权归原作者 又 欠 所有, 如有侵权,请联系我们删除。

“一篇文章搞懂MySQL的order by”的评论:

还没有评论