Loading [MathJax]/jax/output/CommonHTML/config.js
前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >MySQL ORDER BY 实现原理

MySQL ORDER BY 实现原理

作者头像
恋喵大鲤鱼
发布于 2024-01-26 02:37:26
发布于 2024-01-26 02:37:26
23800
代码可运行
举报
文章被收录于专栏:C/C++基础C/C++基础
运行总次数:0
代码可运行

1.何为 Sort Buffer?

假设有一张表 tb_user 表,表里有 5 个字段 id、name、age、city、created_at。

给定如下 SQL:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select * from tb_user where age=18 order by create_time desc;

上面这条SQL执行过程如下: 1.根据 SQL 条件过滤数据,这里会把 age=18 之外的数据先过滤掉。 2.把符合条件的数据放到 sort buffer 里(sort buffer 是在内存的)。 3.在 sort buffer 里根据 created_at 对数据进行排序。 4.返回客户端排完序的数据。

MySQL 会为每个查询线程分配一块内存,叫做 Sort Buffer,这块内存的作用是用来排序的。

2.Sort Buffer 空间不够怎么办?

Sort Buffer 大小由参数 sort_buffer_size 控制,可以通过如下命令来查看和修改:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
-- 查看 sort_buffer 的大小
show variables like 'sort_buffer_size';

-- 修改 sort_buffer 的大小
set global sort_buffer_size = 262144;

(1)临时文件排序

如果当 sort buffer 空间无法容纳我们需要排序的数据时,这时会采用另外一种临时文件的方式进行排序,临时文件排序采用归并排序的算法,首先会把需要排序的数据拆分到多个临时文件里同步进行排序操作,然后把多个排好序的文件合并成一个结果集返回给客户端,不过在临时文件里排序相对于在 sort buffer 里排序来说,性能会慢很多,因为一个是在内存里操作,一个是在磁盘里操作。

(2)避免临时文件排序:rowid 排序

临时文件排序性能低下,所以 MySQL 会尽量避免使用临时文件排序。

这里 MySQL 根据单行数据的长度是否大于 max_length_for_sort_data 参数设置的值来判断是否可能会用到文件排序,当行数据长度大于 max_length_for_sort_data 时,它会进行优化,这里优化思路是尽量不把非必要的字段放到 sort buffer 中去。

什么是非必要的数据呢?以上面的案例来说,我们要对 created_at 字段排序,那么除了 created_at 字段外,其它的数据都可不必放到 sort buffer 中去,我们是不是可以先把 created_at 放到 sort buffer 里面排好序,然后再回表查询出其它关联字段返回给客户端。

因为排好序之后还要关联查询出其它列的数据,所以除了 created_at 之外,我们还需要有 id 字段,所以 id 字段我们也是必须要放到 sort buffer 里面的。这样的话执行流程大致如下:

  1. 把符合条件 created_at、id 列查询出来放到 sort buffer 里。
  2. 在 sort buffer 里根据 create_time 字段对数据进行排序。
  3. 把排好序的数据根据 id 再拿到 city、name 等其他字段。
  4. 返回结果给客户端。

3.ORDER BY 优化思路

根据 ORDER BY 的原理我们可以得到一些 SQL 优化思路。

(1)可以适当调大一些 sort_buffer_size。

(2)避免非必要的字段查询,因为这些字段越多,所需要的空间越大,就很可能导致 sort buffer 空间不够,转而使用其他效率低的排序策略,比如临时文件排序和 rowid 排序。

(3)尽量使用索引排序,如果这里使用 ID 排序的话,因为 ID 是索引字段,天生就具备有序特性,所以这种情况都不需要放到 sort buffer 额外进行排序。

(4)将 ORDER BY 字段与 WHERE 字段建立联合索引,即利用联合索引的有序性,优化 ORDER BY。

  • ORDER BY 的索引优化
代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
SELECT [column1],[column2],FROM [table] ORDER BY [sort];

在 [sort] 栏位上建立索引就可以利用索引优化 ORDER BY。

  • WHERE + ORDER BY 的索引优化
代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
SELECT [column1],[column2],. FROM [table] WHERE [columnX] = [value] ORDER BY [sort]; 

