MySQL简单了解“order by”是怎么工作的
来源:脚本之家
时间:2022-12-31 21:53:18 179浏览 收藏
在数据库实战开发的过程中,我们经常会遇到一些这样那样的问题,然后要卡好半天,等问题解决了才发现原来一些细节知识点还是没有掌握好。今天golang学习网就整理分享《MySQL简单了解“order by”是怎么工作的》,聊聊orderbyMysql,希望可以帮助到正在努力赚钱的你。
针对排序来说,order by 是我们使用非常频繁的关键字。结合之前我们对索引的了解再来看这篇文章会让我们深刻理解在排序的时候,是如何利用索引来达到少扫描表或者使用外部排序的。
先定义一个表辅助我们后面理解:
CREATE TABLE `t` ( `id` int(11) NOT NULL, `city` varchar(16) NOT NULL, `name` varchar(16) NOT NULL, `age` int(11) NOT NULL, `addr` varchar(128) DEFAULT NULL, PRIMARY KEY (`id`), KEY `city` (`city`) ) ENGINE=InnoDB;
这时我们写一条查询语句
select city,name,age from t where city='杭州' order by name limit 1000 ;
根据上面的表定义来看,city=xxx 可以使用到我们定义的一个索引。但是 order by name 明显我们没有索引,所以肯定需要先用索引查询到 city=xxx 然后再进行回表查询,最后再排序。
全字段排序
在 city 字段上面创建索引之后,我们使用执行计划来查看这个语句
可以看到有索引的情况下 我们这里还是使用了 "Using filesort" 表示需要排序,MySQL 会给每个线程分配一块内存用于排序 称为 sort_buffer。
我们在执行上面 select 语句的时候通常经历了这样一个过程
1. 初始化 sort_buffer, 确认放入 name, city, age 这三个字段。
2. 从索引 city 找到第一个满足 city='杭州'条件的主键 id。
3. 回表取到 name, city, age 三个字段值,存入 sort_buffer 中。
4. 从索引 city 取下一个主键 id 记录。
5. 重复 3-4 步骤,直到 city 不满足条件。
6. 对 sort_buffer 中的数据按照字段 name 做快速排序。
7. 排序结果取前 1000 行返回给客户端。
这被我们称为全字段排序。
按照 name 排序这个动作即可能在内存中完成,也可以能使用外部文件排序。这取决于 sort_buffer_size 。sort_buffer_size 的默认值是1048576 byte 也就是 1M,如果要排序的数据量小于 1m 排序就在内存中完成,如果排序数据量大,内存放不下,则使用磁盘临时文件辅助排序。
Rowid 排序
如果单行很大,需要的字段全部放进 sort_buffer 效果就不会很好。
MySQL 中专门用于控制排序的行数据长度有个参数 max_length_for_sort_data 默认是1024,如果超过了这个值就会使用 rowid 排序。那么执行上面语句的流程就变成了
1. 初始化 sort_buffe 确定放入两个字段即 name 和 id 。
2. 从索引 city 找到第一个满足 city = '杭州'条件的主键 id。
3. 回表取 name 和 id 两个字段 存入 sort_buffer 中。
4. 取下个满足条件的记录 重复 2 3 步骤。
5. 对 sort_buffer 中的 name 进行排序。
6.遍历结果取前 1000 行。然后按照 id 再回一次表取的结果字段返回给客户端。
其实并不是所有 oder by 语句都需要进行上面的二次排序操作。从上面分析的执行过程,我们可以注意到。MySQL 之所以需要生成临时表,是因为要在临时表上做排序,是因为之前我们取得的是数据是无序的。
如果我们对刚才的索引修改一下,使得他是一个联合索引,那么第二个字段我们拿到的值其实就是有序的了。
联合索引满足这么一个条件,当我们的第一个索引字段是相等的情况下,第二个字段是有序的。
这能保证如果我们建立 (city,name) 索引的话,当我们在搜索 city='杭州'的情况的是时候找到的目标第二个字段 name 其实是有序的。所以查询过程可以简化成。
1. 从索引 (city, name) 找到第一个满足 city = '杭州'条件的主键 id 。
2. 回表取到 name city age 三个值返回。
3. 取下一个 id 。
4. 重复2 3 两个步骤直到 1000 条记录,或者是不满足 city = '杭州'条件结束。
也因为查询过程都可以使用到索引的有序性,所以不再需要排序也不需要时使用 sort buffer 了。
更近一步的优化就是之前说过的索引覆盖,将需要查询的字段也覆盖进索引中,再省掉回表的步骤,可以让整个查询的速度更快。
以上就是本文的全部内容了,是否有顺利帮助你解决问题?若是能给你带来学习上的帮助,请大家多多支持golang学习网!更多关于数据库的相关知识,也可关注golang学习网公众号。
-
501 收藏
-
501 收藏
-
501 收藏
-
501 收藏
-
501 收藏
-
463 收藏
-
206 收藏
-
132 收藏
-
188 收藏
-
404 收藏
-
101 收藏
-
265 收藏
-
209 收藏
-
446 收藏
-
339 收藏
-
285 收藏
-
259 收藏
-
- 前端进阶之JavaScript设计模式
- 设计模式是开发人员在软件开发过程中面临一般问题时的解决方案,代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景,打造一站式知识长龙服务,适合有JS基础的同学学习。
- 立即学习 542次学习
-
- GO语言核心编程课程
- 本课程采用真实案例,全面具体可落地,从理论到实践,一步一步将GO核心编程技术、编程思想、底层实现融会贯通,使学习者贴近时代脉搏,做IT互联网时代的弄潮儿。
- 立即学习 508次学习
-
- 简单聊聊mysql8与网络通信
- 如有问题加微信:Le-studyg;在课程中,我们将首先介绍MySQL8的新特性,包括性能优化、安全增强、新数据类型等,帮助学生快速熟悉MySQL8的最新功能。接着,我们将深入解析MySQL的网络通信机制,包括协议、连接管理、数据传输等,让
- 立即学习 497次学习
-
- JavaScript正则表达式基础与实战
- 在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
- 立即学习 487次学习
-
- 从零制作响应式网站—Grid布局
- 本系列教程将展示从零制作一个假想的网络科技公司官网,分为导航,轮播,关于我们,成功案例,服务流程,团队介绍,数据部分,公司动态,底部信息等内容区块。网站整体采用CSSGrid布局,支持响应式,有流畅过渡和展现动画。
- 立即学习 484次学习
-
- 优秀的水杯
- 好细啊,码住,感谢作者大大的这篇技术文章,我会继续支持!
- 2023-05-18 05:47:08
-
- 自然的故事
- 赞 👍👍,一直没懂这个问题,但其实工作中常常有遇到...不过今天到这,看完之后很有帮助,总算是懂了,感谢up主分享文章!
- 2023-04-27 13:43:29
-
- 简单的小笼包
- 很棒,一直没懂这个问题,但其实工作中常常有遇到...不过今天到这,看完之后很有帮助,总算是懂了,感谢博主分享文章!
- 2023-02-15 09:33:57
-
- 刻苦的绿茶
- 这篇技术贴出现的刚刚好,好细啊,太给力了,已加入收藏夹了,关注博主了!希望博主能多写数据库相关的文章。
- 2023-01-29 17:24:20
-
- 眯眯眼的哈密瓜
- 这篇博文出现的刚刚好,很详细,很好,mark,关注老哥了!希望老哥能多写数据库相关的文章。
- 2023-01-20 20:23:18
-
- 雪白的发卡
- 这篇博文太及时了,老哥加油!
- 2023-01-07 07:56:48