最近有一張2000W條記錄的數(shù)據(jù)表需要優(yōu)化和遷移。2000W數(shù)據(jù)對(duì)于MySQL來(lái)說(shuō)很尷尬，因?yàn)楹侠淼膭?chuàng)建索引速度還是挺快的，再怎么優(yōu)化速度也得不到多大提升。不過這些數(shù)據(jù)有大量的冗余字段和錯(cuò)誤信息，極不方便做統(tǒng)計(jì)和分析。所以我需要?jiǎng)?chuàng)建一張新表，把舊表中的數(shù)據(jù)一條一條取出來(lái)優(yōu)化后放回新表；

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

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

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

數(shù)據(jù)量太大，無(wú)法一次獲取（2000W數(shù)據(jù)扔到內(nèi)存挺可怕的）；

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

select * from table_name limit 15000000,50000;

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

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

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

不過還是很慢，時(shí)間就是生命……還好我們有自增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會(huì)導(dǎo)致SQL的索引命中變差，效率同樣也會(huì)下降。第一條SQL的執(zhí)行時(shí)間是2毫秒，第二條執(zhí)行時(shí)間5毫秒（我取的平均值）。每次數(shù)據(jù)的查詢速度直接從35秒降到2毫秒……

數(shù)據(jù)量太大并且數(shù)據(jù)無(wú)法預(yù)估，某些特殊數(shù)據(jù)會(huì)導(dǎo)致數(shù)據(jù)導(dǎo)入失敗；

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

一條一條插入數(shù)據(jù)；

開始肯定會(huì)想這種方案一定不行，因?yàn)槊看尾迦攵紩?huì)有一次數(shù)據(jù)庫(kù)IO操作。但是該方案有個(gè)好處是能及時(shí)發(fā)現(xiàn)有問題的數(shù)據(jù)，修改后再繼續(xù)執(zhí)行； 在Oracle中使用『綁定變量』能帶來(lái)性能提升，正好MySQL也提供了『綁定變量』的功能。于是在不改變邏輯的情況下，嘗試優(yōu)化數(shù)據(jù)存儲(chǔ)速度。代碼如下：

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的『綁定變量』并沒帶來(lái)明顯的速度提升，不過能有效的防止SQL注入；

一次插入50000條數(shù)據(jù)；

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

組裝成SQL文件，最后統(tǒng)一導(dǎo)入；

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

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

來(lái)自：https://yq.aliyun.com/articles/59907