MySQL straight_join 優化實戰：強制驅動表順序解決檔案排序瓶頸

問題現場

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(practicetime);

• 執行時間：1 分 12 秒
• EXPLAIN：a 表 type=ALL 掃 41 萬行 + Using filesort

優化演進路徑

步驟 1：調整 a 表索引為覆蓋索引

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

• 掃描模式變 type=index，但仍需排序 38 萬行，耗時微降

步驟 2：STRAIGHT_JOIN 強制 b 表為驅動表

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

• 驅動表 b 過濾後 4.2 萬行，關聯 a 表僅 38 行/值
• 耗時：2.56 秒（質的飛躍）

步驟 3：b 表加覆蓋索引消除 Using where

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

• 掃描模式 type=index，Using index
• 最終耗時：1.43 秒

執行計劃對比總結

核心心法

1. STRAIGHT_JOIN 相當於 Oracle USE_NL + LEADING，僅影響連接順序
2. 驅動表選擇原則：過濾後行數最少、且能利用索引關聯被驅動表
3. 覆蓋索引雙刃劍：減少回表但可能增加索引掃描量，需配合驅動表策略
4. 生產環境慎用 STRAIGHT_JOIN，優先透過統計資訊、索引設計引導優化器