慢查詢的診斷流程：CPU 100%，但慢查詢日誌是空的

這是整條排查鏈的分岔點，而它決定了接下來要用完全不同的工具。跳過這一步直接去翻慢查詢日誌，是最常見的浪費時間方式。

兩條路完全不同

怎麼判斷是哪一種

-- 這一個數字就能分流：目前「正在執行」的執行緒數
SHOW GLOBAL STATUS LIKE 'Threads_running';

• 健康值：通常在個位數，經驗上不該長期超過 CPU 核心數的兩倍。
• 突然飆到幾十、上百：代表有東西塞住了——請求進得來、出不去。這是「全部變慢」的特徵。
• 正常，但某支 API 慢：那就是單句問題，走另一條路。

第三種情況：不是變慢，是變錯

還有一種容易被誤判成「慢」的症狀：使用者說「我剛改的資料不見了」。那多半不是效能問題，是讀寫分離的複本延遲（本章後面會講）。這種情況去查慢查詢日誌永遠查不到東西。

排查的總地圖

「系統變慢了」
  │
  ├─ Threads_running 正常 → 某支慢
  │    └→ digest / 慢查詢日誌 → EXPLAIN（ch01）
  │         → 估算失準？（ch02）／JOIN？（ch03）／排序分頁？（ch04）
  │
  ├─ Threads_running 飆高 → 有東西塞住
  │    ├→ 鎖等待？（ch05）
  │    ├→ Buffer Pool 命中率掉？（ch04 深分頁沖快取）
  │    ├→ 磁碟 I/O 打滿？
  │    └→ 都正常但 CPU 高 → 量的問題（本章失敗場景 → ch08）
  │
  └─ DB 指標全正常，但應用端很慢
       └→ 根本不是 DB：連線池、GC、網路、序列化

慢查詢日誌是最多人知道、也最多人設錯的工具。預設設定下它基本上不會記到任何東西。

SELECT @@slow_query_log, @@long_query_time;
-- 0, 10.000000    ← 沒開，而且門檻是 10 秒

合理的設定

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;        -- 依服務的 SLA 調，通常 0.2~1 秒
SET GLOBAL log_slow_admin_statements = ON;   -- ALTER/ANALYZE 這類也記（ch06 用得到）
SET GLOBAL log_slow_replica_statements = ON; -- 複本上的慢語句也記

兩個要小心的選項

• log_queries_not_using_indexes：聽起來很有用，但小表的全表掃描是完全正常的（ch02 講過），開了之後日誌會被那些無害的查詢刷爆，真正的問題反而被淹沒。要開的話務必配 min_examined_row_limit 設個門檻。
• long_query_time = 0（記錄全部）：診斷時很有用，但它本身有成本——每一句都要寫檔案，高 QPS 下會拖慢資料庫。只在短時間的診斷視窗開，開完記得關。

分析：不要用眼睛看

原始日誌不適合直接讀。用 pt-query-digest 聚合成報表：

pt-query-digest /var/log/mysql/slow.log | head -50

一句 5 秒、一天跑 1 次      → 總共 5 秒
一句 10 毫秒、一天跑 200 萬次 → 總共 5.5 小時   ← 該修的是這個

慢查詢日誌的根本限制

它只能記錄「單句超過門檻」的語句。所以下面這些它全部看不到：

• 一句 8 毫秒但被呼叫 240 萬次（本章的失敗場景）。
• 等鎖 40 秒的語句——其實會被記錄，但 rows_examined 是 1，用掃描列數排序時完全隱形（ch05 提過）。
• 連線池排隊——那發生在應用端，資料庫根本沒收到請求。

所以慢查詢日誌是必要但不充分的工具。真正的主力是下一節。

performance_schema 會把所有執行過的語句正規化成「指紋」（digest）後聚合統計——把參數值換成 ?，於是同一種查詢的一百萬次呼叫會被歸成一筆。

SELECT
  LEFT(digest_text, 80) AS query,
  count_star                       AS 次數,
  ROUND(sum_timer_wait/1e12, 1)    AS 總秒數,
  ROUND(avg_timer_wait/1e9, 2)     AS 平均毫秒,
  sum_rows_examined                AS 掃過列數,
  sum_rows_sent                    AS 回傳列數
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC          -- ← 總耗時，不是平均
LIMIT 10;

為什麼它比慢查詢日誌強

• 它看得到「不慢但很多」的查詢——依 sum_timer_wait 排序，那句 8 毫秒的 SQL 會自己浮到第一名。
• 它是即時的，不用等日誌輪替或跑分析工具。
• 它有效率指標：sum_rows_examined / sum_rows_sent 的比值就是「掃了多少、才回傳多少」。比值越大越可疑——比值 100 代表掃 100 列只回傳 1 列，那是索引或計畫的問題（ch01）。

sys schema：已經寫好的視圖

