最近有一張2000W條記錄的數(shù)據(jù)表需要優(yōu)化和遷移娜谊。2000W數(shù)據(jù)對于MySQL來說很尷尬谍珊，因為合理的創(chuàng)建索引速度還是挺快的谊囚，再怎么優(yōu)化速度也得不到多大提升。不過這些數(shù)據(jù)有大量的冗余字段和錯誤信息丹擎，極不方便做統(tǒng)計和分析尾抑。所以我需要創(chuàng)建一張新表歇父，把舊表中的數(shù)據(jù)一條一條取出來優(yōu)化后放回新表；

一. 清除冗余數(shù)據(jù)再愈，優(yōu)化字段結(jié)構(gòu)

2000W數(shù)據(jù)中榜苫，能作為查詢條件的字段我們是預(yù)知的。所以將這部分?jǐn)?shù)據(jù)單獨創(chuàng)建新的字段翎冲，對于有規(guī)則的數(shù)據(jù)合理改變字段結(jié)構(gòu)垂睬，比如身份證就是varchar(18)。對于不重要的數(shù)據(jù)我們合并后存在一個結(jié)構(gòu)為text的字段府适。

對于一些有關(guān)聯(lián)的數(shù)據(jù)我們需要計算，常見的比如身份證種能獲取到準(zhǔn)確的性別肺樟，出生地檐春、生日、年齡么伯。

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

我們從數(shù)據(jù)庫中取出一條舊數(shù)據(jù)疟暖，再通過計算處理后得到想要的新數(shù)據(jù)，最后將新數(shù)據(jù)插入新表田柔。不過在獲取新數(shù)據(jù)時遇到如下問題俐巴。

1. 數(shù)據(jù)量太大，無法一次獲扔脖（2000W數(shù)據(jù)扔到內(nèi)存挺可怕的）欣舵；

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

   select * from table_name limit 15000000,50000;
   

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

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

   可通過二分法拆分2000W數(shù)據(jù)牲剃，當(dāng)執(zhí)行到1000W數(shù)據(jù)時遣疯，將數(shù)據(jù)倒序。優(yōu)化后SQL執(zhí)行效率顯著提升凿傅，從35秒降到9秒缠犀；

   不過還是很慢，時間就是生命……還好我們有自增ID（創(chuàng)建數(shù)據(jù)表第一條定律聪舒，一定要有自增字段）夭坪，優(yōu)化后的SQl如下：

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

   為了直觀演示过椎，我寫了兩條功能一樣的SQL室梅。相比第一條，第二條的limit會導(dǎo)致SQL的索引命中變差，效率同樣也會下降亡鼠。第一條SQL的執(zhí)行時間是2毫秒赏殃，第二條執(zhí)行時間5毫秒（我取的平均值）。每次數(shù)據(jù)的查詢速度直接從35秒降到2毫秒……

2. 數(shù)據(jù)量太大并且數(shù)據(jù)無法預(yù)估间涵，某些特殊數(shù)據(jù)會導(dǎo)致數(shù)據(jù)導(dǎo)入失斎嗜取；

   我們有三種方案去將新數(shù)據(jù)存入新表勾哩，分別如下：

   1. 一條一條插入數(shù)據(jù)抗蠢；

      開始肯定會想這種方案一定不行，因為每次插入都會有一次數(shù)據(jù)庫IO操作思劳。但是該方案有個好處是能及時發(fā)現(xiàn)有問題的數(shù)據(jù)迅矛，修改后再繼續(xù)執(zhí)行；
      在Oracle中使用『綁定變量』能帶來性能提升潜叛，正好MySQL也提供了『綁定變量』的功能秽褒。于是在不改變邏輯的情況下，嘗試優(yōu)化數(shù)據(jù)存儲速度威兜。代碼如下：

      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'];
              //執(zhí)行
              $stmt->execute();
          }
          $stmt->close();
      }
      

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

   2. 一次插入50000條數(shù)據(jù)蚂踊；

      這是我最后選中的方案，一是能及時發(fā)現(xiàn)有問題的數(shù)據(jù)笔宿，二是導(dǎo)入數(shù)據(jù)非常穩(wěn)定悴势。就像支持?jǐn)帱c續(xù)傳一樣，每一步都能看到效果措伐。在執(zhí)行腳本時特纤，也能同步開始寫分析邏輯；

   3. 組裝成SQL文件侥加，最后統(tǒng)一導(dǎo)入捧存；

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

三. 總結(jié)

通過各種優(yōu)化提前，最后將腳本執(zhí)行時間縮短到了20分鐘內(nèi)吗货。優(yōu)化后數(shù)據(jù)質(zhì)量得到了較高保證，下次將嘗試2億數(shù)據(jù)的優(yōu)化&遷移……

更多相關(guān)文章請移步我的博客-原文鏈接：兩千萬數(shù)據(jù)優(yōu)化整理&遷移

程序員編程日志