職業(yè)生涯中，曾經(jīng)有兩年多時(shí)間從事IT內(nèi)部審計(jì)的工作，需要介入公司大部分的業(yè)務(wù)系統(tǒng)，主要的數(shù)據(jù)庫(kù)為 MySQL，特別是三年期審計(jì)，需要統(tǒng)計(jì)三年的數(shù)據(jù)素材（億級(jí)以上）。對(duì)MySQL的性能有一些自已的理解，
一般對(duì)外的高負(fù)載的系統(tǒng)為了提升性能，極力規(guī)避MySQL直接面對(duì)用戶，在用戶和MySQL之間，有一層又一層的緩存，如果流量大量打到了MySQL上，我們稱為緩存穿透，系統(tǒng)很容易就掛掉了，即使在緩存保護(hù)下的應(yīng)用，也會(huì)極力限制復(fù)雜的 SQL查詢，鮮少有 JOIN, GEOUP BY，而在內(nèi)部系統(tǒng)中，因?yàn)橛脩粢?guī)?？煽?。MySQL的各種極致用法都可能出現(xiàn)，我們計(jì)論MySQL性能，一般就是內(nèi)部系統(tǒng)這種場(chǎng)景下。
IT 審計(jì)需要跟公司內(nèi)部大部分的系統(tǒng)打交道，公司有上百個(gè)系統(tǒng)，很多是遺留系統(tǒng)，各種各樣的架構(gòu)設(shè)計(jì)，程序員在這個(gè)領(lǐng)域是超級(jí)個(gè)性化的，同一個(gè)系統(tǒng)功能，換一個(gè)人，就可能是完全不同的設(shè)計(jì)，某個(gè)系統(tǒng)中可能無差別全是分區(qū)表；另一個(gè)系統(tǒng)中單表無分區(qū)競(jìng)有兩億多多條數(shù)據(jù)，且運(yùn)行穩(wěn)定 ......
我對(duì) MySQL數(shù)據(jù)量極限的理解
很多人認(rèn)為 MySQL適合千萬級(jí)以下的數(shù)據(jù)，或者其它量級(jí)，他們的結(jié)論大致是正確的，因?yàn)槭撬麄兊膽?yīng)用場(chǎng)景各不相同。
我將MySQL數(shù)據(jù)量極限劃分為以下情況：
A 不分區(qū)
不分區(qū)時(shí)，性能主要影響因素就是：索引，MySQL表至少有一個(gè)主鍵索引，即使你沒建，MySQL也會(huì)隱藏的創(chuàng)建一個(gè)自增ID
A 01 只有一個(gè)主鍵索引（億級(jí)以上完全沒有問題）
寫入數(shù)據(jù)時(shí)數(shù)據(jù)量對(duì)寫入性能影響比較小。僅當(dāng)主鍵這個(gè)索引需要做二叉樹平衡時(shí)，產(chǎn)生額外IO操作，大部分情況下，和空表插入性能無異。
超級(jí)大表讀取數(shù)據(jù)時(shí)，只能用兩種方式，1. 按主鍵讀取，2 不排序或按主鍵排序 取前多少個(gè)。 其它方式不要想了（抓狂：幾個(gè)小時(shí)無返回，show processlist 一直有這個(gè)進(jìn)程，只能 kill 掉），
A 02 多個(gè)索引（極限數(shù)據(jù)量幾千萬）。
寫入數(shù)據(jù)時(shí)，索引越多，索引需要平衡二叉樹產(chǎn)生重建操作機(jī)率越大。性能下降越厲害，索引個(gè)數(shù)，類型，長(zhǎng)度，行數(shù)據(jù)大小等影響很大，這個(gè)極限沒有確定的值。在一個(gè)業(yè)務(wù)系統(tǒng)中，某個(gè)核心業(yè)務(wù)表，100多個(gè)字段，近 30 個(gè)索引，1000 萬+數(shù)據(jù)，還能硬撐，不過MySQL服務(wù)器內(nèi)存被迫一步步升到了 256G
讀取數(shù)據(jù)時(shí)，只有一條路，一定要走索引，盡可能走唯一索引，盡可要走更精準(zhǔn)的索引（基數(shù)更大的），LIKE 也可以用（但是一定是索引字段， 而且是 LIKE ’***%‘，不能是 %開頭的），聯(lián)合索引只用到了部分字段也可，但必須是按順序最前的（如 A+B+C三個(gè)字段的聯(lián)合索引，WHERE可以只有A字段，或 A AND B 字段，或三個(gè)都有），GROUP BY 也可以用，稍慢點(diǎn)，但一定是 GROUP BY 索引， WHERE條件要走索引，在千萬級(jí)的 業(yè)務(wù)系統(tǒng)中，發(fā)現(xiàn)有GROUP BY 嵌套 （GROUP BY 的結(jié)果作為臨時(shí)表再次GROUP BY ），效率也可接受。
B 分區(qū)表
分區(qū)表將數(shù)據(jù)分成區(qū)塊存儲(chǔ)，可存儲(chǔ)到不同磁盤。最多 1024 個(gè)分區(qū)。和分表性能相當(dāng)。每個(gè)分區(qū)可以認(rèn)為和不分區(qū)單表的限制基本一致，如果單表穩(wěn)定處理1000萬復(fù)雜的業(yè)務(wù)數(shù)據(jù)，那1024個(gè)分區(qū)理論極限可以穩(wěn)定處理 100 億條數(shù)據(jù)
但分區(qū)表最大缺點(diǎn)：操作必須限定在一個(gè)分區(qū)內(nèi)，即 WHERE 查詢條件中 一定要包含分區(qū)字段，這極大地限制了分區(qū)表的應(yīng)用，如果你不小心漏掉了，呵呵，去 KILL 進(jìn)程吧。
數(shù)據(jù)遷移：
做IT審計(jì)，首先面臨的問題是將數(shù)據(jù)定期從業(yè)務(wù)系統(tǒng)復(fù)制到內(nèi)審系統(tǒng)中。主要因?yàn)椋?/p>
業(yè)務(wù)系統(tǒng)有分區(qū)或分表，歷史數(shù)據(jù)也可能封存到別處。
系統(tǒng)隔離，審計(jì)操作不影響業(yè)務(wù)系統(tǒng)，
數(shù)據(jù)需要清洗，加工，拆分或聚合。
早期嘗試了 現(xiàn)成 ETL工具 的方案，主要是 kattle，效率還可以（每秒能處理上萬條），主要障礙在數(shù)據(jù)清洗操作。比如訂單中有? A+B 這樣的組合商品，（我們稱 A+B 為商品編碼，其中 A, B 為 SKU，商品編碼格式?jīng)]有限制，在基礎(chǔ)資料系統(tǒng)中和SKU關(guān)聯(lián)起來即可），表示 兩個(gè)東西捆綁銷售。比如手持風(fēng)扇加個(gè)18650電池。清洗邏輯很多，比如有 將商品編碼打散成 SKU, 調(diào)用基礎(chǔ)資料服務(wù)獲取商品編碼與SKU關(guān)系， 調(diào)用成本服務(wù)獲取各SKU成本價(jià)，按成本價(jià)比例分?jǐn)偸蹆r(jià)。katttle? 不擅長(zhǎng)做這種復(fù)雜操作，實(shí)現(xiàn)起來很麻煩，且無法自動(dòng)化部署，無版本控制。不易測(cè)試，最終決定自已開發(fā)遷移程序。
清洗后入庫(kù)時(shí)對(duì)數(shù)據(jù)插入及更新的 SQL 處理：
1 SQL預(yù)編譯后再批量執(zhí)行
首先 編寫占位符的 SQL
INSERT INTO table(a, b, c ... ) VALUE(?, ?, ? , )
預(yù)編譯后批量注入清洗過數(shù)據(jù)，不同語(yǔ)言和服務(wù)器配置有些差距，但都能很輕松實(shí)現(xiàn) 1萬條以上/秒的速度，包含清洗邏輯（清洗邏輯經(jīng)過了極度優(yōu)化，無處不緩存，耗時(shí)影響比較小）。
2 拼接SQL批量執(zhí)行
INSERT INTO table(a, b, c ... ) VALIES
('1,' '2', '3' , ),
('1,' '2', '3' , ),
('1,' '2', '3' , ),
......
VALUES 后邊大量數(shù)據(jù)，我們一般用 5000 行數(shù)據(jù)拼成一個(gè) SQL，注意數(shù)據(jù)需要主動(dòng)做 escape 處理。這樣的SQL一秒鐘能執(zhí)行5~6 條或更多，跟字段量多少有關(guān)，相當(dāng)于一秒處理 3 萬條以上的數(shù)據(jù)，1分鐘處理 200 萬，5分鐘處理 1000 萬，
但這并不是極限，我們發(fā)現(xiàn) 瓶頸 不在 MySQL身上，而是 網(wǎng)絡(luò)帶寬和磁盤的 IO寫入速度首先達(dá)到了峰值。一旦我們的程序長(zhǎng)時(shí)間啟動(dòng)，用不了多久，運(yùn)維部門的同事就來關(guān)照我們了，以至于我們不得不限制下執(zhí)行時(shí)段或速度。
不確定是插入還是更新情況的處理
REPLACE INTO? table(a, b, c ... ) VALIES
('1,' '2', '3' , ),
('1,' '2', '3' , ),
('1,' '2', '3' , ),
......
MySQL 提供了獨(dú)有的 REPLACE INTO, 和? INSERT INTO 語(yǔ)法一致，數(shù)據(jù)存在時(shí)更新，不存在時(shí)插入，和 ON DUMPLICATE KEY UPDATE 功能類似，但更方便。REPLACE INTO? 操作的表一定要有有唯一鍵，如果沒有，就是單純的 INSERT INTO 了.
更新也可以幾千條批量處理
UPDATE table SET a = CASE
? ? WHEN id=1 THEN 'A1'
? ? WHEN id=2 THEN 'A2'
? ? WHEN id=3 THEN 'A3'?
? ? .....
END
批量更新相比 批量 INSERT INTO 和 REPLACE INTO 差很多，但也比單條更新快很多倍
至此，各業(yè)務(wù)系統(tǒng)的數(shù)據(jù)經(jīng)過清洗，完整部署在了內(nèi)審系統(tǒng)中，年度數(shù)據(jù)5000萬行左右，三年 1.5億， 因?yàn)榍逑催^的數(shù)據(jù)更有價(jià)值，這些數(shù)據(jù)被多方引用，如 Tableau 報(bào)表，財(cái)務(wù)部門，同時(shí)也有同步到 ES 和 ClickHouse 中做不同用途的運(yùn)算。
數(shù)據(jù)應(yīng)用：
外部審計(jì)公司進(jìn)場(chǎng)后，會(huì)要求我們提供某些數(shù)據(jù)，各種維度的， 如：提供一年期前10銷量國(guó)家的復(fù)購(gòu)次數(shù)最多的前 100個(gè)用戶的訂單數(shù)據(jù)。這些需求都是一次性的，在指定時(shí)間段內(nèi)提供即可，大部分需求直接編寫SQL即可實(shí)現(xiàn)。編寫出來的 SQL 一般都包含兩層或三層 GROUP BY 嵌套， 或者幾個(gè) GROUP BY 進(jìn)行 UNION ALL 后再次 GROUP BY。
為了提升效率，我們的數(shù)據(jù)冗余了兩張表，一張不分區(qū)，一張按月份分區(qū)，數(shù)據(jù)完全一致，
如果需求是月度數(shù)據(jù)（查詢量幾百萬條），優(yōu)先使用分區(qū)表，基本是秒級(jí)的查詢。
如果需求是幾個(gè)月的數(shù)數(shù)（查詢量幾千萬條條），優(yōu)先使用分區(qū)表，幾個(gè)月的的分段成單月 SQL 再 UNION ALL 后再聚合，基本是兩三秒的時(shí)間。
如果更長(zhǎng)維度的話，沒辦法使用分區(qū)表，在總表下查詢，要一分鐘以上，某些復(fù)雜的查詢可能出不來，這時(shí)候要去 ClickHouse 了（ DBeaver 客戶端）。又找到了秒級(jí)的感覺。
ClickHouse 幾乎完全兼容 SQL 語(yǔ)句，只有部分 SQL 函數(shù)（如日期）需要特殊處理下，效率相當(dāng)給力。沒有以 ClickHouse 為中心是因?yàn)?MySQL 是基礎(chǔ)應(yīng)用，所有系統(tǒng)都是圍續(xù) MySQL 構(gòu)建的，而 ClickHouse 主要適用于數(shù)據(jù)分析的場(chǎng)景，適合整表寫入，數(shù)據(jù)更新效率很低，因此它的數(shù)據(jù)更新頻率很低，不論數(shù)據(jù)量有多少，它的更新就是刪表，然后整表導(dǎo)入，反而出奇的快。
本文原始網(wǎng)址：https://www.liu12.com/article/mysql-100-million，轉(zhuǎn)載請(qǐng)保留出處
