MySQL優(yōu)化之系統(tǒng)表分析SQL

1 使用系統(tǒng)表

1.1 引言

DBA 不在身邊的時(shí)候，線上 MySQL 突然慢了，第一反應(yīng)是啥？
見過太多人上來就 EXPLAIN 一條業(yè)務(wù) SQL，然后盯著 type 和 rows 看半天看不出個(gè)所以然。問題是你根本不知道"慢"到底發(fā)生在哪——是某個(gè)具體 SQL 爛了，還是鎖卡住了，還是內(nèi)存不夠在瘋狂刷盤，還是有人把連接池打滿了。

排查性能問題的正確姿勢(shì)不是從一條 SQL 開始，是先拿一組體檢 SQL把整臺(tái)數(shù)據(jù)庫(kù)掃一遍，找到真正的病灶，再去局部?jī)?yōu)化。

今天把我自己排障時(shí)常用的 10 條 SQL 整理出來。每條都配了：干什么用的、閾值怎么定、實(shí)際怎么看。不是那種"收藏等于掌握"的清單，而是每條你都能直接抄到生產(chǎn)環(huán)境跑。

前提：MySQL 5.7+ 開了 performance_schema，8.0+ 開了 sys schema?，F(xiàn)在這倆基本都默認(rèn)開著，不用操心。

1.2 綜合性能診斷

1.2.1 找出最耗時(shí)的 TOP 10 查詢

這是每次上手的第一條。直接問 performance_schema 要歷史累計(jì)最慢的 10 條 SQL：

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR AS exec_count,
    ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
    ROUND(MAX_TIMER_WAIT / 1000000000, 2) AS max_ms,
    ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

看哪個(gè)字段： 我通常先按 total_sec 排——代表這條 SQL 累計(jì)消耗了多少秒 CPU。累計(jì)時(shí)間高的比單次慢的更要命，因?yàn)榍罢呖赡苁菆?zhí)行了一百萬次的 10ms，后者可能只是偶爾一次的 5s。
踩坑提醒：DIGEST_TEXT 是參數(shù)歸一化后的模板，比如 WHERE id = ?，不會(huì)把每個(gè)具體參數(shù)單獨(dú)列一行。所以看到的數(shù)量會(huì)比原始日志少得多——這是 feature 不是 bug。

1.3 索引方面

1.3.1 抓出沒走索引的 SQL

慢查詢里最常見的元兇就一個(gè)：全表掃描。開這個(gè)開關(guān)先：

SET GLOBAL log_queries_not_using_indexes = ON;

然后從 slow_log（或者 events_statements_summary_by_digest）里看 NO_INDEX_USED_COUNT：

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_NO_INDEX_USED AS no_index_count,
    ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0
ORDER BY SUM_NO_INDEX_USED DESC
LIMIT 20;

閾值：no_index_count 只要 > 0 就值得看一眼。不是說沒走索引就一定有問題（小表全掃可能比走索引還快），但這是個(gè)強(qiáng)信號(hào)。

1.3.2 找出沒人用的冗余索引

索引不是越多越好。每個(gè)索引都會(huì)拖慢寫入，還占磁盤和內(nèi)存。線上跑了幾年的庫(kù)往往積累一堆"當(dāng)年某個(gè)同事加上去再也沒人看過"的索引。
沒人用的：SELECT * FROM sys.schema_unused_indexes;
重復(fù)的（前綴相同）：SELECT * FROM sys.schema_redundant_indexes;

踩坑提醒：schema_unused_indexes 的沒用過是自上次 MySQL 啟動(dòng)以來的統(tǒng)計(jì)。如果剛重啟過，什么都沒跑，它會(huì)把所有索引都列出來。至少讓庫(kù)穩(wěn)定跑一周再看這張表，否則會(huì)誤刪。

1.3.3 引起文件排序filesort的查詢

排序這個(gè)操作在內(nèi)存里做一般沒事，一旦走磁盤 filesort 性能斷崖式下跌：

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_SORT_ROWS AS total_sorted,
    SUM_SORT_MERGE_PASSES AS merge_passes
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_SORT_ROWS > 0
ORDER BY SUM_SORT_ROWS DESC
LIMIT 10;

閾值： 單條 SQL 的 total_sorted 超過 10 萬就該警惕，超過 100 萬基本就是災(zāi)難。merge_passes 大于 0 意味著真的用了磁盤歸并排序。
修復(fù)方向：ORDER BY 的列加索引，或者把排序改成掃索引（ORDER BY 的列和索引順序一致時(shí) MySQL 就不需要 filesort 了）。

1.4 磁盤IO方面

1.4.1 創(chuàng)建磁盤臨時(shí)表的查詢

臨時(shí)表本身不可怕，磁盤臨時(shí)表才是性能殺手。內(nèi)存裝不下就會(huì)落盤，一落盤 IO 就爆。

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_CREATED_TMP_DISK_TABLES AS disk_tmp,
    SUM_CREATED_TMP_TABLES AS mem_tmp
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_CREATED_TMP_DISK_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC
LIMIT 10;

閾值：disk_tmp 只要大于 0 就應(yīng)該看。對(duì)應(yīng)的常見病因是：GROUP BY / ORDER BY 沒走索引、UNION 去重、長(zhǎng)字段被塞進(jìn)了中間結(jié)果。
修復(fù)方向： 優(yōu)先給 GROUP BY / ORDER BY 的列加復(fù)合索引；改不動(dòng) SQL 的話就調(diào)大 tmp_table_size 和 max_heap_table_size，讓它盡量留在內(nèi)存里。

1.4.2 高碎片率表

