實踐了五千萬的數據表和重建索引，學到了！
本文轉載自微信公眾號「程序新視界」，作者二師兄。轉載本文請聯系程序新視界公眾號。
背景
項目中有一張歷史記錄表，主要用于記錄一些接口調用流水，因為該表的地位不是那么重要，當初的創建者并未對核心字段創建索引。
不知不覺這張表的數據已經有5千萬數據了，由于沒有索引，在排查問題時，發現這種表根本查不動。
于是，決定下手進行分表并建立索引。這張表在系統中只負責插入，影響范圍極小，正好拿來練手。
解決思路
我們知道，在Mysql 5.5及之前版本，在運行的生成環境中對大表執行alter操作，會引起表的重建和鎖表，影響業務正常運轉。
從Mysql 5.6開始，Online DDL特性被引進，運行alter操作時同時允許運行select、insert、update、delete語句。
在數據量小于100w時，可以考慮直接修改表結構建立索引，正常幾秒鐘就可以完成。但當表的數據量超過百萬，無論Mysql 5.6及之前版本的鎖表行為、Mysql 5.6中因慢SQL引起的等待，都不允許直接在生產庫中進行alter table操作。
目前，五千萬的數據，直接修改表來建立索引，肯定是不可行的，弄不好還把數據庫給弄崩了。只能想另外的方法。
解決方案
鑒于這張表本身的影響范圍有限，想到的解決方案就分表。無論是將所有數據一個區間一個區間的拆分出去，還是將整個表都換成新表，然后再處理歷史數據，基本上都要做拆分處理。
基本解決思路：
- 第一步：創建一張數據結構一樣的新表(補全索引)，將業務切換至新表，這樣新生成的數據便有了索引;
- 第二步：對舊表數據進行備份，已被后續處理過程中有問題進行恢復;
- 第三步：按照數據ID，1000萬條數據拆分一個表，新拆分的表(補全索引);
對于分表的數據，數據庫訪問層并未使用，如果業務中有其他地方使用，則可考慮在數據庫訪問層根據請求時間區間或ID等來切換數據庫表名。
基本操作
備份數據
數據庫基于阿里云的云服務，導出數據有多種方式，比如直接copy出一張表、基于Navicat導出、基于mysqldump導出等。
copy出一張新表語句如下：
- create table account_log_1 select * from account_log;
在測試環境上驗證了一下，粗略估計該方式得1小時左右才能執行完數據的備份。
由于沒有安裝Mysql的linux生產服務器可用，就沒采用mysqldump方式導致。
最終，采用在堡壘機上通過Navicat的導出功能，導出內容為SQL語句。
結果也很令人失望，5千萬的數據：導出耗時1小時22分鐘，導出SQL語句磁盤空間占用38.5G。還好在導出過程中，通過監控查看數據庫的整體性能還比較平穩。
為了節省堡壘機的磁盤空間，又花費了十多分鐘將38.5G的數據進行壓縮，最終占用3.8G的存儲空間。
Navicat與mysqldump性能對比
Navicat導出的數據是一條條的insert語句，每一行一條插入語句。
mysqldump導出的數據，多行數據合并成一行插入。批插入減少SQL語法詞法解析，減少插事務(最大的開銷)，較少數據的傳輸;
數據分區
完成了數據備份，剩下的就是創建不同的新表，并安裝分區將數據導入了。
復制表結構
執行表結構的copy：
- create table account_log_1 like account_log;
創建一個結構一樣的不帶數據的表，并對表添加索引。然后再基于添加過索引的表，創建出account_log_2、account_log_3等表。
不同的表機構復制方式有所區別，復制完成之后，注意檢查一下新表的主鍵、索引等是否存在。
由于該表并為具體的實際業務，而且表在設計時缺少創建時間字段，因此就以ID為區分，每1000w條數據一張表。
遷移數據
執行以下語句，直接將前1000w條數據插入到第一張表中：
- INSERT INTO account_log_1 SELECT * FROM account_log WHERE id <= 10000000;
執行1000w條數據，用時205秒，大概3分鐘25秒。粗略估算，5000萬數據如果通過此種方式將全表數據備份，也只需要18分鐘左右。
因此，上面到導出操作算是走的彎路，也見證了一下通過Navicat導出的性能問題。
驗證數據
執行兩條查詢語句，驗證一下導入新表的數據與原始數據的數據量是否一致：
- select count(1) from account_log_1;
- select count(1) from account_log WHERE id <= 10000000;
數據條數一致，驗證無誤。
刪除歷史數據
已經導入新表的歷史數據(備份數據)是可以進行刪除的，提升續查詢速度。當然，如果該表已經不使用，則也可以暫時保留。
刪除語句：
- delete from account_log where id <= 10000000;
這里就暫時不刪除了;
循環執行導入
后續操作就是循環執行導入操作了，將id的條件區間進行擴展：
- NSERT INTO account_log_2 SELECT * FROM account_log WHERE id <= 20000000 and id > 10000000;
然后循環進行驗證、刪除等操作，直至整個大表被拆分完畢。
在循環查詢插入的時候發現：未刪除數據記錄的情況下，處于中間部分的數據遷移耗時最長，主要原因就是查詢時索引的特性決定的。
性能驗證
驗證count語句耗時：
- select count(1) from account_log_2;
耗時，1.8秒查出結果;
順便驗證了一下count(id)、count(*)的查詢，發現在1000w數據的情況下，性能差別并不明顯。
- select count(*) from account_log_2;
- select count(id) from account_log_2;
在實驗的過程中發現，Mysql可能進行了緩存處理，在第一次查的時候時間較長，后續再查就比較快了。
后續有驗證了根據索引查詢的效率，1000w數據中查詢記錄，800毫秒能能查詢出結果來，提升效率非常顯著;
大表數據遷移思考
經過此次大表數據遷移的實踐，對大表遷移有了新的認知和直觀感受。單純的只看技術文章，感覺一切都輕而易舉可以實施，但真正實踐時才會發現有很懂可提升和改進的地方。
學到和一些值得思考的問題：
- 大表導出不僅要考慮導出的時間問題，還需要考慮導出數據的空間問題，以及衍生出來的存儲和傳輸問題;
- 大數據讀取與插入是否會造成表的死鎖。一般，導出數據沒有表鎖，導出會對表加鎖;
- 監控導出操作是否會對服務器實例的IO、帶寬、內存造成影響，造成內存溢出等;
- 遷移的數據特殊類型例如(blob)會不會在導入的時候丟失;
- 不同的引擎之間是否會對導入數據有影響。
通過本篇文章你學到了什么?了解到了什么不曾知道的點?
