解决 MySQL ORDER BY 导致 Using filesort：使用 STRAIGHT_JOIN 控制驱动表

问题背景：关联查询中的性能瓶颈

在处理多对多关系表（如 post 与 post_tag）时，常见的查询语句如下：

SELECT post.*
FROM post
INNER JOIN post_tag ON post.id = post_tag.post_id
WHERE post.status = 1 AND post_tag.tag_id = 123
ORDER BY post.created DESC
LIMIT 100

执行该 SQL 并通过 EXPLAIN 查看执行计划时，可能会发现以下现象：

+----------+---------+-------+-----------------------------+
| table    | key     | rows  | Extra                       |
+----------+---------+-------+-----------------------------+
| post_tag | tag_id  | 71220 | Using where; Using filesort |
| post     | PRIMARY |   1   | Using where                 |
+----------+---------+-------+-----------------------------+

虽然 post_tag 表数据量相对较小，但优化器选择了它作为驱动表。由于排序字段 created 位于被驱动表 post 中，且不在连接条件范围内，导致必须使用外部排序（Using filesort），这通常意味着性能较差。

解决方案：使用 STRAIGHT_JOIN 强制驱动表

要解决此问题，核心思路是确保包含排序字段的表成为驱动表。我们可以通过 STRAIGHT_JOIN 来强制 MySQL 按照指定的顺序连接表：

SELECT post.*
FROM post
STRAIGHT_JOIN post_tag ON post.id = post_tag.post_id
WHERE post.status = 1 AND post_tag.tag_id = 123
ORDER BY post.created DESC
LIMIT 100

再次执行 EXPLAIN，观察执行计划的变化：

+----------+----------------+--------+-------------+
| table    | key            | rows   | Extra       |
+----------+----------------+--------+-------------+
| post     | status_created | 119340 | Using where |
| post_tag | post_id        |      1 | Using where |
+----------+----------------+--------+-------------+

关键变化：

1. 驱动表改变： post 表变成了第一行显示的驱动表。
2. 消除文件排序： 由于排序字段 created 现在位于驱动表中，且使用了合适的索引 status_created，不再出现 Using filesort。
3. 行数对比： 虽然 post 表扫描的行数（119340）大于之前的 post_tag 表（71220），但由于避免了昂贵的文件排序操作，整体查询效率得到提升。

原理分析：为什么优化器会“选错”？

MySQL 优化器在处理多表连接时，默认的启发式策略通常是：选择返回结果集更小的表作为驱动表。

• 默认逻辑： 在上述例子中，根据索引统计，post_tag 通过 tag_id=123 过滤的结果集约为 71,220 行；而 post 通过 status=1 过滤的结果集约为 119,340 行。
• 副作用： 优化器认为小结果集驱动更快，于是选了 post_tag。但这忽略了后续 ORDER BY 的成本。当排序字段不在驱动表中时，MySQL 需要将关联后的临时结果集进行全量排序，开销巨大。
• STRAIGHT_JOIN 的作用： 这是一个非标准的 SQL 语法提示，它告诉优化器严格按照从左到右的顺序连接表。通过强制 post 作为驱动表，我们让排序操作可以在内存或磁盘索引上高效完成，从而绕过了优化器的次优决策。

注意事项与建议

虽然 STRAIGHT_JOIN 能有效解决特定的性能瓶颈，但在日常开发中建议遵循以下原则：

1. 慎用奇技淫巧： STRAIGHT_JOIN 破坏了 SQL 的标准性和可移植性，属于特定场景下的优化手段。
2. 优先优化索引： 大多数情况下，确保 WHERE、JOIN 和 ORDER BY 涉及的列都有合适的复合索引，优化器就能选出最佳计划。
3. 验证执行计划： 每次优化前务必使用 EXPLAIN 确认驱动表和索引使用情况，不要仅凭经验猜测。

以上就是通过 STRAIGHT_JOIN 解决 MySQL 慢查询的详细内容，更多关于 MySQL 性能优化的资料请关注本站其它相关文章！