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

怪我咯
發布: 2017-04-05 13:20:32
原創
1684 人瀏覽過

最近有一張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中文網其他相關文章!

來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板