如何優化MySQL千萬級大表，我寫了6000字的解讀

這是學習筆記的第 2138 篇文章

  

千萬級大表如何優化，這是一個很有技術含量的問題，通常我們的直覺思維都會跳轉到拆分或者資料分割槽，在此我想做一些補充和梳理，想和大家做一些這方面的經驗總結，也歡迎大家提出建議。 

從一開始腦海裡開始也是火光四現，到不斷的自我批評，後來也參考了一些團隊的經驗，我整理了下面的大綱內容。

既然要吃透這個問題，我們勢必要回到本源，我把這個問題分為三部分:

“千萬級”，“大表”，“優化”，

也分別對應我們在圖中標識的

“資料量”，“物件”和“目標”。

我來逐步展開說明一下，從而給出一系列的解決方案。 

1.資料量：千萬級

千萬級其實只是一個感官的數字，就是我們印象中的資料量大。 這裡我們需要把這個概念細化，因為隨著業務和時間的變化，資料量也會有變化，我們應該是帶著一種動態思維來審視這個指標，從而對於不同的場景我們應該有不同的處理策略。

 

1) 資料量為千萬級，可能達到億級或者更高

通常是一些資料流水，日誌記錄的業務，裡面的資料隨著時間的增長會逐步增多，超過千萬門檻是很容易的一件事情。

2) 資料量為千萬級，是一個相對穩定的資料量

如果資料量相對穩定，通常是在一些偏向於狀態的資料，比如有1000萬使用者，那麼這些使用者的資訊在表中都有相應的一行資料記錄，隨著業務的增長，這個量級相對是比較穩定的。

3) 資料量為千萬級，不應該有這麼多的資料

這種情況是我們被動發現的居多，通常發現的時候已經晚了，比如你看到一個配置表，資料量上千萬;或者說一些表裡的資料已經儲存了很久，99%的資料都屬於過期資料或者垃圾資料。

資料量是一個整體的認識，我們需要對資料做更近一層的理解，這就可以引出第二個部分的內容。 

2.物件：資料表

資料操作的過程就好比資料庫中存在著多條管道，這些管道中都流淌著要處理的資料，這些資料的用處和歸屬是不一樣的。

一般根據業務型別把資料分為三種：

（1）流水型資料

流水型資料是無狀態的，多筆業務之間沒有關聯，每次業務過來的時候都會產生新的單據，比如交易流水、支付流水，只要能插入新單據就能完成業務，特點是後面的資料不依賴前面的資料，所有的資料按時間流水進入資料庫。

（2）狀態型資料

狀態型資料是有狀態的，多筆業務之間依賴於有狀態的資料，而且要保證該資料的準確性，比如充值時必須要拿到原來的餘額，才能支付成功。

（3）配置型資料

此型別資料資料量較小，而且結構簡單，一般為靜態資料，變化頻率很低。

至此，我們可以對整體的背景有一個認識了，如果要做優化，其實要面對的是這樣的3*3的矩陣，如果要考慮表的讀寫比例（讀多寫少，讀少寫多...），那麼就會是3*3*4=24種，顯然做窮舉是不顯示的，而且也完全沒有必要，可以針對不同的資料儲存特性和業務特點來指定不同的業務策略。 

對此我們採取抓住重點的方式，把常見的一些優化思路梳理出來，尤其是裡面的核心思想，也是我們整個優化設計的一把尺子，而難度決定了我們做這件事情的動力和風險。

資料量增長情況	資料表型別	業務特點	優化核心思想	優化難度
資料量為千萬級，是一個相對穩定的資料量	狀態表	OLTP業務方向	能不拆就不拆讀需求水平擴充套件	****
資料量為千萬級，可能達到億級或者更高	流水錶	OLTP業務的歷史記錄	業務拆分，面向分散式儲存設計	****
		OLAP業務統計資料來源	設計資料統計需求儲存的分散式擴充套件	***
資料量為千萬級，不應該有這麼多的資料	配置表	通用業務	小而簡，避免大一統	*

而對於優化方案，我想採用面向業務的維度來進行闡述。 

3.目標：優化

在這個階段，我們要說優化的方案了，總結的有點多，相對來說是比較全了。

