最近有一張2000W條記錄的數據表需要優化和遷移。2000W數據對于MySQL來說很尷尬，因為合理的創建索引速度還是挺快的，再怎么優化速度也得不到多大提升。不過這些數據有大量的冗余字段和錯誤信息，極不方便做統計和分析。所以我需要創建一張新表，把舊表中的數據一條一條取出來優化后放回新表；

一. 清除冗余數據，優化字段結構

2000W數據中，能作為查詢條件的字段我們是預知的。所以將這部分數據單獨創建新的字段，對于有規則的數據合理改變字段結構，比如身份證就是varchar(18)。對于不重要的數據我們合并后存在一個結構為text的字段。

對于一些有關聯的數據我們需要計算，常見的比如身份證種能獲取到準確的性別，出生地、生日、年齡。

二. 數據遷移

我們從數據庫中取出一條舊數據，再通過計算處理后得到想要的新數據，最后將新數據插入新表。不過在獲取新數據時遇到如下問題。

1. 數據量太大，無法一次獲取（2000W數據扔到內存挺可怕的）；

   我們可以通過MySQL的limit語法分批獲取。比如每次獲取50000，SQL語句如下：

   select * from table_name limit 15000000,50000;

   通過這種方法能解決數據量太大的問題，但是隨著limit的第一個參數越來越大，查詢速度會慢的嚇人（上面這條SQL執行會花35秒）。時間就是生命，于是我們開始優化SQL語句，優化后變成下面這樣：

   select * from table_name order by id desc limit 5000000,50000;

   可通過二分法拆分2000W數據，當執行到1000W數據時，將數據倒序。優化后SQL執行效率顯著提升，從35秒降到9秒；

   不過還是很慢，時間就是生命……還好我們有自增ID（創建數據表第一條定律，一定要有自增字段），優化后的SQl如下：

   1. select * from table_name where id>15000000 and id<15050000；
   2. select * from table_name where id>15000000 limit 50000; 

   為了直觀演示，我寫了兩條功能一樣的SQL。相比第一條，第二條的limit會導致SQL的索引命中變差，效率同樣也會下降。第一條SQL的執行時間是2毫秒，第二條執行時間5毫秒（我取的平均值）。每次數據的查詢速度直接從35秒降到2毫秒……

2. 數據量太大并且數據無法預估，某些特殊數據會導致數據導入失敗；

   我們有三種方案去將新數據存入新表，分別如下：

   1. 一條一條插入數據；

   開始肯定會想這種方案一定不行，因為每次插入都會有一次數據庫IO操作。但是該方案有個好處是能及時發現有問題的數據，修改后再繼續執行； 在Oracle中使用『綁定變量』能帶來性能提升，正好MySQL也提供了『綁定變量』的功能。于是在不改變邏輯的情況下，嘗試優化數據存儲速度。代碼如下：

   public function actionTest(array $data)
   {$mysqli = new mysqli("192.168.1.106", "username", "password", "test");$sql = "insert into table_name(name,identity) values (?,?)";$stmt = $connection->prepare($sql);$name = "";$identity = "";//使用綁定變量$stmt->bind_param("si", $name, $identity);foreach($data as $val){$name = $val[name];$identity = $val[card_id];//執行$stmt->execute();}$stmt->close();
   }

   最后效果不怎么好，MySQL的『綁定變量』并沒帶來明顯的速度提升，不過能有效的防止SQL注入；

   1. 一次插入50000條數據；

   這是我最后選中的方案，一是能及時發現有問題的數據，二是導入數據非常穩定。就像支持斷點續傳一樣，每一步都能看到效果。在執行腳本時，也能同步開始寫分析邏輯；

   1. 組裝成SQL文件，最后統一導入；

   組裝一個大的SQL文件，最后通過MySQL自帶的工具導入也是極好的。但如果有一條SQL有問題，你可能需要重跑一次腳本。因為在9G大小的文本文件中修改一個符號是很痛苦的事情……

三. 總結

通過各種優化，最后將腳本執行時間縮短到了20分鐘內。優化后數據質量得到了較高保證，下次將嘗試2億數據的優化&遷移……

PS：原文地址 http://blog.it2048.cn/article_2000w-data.html 之后我會陸續把自己博客遷移到云棲社區，希望大家關注！