[[438008]]

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

背景

項(xiàng)目中有一張歷史記錄表，主要用于記錄一些接口調(diào)用流水，因?yàn)樵摫淼牡匚徊皇悄敲粗匾?dāng)初的創(chuàng)建者并未對(duì)核心字段創(chuàng)建索引。

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

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

解決思路

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

從Mysql 5.6開始，Online DDL特性被引進(jìn)，運(yùn)行alter操作時(shí)同時(shí)允許運(yùn)行select、insert、update、delete語句。

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

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

解決方案

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

基本解決思路：

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

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

基本操作

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

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

copy出一張新表語句如下：

create table account_log_1 select * from account_log; 

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

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

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

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

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

Navicat與mysqldump性能對(duì)比

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

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

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

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

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

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

create table account_log_1 like account_log; 

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

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

由于該表并為具體的實(shí)際業(yè)務(wù)，而且表在設(shè)計(jì)時(shí)缺少創(chuàng)建時(shí)間字段，因此就以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ù)，用時(shí)205秒，大概3分鐘25秒。粗略估算，5000萬數(shù)據(jù)如果通過此種方式將全表數(shù)據(jù)備份，也只需要18分鐘左右。

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

驗(yàn)證數(shù)據(jù)

執(zhí)行兩條查詢語句，驗(yàn)證一下導(dǎo)入新表的數(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ù)一致，驗(yàn)證無誤。

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

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

刪除語句：

delete from account_log where id <= 10000000; 

這里就暫時(shí)不刪除了;

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

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

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

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

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

性能驗(yàn)證

驗(yàn)證count語句耗時(shí)：

select count(1) from account_log_2; 

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

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

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

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

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

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

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

學(xué)到和一些值得思考的問題：

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

通過本篇文章你學(xué)到了什么?了解到了什么不曾知道的點(diǎn)?