千萬(wàn)級(jí)大表如何優(yōu)化，這是一個(gè)很有技術(shù)含量的問(wèn)題，通常我們的直覺(jué)思維都會(huì)跳轉(zhuǎn)到拆分或者數(shù)據(jù)分區(qū)，在此我想做一些補(bǔ)充和梳理，想和大家做一些這方面的經(jīng)驗(yàn)總結(jié)，也歡迎大家提出建議。
圖片來(lái)自 Pexels
從一開(kāi)始腦海里火光四現(xiàn)，到不斷的自我批評(píng)，后來(lái)也參考了一些團(tuán)隊(duì)的經(jīng)驗(yàn)，我整理了下面的大綱內(nèi)容。
既然要吃透這個(gè)問(wèn)題，我們勢(shì)必要回到本源，我把這個(gè)問(wèn)題分為三部分：“千萬(wàn)級(jí)”，“大表”，“優(yōu)化”，也分別對(duì)應(yīng)我們?cè)趫D中標(biāo)識(shí)的“數(shù)據(jù)量”，“對(duì)象”和“目標(biāo)”。
我來(lái)逐步展開(kāi)說(shuō)明一下，從而給出一系列的解決方案。
數(shù)據(jù)量：千萬(wàn)級(jí)
千萬(wàn)級(jí)其實(shí)只是一個(gè)感官的數(shù)字，就是我們印象中的數(shù)據(jù)量大。
這里我們需要把這個(gè)概念細(xì)化，因?yàn)殡S著業(yè)務(wù)和時(shí)間的變化，數(shù)據(jù)量也會(huì)有變化，我們應(yīng)該是帶著一種動(dòng)態(tài)思維來(lái)審視這個(gè)指標(biāo)，從而對(duì)于不同的場(chǎng)景我們應(yīng)該有不同的處理策略。
①數(shù)據(jù)量為千萬(wàn)級(jí)，可能達(dá)到億級(jí)或者更高
通常是一些數(shù)據(jù)流水，日志記錄的業(yè)務(wù)，里面的數(shù)據(jù)隨著時(shí)間的增長(zhǎng)會(huì)逐步增多，超過(guò)千萬(wàn)門檻是很容易的一件事情。
②數(shù)據(jù)量為千萬(wàn)級(jí)，是一個(gè)相對(duì)穩(wěn)定的數(shù)據(jù)量
如果數(shù)據(jù)量相對(duì)穩(wěn)定，通常是在一些偏向于狀態(tài)的數(shù)據(jù)，比如有 1000 萬(wàn)用戶，那么這些用戶的信息在表中都有相應(yīng)的一行數(shù)據(jù)記錄，隨著業(yè)務(wù)的增長(zhǎng)，這個(gè)量級(jí)相對(duì)是比較穩(wěn)定的。
③數(shù)據(jù)量為千萬(wàn)級(jí)，不應(yīng)該有這么多的數(shù)據(jù)
這種情況是我們被動(dòng)發(fā)現(xiàn)的居多，通常發(fā)現(xiàn)的時(shí)候已經(jīng)晚了，比如你看到一個(gè)配置表，數(shù)據(jù)量上千萬(wàn);或者說(shuō)一些表里的數(shù)據(jù)已經(jīng)存儲(chǔ)了很久，99% 的數(shù)據(jù)都屬于過(guò)期數(shù)據(jù)或者垃圾數(shù)據(jù)。
數(shù)據(jù)量是一個(gè)整體的認(rèn)識(shí)，我們需要對(duì)數(shù)據(jù)做更近一層的理解，這就可以引出第二個(gè)部分的內(nèi)容。
對(duì)象：數(shù)據(jù)表
數(shù)據(jù)操作的過(guò)程就好比數(shù)據(jù)庫(kù)中存在著多條管道，這些管道中都流淌著要處理的數(shù)據(jù)，這些數(shù)據(jù)的用處和歸屬是不一樣的。
一般根據(jù)業(yè)務(wù)類型把數(shù)據(jù)分為三種：
①流水型數(shù)據(jù)
流水型數(shù)據(jù)是無(wú)狀態(tài)的，多筆業(yè)務(wù)之間沒(méi)有關(guān)聯(lián)，每次業(yè)務(wù)過(guò)來(lái)的時(shí)候都會(huì)產(chǎn)生新的單據(jù)。
比如交易流水、支付流水，只要能插入新單據(jù)就能完成業(yè)務(wù)，特點(diǎn)是后面的數(shù)據(jù)不依賴前面的數(shù)據(jù)，所有的數(shù)據(jù)按時(shí)間流水進(jìn)入數(shù)據(jù)庫(kù)。
②狀態(tài)型數(shù)據(jù)
狀態(tài)型數(shù)據(jù)是有狀態(tài)的，多筆業(yè)務(wù)之間依賴于有狀態(tài)的數(shù)據(jù)，而且要保證該數(shù)據(jù)的準(zhǔn)確性，比如充值時(shí)必須要拿到原來(lái)的余額，才能支付成功。
③配置型數(shù)據(jù)
此類型數(shù)據(jù)數(shù)據(jù)量較小，而且結(jié)構(gòu)簡(jiǎn)單，一般為靜態(tài)數(shù)據(jù)，變化頻率很低。
至此，我們可以對(duì)整體的背景有一個(gè)認(rèn)識(shí)了，如果要做優(yōu)化，其實(shí)要面對(duì)的是這樣的 3*3 的矩陣，如果要考慮表的讀寫比例(讀多寫少，讀少寫多...)，那么就會(huì)是 3*3*4=24 種，顯然做窮舉是不顯示的，而且也完全沒(méi)有必要，可以針對(duì)不同的數(shù)據(jù)存儲(chǔ)特性和業(yè)務(wù)特點(diǎn)來(lái)指定不同的業(yè)務(wù)策略。
對(duì)此我們采取抓住重點(diǎn)的方式，把常見(jiàn)的一些優(yōu)化思路梳理出來(lái)，尤其是里面的核心思想，也是我們整個(gè)優(yōu)化設(shè)計(jì)的一把尺子，而難度決定了我們做這件事情的動(dòng)力和風(fēng)險(xiǎn)。
而對(duì)于優(yōu)化方案，我想采用面向業(yè)務(wù)的維度來(lái)進(jìn)行闡述。
目標(biāo)：優(yōu)化
在這個(gè)階段，我們要說(shuō)優(yōu)化的方案了，總結(jié)的有點(diǎn)多，相對(duì)來(lái)說(shuō)是比較全了。整體分為五個(gè)部分：
其實(shí)我們通常所說(shuō)的分庫(kù)分表等方案只是其中的一小部分，如果展開(kāi)之后就比較豐富了。
不難理解，我們要支撐的表數(shù)據(jù)量是千萬(wàn)級(jí)別，相對(duì)來(lái)說(shuō)是比較大了，DBA 要維護(hù)的表肯定不止一張，如何能夠更好的管理，同時(shí)在業(yè)務(wù)發(fā)展中能夠支撐擴(kuò)展，同時(shí)保證性能，這是擺在我們面前的幾座大山。
我們分別來(lái)說(shuō)一下這五類改進(jìn)方案：
- 規(guī)范設(shè)計(jì)
- 業(yè)務(wù)層優(yōu)化
- 架構(gòu)層優(yōu)化
- 數(shù)據(jù)庫(kù)優(yōu)化
- 管理優(yōu)化
規(guī)范設(shè)計(jì)
在此我們先提到的是規(guī)范設(shè)計(jì)，而不是其他高大上的設(shè)計(jì)方案。
黑格爾說(shuō)：秩序是自由的第一條件。在分工協(xié)作的工作場(chǎng)景中尤其重要，否則團(tuán)隊(duì)之間互相牽制太多，問(wèn)題多多。
我想提到如下的幾個(gè)規(guī)范，其實(shí)只是屬于開(kāi)發(fā)規(guī)范的一部分內(nèi)容，可以作為參考。
規(guī)范的本質(zhì)不是解決問(wèn)題，而是有效杜絕一些潛在問(wèn)題，對(duì)于千萬(wàn)級(jí)大表要遵守的規(guī)范，我梳理了如下的一些細(xì)則，基本可以涵蓋我們常見(jiàn)的一些設(shè)計(jì)和使用問(wèn)題。
比如表的字段設(shè)計(jì)不管三七二十一，都是 varchar(500)，其實(shí)是很不規(guī)范的一種實(shí)現(xiàn)方式，我們來(lái)展開(kāi)說(shuō)一下這幾個(gè)規(guī)范。
配置規(guī)范：
- MySQL 數(shù)據(jù)庫(kù)默認(rèn)使用 InnoDB 存儲(chǔ)引擎。
- 保證字符集設(shè)置統(tǒng)一，MySQL 數(shù)據(jù)庫(kù)相關(guān)系統(tǒng)、數(shù)據(jù)庫(kù)、表的字符集都使用 UTF8，應(yīng)用程序連接、展示等可以設(shè)置字符集的地方也都統(tǒng)一設(shè)置為 UTF8 字符集。
- 注：UTF8 格式是存儲(chǔ)不了表情類數(shù)據(jù)，需要使用 UTF8MB4，可在 MySQL 字符集里面設(shè)置。在 8.0 中已經(jīng)默認(rèn)為 UTF8MB4，可以根據(jù)公司的業(yè)務(wù)情況進(jìn)行統(tǒng)一或者定制化設(shè)置。
- MySQL 數(shù)據(jù)庫(kù)的事務(wù)隔離級(jí)別默認(rèn)為 RR(Repeatable-Read)，建議初始化時(shí)統(tǒng)一設(shè)置為 RC(Read-Committed)，對(duì)于 OLTP 業(yè)務(wù)更適合。
- 數(shù)據(jù)庫(kù)中的表要合理規(guī)劃，控制單表數(shù)據(jù)量，對(duì)于 MySQL 數(shù)據(jù)庫(kù)來(lái)說(shuō)，建議單表記錄數(shù)控制在 2000W 以內(nèi)。
- MySQL 實(shí)例下，數(shù)據(jù)庫(kù)、表數(shù)量盡可能少;數(shù)據(jù)庫(kù)一般不超過(guò) 50 個(gè)，每個(gè)數(shù)據(jù)庫(kù)下，數(shù)據(jù)表數(shù)量一般不超過(guò) 500 個(gè)(包括分區(qū)表)。
建表規(guī)范：
- InnoDB 禁止使用外鍵約束，可以通過(guò)程序?qū)用姹ＷC。
- 存儲(chǔ)精確浮點(diǎn)數(shù)必須使用 DECIMAL 替代 FLOAT 和 DOUBLE。
- 整型定義中無(wú)需定義顯示寬度，比如：使用 INT，而不是 INT(4)。
- 不建議使用 ENUM 類型，可使用 TINYINT 來(lái)代替。
- 盡可能不使用 TEXT、BLOB 類型，如果必須使用，建議將過(guò)大字段或是不常用的描述型較大字段拆分到其他表中;另外，禁止用數(shù)據(jù)庫(kù)存儲(chǔ)圖片或文件。
- 存儲(chǔ)年時(shí)使用 YEAR(4)，不使用 YEAR(2)。
- 建議字段定義為 NOT NULL。
- 建議 DBA 提供 SQL 審核工具，建表規(guī)范性需要通過(guò)審核工具審核后。
命名規(guī)范：
- 庫(kù)、表、字段全部采用小寫。
- 庫(kù)名、表名、字段名、索引名稱均使用小寫字母，并以“_”分割。
- 庫(kù)名、表名、字段名建議不超過(guò) 12 個(gè)字符。(庫(kù)名、表名、字段名支持最多 64 個(gè)字符，但為了統(tǒng)一規(guī)范、易于辨識(shí)以及減少傳輸量，統(tǒng)一不超過(guò) 12 字符)
- 庫(kù)名、表名、字段名見(jiàn)名知意，不需要添加注釋。
對(duì)于對(duì)象命名規(guī)范的一個(gè)簡(jiǎn)要總結(jié)如下表所示，供參考：
命名列表
索引規(guī)范：
- 索引建議命名規(guī)則：idx_col1_col2[_colN]、uniq_col1_col2[_colN](如果字段過(guò)長(zhǎng)建議采用縮寫)。
- 索引中的字段數(shù)建議不超過(guò) 5 個(gè)。
- 單張表的索引個(gè)數(shù)控制在 5 個(gè)以內(nèi)。
- InnoDB 表一般都建議有主鍵列，尤其在高可用集群方案中是作為必須項(xiàng)的。
- 建立復(fù)合索引時(shí)，優(yōu)先將選擇性高的字段放在前面。
- UPDATE、DELETE 語(yǔ)句需要根據(jù) WHERE 條件添加索引。
- 不建議使用 % 前綴模糊查詢，例如 LIKE “%weibo”，無(wú)法用到索引，會(huì)導(dǎo)致全表掃描。
- 合理利用覆蓋索引，例如：SELECT email，uid FROM user_email WHERE uid=xx，如果 uid 不是主鍵，可以創(chuàng)建覆蓋索引 idx_uid_email(uid，email)來(lái)提高查詢效率。
- 避免在索引字段上使用函數(shù)，否則會(huì)導(dǎo)致查詢時(shí)索引失效。
- 確認(rèn)索引是否需要變更時(shí)要聯(lián)系 DBA。
應(yīng)用規(guī)范：
- 避免使用存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)等，容易將業(yè)務(wù)邏輯和DB耦合在一起，后期做分布式方案時(shí)會(huì)成為瓶頸。
- 考慮使用 UNION ALL，減少使用 UNION，因?yàn)?UNION ALL 不去重，而少了排序操作，速度相對(duì)比 UNION 要快，如果沒(méi)有去重的需求，優(yōu)先使用 UNION ALL。
- 考慮使用 limit N，少用 limit M，N，特別是大表或 M 比較大的時(shí)候。
- 減少或避免排序，如：group by 語(yǔ)句中如果不需要排序，可以增加 order by null。
- 統(tǒng)計(jì)表中記錄數(shù)時(shí)使用 COUNT(*)，而不是 COUNT(primary_key) 和 COUNT(1)。
- InnoDB 表避免使用 COUNT(*) 操作，計(jì)數(shù)統(tǒng)計(jì)實(shí)時(shí)要求較強(qiáng)可以使用 Memcache 或者 redis，非實(shí)時(shí)統(tǒng)計(jì)可以使用單獨(dú)統(tǒng)計(jì)表，定時(shí)更新。
- 做字段變更操作(modify column/change column)的時(shí)候必須加上原有的注釋屬性，否則修改后，注釋會(huì)丟失。
- 使用 prepared statement 可以提高性能并且避免 SQL 注入。
- SQL 語(yǔ)句中 IN 包含的值不應(yīng)過(guò)多。
- UPDATE、DELETE 語(yǔ)句一定要有明確的 WHERE 條件。
- WHERE 條件中的字段值需要符合該字段的數(shù)據(jù)類型，避免 MySQL 進(jìn)行隱式類型轉(zhuǎn)化。
- SELECT、INSERT 語(yǔ)句必須顯式的指明字段名稱，禁止使用 SELECT * 或是 INSERT INTO table_name values()。
- INSERT 語(yǔ)句使用 batch 提交(INSERT INTO table_name VALUES(),(),()……)，values 的個(gè)數(shù)不應(yīng)過(guò)多。
業(yè)務(wù)層優(yōu)化
業(yè)務(wù)層優(yōu)化應(yīng)該是收益最高的優(yōu)化方式了，而且對(duì)于業(yè)務(wù)層完全可見(jiàn)，主要有業(yè)務(wù)拆分，數(shù)據(jù)拆分和兩類常見(jiàn)的優(yōu)化場(chǎng)景(讀多寫少，讀少寫多)!
①業(yè)務(wù)拆分
業(yè)務(wù)拆分分為如下兩個(gè)方面：
- 將混合業(yè)務(wù)拆分為獨(dú)立業(yè)務(wù)
- 將狀態(tài)和歷史數(shù)據(jù)分離
業(yè)務(wù)拆分其實(shí)是把一個(gè)混合的業(yè)務(wù)剝離成為更加清晰的獨(dú)立業(yè)務(wù)，這樣業(yè)務(wù) 1，業(yè)務(wù) 2......獨(dú)立的業(yè)務(wù)使得業(yè)務(wù)總量依舊很大，但是每個(gè)部分都是相對(duì)獨(dú)立的，可靠性依然有保證。
對(duì)于狀態(tài)和歷史數(shù)據(jù)分離，我可以舉一個(gè)例子來(lái)說(shuō)明。
例如：我們有一張表 Account，假設(shè)用戶余額為 100。
我們需要在發(fā)生數(shù)據(jù)變更后，能夠追溯數(shù)據(jù)變更的歷史信息，如果對(duì)賬戶更新?tīng)顟B(tài)數(shù)據(jù)，增加 100 的余額，這樣余額為 200。
這個(gè)過(guò)程可能對(duì)應(yīng)一條 update 語(yǔ)句，一條 insert 語(yǔ)句。對(duì)此我們可以改造為兩個(gè)不同的數(shù)據(jù)源，account 和 account_hist。
在 account_hist 中就會(huì)是兩條 insert 記錄，如下：
而在 account 中則是一條 update 語(yǔ)句，如下：
這也是一種很基礎(chǔ)的冷熱分離，可以大大減少維護(hù)的復(fù)雜度，提高業(yè)務(wù)響應(yīng)效率。
②數(shù)據(jù)拆分
按照日期拆分：這種使用方式比較普遍，尤其是按照日期維度的拆分，其實(shí)在程序?qū)用娴母膭?dòng)很小，但是擴(kuò)展性方面的收益很大。
- 數(shù)據(jù)按照日期維度拆分，如 test_20191021。
- 數(shù)據(jù)按照周月為維度拆分，如 test_201910。
- 數(shù)據(jù)按照季度，年維度拆分，如 test_2019。
采用分區(qū)模式：分區(qū)模式也是常見(jiàn)的使用方式，采用 hash，range 等方式會(huì)多一些。
在 MySQL 中我是不大建議使用分區(qū)表的使用方式，因?yàn)殡S著存儲(chǔ)容量的增長(zhǎng)，數(shù)據(jù)雖然做了垂直拆分，但是歸根結(jié)底，數(shù)據(jù)其實(shí)難以實(shí)現(xiàn)水平擴(kuò)展，在 MySQL 中是有更好的擴(kuò)展方式。
③讀多寫少優(yōu)化場(chǎng)景
采用緩存，采用 Redis 技術(shù)，將讀請(qǐng)求打在緩存層面，這樣可以大大降低 MySQL 層面的熱點(diǎn)數(shù)據(jù)查詢壓力。
④讀少寫多優(yōu)化場(chǎng)景
讀少寫多優(yōu)化場(chǎng)景，可以采用三步走：
- 采用異步提交模式，異步對(duì)于應(yīng)用層來(lái)說(shuō)最直觀的就是性能的提升，產(chǎn)生最少的同步等待。
- 使用隊(duì)列技術(shù)，大量的寫請(qǐng)求可以通過(guò)隊(duì)列的方式來(lái)進(jìn)行擴(kuò)展，實(shí)現(xiàn)批量的數(shù)據(jù)寫入。
- 降低寫入頻率，這個(gè)比較難理解，我舉個(gè)例子：
對(duì)于業(yè)務(wù)數(shù)據(jù)，比如積分類，相比于金額來(lái)說(shuō)業(yè)務(wù)優(yōu)先級(jí)略低的場(chǎng)景，如果數(shù)據(jù)的更新過(guò)于頻繁，可以適度調(diào)整數(shù)據(jù)更新的范圍(比如從原來(lái)的每分鐘調(diào)整為 10 分鐘)來(lái)減少更新的頻率。
例如：更新?tīng)顟B(tài)數(shù)據(jù)，積分為 200，如下圖所示：
可以改造為，如下圖所示：
如果業(yè)務(wù)數(shù)據(jù)在短時(shí)間內(nèi)更新過(guò)于頻繁，比如 1 分鐘更新 100 次，積分從 100 到 10000，則可以根據(jù)時(shí)間頻率批量提交。
例如：更新?tīng)顟B(tài)數(shù)據(jù)，積分為 100，如下圖所示：
無(wú)需生成 100 個(gè)事務(wù)(200 條 SQL 語(yǔ)句)可以改造為 2 條 SQL 語(yǔ)句，如下圖所示：
對(duì)于業(yè)務(wù)指標(biāo)，比如更新頻率細(xì)節(jié)信息，可以根據(jù)具體業(yè)務(wù)場(chǎng)景來(lái)討論決定。
架構(gòu)層優(yōu)化
架構(gòu)層優(yōu)化其實(shí)就是我們認(rèn)為的那種技術(shù)含量很高的工作，我們需要根據(jù)業(yè)務(wù)場(chǎng)景在架構(gòu)層面引入一些新的花樣來(lái)。
①系統(tǒng)水平擴(kuò)展場(chǎng)景
采用中間件技術(shù)：可以實(shí)現(xiàn)數(shù)據(jù)路由，水平擴(kuò)展，常見(jiàn)的中間件有 MyCAT，ShardingSphere，ProxySQL 等。
采用讀寫分離技術(shù)：這是針對(duì)讀需求的擴(kuò)展，更側(cè)重于狀態(tài)表，在允許一定延遲的情況下，可以采用多副本的模式實(shí)現(xiàn)讀需求的水平擴(kuò)展，也可以采用中間件來(lái)實(shí)現(xiàn)，如 MyCAT，ProxySQL，MaxScale，MySQL Router 等。
采用負(fù)載均衡技術(shù)：常見(jiàn)的有 LVS 技術(shù)或者基于域名服務(wù)的 Consul 技術(shù)等。
②兼顧 OLTP+OLAP 的業(yè)務(wù)場(chǎng)景
可以采用 NewSQL，優(yōu)先兼容 MySQL 協(xié)議的 HTAP 技術(shù)棧，如 TiDB。
③離線統(tǒng)計(jì)的業(yè)務(wù)場(chǎng)景
有幾類方案可供選擇：
- 采用 NoSQL 體系，主要有兩類，一類是適合兼容 MySQL 協(xié)議的數(shù)據(jù)倉(cāng)庫(kù)體系，常見(jiàn)的有 Infobright 或者 ColumnStore，另外一類是基于列式存儲(chǔ)，屬于異構(gòu)方向，如 HBase 技術(shù)。
- 采用數(shù)倉(cāng)體系，基于 MPP 架構(gòu)，如使用 Greenplum 統(tǒng)計(jì)，如 T+1 統(tǒng)計(jì)。
數(shù)據(jù)庫(kù)優(yōu)化
數(shù)據(jù)庫(kù)優(yōu)化，其實(shí)可打的牌也不少，但是相對(duì)來(lái)說(shuō)空間沒(méi)有那么大了，我們來(lái)逐個(gè)說(shuō)一下。
①事務(wù)優(yōu)化
根據(jù)業(yè)務(wù)場(chǎng)景選擇事務(wù)模型，是否是強(qiáng)事務(wù)依賴。對(duì)于事務(wù)降維策略，我們來(lái)舉出幾個(gè)小例子來(lái)。
降維策略 1：存儲(chǔ)過(guò)程調(diào)用轉(zhuǎn)換為透明的 SQL 調(diào)用
對(duì)于新業(yè)務(wù)而言，使用存儲(chǔ)過(guò)程顯然不是一個(gè)好主意，MySQL 的存儲(chǔ)過(guò)程和其他商業(yè)數(shù)據(jù)庫(kù)相比，功能和性能都有待驗(yàn)證，而且在目前輕量化的業(yè)務(wù)處理中，存儲(chǔ)過(guò)程的處理方式太“重”了。
有些應(yīng)用架構(gòu)看起來(lái)是按照分布式部署的，但在數(shù)據(jù)庫(kù)層的調(diào)用方式是基于存儲(chǔ)過(guò)程，因?yàn)榇鎯?chǔ)過(guò)程封裝了大量的邏輯，難以調(diào)試，而且移植性不高。
這樣業(yè)務(wù)邏輯和性能壓力都在數(shù)據(jù)庫(kù)層面了，使得數(shù)據(jù)庫(kù)層很容易成為瓶頸，而且難以實(shí)現(xiàn)真正的分布式。
所以有一個(gè)明確的改進(jìn)方向就是對(duì)于存儲(chǔ)過(guò)程的改造，把它改造為 SQL 調(diào)用的方式，可以極大地提高業(yè)務(wù)的處理效率，在數(shù)據(jù)庫(kù)的接口調(diào)用上足夠簡(jiǎn)單而且清晰可控。
降維策略 2：DDL 操作轉(zhuǎn)換為 DML 操作
有些業(yè)務(wù)經(jīng)常會(huì)有一種緊急需求，總是需要給一個(gè)表添加字段，搞得 DBA 和業(yè)務(wù)同學(xué)都挺累，可以想象一個(gè)表有上百個(gè)字段，而且基本都是 name1，name2……name100，這種設(shè)計(jì)本身就是有問(wèn)題的，更不用考慮性能了。
究其原因，是因?yàn)闃I(yè)務(wù)的需求動(dòng)態(tài)變化，比如一個(gè)游戲裝備有 20 個(gè)屬性，可能過(guò)了一個(gè)月之后就增加到了 40 個(gè)屬性，這樣一來(lái)，所有的裝備都有 40 個(gè)屬性，不管用沒(méi)用到，而且這種方式也存在諸多的冗余。
我們?cè)谠O(shè)計(jì)規(guī)范里面也提到了一些設(shè)計(jì)的基本要素，在這些基礎(chǔ)上需要補(bǔ)充的是，保持有限的字段，如果要實(shí)現(xiàn)這些功能的擴(kuò)展，其實(shí)完全可以通過(guò)配置化的方式來(lái)實(shí)現(xiàn)，比如把一些動(dòng)態(tài)添加的字段轉(zhuǎn)換為一些配置信息。
配置信息可以通過(guò) DML 的方式進(jìn)行修改和補(bǔ)充，對(duì)于數(shù)據(jù)入口也可以更加動(dòng)態(tài)、易擴(kuò)展。
降維策略 3：Delete 操作轉(zhuǎn)換為高效操作
有些業(yè)務(wù)需要定期來(lái)清理一些周期性數(shù)據(jù)，比如表里的數(shù)據(jù)只保留一個(gè)月，那么超出時(shí)間范圍的數(shù)據(jù)就要清理掉了。
而如果表的量級(jí)比較大的情況下，這種 Delete 操作的代價(jià)實(shí)在太高，我們可以有兩類解決方案來(lái)把 Delete 操作轉(zhuǎn)換為更為高效的方式。
第一種是根據(jù)業(yè)務(wù)建立周期表，比如按照月表、周表、日表等維度來(lái)設(shè)計(jì)，這樣數(shù)據(jù)的清理就是一個(gè)相對(duì)可控而且高效的方式了。
第二種方案是使用 MySQL rename 的操作方式，比如一張 2 千萬(wàn)的大表要清理 99% 的數(shù)據(jù)，那么需要保留的 1% 的數(shù)據(jù)我們可以很快根據(jù)條件過(guò)濾補(bǔ)錄，實(shí)現(xiàn)“移形換位”。
②SQL 優(yōu)化
其實(shí)相對(duì)來(lái)說(shuō)需要的極簡(jiǎn)的設(shè)計(jì)，很多點(diǎn)都在規(guī)范設(shè)計(jì)里面了，如果遵守規(guī)范，八九不離十的問(wèn)題都會(huì)杜絕掉。
在此補(bǔ)充幾點(diǎn)：
- SQL 語(yǔ)句簡(jiǎn)化，簡(jiǎn)化是 SQL 優(yōu)化的一大利器，因?yàn)楹?jiǎn)單，所以優(yōu)越。
- 盡可能避免或者杜絕多表復(fù)雜關(guān)聯(lián)，大表關(guān)聯(lián)是大表處理的噩夢(mèng)，一旦打開(kāi)了這個(gè)口子，越來(lái)越多的需求需要關(guān)聯(lián)，性能優(yōu)化就沒(méi)有回頭路了，更何況大表關(guān)聯(lián)是 MySQL 的弱項(xiàng)，盡管 Hash Join 才推出，不要像掌握了絕對(duì)大殺器一樣，在商業(yè)數(shù)據(jù)庫(kù)中早就存在，問(wèn)題照樣層出不窮。
- SQL 中盡可能避免反連接，避免半連接，這是優(yōu)化器做得薄弱的一方面，什么是反連接，半連接?
- 其實(shí)比較好理解，舉個(gè)例子：not in，not exists 就是反連接，in，exists 就是半連接，在千萬(wàn)級(jí)大表中出現(xiàn)這種問(wèn)題，性能是幾個(gè)數(shù)量級(jí)的差異。
③索引優(yōu)化
應(yīng)該是大表優(yōu)化中需要把握的一個(gè)度：
- 首先必須有主鍵，規(guī)范設(shè)計(jì)中第一條就是，此處不接收反駁。
- 其次，SQL 查詢基于索引或者唯一性索引，使得查詢模型盡可能簡(jiǎn)單。
- 最后，盡可能杜絕范圍數(shù)據(jù)的查詢，范圍掃描在千萬(wàn)級(jí)大表情況下還是盡可能減少。
管理優(yōu)化
這部分應(yīng)該是在所有的解決方案中最容易被忽視的部分了，我放在最后，在此也向運(yùn)維同事致敬，總是為很多認(rèn)為本應(yīng)該正常的問(wèn)題盡職盡責(zé)(背鍋)。
千萬(wàn)級(jí)大表的數(shù)據(jù)清理一般來(lái)說(shuō)是比較耗時(shí)的，在此建議在設(shè)計(jì)中需要完善冷熱數(shù)據(jù)分離的策略，可能聽(tīng)起來(lái)比較拗口，我來(lái)舉一個(gè)例子，把大表的 Drop 操作轉(zhuǎn)換為可逆的 DDL 操作。
Drop 操作是默認(rèn)提交的，而且是不可逆的，在數(shù)據(jù)庫(kù)操作中都是跑路的代名詞，MySQL 層面目前沒(méi)有相應(yīng)的 Drop 操作恢復(fù)功能，除非通過(guò)備份來(lái)恢復(fù)，但是我們可以考慮將 Drop 操作轉(zhuǎn)換為一種可逆的 DDL 操作。
MySQL 中默認(rèn)每個(gè)表有一個(gè)對(duì)應(yīng)的 ibd 文件，其實(shí)可以把 Drop 操作轉(zhuǎn)換為一個(gè) rename 操作，即把文件從 testdb 遷移到 testdb_arch 下面。
從權(quán)限上來(lái)說(shuō)，testdb_arch 是業(yè)務(wù)不可見(jiàn)的，rename 操作可以平滑的實(shí)現(xiàn)這個(gè)刪除功能，如果在一定時(shí)間后確認(rèn)可以清理，則數(shù)據(jù)清理對(duì)于已有的業(yè)務(wù)流程是不可見(jiàn)的，如下圖所示：
此外，還有兩個(gè)額外建議，一個(gè)是對(duì)于大表變更，盡可能考慮低峰時(shí)段的在線變更，比如使用 pt-osc 工具或者是維護(hù)時(shí)段的變更，就不再贅述了。
最后總結(jié)一下，其實(shí)就是一句話：千萬(wàn)級(jí)大表的優(yōu)化是根據(jù)業(yè)務(wù)場(chǎng)景，以成本為代價(jià)進(jìn)行優(yōu)化的，絕對(duì)不是孤立的一個(gè)層面的優(yōu)化。
