免费制作封面的网站:MySQL中order by语句的完成原理以及优化手法
本文摘要: 相信很多人在面试过程当中,总被问到有无 SQL 调优经历,关于工作经历年限较少的求职者,通常都是在面试之前从网上百度一些答案,提前背熟,然后边试的时分直接将提前背好的答案说出来。笔者作为一名菜鸟,在刚满一年工作经历的
相信很多人在面试过程当中,总被问到有无 SQL 调优经历,关于工作经历年限较少的求职者,通常都是在面试之前从网上百度一些答案,提前背熟,然后边试的时分直接将提前背好的答案说出来。笔者作为一名菜鸟,在刚满一年工作经历的时分,出去面试,就是这么干的。记得去某家公司面试的时分,被面试官问到 order by 在排序的时分执行较慢,这个时分该怎么优化?我当初想都没想,就答复说给 order by 子句中的字段加上索引(当然这答案也是我提前从网上百度来的),接着面试官问为何加索引就可以提高 order by 的执行功率的时分,我就懵逼了,这我哪知道为何啊,百度也没通知我啊。后来面试天然也就黄了。

现在回想一下,当初是真的菜啊。不过话说回来,为何给 order by 子句中的字段加索引就可以加速 SQL 的执行?它一定能提高 SQL 的功率吗?为了搞清楚这些问题,我们就得从 order by 的完成原理上说起了。

示例表


为了便利说明举例,我们先创建一个示例表,建表语句如下。
CREATE TABLE `user` (
`id` BIGINT ( 11 ) AUTO_INCREMENT COMMENT '主键id',
`id_card` VARCHAR ( 20 ) NOT NULL COMMENT '身份证号码',
`name` VARCHAR ( 64 ) NOT NULL COMMENT '姓名',
`age` INT ( 4 ) NOT NULL COMMENT '年纪',
PRIMARY KEY ( `id` ),
INDEX ( `name` )
) ENGINE = INNODB COMMENT '用户表';

insert into `user`(id_card,name,age) values
('429006xxxxxxxx2134','张三',22),
('429006xxxxxxxx2135','李四',26),
('129006xxxxxxxx3136','王五',28),
('129106xxxxxxxx3337','赵六',17),
('129106xxxxxxxx3349','孙XX',43),
('129106xxxxxxxx3135','马大哈',39),
('129106xxxxxxxx3134','王一',55),
('139106xxxxxxxx2236','张三',7),
('139106xxxxxxxx2130','张三',31),
('439106xxxxxxxx2729','张三',29),
('439106xxxxxxxx2734','李明',78),
('429106xxxxxxxx1734','张三',96),
('129106xxxxxxxx1737','张三',89),
('129106xxxxxxxx1132','张三',3),
('129106xxxxxxxx1197','张三',11),
('129106xxxxxxxx1184','张三',14);

复制代码

我们创建了一张用户表,表中有 4 个字段:id 是自增的主键,id_card 表明用户的身份证号码,name 表明用户姓名,age 表明用户年纪,另外我们除了主键索引 id 外,还为 name 字段创建了一个普通索引。终究向表中刺进了几条数据,便利后边举例。

假设我们现在有如下需求:依照年纪从小到大,查询前三个姓名叫做张三的用户的身份证号码、姓名、年纪。对应的 SQL 语句应该这么写:
select id_card,name,age from user where name = '张三' order by age limit 3;
复制代码

这条 SQL 语句逻辑比较简略,很好了解,语句在执行时会使用到 name 索引树,并且会进行排序,我们可以通过要害字 Explain 来查看一下 SQL 的执行方案。
explain select id_card,name,age from user where name = '张三' order by age limit 3 \\G
复制代码


上图中 key 这一行的值为 name,这表明本次查询会使用到 name 索引;Extra 这一行的值为 Using filesort,这表明本次查询需要用到排序操作。接下来我们来看下,MySQL 的排序过程。

全字段排序


首要 MySQL 会为每一个查询线程分配一块内存,叫做 sort_buffer,这块内存的作用就是用来排序的。这块内存有多大呢?由参数 sort_buffer_size 控制,可以通过如下命令来查看和修正:
# 查看sort_buffer的巨细
show variables like 'sort_buffer_size';
# 修正sort_buffer的巨细
set global sort_buffer_size = 262144;
复制代码

