MySQL连接，排序，分组语句的执行流程_mysql join group 排序-优快云博客

本文详细介绍了MySQL中连接（NLJ、BNL算法）、排序（全字段排序、rowid排序、利用索引排序）及分组语句的执行流程。通过示例解析了不同查询语句在不同情况下的执行策略，包括内存使用、索引优化等，以帮助理解查询效率的影响因素。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

0 引言

针对mysql中连接(join)，分组(group by)，排序(order by)语句查询过程的一些知识总结，主要学习资源来自极客时间的《mysql实战45讲》与《mysql是怎样运行的》

1 连接语句

CREATE TABLE `t1` (
  `id` INT(11) NOT NULL,
  `a` INT(11) DEFAULT NULL,
  `b` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=INNODB;

创建一个如上的表t1，并插入100条数据，然后创建一个和表t1结构一样的表t2不过插入1000条数据。然后对t1和t2执行表的连接查询，根据查询语句的不同我们MySQL共有两种进行连接查询的算法，分别是NLJ和BNL算法，具体介绍如下

1.1 NLJ算法

当我们被驱动表上有索引可以使用的时候采用的就是NLJ算法，比如执行以下语句：

select * from t1, t2 where t1.a = t2.b;

该语句explain的结果如下：

可以看到t1是驱动表，t2是被驱动表，其中t1走的是全表扫描，t2走的是索引查询，这个语句的执行过程如下：

在t1中拿到一条记录R并取出其a值
用在t1中拿到的a值在t2的二级索引中找到对应的记录
根据前一步找到的记录回表到t2的聚簇索引中找到对应的记录
将前一步得到的记录与R合并就得到了最终的记录
重复前面四个步骤直到扫描到t1的最后一条记录为止

这就是NLJ算法的执行流程，我们可以看到在t1中100次查询，在t2中也进行了100次查询，最后总共执行了200次的查询过程。

1.2 BNL算法

当我们被驱动表上没有索引可以使用的时候就会用到这个算法，用这个算法的时候会在内存中开辟一块join_buffer使用，比如我们执行下面的语句

SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE t1.a = t2.b;

对该语句explain后得到的结果如下：

这里t1是驱动表，t2是被驱动表，注意一定要加上straight_join否则我们mysql底层会进行优化，将t2视为驱动表，t1作为被驱动表，这时就变成了上面的NLJ算法了。

这个语句的具体执行过程如下：

首先将t1中符合条件的记录全部放入到join_buffer中，由于这里我们是select *，所以会将t1表的所有字段都查询放到join_buffer中
从t2表中读取数据，并将每条数据与join_buffer中的数据进行比较，符合条件则将其放到我们的结果集当中，当全表扫描t2结束的时候算法也就停止了

整个过程需要扫描的记录行数是1100条，需要进行比较1000*100次，可见性能是比NLJ算法差的，而且还需要在内存中开辟join_buffer

这里还有一点需要注意，我们的join_buffer是固定大小的，倘若我们驱动表非常大，那么可能join_buffer中放不下这么多数据，此时就需要分多次将驱动表中的数据读取到join_buffer中进行比较，比如join_buffer中只能存放M条数据时，算法流程如下：

将驱动表中符合条件的前M条数据读取到join_buffer中
将被驱动中的所有数据与join_buffer中的数据进行一一比较，符合条件则放入到结果集当中
待着M条数据比较完以后将join_buffer清空，然后再读取驱动表中后面M条数据到join_buffer中进行比较直到驱动表中数据全部读取完毕。

2 排序语句

这里我们同样用上面的表t1为例子来说明排序语句的执行流程，排序算法主要分为两种，分别是全字段排序，rowId排序和利用索引的排序，三者的介绍如下：

2.1 全字段排序

当我们执行以下语句：

SELECT a, b FROM t1 ORDER BY b;

对该语句进行explain后结果如下：

在Extra字段中可以看到是Using FileSort，也就是说用到了临时文件或者sort_buffer排序

上面语句的执行流程如下：

首先在内存中开辟一块sort_buffer用于排序
将我们要查询的字段a, b放入到我们的sort_buffer中
在sort_buffer中对b字段进行排序
返回最终的排序结果

这里注意当我们需要排序的字段非常多的时候就需要用到临时文件而不是内存中的sort_buffer了，我们首先将数据分配到不同的临时文件中，在临时文件中排序后再将临时文件合并就得到了我们的最终结果

2.2 rowid排序

当时使用全字段排序的时候，我们需要将要查询的字段全部都放入到我们的sort_buffer中，当放入的字段总长度过长的时候就会使用rowid排序，该算法的执行流程如下：

将我们要查询的数据的排序字段和字段的主键放入到sort_buffer中
在sort_buffer中对排序字段进行排序
对排序好的字段，将每一条取出后根据id到聚簇索引中查找所需的数据信息

这种算法的性能明显较差，我们可以看到我们首先需要查表将排序字段放入到sort_buffer，待sort_buffer中排序完毕以后还需要再在sort_buffer中拿到主键后再查一次表，差一条记录需要执行两次B+树的搜索，所以性能比全字段排序差

2.3 利用索引排序

我们直到索引本身就是有序的，若我们的排序字段就是索引，比如我们执行以下语句：

SELECT a, id FROM t1 ORDER BY a;

对其explain后的结果如下：

可以看到这时候就没有using filesort了而是using index，说明只用到了覆盖索引

但也并不是说order by后面是一个索引字段就一定会用到索引，比如我们执行以下语句：

SELECT a, b FROM t1 ORDER BY a;

对其explain后得到的结果如下：

可以看到还是使用到了using filesort，这里个人认为还是使用using filesort的原因是因为即使我们使用索引的有序性，那么我们也只能在二级索引a上拿到a和id字段，这样的化我们还得再执行回表操作来获得我们的a,b字段，我们的查询优化器认为使用索引需要回表的成本要比全字段排序使用filesort的成本要高，所以最终还是使用了全字段排序的方法执行排序。

3 分组语句

来看下面这个分组语句，这个语句的语义是在t2中查询数据并按照b字段分组，然后再在根据字段b进行排序

SELECT * FROM t2 GROUP BY b

对其进行explain后的结果如下：

可以看到用到了排序和临时表，这个语句的执行流程如下：

首先在内存中创建内存临时表
将要查询的字段全部放入到内存临时表中
然后再sort_buffer中根据字段b进行排序后返回结果集

分组语句也是可以通过索引来进行优化的，比如执行以下语句：

SELECT * FROM t2 GROUP BY a

对该语句执行explain后的结果如下：

可以看到已经没有用到临时表和排序了，因为字段a上是有索引的，已经是排序好了的，从这里我们也可以看到使用临时表的成本是非常大的，我们前面的order by a语句仍然是使用filesort，这是因为回表的成本更高，而这里的group by语句选择了索引+回表，可见查询优化器认为采用临时表的成本比回表的成本还要高。所以我们平时在使用group by语句的时可以从建立索引来进行优化。