mysql order by是怎么工作的？

拼搏现实的明天。 2022-01-29 04:21 277阅读 0赞

假设我们要查询一个市民表中城市=杭州的所有人的名字，并且按照名字排序

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  ;

接下来我们看下explain的结果

图中的Extra这一列下面的Using filesort表示需要排序，MySQL会为每个连接分配一块内存用于排序，就是sort\_buffer，sort\_buffer\_size可以调整该排序内存大小

因为我们where条件用到了city，所以我们在city上面建立了索引

我们先看下该索引结构

从图中可以看出满足city=杭州的条件是ID\_X到ID\_Y之间的数据

通常情况下这个语句的执行流程如下:

1.初始化sort\_buffer，确定放入name,age,city三个字段

2.从索引city中找到第一个符合条件的数据，也就是ID\_X这个

3.取出索引中id的值，回表查询name,age,city的数据放入sort\_buffer中

4.从索引city取下一个符合条件的id

5.重复步骤3,4直到city的值不满足city=杭州的条件，也就是图中ID\_Y

6.对sort\_buffer中的数据按照name排序

7.按照排序结果取前1000行数据返回给客户端

我们把这个排序过程叫**全字段排序**

如下图所示

上图按name排序这个动作可能在内存中完成也可能需要外部排序，这取决于排序需要的内存大小和sort\_buffer\_size这个参数

如果排序需要的内存大于sort\_buffer\_size设置的数值，那么就需要使用磁盘临时文件辅助排序

**rowid排序**

在上面的那个全字段排序中，只对原表查询了一次，但是如果查询的字段很多的话，那么sort\_buffer中就会很多数据，就会使用到

磁盘临时辅助文件排序，这样性能会变差。

那么如果mysql认为单行数据过大会怎么办呢？

接下来设置一下这个参数为16

max\_length\_for\_sort\_data这个参数是mysql专门用来控制用于排序的行数据的单行的长度的一个参数，如果单行数据的字段的长度超过这个参数设置的值

那么就会使用rowid排序，比如说我们这个例子中name,age,city这三个字段的单行数据长度之和要是大于16，那么就会使用rowid排序

排序流程：

1.初始化sort\_buffer，确定放入id,name

2.取出city索引中第一个满足条件的索引的id值

3.到主键id索引里面取出整行，取出name,id字段放入sort\_buffer

4.去下一个符合条件的索引记录，放入sort\_buffer中

5.重复步骤3.4直到不满足city=杭州

6.对sort\_buffer中的数据按照name进行排序

7.遍历排序结果取出前1000行的数据的id，去表中查询出name,age,city返回给客户端

可以看出来rowid排序比全字段排序多了一次表查询就是步骤7

我们来对比下这两个排序

如果mysql觉得内存不够用就会用到rowid排序，如果内存够用则用全字段排序

也就是说Mysql有个设计思想，就是如果内存够，就尽量用内存，尽量减少磁盘的访问

看到这里你是不是觉得Mysql排序是一个非常复杂的流程，性能会不好，那么是不是所有的order\_by语句都要排序呢？

不是的，如果需要排序的字段天然就是有序的，那么就不需要排序，啥意思呢，比如说我们建立一个city和name的联合索引

alter table t add index city\_user(city, name);

作为与city索引的对比，我们看看这个索引

如果建立了这个索引那么执行流程就变成了这样

1.查询出第一条联合索引中city,name里面city=杭州的数据的id值

2.到主键索引里面取出整行，取出name,age,city字段

3.从索引city,name去下一个记录主键id

4.重复步骤2,3直到查到1000条记录或者不符合city=杭州循环结束

可以看到这个过程不需要排序，也不需要用到临时表

用explain验证一下

那么这个语句还有没有优化空间呢？

有的

我们建立一个三个字段的联合索引

alter table t add index city\_user\_age(city, name, age);

那么流程就变成了这样

1.查询出索引中第一条符合条件的数据，取出city,name,age作为结果集的一部分直接返回

2.从索引继续取下一个符合条件的数据作为结果集的一部分直接返回

3.重复步骤2直到查到1000条记录或者不符合city=杭州循环结束

这里其实就是用到了覆盖索引，直接不用回表查询了

当然这里绝对不是说遇到问题就加索引，这里只是举个例子，因为毕竟维护索引也是有代价的

发表评论取消回复

表情：

评论列表（有 0 条评论，277人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL进阶45讲【15】“order by“是怎么工作的？

MySQL进阶45讲【15】"order by"是怎么工作的？

柔情只为你懂/ 2024年05月07日 22:32/ 0 赞/ 46 阅读

相关 MySQL的order by

当我们需要对从数据库里查询出来的数据进行排序时，就得用到order by语句； order by语句相比与其它语句执行时间比较靠后，所以一般写在sql语句的最后； 1、基础

快来打我*/ 2023年09月26日 16:37/ 0 赞/ 113 阅读

相关 mysql order by and_Mysql - ORDER BY详解

0 索引 1 概述 2 索引扫描排序和文件排序简介 3 索引扫描排序执行过程分析 4 文件排序 5 补充说明 6 参考资料 1 概述 MySQL有两种方式可以实

逃离我推掉我的手/ 2023年01月11日 01:25/ 0 赞/ 140 阅读

相关 Mysql order by没效果怎么解决？

下面先来看下表结构 CREATE TABLE `person` ( `id` int(11) NOT NULL, `name` varchar

心已赠人/ 2022年11月29日 13:26/ 0 赞/ 125 阅读

相关 mysql group by order by

这两天让一个数据查询难了。主要是对group by 理解的不够深入。才出现这样的情况这种需求，我想很多人都遇到过。下面是我模拟我的内容表复制代码代码如下:

矫情吗；*/ 2022年04月15日 01:40/ 0 赞/ 184 阅读

相关【Mysql】 “order by”工作原理

CREATE TABLE \`t\` ( \`id\` int(11) NOT NULL, \`city\` varchar(16) NOT NULL,

矫情吗；*/ 2022年03月17日 03:06/ 0 赞/ 174 阅读

相关 mysql order by是怎么工作的？

假设我们要查询一个市民表中城市=杭州的所有人的名字，并且按照名字排序 CREATE TABLE `t` ( `id` int(11) NOT NULL,

拼搏现实的明天。/ 2022年01月29日 04:21/ 0 赞/ 278 阅读

相关 mysql实战16 | “order by”是怎么工作的？

在你开发应用的时候，一定会经常碰到需要根据指定的字段排序来显示结果的需求。还是以我们前面举例用过的市民表为例，假设你要查询城市是“杭州”的所有人名字，并且按照姓名排序返回前 1

╰+攻爆jí腚メ/ 2022年01月12日 05:29/ 0 赞/ 211 阅读

相关 order by怎么使用索引

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ub

刺骨的言语ヽ痛彻心扉/ 2021年11月23日 09:52/ 0 赞/ 283 阅读

相关 order by 是怎么工作的？读后总结

order by 排序的两种模式 1. 全字段排序我们一般用explain命令查看sql是否使用排序。一般查看extra字段的描述。 mysql会

àì夳堔傛蜴生んèń/ 2021年10月30日 04:08/ 0 赞/ 341 阅读