一、慢 SQL 排查全流程

1. 開啟慢查詢日志：精準定位問題 SQL

慢查詢日志是定位性能問題的首要工具，通過記錄執行超時或未使用索引的 SQL，為優化提供依據。

配置步驟：

① 臨時啟用（生效至服務重啟）

sql

-- 開啟慢查詢日志
SET GLOBAL slow_query_log = ON;
-- 設置超時閾值（單位：秒，默認10秒，建議設為1秒）
SET GLOBAL long_query_time = 1;
-- 記錄未使用索引的查詢（即使執行時間未超時）
SET GLOBAL log_queries_not_using_indexes = ON;

② 永久生效（修改配置文件）
編輯 MySQL 配置文件（my.cnf/my.ini）：

ini

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log  # 日志存儲路徑
long_query_time = 1
log_queries_not_using_indexes = 1

③ 查看日志位置

sql

SHOW VARIABLES LIKE 'slow_query_log_file';

分析工具：

• mysqldumpslow（內置工具，命令行分析）：

  bash

  # 按執行時間排序，取最慢的10條SQL
  mysqldumpslow -s t -t 10 /var/log/mysql/slow.log  
  # 按訪問次數排序，取最頻繁的10條SQL
  mysqldumpslow -s c -t 10 /var/log/mysql/slow.log  
  

• pt-query-digest（Percona Toolkit，功能更強）：

  bash

  pt-query-digest /var/log/mysql/slow.log > slow_analysis.txt
  

2. 解析 EXPLAIN 執行計劃：洞察查詢邏輯

EXPLAIN通過可視化執行步驟，揭示 SQL 是否有效利用索引、掃描行數等關鍵信息。

核心字段解讀：

字段	含義與優化重點
type	連接類型（效率從高到低）：system?>?const?>?eq_ref?>?ref?>?range?>?index?>?ALL ** 警惕ALL（全表掃描），必須通過索引優化。
key	實際使用的索引，若為NULL表示未用索引，需檢查WHERE/JOIN條件是否命中索引。
rows	估算掃描行數，數值越小越好，全表掃描時可能等于表數據量。
Extra	-?Using filesort：需額外排序（優化：為ORDER BY字段加索引） -?Using temporary：使用臨時表（優化：簡化GROUP BY/DISTINCT邏輯） -?Using index：覆蓋索引（理想狀態，無需回表）。

示例分析：

sql

EXPLAIN SELECT u.name, o.order_date 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.age > 30 AND o.status = 'paid';

id	type	key	rows	Extra
1	range	idx_age	1000	Using where
1	ref	idx_user_id	5	Using where

結論：

• users表通過idx_age索引掃描 1000 行（范圍查詢），性能可接受；
• orders表通過user_id索引關聯，但status字段未用索引，建議創建復合索引(user_id, status)。

二、索引失效的六大典型場景

1. 全值匹配時順序錯誤（復合索引）

場景：復合索引(a, b, c)，查詢WHERE b=1或WHERE a=1 AND c=1。
原因：索引需按順序匹配，中間字段跳過則后續失效。
修復：查詢條件包含索引最左前綴（如a=1 AND b=1）。

2. 范圍查詢后字段未使用索引

場景：WHERE a > 10 AND b=20，若a是范圍查詢（>,?<,?BETWEEN），b字段的索引失效。
原因：MySQL 僅對第一個范圍字段使用索引，后續字段無法利用。
修復：對高頻查詢字段調整順序，如復合索引(b, a)。

3. 數據類型隱式轉換

場景：字段user_id為INT，查詢WHERE user_id = '123a'（字符串轉數字失敗）或WHERE phone='13812345678'但字段定義為INT。
原因：類型不匹配導致索引失效，轉為全表掃描。
修復：確保查詢條件與字段類型一致（避免字符串加引號查詢數字字段）。

4. 索引字段使用函數或表達式

場景：WHERE YEAR(create_time) = 2023?或?WHERE id + 1 = 5。
原因：對索引字段使用函數 / 計算，MySQL 無法命中索引。
修復：改寫為WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'。

5. OR 條件連接非索引字段

場景：WHERE id=1 OR name='Alice'，若name無索引，則整個條件退化為全表掃描。
原因：OR兩側字段必須都有索引才會生效，否則放棄索引。
修復：為name添加索引，或改用UNION拆分查詢。

6. 模糊查詢以通配符開頭

場景：WHERE name LIKE '%abc'?或?WHERE name LIKE '%abc%'。
原因：左模糊（%開頭）無法利用索引，僅右模糊（abc%）可命中。
修復：避免左模糊查詢，或使用全文索引（Full-Text Index）。

三、索引高級特性：下推與覆蓋

1. 索引下推（Index Condition Pushdown, ICP）

原理：MySQL 5.6 + 引入的優化，在索引遍歷過程中，直接過濾掉不滿足WHERE條件的記錄，減少回表次數。
示例：表users有索引(name, age)，查詢WHERE name LIKE 'A%' AND age=20。

• 無 ICP：通過name索引找到所有以 'A' 開頭的記錄，回表后再過濾age=20。
• 有 ICP：在索引層直接判斷age=20，僅對符合條件的記錄回表，減少 I/O。
  開啟方式：默認啟用，可通過SHOW VARIABLES LIKE 'optimizer_switch'查看index_condition_pushdown狀態。

2. 索引覆蓋（Covering Index）

定義：查詢所需的所有字段都包含在索引中，無需回表查詢原數據，是索引優化的終極目標。
示例：表orders有索引(user_id, status, order_time)，查詢SELECT status, order_time FROM orders WHERE user_id=1。

• 覆蓋索引：直接通過索引獲取數據，效率極高（Extra顯示Using index）。
• 非覆蓋索引：若索引缺少order_time，需回表查詢完整行數據。
  設計原則：為高頻查詢語句創建包含所有 SELECT 字段的復合索引，避免SELECT *。

四、最佳實踐總結

1. 慢 SQL 排查閉環：
   開啟慢日志 → 分析日志定位問題 SQL → 用EXPLAIN剖析執行計劃 → 針對性優化索引或查詢語句。
2. 索引設計三原則：
   • 最左前綴匹配：復合索引按查詢條件順序創建；
   • 覆蓋索引優先：減少回表，提升查詢速度；
   • 避免過度索引：索引過多會影響寫入性能（插入 / 更新 / 刪除）。
3. 工具鏈推薦：
   • 日志分析：pt-query-digest（全面統計）、mysqldumpslow（快速預覽）；
   • 可視化：MySQL Workbench（圖形化執行計劃）、Navicat（索引管理）。

通過系統化排查與索引優化，可顯著提升 MySQL 查詢性能，避免因慢 SQL 導致的系統瓶頸。

編輯

分享

在排查慢 SQL 時，如何確定慢查詢的閾值？

如何選擇合適的索引來優化查詢性能？

分享一些排查和優化 MySQL 慢 SQL 的實際案例