整體分為五個部分：

其實我們通常所說的分庫分表等方案只是其中的一小部分，如果展開之後就比較豐富了。

其實不難理解，我們要支撐的表資料量是千萬級別，相對來說是比較大了，DBA要維護的表肯定不止一張，如何能夠更好的管理，同時在業務發展中能夠支撐擴充套件，同時保證效能，這是擺在我們面前的幾座大山。

我們分別來說一下這五類改進方案：

優化設計方案1.規範設計

在此我們先提到的是規範設計，而不是其他高大上的設計方案。

黑格爾說：秩序是自由的第一條件。在分工協作的工作場景中尤其重要，否則團隊之間互相牽制太多，問題多多。

規範設計我想提到如下的幾個規範，其實只是屬於開發規範的一部分內容，可以作為參考。

規範的本質不是解決問題，而是有效杜絕一些潛在問題，對於千萬級大表要遵守的規範，我梳理了如下的一些細則，基本可以涵蓋我們常見的一些設計和使用問題，比如表的欄位設計不管三七二十一，都是varchar(500),其實是很不規範的一種實現方式，我們來展開說一下這幾個規範。

1）配置規範

（1）MySQL資料庫預設使用InnoDB儲存引擎。

（2）保證字符集設定統一，MySQL資料庫相關係統、資料庫、表的字符集使都用UTF8，應用程式連線、展示等可以設定字符集的地方也都統一設定為UTF8字符集。

注：UTF8格式是儲存不了表情類資料，需要使用UTF8MB4，可在MySQL字符集裡面設定。在8.0中已經預設為UTF8MB4，可以根據公司的業務情況進行統一或者定製化設定。

（3）MySQL資料庫的事務隔離級別預設為RR（Repeatable-Read），建議初始化時統一設定為RC（Read-Committed），對於OLTP業務更適合。

（4）資料庫中的表要合理規劃，控制單表資料量，對於MySQL資料庫來說，建議單表記錄數控制在2000W以內。

（5）MySQL例項下，資料庫、表數量儘可能少；資料庫一般不超過50個，每個資料庫下，資料表數量一般不超過500個（包括分割槽表）。

2）建表規範

（1）InnoDB禁止使用外來鍵約束，可以通過程式層面保證。

（2）儲存精確浮點數必須使用DECIMAL替代FLOAT和DOUBLE。

（3）整型定義中無需定義顯示寬度，比如：使用INT，而不是INT(4)。

（4）不建議使用ENUM型別，可使用TINYINT來代替。

（5）儘可能不使用TEXT、BLOB型別，如果必須使用，建議將過大欄位或是不常用的描述型較大欄位拆分到其他表中；另外，禁止用資料庫儲存圖片或檔案。

（6）儲存年時使用YEAR(4)，不使用YEAR(2)。

（7）建議欄位定義為NOT NULL。

（8）建議DBA提供SQL稽核工具，建表規範性需要通過稽核工具稽核後

3）命名規範

（1）庫、表、欄位全部採用小寫。

（2）庫名、表名、欄位名、索引名稱均使用小寫字母，並以“_”分割。

（3）庫名、表名、欄位名建議不超過12個字元。（庫名、表名、欄位名支援最多64個字元，但為了統一規範、易於辨識以及減少傳輸量，統一不超過12字元）

（4）庫名、表名、欄位名見名知意，不需要新增註釋。

對於物件命名規範的一個簡要總結如下表4-1所示，供參考。

命名列表

物件中文名稱	物件英文全稱	MySQL物件簡寫
檢視	view	view_
函式	function	func_
儲存過程	procedure	proc_
觸發器	trigger	trig_
普通索引	index	idx_
唯一索引	unique index	uniq_
主鍵索引	primary key	pk_

4）索引規範

（1）索引建議命名規則：idx_col1_col2[_colN]、uniq_col1_col2[_colN]（如果欄位過長建議採用縮寫）。

（2）索引中的欄位數建議不超過5個。

（3）單張表的索引個數控制在5個以內。

（4）InnoDB表一般都建議有主鍵列，尤其在高可用叢集方案中是作為必須項的。

