使用STRAIGHT_JOIN优化MySQL查询性能：实战案例分析

问题背景：优化器决策失误的代价

在复杂多表关联查询中，MySQL优化器可能选择低效执行路径：

/* 原始查询（优化器选择） */
EXPLAIN
SELECT ...
FROM medical_records
JOIN exam_requests ON medical_records.request_id = exam_requests.id
LEFT JOIN medical_teams ON exam_requests.team_id = medical_teams.id
ORDER BY final_result_time DESC, id DESC;

/* 执行计划关键指标 */
| Table           | Type  | Rows    | Extra                          |
|-----------------|-------|---------|--------------------------------|
| exam_requests   | index | 4,600   | Using temporary; Using filesort|
| medical_teams   | eq_ref| 1       |                                |
| medical_records | ref   | 39      |                                |

性能痛点：

• 临时表创建（Using temporary）
• 二级索引回表查询增加随机I/O
• 排序操作与临时表双重开销

排序时没有使用medical_records表的字段索引，所以我们强制使用medical_records表作为主表，就可以不创建临时表，走索引。

解决方案：STRAIGHT_JOIN强制连接顺序

/* 优化后查询 */
EXPLAIN
SELECT ...
FROM medical_records STRAIGHT_JOIN exam_requests  -- 强制连接顺序
  ON medical_records.request_id = exam_requests.id
LEFT JOIN medical_teams 
  ON exam_requests.team_id = medical_teams.id
ORDER BY final_result_time DESC, id DESC;

/* 优化后执行计划 */
| Table           | Type  | Rows     | Extra          |
|-----------------|-------|----------|----------------|
| medical_records | index | 206,180  | Using filesort |
| exam_requests   | eq_ref| 1        |                |
| medical_teams   | eq_ref| 1        |                |