我是怎么做 MySQL 調(diào)優(yōu)的（實(shí)戰(zhàn)思路）

這不是“某幾個(gè)參數(shù)調(diào)一調(diào)”的問(wèn)題，而是一整套從 架構(gòu) → SQL → 索引 → 配置 → 系統(tǒng) 的排查流程。
你可以把這篇當(dāng)成自己的 MySQL 調(diào)優(yōu) Checklist。

一、調(diào)優(yōu)前的共識(shí)：先度量，再優(yōu)化

調(diào)優(yōu)最怕兩件事：

1. 拍腦袋調(diào)參數(shù)：改了一堆配置，QPS 沒(méi)上去，反而更慢；
2. 只盯一個(gè)點(diǎn)：只會(huì)改 innodb_buffer_pool_size，其他全靠緣分。

所以第一步永遠(yuǎn)是：

• 確認(rèn)問(wèn)題是什么
  • 慢？慢在哪？是“偶爾很慢”還是“永遠(yuǎn)都不快”？
  • CPU 打滿？IO 打滿？連接數(shù)飆高？鎖等待多？
• 收集基礎(chǔ)信息
  • 版本：SELECT VERSION();
  • 業(yè)務(wù)類型：讀多寫(xiě)少？寫(xiě)多讀少？混合？
  • 數(shù)據(jù)量、QPS、TPS。
• 看監(jiān)控 / 日志
  • 慢查詢?nèi)罩荆╯low log）；
  • CPU / IO / QPS 曲線；
  • SHOW GLOBAL STATUS 中的一些關(guān)鍵指標(biāo)。

原則：先找到“最痛的那塊”，再下手。

二、第一刀：從 SQL 入手（慢查詢 & 執(zhí)行計(jì)劃）

絕大多數(shù) MySQL 性能問(wèn)題，都可以歸結(jié)為：SQL 寫(xiě)得不行 + 索引沒(méi)設(shè)計(jì)好。

2.1 開(kāi)啟慢查詢?nèi)罩?/h4>

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;   -- 超過(guò) 1 秒的算慢查詢
SET GLOBAL log_queries_not_using_indexes = 1;  -- 也可以先開(kāi)一陣

然后看：

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

線下或運(yùn)維平臺(tái)中，可以用工具分析 slow log：

• 比如 mysqldumpslow、pt-query-digest 等；
• 找出 次數(shù)最多 / 最慢 / 總耗時(shí)最高 的那些 SQL。

2.2 對(duì)慢 SQL 用 EXPLAIN 做執(zhí)行計(jì)劃分析

EXPLAIN SELECT ...;

重點(diǎn)看：

• type：訪問(wèn)類型（ALL、index、range、ref、const 等）；
  • ALL = 全表掃描，基本就是優(yōu)化對(duì)象；
• key：實(shí)際使用的索引；
• rows：預(yù)估掃描行數(shù)；
• Extra：Using filesort、Using temporary、Using index 等。

2.3 改寫(xiě) SQL + 補(bǔ)索引

• 常見(jiàn)優(yōu)化方式：
  • 為 高頻 WHERE 條件 / JOIN 字段 / ORDER BY 字段 建索引；
  • 把“函數(shù)/計(jì)算寫(xiě)在左邊”的寫(xiě)法改掉：

-- ? 錯(cuò)誤示例：索引容易失效
WHERE DATE(create_time) = '2025-01-01';
-- ? 正確示例：使用范圍
WHERE create_time >= '2025-01-01 00:00:00'
  AND create_time <  '2025-01-02 00:00:00';

優(yōu)先使用 聯(lián)合索引 + 最左前綴：

-- 經(jīng)常這么查
WHERE user_id = ? AND status = ? AND create_time > ?;
-- 就建一個(gè)
CREATE INDEX idx_user_status_time ON t_order(user_id, status, create_time);

• 避免在高并發(fā)場(chǎng)景下使用：
  • SELECT *（只取必要字段）；
• 大范圍 IN；
  • OR 拼字段（可考慮拆成 UNION）；
  • 復(fù)雜嵌套子查詢（可改 JOIN 或分步查詢）。

實(shí)戰(zhàn)經(jīng)驗(yàn)：先搞定 Top N 慢 SQL，系統(tǒng)性能往往就上一個(gè)臺(tái)階。

三、第二刀：索引與表結(jié)構(gòu)設(shè)計(jì)

3.1 索引設(shè)計(jì)思路

1. 區(qū)分“高頻查詢條件”和“偶爾查查”；
2. 給經(jīng)常出現(xiàn)在 WHERE / JOIN / ORDER BY / GROUP BY 的列設(shè)計(jì)索引；
3. 盡量用 聯(lián)合索引代替多個(gè)單列索引；
4. 注意索引列的選擇性（區(qū)分度）：
   • 性別、是否刪除（0/1）這類字段不要單獨(dú)建索引；
   • 可以放在聯(lián)合索引里。

3.2 合理選擇主鍵與存儲(chǔ)引擎

InnoDB：

• 強(qiáng)烈建議：自增整型主鍵 + InnoDB；
• 避免使用 UUID/業(yè)務(wù)字符串作為聚簇索引主鍵；
• 避免過(guò)寬的主鍵，所有二級(jí)索引都要跟著變胖。

表結(jié)構(gòu)方面：

• 避免大字段（TEXT/BLOB）頻繁參與查詢，可拆分表；
• 合理拆分“冷熱字段”，避免每次查詢都掃一大坨無(wú)關(guān)數(shù)據(jù)。

四、第三刀：InnoDB 關(guān)鍵參數(shù)調(diào)優(yōu)

