|
|
51CTO旗下网站
|
|
移动端

MySQL排序工作原理

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

作者:风度玉门来源:今天头条|2019-05-17 11:53

MySQL的 order by 工作原理

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

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

  1. CREATE TABLE `user_info` ( 
  2.  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID'
  3.  `city` varchar(16) NOT NULL COMMENT '城市'
  4.  `namevarchar(16) NOT NULL COMMENT '姓名'
  5.  `age` int(11) NOT NULL COMMENT '年龄'
  6.  `addr` varchar(128) DEFAULT NULL COMMENT '地址'
  7.  PRIMARY KEY (`id`), 
  8.  KEY `city` (`city`) 
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 

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

  1. 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)的压力。

  1. 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 字段的排序。

  1. 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 工作原理

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

  1. 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 工作原理

【编辑推荐】

  1. 解决线上数据库死锁,就是这么简单!
  2. MySQL+MyCat分库分表 读写分离配置
  3. 部署MySQL延迟从库的几个好处
  4. 影响MySQL查询性能的案例
  5. 从运维角度来分析Mysql数据库优化的一些关键点
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

这就是5G

这就是5G

5G那些事儿
共15章 | armmay

103人订阅学习

16招轻松掌握PPT技巧

16招轻松掌握PPT技巧

GET职场加薪技能
共16章 | 晒书包

364人订阅学习

20个局域网建设改造案例

20个局域网建设改造案例

网络搭建技巧
共20章 | 捷哥CCIE

751人订阅学习

读 书 +更多

ASP快速建站全程实录

本书从一个网站制作过程入手,详细介绍基于ASP技术建设网站的全过程。全书共10章。第1章,网站制作规划与流程;第2章,IIS安装与调试;第3...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微