級(jí)性能優(yōu)化實(shí)戰(zhàn)：從索引到高可用架構(gòu)的完整鏈路)
“MySQL 好像變慢了”“線上一條查詢跑了 8 秒”“接口偶爾卡死DBA 說是鎖等待”……這些數(shù)據(jù)庫(kù)問題幾乎每個(gè)做后端開發(fā)的程序員都遇到過。尤其在企業(yè)級(jí)項(xiàng)目里MySQL 早已不是“裝個(gè)庫(kù)、建個(gè)表、寫個(gè) CRUD”這么簡(jiǎn)單數(shù)據(jù)量一上來索引沒建對(duì)一條 SQL 就能拖垮整個(gè)服務(wù)并發(fā)一高鎖和事務(wù)隔離級(jí)別沒搞清楚線上就會(huì)頻繁出現(xiàn)死鎖和超時(shí)。市面上講 MySQL 的資料很多但大多要么停留在基礎(chǔ)語(yǔ)法要么直接上升到分布式中間件真正能把“企業(yè)級(jí)實(shí)戰(zhàn)”這條路走通、走順、走完整的教程并不多。這也是高性能 MySQL 實(shí)戰(zhàn)類內(nèi)容最近持續(xù)受到關(guān)注的原因開發(fā)者需要的不是概念拼盤而是一套能真正落地到項(xiàng)目里的性能設(shè)計(jì)、優(yōu)化方法和排錯(cuò)思路。這篇文章我想用自己的學(xué)習(xí)和實(shí)踐視角把 MySQL 企業(yè)級(jí)應(yīng)用中最關(guān)鍵的性能問題、優(yōu)化路徑和實(shí)戰(zhàn)案例做一個(gè)系統(tǒng)梳理。內(nèi)容會(huì)覆蓋數(shù)據(jù)庫(kù)架構(gòu)與引擎原理、Schema 設(shè)計(jì)、索引優(yōu)化、SQL 改寫、事務(wù)與鎖、高可用架構(gòu)、監(jiān)控告警這些核心模塊。尤其會(huì)側(cè)重那些“看起來簡(jiǎn)單、真正做起來容易踩坑”的環(huán)節(jié)比如聯(lián)合索引的最左前綴到底怎么用、為什么不建議在索引列上做函數(shù)運(yùn)算、可重復(fù)讀隔離級(jí)別下到底會(huì)不會(huì)出現(xiàn)幻讀、分庫(kù)分表之前一定要先做哪些評(píng)估。讀完之后即使你還沒有機(jī)會(huì)在生產(chǎn)環(huán)境里操刀大型項(xiàng)目也至少能建立一條完整的高性能 MySQL 優(yōu)化鏈路拿到一個(gè)慢 SQL知道從哪下手設(shè)計(jì)一張業(yè)務(wù)表知道索引該怎么規(guī)劃系統(tǒng)出現(xiàn)鎖等待知道去哪里看、怎么解。這就是本文最想交付給你的價(jià)值。1. 企業(yè)級(jí) MySQL 為什么需要一套“性能方法論”很多人對(duì) MySQL 性能優(yōu)化的理解停留在“建索引”和“寫 SQL 的時(shí)候注意一下”這個(gè)層面。但如果你真正參與過企業(yè)級(jí)項(xiàng)目會(huì)發(fā)現(xiàn)性能問題遠(yuǎn)不是這么簡(jiǎn)單。企業(yè)級(jí)應(yīng)用和個(gè)人項(xiàng)目、課程作業(yè)有一個(gè)本質(zhì)區(qū)別它的狀態(tài)是持續(xù)演進(jìn)的數(shù)據(jù)是持續(xù)增長(zhǎng)的并發(fā)是持續(xù)存在的。今天一張表 10 萬(wàn)條數(shù)據(jù)隨便怎么查都很快到了 5000 萬(wàn)條即使有索引也可能因?yàn)樗饕O(shè)計(jì)不合理出現(xiàn)回表過多、隨機(jī) IO 暴漲。更麻煩的是系統(tǒng)一旦上線很多結(jié)構(gòu)性問題就很難推倒重來字段類型不合適要改涉及數(shù)據(jù)遷移索引建得不對(duì)要調(diào)涉及線上 DDL事務(wù)粒度太大導(dǎo)致鎖范圍擴(kuò)大涉及代碼重構(gòu)。這些問題的根源往往不是在寫某一條 SQL 時(shí)才出現(xiàn)的而是在表結(jié)構(gòu)設(shè)計(jì)、框架選型、事務(wù)邊界劃分這些更早的環(huán)節(jié)就埋下了伏筆。所以企業(yè)級(jí) MySQL 的性能認(rèn)知本質(zhì)上是一套前置的方法論在設(shè)計(jì)階段預(yù)判未來的數(shù)據(jù)量和訪問模式在開發(fā)階段寫出能高效利用索引的 SQL在運(yùn)維階段通過監(jiān)控和慢查詢?nèi)罩境掷m(xù)發(fā)現(xiàn)問題在架構(gòu)階段通過主從復(fù)制、讀寫分離、分庫(kù)分表來突破單機(jī)瓶頸。這條鏈路里任何一個(gè)環(huán)節(jié)缺失都會(huì)在流量上來之后以線上故障的形式暴露出來。另外還有一個(gè)很容易被忽視的點(diǎn)MySQL 性能優(yōu)化并不是 DBA 一個(gè)人的事情。開發(fā)人員寫的每一條 SQL、設(shè)計(jì)的每一張表、選擇的每一個(gè) ORM 用法都在直接影響數(shù)據(jù)庫(kù)的負(fù)載。一個(gè)連EXPLAIN都不會(huì)看的后端程序員和一個(gè)能從執(zhí)行計(jì)劃里快速判斷索引是否命中的后端程序員在同一個(gè)團(tuán)隊(duì)里產(chǎn)出的系統(tǒng)性能差距可能是數(shù)量級(jí)的。這也是我認(rèn)為每個(gè) Java 后端、Go 后端、Python 后端開發(fā)者都應(yīng)該認(rèn)真看一輪 MySQL 實(shí)戰(zhàn)內(nèi)容的原因。2. MySQL 核心架構(gòu)與性能模型在討論具體優(yōu)化手段之前有必要先把 MySQL 的整體架構(gòu)講清楚。很多調(diào)優(yōu)動(dòng)作之所以讓人迷惑是因?yàn)槟愀静恢酪粭l SQL 在數(shù)據(jù)庫(kù)內(nèi)部到底經(jīng)歷了什么。2.1 一條 SQL 的執(zhí)行鏈路MySQL 從整體上可以分為兩層Server 層和存儲(chǔ)引擎層。Server 層負(fù)責(zé)連接管理、語(yǔ)法解析、查詢優(yōu)化、執(zhí)行計(jì)劃生成存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的實(shí)際存儲(chǔ)和讀取。常見的 InnoDB 就是一個(gè)存儲(chǔ)引擎也是目前 MySQL 默認(rèn)且最常用的引擎。一條查詢 SQL 的執(zhí)行過程大致是客戶端通過連接器建立連接進(jìn)行身份認(rèn)證。查詢緩存8.0 之前有8.0 之后已移除檢查是否命中緩存。分析器做詞法分析和語(yǔ)法分析生成語(yǔ)法樹。優(yōu)化器決定使用哪個(gè)索引、以什么順序關(guān)聯(lián)多張表生成執(zhí)行計(jì)劃。執(zhí)行器調(diào)用存儲(chǔ)引擎接口逐行讀取數(shù)據(jù)并返回結(jié)果。這個(gè)鏈路里優(yōu)化器是最關(guān)鍵也最容易被誤解的部分。你以為你寫的 SQL 會(huì)按照“你想象的順序”執(zhí)行但實(shí)際上優(yōu)化器會(huì)基于統(tǒng)計(jì)信息選擇它認(rèn)為成本最低的執(zhí)行路徑。有時(shí)候你明明建了索引優(yōu)化器卻選擇了全表掃描這可能是因?yàn)樗J(rèn)為回表成本比全表掃描還高也可能是因?yàn)榻y(tǒng)計(jì)信息過期。2.2 InnoDB 與 MyISAM 的核心差異很多初學(xué)者會(huì)問InnoDB 和 MyISAM 到底有什么區(qū)別為什么現(xiàn)在幾乎都推薦 InnoDB核心差異可以總結(jié)為下表對(duì)比維度InnoDBMyISAM事務(wù)支持支持 ACID 事務(wù)不支持事務(wù)鎖粒度支持行級(jí)鎖只有表級(jí)鎖崩潰恢復(fù)支持通過 redo log 恢復(fù)不支持崩潰安全恢復(fù)外鍵支持支持不支持聚集索引有數(shù)據(jù)按主鍵順序存儲(chǔ)無數(shù)據(jù)和索引分離適用場(chǎng)景企業(yè)級(jí) OLTP 業(yè)務(wù)只讀、日志分析類場(chǎng)景已逐漸邊緣化變化判斷從 MySQL 5.5 開始InnoDB 就是默認(rèn)存儲(chǔ)引擎到了 8.0MyISAM 的所有系統(tǒng)表都被 InnoDB 取代。如果你還在新項(xiàng)目里主動(dòng)指定 MyISAM除非有非常特殊的只讀報(bào)表需求否則幾乎找不到理由。2.3 為什么“性能模型”比“單條 SQL 快”更重要在企業(yè)級(jí)系統(tǒng)里數(shù)據(jù)庫(kù)性能不只是“單條查詢快不快”而是“系統(tǒng)在持續(xù)負(fù)載下的吞吐量和延遲是否穩(wěn)定”。一個(gè)每秒只能支撐 100 次查詢、但每次查詢只要 5ms 的系統(tǒng)和一個(gè)每秒能支撐 5000 次查詢、平均 20ms 的系統(tǒng)后者的業(yè)務(wù)價(jià)值往往大得多。因此后續(xù)所有的優(yōu)化手段都要回歸到兩個(gè)核心指標(biāo)QPS每秒查詢數(shù)衡量數(shù)據(jù)庫(kù)吞吐能力。響應(yīng)時(shí)間衡量單次請(qǐng)求延遲通常關(guān)注 p95、p99 而不是平均值。理解了這一點(diǎn)你再看很多優(yōu)化建議時(shí)就會(huì)明白其背后的指向減少回表是為了降低隨機(jī) IO使用覆蓋索引是為了減少數(shù)據(jù)頁(yè)訪問批量寫入是為了減少 redo log 刷盤次數(shù)連接池是為了減少線程頻繁創(chuàng)建銷毀的開銷。所有手段的目的都是在單位時(shí)間內(nèi)讓數(shù)據(jù)庫(kù)做更少無效工作從而支撐更高的吞吐。3. 環(huán)境準(zhǔn)備搭建一套可復(fù)現(xiàn)的 MySQL 學(xué)習(xí)與測(cè)試環(huán)境實(shí)戰(zhàn)教程最怕環(huán)境不一致。為了確保后續(xù)示例可以運(yùn)行我們需要在一臺(tái)干凈的機(jī)器上準(zhǔn)備 MySQL 環(huán)境。這里我推薦使用 Docker 來搭建原因有兩個(gè)一是版本切換方便不會(huì)污染宿主機(jī)二是可以隨時(shí)刪除重建適合反復(fù)練習(xí)。3.1 使用 Docker 安裝 MySQL 8.0在開始之前確認(rèn)機(jī)器上已經(jīng)安裝了 Docker。然后執(zhí)行下面的命令docker pull mysql:8.0啟動(dòng)一個(gè) MySQL 容器并做基本配置docker run -d \ --name mysql-practice \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDroot123456 \ -e MYSQL_DATABASEcompany \ mysql:8.0 \ --character-set-serverutf8mb4 \ --collation-serverutf8mb4_unicode_ci這條命令里MYSQL_ROOT_PASSWORD設(shè)置了 root 密碼MYSQL_DATABASEcompany會(huì)自動(dòng)創(chuàng)建一個(gè)名為 company 的數(shù)據(jù)庫(kù)。--character-set-serverutf8mb4和--collation-serverutf8mb4_unicode_ci是企業(yè)級(jí)項(xiàng)目必須注意的兩個(gè)參數(shù)它們保證數(shù)據(jù)庫(kù)能夠正確存儲(chǔ)中文和 emoji 等四字節(jié)字符。查看容器是否正常運(yùn)行docker ps | grep mysql-practice進(jìn)入容器并使用命令行連接docker exec -it mysql-practice mysql -uroot -proot1234563.2 準(zhǔn)備測(cè)試數(shù)據(jù)為了模擬真實(shí)業(yè)務(wù)場(chǎng)景我們創(chuàng)建一張員工表和一張部門表并插入一定量的數(shù)據(jù)。這里的數(shù)據(jù)量可以不必太大重點(diǎn)是理解執(zhí)行計(jì)劃。CREATE DATABASE IF NOT EXISTS company DEFAULT CHARSET utf8mb4; USE company; CREATE TABLE department ( dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(50) NOT NULL ) ENGINEInnoDB; CREATE TABLE employee ( emp_id INT PRIMARY KEY AUTO_INCREMENT, emp_no VARCHAR(20) NOT NULL, emp_name VARCHAR(50) NOT NULL, age INT NOT NULL, dept_id INT NOT NULL, salary DECIMAL(10, 2) NOT NULL, hire_date DATE NOT NULL, KEY idx_dept_id (dept_id), KEY idx_hire_date (hire_date) ) ENGINEInnoDB;這里先建立兩個(gè)索引idx_dept_id和idx_hire_date它們的用途會(huì)在后續(xù) SQL 優(yōu)化案例里反復(fù)體現(xiàn)。插入測(cè)試數(shù)據(jù)時(shí)可以使用存儲(chǔ)過程或連接查詢批量插入。這里給一個(gè)簡(jiǎn)單的存儲(chǔ)過程示例DELIMITER $$ CREATE PROCEDURE insert_employee_data() BEGIN DECLARE i INT DEFAULT 1; WHILE i 10000 DO INSERT INTO employee (emp_no, emp_name, age, dept_id, salary, hire_date) VALUES ( CONCAT(EMP, LPAD(i, 6, 0)), CONCAT(員工, i), 20 (i % 30), 1 (i % 10), 5000 (i % 50000), DATE_ADD(2015-01-01, INTERVAL (i % 3000) DAY) ); SET i i 1; END WHILE; END$$ DELIMITER ; CALL insert_employee_data();插入完成后確認(rèn)數(shù)據(jù)量SELECT COUNT(*) FROM employee;3.3 環(huán)境就緒后下一步做什么環(huán)境就緒后建議你先做一件事情打開 MySQL 的慢查詢?nèi)罩景验撝翟O(shè)置得低一些。這樣后面執(zhí)行任何測(cè)試 SQL都能快速判斷它是否屬于慢查詢。這也是企業(yè)級(jí)調(diào)優(yōu)的第一步——先建立可觀測(cè)性再談優(yōu)化。在容器中執(zhí)行mysql -uroot -proot123456 -e SET GLOBAL slow_query_log ON; mysql -uroot -proot123456 -e SET GLOBAL long_query_time 1;這樣超過 1 秒的查詢都會(huì)被記錄到慢查詢?nèi)罩局?。生產(chǎn)環(huán)境一般建議閾值設(shè)置在 1 秒或更低具體需要結(jié)合實(shí)際業(yè)務(wù)判斷。4. 數(shù)據(jù)庫(kù)設(shè)計(jì)性能問題從 Schema 階段就開始很多性能問題表面上是 SQL 慢根源卻是表結(jié)構(gòu)設(shè)計(jì)不合理。所以真正的高性能 MySQL 實(shí)戰(zhàn)一定要從 Schema 設(shè)計(jì)講起。4.1 字段類型選擇不要圖省事用大字段一個(gè)最常見的錯(cuò)誤是把所有字段都設(shè)計(jì)成VARCHAR(255)或者更夸張的TEXT。這種做法會(huì)導(dǎo)致幾個(gè)問題數(shù)據(jù)頁(yè)能容納的行數(shù)變少同樣一張表需要更多數(shù)據(jù)頁(yè)掃描成本更高。索引字段如果過長(zhǎng)索引體積變大緩存命中率下降。TEXT類型的字段在內(nèi)存中臨時(shí)表排序時(shí)會(huì)導(dǎo)致磁盤臨時(shí)表性能驟降。字段類型選擇的基本原則是夠用就好。狀態(tài)值用TINYINT金額用DECIMAL定長(zhǎng)短字符串用CHAR變長(zhǎng)字符串用合理的VARCHAR長(zhǎng)度日期用DATE或DATETIME不建議用字符串存儲(chǔ)日期。-- 反例所有字段都用 VARCHAR CREATE TABLE bad_example ( id VARCHAR(20) PRIMARY KEY, status VARCHAR(10), create_time VARCHAR(30) ); -- 正例合理選擇字段類型 CREATE TABLE good_example ( id INT PRIMARY KEY AUTO_INCREMENT, status TINYINT NOT NULL DEFAULT 0 COMMENT 0-未處理 1-已處理, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 創(chuàng)建時(shí)間 );4.2 主鍵設(shè)計(jì)自增主鍵 vs 業(yè)務(wù)主鍵InnoDB 是聚集索引組織表數(shù)據(jù)行實(shí)際上是按主鍵順序存儲(chǔ)在 B 樹葉子節(jié)點(diǎn)上的。這意味著主鍵的選擇會(huì)直接影響寫入性能和空間使用。自增主鍵由于新值總是比舊值大插入時(shí)只需要順序追加不需要頻繁移動(dòng)已有數(shù)據(jù)因此寫入性能最好。業(yè)務(wù)主鍵如果是無序的字符串或 UUID插入時(shí)就可能觸發(fā)頁(yè)分裂造成隨機(jī) IO 和碎片。-- 推薦自增主鍵 CREATE TABLE order_info ( order_id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, UNIQUE KEY uk_order_no (order_no) ) ENGINEInnoDB; -- 不推薦直接用 UUID 字符串做主鍵 -- 會(huì)導(dǎo)致 B 樹頻繁頁(yè)分裂寫入性能差索引體積大要注意的是這里說的是“主鍵”不要用 UUID但業(yè)務(wù)唯一標(biāo)識(shí)如order_no可以單獨(dú)建唯一索引。這樣既保證業(yè)務(wù)查詢需要又不犧牲聚集索引的寫入性能。4.3 反范式設(shè)計(jì)適度冗余減少關(guān)聯(lián)查詢?cè)谄髽I(yè)級(jí)項(xiàng)目中完全遵循數(shù)據(jù)庫(kù)三范式并不現(xiàn)實(shí)。三范式把數(shù)據(jù)拆得越細(xì)表之間的關(guān)聯(lián)查詢就越多JOIN 的開銷就越大。實(shí)際優(yōu)化中經(jīng)常采用適度反范式在訂單表里冗余一份用戶名在統(tǒng)計(jì)表里預(yù)計(jì)算每日匯總值。但反范式設(shè)計(jì)不是越冗余越好。冗余字段最大的問題是數(shù)據(jù)一致性和維護(hù)成本。解決方案可以依賴事務(wù)、應(yīng)用層代碼或定時(shí)任務(wù)來同步。這個(gè)權(quán)衡要結(jié)合具體業(yè)務(wù)來決定原則是高頻查詢的維度才值得冗余低頻更新且允許短暫不一致的字段才適合冗余。5. 索引優(yōu)化高性能查詢的第一道防線索引是 MySQL 性能優(yōu)化里性價(jià)比最高的手段。一個(gè)合適的索引可以把全表掃描的幾十秒降到毫秒級(jí)。但索引也不是越多越好它本身會(huì)占用空間并且拖慢寫入速度。所以索引設(shè)計(jì)需要結(jié)合業(yè)務(wù)查詢模式來做。5.1 B 樹索引為什么適合數(shù)據(jù)庫(kù)InnoDB 的索引底層是 B 樹。B 樹相比二叉樹、哈希索引有幾個(gè)核心優(yōu)勢(shì)樹的高度低一般三層就能存千萬(wàn)級(jí)數(shù)據(jù)查找次數(shù)穩(wěn)定。葉子節(jié)點(diǎn)通過鏈表連接適合范圍查詢。數(shù)據(jù)在葉子節(jié)點(diǎn)按順序排列排序和分組可以利用索引有序性。理解 B 樹的有序性非常重要。很多優(yōu)化技巧比如聯(lián)合索引最左前綴、索引下推、覆蓋索引都是建立在“索引數(shù)據(jù)有序”這個(gè)基礎(chǔ)之上的。5.2 聯(lián)合索引的最左前綴原則假設(shè)我們建了一個(gè)聯(lián)合索引(dept_id, hire_date, emp_no)。這個(gè)索引的實(shí)際結(jié)構(gòu)是先按dept_id排序dept_id相同的按hire_date排序兩者都相同的再按emp_no排序。因此在查詢時(shí)只有遵循最左前綴原則才能使用到這個(gè)索引。-- 能用到聯(lián)合索引 SELECT * FROM employee WHERE dept_id 3; SELECT * FROM employee WHERE dept_id 3 AND hire_date 2020-01-01; SELECT * FROM employee WHERE dept_id 3 AND hire_date 2020-01-01 AND emp_no EMP000123; -- 不能用到聯(lián)合索引 SELECT * FROM employee WHERE hire_date 2020-01-01; SELECT * FROM employee WHERE emp_no EMP000123;最左前綴原則的真正含義是聯(lián)合索引的任何一個(gè)前綴子集都可以獨(dú)立使用但跳過前置列直接使用后面的列則無法命中索引。因此創(chuàng)建聯(lián)合索引時(shí)字段順序非常關(guān)鍵將等值查詢的字段放在前面。將范圍查詢的字段放在后面。將區(qū)分度高的字段放在前面。5.3 回表、索引覆蓋與索引下推InnoDB 普通索引的葉子節(jié)點(diǎn)存的是主鍵值。如果查詢的列在普通索引中不存在就需要通過主鍵再回表查詢一次這個(gè)過程叫回表?；乇頃?huì)帶來額外的隨機(jī) IO。覆蓋索引可以讓一個(gè)查詢只掃描索引就拿到所有需要的列不需要回表。設(shè)計(jì)覆蓋索引時(shí)要把 SELECT 的字段也考慮進(jìn)索引。-- employee 表有 idx_dept_id (dept_id) -- 這條 SQL 需要回表查詢 emp_name 和 salary SELECT emp_name, salary FROM employee WHERE dept_id 3; -- 如果改成覆蓋索引 idx_dept_id_name (dept_id, emp_name, salary) -- 這條 SQL 就不需要回表索引本身已經(jīng)包含了所有需要的列索引下推是 MySQL 5.6 引入的優(yōu)化。假設(shè)索引是(dept_id, hire_date)查詢條件是dept_id 3 AND hire_date 2020-01-01在沒有索引下推時(shí)存儲(chǔ)引擎需要把所有dept_id 3的記錄都回表再在 Server 層過濾 hire_date有了索引下推之后hire_date的過濾條件會(huì)在存儲(chǔ)引擎層直接處理減少回表次數(shù)。在實(shí)際工作中用EXPLAIN查看執(zhí)行計(jì)劃時(shí)如果看到Using index condition說明索引下推生效了。這是一個(gè)積極信號(hào)。5.4 最容易被忽視的索引失效場(chǎng)景有幾類 SQL 寫法會(huì)導(dǎo)致索引失效即使你建了索引也白建場(chǎng)景示例原因?qū)λ饕凶龊瘮?shù)運(yùn)算WHERE YEAR(hire_date) 2020函數(shù)破壞了索引列的有序性隱式類型轉(zhuǎn)換WHERE emp_no 123emp_no 為字符串類型轉(zhuǎn)換導(dǎo)致無法匹配索引前導(dǎo)模糊查詢WHERE emp_name LIKE %張三字符串前綴無法確定索引無法定位OR 連接非索引條件WHERE dept_id 3 OR age 30優(yōu)化器可能選擇全表掃描-- 反例對(duì)索引列使用函數(shù) SELECT * FROM employee WHERE YEAR(hire_date) 2020; -- 正例改寫為范圍查詢 SELECT * FROM employee WHERE hire_date 2020-01-01 AND hire_date 2021-01-01;記住一個(gè)核心原則不要在索引列上做計(jì)算。6. SQL 優(yōu)化實(shí)戰(zhàn)從執(zhí)行計(jì)劃到改寫技巧SQL 優(yōu)化不能靠猜必須基于執(zhí)行計(jì)劃來分析。MySQL 提供了EXPLAIN命令可以查看一條 SQL 的執(zhí)行計(jì)劃。掌握它你才能從“看 SQL 靠感覺”進(jìn)化到“看執(zhí)行計(jì)劃定位問題”。6.1 讀懂 EXPLAIN 的關(guān)鍵列以一條實(shí)際查詢?yōu)槔鼸XPLAIN SELECT emp_name, salary FROM employee WHERE dept_id 3 AND age 25;輸出中需要重點(diǎn)關(guān)注的列包括列名含義重點(diǎn)關(guān)注點(diǎn)type訪問類型const、ref、range優(yōu)于ALL全表掃描key實(shí)際使用的索引是否為 NULLNULL 代表沒走索引rows預(yù)估掃描行數(shù)越小越好filtered過濾比例代表 Server 層還要過濾多少行Extra額外信息出現(xiàn)Using filesort、Using temporary要警惕如果type ALL并且rows很大說明這條 SQL 在做全表掃描是首要優(yōu)化對(duì)象。6.2 分頁(yè)查詢優(yōu)化深分頁(yè)帶來的性能災(zāi)難后臺(tái)管理列表最常見的做法是LIMIT offset, size。當(dāng)頁(yè)數(shù)足夠深時(shí)偏移量會(huì)非常大MySQL 需要掃描并丟棄前面所有的行才能拿到目標(biāo)數(shù)據(jù)。-- 深分頁(yè)性能極差offset 越大掃描越多 SELECT * FROM employee ORDER BY hire_date DESC LIMIT 100000, 20;優(yōu)化方式有兩種常見方案。第一種是延遲關(guān)聯(lián)先用覆蓋索引查出主鍵再通過主鍵關(guān)聯(lián)回原表獲取完整數(shù)據(jù)。SELECT e.* FROM employee e INNER JOIN ( SELECT emp_id FROM employee ORDER BY hire_date DESC LIMIT 100000, 20 ) t ON e.emp_id t.emp_id;第二種是基于排序字段的游標(biāo)分頁(yè)適合滾動(dòng)加載場(chǎng)景-- 記住上一頁(yè)最后一條記錄的 hire_date 和 emp_id SELECT * FROM employee WHERE (hire_date, emp_id) (2023-05-20, 50020) ORDER BY hire_date DESC, emp_id DESC LIMIT 20;這種寫法充分利用索引的有序性不需要掃描和丟棄臟數(shù)據(jù)頁(yè)數(shù)越深優(yōu)勢(shì)越明顯。6.3 JOIN 優(yōu)化小表驅(qū)動(dòng)大表在多表關(guān)聯(lián)時(shí)MySQL 的優(yōu)化器通常會(huì)選擇“小表驅(qū)動(dòng)大表”的策略。因此在寫 JOIN 時(shí)盡量讓小表作為驅(qū)動(dòng)表大表作為被驅(qū)動(dòng)表并在被驅(qū)動(dòng)表的連接字段上建立索引。-- 推薦被驅(qū)動(dòng)表 department 的主鍵索引就足夠 SELECT e.emp_name, d.dept_name FROM employee e INNER JOIN department d ON e.dept_id d.dept_id WHERE e.age 30;如果被驅(qū)動(dòng)表的關(guān)聯(lián)字段上沒有索引優(yōu)化器可能選擇Block Nested-Loop Join也就是把所有滿足條件的驅(qū)動(dòng)表數(shù)據(jù)放入 join buffer再全表掃描被驅(qū)動(dòng)表做匹配性能會(huì)差很多。6.4 聚合查詢優(yōu)化避免臨時(shí)表和文件排序GROUP BY 和 ORDER BY 是出現(xiàn)Using temporary和Using filesort的高發(fā)場(chǎng)景。如果 GROUP BY 的字段不是索引字段MySQL 就不得不在內(nèi)存或磁盤上創(chuàng)建臨時(shí)表。-- 可能出現(xiàn) Using temporary SELECT dept_id, COUNT(*) FROM employee GROUP BY dept_id; -- 如果 dept_id 有索引可以通過索引有序掃描直接完成分組 -- 無需額外排序更徹底的優(yōu)化思路是使用匯總表。比如統(tǒng)計(jì)每個(gè)部門的員工數(shù)如果業(yè)務(wù)對(duì)實(shí)時(shí)性要求不高可以每天晚上跑定時(shí)任務(wù)生成匯總表業(yè)務(wù)側(cè)直接查詢匯總表。這就是從“實(shí)時(shí)計(jì)算”到“預(yù)計(jì)算”的典型優(yōu)化。7. 事務(wù)、鎖與并發(fā)控制高并發(fā)場(chǎng)景下數(shù)據(jù)庫(kù)最容易出現(xiàn)的兩大問題就是鎖等待和死鎖。這背后其實(shí)是事務(wù)隔離級(jí)別、鎖機(jī)制、事務(wù)粒度的綜合問題。7.1 事務(wù)隔離級(jí)別如何影響并發(fā)能力MySQL InnoDB 默認(rèn)隔離級(jí)別是REPEATABLE READ可重復(fù)讀。和 Oracle、PostgreSQL 默認(rèn)的READ COMMITTED不同這個(gè)選擇主要是歷史原因——MySQL 的 binlog 在STATEMENT格式下只有在可重復(fù)讀級(jí)別才能保證主從復(fù)制的一致性。但在 MySQL 8.0 中ROW格式已經(jīng)是默認(rèn) binlog 格式隔離級(jí)別的選擇反而更靈活了。-- 查看當(dāng)前隔離級(jí)別 SELECT transaction_isolation;可重復(fù)讀通過MVCC 間隙鎖在大多數(shù)場(chǎng)景下避免了“不可重復(fù)讀”和“幻讀”。但在高并發(fā)寫入場(chǎng)景中間隙鎖會(huì)擴(kuò)大鎖范圍加大鎖等待的概率。如果業(yè)務(wù)對(duì)一致性要求允許放寬可以評(píng)估是否使用READ COMMITTED因?yàn)?RC 級(jí)別只有記錄鎖沒有間隙鎖并發(fā)能力更高。隔離級(jí)別的選擇是一個(gè)典型的業(yè)務(wù)需求與并發(fā)能力的權(quán)衡沒有絕對(duì)的好壞。7.2 死鎖是怎么產(chǎn)生的死鎖的本質(zhì)是多個(gè)事務(wù)以不同順序持有資源互相等待。經(jīng)典的例子是事務(wù) A 先更新表 1 再更新表 2事務(wù) B 先更新表 2 再更新表 1兩個(gè)事務(wù)同時(shí)提交時(shí)就有概率死鎖。-- 事務(wù)A先更新 dept_id1 再更新 dept_id2 BEGIN; UPDATE employee SET salary salary 100 WHERE dept_id 1; UPDATE employee SET salary salary 100 WHERE dept_id 2; COMMIT; -- 事務(wù)B先更新 dept_id2 再更新 dept_id1 BEGIN; UPDATE employee SET salary salary 100 WHERE dept_id 2; UPDATE employee SET salary salary 100 WHERE dept_id 1; COMMIT;要避免死鎖核心有幾個(gè)方向保持一致的加鎖順序讓所有事務(wù)都按同樣的順序更新記錄。控制事務(wù)粒度不要在一個(gè)事務(wù)里執(zhí)行太多無關(guān)操作減少持鎖時(shí)間。在無法避免死鎖時(shí)通過重試機(jī)制處理死鎖報(bào)錯(cuò)而不是直接讓接口失敗。7.3 大事務(wù)慢查詢和鎖等待的隱形殺手一個(gè)事務(wù)里執(zhí)行了大量 INSERT、UPDATE 或包含遠(yuǎn)程調(diào)用會(huì)導(dǎo)致持鎖時(shí)間過長(zhǎng)輕則拖慢并發(fā)性能重則引發(fā)大規(guī)模鎖等待和主從延遲。實(shí)踐中應(yīng)該遵守幾個(gè)原則事務(wù)中避免遠(yuǎn)程調(diào)用避免循環(huán)逐條更新避免一次性處理過多數(shù)據(jù)。# 反例事務(wù)中做遠(yuǎn)程調(diào)用 循環(huán)更新 def bad_batch_update(): with transaction(): for order in order_list: resp call_payment_service(order.id) # 網(wǎng)絡(luò)調(diào)用持鎖 update_order(order.id, resp.status)# 正例本地計(jì)算完?duì)顟B(tài)后批量更新 def good_batch_update(): results [] for order in order_list: results.append((order.id, order.status)) call_payment_service_async(order.id) batch_update_orders(results)8. 高可用與讀寫分離突破單機(jī)瓶頸的架構(gòu)手段當(dāng)單臺(tái) MySQL 的讀寫性能達(dá)到瓶頸時(shí)首先要考慮的往往是讀寫分離而不是直接分庫(kù)分表。因?yàn)榇蟛糠謽I(yè)務(wù)是讀多寫少把讀流量分發(fā)到從庫(kù)可以顯著緩解主庫(kù)壓力。8.1 主從復(fù)制的原理與延遲問題MySQL 主從復(fù)制的核心原理是主庫(kù)把數(shù)據(jù)變更寫入 binlog從庫(kù)通過 IO 線程拉取 binlog 寫入自己的 relay log再由 SQL 線程重放 relay log 完成數(shù)據(jù)同步。要特別注意的是主從延遲。從庫(kù)重放是單線程執(zhí)行的8.0 之前是單線程8.0 之后可以在并行復(fù)制下緩解如果主庫(kù)寫并發(fā)很高從庫(kù)可能追不上主庫(kù)。主從延遲導(dǎo)致的典型問題就是剛插入的數(shù)據(jù)立刻從庫(kù)查詢查不到。解決方案包括關(guān)鍵業(yè)務(wù)強(qiáng)制走主庫(kù)。通過半同步復(fù)制減少延遲窗口。延遲敏感度高的場(chǎng)景使用緩存或直接讀主庫(kù)。8.2 讀寫分離在應(yīng)用層怎么落地以 Java 的 Spring 為例一個(gè)簡(jiǎn)單的思路是使用AbstractRoutingDataSource動(dòng)態(tài)數(shù)據(jù)源在事務(wù)開始時(shí)把只讀請(qǐng)求路由到從庫(kù)。public class ReadWriteRoutingDataSource extends AbstractRoutingDataSource { Override protected Object determineCurrentLookupKey() { String key DataSourceContextHolder.getDataSource(); return key; } }public class DataSourceContextHolder { private static final ThreadLocalString HOLDER new ThreadLocal(); public static void setDataSource(String key) { HOLDER.set(key); } public static String getDataSource() { return HOLDER.get(); } public static void clear() { HOLDER.remove(); } }然后在事務(wù)攔截器或 AOP 中根據(jù)方法名或注解判斷走主庫(kù)還是從庫(kù)。這個(gè)方案雖然簡(jiǎn)單但需要注意事務(wù)傳播行為避免一個(gè)讀寫事務(wù)中途切換數(shù)據(jù)源造成同一個(gè)事務(wù)里讀從庫(kù)、寫主庫(kù)的不一致問題。8.3 分庫(kù)分表最后手段而不是首選方案分庫(kù)分表可以解決單庫(kù)單表的數(shù)據(jù)量和寫入吞吐瓶頸但它會(huì)引入分布式事務(wù)、跨庫(kù) JOIN、全局唯一 ID、數(shù)據(jù)遷移等大量復(fù)雜度。所以我的觀點(diǎn)是分庫(kù)分表是最后手段不是首選方案。在做分庫(kù)分表之前建議先按順序評(píng)估以下幾個(gè)方案是否可以通過索引優(yōu)化、SQL 改寫解決性能問題。是否可以通過增加硬件資源、升級(jí)到更高配置解決問題。是否可以通過緩存Redis扛住熱點(diǎn)讀。是否可以通過歸檔歷史數(shù)據(jù)、冷熱分離減小核心表體積。是否可以通過讀寫分離解決讀壓力。最后才是分庫(kù)分表并且優(yōu)先考慮垂直拆分其次才是水平拆分。選擇分片鍵時(shí)要關(guān)注業(yè)務(wù)查詢的主要維度比如訂單表通常按user_id或order_id分片這樣用戶維度的查詢可以在單個(gè)分片內(nèi)完成避免跨庫(kù)查詢。分片鍵一旦定下來后續(xù)調(diào)整成本極高所以方案要足夠謹(jǐn)慎。9. 監(jiān)控、慢查詢分析與性能調(diào)優(yōu)閉環(huán)性能優(yōu)化不是一次性動(dòng)作而是一個(gè)持續(xù)閉環(huán)發(fā)現(xiàn)問題、定位原因、實(shí)施優(yōu)化、驗(yàn)證效果、持續(xù)監(jiān)控。企業(yè)級(jí) MySQL 實(shí)戰(zhàn)必須建立這個(gè)閉環(huán)。9.1 慢查詢?nèi)罩九c mysqldumpslow慢查詢?nèi)罩臼亲罨A(chǔ)的性能觀測(cè)手段。開啟之后它會(huì)記錄執(zhí)行時(shí)間超過long_query_time的 SQL。查看慢查詢?nèi)罩緝?nèi)容可以用mysqldumpslow工具做聚合統(tǒng)計(jì)mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log這條命令會(huì)按照平均執(zhí)行時(shí)間排序顯示最慢的 10 條 SQL。拿到慢 SQL 之后再用EXPLAIN逐步分析是一個(gè)標(biāo)準(zhǔn)動(dòng)作。9.2 常用性能狀態(tài)指標(biāo)除了慢查詢?nèi)罩具€需要關(guān)注 MySQL 的實(shí)時(shí)狀態(tài)變量SHOW GLOBAL STATUS LIKE Threads_running; SHOW GLOBAL STATUS LIKE Threads_connected; SHOW GLOBAL STATUS LIKE Innodb_row_lock_waits;Threads_running過高可能說明短查詢壓力過大或鎖等待嚴(yán)重。Threads_connected過高需要檢查連接池配置是否合理。Innodb_row_lock_waits持續(xù)增長(zhǎng)說明鎖競(jìng)爭(zhēng)明顯。生產(chǎn)環(huán)境建議接入 Prometheus Grafana 或者云廠商的 RDS 監(jiān)控體系把這些指標(biāo)做可視化設(shè)置告警規(guī)則才能在故障發(fā)生前及時(shí)介入。9.3 一條完整的調(diào)優(yōu)閉環(huán)示例假設(shè)線上反饋某個(gè)列表接口變慢。完整的排查鏈路應(yīng)該是打開慢查詢?nèi)罩菊业铰?SQL。用EXPLAIN查看執(zhí)行計(jì)劃確認(rèn)是全表掃描還是索引失效。確認(rèn)索引失效原因比如對(duì)索引列做了函數(shù)運(yùn)算。改寫 SQL將WHERE YEAR(create_time) 2023改寫為范圍查詢。在測(cè)試環(huán)境驗(yàn)證改寫后的執(zhí)行計(jì)劃和響應(yīng)時(shí)間。發(fā)布到生產(chǎn)環(huán)境觀察慢查詢數(shù)量是否下降。如果依然慢再?gòu)谋斫Y(jié)構(gòu)、鎖競(jìng)爭(zhēng)、業(yè)務(wù)邏輯層面繼續(xù)深入。這個(gè)閉環(huán)看起來不復(fù)雜但很多團(tuán)隊(duì)連第一步都做得不完整導(dǎo)致每次性能問題都像“玄學(xué)”。真正的高手不是憑感覺調(diào)優(yōu)而是讓每一步都有可觀測(cè)的數(shù)據(jù)支撐。10. 常見問題與排查思路在實(shí)際工作和學(xué)習(xí)過程中以下問題是出現(xiàn)頻率最高的。我整理成了排查表便于直接對(duì)照使用。問題現(xiàn)象可能原因排查方式解決方案查詢突然變慢數(shù)據(jù)量增長(zhǎng)導(dǎo)致索引效率下降查看執(zhí)行計(jì)劃確認(rèn)是否回表過多建立覆蓋索引優(yōu)化 SQL索引建了但不生效對(duì)索引列使用函數(shù)或隱式類型轉(zhuǎn)換查看執(zhí)行計(jì)劃 key 字段是否為 NULL改寫 SQL避免在索引列上做計(jì)算CPU 飆升慢查詢多或掃描行數(shù)過大開啟慢查詢?nèi)罩痉治?TOP SQL優(yōu)化 SQL增加合理索引死鎖頻繁多事務(wù)加鎖順序不一致SHOW ENGINE INNODB STATUS查看最近死鎖統(tǒng)一加鎖順序縮短事務(wù)時(shí)間主從延遲大主庫(kù)寫入壓力大或從庫(kù)并行復(fù)制不足查看Seconds_Behind_Master優(yōu)化寫入 SQL升級(jí)并行復(fù)制連接數(shù)打滿連接池配置過大或存在慢請(qǐng)求占連接查看Threads_connected調(diào)整連接池縮短事務(wù)執(zhí)行時(shí)間磁盤 IO 高頻繁回表、全表掃描觀察 IO 指標(biāo)分析 SQL使用覆蓋索引優(yōu)化查詢深分頁(yè)很慢LIMIT offset偏移量過大查看rows字段改用延遲關(guān)聯(lián)或游標(biāo)分頁(yè)11. 最佳實(shí)踐與工程建議最后把我在學(xué)習(xí)和實(shí)踐過程中比較認(rèn)可的 MySQL 企業(yè)級(jí)應(yīng)用原則做一個(gè)匯總。這些原則不一定每條都適用于所有項(xiàng)目但它們可以作為你設(shè)計(jì)和優(yōu)化時(shí)的檢查清單。第一條SQL 規(guī)范要前置。團(tuán)隊(duì)里應(yīng)該有統(tǒng)一的 SQL 編寫規(guī)范包括禁止SELECT *、禁止無 WHERE 條件的 UPDATE 和 DELETE、禁止在索引列上做函數(shù)運(yùn)算。通過 Code Review 和 SQL 審查工具在代碼進(jìn)生產(chǎn)之前就把問題攔下來。第二條索引寧缺毋濫。索引是給查詢用的不是給心靈安全感用的。每多一個(gè)索引寫入就多一份開銷。索引的新增應(yīng)該由真實(shí)業(yè)務(wù)查詢驅(qū)動(dòng)而不是預(yù)先堆砌。刪除索引也要謹(jǐn)慎最好有監(jiān)控?cái)?shù)據(jù)支持。第三條事務(wù)要短鎖范圍要小。事務(wù)里不放遠(yuǎn)程調(diào)用不放慢查詢不循環(huán)逐條操作。能批量更新就批量更新能縮小鎖范圍就縮小鎖范圍。第四條先看執(zhí)行計(jì)劃再做優(yōu)化。任何 SQL 優(yōu)化都不應(yīng)該脫離EXPLAIN。很多問題在編寫 SQL 的時(shí)候就能通過執(zhí)行計(jì)劃提前發(fā)現(xiàn)避免上線后才排查。第五條線上變更要有回滾方案。無論是加索引、改 SQL 還是調(diào)整事務(wù)邏輯都要做測(cè)試驗(yàn)證并保證可以在線上快速回滾。比如一個(gè) SQL 改寫方案除了驗(yàn)證執(zhí)行計(jì)劃和響應(yīng)時(shí)間還要關(guān)注它對(duì)業(yè)務(wù)結(jié)果是否有影響。第六條監(jiān)控要比故障先到。如果你等到用戶反饋才發(fā)現(xiàn)數(shù)據(jù)庫(kù)變慢說明監(jiān)控體系是缺失的。慢查詢?nèi)罩?、CPU、連接數(shù)、鎖等待、主從延遲這些核心指標(biāo)都應(yīng)該在系統(tǒng)上線第一天就接入監(jiān)控。第七條數(shù)據(jù)歸檔要制度化。業(yè)務(wù)表的數(shù)據(jù)不是永遠(yuǎn)都在增長(zhǎng)很多歷史數(shù)據(jù)可以通過歸檔表、冷熱分離等方式移出核心業(yè)務(wù)庫(kù)。這能有效控制大表體積減少查詢和備份壓力。12. 總結(jié)與后續(xù)學(xué)習(xí)方向高性能 MySQL 實(shí)戰(zhàn)這件事本質(zhì)上不是學(xué)會(huì)幾個(gè)命令、記住幾條優(yōu)化技巧而是建立一套完整的“設(shè)計(jì)-開發(fā)-運(yùn)維”鏈路認(rèn)知。從 Schema 設(shè)計(jì)階段決定字段類型和主鍵策略到 SQL 編寫階段關(guān)注索引命中和執(zhí)行計(jì)劃再到事務(wù)設(shè)計(jì)階段控制鎖粒度和事務(wù)長(zhǎng)度最后到架構(gòu)層面決定讀寫分離和分庫(kù)分表每一個(gè)環(huán)節(jié)都相互關(guān)聯(lián)。任何一個(gè)環(huán)節(jié)的短板都會(huì)在數(shù)據(jù)量和并發(fā)上升到一定程度后暴露出來。對(duì)于剛接觸 MySQL 性能優(yōu)化的讀者我的建議是先做三件事第一把EXPLAIN用熟拿到任何一條慢 SQL 都能看懂執(zhí)行計(jì)劃第二把索引失效的幾種場(chǎng)景牢記寫 SQL 時(shí)主動(dòng)規(guī)避第三在自己的本地或測(cè)試環(huán)境搭一套帶監(jiān)控的最小系統(tǒng)跑通“慢查詢發(fā)現(xiàn)-SQL 改寫-驗(yàn)證效果”的完整閉環(huán)。這三件事做完你已經(jīng)超過大多數(shù)只會(huì)寫 CRUD 的同學(xué)。后續(xù)可以繼續(xù)深入的方向包括InnoDB 底層原理與 redo log、binlog 的協(xié)作機(jī)制MySQL 8.0 的成本優(yōu)化模型分區(qū)表的設(shè)計(jì)與限制分布式事務(wù)方案如 Seata以及云數(shù)據(jù)庫(kù)和自建數(shù)據(jù)庫(kù)在治理模式上的差異。MySQL 這個(gè)領(lǐng)域看起來入門門檻低但真正走到深處會(huì)發(fā)現(xiàn)它連接著操作系統(tǒng)、存儲(chǔ)、網(wǎng)絡(luò)、分布式系統(tǒng)幾乎所有后端基礎(chǔ)知識(shí)。深入進(jìn)去瓶頸越少解決問題的確定性就越高。 MySQL企業(yè)級(jí)性能優(yōu)化實(shí)戰(zhàn)：從索引到高可用架構(gòu)的完整鏈路
“MySQL 好像變慢了”“線上一條查詢跑了 8 秒”“接口偶爾卡死DBA 說是鎖等待”……這些數(shù)據(jù)庫(kù)問題幾乎每個(gè)做后端開發(fā)的程序員都遇到過。尤其在企業(yè)級(jí)項(xiàng)目里MySQL 早已不是“裝個(gè)庫(kù)、建個(gè)表、寫個(gè) CRUD”這么簡(jiǎn)單數(shù)據(jù)量一上來索引沒建對(duì)一條 SQL 就能拖垮整個(gè)服務(wù)并發(fā)一高鎖和事務(wù)隔離級(jí)別沒搞清楚線上就會(huì)頻繁出現(xiàn)死鎖和超時(shí)。市面上講 MySQL 的資料很多但大多要么停留在基礎(chǔ)語(yǔ)法要么直接上升到分布式中間件真正能把“企業(yè)級(jí)實(shí)戰(zhàn)”這條路走通、走順、走完整的教程并不多。這也是高性能 MySQL 實(shí)戰(zhàn)類內(nèi)容最近持續(xù)受到關(guān)注的原因開發(fā)者需要的不是概念拼盤而是一套能真正落地到項(xiàng)目里的性能設(shè)計(jì)、優(yōu)化方法和排錯(cuò)思路。這篇文章我想用自己的學(xué)習(xí)和實(shí)踐視角把 MySQL 企業(yè)級(jí)應(yīng)用中最關(guān)鍵的性能問題、優(yōu)化路徑和實(shí)戰(zhàn)案例做一個(gè)系統(tǒng)梳理。內(nèi)容會(huì)覆蓋數(shù)據(jù)庫(kù)架構(gòu)與引擎原理、Schema 設(shè)計(jì)、索引優(yōu)化、SQL 改寫、事務(wù)與鎖、高可用架構(gòu)、監(jiān)控告警這些核心模塊。尤其會(huì)側(cè)重那些“看起來簡(jiǎn)單、真正做起來容易踩坑”的環(huán)節(jié)比如聯(lián)合索引的最左前綴到底怎么用、為什么不建議在索引列上做函數(shù)運(yùn)算、可重復(fù)讀隔離級(jí)別下到底會(huì)不會(huì)出現(xiàn)幻讀、分庫(kù)分表之前一定要先做哪些評(píng)估。讀完之后即使你還沒有機(jī)會(huì)在生產(chǎn)環(huán)境里操刀大型項(xiàng)目也至少能建立一條完整的高性能 MySQL 優(yōu)化鏈路拿到一個(gè)慢 SQL知道從哪下手設(shè)計(jì)一張業(yè)務(wù)表知道索引該怎么規(guī)劃系統(tǒng)出現(xiàn)鎖等待知道去哪里看、怎么解。這就是本文最想交付給你的價(jià)值。1. 企業(yè)級(jí) MySQL 為什么需要一套“性能方法論”很多人對(duì) MySQL 性能優(yōu)化的理解停留在“建索引”和“寫 SQL 的時(shí)候注意一下”這個(gè)層面。但如果你真正參與過企業(yè)級(jí)項(xiàng)目會(huì)發(fā)現(xiàn)性能問題遠(yuǎn)不是這么簡(jiǎn)單。企業(yè)級(jí)應(yīng)用和個(gè)人項(xiàng)目、課程作業(yè)有一個(gè)本質(zhì)區(qū)別它的狀態(tài)是持續(xù)演進(jìn)的數(shù)據(jù)是持續(xù)增長(zhǎng)的并發(fā)是持續(xù)存在的。今天一張表 10 萬(wàn)條數(shù)據(jù)隨便怎么查都很快到了 5000 萬(wàn)條即使有索引也可能因?yàn)樗饕O(shè)計(jì)不合理出現(xiàn)回表過多、隨機(jī) IO 暴漲。更麻煩的是系統(tǒng)一旦上線很多結(jié)構(gòu)性問題就很難推倒重來字段類型不合適要改涉及數(shù)據(jù)遷移索引建得不對(duì)要調(diào)涉及線上 DDL事務(wù)粒度太大導(dǎo)致鎖范圍擴(kuò)大涉及代碼重構(gòu)。這些問題的根源往往不是在寫某一條 SQL 時(shí)才出現(xiàn)的而是在表結(jié)構(gòu)設(shè)計(jì)、框架選型、事務(wù)邊界劃分這些更早的環(huán)節(jié)就埋下了伏筆。所以企業(yè)級(jí) MySQL 的性能認(rèn)知本質(zhì)上是一套前置的方法論在設(shè)計(jì)階段預(yù)判未來的數(shù)據(jù)量和訪問模式在開發(fā)階段寫出能高效利用索引的 SQL在運(yùn)維階段通過監(jiān)控和慢查詢?nèi)罩境掷m(xù)發(fā)現(xiàn)問題在架構(gòu)階段通過主從復(fù)制、讀寫分離、分庫(kù)分表來突破單機(jī)瓶頸。這條鏈路里任何一個(gè)環(huán)節(jié)缺失都會(huì)在流量上來之后以線上故障的形式暴露出來。另外還有一個(gè)很容易被忽視的點(diǎn)MySQL 性能優(yōu)化并不是 DBA 一個(gè)人的事情。開發(fā)人員寫的每一條 SQL、設(shè)計(jì)的每一張表、選擇的每一個(gè) ORM 用法都在直接影響數(shù)據(jù)庫(kù)的負(fù)載。一個(gè)連EXPLAIN都不會(huì)看的后端程序員和一個(gè)能從執(zhí)行計(jì)劃里快速判斷索引是否命中的后端程序員在同一個(gè)團(tuán)隊(duì)里產(chǎn)出的系統(tǒng)性能差距可能是數(shù)量級(jí)的。這也是我認(rèn)為每個(gè) Java 后端、Go 后端、Python 后端開發(fā)者都應(yīng)該認(rèn)真看一輪 MySQL 實(shí)戰(zhàn)內(nèi)容的原因。2. MySQL 核心架構(gòu)與性能模型在討論具體優(yōu)化手段之前有必要先把 MySQL 的整體架構(gòu)講清楚。很多調(diào)優(yōu)動(dòng)作之所以讓人迷惑是因?yàn)槟愀静恢酪粭l SQL 在數(shù)據(jù)庫(kù)內(nèi)部到底經(jīng)歷了什么。2.1 一條 SQL 的執(zhí)行鏈路MySQL 從整體上可以分為兩層Server 層和存儲(chǔ)引擎層。Server 層負(fù)責(zé)連接管理、語(yǔ)法解析、查詢優(yōu)化、執(zhí)行計(jì)劃生成存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的實(shí)際存儲(chǔ)和讀取。常見的 InnoDB 就是一個(gè)存儲(chǔ)引擎也是目前 MySQL 默認(rèn)且最常用的引擎。一條查詢 SQL 的執(zhí)行過程大致是客戶端通過連接器建立連接進(jìn)行身份認(rèn)證。查詢緩存8.0 之前有8.0 之后已移除檢查是否命中緩存。分析器做詞法分析和語(yǔ)法分析生成語(yǔ)法樹。優(yōu)化器決定使用哪個(gè)索引、以什么順序關(guān)聯(lián)多張表生成執(zhí)行計(jì)劃。執(zhí)行器調(diào)用存儲(chǔ)引擎接口逐行讀取數(shù)據(jù)并返回結(jié)果。這個(gè)鏈路里優(yōu)化器是最關(guān)鍵也最容易被誤解的部分。你以為你寫的 SQL 會(huì)按照“你想象的順序”執(zhí)行但實(shí)際上優(yōu)化器會(huì)基于統(tǒng)計(jì)信息選擇它認(rèn)為成本最低的執(zhí)行路徑。有時(shí)候你明明建了索引優(yōu)化器卻選擇了全表掃描這可能是因?yàn)樗J(rèn)為回表成本比全表掃描還高也可能是因?yàn)榻y(tǒng)計(jì)信息過期。2.2 InnoDB 與 MyISAM 的核心差異很多初學(xué)者會(huì)問InnoDB 和 MyISAM 到底有什么區(qū)別為什么現(xiàn)在幾乎都推薦 InnoDB核心差異可以總結(jié)為下表對(duì)比維度InnoDBMyISAM事務(wù)支持支持 ACID 事務(wù)不支持事務(wù)鎖粒度支持行級(jí)鎖只有表級(jí)鎖崩潰恢復(fù)支持通過 redo log 恢復(fù)不支持崩潰安全恢復(fù)外鍵支持支持不支持聚集索引有數(shù)據(jù)按主鍵順序存儲(chǔ)無數(shù)據(jù)和索引分離適用場(chǎng)景企業(yè)級(jí) OLTP 業(yè)務(wù)只讀、日志分析類場(chǎng)景已逐漸邊緣化變化判斷從 MySQL 5.5 開始InnoDB 就是默認(rèn)存儲(chǔ)引擎到了 8.0MyISAM 的所有系統(tǒng)表都被 InnoDB 取代。如果你還在新項(xiàng)目里主動(dòng)指定 MyISAM除非有非常特殊的只讀報(bào)表需求否則幾乎找不到理由。2.3 為什么“性能模型”比“單條 SQL 快”更重要在企業(yè)級(jí)系統(tǒng)里數(shù)據(jù)庫(kù)性能不只是“單條查詢快不快”而是“系統(tǒng)在持續(xù)負(fù)載下的吞吐量和延遲是否穩(wěn)定”。一個(gè)每秒只能支撐 100 次查詢、但每次查詢只要 5ms 的系統(tǒng)和一個(gè)每秒能支撐 5000 次查詢、平均 20ms 的系統(tǒng)后者的業(yè)務(wù)價(jià)值往往大得多。因此后續(xù)所有的優(yōu)化手段都要回歸到兩個(gè)核心指標(biāo)QPS每秒查詢數(shù)衡量數(shù)據(jù)庫(kù)吞吐能力。響應(yīng)時(shí)間衡量單次請(qǐng)求延遲通常關(guān)注 p95、p99 而不是平均值。理解了這一點(diǎn)你再看很多優(yōu)化建議時(shí)就會(huì)明白其背后的指向減少回表是為了降低隨機(jī) IO使用覆蓋索引是為了減少數(shù)據(jù)頁(yè)訪問批量寫入是為了減少 redo log 刷盤次數(shù)連接池是為了減少線程頻繁創(chuàng)建銷毀的開銷。所有手段的目的都是在單位時(shí)間內(nèi)讓數(shù)據(jù)庫(kù)做更少無效工作從而支撐更高的吞吐。3. 環(huán)境準(zhǔn)備搭建一套可復(fù)現(xiàn)的 MySQL 學(xué)習(xí)與測(cè)試環(huán)境實(shí)戰(zhàn)教程最怕環(huán)境不一致。為了確保后續(xù)示例可以運(yùn)行我們需要在一臺(tái)干凈的機(jī)器上準(zhǔn)備 MySQL 環(huán)境。這里我推薦使用 Docker 來搭建原因有兩個(gè)一是版本切換方便不會(huì)污染宿主機(jī)二是可以隨時(shí)刪除重建適合反復(fù)練習(xí)。3.1 使用 Docker 安裝 MySQL 8.0在開始之前確認(rèn)機(jī)器上已經(jīng)安裝了 Docker。然后執(zhí)行下面的命令docker pull mysql:8.0啟動(dòng)一個(gè) MySQL 容器并做基本配置docker run -d \ --name mysql-practice \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDroot123456 \ -e MYSQL_DATABASEcompany \ mysql:8.0 \ --character-set-serverutf8mb4 \ --collation-serverutf8mb4_unicode_ci這條命令里MYSQL_ROOT_PASSWORD設(shè)置了 root 密碼MYSQL_DATABASEcompany會(huì)自動(dòng)創(chuàng)建一個(gè)名為 company 的數(shù)據(jù)庫(kù)。--character-set-serverutf8mb4和--collation-serverutf8mb4_unicode_ci是企業(yè)級(jí)項(xiàng)目必須注意的兩個(gè)參數(shù)它們保證數(shù)據(jù)庫(kù)能夠正確存儲(chǔ)中文和 emoji 等四字節(jié)字符。查看容器是否正常運(yùn)行docker ps | grep mysql-practice進(jìn)入容器并使用命令行連接docker exec -it mysql-practice mysql -uroot -proot1234563.2 準(zhǔn)備測(cè)試數(shù)據(jù)為了模擬真實(shí)業(yè)務(wù)場(chǎng)景我們創(chuàng)建一張員工表和一張部門表并插入一定量的數(shù)據(jù)。這里的數(shù)據(jù)量可以不必太大重點(diǎn)是理解執(zhí)行計(jì)劃。CREATE DATABASE IF NOT EXISTS company DEFAULT CHARSET utf8mb4; USE company; CREATE TABLE department ( dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(50) NOT NULL ) ENGINEInnoDB; CREATE TABLE employee ( emp_id INT PRIMARY KEY AUTO_INCREMENT, emp_no VARCHAR(20) NOT NULL, emp_name VARCHAR(50) NOT NULL, age INT NOT NULL, dept_id INT NOT NULL, salary DECIMAL(10, 2) NOT NULL, hire_date DATE NOT NULL, KEY idx_dept_id (dept_id), KEY idx_hire_date (hire_date) ) ENGINEInnoDB;這里先建立兩個(gè)索引idx_dept_id和idx_hire_date它們的用途會(huì)在后續(xù) SQL 優(yōu)化案例里反復(fù)體現(xiàn)。插入測(cè)試數(shù)據(jù)時(shí)可以使用存儲(chǔ)過程或連接查詢批量插入。這里給一個(gè)簡(jiǎn)單的存儲(chǔ)過程示例DELIMITER $$ CREATE PROCEDURE insert_employee_data() BEGIN DECLARE i INT DEFAULT 1; WHILE i 10000 DO INSERT INTO employee (emp_no, emp_name, age, dept_id, salary, hire_date) VALUES ( CONCAT(EMP, LPAD(i, 6, 0)), CONCAT(員工, i), 20 (i % 30), 1 (i % 10), 5000 (i % 50000), DATE_ADD(2015-01-01, INTERVAL (i % 3000) DAY) ); SET i i 1; END WHILE; END$$ DELIMITER ; CALL insert_employee_data();插入完成后確認(rèn)數(shù)據(jù)量SELECT COUNT(*) FROM employee;3.3 環(huán)境就緒后下一步做什么環(huán)境就緒后建議你先做一件事情打開 MySQL 的慢查詢?nèi)罩景验撝翟O(shè)置得低一些。這樣后面執(zhí)行任何測(cè)試 SQL都能快速判斷它是否屬于慢查詢。這也是企業(yè)級(jí)調(diào)優(yōu)的第一步——先建立可觀測(cè)性再談優(yōu)化。在容器中執(zhí)行mysql -uroot -proot123456 -e SET GLOBAL slow_query_log ON; mysql -uroot -proot123456 -e SET GLOBAL long_query_time 1;這樣超過 1 秒的查詢都會(huì)被記錄到慢查詢?nèi)罩局?。生產(chǎn)環(huán)境一般建議閾值設(shè)置在 1 秒或更低具體需要結(jié)合實(shí)際業(yè)務(wù)判斷。4. 數(shù)據(jù)庫(kù)設(shè)計(jì)性能問題從 Schema 階段就開始很多性能問題表面上是 SQL 慢根源卻是表結(jié)構(gòu)設(shè)計(jì)不合理。所以真正的高性能 MySQL 實(shí)戰(zhàn)一定要從 Schema 設(shè)計(jì)講起。4.1 字段類型選擇不要圖省事用大字段一個(gè)最常見的錯(cuò)誤是把所有字段都設(shè)計(jì)成VARCHAR(255)或者更夸張的TEXT。這種做法會(huì)導(dǎo)致幾個(gè)問題數(shù)據(jù)頁(yè)能容納的行數(shù)變少同樣一張表需要更多數(shù)據(jù)頁(yè)掃描成本更高。索引字段如果過長(zhǎng)索引體積變大緩存命中率下降。TEXT類型的字段在內(nèi)存中臨時(shí)表排序時(shí)會(huì)導(dǎo)致磁盤臨時(shí)表性能驟降。字段類型選擇的基本原則是夠用就好。狀態(tài)值用TINYINT金額用DECIMAL定長(zhǎng)短字符串用CHAR變長(zhǎng)字符串用合理的VARCHAR長(zhǎng)度日期用DATE或DATETIME不建議用字符串存儲(chǔ)日期。-- 反例所有字段都用 VARCHAR CREATE TABLE bad_example ( id VARCHAR(20) PRIMARY KEY, status VARCHAR(10), create_time VARCHAR(30) ); -- 正例合理選擇字段類型 CREATE TABLE good_example ( id INT PRIMARY KEY AUTO_INCREMENT, status TINYINT NOT NULL DEFAULT 0 COMMENT 0-未處理 1-已處理, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 創(chuàng)建時(shí)間 );4.2 主鍵設(shè)計(jì)自增主鍵 vs 業(yè)務(wù)主鍵InnoDB 是聚集索引組織表數(shù)據(jù)行實(shí)際上是按主鍵順序存儲(chǔ)在 B 樹葉子節(jié)點(diǎn)上的。這意味著主鍵的選擇會(huì)直接影響寫入性能和空間使用。自增主鍵由于新值總是比舊值大插入時(shí)只需要順序追加不需要頻繁移動(dòng)已有數(shù)據(jù)因此寫入性能最好。業(yè)務(wù)主鍵如果是無序的字符串或 UUID插入時(shí)就可能觸發(fā)頁(yè)分裂造成隨機(jī) IO 和碎片。-- 推薦自增主鍵 CREATE TABLE order_info ( order_id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, UNIQUE KEY uk_order_no (order_no) ) ENGINEInnoDB; -- 不推薦直接用 UUID 字符串做主鍵 -- 會(huì)導(dǎo)致 B 樹頻繁頁(yè)分裂寫入性能差索引體積大要注意的是這里說的是“主鍵”不要用 UUID但業(yè)務(wù)唯一標(biāo)識(shí)如order_no可以單獨(dú)建唯一索引。這樣既保證業(yè)務(wù)查詢需要又不犧牲聚集索引的寫入性能。4.3 反范式設(shè)計(jì)適度冗余減少關(guān)聯(lián)查詢?cè)谄髽I(yè)級(jí)項(xiàng)目中完全遵循數(shù)據(jù)庫(kù)三范式并不現(xiàn)實(shí)。三范式把數(shù)據(jù)拆得越細(xì)表之間的關(guān)聯(lián)查詢就越多JOIN 的開銷就越大。實(shí)際優(yōu)化中經(jīng)常采用適度反范式在訂單表里冗余一份用戶名在統(tǒng)計(jì)表里預(yù)計(jì)算每日匯總值。但反范式設(shè)計(jì)不是越冗余越好。冗余字段最大的問題是數(shù)據(jù)一致性和維護(hù)成本。解決方案可以依賴事務(wù)、應(yīng)用層代碼或定時(shí)任務(wù)來同步。這個(gè)權(quán)衡要結(jié)合具體業(yè)務(wù)來決定原則是高頻查詢的維度才值得冗余低頻更新且允許短暫不一致的字段才適合冗余。5. 索引優(yōu)化高性能查詢的第一道防線索引是 MySQL 性能優(yōu)化里性價(jià)比最高的手段。一個(gè)合適的索引可以把全表掃描的幾十秒降到毫秒級(jí)。但索引也不是越多越好它本身會(huì)占用空間并且拖慢寫入速度。所以索引設(shè)計(jì)需要結(jié)合業(yè)務(wù)查詢模式來做。5.1 B 樹索引為什么適合數(shù)據(jù)庫(kù)InnoDB 的索引底層是 B 樹。B 樹相比二叉樹、哈希索引有幾個(gè)核心優(yōu)勢(shì)樹的高度低一般三層就能存千萬(wàn)級(jí)數(shù)據(jù)查找次數(shù)穩(wěn)定。葉子節(jié)點(diǎn)通過鏈表連接適合范圍查詢。數(shù)據(jù)在葉子節(jié)點(diǎn)按順序排列排序和分組可以利用索引有序性。理解 B 樹的有序性非常重要。很多優(yōu)化技巧比如聯(lián)合索引最左前綴、索引下推、覆蓋索引都是建立在“索引數(shù)據(jù)有序”這個(gè)基礎(chǔ)之上的。5.2 聯(lián)合索引的最左前綴原則假設(shè)我們建了一個(gè)聯(lián)合索引(dept_id, hire_date, emp_no)。這個(gè)索引的實(shí)際結(jié)構(gòu)是先按dept_id排序dept_id相同的按hire_date排序兩者都相同的再按emp_no排序。因此在查詢時(shí)只有遵循最左前綴原則才能使用到這個(gè)索引。-- 能用到聯(lián)合索引 SELECT * FROM employee WHERE dept_id 3; SELECT * FROM employee WHERE dept_id 3 AND hire_date 2020-01-01; SELECT * FROM employee WHERE dept_id 3 AND hire_date 2020-01-01 AND emp_no EMP000123; -- 不能用到聯(lián)合索引 SELECT * FROM employee WHERE hire_date 2020-01-01; SELECT * FROM employee WHERE emp_no EMP000123;最左前綴原則的真正含義是聯(lián)合索引的任何一個(gè)前綴子集都可以獨(dú)立使用但跳過前置列直接使用后面的列則無法命中索引。因此創(chuàng)建聯(lián)合索引時(shí)字段順序非常關(guān)鍵將等值查詢的字段放在前面。將范圍查詢的字段放在后面。將區(qū)分度高的字段放在前面。5.3 回表、索引覆蓋與索引下推InnoDB 普通索引的葉子節(jié)點(diǎn)存的是主鍵值。如果查詢的列在普通索引中不存在就需要通過主鍵再回表查詢一次這個(gè)過程叫回表?；乇頃?huì)帶來額外的隨機(jī) IO。覆蓋索引可以讓一個(gè)查詢只掃描索引就拿到所有需要的列不需要回表。設(shè)計(jì)覆蓋索引時(shí)要把 SELECT 的字段也考慮進(jìn)索引。-- employee 表有 idx_dept_id (dept_id) -- 這條 SQL 需要回表查詢 emp_name 和 salary SELECT emp_name, salary FROM employee WHERE dept_id 3; -- 如果改成覆蓋索引 idx_dept_id_name (dept_id, emp_name, salary) -- 這條 SQL 就不需要回表索引本身已經(jīng)包含了所有需要的列索引下推是 MySQL 5.6 引入的優(yōu)化。假設(shè)索引是(dept_id, hire_date)查詢條件是dept_id 3 AND hire_date 2020-01-01在沒有索引下推時(shí)存儲(chǔ)引擎需要把所有dept_id 3的記錄都回表再在 Server 層過濾 hire_date有了索引下推之后hire_date的過濾條件會(huì)在存儲(chǔ)引擎層直接處理減少回表次數(shù)。在實(shí)際工作中用EXPLAIN查看執(zhí)行計(jì)劃時(shí)如果看到Using index condition說明索引下推生效了。這是一個(gè)積極信號(hào)。5.4 最容易被忽視的索引失效場(chǎng)景有幾類 SQL 寫法會(huì)導(dǎo)致索引失效即使你建了索引也白建場(chǎng)景示例原因?qū)λ饕凶龊瘮?shù)運(yùn)算WHERE YEAR(hire_date) 2020函數(shù)破壞了索引列的有序性隱式類型轉(zhuǎn)換WHERE emp_no 123emp_no 為字符串類型轉(zhuǎn)換導(dǎo)致無法匹配索引前導(dǎo)模糊查詢WHERE emp_name LIKE %張三字符串前綴無法確定索引無法定位OR 連接非索引條件WHERE dept_id 3 OR age 30優(yōu)化器可能選擇全表掃描-- 反例對(duì)索引列使用函數(shù) SELECT * FROM employee WHERE YEAR(hire_date) 2020; -- 正例改寫為范圍查詢 SELECT * FROM employee WHERE hire_date 2020-01-01 AND hire_date 2021-01-01;記住一個(gè)核心原則不要在索引列上做計(jì)算。6. SQL 優(yōu)化實(shí)戰(zhàn)從執(zhí)行計(jì)劃到改寫技巧SQL 優(yōu)化不能靠猜必須基于執(zhí)行計(jì)劃來分析。MySQL 提供了EXPLAIN命令可以查看一條 SQL 的執(zhí)行計(jì)劃。掌握它你才能從“看 SQL 靠感覺”進(jìn)化到“看執(zhí)行計(jì)劃定位問題”。6.1 讀懂 EXPLAIN 的關(guān)鍵列以一條實(shí)際查詢?yōu)槔鼸XPLAIN SELECT emp_name, salary FROM employee WHERE dept_id 3 AND age 25;輸出中需要重點(diǎn)關(guān)注的列包括列名含義重點(diǎn)關(guān)注點(diǎn)type訪問類型const、ref、range優(yōu)于ALL全表掃描key實(shí)際使用的索引是否為 NULLNULL 代表沒走索引rows預(yù)估掃描行數(shù)越小越好filtered過濾比例代表 Server 層還要過濾多少行Extra額外信息出現(xiàn)Using filesort、Using temporary要警惕如果type ALL并且rows很大說明這條 SQL 在做全表掃描是首要優(yōu)化對(duì)象。6.2 分頁(yè)查詢優(yōu)化深分頁(yè)帶來的性能災(zāi)難后臺(tái)管理列表最常見的做法是LIMIT offset, size。當(dāng)頁(yè)數(shù)足夠深時(shí)偏移量會(huì)非常大MySQL 需要掃描并丟棄前面所有的行才能拿到目標(biāo)數(shù)據(jù)。-- 深分頁(yè)性能極差offset 越大掃描越多 SELECT * FROM employee ORDER BY hire_date DESC LIMIT 100000, 20;優(yōu)化方式有兩種常見方案。第一種是延遲關(guān)聯(lián)先用覆蓋索引查出主鍵再通過主鍵關(guān)聯(lián)回原表獲取完整數(shù)據(jù)。SELECT e.* FROM employee e INNER JOIN ( SELECT emp_id FROM employee ORDER BY hire_date DESC LIMIT 100000, 20 ) t ON e.emp_id t.emp_id;第二種是基于排序字段的游標(biāo)分頁(yè)適合滾動(dòng)加載場(chǎng)景-- 記住上一頁(yè)最后一條記錄的 hire_date 和 emp_id SELECT * FROM employee WHERE (hire_date, emp_id) (2023-05-20, 50020) ORDER BY hire_date DESC, emp_id DESC LIMIT 20;這種寫法充分利用索引的有序性不需要掃描和丟棄臟數(shù)據(jù)頁(yè)數(shù)越深優(yōu)勢(shì)越明顯。6.3 JOIN 優(yōu)化小表驅(qū)動(dòng)大表在多表關(guān)聯(lián)時(shí)MySQL 的優(yōu)化器通常會(huì)選擇“小表驅(qū)動(dòng)大表”的策略。因此在寫 JOIN 時(shí)盡量讓小表作為驅(qū)動(dòng)表大表作為被驅(qū)動(dòng)表并在被驅(qū)動(dòng)表的連接字段上建立索引。-- 推薦被驅(qū)動(dòng)表 department 的主鍵索引就足夠 SELECT e.emp_name, d.dept_name FROM employee e INNER JOIN department d ON e.dept_id d.dept_id WHERE e.age 30;如果被驅(qū)動(dòng)表的關(guān)聯(lián)字段上沒有索引優(yōu)化器可能選擇Block Nested-Loop Join也就是把所有滿足條件的驅(qū)動(dòng)表數(shù)據(jù)放入 join buffer再全表掃描被驅(qū)動(dòng)表做匹配性能會(huì)差很多。6.4 聚合查詢優(yōu)化避免臨時(shí)表和文件排序GROUP BY 和 ORDER BY 是出現(xiàn)Using temporary和Using filesort的高發(fā)場(chǎng)景。如果 GROUP BY 的字段不是索引字段MySQL 就不得不在內(nèi)存或磁盤上創(chuàng)建臨時(shí)表。-- 可能出現(xiàn) Using temporary SELECT dept_id, COUNT(*) FROM employee GROUP BY dept_id; -- 如果 dept_id 有索引可以通過索引有序掃描直接完成分組 -- 無需額外排序更徹底的優(yōu)化思路是使用匯總表。比如統(tǒng)計(jì)每個(gè)部門的員工數(shù)如果業(yè)務(wù)對(duì)實(shí)時(shí)性要求不高可以每天晚上跑定時(shí)任務(wù)生成匯總表業(yè)務(wù)側(cè)直接查詢匯總表。這就是從“實(shí)時(shí)計(jì)算”到“預(yù)計(jì)算”的典型優(yōu)化。7. 事務(wù)、鎖與并發(fā)控制高并發(fā)場(chǎng)景下數(shù)據(jù)庫(kù)最容易出現(xiàn)的兩大問題就是鎖等待和死鎖。這背后其實(shí)是事務(wù)隔離級(jí)別、鎖機(jī)制、事務(wù)粒度的綜合問題。7.1 事務(wù)隔離級(jí)別如何影響并發(fā)能力MySQL InnoDB 默認(rèn)隔離級(jí)別是REPEATABLE READ可重復(fù)讀。和 Oracle、PostgreSQL 默認(rèn)的READ COMMITTED不同這個(gè)選擇主要是歷史原因——MySQL 的 binlog 在STATEMENT格式下只有在可重復(fù)讀級(jí)別才能保證主從復(fù)制的一致性。但在 MySQL 8.0 中ROW格式已經(jīng)是默認(rèn) binlog 格式隔離級(jí)別的選擇反而更靈活了。-- 查看當(dāng)前隔離級(jí)別 SELECT transaction_isolation;可重復(fù)讀通過MVCC 間隙鎖在大多數(shù)場(chǎng)景下避免了“不可重復(fù)讀”和“幻讀”。但在高并發(fā)寫入場(chǎng)景中間隙鎖會(huì)擴(kuò)大鎖范圍加大鎖等待的概率。如果業(yè)務(wù)對(duì)一致性要求允許放寬可以評(píng)估是否使用READ COMMITTED因?yàn)?RC 級(jí)別只有記錄鎖沒有間隙鎖并發(fā)能力更高。隔離級(jí)別的選擇是一個(gè)典型的業(yè)務(wù)需求與并發(fā)能力的權(quán)衡沒有絕對(duì)的好壞。7.2 死鎖是怎么產(chǎn)生的死鎖的本質(zhì)是多個(gè)事務(wù)以不同順序持有資源互相等待。經(jīng)典的例子是事務(wù) A 先更新表 1 再更新表 2事務(wù) B 先更新表 2 再更新表 1兩個(gè)事務(wù)同時(shí)提交時(shí)就有概率死鎖。-- 事務(wù)A先更新 dept_id1 再更新 dept_id2 BEGIN; UPDATE employee SET salary salary 100 WHERE dept_id 1; UPDATE employee SET salary salary 100 WHERE dept_id 2; COMMIT; -- 事務(wù)B先更新 dept_id2 再更新 dept_id1 BEGIN; UPDATE employee SET salary salary 100 WHERE dept_id 2; UPDATE employee SET salary salary 100 WHERE dept_id 1; COMMIT;要避免死鎖核心有幾個(gè)方向保持一致的加鎖順序讓所有事務(wù)都按同樣的順序更新記錄。控制事務(wù)粒度不要在一個(gè)事務(wù)里執(zhí)行太多無關(guān)操作減少持鎖時(shí)間。在無法避免死鎖時(shí)通過重試機(jī)制處理死鎖報(bào)錯(cuò)而不是直接讓接口失敗。7.3 大事務(wù)慢查詢和鎖等待的隱形殺手一個(gè)事務(wù)里執(zhí)行了大量 INSERT、UPDATE 或包含遠(yuǎn)程調(diào)用會(huì)導(dǎo)致持鎖時(shí)間過長(zhǎng)輕則拖慢并發(fā)性能重則引發(fā)大規(guī)模鎖等待和主從延遲。實(shí)踐中應(yīng)該遵守幾個(gè)原則事務(wù)中避免遠(yuǎn)程調(diào)用避免循環(huán)逐條更新避免一次性處理過多數(shù)據(jù)。# 反例事務(wù)中做遠(yuǎn)程調(diào)用 循環(huán)更新 def bad_batch_update(): with transaction(): for order in order_list: resp call_payment_service(order.id) # 網(wǎng)絡(luò)調(diào)用持鎖 update_order(order.id, resp.status)# 正例本地計(jì)算完?duì)顟B(tài)后批量更新 def good_batch_update(): results [] for order in order_list: results.append((order.id, order.status)) call_payment_service_async(order.id) batch_update_orders(results)8. 高可用與讀寫分離突破單機(jī)瓶頸的架構(gòu)手段當(dāng)單臺(tái) MySQL 的讀寫性能達(dá)到瓶頸時(shí)首先要考慮的往往是讀寫分離而不是直接分庫(kù)分表。因?yàn)榇蟛糠謽I(yè)務(wù)是讀多寫少把讀流量分發(fā)到從庫(kù)可以顯著緩解主庫(kù)壓力。8.1 主從復(fù)制的原理與延遲問題MySQL 主從復(fù)制的核心原理是主庫(kù)把數(shù)據(jù)變更寫入 binlog從庫(kù)通過 IO 線程拉取 binlog 寫入自己的 relay log再由 SQL 線程重放 relay log 完成數(shù)據(jù)同步。要特別注意的是主從延遲。從庫(kù)重放是單線程執(zhí)行的8.0 之前是單線程8.0 之后可以在并行復(fù)制下緩解如果主庫(kù)寫并發(fā)很高從庫(kù)可能追不上主庫(kù)。主從延遲導(dǎo)致的典型問題就是剛插入的數(shù)據(jù)立刻從庫(kù)查詢查不到。解決方案包括關(guān)鍵業(yè)務(wù)強(qiáng)制走主庫(kù)。通過半同步復(fù)制減少延遲窗口。延遲敏感度高的場(chǎng)景使用緩存或直接讀主庫(kù)。8.2 讀寫分離在應(yīng)用層怎么落地以 Java 的 Spring 為例一個(gè)簡(jiǎn)單的思路是使用AbstractRoutingDataSource動(dòng)態(tài)數(shù)據(jù)源在事務(wù)開始時(shí)把只讀請(qǐng)求路由到從庫(kù)。public class ReadWriteRoutingDataSource extends AbstractRoutingDataSource { Override protected Object determineCurrentLookupKey() { String key DataSourceContextHolder.getDataSource(); return key; } }public class DataSourceContextHolder { private static final ThreadLocalString HOLDER new ThreadLocal(); public static void setDataSource(String key) { HOLDER.set(key); } public static String getDataSource() { return HOLDER.get(); } public static void clear() { HOLDER.remove(); } }然后在事務(wù)攔截器或 AOP 中根據(jù)方法名或注解判斷走主庫(kù)還是從庫(kù)。這個(gè)方案雖然簡(jiǎn)單但需要注意事務(wù)傳播行為避免一個(gè)讀寫事務(wù)中途切換數(shù)據(jù)源造成同一個(gè)事務(wù)里讀從庫(kù)、寫主庫(kù)的不一致問題。8.3 分庫(kù)分表最后手段而不是首選方案分庫(kù)分表可以解決單庫(kù)單表的數(shù)據(jù)量和寫入吞吐瓶頸但它會(huì)引入分布式事務(wù)、跨庫(kù) JOIN、全局唯一 ID、數(shù)據(jù)遷移等大量復(fù)雜度。所以我的觀點(diǎn)是分庫(kù)分表是最后手段不是首選方案。在做分庫(kù)分表之前建議先按順序評(píng)估以下幾個(gè)方案是否可以通過索引優(yōu)化、SQL 改寫解決性能問題。是否可以通過增加硬件資源、升級(jí)到更高配置解決問題。是否可以通過緩存Redis扛住熱點(diǎn)讀。是否可以通過歸檔歷史數(shù)據(jù)、冷熱分離減小核心表體積。是否可以通過讀寫分離解決讀壓力。最后才是分庫(kù)分表并且優(yōu)先考慮垂直拆分其次才是水平拆分。選擇分片鍵時(shí)要關(guān)注業(yè)務(wù)查詢的主要維度比如訂單表通常按user_id或order_id分片這樣用戶維度的查詢可以在單個(gè)分片內(nèi)完成避免跨庫(kù)查詢。分片鍵一旦定下來后續(xù)調(diào)整成本極高所以方案要足夠謹(jǐn)慎。9. 監(jiān)控、慢查詢分析與性能調(diào)優(yōu)閉環(huán)性能優(yōu)化不是一次性動(dòng)作而是一個(gè)持續(xù)閉環(huán)發(fā)現(xiàn)問題、定位原因、實(shí)施優(yōu)化、驗(yàn)證效果、持續(xù)監(jiān)控。企業(yè)級(jí) MySQL 實(shí)戰(zhàn)必須建立這個(gè)閉環(huán)。9.1 慢查詢?nèi)罩九c mysqldumpslow慢查詢?nèi)罩臼亲罨A(chǔ)的性能觀測(cè)手段。開啟之后它會(huì)記錄執(zhí)行時(shí)間超過long_query_time的 SQL。查看慢查詢?nèi)罩緝?nèi)容可以用mysqldumpslow工具做聚合統(tǒng)計(jì)mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log這條命令會(huì)按照平均執(zhí)行時(shí)間排序顯示最慢的 10 條 SQL。拿到慢 SQL 之后再用EXPLAIN逐步分析是一個(gè)標(biāo)準(zhǔn)動(dòng)作。9.2 常用性能狀態(tài)指標(biāo)除了慢查詢?nèi)罩具€需要關(guān)注 MySQL 的實(shí)時(shí)狀態(tài)變量SHOW GLOBAL STATUS LIKE Threads_running; SHOW GLOBAL STATUS LIKE Threads_connected; SHOW GLOBAL STATUS LIKE Innodb_row_lock_waits;Threads_running過高可能說明短查詢壓力過大或鎖等待嚴(yán)重。Threads_connected過高需要檢查連接池配置是否合理。Innodb_row_lock_waits持續(xù)增長(zhǎng)說明鎖競(jìng)爭(zhēng)明顯。生產(chǎn)環(huán)境建議接入 Prometheus Grafana 或者云廠商的 RDS 監(jiān)控體系把這些指標(biāo)做可視化設(shè)置告警規(guī)則才能在故障發(fā)生前及時(shí)介入。9.3 一條完整的調(diào)優(yōu)閉環(huán)示例假設(shè)線上反饋某個(gè)列表接口變慢。完整的排查鏈路應(yīng)該是打開慢查詢?nèi)罩菊业铰?SQL。用EXPLAIN查看執(zhí)行計(jì)劃確認(rèn)是全表掃描還是索引失效。確認(rèn)索引失效原因比如對(duì)索引列做了函數(shù)運(yùn)算。改寫 SQL將WHERE YEAR(create_time) 2023改寫為范圍查詢。在測(cè)試環(huán)境驗(yàn)證改寫后的執(zhí)行計(jì)劃和響應(yīng)時(shí)間。發(fā)布到生產(chǎn)環(huán)境觀察慢查詢數(shù)量是否下降。如果依然慢再?gòu)谋斫Y(jié)構(gòu)、鎖競(jìng)爭(zhēng)、業(yè)務(wù)邏輯層面繼續(xù)深入。這個(gè)閉環(huán)看起來不復(fù)雜但很多團(tuán)隊(duì)連第一步都做得不完整導(dǎo)致每次性能問題都像“玄學(xué)”。真正的高手不是憑感覺調(diào)優(yōu)而是讓每一步都有可觀測(cè)的數(shù)據(jù)支撐。10. 常見問題與排查思路在實(shí)際工作和學(xué)習(xí)過程中以下問題是出現(xiàn)頻率最高的。我整理成了排查表便于直接對(duì)照使用。問題現(xiàn)象可能原因排查方式解決方案查詢突然變慢數(shù)據(jù)量增長(zhǎng)導(dǎo)致索引效率下降查看執(zhí)行計(jì)劃確認(rèn)是否回表過多建立覆蓋索引優(yōu)化 SQL索引建了但不生效對(duì)索引列使用函數(shù)或隱式類型轉(zhuǎn)換查看執(zhí)行計(jì)劃 key 字段是否為 NULL改寫 SQL避免在索引列上做計(jì)算CPU 飆升慢查詢多或掃描行數(shù)過大開啟慢查詢?nèi)罩痉治?TOP SQL優(yōu)化 SQL增加合理索引死鎖頻繁多事務(wù)加鎖順序不一致SHOW ENGINE INNODB STATUS查看最近死鎖統(tǒng)一加鎖順序縮短事務(wù)時(shí)間主從延遲大主庫(kù)寫入壓力大或從庫(kù)并行復(fù)制不足查看Seconds_Behind_Master優(yōu)化寫入 SQL升級(jí)并行復(fù)制連接數(shù)打滿連接池配置過大或存在慢請(qǐng)求占連接查看Threads_connected調(diào)整連接池縮短事務(wù)執(zhí)行時(shí)間磁盤 IO 高頻繁回表、全表掃描觀察 IO 指標(biāo)分析 SQL使用覆蓋索引優(yōu)化查詢深分頁(yè)很慢LIMIT offset偏移量過大查看rows字段改用延遲關(guān)聯(lián)或游標(biāo)分頁(yè)11. 最佳實(shí)踐與工程建議最后把我在學(xué)習(xí)和實(shí)踐過程中比較認(rèn)可的 MySQL 企業(yè)級(jí)應(yīng)用原則做一個(gè)匯總。這些原則不一定每條都適用于所有項(xiàng)目但它們可以作為你設(shè)計(jì)和優(yōu)化時(shí)的檢查清單。第一條SQL 規(guī)范要前置。團(tuán)隊(duì)里應(yīng)該有統(tǒng)一的 SQL 編寫規(guī)范包括禁止SELECT *、禁止無 WHERE 條件的 UPDATE 和 DELETE、禁止在索引列上做函數(shù)運(yùn)算。通過 Code Review 和 SQL 審查工具在代碼進(jìn)生產(chǎn)之前就把問題攔下來。第二條索引寧缺毋濫。索引是給查詢用的不是給心靈安全感用的。每多一個(gè)索引寫入就多一份開銷。索引的新增應(yīng)該由真實(shí)業(yè)務(wù)查詢驅(qū)動(dòng)而不是預(yù)先堆砌。刪除索引也要謹(jǐn)慎最好有監(jiān)控?cái)?shù)據(jù)支持。第三條事務(wù)要短鎖范圍要小。事務(wù)里不放遠(yuǎn)程調(diào)用不放慢查詢不循環(huán)逐條操作。能批量更新就批量更新能縮小鎖范圍就縮小鎖范圍。第四條先看執(zhí)行計(jì)劃再做優(yōu)化。任何 SQL 優(yōu)化都不應(yīng)該脫離EXPLAIN。很多問題在編寫 SQL 的時(shí)候就能通過執(zhí)行計(jì)劃提前發(fā)現(xiàn)避免上線后才排查。第五條線上變更要有回滾方案。無論是加索引、改 SQL 還是調(diào)整事務(wù)邏輯都要做測(cè)試驗(yàn)證并保證可以在線上快速回滾。比如一個(gè) SQL 改寫方案除了驗(yàn)證執(zhí)行計(jì)劃和響應(yīng)時(shí)間還要關(guān)注它對(duì)業(yè)務(wù)結(jié)果是否有影響。第六條監(jiān)控要比故障先到。如果你等到用戶反饋才發(fā)現(xiàn)數(shù)據(jù)庫(kù)變慢說明監(jiān)控體系是缺失的。慢查詢?nèi)罩?、CPU、連接數(shù)、鎖等待、主從延遲這些核心指標(biāo)都應(yīng)該在系統(tǒng)上線第一天就接入監(jiān)控。第七條數(shù)據(jù)歸檔要制度化。業(yè)務(wù)表的數(shù)據(jù)不是永遠(yuǎn)都在增長(zhǎng)很多歷史數(shù)據(jù)可以通過歸檔表、冷熱分離等方式移出核心業(yè)務(wù)庫(kù)。這能有效控制大表體積減少查詢和備份壓力。12. 總結(jié)與后續(xù)學(xué)習(xí)方向高性能 MySQL 實(shí)戰(zhàn)這件事本質(zhì)上不是學(xué)會(huì)幾個(gè)命令、記住幾條優(yōu)化技巧而是建立一套完整的“設(shè)計(jì)-開發(fā)-運(yùn)維”鏈路認(rèn)知。從 Schema 設(shè)計(jì)階段決定字段類型和主鍵策略到 SQL 編寫階段關(guān)注索引命中和執(zhí)行計(jì)劃再到事務(wù)設(shè)計(jì)階段控制鎖粒度和事務(wù)長(zhǎng)度最后到架構(gòu)層面決定讀寫分離和分庫(kù)分表每一個(gè)環(huán)節(jié)都相互關(guān)聯(lián)。任何一個(gè)環(huán)節(jié)的短板都會(huì)在數(shù)據(jù)量和并發(fā)上升到一定程度后暴露出來。對(duì)于剛接觸 MySQL 性能優(yōu)化的讀者我的建議是先做三件事第一把EXPLAIN用熟拿到任何一條慢 SQL 都能看懂執(zhí)行計(jì)劃第二把索引失效的幾種場(chǎng)景牢記寫 SQL 時(shí)主動(dòng)規(guī)避第三在自己的本地或測(cè)試環(huán)境搭一套帶監(jiān)控的最小系統(tǒng)跑通“慢查詢發(fā)現(xiàn)-SQL 改寫-驗(yàn)證效果”的完整閉環(huán)。這三件事做完你已經(jīng)超過大多數(shù)只會(huì)寫 CRUD 的同學(xué)。后續(xù)可以繼續(xù)深入的方向包括InnoDB 底層原理與 redo log、binlog 的協(xié)作機(jī)制MySQL 8.0 的成本優(yōu)化模型分區(qū)表的設(shè)計(jì)與限制分布式事務(wù)方案如 Seata以及云數(shù)據(jù)庫(kù)和自建數(shù)據(jù)庫(kù)在治理模式上的差異。MySQL 這個(gè)領(lǐng)域看起來入門門檻低但真正走到深處會(huì)發(fā)現(xiàn)它連接著操作系統(tǒng)、存儲(chǔ)、網(wǎng)絡(luò)、分布式系統(tǒng)幾乎所有后端基礎(chǔ)知識(shí)。深入進(jìn)去瓶頸越少解決問題的確定性就越高。