MySQL 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;

有如下 SQL 语句:select city,name,age from t where city='杭州' order by name limit 1000; 

Extra 这个字段中的“Using filesort”表示的就是需要排序,MySQL 会给每个线程分配一块内存用于排序,称为 sort_buffer。内存大小根据 sort_buffer_size 参数决定 

通常情况下,这个语句执行流程如下所示 :

  1. 初始化 sort_buffer,确定放入 name、city、age 这三个字段;
  2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id,也就是图中的 ID_X;
  3. 到主键 id 索引取出整行,取 name、city、age 三个字段的值,存入 sort_buffer 中;
  4. 从索引 city 取下一个记录的主键 id;
  5. 重复步骤 3、4 直到 city 的值不满足查询条件为止,对应的主键 id 也就是图中的 ID_Y;
  6. 对 sort_buffer 中的数据按照字段 name 做快速排序;
  7. 按照排序结果取前 1000 行返回给客户端。

我们暂且把这个排序过程,称为全字段排序。

图中“按 name 排序”这个动作,可能在内存中完成,也可能需要使用外部排序,这取决于排序所需的内存和参数 sort_buffer_size。内存放不下时,就需要使用外部排序,外部排序一般使用归并排序算法。MySQL 将需要排序的数据分成 12 份,每一份单独排序后存在这些临时文件中。然后把这 12 个有序文件再合并成一个有序的大文件。

rowid 排序

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

max_length_for_sort_data,是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是,如果单行的长度超过这个值,MySQL 就认为单行太大,要换一个算法。

 整个执行流程就变成如下所示的样子:

  1. 初始化 sort_buffer,确定放入两个字段,即 name 和 id;
  2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id,也就是图中的 ID_X;
  3. 到主键 id 索引取出整行,取 name、id 这两个字段,存入 sort_buffer 中;
  4. 从索引 city 取下一个记录的主键 id;
  5. 重复步骤 3、4 直到不满足 city='杭州’条件为止,也就是图中的 ID_Y;
  6. 对 sort_buffer 中的数据按照字段 name 进行排序;
  7. 遍历排序结果,取前 1000 行,并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

 rowid 排序多访问了一次表 t 的主键索引,就是步骤 7。

全字段排序 VS rowid 排序

如果 MySQL 认为内存足够大,会优先选择全字段排序,把需要的字段都放到 sort_buffer 中,这样排序后就会直接从内存里面返回查询结果了,不用再回到原表去取数据。对于 InnoDB 表来说,rowid 排序会要求回表多造成磁盘读,因此不会被优先选择。

MySQL 做排序是一个成本比较高的操作。并不是所有的 order by 语句,都需要排序操作的,MySQL 之所以需要生成临时表,并且在临时表上做排序操作,其原因是原来的数据都是无序的。

如果能够保证从 city 这个索引上取出来的行,天然就是按照 name 递增排序的话,就可以不用再排序了。

alter table t add index city_user(city, name);

这样整个查询过程的流程就变成了:

  1. 从索引 (city,name) 找到第一个满足 city='杭州’条件的主键 id;
  2. 到主键 id 索引取出整行,取 name、city、age 三个字段的值,作为结果集的一部分直接返回;
  3. 从索引 (city,name) 取下一个记录主键 id;
  4. 重复步骤 2、3,直到查到第 1000 条记录,或者是不满足 city='杭州’条件时循环结束。

  推荐阅读

一条 SQL 更新语句如何执行的

MySQL 事务的原理以及长事务的预防和处置

InnoDB索引优化

一条 sql 语句可能导致的表锁和行锁以及死锁检测

MySQL删除数据 文件大小不变的原因以及处理空洞问题 

 

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/276049.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

抖音在线点赞任务发布接单运营平台PHP网站源码

源码简介 抖音在线点赞任务发布接单运营平台PHP网站源码 多个支付通道分级会员制度 介绍: 1、三级代理裂变,静态返佣/动态返佣均可设置。(烧伤制度)。 2、邀请二维码接入防红跳转。 3、自动机器人做任务,任务时间…

STM32CubeIDE基础学习-STM32CubeIDE软件新增工程文件夹

STM32CubeIDE基础学习-STM32CubeIDE软件新增工程文件夹 文章目录 STM32CubeIDE基础学习-STM32CubeIDE软件新增工程文件夹前言第1章 添加文件夹第2章 添加文件路径2.1 相对路径方法2.2 绝对路径方法 总结 前言 在编程的过程中,如果需要在原有的工程基础上新增其它的…

微信小程序-day01

文章目录 前言微信小程序介绍 一、为什么要学习微信小程序?二、微信小程序的历史创建开发环境1.注册账号2.获取APPID 三、下载微信开发者工具1.创建微信小程序项目2.填写相关信息3.项目创建成功 四、小程序目录结构项目的主体组成结构 总结 前言 微信小程序介绍 微信小程序&…

【OceanBase诊断调优 】 —— 合并问题如何排查?

