MySQL优化实战：使用straight_join强制驱动表加速慢查询

问题分析与背景

Oracle中的HINT机制（如LEADING或USE_NL）允许DBA干预连接方式，而MySQL默认仅支持嵌套循环（Nested Loops）连接。当优化器计算错误时，它可能会选择数据量巨大的一方作为驱动表，这会导致被驱动表需要被反复查找，且产生大量的中间结果集用于后续排序。
风险提示：在生产环境直接修改表结构或添加/删除索引前，务必备份相关数据及定义脚本，并在业务低峰期进行操作。

第1步：复现慢查询与查看执行计划

首先引入一条执行极其缓慢的业务SQL。该查询涉及两张表：test_log（行为日志，约41万行）和 USER（用户表）。目标是从日志中统计符合条件的独立用户数量并按时间分组。

原始查询语句

SELECT DATE(practicetime) date_time, COUNT(DISTINCT a.userid) people_rows 
FROM test_log a, USER b 
WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4 
GROUP BY DATE(praissetime); 

初始执行计划分析

执行 EXPLAIN 命令观察其逻辑过程：

mysql> EXPLAIN SELECT ... GROUP BY DATE(practicetime)\G; 
*************************** 1. row *************************** 
id: 1 
table: a (alias for test_log) 
type: ALL （全表扫描！） 
rows: 416782 
Extra: Using filesort 
... 
2 rows in set (0.00 sec)

可以看到，优化器将 test_log（别名a）作为驱动表进行全表扫描，并进行了文件排序。这通常是灾难性的起点。

第2步：初步索引优化尝试

查看 test_log 表的索引结构，发现存在一个单独的 userid 索引。

为了提高效率，我们对该表添加了联合索引，利用“覆盖索引”原则减少回表操作：

ALTER TABLE test_log DROP INDEX ix_test_log_userid, ADD INDEX ix_test_log_userid(userid, practicetime); 

再次查看执行计划，驱动表依然还是 a（全扫描+filesort）。虽然物理索引更紧凑了，但扫描基数未变。实际运行SQL后，查询耗时约为 72秒，并未从根本上解决问题。

第3步：引入 STRAIGHT_JOIN 强行调整驱动表

既然 USER 表的数据量相对较小（约4.2万行），应当将其作为驱动表来过滤数据。我们需要使用 STRAIGHT_JOIN 关键字强制指定从左到右的连接顺序：

mysql> EXPLAIN SELECT ... FROM USER b STRAIGHT_JOIN test_log a WHERE ... \G; 
*************************** 1. row *************************** 
id: 1 
table: b (alias for USER) 
type: ALL 
rows: 42806 
Extra: Using where; Using temporary; Using filesort 
*************************** 2. row *************************** 
id: 1 
table: a (alias for test_log) 
type: ref 
ref: user_app_test.b.userid 
rows: 38 
Extra: Using index

关键变化： 1. 现在是小表 USER 先做全表扫描（虽然仍是ALL，但总量远小于前者）。
2. 在匹配到大表 test_log 时，类型变为 ref（通过索引查找单行），每次只需查找约38次左右即可定位。

性能提升效果： 加上强制连接顺序后，查询执行时间骤降至 2.56秒，实现了质的飞跃。

第4步：深度优化 - 消除额外的文件排序

虽然速度已经很快，但检查新的执行计划，发现小表 USER 这一步依然存在 Using where; Using temporary; Using filesort。这意味着MySQL仍在内存或磁盘中对过滤后的结果集进行排序，这可以通过进一步的覆盖索引来消除。

添加复合索引

由于SQL条件中包含 b.isfree=0 和 LENGTH(b.username)>4，我们将这两个字段加入联合索引：

ALTER TABLE user DROP INDEX ix_user_username, ADD INDEX ix_user_username(username, isfree); 

此时再查看最终执行计划：

*************************** 1. row *************************** 
table: b 
type: index 
key: ix_user_username 
Extra: Using index; Using temporary; Using filesort 
(注：由于length()函数特性完全避免using where较难，但避免了回表，部分场景下可进一步优化为纯index scan)

最终的实测结果为 1.43秒。相比最初的72秒，整体提升了约50倍。

总结

本次优化主要包含两个核心手段：
- 使用 STRAIGHT_JOIN 确保小表驱动大表，降低主循环的嵌套层级。
- 构建符合查询条件的 (字段1, 字段2) 复合覆盖索引，最大限度减少磁盘I/O和回表。

以上就是MySQL优化中使用straight_join技巧处理驱动表问题的详细内容，更多关于MySQL多表关联优化的资料请关注本站其它相关文章！