针对上面我们提到的示例,我们来看下这个排序流程:
  1. 首要 MySQL 为对应的线程分配一块巨细为 sort_buffer_size 的内存,然后确认要向内存中放入哪些字段,因为本文示例中要查询的是 id_card、name、age 这三个字段,因此这块 sort_buffer 中要存放的字段是 id_card、name、age;
  2. 通过前面的执行方案我们现已知道了该条 SQL 语句在执行时会使用 name 索引树,所以存储引擎会先在 name 索引树中找到第一个 name="张三"的叶子结点,然后返回叶子结点中存放的主键 id 值;
  3. 依据上一步中返回的主键 id,进行回表,回到主键索引树上查找到对应 id 的数据,并取出 id_card、name、age 这三个字段的值,返回给 MySQL 的 server 层,放入到 sort_buffer 中;
  4. 继续在 name 索引树中查找下一条 name="张三"的结点,重复步骤 2、3,直到在 name 索引树上找到第一条 name 不等于张三时,停止查找。
  5. 前面 4 步现已查找到了所有 name 为张三的数据,接下来就是在 sort_buffer 中,将所稀有据依据 age 进行排序;
  6. 从排好序的数据中,取前三条返回。

整个过程的示意图如下:

这个排序过程,因为是将查询的所有字段都放入到了 sort_buffer 中(id_card、name、age),因此也被称之为全字段排序。

看到这里,有人肯定就会有疑问了,假如我们查询的数据量太大,契合 name="张三"的数据有很多条,以至于 sort_buffer 这块内存无法装下所稀有据,那这个时分我们肯定无法在 sort_buffer 内存中完成所有的排序了,那又该怎么办呢?答案就是借助磁盘文件来进行排序。

借助磁盘文件进行排序的时分,通常使用归并排序算法。当从主键 id 索引树中查询到数据时,将数据放入到 sort_buffer 中,当 sort_buffer 中快满时,就在 sort_buffer 中对这部分数据进行排序,然后将排好序的数据暂时存放进磁盘的一个小文件中,然后再继续从主键索引树中查询数据,再在 sort_buffer 中进行排序,写入磁盘的暂时文件中,循环操作,直到所稀有据读取完。终究将磁盘上的这些数据有序的小文件,合并成一个有序的大文件,这样就完成了所稀有据的排序操作。

从这个过程当中,我们可以发现,在要排序的数据的巨细一定额状况下,假如 sort_buffer 的巨细越小,即 sort_bufer_size 越小,那么我们在借助磁盘排序时,需要的暂时文件越多,那么发生 IO 的次数就越多,性能也就越差。

虽然我们知道了全字段排序原理,也能通过查询数据库的配置知道 sort_buffer_size 的巨细,可是我们该怎么知道我们的 SQL 在执行排序时有无借助磁盘文件进行排序呢?我们可以通过 MySQL 中 information_schema 库中 optimizer_trace 表来查看 SQL 执行的优化信息,可是默许状况下 optimizer_trace 的开关是关闭的,因为它记载的是 SQL 相关的优化信息,会额定耗费 MySQL 的资源。我们可以通过如下命令来查看和修正 optimizer_trace 的状态
# 查看
show variables like 'optimizer_trace';
# 暂时针对其时数据库连接修正(连接断开后,下次再连接数据库时,该值仍是false)
set optimizer_trace = "enabled=on";
# 针对所稀有据库连接修正
set global optimizer_trace = "enabled=on";
复制代码

开启了 optimizer_trace 统计信息之后,我们就能够从这张表中查看 SQL 执行的相关信息了。以文章前面示例为例,我们一次执行以下 SQL:
# 开启统计
set optimizer_trace = "enabled=on";
# 执行查询SQL
select id_card,name,age from user where name = '张三' order by age limit 3;
# 查询统计信息
select * from information_schema.optimizer_trace \\G
复制代码

最终我们可以看到如下图所示的统计信息(我只截取了一部分)

图中的 number_of_tmp_files 这一行表明的是排序时使用到的暂时文件的个数,假如为 0,则表明的是本次排序没有借助磁盘文件排序,假如大于 0,则表明借助了磁盘文件排序。因为我电脑上装置的 MySQL,默许 sort_buffer_size 为 256KB,例子中查询的数据量也比较小,所以完全可以在 sort_buffer 内存中完成排序,不需要借助磁盘文件。

