MySQL千萬級大表優化，看這一篇就忘不掉了

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

圖片來自 Pexels

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

既然要吃透這個問題，我們勢必要回到本源，我把這個問題分為三部分：“千萬級”，“大表”，“優化”，也分別對應我們在圖中標識的“數據量”，“對象”和“目標”。

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

數據量：千萬級

千萬級其實只是一個感官的數字，就是我們印象中的數據量大。

這裡我們需要把這個概念細化，因為隨著業務和時間的變化，數據量也會有變化，我們應該是帶著一種動態思維來審視這個指標，從而對於不同的場景我們應該有不同的處理策略。

①數據量為千萬級，可能達到億級或者更高

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

②數據量為千萬級，是一個相對穩定的數據量

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

③數據量為千萬級，不應該有這麼多的數據

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

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

對象：數據表

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

一般根據業務類型把數據分為三種：

①流水型數據

流水型數據是無狀態的，多筆業務之間沒有關聯，每次業務過來的時候都會產生新的單據。

比如交易流水、支付流水，只要能插入新單據就能完成業務，特點是後面的數據不依賴前面的數據，所有的數據按時間流水進入數據庫。

②狀態型數據

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

③配置型數據

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

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

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

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

目標：優化

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

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

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

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

• 規範設計
• 業務層優化
• 架構層優化
• 數據庫優化
• 管理優化

規範設計

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

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

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

規範的本質不是解決問題，而是有效杜絕一些潛在問題，對於千萬級大表要遵守的規範，我梳理瞭如下的一些細則，基本可以涵蓋我們常見的一些設計和使用問題。

比如表的字段設計不管三七二十一，都是 varchar(500)，其實是很不規範的一種實現方式，我們來展開說一下這幾個規範。

配置規範：

• MySQL 數據庫默認使用 InnoDB 存儲引擎。
• 保證字符集設置統一，MySQL 數據庫相關係統、數據庫、表的字符集都使用 UTF8，應用程序連接、展示等可以設置字符集的地方也都統一設置為 UTF8 字符集。
• 注：UTF8 格式是存儲不了表情類數據，需要使用 UTF8MB4，可在 MySQL 字符集裡面設置。在 8.0 中已經默認為 UTF8MB4，可以根據公司的業務情況進行統一或者定製化設置。
• MySQL 數據庫的事務隔離級別默認為 RR(Repeatable-Read)，建議初始化時統一設置為 RC(Read-Committed)，對於 OLTP 業務更適合。
• 數據庫中的表要合理規劃，控制單表數據量，對於 MySQL 數據庫來說，建議單表記錄數控制在 2000W 以內。
• MySQL 實例下，數據庫、表數量儘可能少;數據庫一般不超過 50 個，每個數據庫下，數據表數量一般不超過 500 個(包括分區表)。

建表規範：

• InnoDB 禁止使用外鍵約束，可以通過程序層面保證。
• 存儲精確浮點數必須使用 DECIMAL 替代 FLOAT 和 DOUBLE。
• 整型定義中無需定義顯示寬度，比如：使用 INT，而不是 INT(4)。
• 不建議使用 ENUM 類型，可使用 TINYINT 來代替。
• 儘可能不使用 TEXT、BLOB 類型，如果必須使用，建議將過大字段或是不常用的描述型較大字段拆分到其他表中;另外，禁止用數據庫存儲圖片或文件。
• 存儲年時使用 YEAR(4)，不使用 YEAR(2)。
• 建議字段定義為 NOT NULL。
• 建議 DBA 提供 SQL 審核工具，建表規範性需要通過審核工具審核後。

命名規範：

• 庫、表、字段全部採用小寫。
• 庫名、表名、字段名、索引名稱均使用小寫字母，並以“_”分割。
• 庫名、表名、字段名建議不超過 12 個字符。(庫名、表名、字段名支持最多 64 個字符，但為了統一規範、易於辨識以及減少傳輸量，統一不超過 12 字符)
• 庫名、表名、字段名見名知意，不需要添加註釋。

對於對象命名規範的一個簡要總結如下表所示，供參考：

命名列表

索引規範：

