每次接手一個“MySQL慢得像蝸牛”的排查任務我第一反應不是去看服務器配置也不是去罵產品經理又亂寫需求而是先打開慢查詢日志和EXPLAIN。干這行十年經手的MySQL優化案例沒有一千也有八百不管是單表幾十萬數據的初創項目還是分庫分表后單表仍上億的成熟業務系統最終的性能瓶頸往往都收束到三個方向索引是否設計合理、SQL是否寫得到位、數據量大到一定程度后架構是否扛得住。這篇文章不會跟你聊那些“高大上”的底層原理名詞堆砌就實打實地把索引、SQL調優、分庫分表這三塊硬骨頭拆開揉碎講清楚每個方案背后的取舍邏輯和我在項目里驗證過的真實做法。這篇攻略適合誰看如果你剛把業務代碼寫完發現線上數據庫CPU經常飆到100%或者查詢接口隨著數據量增長從200ms一路滑到2秒開外又或者你正在為“要不要分庫分表”這個問題糾結到失眠——那這篇文章就是寫給你的。我盡量把每個決策點都還原成“當時我遇到的是什么場景、為什么選這個方案、踩了哪些坑”而不是給你一堆PPT式的理論框架。1. 先定位慢在哪90%的MySQL性能問題都出在同一個地方我見過太多團隊一上來就討論要不要換PostgreSQL、要不要上分布式數據庫結果我上去一查單表才三千萬數據連接數才一兩百根本還沒到換數據庫的地步純粹是幾條核心SQL沒寫好索引走了全表掃描。定位慢查詢這件事是后續所有優化的地基地基不穩后面所有動作都是瞎忙活。1.1 慢查詢日志與EXPLAIN的正確打開方式排查的第一步永遠是打開慢查詢日志。很多開發者說“我開了啊”結果一查long_query_time設置的是默認的10秒等于沒開。這個閾值我一般建議從0.5秒開始設先抓出當前消耗最高的SQL——晚上用pt-query-digest這類工具一匯總top 10的慢SQL基本就是你要優化的大頭。拿到慢SQL之后先別急著看業務邏輯直接EXPLAIN看執行計劃。這里有個新手特別容易忽略的點EXPLAIN看的是預估執行計劃它告訴你的是優化器“打算怎么干”不是“實際什么表現”。真正要看你得開EXPLAIN ANALYZEMySQL 8.0.18支持它能給出實際執行時間和掃描行數做完索引改動之后也必須用這個命令確認優化效果而不是只看type是不是從ALL變成了ref就以為萬事大吉。看執行計劃時我有一套自己的快速判斷順序type字段從好到差依次是 system const eq_ref ref range index ALL。只要看到ALL基本可以確定這條SQL要全表掃除非表就幾百行否則一定要優化。key字段看看實際用了哪個索引。經常出現的坑是possible_keys里有候選索引但key是NULL——就是優化器壓根沒選你的索引。rows字段預估掃描行數。這個數值和實際行數相差太大通常意味著統計信息過期或者WHERE條件的寫法讓優化器無法做準確估算。Extra字段看到Using filesort和Using temporary這是兩個危險信號一個說明排序沒有走索引一個說明臨時表被用到了后面第六節詳細講。1.2 從執行計劃到根因判斷一張排查對照表為了讓你排查起來更有方向感我把日常最常遇到的執行計劃特點和對應根因整理成一張表。這個話單是多年實戰積累下來的濃縮版排查時直接對照著看就行執行計劃特征可能根因優先處理建議typeALL, rows 很大沒建索引或索引失效檢查WHERE和JOIN條件字段建立合適的單列或聯合索引keyNULL, possible_keys 有值優化器放棄索引可能是數據分布問題或函數運算導致重寫SQL去掉函數包裹如果實在無法避免考慮強制索引Using filesort排序字段和WHERE條件不在同一個聯合索引里調整聯合索引字段順序讓排序走索引而不是再開一道排序Using temporaryGROUP BY 或 DISTINCT 觸發了臨時表重寫查詢或者調整索引讓分組字段有序rows 誤差超過10倍統計信息嚴重過期執行 ANALYZE TABLE 刷新統計信息索引選擇性極低重復率過高建了索引但區分度不夠優化器不想用換區分度更高的字段或者用覆蓋索引繞開回表還有一種坑比較隱蔽——索引選擇性誤判。比如性別字段你建了索引結果優化器發現全表一半是男一半是女它覺得用這個索引還不如直接掃全表劃算于是直接放棄索引。遇到這種情況別硬剛優化器要么用覆蓋索引帶上查詢需要的其他列讓掃描成本降低要么直接改業務查詢邏輯去掉這個條件。2. 索引為什么失效回表、最左前綴與函數運算的連鎖反應索引優化是MySQL性能優化的第一顆子彈。但很多人建完索引就以為完事了結果上線后該慢還是慢。索引失效的那幾類典型場景我閉著眼都能背出來——不是因為我記憶力好而是每個坑我都親自掉進去過又親自爬出來。2.1 函數包裹、隱式轉換和區分度問題一個聯合索引的設計案例先看一個我最近處理的真實案例。一張訂單表查詢條件是WHERE pay_time BETWEEN ... AND ...我建議開發在上面建索引他說建了但你猜怎么著他是這么寫的WHERE DATE(pay_time) BETWEEN 2024-01-01 AND 2024-01-31。索引是建在pay_time上的但DATE()函數一包索引直接失效——因為MySQL要先算出DATE(pay_time)的結果才能去和區間比較這等于把全部行先計算一遍那建索引還有什么意義正確的寫法應該是WHERE pay_time 2024-01-01 AND pay_time 2024-02-01既覆蓋了整個一月份的區間又沒做任何函數運算索引能完美走range掃描。這條規則幾乎適用于所有日期范圍查詢也是我審查SQL時一眼就能掃出來的問題。同樣的道理字段類型不匹配的隱式轉換也是索引殺手。比如user_id字段是varchar(32)但你在代碼里傳入的是整數類型的IDMySQL會自動把字符串轉成數字再比對。這本來不算什么大問題壞就壞在當你拿一個數字去和varchar字段比較的時候MySQL會先對所有行的user_id做一次類型轉換于是索引全部失效。這類問題很難排查因為它不報錯執行計劃也不崩但rows行數就是下不來。真正設計聯合索引的時候我養成了三個習慣分享出來供你參考把等值條件放最前面然后才是范圍條件。比如查詢條件是WHERE status1 AND create_time 2024-01-01聯合索引就應該設計為(status, create_time)。因為等值條件可以精確定位范圍條件只需要在等值定位后的范圍里掃一小段。能用覆蓋索引解決的盡量用覆蓋索引。比如查詢只需要id和status建一個(status, id)的索引MySQL就能直接從索引里拿數據連回表都省了這是最快的一種查詢路徑。控制索引數量。這塊我必須多說兩句。我見過一個項目為了一張表建了12個索引寫入性能慘不忍睹。索引不是越多越好每多一個索引INSERT和UPDATE就要多維護一個B樹。通常單表索引控制在5個以內能復用聯合索引的左前綴就絕不重復建索引。2.2 復合索引的最左前綴原則一個字段順序引發的性能災難復合索引聯合索引最核心的規則就是最左前綴原則——MySQL 8.0之前優化器只能從左往右匹配索引列你不能跳過第一列直接用到第二列。這個我建議你牢記在腦子里因為實際開發中被它坑過的案例我至少親手處理過十次。具體來說假設你建了復合索引(a, b, c)WHERE a1能走索引WHERE a1 AND b2能走索引WHERE a1 AND c3只能用到索引的a列c列用不上WHERE b2完全不走這個索引最經典的反例是業務需求經常按name和create_time查開發就建了(name, create_time)的索引。但后來新增了一個需求只按create_time范圍查詢發現這條新SQL慢得離譜查看執行計劃居然走了全表掃描。原因就是這個復合索引根本幫不上忙——因為create_time不是第一列只按它查的時候索引直接報廢。這時候正確的做法有三種。第一種是再建一個(create_time)的單列索引第二種是如果你頻繁需要這個組合直接正面剛改查詢讓name條件不缺席比如業務上允許默認帶一個空串或通配但要注意通配符走不了索引這里不做推薦第三種最優雅——把復合索引設計成(create_time, name)既支持按時間查又支持按時間姓名查。我強烈推薦第三種因為create_time通常是范圍條件放在左邊之后右邊接等值條件大多數情況下都能滿足新老需求。索引失效的場景還有很多比如WHERE name LIKE %張這種前置通配符會導致索引失效這個好理解B樹是按前綴有序的你硬要在中間挖一段它沒法定位還有OR連接的非索引列場景以及SQL里出現!或IS NOT NULL的情況。但歸根結底你要理解B樹的排序存儲特性就能自己推導出索引什么時候能用只要是破壞了有序性或者需要全量計算的場景索引大概率就廢了。3. SQL層面最容易忽略的性能殺手分頁、JOIN與排序的隱形代價索引設計得再合理SQL寫得不靠譜照樣卡死。這一節我從最常見的三個業務場景入手分頁、多表關聯和排序分組把隱藏在業務代碼背后的性能殺手挨個揪出來。3.1 分頁翻得越深越慢limit深翻頁問題的三種解法做后臺管理系統的同學應該有切身體會——導數據列表的時候越往后面翻頁越慢。比如運營人員在列表頁一頁頁翻到第200頁每頁50條這頁只顯示第9951到10000條但MySQL干的事是把從101到10000行的所有行先掃出來然后全部丟掉只把最后50條返回給你。數據量小的時候感覺不出來數據量上到9000萬這種寫法的延遲你根本沒法接受。排查的時候往往發現開發同學的SQL是這種形態SELECT * FROM orders ORDER BY create_time DESC LIMIT 10000, 20;這條基本就是深翻頁臭名昭著的寫法。優化方案有三種按優先級推薦方案一延遲關聯所有版本通用。先只查id覆蓋索引能快速定位再用這些id去關聯查詢完整行SELECT o.* FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY create_time DESC LIMIT 10000, 20) t ON o.id t.id;方案二基于游標分頁推薦用在Web端。不是翻頁跳轉而是通過上一頁最后一條記錄的ID來進行下一頁定位SELECT * FROM orders WHERE create_time 2024-01-15 10:00:00 ORDER BY create_time DESC LIMIT 20;用where條件過濾掉上一頁已知的最后一條走索引只在數據里順序取20條不丟數據不重排。缺點是用戶不能直接跳到任意頁只能在相鄰頁之間點擊“加載更多”這種交互形式移動端和多數信息流都這么玩因為它壓測下來性能最穩。方案三如果產品必須跳頁提前算好ID集合一次性查詢。用前端的countoffset換算成已知的主鍵集合然后WHERE id IN (...) LIMIT 20。根據我的實測經驗方案二在沒有深翻頁訴求時性能最佳方案一在必須保留跳頁時性價比最高。極限情況下同樣的深翻頁SQL從800ms優化到80ms以內的都是常規操作。3.2 JOIN不是不能用但ON條件沒索引等于災難再來說JOIN。很多規范文檔都會告訴你“盡量少用JOIN”這句話讓不少新手走入了另一個極端——把原本一條JOIN拆成三五條SQL在代碼里挨個查結果網絡來回比以前更慢。我的觀點是JOIN本身不是洪水猛獸關鍵在JOIN字段是否走索引以及小表驅動大表的原則是否被遵守。MySQL的JOIN執行邏輯是這樣的外層驅動表每一行都需要去匹配內層表。如果內層表的連接字段沒有索引那就是一次全表掃描乘以驅動表的行數——這個代價是幾何級增長的。以一個業務系統為例用戶表幾十萬條訂單表上千萬條要查某時間段內有訂單的用戶信息。如果JOIN條件是u.id o.user_id但訂單表的user_id沒建索引每次匹配都要去掃訂單表的一千多萬行這條查詢基本就是災難現場。建上user_id索引一切迎刃而解。所以我審查SQL的第一原則不是“見了JOIN就皺眉”而是看EXPLAIN里的type只要被驅動表的連接字段是ref或eq_ref級別JOIN完全可以放心用。另一個重點是小表驅動大表。MySQL的優化器一般會自動選擇小表做驅動表但如果你用什么子查詢包裹的方式去打亂它的統計幻覺優化器也有可能做出錯誤選擇。這時候STRAIGHT_JOIN可以直接指定驅動順序把大表放到被驅動位置。這個技巧不常用但當你在EXPLAIN里看到驅動表選錯的時候它比硬改SQL簡單得多。3.3 filesort和臨時表ORDER BY與GROUP BY為什么會拖垮查詢ORDER BY最怕的不是排序本身而是排序字段沒法走索引迫使MySQL把所有結果集撈出來再額外執行一次內存排序或者磁盤排序。EXPLAIN里的Using filesort就是它。在這個地方聯合索引的字段順序又體現出關鍵作用。比如聯合索引是(status, create_time)當查詢條件是WHERE status1 ORDER BY create_time DESCMySQL可以直接按索引順序來讀不需要額外排序。但如果你把ORDER BY create_time中的create_time放在索引非末尾位置或者排序條件和查詢條件的順序不匹配filesort就出現了。同樣GROUP BY 之所以效率低是因為它通常伴隨著臨時表的創建MySQL 8.0之前還會專門建個內部臨時表來存分組中間結果量大還可能落到磁盤。遇到大表上的GROUP BY我的優化順序是先看能否通過聯合索引設計讓分組字段有序這樣MySQL就可以直接順序掃索引然后順便分組不需要建臨時表。如果分組字段的基數很小比如就幾個枚舉狀態改用COUNT(DISTINCT ...)配合業務側的Map匯總有時反而更利落。非要在SQL里GROUP BY把返回字段做得盡量窄只取需要分組的字段和聚合函數字段減少臨時表的內存壓力。4. 寫入性能與并發控制的隱形瓶頸不只是慢查詢才需要關注慢查詢折磨人寫入抖動同樣是事故隱患。很多后臺系統的MySQL優化文章通篇講SELECT但一到每天零點定時任務跑批、或者業務高峰導入數據的場景INSERT和UPDATE的性能問題能把線上打掛。這一節聊聊我在處理寫入瓶頸和并發控制時常用的兩招批量寫的正確姿勢和鎖競爭的有效規避。4.1 批量提交與事務大小的黃金分割點剛工作那會兒我寫過一個批量導入腳本循環一萬條數據一條一條INSERT跑了一個多小時還沒跑完。后來改成一次事務批量INSERT一千條十分鐘跑完。差異就在于INSERT本身一條條執行時每次都有網絡往返、事務提交刷盤的開銷而批量提交可以把這些固定開銷攤薄。但也不是批量越大越好。我有過一次性INSERT十萬條的“翻車”經歷——事務過大導致undo日志膨脹MVCC機制要保留舊版本數據binlog同步延遲飆升主庫事務把從庫備庫拖得追不上差點引發主從切換事故。后來總結出的經驗是單個事務控制在1000~5000條范圍或者事務執行時間控制在1秒以內。這個數字不是拍腦袋定的而是權衡了網絡往返、鎖持有時長、binlog體積和回滾成本之后的可接受區間你可以根據自己業務的寫入量做微調。預處理語句綁定參數值得強烈推薦INSERT INTO orders (order_id, user_id, amount, status) VALUES (?, ?, ?, ?)使用prepared statement批量綁定參數能避免每條INSERT都做一次SQL解析。在大批量導數據場景這個優化效果非常明顯一千萬行的導數任務從小時級縮到十幾分鐘。4.2 行鎖、間隙鎖與死鎖為什么長事務比慢SQL更可怕慢SQL至少還能在慢查詢日志里顯式看到長事務才是真正的隱形殺手。事務不提交它持有的鎖就一直不釋放后續所有要更新同一條記錄、甚至插入相鄰記錄的事務都會排隊堵住。更隱蔽的是MVCC下的undo日志膨脹長事務意味著大量舊版本數據不能被purge線程清理這會導致表空間無限膨脹查詢還要在版本鏈上往前找正確的版本性能越來越差。這類長事務的來源十有八九是業務代碼在同一個事務里調用了外部接口。例如一個“創建訂單并扣庫存”的接口你在Spring的Transactional事務里調用支付回調接口等外部響應等了3秒。這三秒里整個訂單表相關范圍的鎖都捏在你手里所有同類請求全部排隊阻塞線上就炸鍋了。死鎖同樣值得反復排查。我處理過最典型的死鎖場景是兩個事務都執行“先更新主表再更新明細表”但順序恰好反過來兩邊互相等鎖MySQL檢測到死鎖后就隨機選一個事務回滾。這種死鎖在高并發寫入下特別常見。規避方法不復雜所有事務按照固定順序訪問資源比如先主表后明細表或者把相關行一次性用SELECT ... FOR UPDATE鎖住。經驗之談線上環境一定要監控長事務和鎖等待事件。information_schema.innodb_trx這張表可以直接查出來哪些事務超過3秒還沒提交配套sys.innodb_lock_waits能定位鎖等待的根因。趁事務還沒膨脹到釀成大禍之前把它殺在萌芽階段是DBA和資深后端必須建立的夜間值班基本功。5. 分庫分表的前提判斷數據量大不等于必須要分講到分庫分表我先潑一盆冷水分庫分表是最后的手段不是第一選擇。很多人看數據量到了幾千萬就喊著要分庫分表結果分完以后跨庫JOIN、分布式事務、分布式ID、擴容數據遷移樣樣都是新坑問題沒解決反而更多了。我的經驗是在動手分庫分表之前你先把下面這些事做扎實了再說。5.1 什么數據量級才需要分庫分表三大前置判斷標準判斷要不要分庫分表不是一個數字閾值就拍板的我通常會看三個維度同時亮紅燈才啟動方案單表數據量超過MySQL的舒適區。InnoDB的B樹在單表數據量一億以內理論上都能扛住但實際運維中發現當表超過2000萬行且索引深度超過3層時查詢延遲開始明顯可見地退化。當然這和數據寬度、磁盤IO性能都有關系不是絕對判斷標準。單庫的并發寫入能力成為瓶頸。比如數據庫連接數打滿每秒寫入行數受制于磁盤IOPS即使拆索引、優化SQL也提不上去。數據增長趨勢是持續且陡峭的。如果只是短期活動帶來的一波數據量活動結束就平穩了那沒必要為了峰值去做永久性的分庫分表改造。我見過一個日志表三個月時間從2億漲到15億這種就屬于必須分另一種是用戶表全年就漲個2000萬說實話單表再撐幾年問題不大。如果只占了第一點單表數據量大但讀多寫少優先考慮冷熱分離歸檔。比如訂單表把一年前的訂單遷移到一個獨立的歸檔庫或者只讀歷史表主表數據量直接砍掉70%查詢性能秒回。占有便宜又見效快完全不需要分庫分表。如果第二、第三點同時出現比如訂單系統日訂單量突破500萬單、流水表半年就過10億行那才是分庫分表的啟動時機可以進入下一節。5.2 分片鍵選錯是最大的災難訂單表案例的教訓分庫分表的第一步不是選中間件而是選分片鍵。這里有一條血的教訓分片鍵必須滿足80%以上的核心查詢能帶著它走否則就是給自己埋雷。以電商訂單系統為例主流分片鍵是user_id或order_id。選user_id的好處是用戶能查到自己的全部訂單天然落在同一個分片上壞處是通過order_id直接查詢詳情就麻煩了——訂單號不知道屬于哪個用戶得路由到所有分片去查一次點開詳情頁所有分片都得掃一遍。選order_id作為分片鍵則剛好反過來通過訂單號查詳情很快但查用戶某個時間段的訂單列表就麻煩了。工程上的常見解法是維護一張映射表或者在生成order_id時內嵌用戶ID的分片信息讓order_id自身就攜帶分片路由信息。舉個例子生成訂單號的時候把用戶ID的后4位嵌入到訂單號中這樣拿到order_id算一下就知道去哪個分片查不需要額外建立映射關系。分布式ID這塊我強烈建議不要再用數據庫自增ID作為分片表的全局主鍵。因為分片之后每個實例的自增ID會重復必須要引入全局唯一ID生成方案。常見的方案有雪花算法Snowflake、各個中間件自帶的ID生成器或者用Redis的原子INCR。雪花算法是目前使用最廣泛的方案它的核心是時間戳機器ID序列號每秒能生成幾百萬個不重復ID而且趨勢遞增對數據庫索引友好。5.3 分庫分表之后這些SQL全都不能用了分庫分表帶來的功能損失如果你沒有足夠的心理和技術準備上線后會讓業務方罵娘。我先列一份“黑名單”給你做好準備全局表JOIN消失了。不能做跨分片的JOIN查詢。以前一條SQL搞定的事現在得代碼里先在各個分片上分別查完然后在內存里做map合并和關聯。數據量小時還能接受數據量大了這種聚合層邏輯也是性能瓶頸。全局排序分頁非常痛苦。跨分片做ORDER BY create_time LIMIT 0, 20你得把所有分片的top N都拉出來然后在代碼層重新排序截取。更惡心的深翻頁——你根本沒法像單表那樣直接跳到第100頁因為每個分片都不知道全局的第990到第1000行是哪幾條。這也是為什么分庫分表之后的產品設計大都改成“下拉加載更多”而不是跳頁。分布式事務成本高。分片之后跨分片的事務已經超越了MySQL本地事務的能力你需要引入分布式事務方案兩階段提交、最終一致性的消息事務、或者TCC這套復雜度不是小團隊能輕易駕馭的。所以分庫分表后第一原則是盡可能把需要事務的數據放到同一個分片也就是通過合理選分片鍵來避免跨分片事務。這些問題沒辦法完全規避只能通過合理的表結構設計來緩解。我見過的高可用設計是把訂單主表按user_id分片訂單明細表也按user_id分片這樣同一個用戶的主表記錄和明細表記錄落在同一個物理庫還是可以走本地事務這是把跨分片事務扼殺在搖籃里的標準做法。5.4 中間件選型ShardingSphere、MyCat還是自研路由分庫分表的落地中間件選型決定了你后續半年的運維成本。當前主流有三個方向ShardingSphere推薦首選Apache頂級項目支持分片、讀寫分離、數據加密等多種能力。它的JDBC模式是一個輕量級jar包嵌入到應用里代碼侵入小、性能損耗低適合Java技術棧是我們團隊目前的主力方案。MyCat適合做數據庫代理層獨立部署的中間件服務對應用透明應用不需要改連接方式但它需要額外部署和運維一套集群鏈路更長性能損耗也更大。如果非Java技術棧或者想引入數據庫中間件這個方向可以評估。自研路由適合極簡場景只在代碼層做一個簡單的分片規則算法比如通過用戶ID取模路由到不同的庫。這個方案性能最好、最可控但只適合分片規則簡單、分片數量固定、不要求彈性擴縮容的場景。再復雜一點你就得從頭自己解決分布式事務、平滑擴容這些問題代價極高。我在這塊的取舍原則是沒有完美的中間件適合當前團隊規模和技術棧的才是最好的。如果團隊只有兩三個人我建議直接用ShardingSphere-JDBC學習成本低出了問題社區答案也多如果公司數據團隊規范成熟、對數據庫有強管控需求MyCat的代理模式會讓DBA更安心。5.5 數據遷移與擴容從停機遷移到平滑擴容的演進分庫分表之后遲早要面對擴容的問題。假設初始分成了16個分片兩年后數據量漲到需要32個分片怎么辦這時候最忌諱的是用hash(user_id) % 32這種規則因為一旦把取模基數從16改成32幾乎所有數據都要重新分布遷移成本讓人崩潰。我建議在設計分片規則時就直接避免這個問題。兩個常用方案一致性哈希。它把哈希值空間組織成環數據落在哪個分片由它沿環順時針找到的第一個節點決定。擴容的時候只需要遷移部分分片上的數據到新節點不用全部重排。運維成本要低很多。按時間分片歷史數據冷處理。對于日志、流水類數據按月份或季度分片。過了當前月份的數據不會再有寫入完全不需要遷移。查詢的時候帶上月份條件就能定位到對應分片讀取性能和擴容便利性都有保障。這兩個方案選哪個取決于業務特性核心還是要前置考慮別等數據量大了再改路由規則那真是所有DBA的噩夢。6. 優化效果的一次完整復盤一個日訂單量百萬級系統的改造路徑理論講了這么多還是給你看一個我印象比較深的完整改造案例。這套系統我接手時已經線上運行了兩年每天新增訂單量在100萬行左右數據庫單表已經突破1.8億行高峰期核心查詢接口平均響應時間從最初的300ms惡化到3秒左右時不時還會因為慢查詢拖垮連接池。6.1 從200ms到20ms索引重建與SQL重寫的組合拳接手第一階段我根本沒考慮分庫分表而是先針對現有單表做極限優化目標是確認在不動架構的前提下能壓榨出多少性能。先抓了top慢SQL發現80%的請求落在兩個查詢上一是訂單列表查詢按user_id和order_status過濾按create_time倒序分頁二是訂單數統計按seller_id和日期范圍聚合。訂單列表查詢的問題是原來的單列索引(user_id)和(create_time)單獨存在導致WHERE條件走了user_id索引但ORDER BY create_time依然要filesort。我用覆蓋索引方案把聯合索引直接設計為(user_id, order_status, create_time, id)查詢條件直接從索引里全部取出連回表都省了。這個索引上線后列表查詢從450ms降到25ms。訂單數統計的問題在GROUP BY。原來的SQL是SELECT seller_id, COUNT(*) FROM orders WHERE create_time BETWEEN ? AND ? GROUP BY seller_id沒有合適的聯合索引觸發了臨時表和全表掃描。我調整成了(create_time, seller_id)聯合索引讓時間過濾和分組字段都能走索引。日期范圍檢索后的分組從全表掃描變成了索引順序掃描加分組統計耗時從2.8秒降到180ms。這輪改造做完線上整體慢查詢數量直接下降了一個數量級因為90%的核心查詢都通過新索引跑進了幾十毫秒的區間。6.2 什么觸發了最終的分庫分表以及落地后的效果對比索引和SQL優化到極限之后系統穩定運行了相當長一段時間。但隨著業務繼續擴張單日訂單量從100萬翻到500萬單表數據量爆炸式增長到7億行以上索引深度已經達到4層即使覆蓋索引也開始出現明顯延遲數據庫磁盤占用率長期接近90%。更致命的是核心表的寫入并發量大增單庫的寫入性能開始成為瓶頸連續兩次因為讀寫互鎖導致高峰期訂單寫入延遲超過10秒。到了這個節點單表優化的路已經走到頭了我們才正式啟動了分庫分表方案。按順序挑了ShardingSphere-JDBC作為中間件分片鍵選了user_id分成16個物理庫每個庫內按月份做子表這樣既能根據用戶很快定位又能利用時間維度做冷熱分離這個方案兼顧了業務查詢規律和后續數據歸檔的便利性。因為分片鍵選對了大部分按用戶視角的查詢只需要路由到唯一一個分片響應時間從優化前的平均500ms進一步降到30ms以內。以前那些跨月查詢訂單列表的SQL現在通過ShardingSphere的綁定表功能自動路由到各個月表再合并結果整體體驗也沒差太多。這輪改造落地的關鍵其實還是前面的鋪墊因為索引和SQL優化已經把單表性能壓到了極致我們才敢在數據量確實起來以后有條不紊地分庫分表而不是在性能問題一出現就倉促上馬。很多團隊把順序搞反了單表索引還沒優化好就慌忙分庫分表后果基本都是雪上加霜。7. 全局優化思路的更高層面索引優化與架構演進的金線在哪里最后想聊聊我在每一次優化項目中都會問自己的一個問題優化的金線到底畫在哪里這個話題可能有點“務虛”但它決定了你在一個項目里投入多少精力和資源值得單獨拿一小節來說。7.1 優化決策的兩條鐵律不要過度設計也不要放棄治療第一條鐵律是先拿數據說話再談方案取舍。每個優化的起點都是監控數據而不是直覺。慢查詢日志、監控面板上的CPU、IOPS、連接數曲線能告訴你系統真實的壓力點在哪個維度。數據還沒確認瓶頸先優化了一堆業務代碼那是白費勁。第二條鐵律是不在錯誤層次上優化。有些團隊遇到慢查詢第一反應是換數據庫結果換完發現還是慢——因為慢的根本原因是查詢要返回幾百兆數據給應用層。反過來有些團隊一遇到寫入慢就硬著頭皮分析SQL結果根因是磁盤IOPS不夠加一塊SSD或者把RAID級別換一下就好了。怎么判斷正確的優化層次我的經驗是如果CPU高通常優先考慮SQL和索引優化如果磁盤IO高往往要考慮減少回表冷讀量、增加內存緩沖池或者升級硬件如果連接數滿一般查慢查詢和鎖等待如果以上都排除了還是扛不住才輪到架構層——緩存、讀寫分離、分庫分表逐層升級。7.2 當分庫分表也不夠用的時候冷熱分離與數據歸檔的最后一公里有一種情況即使做了分庫分表你仍然會發現熱數據被冷數據拖累。因為分片是按用戶ID或者訂單號分某個分片上的用戶可能恰好一年了還有大量歷史訂單查詢需求和另一個活躍度低的分片負載極不均衡。這時候冷熱分離就非常重要了。以訂單系統為例我們上線了數據歸檔任務把超過一年的訂單從在線訂單庫定期遷移到歷史歸檔庫。在線庫里只保留近一年的熱數據一年前的訂單需要查詢時走獨立的歸檔查詢接口。由于在線庫的數據量直接砍掉一半以上索引深度降低查詢和寫入都變輕了。這一步和分庫分表是能疊加的。很多團隊分庫分表后就停在原地其實還可以再往前走一步在分片內部再按時間滾動切割表或者定期對冷數據做壓縮歸檔。這一套組合拳打下來系統的橫向擴展能力和縱向數據治理能力才算是真正建立起來了。7.3 緩存系統與MySQL的分工協作別把緩存當成萬能藥最后想提醒一個特別容易被誤用的點很多人把Redis緩存當成MySQL問題的萬能藥有什么性能問題先加一層緩存。緩存確實能分擔讀壓力但如果你把緩存當成覆蓋一切查詢的手段等到緩存穿透、緩存雪崩和緩存一致性這些問題一起來的時候你才會意識到緩存治理的復雜度完全不亞于數據庫優化。更合理的方式是先讓MySQL本身健健康康地跑再用緩存去解決MySQL“不該承擔”的讀壓力。什么算不該承擔熱點數據讀多寫少、一致性要求不高的場景比如商品詳情、配置信息這些完全可以扔到Redis里。但強一致性的庫存扣減、訂單狀態流轉這些數據最好留在MySQL中用數據庫事務能力來保證而不是把一致性邏輯搬到Redis的Lua腳本里去任何一個環節失誤都會造成資損。我見過最“嚇人”的架構是把訂單都放到Redis里MySQL只做持久化備份結果Redis宕機一次就永久丟了一部分未落庫的訂單。這種災難完全可以避免——緩存回歸緩存數據庫承擔它該承擔的一致性保障角色。這里不是否認緩存的價值而是強調分層清楚、職責分明。做了這么多年MySQL優化我最深的體會是優化不是一錘子買賣也不是一招鮮吃遍天。索引、SQL、分庫分表乃至緩存與冷熱分離是一個層層遞進、互相配合的體系。每個階段都有它該做的功課和不該越過的紅線而判斷那條金線在哪里才是區分一個普通增刪改查工程師和資深性能優化專家的根本差異。如果你現在正被MySQL性能問題困擾我建議你按這篇文章的層次逐一排查先打開慢查詢日志抓問題SQL再用EXPLAIN分析執行計劃然后用索引設計和SQL重寫去解決85%以上的性能問題當單表優化到頭且數據增長趨勢確實不可逆時再理性評估分庫分表并且一定要記住分片鍵大于一切路由規則要預留擴展空間。順著這個思路走你至少能少踩我當年踩過的那一連串大坑。 MySQL性能優化實戰：從慢查詢定位到索引與分庫分表
每次接手一個“MySQL慢得像蝸牛”的排查任務我第一反應不是去看服務器配置也不是去罵產品經理又亂寫需求而是先打開慢查詢日志和EXPLAIN。干這行十年經手的MySQL優化案例沒有一千也有八百不管是單表幾十萬數據的初創項目還是分庫分表后單表仍上億的成熟業務系統最終的性能瓶頸往往都收束到三個方向索引是否設計合理、SQL是否寫得到位、數據量大到一定程度后架構是否扛得住。這篇文章不會跟你聊那些“高大上”的底層原理名詞堆砌就實打實地把索引、SQL調優、分庫分表這三塊硬骨頭拆開揉碎講清楚每個方案背后的取舍邏輯和我在項目里驗證過的真實做法。這篇攻略適合誰看如果你剛把業務代碼寫完發現線上數據庫CPU經常飆到100%或者查詢接口隨著數據量增長從200ms一路滑到2秒開外又或者你正在為“要不要分庫分表”這個問題糾結到失眠——那這篇文章就是寫給你的。我盡量把每個決策點都還原成“當時我遇到的是什么場景、為什么選這個方案、踩了哪些坑”而不是給你一堆PPT式的理論框架。1. 先定位慢在哪90%的MySQL性能問題都出在同一個地方我見過太多團隊一上來就討論要不要換PostgreSQL、要不要上分布式數據庫結果我上去一查單表才三千萬數據連接數才一兩百根本還沒到換數據庫的地步純粹是幾條核心SQL沒寫好索引走了全表掃描。定位慢查詢這件事是后續所有優化的地基地基不穩后面所有動作都是瞎忙活。1.1 慢查詢日志與EXPLAIN的正確打開方式排查的第一步永遠是打開慢查詢日志。很多開發者說“我開了啊”結果一查long_query_time設置的是默認的10秒等于沒開。這個閾值我一般建議從0.5秒開始設先抓出當前消耗最高的SQL——晚上用pt-query-digest這類工具一匯總top 10的慢SQL基本就是你要優化的大頭。拿到慢SQL之后先別急著看業務邏輯直接EXPLAIN看執行計劃。這里有個新手特別容易忽略的點EXPLAIN看的是預估執行計劃它告訴你的是優化器“打算怎么干”不是“實際什么表現”。真正要看你得開EXPLAIN ANALYZEMySQL 8.0.18支持它能給出實際執行時間和掃描行數做完索引改動之后也必須用這個命令確認優化效果而不是只看type是不是從ALL變成了ref就以為萬事大吉。看執行計劃時我有一套自己的快速判斷順序type字段從好到差依次是 system const eq_ref ref range index ALL。只要看到ALL基本可以確定這條SQL要全表掃除非表就幾百行否則一定要優化。key字段看看實際用了哪個索引。經常出現的坑是possible_keys里有候選索引但key是NULL——就是優化器壓根沒選你的索引。rows字段預估掃描行數。這個數值和實際行數相差太大通常意味著統計信息過期或者WHERE條件的寫法讓優化器無法做準確估算。Extra字段看到Using filesort和Using temporary這是兩個危險信號一個說明排序沒有走索引一個說明臨時表被用到了后面第六節詳細講。1.2 從執行計劃到根因判斷一張排查對照表為了讓你排查起來更有方向感我把日常最常遇到的執行計劃特點和對應根因整理成一張表。這個話單是多年實戰積累下來的濃縮版排查時直接對照著看就行執行計劃特征可能根因優先處理建議typeALL, rows 很大沒建索引或索引失效檢查WHERE和JOIN條件字段建立合適的單列或聯合索引keyNULL, possible_keys 有值優化器放棄索引可能是數據分布問題或函數運算導致重寫SQL去掉函數包裹如果實在無法避免考慮強制索引Using filesort排序字段和WHERE條件不在同一個聯合索引里調整聯合索引字段順序讓排序走索引而不是再開一道排序Using temporaryGROUP BY 或 DISTINCT 觸發了臨時表重寫查詢或者調整索引讓分組字段有序rows 誤差超過10倍統計信息嚴重過期執行 ANALYZE TABLE 刷新統計信息索引選擇性極低重復率過高建了索引但區分度不夠優化器不想用換區分度更高的字段或者用覆蓋索引繞開回表還有一種坑比較隱蔽——索引選擇性誤判。比如性別字段你建了索引結果優化器發現全表一半是男一半是女它覺得用這個索引還不如直接掃全表劃算于是直接放棄索引。遇到這種情況別硬剛優化器要么用覆蓋索引帶上查詢需要的其他列讓掃描成本降低要么直接改業務查詢邏輯去掉這個條件。2. 索引為什么失效回表、最左前綴與函數運算的連鎖反應索引優化是MySQL性能優化的第一顆子彈。但很多人建完索引就以為完事了結果上線后該慢還是慢。索引失效的那幾類典型場景我閉著眼都能背出來——不是因為我記憶力好而是每個坑我都親自掉進去過又親自爬出來。2.1 函數包裹、隱式轉換和區分度問題一個聯合索引的設計案例先看一個我最近處理的真實案例。一張訂單表查詢條件是WHERE pay_time BETWEEN ... AND ...我建議開發在上面建索引他說建了但你猜怎么著他是這么寫的WHERE DATE(pay_time) BETWEEN 2024-01-01 AND 2024-01-31。索引是建在pay_time上的但DATE()函數一包索引直接失效——因為MySQL要先算出DATE(pay_time)的結果才能去和區間比較這等于把全部行先計算一遍那建索引還有什么意義正確的寫法應該是WHERE pay_time 2024-01-01 AND pay_time 2024-02-01既覆蓋了整個一月份的區間又沒做任何函數運算索引能完美走range掃描。這條規則幾乎適用于所有日期范圍查詢也是我審查SQL時一眼就能掃出來的問題。同樣的道理字段類型不匹配的隱式轉換也是索引殺手。比如user_id字段是varchar(32)但你在代碼里傳入的是整數類型的IDMySQL會自動把字符串轉成數字再比對。這本來不算什么大問題壞就壞在當你拿一個數字去和varchar字段比較的時候MySQL會先對所有行的user_id做一次類型轉換于是索引全部失效。這類問題很難排查因為它不報錯執行計劃也不崩但rows行數就是下不來。真正設計聯合索引的時候我養成了三個習慣分享出來供你參考把等值條件放最前面然后才是范圍條件。比如查詢條件是WHERE status1 AND create_time 2024-01-01聯合索引就應該設計為(status, create_time)。因為等值條件可以精確定位范圍條件只需要在等值定位后的范圍里掃一小段。能用覆蓋索引解決的盡量用覆蓋索引。比如查詢只需要id和status建一個(status, id)的索引MySQL就能直接從索引里拿數據連回表都省了這是最快的一種查詢路徑。控制索引數量。這塊我必須多說兩句。我見過一個項目為了一張表建了12個索引寫入性能慘不忍睹。索引不是越多越好每多一個索引INSERT和UPDATE就要多維護一個B樹。通常單表索引控制在5個以內能復用聯合索引的左前綴就絕不重復建索引。2.2 復合索引的最左前綴原則一個字段順序引發的性能災難復合索引聯合索引最核心的規則就是最左前綴原則——MySQL 8.0之前優化器只能從左往右匹配索引列你不能跳過第一列直接用到第二列。這個我建議你牢記在腦子里因為實際開發中被它坑過的案例我至少親手處理過十次。具體來說假設你建了復合索引(a, b, c)WHERE a1能走索引WHERE a1 AND b2能走索引WHERE a1 AND c3只能用到索引的a列c列用不上WHERE b2完全不走這個索引最經典的反例是業務需求經常按name和create_time查開發就建了(name, create_time)的索引。但后來新增了一個需求只按create_time范圍查詢發現這條新SQL慢得離譜查看執行計劃居然走了全表掃描。原因就是這個復合索引根本幫不上忙——因為create_time不是第一列只按它查的時候索引直接報廢。這時候正確的做法有三種。第一種是再建一個(create_time)的單列索引第二種是如果你頻繁需要這個組合直接正面剛改查詢讓name條件不缺席比如業務上允許默認帶一個空串或通配但要注意通配符走不了索引這里不做推薦第三種最優雅——把復合索引設計成(create_time, name)既支持按時間查又支持按時間姓名查。我強烈推薦第三種因為create_time通常是范圍條件放在左邊之后右邊接等值條件大多數情況下都能滿足新老需求。索引失效的場景還有很多比如WHERE name LIKE %張這種前置通配符會導致索引失效這個好理解B樹是按前綴有序的你硬要在中間挖一段它沒法定位還有OR連接的非索引列場景以及SQL里出現!或IS NOT NULL的情況。但歸根結底你要理解B樹的排序存儲特性就能自己推導出索引什么時候能用只要是破壞了有序性或者需要全量計算的場景索引大概率就廢了。3. SQL層面最容易忽略的性能殺手分頁、JOIN與排序的隱形代價索引設計得再合理SQL寫得不靠譜照樣卡死。這一節我從最常見的三個業務場景入手分頁、多表關聯和排序分組把隱藏在業務代碼背后的性能殺手挨個揪出來。3.1 分頁翻得越深越慢limit深翻頁問題的三種解法做后臺管理系統的同學應該有切身體會——導數據列表的時候越往后面翻頁越慢。比如運營人員在列表頁一頁頁翻到第200頁每頁50條這頁只顯示第9951到10000條但MySQL干的事是把從101到10000行的所有行先掃出來然后全部丟掉只把最后50條返回給你。數據量小的時候感覺不出來數據量上到9000萬這種寫法的延遲你根本沒法接受。排查的時候往往發現開發同學的SQL是這種形態SELECT * FROM orders ORDER BY create_time DESC LIMIT 10000, 20;這條基本就是深翻頁臭名昭著的寫法。優化方案有三種按優先級推薦方案一延遲關聯所有版本通用。先只查id覆蓋索引能快速定位再用這些id去關聯查詢完整行SELECT o.* FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY create_time DESC LIMIT 10000, 20) t ON o.id t.id;方案二基于游標分頁推薦用在Web端。不是翻頁跳轉而是通過上一頁最后一條記錄的ID來進行下一頁定位SELECT * FROM orders WHERE create_time 2024-01-15 10:00:00 ORDER BY create_time DESC LIMIT 20;用where條件過濾掉上一頁已知的最后一條走索引只在數據里順序取20條不丟數據不重排。缺點是用戶不能直接跳到任意頁只能在相鄰頁之間點擊“加載更多”這種交互形式移動端和多數信息流都這么玩因為它壓測下來性能最穩。方案三如果產品必須跳頁提前算好ID集合一次性查詢。用前端的countoffset換算成已知的主鍵集合然后WHERE id IN (...) LIMIT 20。根據我的實測經驗方案二在沒有深翻頁訴求時性能最佳方案一在必須保留跳頁時性價比最高。極限情況下同樣的深翻頁SQL從800ms優化到80ms以內的都是常規操作。3.2 JOIN不是不能用但ON條件沒索引等于災難再來說JOIN。很多規范文檔都會告訴你“盡量少用JOIN”這句話讓不少新手走入了另一個極端——把原本一條JOIN拆成三五條SQL在代碼里挨個查結果網絡來回比以前更慢。我的觀點是JOIN本身不是洪水猛獸關鍵在JOIN字段是否走索引以及小表驅動大表的原則是否被遵守。MySQL的JOIN執行邏輯是這樣的外層驅動表每一行都需要去匹配內層表。如果內層表的連接字段沒有索引那就是一次全表掃描乘以驅動表的行數——這個代價是幾何級增長的。以一個業務系統為例用戶表幾十萬條訂單表上千萬條要查某時間段內有訂單的用戶信息。如果JOIN條件是u.id o.user_id但訂單表的user_id沒建索引每次匹配都要去掃訂單表的一千多萬行這條查詢基本就是災難現場。建上user_id索引一切迎刃而解。所以我審查SQL的第一原則不是“見了JOIN就皺眉”而是看EXPLAIN里的type只要被驅動表的連接字段是ref或eq_ref級別JOIN完全可以放心用。另一個重點是小表驅動大表。MySQL的優化器一般會自動選擇小表做驅動表但如果你用什么子查詢包裹的方式去打亂它的統計幻覺優化器也有可能做出錯誤選擇。這時候STRAIGHT_JOIN可以直接指定驅動順序把大表放到被驅動位置。這個技巧不常用但當你在EXPLAIN里看到驅動表選錯的時候它比硬改SQL簡單得多。3.3 filesort和臨時表ORDER BY與GROUP BY為什么會拖垮查詢ORDER BY最怕的不是排序本身而是排序字段沒法走索引迫使MySQL把所有結果集撈出來再額外執行一次內存排序或者磁盤排序。EXPLAIN里的Using filesort就是它。在這個地方聯合索引的字段順序又體現出關鍵作用。比如聯合索引是(status, create_time)當查詢條件是WHERE status1 ORDER BY create_time DESCMySQL可以直接按索引順序來讀不需要額外排序。但如果你把ORDER BY create_time中的create_time放在索引非末尾位置或者排序條件和查詢條件的順序不匹配filesort就出現了。同樣GROUP BY 之所以效率低是因為它通常伴隨著臨時表的創建MySQL 8.0之前還會專門建個內部臨時表來存分組中間結果量大還可能落到磁盤。遇到大表上的GROUP BY我的優化順序是先看能否通過聯合索引設計讓分組字段有序這樣MySQL就可以直接順序掃索引然后順便分組不需要建臨時表。如果分組字段的基數很小比如就幾個枚舉狀態改用COUNT(DISTINCT ...)配合業務側的Map匯總有時反而更利落。非要在SQL里GROUP BY把返回字段做得盡量窄只取需要分組的字段和聚合函數字段減少臨時表的內存壓力。4. 寫入性能與并發控制的隱形瓶頸不只是慢查詢才需要關注慢查詢折磨人寫入抖動同樣是事故隱患。很多后臺系統的MySQL優化文章通篇講SELECT但一到每天零點定時任務跑批、或者業務高峰導入數據的場景INSERT和UPDATE的性能問題能把線上打掛。這一節聊聊我在處理寫入瓶頸和并發控制時常用的兩招批量寫的正確姿勢和鎖競爭的有效規避。4.1 批量提交與事務大小的黃金分割點剛工作那會兒我寫過一個批量導入腳本循環一萬條數據一條一條INSERT跑了一個多小時還沒跑完。后來改成一次事務批量INSERT一千條十分鐘跑完。差異就在于INSERT本身一條條執行時每次都有網絡往返、事務提交刷盤的開銷而批量提交可以把這些固定開銷攤薄。但也不是批量越大越好。我有過一次性INSERT十萬條的“翻車”經歷——事務過大導致undo日志膨脹MVCC機制要保留舊版本數據binlog同步延遲飆升主庫事務把從庫備庫拖得追不上差點引發主從切換事故。后來總結出的經驗是單個事務控制在1000~5000條范圍或者事務執行時間控制在1秒以內。這個數字不是拍腦袋定的而是權衡了網絡往返、鎖持有時長、binlog體積和回滾成本之后的可接受區間你可以根據自己業務的寫入量做微調。預處理語句綁定參數值得強烈推薦INSERT INTO orders (order_id, user_id, amount, status) VALUES (?, ?, ?, ?)使用prepared statement批量綁定參數能避免每條INSERT都做一次SQL解析。在大批量導數據場景這個優化效果非常明顯一千萬行的導數任務從小時級縮到十幾分鐘。4.2 行鎖、間隙鎖與死鎖為什么長事務比慢SQL更可怕慢SQL至少還能在慢查詢日志里顯式看到長事務才是真正的隱形殺手。事務不提交它持有的鎖就一直不釋放后續所有要更新同一條記錄、甚至插入相鄰記錄的事務都會排隊堵住。更隱蔽的是MVCC下的undo日志膨脹長事務意味著大量舊版本數據不能被purge線程清理這會導致表空間無限膨脹查詢還要在版本鏈上往前找正確的版本性能越來越差。這類長事務的來源十有八九是業務代碼在同一個事務里調用了外部接口。例如一個“創建訂單并扣庫存”的接口你在Spring的Transactional事務里調用支付回調接口等外部響應等了3秒。這三秒里整個訂單表相關范圍的鎖都捏在你手里所有同類請求全部排隊阻塞線上就炸鍋了。死鎖同樣值得反復排查。我處理過最典型的死鎖場景是兩個事務都執行“先更新主表再更新明細表”但順序恰好反過來兩邊互相等鎖MySQL檢測到死鎖后就隨機選一個事務回滾。這種死鎖在高并發寫入下特別常見。規避方法不復雜所有事務按照固定順序訪問資源比如先主表后明細表或者把相關行一次性用SELECT ... FOR UPDATE鎖住。經驗之談線上環境一定要監控長事務和鎖等待事件。information_schema.innodb_trx這張表可以直接查出來哪些事務超過3秒還沒提交配套sys.innodb_lock_waits能定位鎖等待的根因。趁事務還沒膨脹到釀成大禍之前把它殺在萌芽階段是DBA和資深后端必須建立的夜間值班基本功。5. 分庫分表的前提判斷數據量大不等于必須要分講到分庫分表我先潑一盆冷水分庫分表是最后的手段不是第一選擇。很多人看數據量到了幾千萬就喊著要分庫分表結果分完以后跨庫JOIN、分布式事務、分布式ID、擴容數據遷移樣樣都是新坑問題沒解決反而更多了。我的經驗是在動手分庫分表之前你先把下面這些事做扎實了再說。5.1 什么數據量級才需要分庫分表三大前置判斷標準判斷要不要分庫分表不是一個數字閾值就拍板的我通常會看三個維度同時亮紅燈才啟動方案單表數據量超過MySQL的舒適區。InnoDB的B樹在單表數據量一億以內理論上都能扛住但實際運維中發現當表超過2000萬行且索引深度超過3層時查詢延遲開始明顯可見地退化。當然這和數據寬度、磁盤IO性能都有關系不是絕對判斷標準。單庫的并發寫入能力成為瓶頸。比如數據庫連接數打滿每秒寫入行數受制于磁盤IOPS即使拆索引、優化SQL也提不上去。數據增長趨勢是持續且陡峭的。如果只是短期活動帶來的一波數據量活動結束就平穩了那沒必要為了峰值去做永久性的分庫分表改造。我見過一個日志表三個月時間從2億漲到15億這種就屬于必須分另一種是用戶表全年就漲個2000萬說實話單表再撐幾年問題不大。如果只占了第一點單表數據量大但讀多寫少優先考慮冷熱分離歸檔。比如訂單表把一年前的訂單遷移到一個獨立的歸檔庫或者只讀歷史表主表數據量直接砍掉70%查詢性能秒回。占有便宜又見效快完全不需要分庫分表。如果第二、第三點同時出現比如訂單系統日訂單量突破500萬單、流水表半年就過10億行那才是分庫分表的啟動時機可以進入下一節。5.2 分片鍵選錯是最大的災難訂單表案例的教訓分庫分表的第一步不是選中間件而是選分片鍵。這里有一條血的教訓分片鍵必須滿足80%以上的核心查詢能帶著它走否則就是給自己埋雷。以電商訂單系統為例主流分片鍵是user_id或order_id。選user_id的好處是用戶能查到自己的全部訂單天然落在同一個分片上壞處是通過order_id直接查詢詳情就麻煩了——訂單號不知道屬于哪個用戶得路由到所有分片去查一次點開詳情頁所有分片都得掃一遍。選order_id作為分片鍵則剛好反過來通過訂單號查詳情很快但查用戶某個時間段的訂單列表就麻煩了。工程上的常見解法是維護一張映射表或者在生成order_id時內嵌用戶ID的分片信息讓order_id自身就攜帶分片路由信息。舉個例子生成訂單號的時候把用戶ID的后4位嵌入到訂單號中這樣拿到order_id算一下就知道去哪個分片查不需要額外建立映射關系。分布式ID這塊我強烈建議不要再用數據庫自增ID作為分片表的全局主鍵。因為分片之后每個實例的自增ID會重復必須要引入全局唯一ID生成方案。常見的方案有雪花算法Snowflake、各個中間件自帶的ID生成器或者用Redis的原子INCR。雪花算法是目前使用最廣泛的方案它的核心是時間戳機器ID序列號每秒能生成幾百萬個不重復ID而且趨勢遞增對數據庫索引友好。5.3 分庫分表之后這些SQL全都不能用了分庫分表帶來的功能損失如果你沒有足夠的心理和技術準備上線后會讓業務方罵娘。我先列一份“黑名單”給你做好準備全局表JOIN消失了。不能做跨分片的JOIN查詢。以前一條SQL搞定的事現在得代碼里先在各個分片上分別查完然后在內存里做map合并和關聯。數據量小時還能接受數據量大了這種聚合層邏輯也是性能瓶頸。全局排序分頁非常痛苦。跨分片做ORDER BY create_time LIMIT 0, 20你得把所有分片的top N都拉出來然后在代碼層重新排序截取。更惡心的深翻頁——你根本沒法像單表那樣直接跳到第100頁因為每個分片都不知道全局的第990到第1000行是哪幾條。這也是為什么分庫分表之后的產品設計大都改成“下拉加載更多”而不是跳頁。分布式事務成本高。分片之后跨分片的事務已經超越了MySQL本地事務的能力你需要引入分布式事務方案兩階段提交、最終一致性的消息事務、或者TCC這套復雜度不是小團隊能輕易駕馭的。所以分庫分表后第一原則是盡可能把需要事務的數據放到同一個分片也就是通過合理選分片鍵來避免跨分片事務。這些問題沒辦法完全規避只能通過合理的表結構設計來緩解。我見過的高可用設計是把訂單主表按user_id分片訂單明細表也按user_id分片這樣同一個用戶的主表記錄和明細表記錄落在同一個物理庫還是可以走本地事務這是把跨分片事務扼殺在搖籃里的標準做法。5.4 中間件選型ShardingSphere、MyCat還是自研路由分庫分表的落地中間件選型決定了你后續半年的運維成本。當前主流有三個方向ShardingSphere推薦首選Apache頂級項目支持分片、讀寫分離、數據加密等多種能力。它的JDBC模式是一個輕量級jar包嵌入到應用里代碼侵入小、性能損耗低適合Java技術棧是我們團隊目前的主力方案。MyCat適合做數據庫代理層獨立部署的中間件服務對應用透明應用不需要改連接方式但它需要額外部署和運維一套集群鏈路更長性能損耗也更大。如果非Java技術棧或者想引入數據庫中間件這個方向可以評估。自研路由適合極簡場景只在代碼層做一個簡單的分片規則算法比如通過用戶ID取模路由到不同的庫。這個方案性能最好、最可控但只適合分片規則簡單、分片數量固定、不要求彈性擴縮容的場景。再復雜一點你就得從頭自己解決分布式事務、平滑擴容這些問題代價極高。我在這塊的取舍原則是沒有完美的中間件適合當前團隊規模和技術棧的才是最好的。如果團隊只有兩三個人我建議直接用ShardingSphere-JDBC學習成本低出了問題社區答案也多如果公司數據團隊規范成熟、對數據庫有強管控需求MyCat的代理模式會讓DBA更安心。5.5 數據遷移與擴容從停機遷移到平滑擴容的演進分庫分表之后遲早要面對擴容的問題。假設初始分成了16個分片兩年后數據量漲到需要32個分片怎么辦這時候最忌諱的是用hash(user_id) % 32這種規則因為一旦把取模基數從16改成32幾乎所有數據都要重新分布遷移成本讓人崩潰。我建議在設計分片規則時就直接避免這個問題。兩個常用方案一致性哈希。它把哈希值空間組織成環數據落在哪個分片由它沿環順時針找到的第一個節點決定。擴容的時候只需要遷移部分分片上的數據到新節點不用全部重排。運維成本要低很多。按時間分片歷史數據冷處理。對于日志、流水類數據按月份或季度分片。過了當前月份的數據不會再有寫入完全不需要遷移。查詢的時候帶上月份條件就能定位到對應分片讀取性能和擴容便利性都有保障。這兩個方案選哪個取決于業務特性核心還是要前置考慮別等數據量大了再改路由規則那真是所有DBA的噩夢。6. 優化效果的一次完整復盤一個日訂單量百萬級系統的改造路徑理論講了這么多還是給你看一個我印象比較深的完整改造案例。這套系統我接手時已經線上運行了兩年每天新增訂單量在100萬行左右數據庫單表已經突破1.8億行高峰期核心查詢接口平均響應時間從最初的300ms惡化到3秒左右時不時還會因為慢查詢拖垮連接池。6.1 從200ms到20ms索引重建與SQL重寫的組合拳接手第一階段我根本沒考慮分庫分表而是先針對現有單表做極限優化目標是確認在不動架構的前提下能壓榨出多少性能。先抓了top慢SQL發現80%的請求落在兩個查詢上一是訂單列表查詢按user_id和order_status過濾按create_time倒序分頁二是訂單數統計按seller_id和日期范圍聚合。訂單列表查詢的問題是原來的單列索引(user_id)和(create_time)單獨存在導致WHERE條件走了user_id索引但ORDER BY create_time依然要filesort。我用覆蓋索引方案把聯合索引直接設計為(user_id, order_status, create_time, id)查詢條件直接從索引里全部取出連回表都省了。這個索引上線后列表查詢從450ms降到25ms。訂單數統計的問題在GROUP BY。原來的SQL是SELECT seller_id, COUNT(*) FROM orders WHERE create_time BETWEEN ? AND ? GROUP BY seller_id沒有合適的聯合索引觸發了臨時表和全表掃描。我調整成了(create_time, seller_id)聯合索引讓時間過濾和分組字段都能走索引。日期范圍檢索后的分組從全表掃描變成了索引順序掃描加分組統計耗時從2.8秒降到180ms。這輪改造做完線上整體慢查詢數量直接下降了一個數量級因為90%的核心查詢都通過新索引跑進了幾十毫秒的區間。6.2 什么觸發了最終的分庫分表以及落地后的效果對比索引和SQL優化到極限之后系統穩定運行了相當長一段時間。但隨著業務繼續擴張單日訂單量從100萬翻到500萬單表數據量爆炸式增長到7億行以上索引深度已經達到4層即使覆蓋索引也開始出現明顯延遲數據庫磁盤占用率長期接近90%。更致命的是核心表的寫入并發量大增單庫的寫入性能開始成為瓶頸連續兩次因為讀寫互鎖導致高峰期訂單寫入延遲超過10秒。到了這個節點單表優化的路已經走到頭了我們才正式啟動了分庫分表方案。按順序挑了ShardingSphere-JDBC作為中間件分片鍵選了user_id分成16個物理庫每個庫內按月份做子表這樣既能根據用戶很快定位又能利用時間維度做冷熱分離這個方案兼顧了業務查詢規律和后續數據歸檔的便利性。因為分片鍵選對了大部分按用戶視角的查詢只需要路由到唯一一個分片響應時間從優化前的平均500ms進一步降到30ms以內。以前那些跨月查詢訂單列表的SQL現在通過ShardingSphere的綁定表功能自動路由到各個月表再合并結果整體體驗也沒差太多。這輪改造落地的關鍵其實還是前面的鋪墊因為索引和SQL優化已經把單表性能壓到了極致我們才敢在數據量確實起來以后有條不紊地分庫分表而不是在性能問題一出現就倉促上馬。很多團隊把順序搞反了單表索引還沒優化好就慌忙分庫分表后果基本都是雪上加霜。7. 全局優化思路的更高層面索引優化與架構演進的金線在哪里最后想聊聊我在每一次優化項目中都會問自己的一個問題優化的金線到底畫在哪里這個話題可能有點“務虛”但它決定了你在一個項目里投入多少精力和資源值得單獨拿一小節來說。7.1 優化決策的兩條鐵律不要過度設計也不要放棄治療第一條鐵律是先拿數據說話再談方案取舍。每個優化的起點都是監控數據而不是直覺。慢查詢日志、監控面板上的CPU、IOPS、連接數曲線能告訴你系統真實的壓力點在哪個維度。數據還沒確認瓶頸先優化了一堆業務代碼那是白費勁。第二條鐵律是不在錯誤層次上優化。有些團隊遇到慢查詢第一反應是換數據庫結果換完發現還是慢——因為慢的根本原因是查詢要返回幾百兆數據給應用層。反過來有些團隊一遇到寫入慢就硬著頭皮分析SQL結果根因是磁盤IOPS不夠加一塊SSD或者把RAID級別換一下就好了。怎么判斷正確的優化層次我的經驗是如果CPU高通常優先考慮SQL和索引優化如果磁盤IO高往往要考慮減少回表冷讀量、增加內存緩沖池或者升級硬件如果連接數滿一般查慢查詢和鎖等待如果以上都排除了還是扛不住才輪到架構層——緩存、讀寫分離、分庫分表逐層升級。7.2 當分庫分表也不夠用的時候冷熱分離與數據歸檔的最后一公里有一種情況即使做了分庫分表你仍然會發現熱數據被冷數據拖累。因為分片是按用戶ID或者訂單號分某個分片上的用戶可能恰好一年了還有大量歷史訂單查詢需求和另一個活躍度低的分片負載極不均衡。這時候冷熱分離就非常重要了。以訂單系統為例我們上線了數據歸檔任務把超過一年的訂單從在線訂單庫定期遷移到歷史歸檔庫。在線庫里只保留近一年的熱數據一年前的訂單需要查詢時走獨立的歸檔查詢接口。由于在線庫的數據量直接砍掉一半以上索引深度降低查詢和寫入都變輕了。這一步和分庫分表是能疊加的。很多團隊分庫分表后就停在原地其實還可以再往前走一步在分片內部再按時間滾動切割表或者定期對冷數據做壓縮歸檔。這一套組合拳打下來系統的橫向擴展能力和縱向數據治理能力才算是真正建立起來了。7.3 緩存系統與MySQL的分工協作別把緩存當成萬能藥最后想提醒一個特別容易被誤用的點很多人把Redis緩存當成MySQL問題的萬能藥有什么性能問題先加一層緩存。緩存確實能分擔讀壓力但如果你把緩存當成覆蓋一切查詢的手段等到緩存穿透、緩存雪崩和緩存一致性這些問題一起來的時候你才會意識到緩存治理的復雜度完全不亞于數據庫優化。更合理的方式是先讓MySQL本身健健康康地跑再用緩存去解決MySQL“不該承擔”的讀壓力。什么算不該承擔熱點數據讀多寫少、一致性要求不高的場景比如商品詳情、配置信息這些完全可以扔到Redis里。但強一致性的庫存扣減、訂單狀態流轉這些數據最好留在MySQL中用數據庫事務能力來保證而不是把一致性邏輯搬到Redis的Lua腳本里去任何一個環節失誤都會造成資損。我見過最“嚇人”的架構是把訂單都放到Redis里MySQL只做持久化備份結果Redis宕機一次就永久丟了一部分未落庫的訂單。這種災難完全可以避免——緩存回歸緩存數據庫承擔它該承擔的一致性保障角色。這里不是否認緩存的價值而是強調分層清楚、職責分明。做了這么多年MySQL優化我最深的體會是優化不是一錘子買賣也不是一招鮮吃遍天。索引、SQL、分庫分表乃至緩存與冷熱分離是一個層層遞進、互相配合的體系。每個階段都有它該做的功課和不該越過的紅線而判斷那條金線在哪里才是區分一個普通增刪改查工程師和資深性能優化專家的根本差異。如果你現在正被MySQL性能問題困擾我建議你按這篇文章的層次逐一排查先打開慢查詢日志抓問題SQL再用EXPLAIN分析執行計劃然后用索引設計和SQL重寫去解決85%以上的性能問題當單表優化到頭且數據增長趨勢確實不可逆時再理性評估分庫分表并且一定要記住分片鍵大于一切路由規則要預留擴展空間。順著這個思路走你至少能少踩我當年踩過的那一連串大坑。