假如想要演示借助磁盘文件排序,可以先将 sort_buffer_size 设置为一个很小的值,然后再执行查询操作,终究再看看 optimizer_trace 中统计的信息。因为本文的数据太少,而 sort_buffer_size 最小可以被设置为 32KB,不能比 32KB 还小,所以最终仍是使用的是内存排序,因此就不再做演示了,你可以在工作中的测试环境中试试,验证一下。

rowid 排序


看懂了上面的全字段排序,可能有人就会有疑问了,我们实践上只需要对 name 字段排序,为何还需要把 id_card 字段和 age 字段也放入到 sort_buffer 中呢?并且 sort_buffer 本身是有内存巨细限制的,sort_buffer 中放入的字段越多,那它能存放的数据条数就越少,假如要对多条数据排序,那就很有可能需要用到磁盘文件排序了,显然磁盘文件排序没有内存排序快。

既然知道了全字段排序的缺点,那么我们该怎么改善呢?这一点 MySQL 的开发人员早就现已考虑到了,因此就有了另外一种排序方式,暂且叫它 rowid 排序吧(为何是暂且呢?因为我在 MySQL 官方文档中并没有找到这种说法,这种说法是在极客时间上《MySQL 实战 45 讲》中看到的,不过详细叫什么不重要,重要的是知道其间的原理)。

rowid 排序原理的大致思路就是,不会将 SQL 语句中 select 后边的所有字段都放入到 sort_buffer 中,而是只会将需要被排序的字段和主键 id 放入到 sort_buffer 中,对应到本文的例子中就是:将 name 字段和主键 id 字段放入到 sort_buffer 中。

在实践开发过程傍边,有的表我们没有创建主键索引,那这个时分 MySQL 会判断表中有无仅有索引,假如有仅有索引,那就会将这个仅有索引作为主键;假如也没有仅有索引,那么 MySQL 会默许为每一行数据生成一个 rowid,这个 rowid 作用和主键作用一样,那么在排序的时分,放入到 sort_buffer 中的字段就是被排序的字段和 rowid 了,这也是为何叫它 rowid 排序的由来了。

前面我们说了全字段排序会将不需要排序的字段也放入到 sort_buffer 中,这些字段会占用内存,当这些字段多到一定程度时,MySQL 会认为全字段排序这种排序方式可能会需要借助磁盘文件排序,会影响性能,因此就将排序方式改为 rowid 排序。那么这个“一定程度”究竟是个什么程度呢?它是由参数 max_length_for_sort_data 控制的,这个参数表明的是当需要放入到 sort_buffer 中的字段的长度之和超过这个参数值时,就会使用 rowid 排序。你可以通过如下命令查看该参数的值。
show variables like 'max_length_for_sort_data';
复制代码

max_length_for_sort_data 参数的默许值为 1024 个字节,关于本文中的示例,因为 id_card、name、age 这三个字段的总长度加起来肯定是小于 1024 个字节的,没有超过 max_length_for_sort_data 的限制,因此不会使用 rowid 排序。

为了看看 rowid 的排序流程,我先将 max_length_for_sort_data 的值设置为一个较小的值,例如 16 个字节,这样 id_card、name、age 这三个字段的长度之和就超过了这个限制,因尔后面排序时会使用 rowid 排序算法。
# 限制设置为16个字节
set max_length_for_sort_data = 16;
# 查询数据
select id_card,name,age from user where name = "张三" order by age limit 3
复制代码

