[[438008]]

本文轉(zhuǎn)載自微信公眾號「程序新視界」，作者二師兄。轉(zhuǎn)載本文請聯(lián)系程序新視界公眾號。

背景

項目中有一張歷史記錄表，主要用于記錄一些接口調(diào)用流水，因為該表的地位不是那么重要，當初的創(chuàng)建者并未對核心字段創(chuàng)建索引。

不知不覺這張表的數(shù)據(jù)已經(jīng)有5千萬數(shù)據(jù)了，由于沒有索引，在排查問題時，發(fā)現(xiàn)這種表根本查不動。

于是，決定下手進行分表并建立索引。這張表在系統(tǒng)中只負責插入，影響范圍極小，正好拿來練手。

解決思路

我們知道，在Mysql 5.5及之前版本，在運行的生成環(huán)境中對大表執(zhí)行alter操作，會引起表的重建和鎖表，影響業(yè)務(wù)正常運轉(zhuǎn)。

從Mysql 5.6開始，Online DDL特性被引進，運行alter操作時同時允許運行select、insert、update、delete語句。

在數(shù)據(jù)量小于100w時，可以考慮直接修改表結(jié)構(gòu)建立索引，正常幾秒鐘就可以完成。但當表的數(shù)據(jù)量超過百萬，無論Mysql 5.6及之前版本的鎖表行為、Mysql 5.6中因慢SQL引起的等待，都不允許直接在生產(chǎn)庫中進行alter table操作。

目前，五千萬的數(shù)據(jù)，直接修改表來建立索引，肯定是不可行的，弄不好還把數(shù)據(jù)庫給弄崩了。只能想另外的方法。

解決方案

鑒于這張表本身的影響范圍有限，想到的解決方案就分表。無論是將所有數(shù)據(jù)一個區(qū)間一個區(qū)間的拆分出去，還是將整個表都換成新表，然后再處理歷史數(shù)據(jù)，基本上都要做拆分處理。

基本解決思路：

• 第一步：創(chuàng)建一張數(shù)據(jù)結(jié)構(gòu)一樣的新表(補全索引)，將業(yè)務(wù)切換至新表，這樣新生成的數(shù)據(jù)便有了索引;
• 第二步：對舊表數(shù)據(jù)進行備份，已被后續(xù)處理過程中有問題進行恢復;
• 第三步：按照數(shù)據(jù)ID，1000萬條數(shù)據(jù)拆分一個表，新拆分的表(補全索引);

對于分表的數(shù)據(jù)，數(shù)據(jù)庫訪問層并未使用，如果業(yè)務(wù)中有其他地方使用，則可考慮在數(shù)據(jù)庫訪問層根據(jù)請求時間區(qū)間或ID等來切換數(shù)據(jù)庫表名。

基本操作

備份數(shù)據(jù)

數(shù)據(jù)庫基于阿里云的云服務(wù)，導出數(shù)據(jù)有多種方式，比如直接copy出一張表、基于Navicat導出、基于mysqldump導出等。

copy出一張新表語句如下：

create table account_log_1 select * from account_log; 

在測試環(huán)境上驗證了一下，粗略估計該方式得1小時左右才能執(zhí)行完數(shù)據(jù)的備份。

由于沒有安裝Mysql的linux生產(chǎn)服務(wù)器可用，就沒采用mysqldump方式導致。

最終，采用在堡壘機上通過Navicat的導出功能，導出內(nèi)容為SQL語句。

結(jié)果也很令人失望，5千萬的數(shù)據(jù)：導出耗時1小時22分鐘，導出SQL語句磁盤空間占用38.5G。還好在導出過程中，通過監(jiān)控查看數(shù)據(jù)庫的整體性能還比較平穩(wěn)。

為了節(jié)省堡壘機的磁盤空間，又花費了十多分鐘將38.5G的數(shù)據(jù)進行壓縮，最終占用3.8G的存儲空間。

Navicat與mysqldump性能對比

Navicat導出的數(shù)據(jù)是一條條的insert語句，每一行一條插入語句。

mysqldump導出的數(shù)據(jù)，多行數(shù)據(jù)合并成一行插入。批插入減少SQL語法詞法解析，減少插事務(wù)(最大的開銷)，較少數(shù)據(jù)的傳輸;

數(shù)據(jù)分區(qū)

完成了數(shù)據(jù)備份，剩下的就是創(chuàng)建不同的新表，并安裝分區(qū)將數(shù)據(jù)導入了。