• 索引建議命名規則：idx_col1_col2[_colN]、uniq_col1_col2[_colN](如果字段過長建議採用縮寫)。
• 索引中的字段數建議不超過 5 個。
• 單張表的索引個數控制在 5 個以內。
• InnoDB 表一般都建議有主鍵列，尤其在高可用集群方案中是作為必須項的。
• 建立複合索引時，優先將選擇性高的字段放在前面。
• UPDATE、DELETE 語句需要根據 WHERE 條件添加索引。
• 不建議使用 % 前綴模糊查詢，例如 LIKE “%weibo”，無法用到索引，會導致全表掃描。
• 合理利用覆蓋索引，例如：SELECT email，uid FROM user_email WHERE uid=xx，如果 uid 不是主鍵，可以創建覆蓋索引 idx_uid_email(uid，email)來提高查詢效率。
• 避免在索引字段上使用函數，否則會導致查詢時索引失效。
• 確認索引是否需要變更時要聯繫 DBA。

應用規範：

• 避免使用存儲過程、觸發器、自定義函數等，容易將業務邏輯和DB耦合在一起，後期做分佈式方案時會成為瓶頸。
• 考慮使用 UNION ALL，減少使用 UNION，因為 UNION ALL 不去重，而少了排序操作，速度相對比 UNION 要快，如果沒有去重的需求，優先使用 UNION ALL。
• 考慮使用 limit N，少用 limit M，N，特別是大表或 M 比較大的時候。
• 減少或避免排序，如：group by 語句中如果不需要排序，可以增加 order by null。
• 統計表中記錄數時使用 COUNT(*)，而不是 COUNT(primary_key) 和 COUNT(1)。
• InnoDB 表避免使用 COUNT(*) 操作，計數統計實時要求較強可以使用 Memcache 或者 Redis，非實時統計可以使用單獨統計表，定時更新。
• 做字段變更操作(modify column/change column)的時候必須加上原有的註釋屬性，否則修改後，註釋會丟失。
• 使用 prepared statement 可以提高性能並且避免 SQL 注入。
• SQL 語句中 IN 包含的值不應過多。
• UPDATE、DELETE 語句一定要有明確的 WHERE 條件。
• WHERE 條件中的字段值需要符合該字段的數據類型，避免 MySQL 進行隱式類型轉化。
• SELECT、INSERT 語句必須顯式的指明字段名稱，禁止使用 SELECT * 或是 INSERT INTO table_name values()。
• INSERT 語句使用 batch 提交(INSERT INTO table_name VALUES(),(),()……)，values 的個數不應過多。

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

①業務拆分

業務拆分分為如下兩個方面：

• 將混合業務拆分為獨立業務
• 將狀態和歷史數據分離

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

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

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

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

這個過程可能對應一條 update 語句，一條 insert 語句。對此我們可以改造為兩個不同的數據源，account 和 account_hist。

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

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

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

②數據拆分

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

• 數據按照日期維度拆分，如 test_20191021。
• 數據按照周月為維度拆分，如 test_201910。
• 數據按照季度，年維度拆分，如 test_2019。

採用分區模式：分區模式也是常見的使用方式，採用 hash，range 等方式會多一些。

在 MySQL 中我是不大建議使用分區表的使用方式，因為隨著存儲容量的增長，數據雖然做了垂直拆分，但是歸根結底，數據其實難以實現水平擴展，在 MySQL 中是有更好的擴展方式。

③讀多寫少優化場景

採用緩存，採用 Redis 技術，將讀請求打在緩存層面，這樣可以大大降低 MySQL 層面的熱點數據查詢壓力。

④讀少寫多優化場景

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

• 採用異步提交模式，異步對於應用層來說最直觀的就是性能的提升，產生最少的同步等待。
• 使用隊列技術，大量的寫請求可以通過隊列的方式來進行擴展，實現批量的數據寫入。
• 降低寫入頻率，這個比較難理解，我舉個例子：

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

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

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

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

例如：更新狀態數據，積分為 100，如下圖所示：

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

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

架構層優化

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

①系統水平擴展場景

採用中間件技術：可以實現數據路由，水平擴展，常見的中間件有 MyCAT，ShardingSphere，ProxySQL 等。

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

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

②兼顧 OLTP+OLAP 的業務場景