sys 是 performance_schema 的人類可讀版，直接給答案：

SELECT * FROM sys.statement_analysis LIMIT 10;              -- 綜合分析
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10; -- 全表掃描的（→ ch01）
SELECT * FROM sys.statements_with_sorting LIMIT 10;          -- 有排序的（→ ch04）
SELECT * FROM sys.statements_with_temp_tables LIMIT 10;      -- 用暫存表的（→ ch04）
SELECT * FROM sys.schema_unused_indexes;                     -- 沒被用過的索引（→ ch06 清理）
SELECT * FROM sys.innodb_lock_waits;                         -- 誰擋誰（→ ch05）

digest 的兩個陷阱

• digest 表有容量上限：performance_schema_digests_size 預設 5000 種指紋，超過之後新的指紋會被丟進一個叫 NULL 的桶子。看到 digest_text 為 NULL 卻佔了很大比重時，代表統計已經失真——通常是因為有人在拼字串 SQL（每次的指紋都不同）。
• 它不知道是誰呼叫的。digest 只告訴你「這句 SQL 很貴」，不會告訴你它來自哪個 API、哪個排程。解法是在 SQL 裡加註解標籤：

  SELECT /* app=order-api,ep=list */ ... FROM orders WHERE ...

  註解會出現在 digest 裡，於是統計表就能直接對應到程式碼的位置。這件事只要做一次，之後每一場故障都受益。

「全部變慢」那條路上，看這六個就夠了。每個指標異常時，答案都在前面某一章。

① Threads_running — 有沒有塞住

SHOW GLOBAL STATUS LIKE 'Threads_running';

飆高＝請求出不去。接著看是被什麼卡住（②③④）。

② 鎖等待 — 是不是有人卡著（→ ch05）

SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';
-- Innodb_row_lock_waits      等過鎖的次數
-- Innodb_row_lock_time_avg   平均等多久
-- Innodb_row_lock_current_waits  現在有幾個在等   ← 案發時看這個

SELECT * FROM sys.innodb_lock_waits\G   -- 誰擋誰

③ Buffer Pool 命中率 — 快取是不是被沖掉了（→ ch04）

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

健康的 OLTP 系統通常在 99% 以上。重點不是絕對值，是「突然掉下來」——那就是有人在大量掃冷資料（深分頁、全表掃描、批次）。

④ 磁碟 I/O — 是不是被打滿

SELECT * FROM sys.io_global_by_file_by_bytes LIMIT 10;
-- 順便看誰在寫：暫存檔很大 = 排序或暫存表落磁碟（→ ch04）

⑤ QPS 與呼叫次數 — 是不是量變了

SHOW GLOBAL STATUS LIKE 'Questions';   -- 兩次取樣相減再除以秒數
SHOW GLOBAL STATUS LIKE 'Com_select';  -- 也可以分讀寫看

QPS 突然翻倍而單句沒變慢——那是量的問題，不是 SQL 的問題（本章失敗場景）。

⑥ 複本延遲 — 而且它會騙人

SHOW REPLICA STATUS\G
-- Seconds_Behind_Source: 0     ← 不要盡信

複本延遲的症狀特別容易被誤判：使用者說「我剛儲存的資料不見了」——那不是慢，是讀到了舊資料。去查慢查詢日誌永遠查不到。

有四種情況，症狀是「資料庫很慢」，但問題完全不在資料庫。認出它們可以省下好幾個小時。

① 連線池排隊（→ backend ch03）

判斷方式很明確：應用端的延遲很高，但資料庫的 Threads_running 很低。那代表請求根本還沒送到資料庫，它們卡在應用端等連線。

// HikariCP 要看的是「等待取得連線」的時間，不是 SQL 執行時間
hikaricp_connections_pending      // 有幾個在排隊
hikaricp_connections_acquire_seconds // 拿到連線花多久

② GC 停頓（→ backend ch04）

特徵是週期性的 p99 尖刺，而且 DB 端完全看不到對應的慢語句。查 GC 日誌比查資料庫快得多。ch08 會看到一個典型成因：一次載入太多實體。

③ 結果集太大：網路與序列化

資料庫端 5 毫秒、應用端 2 秒——時間花在傳輸幾 MB 的結果、以及 ORM 把它映射成物件。digest 的 sum_rows_sent 很大就是這個訊號（回扣 ch04 的 SELECT *）。

④ 複本延遲：不是慢，是讀到舊資料

使用者的描述會是「資料不見了」「剛改的沒生效」。判斷方式是去主庫查同一筆——如果主庫是新的、複本是舊的，就是延遲。常見成因：大交易、批次寫入、以及 ch06 那個在複本上也要跑一次的 DDL。

一句話的分流表

徵狀

週一上午，資料庫 CPU 從平常的 30% 升到 95% 並維持不下。