（5）建立複合索引時，優先將選擇性高的欄位放在前面。

（6）UPDATE、DELETE語句需要根據WHERE條件新增索引。

（7）不建議使用%字首模糊查詢，例如LIKE “%weibo”，無法用到索引，會導致全表掃描。

（8）合理利用覆蓋索引，例如：

（9）SELECT email,uid FROM user_email WHERE uid=xx，如果uid不是主鍵，可以建立覆蓋索引idx_uid_email(uid,email)來提高查詢效率。

（10）避免在索引欄位上使用函式，否則會導致查詢時索引失效。

（11）確認索引是否需要變更時要聯絡DBA。

5）應用規範

（1）避免使用儲存過程、觸發器、自定義函式等，容易將業務邏輯和DB耦合在一起，後期做分散式方案時會成為瓶頸。

（2）考慮使用UNION ALL，減少使用UNION，因為UNION ALL不去重，而少了排序操作，速度相對比UNION要快，如果沒有去重的需求，優先使用UNION ALL。

（3）考慮使用limit N，少用limit M，N，特別是大表或M比較大的時候。

（4）減少或避免排序，如：group by語句中如果不需要排序，可以增加order by null。

（5）統計表中記錄數時使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1)；InnoDB表避免使用COUNT(*)操作，計數統計實時要求較強可以使用Memcache或者Redis，非實時統計可以使用單獨統計表，定時更新。

（6）做欄位變更操作（modify column/change column）的時候必須加上原有的註釋屬性，否則修改後，註釋會丟失。

（7）使用prepared statement可以提高效能並且避免SQL隱碼攻擊。

（8）SQL語句中IN包含的值不應過多。

（9）UPDATE、DELETE語句一定要有明確的WHERE條件。

（10）WHERE條件中的欄位值需要符合該欄位的資料型別，避免MySQL進行隱式型別轉化。

（11）SELECT、INSERT語句必須顯式的指明欄位名稱，禁止使用SELECT * 或是INSERT INTO table_name values()。

（12）INSERT語句使用batch提交（INSERT INTO table_name VALUES(),(),()……），values的個數不應過多。

優化設計方案2：業務層優化

業務層優化應該是收益最高的優化方式了，而且對於業務層完全可見，主要有業務拆分，資料拆分和兩類常見的優化場景（讀多寫少，讀少寫多）

1）業務拆分

ü 將混合業務拆分為獨立業務

ü 將狀態和歷史資料分離

業務拆分其實是把一個混合的業務剝離成為更加清晰的獨立業務，這樣業務1，業務2。。。獨立的業務使得業務總量依舊很大，但是每個部分都是相對獨立的，可靠性依然有保證。

對於狀態和歷史資料分離，我可以舉一個例子來說明。

例如：我們有一張表Account，假設使用者餘額為100。

我們需要在發生資料變更後，能夠追溯資料變更的歷史資訊，如果對賬戶更新狀態資料，增加100的餘額，這樣餘額為200。

這個過程可能對應一條update語句，一條insert語句。

對此我們可以改造為兩個不同的資料來源，account和account_hist

在account_hist中就會是兩條insert記錄，如下:

而在account中則是一條update語句，如下：

這也是一種很基礎的冷熱分離，可以大大減少維護的複雜度，提高業務響應效率。

2）資料拆分

2.1 按照日期拆分，這種使用方式比較普遍，尤其是按照日期維度的拆分，其實在程式層面的改動很小，但是擴充套件性方面的收益很大。

• 資料按照日期維度拆分，如test_20191021

• 資料按照周月為維度拆分,如test_201910

• 資料按照季度，年維度拆分,如test_2019

2.2 採用分割槽模式，分割槽模式也是常見的使用方式，採用hash,range等方式會多一些，在MySQL中我是不大建議使用分割槽表的使用方式，因為隨著儲存容量的增長，資料雖然做了垂直拆分，但是歸根結底，資料其實難以實現水平擴充套件，在MySQL中是有更好的擴充套件方式。

2.3 讀多寫少優化場景

採用快取，採用Redis技術，將讀請求打在快取層面，這樣可以大大降低MySQL層面的熱點資料查詢壓力。