最近总结一些诊断OCeanBase的一些经验,出一个【OceanBase诊断调优】专题,也欢迎大家贡献自己的诊断OceanBase的方法。 1. 前言 OceanBase 数据库的存储引擎基于 LSM-Tree 架构,将数据分为静态基线数据(放在 SSTable 中&#xff…

留学生课设|R语言|研究方法课设

目录 INSTRUCTIONS Question 1. Understanding Quantitative Research Question 2. Inputting data into Jamovi and creating variables (using the dataset) Question 3. Outliers Question 4. Tests for mean difference Question 5. Correlation Analysis INSTRUCTIO…

有趣的前端知识(三)

推荐阅读 有趣的前端知识(一) 有趣的前端知识(二) 文章目录 推荐阅读JS内置对象JS外部对象BOM模型history对象screen对象navigator对象 DOM(文档对象模型)DOM的方法(对于节点的操作&#xff09…

金蝶BI方案能解决云星空数据分析痛点吗?

金蝶云星空作为一个主攻企业管理流程的软件确实立下了汗马功劳,但一到数据分析方面那就阻碍重重了。直接的感受是分析步骤多且复杂,数据展现不够直观易懂,有些分析指标的计算真的很难实现,跨部门跨组织计算指标、合并账套什么的能…

vscode 向下复制当前行(即visual studio 中的Ctrl + D)功能快捷键

参考:https://blog.csdn.net/haihui1996/article/details/87937912 打开vscode左下角键盘快捷键设置,找到copy line down,即可查看当前默认快捷键为“shift Alt ↓” 双击快捷键,输入自己想要的快捷组合,如CtrlD,然…

尚硅谷SpringBoot3笔记 (二) Web开发

Servlet,SpringMVC视频推荐:53_尚硅谷_servlet3.0-简介&测试_哔哩哔哩_bilibili HttpServlet 是Java Servlet API 的一个抽象类,用于处理来自客户端的HTTP请求并生成HTTP响应。开发人员可以通过继承HttpServlet类并重写其中的doGet()、do…

给电脑加硬件的办法 先找电脑支持的接口,再买相同接口的

需求:我硬盘太小,换或加一个大硬盘 结论:接口是NVMe PCIe 3.0 x4 1.找到硬盘型号 主硬盘 三星 MZALQ512HALU-000L2 (512 GB / 固态硬盘) 2.上官网查 或用bing查 非官方渠道信息,不确定。

[论文笔记]LLaMA: Open and Efficient Foundation Language Models

引言 今天带来经典论文 LLaMA: Open and Efficient Foundation Language Models 的笔记,论文标题翻译过来就是 LLaMA:开放和高效的基础语言模型。 LLaMA提供了不可多得的大模型开发思路,为很多国产化大模型打开了一片新的天地,论文和代码值…

uniapp运行钉钉小程序

因项目原因,公司需要在钉钉里面开发小程序。之前用uniapp开发过app,H5,小程序。还真没尝试过钉钉小程序,今天就简单的记录下uniapp运行钉钉小程序中的过程。 在项目目录新建package.json文件,在文件中添加如下代码&am…

医学图像目标跟踪论文阅读笔记 2024.03.08~2024.03.14

“Inter-fractional portability of deep learning models for lung target tracking on cine imaging acquired in MRI-guided radiotherapy” 2024年 期刊 Physical and Engineering Sciences in Medicine 医学4区 没资源,只读了摘要,用的是U-net、a…

解决iview表格固定列横向滚动条无法拖动问题

问题描述: iview的table添加固定列以后,滚动条在固定列下面无法拖动,只能在滚动区域有所反应 解决办法 【写入main.js引入的全局文件时不需要::v-deep; 写入单个文件需要加::v-deep】 方法一:【带合计行也适用】 //解决iview表…

Unity资源热更新----AssetBundle

13.1 资源热更新——AssetBundle1-1_哔哩哔哩_bilibili Resources 性能消耗较大 Resources文件夹大小不能超过2个G 获取AssetBundle中的资源 打包流程 选择图片后点击 创建文件夹,Editor优先编译 打包文件夹位置 using UnityEditor; using UnityEngine; public cla…

计算机网络——OSI网络层次模型

计算机网络——OSI网络层次模型 应用层表示层会话层传输层TCP和UDP协议复用分用 网络层数据链路层物理层OSI网络层次模型中的硬件设备MAC地址和IP地址MAC地址IP地址MAC地址和IP地址区别 OSI网络层次模型通信过程解释端到端点到点端到端和点到点的区别 我们之前简单介绍了一下网…

idea如何使用,从激活开始

idea到期后激活使用 如何使用 点击阅读 idea分享

HTML5、CSS3面试题(二)

上一章:HTML5、CSS3面试题(一) 哪些是块级元素那些是行内元素,各有什么特点 ?(必会) 行内元素: a、span、b、img、strong、input、select、lable、em、button、textarea 、selecting 块级元素&#xff1…

阿里云免费证书改为3个月,应对方法很简单

情商高点的说法是 Google 积极推进90天免费证书,各服务商积极响应。 情商低点的话,就是钱的问题。 现在基本各大服务商都在2024年停止签发1年期的免费SSL证书产品,有效期都缩短至3个月。 目前腾讯云倒还是一年期。 如果是一年期的话&#x…

机器学习-0X-神经网络

总结 本系列是机器学习课程的系列课程,主要介绍机器学习中神经网络算法。 本门课程的目标 完成一个特定行业的算法应用全过程: 懂业务会选择合适的算法数据处理算法训练算法调优算法融合 算法评估持续调优工程化接口实现 参考 机器学习定义 关于机…