• 慢查詢日誌是空的（long_query_time = 0.5，設定沒問題）。
• Threads_running 在 40~60 之間（平常 5 以下）。
• 鎖等待正常、Buffer Pool 命中率 99.4% 正常、磁碟 I/O 也不高。
• QPS 從 3,000 升到 21,000——而前端流量只成長了 15%。

最後那一條是關鍵：流量沒怎麼變，但送到資料庫的查詢變成七倍。

診斷

慢查詢日誌是空的，代表沒有任何單句超過 0.5 秒。所以問題不在「單價」，在「數量」——換 digest，依總耗時排序：

SELECT LEFT(digest_text, 60) AS q, count_star,
       ROUND(sum_timer_wait/1e12, 0) AS 總秒數,
       ROUND(avg_timer_wait/1e9, 2)  AS 平均毫秒,
       sum_rows_examined, sum_rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 3;

-- q                                          count_star  總秒數  平均毫秒
-- SELECT ... FROM products WHERE id = ?        8,214,003   65,712    8.00
-- SELECT ... FROM members  WHERE id = ?        2,104,881   14,734    7.00
-- SELECT ... FROM orders   WHERE user_id = ?      92,441    1,201   13.00

接著要回答「誰在呼叫它」。digest 不記錄呼叫端，所以用之前埋好的 SQL 註解標籤：

SELECT digest_text FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text LIKE '%products%' LIMIT 1;
-- SELECT /* app=web,ep=order-list */ ... FROM products WHERE id = ?

ep=order-list——訂單列表頁。一個列表頁為什麼要查商品表八百萬次？

根因

這就是 N+1——而它在資料庫端的樣子，就是「一堆完美的查詢」。

為什麼所有 DB 端的工具都指不出它

• EXPLAIN：type=const、rows=1，漂亮得不得了（ch01 早就說過這個盲點）。
• 慢查詢日誌：一句都不超過門檻。
• 索引：全都用對了，沒有任何東西可以加。

修法

教訓

這一節把前面六章接成一條可以直接照著跑的流程。照順序走，不要跳。

平常就要準備好的六件事

鎖與 N+1 這類問題必須抓現場，事後查不到。所以這些要在沒事的時候先做：

• slow_query_log = ON，long_query_time 設成符合 SLA 的值（不是預設的 10 秒）。
• innodb_print_all_deadlocks = ON（ch05）。
• SQL 註解標籤（/* app=…,ep=… */）——讓 digest 對得回程式碼。
• 長交易告警：innodb_trx 裡超過 N 秒的交易。
• 單請求 SQL 次數的監控與測試斷言（本章失敗場景）。
• 心跳表量複本延遲，不要只信 Seconds_Behind_Source。

三個要避免的反射動作

• 「慢就加索引」：先看它為什麼不用現有的索引（ch01、ch02）。索引不是免費的（ch06）。
• 「先調參數」：innodb_buffer_pool_size 以外的參數，在沒有量測之前調整多半是換一種問題。而且參數是全域的，為了一句 SQL 動全域是最糟的交易。
• 「重啟看看」：它會清掉所有現場證據（SHOW ENGINE INNODB STATUS、digest 統計、正在跑的交易），下次還會再發生一次，而你少了一份證據。

這一章推薦了一堆要開的東西。它們都不是免費的，這一節把帳算清楚。

五筆帳

• long_query_time = 0（記錄全部） → 每句都寫檔案，高 QPS 下明顯拖慢資料庫。只在診斷視窗開，而且要設鬧鐘記得關。
• log_queries_not_using_indexes → 小表全表掃描是正常的，日誌會被無害查詢刷爆，真問題反而被淹沒。
• performance_schema → 預設開啟，佔幾百 MB 記憶體，而那是從 Buffer Pool 分走的。要調的是收集哪些 instrument，而不是整個關掉——關掉之後你就沒有 digest 了。
• digest 表容量 → performance_schema_digests_size 預設 5000，超過的指紋全被丟進 NULL 桶。拼字串 SQL 的專案很容易撐爆它，統計就此失真。
• EXPLAIN ANALYZE 在正式環境 → 它真的執行（ch01），對重查詢等於再壓一次。配 max_execution_time。

指標本身的盲點

• 平均值會騙人。平均 8 毫秒可能是「九成 2 毫秒、一成 60 毫秒」。要看 p95／p99，而 digest 只給平均與最大值——這是它比應用端 APM 弱的地方。
• digest 不知道呼叫端（除非埋標籤），也不知道一個請求發了幾句。這兩件事只有應用端量得到。
• Seconds_Behind_Source 可能顯示 0 而實際落後很多（本章講過）。
• 抽樣視窗會蓋掉尖峰。五分鐘取樣一次的監控，看不到持續 40 秒的鎖風暴——而那 40 秒就是使用者實際遇到的故障。