MySQL SQL 优化

一、执行流程

1.1 SELECT 语句内部的执行步骤

一条完整的 SELECT 语句内部的执行顺序是这样的：

  1. FROM 子句组装数据（包括通过 ON 进行连接）
  2. WHERE 子句进行条件筛选
  3. GROUP BY 分组
  4. 使用聚集函数进行计算
  5. HAVING 筛选分组
  6. 计算所有的表达式
  7. SELECT 的字段
  8. ORDER BY 排序
  9. LIMIT 筛选

二、关键字的底层实现

2.1 JOIN 的实现方式

2.1.1 Index Nested-Loop Join（索引嵌套循环连接）

（待补充内容）

优化方式：

MRR（Multi-Range Read）优化

（待补充内容）

2.1.2 Simple Nested-Loop Join（简单嵌套循环连接）

（待补充内容）

2.1.3 Block Nested-Loop Join（块嵌套循环连接）

（待补充内容）

优化方式：

（待补充内容）

2.1.4 Hash Join（哈希连接）

（待补充内容）

2.2 ORDER BY 的实现方式

2.2.1 全字段排序

（待补充内容）

2.2.2 rowid 排序

（待补充内容）

2.2.3 对比总结

（待补充内容）

三、慢 SQL 优化

3.1 常见优化方法

（待补充内容）

3.2 EXPLAIN 分析

（待补充内容）

3.3 内部临时表

3.3.1 临时表的使用场景

sort buffer、内存临时表和 join buffer，这三个数据结构都是用来存放语句执行过程中的中间数据，以辅助 SQL 语句的执行的。其中，我们在排序的时候用到了 sort buffer，在使用 join 语句的时候用到了 join buffer。

一个常见的使用临时表的例子是 group by，我们来看一下这个语句：

1
select id%10 as m, count(*) as c from t1 group by m;

这个语句的逻辑是把表 t1 里的数据，按照 id%10 进行分组统计，并按照 m 的结果排序后输出。

3.3.2 EXPLAIN 结果分析

在 Extra 字段里面，我们可以看到三个信息：

  • Using index：表示这个语句使用了覆盖索引，选择了索引 a，不需要回表
  • Using temporary：表示使用了临时表
  • Using filesort：表示需要排序

3.3.3 执行流程

这个语句的执行流程是这样的：

  1. 创建内存临时表：表里有两个字段 m 和 c，主键是 m

  2. 扫描表 t1 的索引 a：依次取出叶子节点上的 id 值，计算 id%10 的结果，记为 x：

    • 如果临时表中没有主键为 x 的行，就插入一个记录 (x,1)
    • 如果表中有主键为 x 的行，就将 x 这一行的 c 值加 1

  3. 遍历完成后：再根据字段 m 做排序，得到结果集返回给客户端

这个例子里由于临时表只有 10 行，内存可以放得下，因此全程只使用了内存临时表。

3.3.4 内存临时表的限制

但是，内存临时表的大小是有限制的，参数 tmp_table_size 就是控制这个内存大小的，默认是 16M。

示例：

把内存临时表的大小限制为最大 1024 字节，并把语句改成 id%100，这样返回结果里有 100 行数据。但是，这时的内存临时表大小不够存下这 100 行数据，也就是说，执行过程中会发现内存临时表大小到达了上限（1024 字节）。

那么，这时候就会把内存临时表转成磁盘临时表，磁盘临时表默认使用的引擎是 InnoDB。

四、总结

4.1 SQL 执行顺序

执行顺序 子句 说明
1 FROM 组装数据，包括 JOIN 连接
2 WHERE 条件筛选
3 GROUP BY 分组
4 聚集函数 计算聚合结果
5 HAVING 筛选分组
6 表达式 计算所有表达式
7 SELECT 选择字段
8 ORDER BY 排序
9 LIMIT 限制结果集

4.2 JOIN 方式对比

JOIN 类型 特点 适用场景
Index Nested-Loop Join 使用索引查找 被驱动表有索引
Simple Nested-Loop Join 简单嵌套循环 小表连接
Block Nested-Loop Join 使用 join buffer 被驱动表无索引
Hash Join 使用哈希表 MySQL 8.0.18+

4.3 临时表使用场景

场景 临时表类型 触发条件
GROUP BY 内存临时表 数据量小于 tmp_table_size
GROUP BY 磁盘临时表 数据量超过 tmp_table_size
ORDER BY sort buffer 排序字段较少
JOIN join buffer 被驱动表无索引

4.4 优化建议

  1. 理解执行顺序：

    • 了解 SQL 的执行顺序有助于编写高效的查询
    • WHERE 在 GROUP BY 之前执行，尽量在 WHERE 中过滤数据
    • HAVING 在 GROUP BY 之后执行，用于过滤分组结果

  2. JOIN 优化：

    • 优先使用 Index Nested-Loop Join，确保被驱动表有索引
    • 小表驱动大表，减少循环次数
    • 考虑使用 Hash Join（MySQL 8.0.18+）

  3. 临时表优化：

    • 避免使用临时表，尽量使用索引
    • 合理设置 tmp_table_size 参数
    • 注意内存临时表转磁盘临时表的性能影响

  4. 排序优化：

    • 尽量使用索引排序，避免 filesort
    • 减少排序字段的数量和大小
    • 合理设置 sort_buffer_size

  5. 使用 EXPLAIN 分析：

    • 定期使用 EXPLAIN 分析慢查询
    • 关注 Extra 字段的提示信息
    • 优化索引使用，避免全表扫描

五、扩展阅读

5.1 相关参数配置

临时表相关：

1
2
3
4
5
-- 查看临时表大小限制
SHOW VARIABLES LIKE 'tmp_table_size';

-- 设置临时表大小（单位：字节）
SET tmp_table_size = 16777216;  -- 16MB

排序相关：

1
2
3
4
5
-- 查看排序缓冲区大小
SHOW VARIABLES LIKE 'sort_buffer_size';

-- 设置排序缓冲区大小
SET sort_buffer_size = 2097152;  -- 2MB

JOIN 相关：

1
2
3
4
5
-- 查看 join buffer 大小
SHOW VARIABLES LIKE 'join_buffer_size';

-- 设置 join buffer 大小
SET join_buffer_size = 262144;  -- 256KB

5.2 性能监控

查看临时表使用情况：

1
2
-- 查看创建的临时表数量
SHOW STATUS LIKE 'Created_tmp%';

查看排序情况：

1
2
-- 查看排序操作统计
SHOW STATUS LIKE 'Sort%';

六、总结

MySQL SQL 优化是一个系统工程，需要从多个维度进行考虑：

  1. 执行流程：理解 SQL 的执行顺序，有助于编写高效的查询语句
  2. JOIN 优化：选择合适的 JOIN 方式，确保被驱动表有索引
  3. 临时表优化：避免使用临时表，合理配置相关参数
  4. 排序优化：尽量使用索引排序，减少 filesort 操作
  5. 监控分析：使用 EXPLAIN 和性能监控工具，及时发现和解决问题

通过系统的优化和持续的监控，可以显著提升 MySQL 的查询性能。