可以採用 NewSQL，優先兼容 MySQL 協議的 HTAP 技術棧，如 TiDB。

③離線統計的業務場景

有幾類方案可供選擇：

• 採用 NoSQL 體系，主要有兩類，一類是適合兼容 MySQL 協議的數據倉庫體系，常見的有 Infobright 或者 ColumnStore，另外一類是基於列式存儲，屬於異構方向，如 HBase 技術。
• 採用數倉體系，基於 MPP 架構，如使用 Greenplum 統計，如 T+1 統計。

數據庫優化

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

①事務優化

根據業務場景選擇事務模型，是否是強事務依賴。對於事務降維策略，我們來舉出幾個小例子來。

降維策略 1：存儲過程調用轉換為透明的 SQL 調用

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

有些應用架構看起來是按照分佈式部署的，但在數據庫層的調用方式是基於存儲過程，因為存儲過程封裝了大量的邏輯，難以調試，而且移植性不高。

這樣業務邏輯和性能壓力都在數據庫層面了，使得數據庫層很容易成為瓶頸，而且難以實現真正的分佈式。

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

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

有些業務經常會有一種緊急需求，總是需要給一個表添加字段，搞得 DBA 和業務同學都挺累，可以想象一個表有上百個字段，而且基本都是 name1，name2……name100，這種設計本身就是有問題的，更不用考慮性能了。

究其原因，是因為業務的需求動態變化，比如一個遊戲裝備有 20 個屬性，可能過了一個月之後就增加到了 40 個屬性，這樣一來，所有的裝備都有 40 個屬性，不管用沒用到，而且這種方式也存在諸多的冗餘。

我們在設計規範裡面也提到了一些設計的基本要素，在這些基礎上需要補充的是，保持有限的字段，如果要實現這些功能的擴展，其實完全可以通過配置化的方式來實現，比如把一些動態添加的字段轉換為一些配置信息。

配置信息可以通過 DML 的方式進行修改和補充，對於數據入口也可以更加動態、易擴展。

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

有些業務需要定期來清理一些週期性數據，比如表裡的數據只保留一個月，那麼超出時間範圍的數據就要清理掉了。

而如果表的量級比較大的情況下，這種 Delete 操作的代價實在太高，我們可以有兩類解決方案來把 Delete 操作轉換為更為高效的方式。

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

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

②SQL 優化

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

在此補充幾點：

• SQL 語句簡化，簡化是 SQL 優化的一大利器，因為簡單，所以優越。
• 儘可能避免或者杜絕多表複雜關聯，大表關聯是大表處理的噩夢，一旦打開了這個口子，越來越多的需求需要關聯，性能優化就沒有回頭路了，更何況大表關聯是 MySQL 的弱項，儘管 Hash Join 才推出，不要像掌握了絕對大殺器一樣，在商業數據庫中早就存在，問題照樣層出不窮。
• SQL 中儘可能避免反連接，避免半連接，這是優化器做得薄弱的一方面，什麼是反連接，半連接?
• 其實比較好理解，舉個例子：not in，not exists 就是反連接，in，exists 就是半連接，在千萬級大表中出現這種問題，性能是幾個數量級的差異。

③索引優化

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

• 首先必須有主鍵，規範設計中第一條就是，此處不接收反駁。
• 其次，SQL 查詢基於索引或者唯一性索引，使得查詢模型儘可能簡單。
• 最後，儘可能杜絕範圍數據的查詢，範圍掃描在千萬級大表情況下還是儘可能減少。

管理優化

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

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

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

MySQL 中默認每個表有一個對應的 ibd 文件，其實可以把 Drop 操作轉換為一個 rename 操作，即把文件從 testdb 遷移到 testdb_arch 下面。

從權限上來說，testdb_arch 是業務不可見的，rename 操作可以平滑的實現這個刪除功能，如果在一定時間後確認可以清理，則數據清理對於已有的業務流程是不可見的，如下圖所示：

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

最後總結一下，其實就是一句話：千萬級大表的優化是根據業務場景，以成本為代價進行優化的，絕對不是孤立的一個層面的優化。

閱讀更多 51CTO 的文章

關鍵字: 大表 支付 數據庫