新闻资讯  快讯  焦点  财经  政策  社会
互 联 网   电商  金融  数据  计算  技巧
生活百科  科技  职场  健康  法律  汽车
手机百科  知识  软件  修理  测评  微信
软件技术  应用  系统  图像  视频  经验
硬件技术  知识  技术  测评  选购  维修
网络技术  硬件  软件  设置  安全  技术
程序开发  语言  移动  数据  开源  百科
安全防护  资讯  黑客  木马  病毒  移动
站长技术  搜索  SEO  推广  媒体  移动
财经百科  股票  知识  理财  财务  金融
教育考试  育儿  小学  高考  考研  留学
您当前的位置:首页 > IT百科 > 数据库 > MYSQL

MySQL的 order by 工作原理

时间:2019-05-15 17:05:34  来源:  作者:
<a href=http://www.solves.com.cn/it/sjk/MYSQL/ target=_blank class=infotextkey>MySQL</a>的 order by 工作原理

 

在程序设计当中,我们很多场景下都会用 group by 关键字。比如在分页读取数据时,为了避免重复扫描记录,这就是必须要使用 group by 了。

比如我们使用如下 DDL 创建表:

CREATE TABLE `user_info` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
 `city` varchar(16) NOT NULL COMMENT '城市',
 `name` varchar(16) NOT NULL COMMENT '姓名',
 `age` int(11) NOT NULL COMMENT '年龄',
 `addr` varchar(128) DEFAULT NULL COMMENT '地址',
 PRIMARY KEY (`id`),
 KEY `city` (`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

并且我们会执行如下查询语句

SELECT city,`name`,age FROM user_info WHERE city='上海' ORDER BY `name` LIMIT 1000;

全字段排序

因为上面的建表语句已经在 city 字段上面创建索引了,当我们使用 EXPLAIN 命令时,会有如下结果:

MySQL的 order by 工作原理

 

上面 Extra 字段中的 “Using filesort” 表示的就是需要排序,MySQL 会为每个线程分配一块内存用于排序,成为 sort_buffer。下面我们看一下 index(city) 的结构示意图。

MySQL的 order by 工作原理

 

执行流程如下:

  1. 初始化 sort_buffer,确定放入 city name age 这 3 个字段;
  2. 从 city 索引中获取到第一个 city='上海' 的记录,也就是 id_x;
  3. 到主键索引中获取对应的记录,并取出 name city age 的值放入 sort_buffer;
  4. 取下一条符合条件的记录,重复 3 4 的操作,直至不符合条件为止;
  5. 对 sort_buffer 中的数据按照 name 做快速排序;
  6. 取出前 1000 条数据并返回。

我们暂时叫这种排序过程为“全字段排序”,如下所示:

MySQL的 order by 工作原理

 

图中的“按 name 排序” 可能在内存中,也可能使用磁盘文件排序,这取决与排序所需要的内存和 sort_buffer_size 。sort_buffer_size 就是 MySQL 为排序开辟的内存大小,当所需内存小于 sort_buffer_size 时,就直接在内存中完成排序,如果所需要的内存 大于 sort_buffer_size ,就需要额外的磁盘空间辅助排序。

rowid 排序

上面的算法在数据量比较大的时候,可能会出现一些问题。因为在排序的时候,存放了所有的返回字段,增加了 排序空间 (sort_buffer)的压力。

SET max_length_for_sort_data=16;

max_length_for_sort_data 是MySQL 限制排序行大小的参数。意思是,如果排序行大小超过了这个值,就会另选排序算法。上面 name city age 3 个字段的大小为 36,大于 16 ,在新的算法中将只有 name (排序字段) 和id 参与 sort_buffer 中的排序。过程如下

  1. 初始化 sort_buffer,确定放入 name id 这 2 个字段;
  2. 从 city 索引中获取到第一个 city='上海' 的记录,也就是 id_x;
  3. 到主键索引中获取对应的记录,并取出 name id 的值放入 sort_buffer;
  4. 取下一条符合条件的记录,重复 3 4 的操作,直至不符合条件为止;
  5. 对 sort_buffer 中的数据按照 name 做快速排序;
  6. 取出前 1000 条数据,然后根据 id 取出对应记录的 name city age 3 个字段并返回结果。

这种排序过程,我们称为 rowid 排序,过程如下所示:

MySQL的 order by 工作原理

 

全字段排序 VS rowid 排序

从上面 2 个流程看来,如果内存足够时,MySQL 会让返回值中所有字段存放在排序空间。当MySQL 内存过小时,才会考虑使用rowid 排序。但是从上面的流程看来,rowid 排序在返回结果前,还会再一次的回表。因此MySQL 认为内存充足的时候,会优先采用 全字段排序。

上面的场景是:city 字段过滤后,name 字段不是有序的。其实我们可以通过联合索引来规避掉 name 字段的排序。

alter table user_info add index idx_city_user(city, name);

下面我们看一下联合索引的示意图:

MySQL的 order by 工作原理

 

从上面流程图可以看出,当我们取出 city='上海' 的记录时,name的字段也是有序的。过程如下

  1. 从 (city, name)索引中获取到第一个 city='上海' 的记录 id_x;
  2. 到主键索引中获取对应的记录,并取出 name city age 的值作为结果集的一部分直接返回;
  3. 取下一条符合条件的记录,重复 2 3 的操作,直至不符合条件或者达到 1000 条为止;
MySQL的 order by 工作原理

 

从联合索引看来,我们是可以不用排序操作了,那么我们是否可以直接通过 索引就直接返回结果呢?也就是不要回表操作。答案是有的,那就是覆盖索引。

alter table user_info add index idx_city_user_age(city, name, age);

当执行查询语句时,不仅 name 中的字段是有序的,并且 索引中已经包含了结果集中的所有字段,过程如下:

  1. 从 (city, name,age)索引中获取到第一个 city='上海' 的记录,并取出 name city age 的值作为结果集的一部分直接返回;
  2. 取下一条符合条件的记录,重复 1 2 的操作,直至不符合条件或者达到 1000 条为止;
MySQL的 order by 工作原理

 


参考:《极客时间:MySQL实战》、《高性能MySQL》
 



Tags:MySQL   点击:()  评论:()
声明:本站部分内容来自互联网,内容观点仅代表作者本人,如有任何版权侵犯请与我们联系,我们将立即删除。
▌相关评论
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
▌相关推荐
1、字符串拼接SELECT CONCAT(&#39;My&#39;,&#39;S&#39;,&#39;QL&#39;) 2、字符串比较SELECT STRCMP(&#39;abc&#39;,&#39;abd&#39;),STRCMP(&#39;abc&#39;,&#39;abc&#39;),S...【详细内容】
2020-03-19   MySQL  点击:(3)  评论:(0)  加入收藏
这个是公司技术基础建设的一部分,在以后我会逐步推出更多的技术相关文章,感觉有用的请关注。这个文章是为了规范公司的数据库使用的,如果公司有原来使用的规范可以做下对比。这...【详细内容】
2020-03-18   MySQL  点击:(8)  评论:(0)  加入收藏
概述很长一段时间,MySQL 执行 连接 的唯一算法是 嵌套循环算法 ( nested loop algorithm) 的变体 ,但是 嵌套循环算法 在某些场景下非常低效,也是 MySQL 一直被诟病的一个问...【详细内容】
2020-03-18   MySQL  点击:(2)  评论:(0)  加入收藏
聚集索引和二级索引每张使用 InnoDB 作为存储引擎的表都有一个特殊的索引称为聚集索引,它保存着每一行的数据,通常,聚集索引就是主键索引。为了得到更高效的查询、插入以及其他...【详细内容】
2020-03-16   MySQL  点击:(4)  评论:(0)  加入收藏
行锁的三种算法InnoDB存储引擎有3种行锁的算法,其分别是: Record Lock:单个行记录上的范围 Gap Lock:间隙锁,锁定一个范围,但不包含记录本身 Next-Key Lock:Gap Lock + Record Loc...【详细内容】
2020-03-16   MySQL  点击:(4)  评论:(0)  加入收藏
《深入理解Java虚拟机》但要想真的深入理解虚拟机一问肯定远远不够的,但是本文中分三部分对JVM有深入的解析。第1章 走近Java第2章 Java内存区域与内存溢出异常第3章 垃圾收...【详细内容】
2020-03-16   MySQL  点击:(6)  评论:(0)  加入收藏
前言随着系统的运行,数据量变得越来越大,单纯的将数据存储在mysql中,已然不能满足查询要求了,此时我们引入Redis作为查询的缓存层,将业务中的热数据保存到Redis,扩展传统关系型数...【详细内容】
2020-03-16   MySQL  点击:(8)  评论:(0)  加入收藏
导论笔者的毕业设计是一个与大数据相关的项目,其中遇到了一个将百万级别数据存储到MySQL 的问题,本来是打算使用Spark SQL 的DataFrame 自带的API来直接将数据存储到MySQL 中,...【详细内容】
2020-03-16   MySQL  点击:(2)  评论:(0)  加入收藏
本文介绍如何利用python来对MySQL数据库进行操作,本文将主要从以下几个方面展开介绍:1.数据库介绍2.MySQL数据库安装和设置3.Python操作MySQL在Python3.X上安装MySQL驱动创建...【详细内容】
2020-03-15   MySQL  点击:(11)  评论:(0)  加入收藏
window7下的mysql的数据;C:\ProgramData\MySQL\MySQL Server 5.1\data如果是mysql同版本的数据库,直接备份这个文件夹的数据即可。如果是不同版本,可以导出sql脚本后再重新导...【详细内容】
2020-03-15   MySQL  点击:(8)  评论:(0)  加入收藏
一,首先还是登录到我们的服务器服务器如何登录我在上一节已经讲过了,大家只需要去看我上一节课程即可。 然后通过下面命令行,检测服务器上是否安装过mysqlrpm -qa|grep mysql如...【详细内容】
2020-03-13   MySQL  点击:(2)  评论:(0)  加入收藏
需要搞清楚pt-query工具Anemometer基于pt-query-digest将MySQL慢查询可视化percona-toolkit工具的安装安装目的:pt-query-digest是percona-toolkit里面一个工具,其作用就是分...【详细内容】
2020-03-13   MySQL  点击:(2)  评论:(0)  加入收藏
MySQL编码过程MySQL出现乱码的原因有很多,一般与character_set参数有关。我们先来看看有哪些参数:SHOW VARIABLES LIKE "character%";Variable_name Value character_set_clie...【详细内容】
2020-03-12   MySQL  点击:(7)  评论:(0)  加入收藏
在满足对某张表中数据的增、删、改的情况下,自动触发的功能称之为触发器。视图就是通过查询得到一张虚拟表,然后将标结果保存下来,下次直接使用即可。索引在MySQL中也叫做“键...【详细内容】
2020-03-12   MySQL  点击:(7)  评论:(0)  加入收藏
Django 默认使用 SQLite3 数据库,而在稍中大型的项目中大多使用 MySQL,因此我们安装 MySQL 进行后续的开发。步骤 1 MySQL 安装文件地址为 https://pan.baidu.com/s/1eSiy6Fw,...【详细内容】
2020-03-10   MySQL  点击:(8)  评论:(0)  加入收藏
MySQL 里有个东西叫 MRR,全称「Multi-Range Read Optimization」。简单说,MRR 通过: 把「随机磁盘读」,转化为「顺序磁盘读」从而: 提高了磁盘读取的性能至于: 为什么要把随机读转...【详细内容】
2020-03-10   MySQL  点击:(12)  评论:(0)  加入收藏
一、MHA原理1、简介:MHA(Master High Availability)目前在MySQL高可用方面是一个相对成熟的解决方案,它由日本DeNA公司youshimaton(现就职于Facebook公司)开发,是一套优秀的作为MyS...【详细内容】
2020-03-10   MySQL  点击:(7)  评论:(0)  加入收藏
在计算机科学中,锁是在执行多线程时用于强行限制资源访问的同步机制,即用于在并发控制中保证对互斥要求的满足。在数据库的锁机制中介绍过,在DBMS中,可以按照锁的粒度把数据库锁...【详细内容】
2020-03-10   MySQL  点击:(8)  评论:(0)  加入收藏
谁在消耗cpu?用户+系统+IO等待+软硬中断+空闲 祸首是谁?用户用户空间CPU消耗,各种逻辑运算正在进行大量tps函数/排序/类型转化/逻辑IO访问&hellip;用户空间消耗大量cpu,产生...【详细内容】
2020-03-09   MySQL  点击:(7)  评论:(0)  加入收藏
双mysql,只是在节约成本的前提下,考虑使用的,更多的是在开发测试环境使用,若是生产环境,尽量不建议如此使用。若是生产环境,森哥不建议双MySQL共存。而且,尽量建议使用提供商提供的...【详细内容】
2020-03-08   MySQL  点击:(3)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条