InnoDB 的表用久了會(huì)有空洞（刪除/更新留下的），占空間還拖慢掃描?？此槠剩?/p> 

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
    ROUND(DATA_FREE / 1024 / 1024, 2) AS free_mb,
    ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100, 2) AS frag_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema')
    AND DATA_LENGTH > 0
ORDER BY frag_pct DESC
LIMIT 20;

閾值：frag_pct 超過 20% 可以考慮 OPTIMIZE TABLE。
注意：OPTIMIZE TABLE 在 InnoDB 下會(huì)重建整張表，期間鎖表（雖然 5.6+ 號(hào)稱支持 online DDL，但實(shí)際場(chǎng)景下還是會(huì)短暫阻塞）。大表千萬別白天直接跑，用 pt-online-schema-change 或業(yè)務(wù)低峰期再操作。

1.4.3 Buffer Pool 命中率夠不夠

InnoDB 所有讀寫先進(jìn) Buffer Pool，命中率低說明內(nèi)存不夠，數(shù)據(jù)庫(kù)在瘋狂刷盤：

SELECT
    ROUND(
        (1 - (
            (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads')
            /
            (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
        )) * 100, 2
    ) AS hit_rate_pct;

閾值： 健康值 ≥ 99%。低于 95% 說明 innodb_buffer_pool_size 配得太小了，考慮加內(nèi)存或者上調(diào)這個(gè)參數(shù)（通常是物理內(nèi)存的 50%–70%）。
注意： 這個(gè)計(jì)算是從啟動(dòng)到現(xiàn)在的全局累計(jì)值，剛啟動(dòng)不久的庫(kù)命中率本來就低，看趨勢(shì)比看單次快照更有意義。

1.5 鎖與連接方面

1.5.1 當(dāng)前的鎖等待

線上偶爾會(huì)碰到SQL 不慢但就是卡住的情況，十有八九是鎖等待。MySQL 8.0 用這個(gè)視圖，清爽得一塌糊涂：
SELECT * FROM sys.innodb_lock_waits
輸出會(huì)告訴你：等的是誰、誰在阻塞他、兩邊各自在執(zhí)行什么 SQL、等了多少秒。
查詢結(jié)果如下：

*************************** 1. row ***************************
                wait_started: 2024-01-01 12:00:00
               waiting_pid: 123
              blocking_pid: 456
             waiting_query: UPDATE table SET ... WHERE ...
              waiting_lock: 0x...
            blocking_lock: 0x...
...

想直接殺掉阻塞方：

SELECT CONCAT('KILL ', blocking_pid, ';') AS kill_cmd
FROM sys.innodb_lock_waits;

把結(jié)果復(fù)制出來執(zhí)行就行。別手滑把被阻塞的那方 kill 了，影響會(huì)更大。

MySQL 5.7 沒有 sys.innodb_lock_waits，用這個(gè)代替：

SELECT
    r.trx_id AS waiting_trx,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

1.5.2 連接池使用情況

線上事故里有一類特別招人恨：應(yīng)用連接池沒回收干凈，MySQL 連接數(shù)被打滿，新請(qǐng)求全部報(bào)錯(cuò)。

SHOW STATUS LIKE 'Threads_%';
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';

重點(diǎn)看：

  • Threads_connected：當(dāng)前連接數(shù)
  • Max_used_connections：歷史最高水位
  • max_connections：上限

閾值：Max_used_connections / max_connections 超過 80% 就要警覺。兩種原因：要么是應(yīng)用突發(fā)流量要擴(kuò)容，要么是連接泄漏——后者是 bug，擴(kuò)容治標(biāo)不治本。
怎么區(qū)分： 看 Threads_connected 趨勢(shì)。如果一直單調(diào)上漲從不回落，八成是泄漏。

1.5.3 JOIN 效率的粗暴判斷

最后這條很多人不知道——performance_schema 里有個(gè)每條 SQL 平均掃描了多少行、返回了多少行的統(tǒng)計(jì)：

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_ROWS_EXAMINED AS examined,
    SUM_ROWS_SENT AS sent,
    ROUND(SUM_ROWS_EXAMINED / GREATEST(SUM_ROWS_SENT, 1), 2) AS scan_ratio
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_ROWS_EXAMINED > 10000
ORDER BY scan_ratio DESC
LIMIT 10;

scan_ratio 就是掃了多少行才篩出一行結(jié)果。

閾值：

  • < 10：健康
  • 10–100：可優(yōu)化
  • > 100：索引有問題
  • > 1000：趕緊改，要么驅(qū)動(dòng)表錯(cuò)了，要么關(guān)聯(lián)字段沒索引

2 總結(jié)建議

總結(jié)建議：

  • 寫個(gè) shell 腳本把這 10 條 SQL 串起來，每天凌晨跑一遍，結(jié)果發(fā)到告警群
  • 線上出性能問題時(shí)，先跑一遍這個(gè)腳本，用輸出數(shù)據(jù)倒推是哪個(gè)環(huán)節(jié)出了問題
  • performance_schema 的統(tǒng)計(jì)是累計(jì)值，排查之前先 RUNCATE TABLE performance_schema.events_statements_summary_by_digest，這樣就能只看最近這段時(shí)間的數(shù)據(jù)，不被歷史污染
    如果沒有權(quán)限，或者不想冒險(xiǎn)直接操作，可以使用 MySQL 官方提供的 sys Schema 存儲(chǔ)過程，這是更安全、便捷的做法：
-- 會(huì)截?cái)?performance_schema 下所有的 summary 和 history 表
CALL sys.ps_truncate_all_tables(FALSE);
  • 另外：performance_schema 本身會(huì)占 約 1% 的 CPU 和幾十到幾百 MB 內(nèi)存。資源緊張的從庫(kù)可以考慮按需開關(guān)，但生產(chǎn)主庫(kù)強(qiáng)烈建議一直開著。
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容