MySQL 調(diào)優(yōu)的實(shí)戰(zhàn)思路
我是怎么做 MySQL 調(diào)優(yōu)的(實(shí)戰(zhàn)思路)
這不是“某幾個(gè)參數(shù)調(diào)一調(diào)”的問題,而是一整套從 架構(gòu) → SQL → 索引 → 配置 → 系統(tǒng) 的排查流程。
你可以把這篇當(dāng)成自己的 MySQL 調(diào)優(yōu) Checklist。
一、調(diào)優(yōu)前的共識(shí):先度量,再優(yōu)化
調(diào)優(yōu)最怕兩件事:
- 拍腦袋調(diào)參數(shù):改了一堆配置,QPS 沒上去,反而更慢;
- 只盯一個(gè)點(diǎn):只會(huì)改
innodb_buffer_pool_size,其他全靠緣分。
所以第一步永遠(yuǎn)是:
- 確認(rèn)問題是什么
- 慢?慢在哪?是“偶爾很慢”還是“永遠(yuǎn)都不快”?
- CPU 打滿?IO 打滿?連接數(shù)飆高?鎖等待多?
- 收集基礎(chǔ)信息
- 版本:
SELECT VERSION(); - 業(yè)務(wù)類型:讀多寫少?寫多讀少?混合?
- 數(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 性能問題,都可以歸結(jié)為:SQL 寫得不行 + 索引沒設(shè)計(jì)好。
2.1 開啟慢查詢?nèi)罩?/h4>
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1; -- 超過 1 秒的算慢查詢
SET GLOBAL log_queries_not_using_indexes = 1; -- 也可以先開一陣
SET GLOBAL slow_query_log = 1; SET GLOBAL long_query_time = 1; -- 超過 1 秒的算慢查詢 SET GLOBAL log_queries_not_using_indexes = 1; -- 也可以先開一陣
然后看:
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:訪問類型(ALL、index、range、ref、const等);ALL= 全表掃描,基本就是優(yōu)化對(duì)象;
key:實(shí)際使用的索引;rows:預(yù)估掃描行數(shù);Extra:Using filesort、Using temporary、Using index等。
2.3 改寫 SQL + 補(bǔ)索引
- 常見優(yōu)化方式:
- 為 高頻 WHERE 條件 / JOIN 字段 / ORDER BY 字段 建索引;
- 把“函數(shù)/計(jì)算寫在左邊”的寫法改掉:
-- ? 錯(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ì)思路
- 區(qū)分“高頻查詢條件”和“偶爾查查”;
- 給經(jīng)常出現(xiàn)在
WHERE / JOIN / ORDER BY / GROUP BY的列設(shè)計(jì)索引; - 盡量用 聯(lián)合索引代替多個(gè)單列索引;
- 注意索引列的選擇性(區(qū)分度):
- 性別、是否刪除(0/1)這類字段不要單獨(dú)建索引;
- 可以放在聯(lián)合索引里。
3.2 合理選擇主鍵與存儲(chǔ)引擎
InnoDB:
- 強(qiáng)烈建議:自增整型主鍵 + InnoDB;
- 避免使用 UUID/業(yè)務(wù)字符串作為聚簇索引主鍵;
- 避免過寬的主鍵,所有二級(jí)索引都要跟著變胖。
表結(jié)構(gòu)方面:
- 避免大字段(
TEXT/BLOB)頻繁參與查詢,可拆分表; - 合理拆分“冷熱字段”,避免每次查詢都掃一大坨無關(guān)數(shù)據(jù)。
四、第三刀:InnoDB 關(guān)鍵參數(shù)調(diào)優(yōu)
在 SQL & 索引搞定后,如果數(shù)據(jù)庫仍吃緊,可以看配置。
4.1 innodb_buffer_pool_size(最關(guān)鍵的內(nèi)存參數(shù))
- 作用:InnoDB 用來緩存數(shù)據(jù)頁 + 索引頁;
- 一般建議:物理內(nèi)存的 50%~70% 左右(線下壓測(cè)決定);
- 過小:大量讀盤,性能抖;
- 過大:擠壓 OS 緩存/其他進(jìn)程,可能換頁。
innodb_buffer_pool_size = 8G # 視機(jī)器內(nèi)存而定
4.2 innodb_log_file_size / log_buffer_size
- redo log 的大小和值:
- 太小:頻繁 flush,增加 IO;
- 太大:崩潰恢復(fù)時(shí)間變長(zhǎng)。
- 典型值可從幾百 MB 到幾 GB,需結(jié)合寫入量、硬件調(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:每次提交寫 OS 緩存,每秒刷盤一次;0:每秒寫一次 + 刷盤。
業(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)過;
此時(shí)數(shù)據(jù)庫仍然吃緊,就要看系統(tǒng)層面。
5.1 磁盤 IO
- 用
iostat、vmstat、iotop看:- 磁盤隊(duì)列長(zhǎng)度、IO 等待;
- 如果 IO 明顯成為瓶頸:
- 換 SSD;
- RAID 級(jí)別調(diào)整;
- 分庫分表、冷熱分離。
5.2 CPU
- 大量復(fù)雜 SQL、函數(shù)計(jì)算、排序、JOIN 會(huì)吃 CPU;
- 通過慢查詢 + EXPLAIN 優(yōu)化 SQL,減少 CPU 壓力;
- 必要時(shí)升級(jí)機(jī)器配置或做讀寫分離、分庫。
5.3 網(wǎng)絡(luò)
- 主從復(fù)制延遲大、跨機(jī)房訪問慢;
- 盡量讓數(shù)據(jù)庫靠近應(yīng)用部署(同一可用區(qū)/機(jī)房)。
六、第五刀:架構(gòu)級(jí)優(yōu)化(分庫分表 & 讀寫分離)
當(dāng)單實(shí)例再怎么調(diào)也扛不住時(shí),就是架構(gòu)層面問題了。
6.1 讀寫分離
典型做法:
- 主庫負(fù)責(zé)寫、從庫負(fù)責(zé)讀;
- 中間加一個(gè)數(shù)據(jù)訪問層(或中間件)做路由;
- 部分請(qǐng)求強(qiáng)制走主庫(寫后立刻讀)。
適合場(chǎng)景:
- 讀遠(yuǎn)多于寫;
- 允許短暫讀到舊數(shù)據(jù)的場(chǎng)景。
6.2 分庫分表
如果單表行數(shù)上億,索引高度、統(tǒng)計(jì)信息、緩存命中都會(huì)出問題。
常見拆分維度:
- 按業(yè)務(wù)維度拆庫(用戶庫、訂單庫、日志庫);
- 按 hash/范圍拆分大表:
- 用戶 ID 取模;
- 按時(shí)間分表(按月/按日)。
拆分后要注意:
- 跨分片 JOIN 不再簡(jiǎn)單支持,需要應(yīng)用層處理;
- 全局唯一主鍵(雪花算法、號(hào)段發(fā)號(hào)等);
- 分布式事務(wù)(能避則避,多用最終一致方案)。
七、監(jiān)控與排查工具清單
調(diào)優(yōu)離不開“觀察工具”:
- 慢查詢?nèi)罩?/strong>:慢 SQL 排查入口;
EXPLAIN/EXPLAIN ANALYZE:看執(zhí)行計(jì)劃、實(shí)際耗時(shí);SHOW PROCESSLIST:看當(dāng)前連接、是否有鎖等待;SHOW ENGINE INNODB STATUS\G:看死鎖、事務(wù)等待;information_schema/performance_schema/sys庫:- 統(tǒng)計(jì)哪些 SQL 最耗費(fèi)資源。
- 操作系統(tǒng)工具:
top、iostat、vmstat、sar等。
八、一套實(shí)戰(zhàn)調(diào)優(yōu)流程(總結(jié)版)
給你一套可直接復(fù)用的步驟:
- 確認(rèn)問題類型
- 慢:是個(gè)別 SQL 慢,還是所有操作都慢?
- 卡:CPU 高?IO 高?鎖等待多?
- 開慢日志 + 抓 Top SQL
- 找出最慢/最頻繁/總時(shí)長(zhǎng)最高的 SQL;
- 優(yōu)先優(yōu)化前 10% 的“重災(zāi)區(qū)”。
- 抓執(zhí)行計(jì)劃(EXPLAIN)
- 看是否使用索引;
type是否為range/ref/const而不是ALL;- 是否大量
Using filesort、Using temporary。
- 設(shè)計(jì)/調(diào)整索引 & 改寫 SQL
- 減少全表掃描、回表、排序臨時(shí)表;
- 利用覆蓋索引和聯(lián)合索引。
- 調(diào)整 InnoDB 核心參數(shù)
innodb_buffer_pool_size、innodb_log_file_size、innodb_flush_log_at_trx_commit等;- 壓測(cè)不同參數(shù)組合的效果。
- 觀察硬件資源
- 磁盤 IO 是否打滿;
- CPU 是否經(jīng)常 100%;
- 內(nèi)存是否頻繁換頁。
- 必要時(shí)考慮架構(gòu)改造
- 加從庫做讀寫分離;
- 對(duì)熱點(diǎn)庫/表做拆分。
九、小結(jié)
MySQL 調(diào)優(yōu)本質(zhì)上是一個(gè)“找瓶頸 → 定位層級(jí) → 有針對(duì)性優(yōu)化”的過程:
- SQL 與索引永遠(yuǎn)是第一優(yōu)先級(jí);
- InnoDB 參數(shù)是在“SQL/索引合理之后”的加分項(xiàng);
- 再往上,是硬件和架構(gòu)層面的擴(kuò)展。
一句話:
不要指望靠改幾個(gè)參數(shù)就“神奇提速”,調(diào)優(yōu)一定是數(shù)據(jù)驅(qū)動(dòng) + 全鏈路思考。
養(yǎng)成“看慢日志 + EXPLAIN + 監(jiān)控”的習(xí)慣,你的 MySQL 水平會(huì)非??斓厣蟻怼?/p>
到此這篇關(guān)于MySQL 調(diào)優(yōu)的實(shí)戰(zhàn)思路的文章就介紹到這了,更多相關(guān)mysql調(diào)優(yōu)思路內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
- Mysql調(diào)優(yōu)Explain工具詳解及實(shí)戰(zhàn)演練(推薦)
- 深入MySQL調(diào)優(yōu)原則
- MySQL中SQL查詢常見調(diào)優(yōu)方案對(duì)比與實(shí)踐
- MySQL優(yōu)化之SQL調(diào)優(yōu)策略分享
- MySQL中如何進(jìn)行SQL調(diào)優(yōu)舉例詳解
- MYSQL數(shù)據(jù)庫連接池及常見參數(shù)調(diào)優(yōu)方式
- mysql調(diào)優(yōu)的幾種方式小結(jié)
- Mysql性能調(diào)優(yōu)之max_allowed_packet使用及說明
- 10個(gè)MySQL性能調(diào)優(yōu)的方法
相關(guān)文章
利用JuiceFS使MySQL?備份驗(yàn)證性能提升?10?倍
這篇文章主要介紹了如何讓?MySQL?備份驗(yàn)證性能提升?10?倍,JuiceFS?非常適合用來做?MySQL?物理備份,通過不斷調(diào)整?XtraBackup?的參數(shù)和?JuiceFS?的掛載參數(shù),在一個(gè)小時(shí)內(nèi)將時(shí)間縮短到原先的?1/10,下文一起來看相關(guān)內(nèi)容的詳細(xì)介紹吧2022-03-03
Mysql數(shù)據(jù)庫手動(dòng)及定時(shí)備份步驟
最近剛好用到了數(shù)據(jù)庫備份,想著還有個(gè)別實(shí)習(xí)或者剛工作的小伙伴一個(gè)drop不小心刪表、刪庫,心內(nèi)慌得一批不知道該怎么辦,就打算跑路了,學(xué)會(huì)這個(gè)小技巧就不用跑路了2021-11-11
MySQL InnoDB ReplicaSet(副本集)簡(jiǎn)單介紹
這篇文章主要介紹了MySQL InnoDB ReplicaSet(副本集)的相關(guān)資料,幫助大家更好的理解和學(xué)習(xí)使用MySQL,感興趣的朋友可以了解下2021-04-04
mysql 字符串長(zhǎng)度計(jì)算實(shí)現(xiàn)代碼(gb2312+utf8)
PHP對(duì)中文字符串的處理一直困擾于剛剛接觸PHP開發(fā)的新手程序員。下面簡(jiǎn)要的剖析一下PHP對(duì)中文字符串長(zhǎng)度的處2011-12-12
簡(jiǎn)單解決Windows中MySQL的中文亂碼與服務(wù)啟動(dòng)問題
這篇文章主要介紹了Windows中MySQL的中文亂碼與服務(wù)啟動(dòng)問題,如果程序沒有特殊需要?jiǎng)t建議MySQL盡量默認(rèn)設(shè)為UTF-8格式編碼,需要的朋友可以參考下2016-03-03
mysql中engine=innodb和engine=myisam的區(qū)別介紹
MyISAM類型不支持事務(wù)處理等高級(jí)處理,而InnoDB類型支持,本文為大家講解下mysql中engine=innodb和engine=myisam的區(qū)別,不懂的朋友可以學(xué)習(xí)下,希望對(duì)大家有所幫助2013-07-07
MySQL數(shù)據(jù)庫子查詢語法規(guī)則詳解
子查詢是在查詢語句里面再嵌套一個(gè)查詢,這是因?yàn)槲覀冊(cè)谔崛?shù)據(jù)的時(shí)候有很多不知道的數(shù)據(jù)產(chǎn)生了依賴關(guān)系。本文為大家總結(jié)了一下MySQL數(shù)據(jù)庫子查詢語法規(guī)則,感興趣的可以了解一下2022-08-08