復制表結(jié)構(gòu)

執(zhí)行表結(jié)構(gòu)的copy：

create table account_log_1 like account_log; 

創(chuàng)建一個結(jié)構(gòu)一樣的不帶數(shù)據(jù)的表，并對表添加索引。然后再基于添加過索引的表，創(chuàng)建出account_log_2、account_log_3等表。

不同的表機構(gòu)復制方式有所區(qū)別，復制完成之后，注意檢查一下新表的主鍵、索引等是否存在。

由于該表并為具體的實際業(yè)務(wù)，而且表在設(shè)計時缺少創(chuàng)建時間字段，因此就以ID為區(qū)分，每1000w條數(shù)據(jù)一張表。

遷移數(shù)據(jù)

執(zhí)行以下語句，直接將前1000w條數(shù)據(jù)插入到第一張表中：

INSERT INTO account_log_1 SELECT * FROM account_log WHERE id <= 10000000; 

執(zhí)行1000w條數(shù)據(jù)，用時205秒，大概3分鐘25秒。粗略估算，5000萬數(shù)據(jù)如果通過此種方式將全表數(shù)據(jù)備份，也只需要18分鐘左右。

因此，上面到導出操作算是走的彎路，也見證了一下通過Navicat導出的性能問題。

驗證數(shù)據(jù)

執(zhí)行兩條查詢語句，驗證一下導入新表的數(shù)據(jù)與原始數(shù)據(jù)的數(shù)據(jù)量是否一致：

select count(1) from account_log_1; 
 
select count(1) from account_log WHERE id <= 10000000; 

數(shù)據(jù)條數(shù)一致，驗證無誤。

刪除歷史數(shù)據(jù)

已經(jīng)導入新表的歷史數(shù)據(jù)(備份數(shù)據(jù))是可以進行刪除的，提升續(xù)查詢速度。當然，如果該表已經(jīng)不使用，則也可以暫時保留。

刪除語句：

delete from account_log where id <= 10000000; 

這里就暫時不刪除了;

循環(huán)執(zhí)行導入

后續(xù)操作就是循環(huán)執(zhí)行導入操作了，將id的條件區(qū)間進行擴展：

NSERT INTO account_log_2 SELECT * FROM account_log WHERE id <= 20000000 and id > 10000000; 

然后循環(huán)進行驗證、刪除等操作，直至整個大表被拆分完畢。

在循環(huán)查詢插入的時候發(fā)現(xiàn)：未刪除數(shù)據(jù)記錄的情況下，處于中間部分的數(shù)據(jù)遷移耗時最長，主要原因就是查詢時索引的特性決定的。

性能驗證

驗證count語句耗時：

select count(1) from account_log_2; 

耗時，1.8秒查出結(jié)果;

順便驗證了一下count(id)、count(*)的查詢，發(fā)現(xiàn)在1000w數(shù)據(jù)的情況下，性能差別并不明顯。

select count(*) from account_log_2; 
 
select count(id) from account_log_2; 

在實驗的過程中發(fā)現(xiàn)，Mysql可能進行了緩存處理，在第一次查的時候時間較長，后續(xù)再查就比較快了。

后續(xù)有驗證了根據(jù)索引查詢的效率，1000w數(shù)據(jù)中查詢記錄，800毫秒能能查詢出結(jié)果來，提升效率非常顯著;

大表數(shù)據(jù)遷移思考

經(jīng)過此次大表數(shù)據(jù)遷移的實踐，對大表遷移有了新的認知和直觀感受。單純的只看技術(shù)文章，感覺一切都輕而易舉可以實施，但真正實踐時才會發(fā)現(xiàn)有很懂可提升和改進的地方。

學到和一些值得思考的問題：

• 大表導出不僅要考慮導出的時間問題，還需要考慮導出數(shù)據(jù)的空間問題，以及衍生出來的存儲和傳輸問題;
• 大數(shù)據(jù)讀取與插入是否會造成表的死鎖。一般，導出數(shù)據(jù)沒有表鎖，導出會對表加鎖;
• 監(jiān)控導出操作是否會對服務(wù)器實例的IO、帶寬、內(nèi)存造成影響，造成內(nèi)存溢出等;
• 遷移的數(shù)據(jù)特殊類型例如(blob)會不會在導入的時候丟失;
• 不同的引擎之間是否會對導入數(shù)據(jù)有影響。 

通過本篇文章你學到了什么?了解到了什么不曾知道的點?