建立一个联合索引 (columnX,sort) 来实现 ORDER BY 优化。

注意:如果 columnX 对应多个值,如下面语句就无法利用联合索引实现 ORDER BY 的优化。因为联合索引是按照 columnX 排序,再按照 sort 排,columnX 不同值对应的 sort 列之间无顺序关系。

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
SELECT [column1],[column2],. FROM [table] WHERE [columnX] IN ([value1],[value2],) ORDER BY [sort];
  • WHERE+多个字段 ORDER BY
代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10; 

建立索引 (uid,x,y) 实现 ORDER BY 优化比建立 (x,y,uid) 索引效果要好得多。

总的来说,MySQL 的 ORDER BY 实现原理是复杂的,它依赖于查询优化器的决策,可能涉及索引排序、内存排序和磁盘排序等策略。目的是为了在尽可能短的时间内返回有序的查询结果。优化查询和适当的索引设计可以改善排序性能。


参考文献

Mysql order by实现原理 - 知乎专栏 MySQL中order by语句的实现原理以及优化手段 - InfoQ 写作社区 MySQL如何利用索引优化ORDER BY排序语句 - CSDN 【原创】面试官:谈谈你对mysql联合索引的认识?

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2024-01-25,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
看一遍就理解:order by详解!
日常开发中,我们经常会使用到order by,亲爱的小伙伴,你是否知道order by 的工作原理呢?order by的优化思路是怎样的呢?使用order by有哪些注意的问题呢?本文将跟大家一起来学习,攻克order by~
macrozheng
2021/07/02
1.4K0
看一遍就理解:order by详解!
面试官:order by 怎么优化?
刚换了新工作,用了两周时间准备,在 3 天之内拿了 5 个 offer,最后选择了广州某互联网行业独角兽 offer,昨天刚入职。这几天刚好整理下在面试中被问到有意思的问题,也借此机会跟大家分享下。
JavaFish
2021/07/05
2.5K0
面试官:order by 怎么优化?
MySQL排序内部原理探秘
一、我们要解决什么问题 二、排序,排序,排序 三、索引优化排序 四、排序模式 4.1实际trace结果 4.2排序模式概览 4.2.1回表排序模式 4.2.2不回表排序模式 4.2.3打包数据排序模式 4.2.4三种模式比较 五、外部排序 5.1普通外部排序 5.1.1两路外部排序 5.1.2多路外部排序 5.2MySQL外部排序 5.2.1MySQL外部排序算法 5.2.2sort_merge_passes 六、trace 结果解释 6.1 是否存在磁盘外部排序 6.2 是否存在优先队列优
沃趣科技
2018/03/26
2.7K0
MySQL排序内部原理探秘
mysql 之order by工作流程
Extra 中 Using index condition; 这个是之前文章中提到的索引下推 ICP Using filesort 这个表示需要排序 mysql会给每个线程分配一块内存 叫做sort_buffer
科技新语
2025/03/20
840
mysql 之order by工作流程
MySQL order by 是怎么工作的?
这个排序过程叫做全字段排序,因为需要返回的字段都放入了 sort_buffer 参与排序过程。
dys
2019/05/13
1.8K0
MySQL order by 是怎么工作的?
Mysql中orderby底层执行流程
前言 在实际的开发中一定会碰到根据某个字段进行排序后来显示结果的需求,但是你真的理解order by在 Mysql 底层是如何执行的吗? 假设你要查询城市是苏州的所有人名字,并且按照姓名进行排序返回前 1000 个人的姓名、年龄,这条 sql 语句应该如何写? 首先创建一张用户表,sql 语句如下: CREATE TABLE user ( id int(11) NOT NULL, city varchar(16) NOT NULL, name varchar(16) NOT NULL, ag
爱撒谎的男孩
2020/04/21
2K0
Mysql中orderby底层执行流程
MySQL - order by 出现 using filesort根因分析及优化
当然了实际工作中是基本不会出现这种情况的, 假设真的取了100万数据, 无论是MySQL内存缓冲区的占用,还是网络带宽的消耗都是巨大的。
小小工匠
2021/11/10
6.8K1
MySQL - order by 出现 using filesort根因分析及优化
MySQL Order By工作原理
Extra中包含Using filesort表示需要排序,在排序时,MySQL会为每个线程分配一块内存区域用于排序,称之为sort_buffer。
shysh95
2022/02/16
8640
MySQL Order By工作原理
order by 原理以及优化
一 简介 偏向于业务的(MySQL)DBA或者业务的开发者来说,order by 排序是一个常见的业务功能,将结果根据指定的字段排序,满足前端展示的需求。然而排序操作也是经常出现慢查询排行榜的座上宾。本文将从原理和实际案例优化,order by 使用限制等几个方面来逐步了解order by 排序。
用户1278550
2018/08/09
7610
order by的工作原理
where条件后面是city字段,然后根据name排序,可以看到,执行计划中有:using filesort字样。这是因为name字段没有索引,所以需要借助sort_buffer来进行排序操作。
AsiaYe
2020/06/22
7540
MySQL排序原理与优化方法(9/16)
**内存临时表排序:**在MySQL中,使用InnoDB引擎执行排序操作时,当处理的数据量较小,可以在内存中完成排序时,MySQL会优先使用内存进行排序操作。在这种情况下,MySQL会创建一个临时内存表来存储排序结果,这样可以快速地对数据进行排序,提高查询效率。
十里桃花舞丶
2024/04/12
2480
MySQL怎样处理排序⭐️如何优化需要排序的查询?
在MySQL的查询中常常会用到 order by 和 group by 这两个关键字
菜菜的后端私房菜
2024/06/21
3240
工作中遇到的99%SQL优化,这里都能给你解决方案(二)
利用最左前缀法则:中间字段不能断,因此查询用到了name索引,从key_len=74也能看出,age索引列用在排序的过程中,因为Extra字段里没有using filesort。
程序员小强
2019/09/10
5020
工作中遇到的99%SQL优化,这里都能给你解决方案(二)
Mysql order by 优化
本节描述MySQL何时可以使用索引来满足ORDER BY子句,当不能使用索引时使用filesort,以及优化器中有关ORDER BY的执行计划信息。
XING辋
2019/03/26
1.5K0
Mysql进阶优化篇05——子查询的优化和排序优化
MySQL 从 4.1 版本开始支持子查询,使用子查询可以进行 SELECT 语句的嵌套查询,即一个 SELECT 查询的结果作为另一个 SELECT 语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的操作 。
半旧518
2022/10/26
2.4K0
Mysql进阶优化篇05——子查询的优化和排序优化
盘点MySQL慢查询的12个原因
日常开发中,我们经常会遇到数据库慢查询。那么导致数据慢查询都有哪些常见的原因呢?今天田螺哥就跟大家聊聊导致MySQL慢查询的12个常见原因,以及对应的解决方法。
捡田螺的小男孩
2023/02/24
1.6K0
盘点MySQL慢查询的12个原因
MySQL Order By实现原理分析和Filesort优化
在使用explain分析查询的时候,利用有序索引获取有序数据显示Using index。而文件排序显示Using filesort。
黄规速
2022/04/14
1.5K0
MySQL Order By实现原理分析和Filesort优化
Mysql的SQL优化指北
在一次和技术大佬的聊天中被问到,平时我是怎么做Mysql的优化的?在这个问题上我只回答出了几点,感觉回答的不够完美,所以我打算整理一次SQL的优化问题。
luozhiyun
2020/02/11
1K0
浅谈MySQL分页查询的工作原理
MySQL 的分页查询在我们的开发过程中还是很常见的,比如一些后台管理系统,我们一般会有查询订单列表页、商品列表页等。
政采云前端团队
2023/11/09
2.2K0
浅谈MySQL分页查询的工作原理
mysql中走与不走索引的情况汇集(待全量实验)
在MySQL中,并不是你建立了索引,并且你在SQL中使用到了该列,MySQL就肯定会使用到那些索引的,有一些情况很可能在你不知不觉中,你就“成功的避开了”MySQL的所有索引。
小勇DW3
2020/07/23
11.6K2
相关推荐
看一遍就理解:order by详解!
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档
本文部分代码块支持一键运行,欢迎体验
本文部分代码块支持一键运行,欢迎体验