MySQL SQL 語句優化進階：索引、WHERE、JOIN、LIMIT 與 DML 實戰技巧

MySQL SQL 語句優化進階實戰指南

接續基礎優化，本文深入剖析索引應用、WHERE 子句重寫、LEFT JOIN 優化、LIMIT 分頁技巧以及 INSERT/UPDATE/DELETE 寫入優化的核心策略。

一、索引使用核心法則

MySQL 索引（PRIMARY、UNIQUE、INDEX）均基於 B+ 樹存儲，主要服務於：

• 快速定位 WHERE 條件記錄
• 加速 JOIN 關聯查詢
• 優化 MAX()/MIN() 聚合函數
• 支援 ORDER BY/GROUP BY 避免文件排序
• 覆蓋索引：僅掃描索引樹即可滿足查詢，避免回表

維護成本：寫入操作（INSERT/UPDATE/DELETE）需同步維護索引，過多索引會拖慢寫入。使用 myisamchk --analyze 分析表統計信息，myisamchk --sort-index --sort-records=1 可按索引順序重組數據提升範圍查詢速度。

二、WHERE 子句自動優化規則

MySQL 查詢優化器會自動執行以下轉換：

1. 消除冗餘括號：((a AND b) AND c) → a AND b AND c
2. 常量傳播與折疊：a=5 AND b>5 → 常量預計算
3. 移除恆真/恆假條件：(b=5 AND 5=5) OR (b=100 AND 2=3) → b=5
4. 常量表優先讀取：零行或單行表、主鍵/唯一鍵等值查詢的表優先訪問
5. JOIN 順序優化：嘗試所有關聯順序組合選擇最優執行計劃
6. 臨時表觸發條件：ORDER BY 與 GROUP BY 列不一致、涉及非驅動表列、使用 SQL_SMALL_RESULT 時創建內存臨時表
7. 索引覆蓋判斷：掃描行數超過 30% 時優化器傾向全表掃描
8. HAVING 後置過濾：輸出前過濾不符合 HAVING 條件的行

三、LEFT JOIN 優化實現原理

MySQL 執行 A LEFT JOIN B 時：

1. B 依賴 A，A 依賴除 B 外的所有 LEFT JOIN 條件表
2. 所有 LEFT JOIN 條件下推至 WHERE 子句
3. 按依賴順序讀表，檢測循環依賴報錯
4. 執行標準 WHERE 優化
5. 若 A 滿足 WHERE 但 B 無匹配行，生成全 NULL 的虛擬行
6. 關鍵優化：WHERE ... IS NULL 反向查找時，找到首個匹配行即停止掃描

四、LIMIT 分頁優化技巧

• LIMIT 1 等效於「找到即停」，大表單行查詢極快
• LIMIT N OFFSET M 配合 ORDER BY：找到第 N 行即終止排序，避免全表排序
• LIMIT + DISTINCT：去重達到限制即停止
• 客戶端接收首批數據後，服務端放棄後續查詢
• LIMIT 0 快速返回空集合，可用於獲取表結構
• 臨時表大小按 LIMIT 估算內存需求

五、INSERT 批量寫入優化

單行插入耗時分解：連接(3) + 發送查詢(2) + 分析(2) + 寫入數據(1×大小) + 寫入索引(1×索引) + 關閉(1)

加速策略：

1. 多值 INSERT：INSERT INTO t VALUES(...),(...),(...) 單語句插入多行，減少網絡往返與解析開銷
2. INSERT DELAYED：異步寫入，適用於日誌類高併發寫入場景
3. MyISAM 並發插入：無刪除空洞時，SELECT 與 INSERT 並行執行
4. LOAD DATA INFILE：批量導入比 INSERT 快 20 倍，首選方案
5. 鎖表批量提交：LOCK TABLES t WRITE; INSERT...; UNLOCK TABLES 將索引緩存一次性刷盤，整體提速 40%+。長事務建議每 1000 行釋放鎖平衡併發
6. 擴大 key_buffer：加速索引緩存刷盤

六、UPDATE/DELETE 速度優化

• UPDATE：耗時與更新列數據量、涉及索引數成正比。延遲修改、鎖表批量提交可顯著提升
• DELETE：耗時與索引數成正比。增大索引緩存、使用 TRUNCATE TABLE 替代全表刪除（無事務、無觸發器、極快）