MySQL兩千萬資料優化&遷移-mysql教程-PHP中文網

MySQL兩千萬資料優化&遷移

黄舟

發布： 2017-02-21 10:22:44

原創

1765 人瀏覽過

最近有一張2000W筆記錄的資料表需要最佳化和遷移。 2000W資料對於MySQL來說很尷尬，因為合理的創建索引速度還是挺快的，再怎麼優化速度也得不到太大提升。不過這些數據有大量的冗餘欄位和錯誤訊息，極不方便做統計和分析。所以我需要建立一張新表，把舊表中的數據一一取出來優化後放回新表；

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

2000W資料中，能作為查詢條件的欄位我們是預知的。所以將這部分資料單獨建立新的字段，對於有規則的資料合理地改變字段結構，例如身分證就是varchar(18)。對於不重要的資料我們合併後存在一個結構為text的欄位。

對於一些有關聯的資料我們需要計算，常見的例如身分證種能取得到準確的性別，出生地、生日、年齡。

二. 資料遷移

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

資料量太大，無法一次取得（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毫秒…
資料量太大且資料無法預估，某些特殊資料會導致資料匯入失敗；
我們有三種方案將新資料存入新表，分別如下：

一條一條插入資料；
開始一定會想這種方案一定不行，因為每次插入都會有一次資料庫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注入；
一次插入50000條數據；

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

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