怎樣將MySQL中兩千萬資料進行最佳化與遷移

最近有一張2000W筆記錄的資料表需要最佳化和遷移。 2000W資料對於MySQL來說很尷尬，因為合理的創建索引速度還是挺快的，再怎麼優化速度也得不到多大提升。張新表，把舊表中的數據一一取出來優化後放回新表；

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

2000W資料中，能作為

查詢

條件的字段我們是預知的。 (18)。地、生日、年齡。將新資料插入新表。 ##我們可以透過MySQL的limit語法分批取得。的第一個參數越來越大，查詢速度會慢的嚇人（上面這條SQL執行會花35秒）。 可透過二分法拆分2000W數據，當執行到1000W數據時，將數據倒序。時間就是生命…還好我們有自增ID（創建資料表第一條定律，一定要有自增字段），優化後的SQl如下：

select * from table_name limit 15000000,50000;

為了直觀演示，我寫了兩條功能一樣的SQL。 （我取的平均值）。會導致資料導入失敗；

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

一條一條

插入數據
1. ；
   開始一定會想這種方案一定不行，因為每次插入都會有一次資料庫IO操作。但此方案有個好處是能及時發現有問題的數據，修改後再繼續執行； 在Oracle中使用『綁定

   變數

   』能帶來效能提升，正好MySQL也提供了『綁定變數’的功能。於是在不改變邏輯的情況下，嘗試優化資料儲存速度。程式碼如下：

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

   最後效果不怎麼好，MySQL的『綁定變數』並沒有帶來明顯的速度提升，不過能有效的防止SQL注入；
   一次插入50000條數據；
   這是我最後選中的方案，一是能及時發現有問題的數據，二是導入數據非常穩定。就像支援斷點續傳一樣，每一步都能看到效果。在執行腳本時，也能同步開始寫入分析邏輯；

2. 組裝成SQL文件，最後統一導入；

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

#三. 總結

#透過各種優化，最後將腳本執行時間縮短到了20分鐘內。優化後資料品質得到了較高保證，下次將嘗試2億資料的最佳化&遷移…

#

以上是怎樣將MySQL中兩千萬資料進行最佳化與遷移的詳細內容。更多資訊請關注PHP中文網其他相關文章！