社区首页 >专栏 >第 52 期：根据 EXPLAIN EXTRA 栏提示进行优化（四）

第 52 期：根据 EXPLAIN EXTRA 栏提示进行优化（四）

爱可生开源社区

发布于 2025-03-13 13:38:43

5500

代码可运行

文章被收录于专栏：爱可生开源社区爱可生开源社区

运行总次数：0

代码可运行

作者：杨涛涛，爱可生技术专家。

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

1Using filesort

Using filesort 是 MySQL 执行计划 EXTRA 栏里最为常见的提示之一，表示 MySQL 要执行这条 SQL 必须要做一个排序的消耗。

一般出现在强制按照某个字段排序，并且没有使用索引字段的有序性特征场景。

举个例子：

(mysql)>desc select * from t1 order by r1 limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 57918
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

但并不是 EXTRA 栏提示 Using filesort 就代表必须优化，也得看业务场景。那如何判断呢？

对于只在内存里排序的业务场景，如果资源管够，同时并发不是很高，这种其实大可不必优化；而对于 内存不足，需要排序的记录溢出到磁盘上的业务场景，则必须优化。比如：排序字段加索引、或者修改 SQL 变为根据主键排序等等，必要时可以考虑把此类操作挪到应用端来处理。

2Using temporary

Using temporary 也是 MySQL 执行计划 EXTRA 栏里最常见的提示之一，表示执行这条 SQL 语句必须要隐式创建一张临时表来保存记录的中间状态。

一般当 SQL 语句里有 GROUP BY ，并且没有合适的索引时会有这个提示。

举个例子：

(mysql)>desc select count(log_date) from t1 group by r1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
...
         rows: 57918
     filtered: 100.00
        Extra: Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)

可以看到，type 栏为 ALL， Extra 栏里提示使用了临时表以及排序，这是因为 MySQL 执行 GROUP BY 默认需要排序，但是分组字段又没有合适的索引。

SQL：select count(log_date) from t1 group by r1

等价于

SQL：select count(log_date) from t1 group by r1 order by r1

MySQL 8.0 的 GROUP BY 默认不排序，也就是说不会有隐式 ORDER BY 语句。针对这种简单场景，给 GROUP BY 字段加索引后，这个提示就会消除。

加完索引后的执行计划：

(mysql)>desc select count(log_date) from t1 group by r1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: index
possible_keys: idx_r1
          key: idx_r1
      key_len: 5
          ref: NULL
         rows: 57918
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

以上例子都是最简单的 SQL，稍微复杂的 SQL 就不会是只加索引就可以消除临时表的。比如分组字段和排序字段不一样，或者说分组需要计算的临时列等等。这些场景得需要些复杂的优化措施，后期有专门的篇章来细讲这些内容，本篇就不深入了。

3Using join buffer

Using join buffer 表示 MySQL 对于两表做 JOIN 的 SQL ，JOIN KEY 不能用到索引时申请的一块内存区域。这块内存区域在 MySQL 里分别对应几种 JOIN 算法：

Using join buffer (Block Nested Loop)

Using join buffer (Block Nested Loop) 是块嵌套循环 JOIN，也是最慢的 JOIN。

举个例子：表 t1 和表 t2 做 INNER JOIN，JOIN KEY 上没有任何索引：

(mysql)>desc select * from t1 a join t2 b using(r2)\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: b
  partitions: NULL
        type: ALL
...
        rows: 9986
    filtered: 100.00
       Extra: NULL
*************************** 2. row ***************************
          id: 1
 select_type: SIMPLE
       table: a
  partitions: NULL
        type: ALL
...
        rows: 57918
    filtered: 10.00
       Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)

由于 JOIN KEY 没有索引，MySQL 对于这样的 JOIN 直接走最简单的嵌套循环，默认小表驱动大表，我们应该避免出现这样的场景（MySQL 最新版本对于这样的场景已经全部转为 HASH JOIN）。

Using join buffer (Batched Key Access)

Using join buffer (Batched Key Access) 是块嵌套循环的升级版，BKA 算法，一般和 MRR 一起用。

主要用在被驱动表的 JOIN KEY 上有索引的场景。利用索引的有序性，对表数据的随机 IO 转换为顺序 IO 的策略。

下面例子是对表 t1 字段 r2 加了索引后的执行计划：

(mysql)>desc select * from t1 a join t2 b using(r2)\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: b
  partitions: NULL
        type: ALL
...
        rows: 9986
    filtered: 100.00
       Extra: Using where
*************************** 2. row ***************************
          id: 1
 select_type: SIMPLE
       table: a
  partitions: NULL
        type: ref
possible_keys: idx_r2
         key: idx_r2
     key_len: 5
         ref: ytt.b.r2
        rows: 616
    filtered: 100.00
       Extra: Using join buffer (Batched Key Access)
2 rows in set, 1 warning (0.01 sec)

Using join buffer (hash join)

Using join buffer (hash join) 也是块嵌套循环的升级版，哈希连接算法，MySQL 版本需要大于 8.0 。

下面示例依然是表 t1 和 t2 做连接，但是替换了JOIN KEY：

(mysql)>desc select * from t1 join t2 using(r3)\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: t2
  partitions: NULL
        type: ALL
...
        rows: 1000
    filtered: 100.00
       Extra: NULL
*************************** 2. row ***************************
          id: 1
 select_type: SIMPLE
       table: t1
  partitions: NULL
        type: ALL
...
        rows: 101745
    filtered: 10.00
       Extra: Using where; Using join buffer (hash join)
2 rows in set, 1 warning (0.00 sec)