2.4 讀少寫多優化場景，可以採用三步走：

1) 採用非同步提交模式，非同步對於應用層來說最直觀的就是效能的提升，產生最少的同步等待。

2) 使用佇列技術，大量的寫請求可以通過佇列的方式來進行擴充套件，實現批量的資料寫入。

3) 降低寫入頻率，這個比較難理解，我舉個例子

對於業務資料，比如積分類，相比於金額來說業務優先順序略低的場景，如果資料的更新過於頻繁，可以適度調整資料更新的範圍（比如從原來的每分鐘調整為10分鐘）來減少更新的頻率。

例如：更新狀態資料，積分為200，如下圖所示

可以改造為，如下圖所示。

如果業務資料在短時間內更新過於頻繁，比如1分鐘更新100次，積分從100到10000，則可以根據時間頻率批量提交。

例如：更新狀態資料，積分為100，如下圖所示。

無需生成100個事務（200條SQL語句）可以改造為2條SQL語句，如下圖所示。

對於業務指標，比如更新頻率細節資訊，可以根據具體業務場景來討論決定。

優化設計方案3：架構層優化

架構層優化其實就是我們認為的那種技術含量很高的工作，我們需要根據業務場景在架構層面引入一些新的花樣來。

3.1.系統水平擴充套件場景

3.1.1採用中介軟體技術，可以實現資料路由，水平擴充套件，常見的中介軟體有MyCAT，ShardingSphere,ProxySQL等

3.1.2 採用讀寫分離技術，這是針對讀需求的擴充套件，更側重於狀態表，在允許一定延遲的情況下，可以採用多副本的模式實現讀需求的水平擴充套件，也可以採用中介軟體來實現，如MyCAT,ProxySQL,MaxScale,MySQL Router等

3.1.3 採用負載均衡技術，常見的有LVS技術或者基於域名服務的Consul技術等

3.2.兼顧OLTP+OLAP的業務場景，可以採用NewSQL，優先相容MySQL協議的HTAP技術棧，如TiDB

3.3.離線統計的業務場景，有幾類方案可供選擇。

3.3.1 採用NoSQL體系，主要有兩類，一類是適合相容MySQL協議的資料倉儲體系，常見的有Infobright或者ColumnStore，另外一類是基於列式儲存，屬於異構方向，如HBase技術

3.3.2 採用數倉體系，基於MPP架構,如使用Greenplum統計，如T+1統計

優化設計方案4：資料庫優化

資料庫優化，其實可打的牌也不少，但是相對來說空間沒有那麼大了，我們來逐個說一下。

4.1 事務優化

根據業務場景選擇事務模型，是否是強事務依賴

對於事務降維策略，我們來舉出幾個小例子來。

4.1.1 降維策略1：儲存過程呼叫轉換為透明的SQL呼叫

對於新業務而言，使用儲存過程顯然不是一個好主意，MySQL的儲存過程和其他商業資料庫相比，功能和效能都有待驗證，而且在目前輕量化的業務處理中，儲存過程的處理方式太“重”了。

有些應用架構看起來是按照分散式部署的，但在資料庫層的呼叫方式是基於儲存過程，因為儲存過程封裝了大量的邏輯，難以除錯，而且移植性不高，這樣業務邏輯和效能壓力都在資料庫層面了，使得資料庫層很容易成為瓶頸，而且難以實現真正的分散式。

所以有一個明確的改進方向就是對於儲存過程的改造，把它改造為SQL呼叫的方式，可以極大地提高業務的處理效率，在資料庫的介面呼叫上足夠簡單而且清晰可控。

4.1.2 降維策略2：DDL操作轉換為DML操作

有些業務經常會有一種緊急需求，總是需要給一個表新增欄位，搞得DBA和業務同學都挺累，可以想象一個表有上百個欄位，而且基本都是name1，name2……name100，這種設計本身就是有問題的，更不用考慮效能了。究其原因，是因為業務的需求動態變化，比如一個遊戲裝備有20個屬性，可能過了一個月之後就增加到了40個屬性，這樣一來，所有的裝備都有40個屬性，不管用沒用到，而且這種方式也存在諸多的冗餘。

