設為首頁 加入收藏
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
您现在的位置： 程式碼之家 >> 數據庫知識 >  >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 查詢優化核心技巧：EXPLAIN 深度解析與 20+ 實戰優化原則

     編輯：MySQL 綜合教學

EXPLAIN：查詢優化的「X 光機」

EXPLAIN 是分析 SQL 執行計畫的核心工具，能揭示優化器如何存取表、使用索引、連接順序等關鍵資訊。

輸出欄位完整解讀

欄位含義關鍵判讀
idSELECT 識別序號相同 id 從上往下執行；id 越大優先級越高
select_type查詢類型見下文詳細分類
table存取的表<derivedN> 衍生表、<unionM,N> 聯合結果
type存取策略核心指標，由好到壞：system > const > eq_ref > ref > range > index > ALL
possible_keys可能用到的索引為 NULL 表示無可用索引，需檢查 WHERE/ON 條件
key實際使用的索引可用 FORCE/USE/IGNORE INDEX 干預
key_len索引長度越短越好（不失精度前提），推斷複合索引用到第幾列
ref比較引用顯示哪欄或常數與 key 配合使用
rows預估掃描行數InnoDB 為估算值，越小越好
Extra額外資訊Using index（覆蓋索引）、Using where、Using filesort（需優化）、Using temporary（需優化）

select_type 完整分類

  • SIMPLE：無 UNION/子查詢的簡單 SELECT
  • PRIMARY：最外層查詢
  • UNION：UNION 中第二或後續 SELECT
  • DEPENDENT UNION：依賴外部查詢的 UNION
  • UNION RESULT：UNION 結果合併
  • SUBQUERY：子查詢第一個 SELECT
  • DEPENDENT SUBQUERY：依賴外部查詢的子查詢
  • DERIVED：FROM 子查詢產生的衍生表（物化到臨時表）

type 存取策略效能階梯

system > const > eq_ref > ref > fulltext > ref_or_null > 
index_merge > unique_subquery > index_subquery > range > index > ALL

底線：至少達到 range，最佳達到 ref 或 eq_ref。

20+ 實戰優化鐵律

索引與條件設計

  1. 同類型比較：避免隱式轉換導致索引失效（如 varchar = 123）
  2. 索引列孤立：條件中索引列不參與運算/函數，保持「乾淨」
  3. 拒絕前導模糊：LIKE '%abc%' 不走索引，LIKE 'abc%' 可走
  4. 數值優於字串：同義場景優先用整數（如狀態碼、枚舉值）
  5. 最小長度原則：定長用 CHAR，變長用 VARCHAR，長度剛好夠用
  6. NOT NULL 優先：減少 NULL 判斷開銷，利於索引統計
  7. 善用 ENUM：內部儲存為整數，處理速度快；PROCEDURE ANALYSE() 可建議轉型

查詢寫法優化

  1. 避免 SELECT *：只取需要欄位，減少 IO、利用覆蓋索引
  2. COUNT(*) 謹慎用：InnoDB 無鎖計數但需全表掃描，考慮計數表或緩存
  3. 優先 GROUP BY 而非 DISTINCT：某些版本 GROUP BY 更優
  4. WHERE/GROUP BY/ORDER BY 用索引列
  5. 索引簡單化：避免多索引包含同一列，增加維護成本
  6. 強制索引：優化器選錯時用 USE INDEX
  7. LIMIT M, N 深分頁陷阱：大偏移量極慢，改用游標/延遲關聯
  8. ORDER BY RAND() 禁用：全表掃描+排序，改用應用層隨機
  9. 子查詢轉 JOIN：特別是 IN (SELECT...) → EXISTS 或 JOIN
  10. INSERT ... ON DUPLICATE KEY UPDATE 替代 SELECT+UPDATE
  11. 避免 MAX() 無索引：用索引列 + ORDER BY DESC LIMIT 1

架構與維運層面

  1. 定期 OPTIMIZE TABLE：碎片整理，尤其變長列頻繁更新刪除的表
  2. 慎用 BLOB/TEXT 索引：前綴索引長度權衡
  3. 持久連接減少握手開銷
  4. 慢查詢日誌 + EXPLAIN 例行化審計
  5. 測試環境模擬生產數據量驗證執行計畫

進階：覆蓋索引與 Index Condition Pushdown (ICP)

當 Extra 顯示 Using index 時，查詢僅掃描索引樹，無需回表，效能提升數量級。設計複合索引時，將 SELECT、WHERE、ORDER BY 涉及的列按「等值篩選 → 範圍篩選 → 排序」順序排列，最大化覆蓋索引命中率。

結語

查詢優化非一次性動作，而是「監控 → 分析 → 優化 → 驗證」的持續循環。掌握 EXPLAIN 讀圖能力，內化上述 20+ 原則，配合慢查詢日誌例行審計，才能在業務增長中守住資料庫效能底線。

  1. 上一篇文章：
  2. 下一篇文章：
MySQL 綜合教學
MySQL MyISAM 引擎常見故障診斷與修復：表損壞、大文件限制、磁碟空間、多實例部署 MySQL MyISAM 引擎常見故障診斷與修復：表損壞、大文件限制、磁碟空間、多實例部署

MyISAM 存儲引擎四大高發問題根因分析與標準化修復方案

MySQL 修改預設字元集為 UTF-8：my.cnf 永久生效與命令列臨時修改 MySQL 修改預設字元集為 UTF-8：my.cnf 永久生效與命令列臨時修改

兩種修改方式 方法一：修改設定檔（永久生效，推薦） 方法二：
Windows 10 安裝 MySQL 5.7 完整圖文教學：從下載到環境設定 Windows 10 安裝 MySQL 5.7 完整圖文教學：從下載到環境設定

Windows 10 安裝 MySQL 5.7 完整圖文教學
MySQL 基礎知識點總覽：從術語到常用命令 MySQL 基礎知識點總覽：從術語到常用命令

核心術語速查 術語說明 Database (資料庫)
MySQL ALTER TABLE 修改表結構完整指南：新增欄位、修改類型、索引操作與備份還原 MySQL 主從複制原理深度解析：三線程協作、Binlog 與 Relay Log 流程圖解 PHP mysql_fetch_array() expects parameter 1 to be resource 錯誤根因與排查口訣 Linux 部署 MySQL 5.7：tar.gz 二進制包解壓安裝法（精簡版） MySQL 資料表損壞修復實戰：從 myisamchk 到 REPAIR TABLE 分級操作指南 MyCat 日志深度解析：啟動流程、路由邏輯、連接池同步、數據合並全鏈路追蹤 MySQL 開啟遠端連線完整步驟 (修改權限 + 解除綁定)
欄目導航
  • MySQL 入門
  • SQL 基礎
  • MySQL 資料庫相關知識
  • MariaDB 資料庫
  • MySQL 綜合教學
    • Copyright © 程式碼之家 All Rights Reserved