以上面的查询 SQL 为例,rowid 的排序流程如下:
  1. 首要 MySQL 为对应的线程分配一块巨细为 sort_buffer_size 的内存,然后确认要向内存中放入哪些字段,因为示例中要查询的是 id_card、name、age 这三个字段,而这三个字段的长度之和超过了 max_length_for_sort_data 的限制,所以选用 rowid 排序, 因此这块 sort_buffer 中要存放的字段是 age 和主键 id;
  2. 存储引擎先在 name 索引树中找到第一个 name="张三"的叶子结点,然后返回叶子结点中存放的主键 id 值;
  3. 依据上一步中返回的主键 id,进行回表,回到主键索引树上查找到对应 id 的记载,并取出 age 字段的值,返回给 MySQL 的 server 层,放入到 sort_buffer 中;
  4. 继续在 name 索引树中查找下一条 name="张三"的结点,重复步骤 2、3,直到在 name 索引树上找到第一条 name 不等于张三时,停止查找。
  5. 前面 4 步现已查找到了所有 name 为张三的数据,并将要排序的字段 age 和主键 id 放入到了 sort_buffer 中,接下来就是在 sort_buffer 中,将所稀有据依据 age 进行排序;
  6. 从排好序的数据中,取前 3 条数据。因为我们要查询的数据是 id_card、name、age 这三个字段,此时 sort_buffer 中只有 id 和 age 字段,因此此时还需要依据取到的三条数据的 id,会到主键索引树上读取 id_card、name、age 的值;
  7. 终究将 id_card、name、age 字段的数据返回。

这个流程的示意图如下:

从这个流程中,我们可以发现,相比全字段排序而言,rowid 排序的回表次数更多。

相同,我们也能够查看一下 rowid 排序时,optimizer_trace 中记载的信息。执行语句如下:
select * from information_schema.optimizer_trace\\G
复制代码


从图中我们可以看到,sort_mode 这一行显示的 rowid,这说明本次排序使用的是 rowid 排序,而关于全字段排序,则显示的不是 rowid。

order by 优化思路


了解了全字段排序和 rowid 排序的原理,现在我们可以考虑一下该怎么优化排序的 SQL。

1. 调整 sort_buffer_size 巨细


首要,无论是全字段排序仍是 rowid 排序,它们都会遭到 sort_buffer 内存巨细的影响,假如数据过多,就到导致借助磁盘文件排序。借助磁盘文件排序,很发生磁盘 IO,性能差,显然这不是我们所期望的,我们应该尽量防止。假如参数 sort_buffer_size 太小,而 MySQL 效劳器的配置又较高,我们可以尝试将 sort_buffer_size 设置得大一点

2. max_length_for_sort_data


当查询字段的长度超过 max_length_for_sort_data 的限制后,MySQL 就会选用 rowid 排序,可是 rowid 排序会发生更多的回表次数,这可能会形成磁盘读,也会下降查询性能,所认为了防止 MySQL 使用 rowid 排序,我们可以将 max_length_for_sort_data 参数的值适当调大一点。

max_length_for_sort_data 参数的值,MySQL 默许是 1024,即 1KB。我个人觉得这个值现已很大了,1024 个字节现已可以包括很多字段了,依照均匀每一个字段 8 个字节来算(变长的 varchar 类型除外),差不多可以容纳 256 个字段了。假如你的查询 SQL 中要查询的字段长度超过了 1024 个字节,这极有多是 SQL 写得有问题了,我们可以尝试去优化 SQL,而不是去调整 MySQL 的体系参数。例如通过减少查询字段,分屡次查询,或者通过中心表来优化 SQL(这个当地也再次印证了尽量不要使用 select * 这类 SQL 语句的说法)。总之我个人觉得,max_length_for_sort_data 参数的值尽量不要调整。

说到这里,《高性能 MySQL》一书中,在第八章最初,作者曾提到过,MySQL 虽然为我们提供了很多可以配置的体系参数,可是这些参数大部分我们都可以直接选用默许值,只有很少数需要我们依据实践场景去调整。假如我们过多的去调整参数,并且还没有通过实践出产验证,极有可能起到反作用。

3. 使用联合索引


因为数据是乱序的,所以我们要对数据进行排序,那假如假设数据本身就是有序的,那么我们就不用再对数据进行排序操作了,防止了后边 sort_buffer 内存巨细、磁盘文件排序等一系列问题了。我们都知道,MySQL 中索引的数据结构使用的是 B+Tree,该数据结构的一大特点就是索引值是有序存放的,那么我们可以使用有序性这一特点,来防止排序工作。

关于本文示例中的 SQL,假如 age 字段在索引树上本身就是有序的,那么我们就不用再额定在 sort_buffer 中排序了,因此我们可以考虑建立一个 name 和 age 的联合索引:index(name,age)。

再继续考虑,因为我们需要查询 id_card、name、age 这三个字段的信息,而 index(name,age)这个联合索引上只有 name 和 age 的字段值,这意味着虽然我们可以通过这个联合索引防止掉排序操作,可是我们还需要回到主键索引树上取 id_card 字段的值,也就是需要回表,回表可能又形成磁盘读,所以我们还有优化空间。