我們在設計規範裡面也提到了一些設計的基本要素，在這些基礎上需要補充的是，保持有限的欄位，如果要實現這些功能的擴充套件，其實完全可以通過配置化的方式來實現，比如把一些動態新增的欄位轉換為一些配置資訊。配置資訊可以通過DML的方式進行修改和補充，對於資料入口也可以更加動態、易擴充套件。

4.1.3 降維策略3：Delete操作轉換為高效操作

有些業務需要定期來清理一些週期性資料，比如表裡的資料只保留一個月，那麼超出時間範圍的資料就要清理掉了，而如果表的量級比較大的情況下，這種Delete操作的代價實在太高，我們可以有兩類解決方案來把Delete操作轉換為更為高效的方式。 

第一種是根據業務建立週期表，比如按照月表、周表、日表等維度來設計，這樣資料的清理就是一個相對可控而且高效的方式了。 

第二種方案是使用MySQL rename的操作方式，比如一張2千萬的大表要清理99%的資料，那麼需要保留的1%的資料我們可以很快根據條件過濾補錄，實現“移形換位”。

4.2 SQL優化

其實相對來說需要的極簡的設計，很多點都在規範設計裡面了，如果遵守規範，八九不離十的問題都會杜絕掉，在此補充幾點：

4.2.1 SQL語句簡化，簡化是SQL優化的一大利器，因為簡單，所以優越。

4.2.2 儘可能避免或者杜絕多表複雜關聯，大表關聯是大表處理的噩夢，一旦開啟了這個口子，越來越多的需求需要關聯，效能優化就沒有回頭路了，更何況大表關聯是MySQL的弱項，儘管Hash Join才推出，不要像掌握了絕對大殺器一樣，在商業資料庫中早就存在，問題照樣層出不窮。

4.2.3 SQL中儘可能避免反連線，避免半連線，這是優化器做得薄弱的一方面，什麼是反連線，半連線？其實比較好理解，舉個例子，not in ,not exists就是反連線，in,exists就是半連線，在千萬級大表中出現這種問題，效能是幾個數量級的差異。 

4.3 索引優化

應該是大表優化中需要把握的一個度。

4.3.1 首先必須有主鍵，規範設計中第一條就是，此處不接收反駁。

4.3.2 其次，SQL查詢基於索引或者唯一性索引，使得查詢模型儘可能簡單。

4.3.3 最後，儘可能杜絕範圍資料的查詢，範圍掃描在千萬級大表情況下還是儘可能減少。

優化設計方案4：管理優化

這部分應該是在所有的解決方案中最容易被忽視的部分了，我放在最後，在此也向運維同事致敬，總是為很多認為本應該正常的問題盡職盡責（背鍋）。

千萬級大表的資料清理一般來說是比較耗時的，在此建議在設計中需要完善冷熱資料分離的策略，可能聽起來比較拗口，我來舉一個例子，把大表的Drop 操作轉換為可逆的DDL操作。

Drop操作是預設提交的，而且是不可逆的，在資料庫操作中都是跑路的代名詞，MySQL層面目前沒有相應的Drop操作恢復功能，除非通過備份來恢復，但是我們可以考慮將Drop操作轉換為一種可逆的DDL操作。

MySQL中預設每個表有一個對應的ibd檔案，其實可以把Drop操作轉換為一個rename操作，即把檔案從testdb遷移到testdb_arch下面；從許可權上來說，testdb_arch是業務不可見的，rename操作可以平滑的實現這個刪除功能，如果在一定時間後確認可以清理，則資料清理對於已有的業務流程是不可見的，如下圖所示。

此外，還有兩個額外建議，一個是對於大表變更，儘可能考慮低峰時段的線上變更，比如使用pt-osc工具或者是維護時段的變更，就不再贅述了。

最後總結一下，其實就是一句話：

千萬級大表的優化是根據業務場景，以成本為代價進行優化的，絕對不是孤立的一個層面的優化。

近期熱文：

個人新書 《MySQL DBA工作筆記》

個人公眾號：jianrong-notes

QQ群號：763628645

QQ群二維碼如下，個人微訊號：jeanron100, 新增請註明：姓名+地區+職位，否則不予通過