在 SQL & 索引搞定后，如果數(shù)據(jù)庫(kù)仍吃緊，可以看配置。

4.1 innodb_buffer_pool_size（最關(guān)鍵的內(nèi)存參數(shù)）

• 作用：InnoDB 用來(lái)緩存數(shù)據(jù)頁(yè) + 索引頁(yè)；
• 一般建議：物理內(nèi)存的 50%～70% 左右（線下壓測(cè)決定）；
• 過(guò)?。捍罅孔x盤，性能抖；
• 過(guò)大：擠壓 OS 緩存/其他進(jìn)程，可能換頁(yè)。

innodb_buffer_pool_size = 8G   # 視機(jī)器內(nèi)存而定

4.2 innodb_log_file_size / log_buffer_size

• redo log 的大小和值：
  • 太?。侯l繁 flush，增加 IO；
  • 太大：崩潰恢復(fù)時(shí)間變長(zhǎng)。
• 典型值可從幾百 MB 到幾 GB，需結(jié)合寫(xiě)入量、硬件調(diào)整。

innodb_log_file_size = 1G
innodb_log_buffer_size = 64M

4.3 innodb_flush_log_at_trx_commit

控制事務(wù)提交時(shí) redo log 的刷盤策略：

• 1：每次事務(wù)提交都刷盤（最安全，最耗 IO）；
• 2：每次提交寫(xiě) OS 緩存，每秒刷盤一次；
• 0：每秒寫(xiě)一次 + 刷盤。

業(yè)務(wù)取舍：

• 金融/強(qiáng)一致：傾向 1；
• 對(duì)少量數(shù)據(jù)丟失能接受，可以考慮 2，性能更好。

4.4 連接數(shù)相關(guān)

max_connections
thread_cache_size
wait_timeout
interactive_timeout

• max_connections 不是越大越好：
  • 太大容易讓機(jī)器被打爆；
  • 一般配合連接池使用，幾百~一兩千視情況。

五、第四刀：操作系統(tǒng) & 硬件層面

當(dāng)你確認(rèn)：

• SQL 已經(jīng)相對(duì)合理；
• 索引也設(shè)計(jì)得不錯(cuò)；
• 配置也調(diào)過(guò)；

此時(shí)數(shù)據(jù)庫(kù)仍然吃緊，就要看系統(tǒng)層面。

5.1 磁盤 IO

• 用 iostat、vmstat、iotop 看：
  • 磁盤隊(duì)列長(zhǎng)度、IO 等待；
• 如果 IO 明顯成為瓶頸：
  • 換 SSD；
  • RAID 級(jí)別調(diào)整；
  • 分庫(kù)分表、冷熱分離。

5.2 CPU

• 大量復(fù)雜 SQL、函數(shù)計(jì)算、排序、JOIN 會(huì)吃 CPU；
• 通過(guò)慢查詢 + EXPLAIN 優(yōu)化 SQL，減少 CPU 壓力；
• 必要時(shí)升級(jí)機(jī)器配置或做讀寫(xiě)分離、分庫(kù)。

5.3 網(wǎng)絡(luò)

• 主從復(fù)制延遲大、跨機(jī)房訪問(wèn)慢；
• 盡量讓數(shù)據(jù)庫(kù)靠近應(yīng)用部署（同一可用區(qū)/機(jī)房）。

六、第五刀：架構(gòu)級(jí)優(yōu)化（分庫(kù)分表 & 讀寫(xiě)分離）

當(dāng)單實(shí)例再怎么調(diào)也扛不住時(shí)，就是架構(gòu)層面問(wèn)題了。

6.1 讀寫(xiě)分離

典型做法：

• 主庫(kù)負(fù)責(zé)寫(xiě)、從庫(kù)負(fù)責(zé)讀；
• 中間加一個(gè)數(shù)據(jù)訪問(wèn)層（或中間件）做路由；
• 部分請(qǐng)求強(qiáng)制走主庫(kù)（寫(xiě)后立刻讀）。

適合場(chǎng)景：

• 讀遠(yuǎn)多于寫(xiě)；
• 允許短暫讀到舊數(shù)據(jù)的場(chǎng)景。

6.2 分庫(kù)分表

如果單表行數(shù)上億，索引高度、統(tǒng)計(jì)信息、緩存命中都會(huì)出問(wèn)題。

常見(jiàn)拆分維度：

• 按業(yè)務(wù)維度拆庫(kù)（用戶庫(kù)、訂單庫(kù)、日志庫(kù)）；
• 按 hash/范圍拆分大表：
  • 用戶 ID 取模；
  • 按時(shí)間分表（按月/按日）。

拆分后要注意：

• 跨分片 JOIN 不再簡(jiǎn)單支持，需要應(yīng)用層處理；
• 全局唯一主鍵（雪花算法、號(hào)段發(fā)號(hào)等）；
• 分布式事務(wù)（能避則避，多用最終一致方案）。

七、監(jiān)控與排查工具清單

調(diào)優(yōu)離不開(kāi)“觀察工具”：

1. 慢查詢?nèi)罩?/strong>：慢 SQL 排查入口；
2. EXPLAIN / EXPLAIN ANALYZE：看執(zhí)行計(jì)劃、實(shí)際耗時(shí)；
3. SHOW PROCESSLIST：看當(dāng)前連接、是否有鎖等待；
4. SHOW ENGINE INNODB STATUS\G：看死鎖、事務(wù)等待；
5. information_schema / performance_schema / sys 庫(kù)：
   • 統(tǒng)計(jì)哪些 SQL 最耗費(fèi)資源。
6. 操作系統(tǒng)工具：top、iostat、vmstat、sar 等。