假如看过我这篇文章的朋友《MySQL 索引的工作原理》,这个时分可能会想到,防止回表操作最常用的手法就是使用掩盖索引技能,所以这个时分我们可以创建 name、age、id_card 这三个字段的联合索引:index(name,age,id_card),这样联合索引树上存的数据,现已悉数满足了我们要查询的数据,所以不需要再进行回表操作了。SQL 语句如下:
# 我们先删除前面为name字段创建的索引
alter table user drop index `name`;
# 创建name、age、id_card的联合索引
alter table user add index(`name`,`age`,`id_card`);
# 使用explain要害字,查看一下SQL的执行方案
EXPLAIN select id_card,name,age from user where name = "张三" order by age limit 3\\G;
复制代码

SQL 的执行方案如下图:

从图中,我们可以发现,Extra 这一行变成了 Using index,没有了 Using filesort。Using index 表明使用到了掩盖索引,没有 Using filesort 表明本次 SQL 的执行不需要用到 sort_buffer 进行排序操作

需要额定说明的是,本文示例 SQL 中,只查询了 name="张三"的数据,所以我们能保证在联合索引 index(name,age,id_card)上,age 字段的值是有序的。假如我们的查询条件为 name in ("张三","王五"),那么就无法保证 age 字段是有序的了,因为联合索引中,是先保证第一列有序,再顺次保证后边的列有序,所以这个时分仍是得排序。假如还想使用这个特性,这个时分我们可以分两次查询,然后在应用程序的内存中进行数据的排序,如:
# 分两次查询
select id_card,name,age from user where name = '张三' order by age limit 3;
select id_card,name,age from user where name = '李四' order by age limit 3;
# 然后在应用程序中自己排序
复制代码

解答开篇


在文章开篇处我提到了一个问题:给 order by 子句中的字段添加索引,就一定能加速 SQL 的执行功率吗?现在我们来做个实验,示例表和数据仍是前面的,不同的是我们将除了主键以外的索引都删除,然后再为 age 字段创建一个索引。最终 user 表中,id 为主键索引,age 列有一个普通索引。然后我们用 Explain 要害字查看一下如下 SQL 的执行方案:
explain select id_card,name,age from user order by age limit 3 \\G
复制代码

执行方案如下图所示。

从图中我们可以发现,key 那一行的值为 null,这说明没有使用到 age 索引;type 这一行的值为 ALL,这说明进行了全表扫描;Extra 这一行的值为 Using filesort,这说明在 sort_buffer 进行了排序。

看到这里是否是有点毁三观啊,我们为 age 列创建了索引,怎么就没有使用呢?

这是因为我们要查询 id_card、name、age 这三个字段,而 age 索引树上没有存放这些信息,所以最终仍是回表到主键索引树上查询这些信息。这个时分 MySQL 认为,虽然 age 索引树上 age 字段值是有序的,可以防止排序操作,可是它需要回表到主键索引树去取其他字段的信息,MySQL 认为这个回表操作所耗费的性能大于防止排序操作所节省的性能,所以爽性就直接扫描主键索引树了,而不使用 age 索引树了。

继续实验,我们的 user 表中的数据太少了,一共只有 16 条,现在我们添加一点数据,我写了个简略的存储过程,向数据库中刺进了 10 万条数据(为了简略,每条数据的 name 和 id_card 的值都是瞎编的)。
delimiter ;;
create procedure fakeData()
BEGIN
DECLARE
 i INT;
SET i = 1;
WHILE
 ( i <= 100000 ) DO
 INSERT INTO user(id_card,name,age)
VALUES
 ( '429006xxxxxxxx2135', CONCAT('AA',i), i%100 ); # 身份证号码都是一样的(实践状况显然不是这样),姓名为AA+i,年纪为对i除以100取模
SET i = i + 1;
END WHILE;
END
delimiter ;;

# 执行存储过程
call fakeData();
复制代码

现在表里边大约有 10 万行数据了,我们在用 Explain 查看一下上面的查询过程:
explain select id_card,name,age from user order by age limit 3 \\G
复制代码

查询的执行方案如下图所示:

