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

MySQL的 order by 工作原理

时间:2019-05-15 17:05:34  来源:  作者:
MySQL的 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   点击:()  评论:()
声明:本站部分内容来自互联网,如有任何版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▌相关评论
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
▌相关推荐
概述操作系统及MySQL数据库的实时性能状态数据尤为重要,特别是在有性能抖动的时候,这些实时的性能数据可以快速帮助你定位系统或MySQL数据库的性能瓶颈,那么有哪些重要的实时性...【详细内容】
2019-05-15 17:05:34   点击:(2)  评论:(0)  加入收藏
在程序设计当中,我们很多场景下都会用 group by 关键字。比如在分页读取数据时,为了避免重复扫描记录,这就是必须要使用 group by 了。比如我们使用如下 DDL 创建表:CREATE TAB...【详细内容】
2019-05-15 17:05:34   点击:(6)  评论:(0)  加入收藏
方法一cmd 到mysql bin目录下用如下命令:mysqldump --opt -h192.168.0.156 -uusername -ppassword --skip-lock-tables databasename>database.sql把ip改成localhost就可以的...【详细内容】
2019-05-15 17:05:34   点击:(4)  评论:(0)  加入收藏
概述Mysql binlog日志有三种格式,分别为Statement,MiXED,以及ROW!这三种格式之间有什么区别呢?下面先介绍下各自的优缺点。ROW日志中会记录成每一行数据被修改的形式,然后在slav...【详细内容】
2019-05-15 17:05:34   点击:(5)  评论:(0)  加入收藏
MySQL中没有Rank排名函数当我们需要查询排名时,只能使用MySQL数据库中的基本查询语句来查询普通排名。尽管如此,可不要小瞧基础而简单的查询语句,我们可以利用其来达到Rank函数...【详细内容】
2019-05-15 17:05:34   点击:(3)  评论:(0)  加入收藏
查看表是否被锁:直接在mysql命令行执行:show engine innodb statusG。查看造成死锁的sql语句,分析索引情况,然后优化sql.然后show processlist,查看造成死锁占用时间长的sql语...【详细内容】
2019-05-15 17:05:34   点击:(4)  评论:(0)  加入收藏
分片(类似分库)分片是把数据库横向扩展(Scale Out)到多个物理节点上的一种有效的方式,其主要目的是为突破单节点数据库服务器的 I/O 能力限制,解决数据库扩展性问题。Shard这个词...【详细内容】
2019-05-15 17:05:34   点击:(5)  评论:(0)  加入收藏
mysql高并发的解决方法有:优化SQL语句,优化数据库字段,加缓存,分区表,读写分离以及垂直拆分,解耦模块,水平切分等。高并发大多的瓶颈在后台,在存储mysql的正常的优化方案如下:(1)代码中...【详细内容】
2019-05-15 17:05:34   点击:(8)  评论:(0)  加入收藏
数据库的索引就像一本书的目录一样,它可以快速定位你所需要的信息。下面来详细说一下MySQL的索引结构。常见索引类型Hash 索引Hash索引的底层实现是由Hash表来实现的,非常适合...【详细内容】
2019-05-15 17:05:34   点击:(1)  评论:(0)  加入收藏
在我们开发的过程中,使用全局锁和表锁的场景比较少,接触的也相对少一点,下面主要介绍一下。全局锁FTWRL全局锁就是对整个数据库实例加锁,MySQL 提供了 flush tables with read l...【详细内容】
2019-05-15 17:05:34   点击:(1)  评论:(0)  加入收藏
推荐资讯
相关文章
栏目更新
栏目热门
'); })();