从图中我们可以看到,type 为 index,key 为 age,这说明使用了 age 这个索引,并且 Extra 这一行显示为 null,说明也不需要额定排序。相同的 SQL 语句,因为表中的数据量不一样,看到的却是不同的执行方案,这是为何呢?

这是因为表中的数据量比较多,id 主键索引树这个时分有 10 万行数据了,假如对 id 索引树进行全表扫描的话,MySQL 会认为这个过程会比较费时。而通过走 age 索引树,取排序前三的 age 所对应的 id,然后回表到主键索引树取数据,这个过程相比较直接对 id 索引树进行全表扫描执行得快,所以就抉择走 age 索引树了,也就是我们在执行方案中看到的。

现在我们再看一下下面这个 SQL 语句的执行方案:
explain select id_card,name,age from user order by age limit 1000 \\G
复制代码


这条 SQL 语句和前面的也是类似,仅有的差异就是 limit 取了前 1000 条记载,成果我们看到执行方案的截图傍边,type 为 ALL,key 为 null,这些说明这条 SQL 语句没有使用 age 索引,并且进行了全表扫描,Extra 这一行为 Using filesort,说明需要在内存中进行排序。

这又是为何呢?这是因为假如使用 age 索引树的话,就得回表,回到主键索引树中取数据。而 limit 为 1000,表明要取 1000 条数据,这就要回 1000 次表,MySQL 使命这个过程回表次数太多,耗费太大,还不如直接对主键索引树进行全表扫描,所以没有选择 age 索引。

看了这三个例子,相同的 SQL 逻辑,不同的是表中的数据量和要返回的数据条数,竟然看到的执行方案不一样,有的使用了索引,有的没有使用索引,发生这些现象的原因在于 MySQL 优化器是怎么选择的。因此在实践开发过程当中,一条 SQL 语句究竟有无使用索引,我们需要先通过 Explain 查看了执行方案才干确定,所以今后不要谈到 SQL 优化,上来不管三七二十一就说创建索引了。

总结


本文主要说明了 order by 排序的两种方法,分别为全字段排序和 rowid 排序,排序过程受体系参数 sort_buffer_size 和 max_length_for_sort_data 的影响。当查询的数据量过大时,超过 sort_buffer_size 的巨细,那么就会借助磁盘文件进行排序。假如查询的字段过多,每一行记载的查询字段长度之和超过 max_length_for_sort_data 后,MySQL 会认为数据量过大,可能会超过 sort_buffer_size,因此会选择使用 rowid 排序。

开发人员怎么知道一条 SQL 语句的排序使用的是全字段排序仍是 rowid 排序呢?排序过程当中有无借助磁盘文件排序呢?可以通过查看 optimizer_trace 来查看,number_of_tmp_files 表明借助暂时磁盘文件的个数,从 sort_mode 这一行中,可以知道是那种排序,假如显示的是 rowid,那就是 rowid 排序,不然是全字段排序。默许状况下,optimizer_trace 的开关是关闭的,因为统计这些信息,需要额定耗费 MySQL 效劳器的资源。

接着我们依据把握的 order by 的排序原理,提供了几种优化 order by 语句的思路,可以通过调整 MySQL 的体系参数 sort_buffer_size 和 max_length_for_sort_data,或者创建联合索引来提高 SQL 的执行功率。

终究,我通过几个例子,证明了 即便为 order by 子句中的字段创建了索引,在执行时就一定会选择索引,MySQL 的优化器会依据实践状况来抉择是否使用索引。而我们在实践开发过程当中,假如要对 SQL 语句优化,也应该是如此,结合实践场景,借助 Explain 等东西,通过分析后再抉择怎么优化 SQL。

参考资料

  • 《高性能 MySQL》
  • 极客时间林晓斌《MySQL 实战 45 讲》


作者:天堂同志
链接:https://juejin.im/post/5ea9972f6fb9a04382227db5

【免责声明】本文仅代表作者或发布者个人观念,不代表(www.lmnkf.cn)及其所属公司官方发声,对文章观念有疑义请先联络作者或发布者自己修正,若内容触及侵权或违法信息,请先联络发布者或作者删除,若需我们协助请联络平台管理员,Emailcxb5918(本平台不支撑其他投诉反馈渠道,谢谢合作)。若需要学习以上相关常识请到巨推学院观看视频教程,网站地址www.tsllg.cn。

相关内容