MySQL下海量數(shù)據(jù)的遷移步驟分享

更新時間：2013年10月25日 19:07:04 作者：

一般mysql小數(shù)據(jù)的情況可以通過直接復制目錄，或者通過帝國備份工具，如果是海量數(shù)據(jù)(6億)該怎么處理呢，下面看下別人是怎么處理的參考一下

公司數(shù)據(jù)中心計劃將海量數(shù)據(jù)做一次遷移，同時增加某時間字段（原來是datatime類型，現(xiàn)在增加一個date類型），單表數(shù)據(jù)量達到6億多條記錄，數(shù)據(jù)是基于時間（月）做的partition由于比較忙，一直沒有總結，所以很細節(jié)的地方都記不清楚了，此處只是簡單總結下當時的情形，備忘

亂打亂撞

最初接到任務，沒有明確的入手點，直接就是select * from db limit 10000,動態(tài)修改翻頁數(shù)量，通過控制臺看耗時情況，慢

復制代碼代碼如下:

SELECT IR_SID,IR_HKEY,IR_GROUPNAME,IR_SITENAME,IR_CHANNEL,IR_MID,IR_URLNAME,IR_STATUS_CONTENT,IR_CREATED_AT,date_format(IR_CREATED_AT,'%Y.%m.%d'),IR_LASTTIME,IR_VIA,IR_THUMBNAIL_PIC,IR_RTTCOUNT,IR_COMMTCOUNT,IR_UID,IR_SCREEN_NAME,IR_RETWEETED_UID,IR_RETWEETED_SCREEN_NAME,IR_RETWEETED_MID,IR_RETWEETED_URL,IR_STATUS_BODY FROM TB_SINA_STATUS WHERE IR_SID>40000 AND IR_SID<50001 INTO OUTFILE '/home/mysql/data/data_outfile.txt'; LOAD DATA INFILE '/home/mysql/data/data_outfile.txt' INTO TABLE NEW_TB_SINA_STATUS; 

是否可以基于partition讀數(shù)據(jù)呢

    既然數(shù)據(jù)庫是按partition做分區(qū)，是否可以按partition讀數(shù)據(jù)呢，如果可以改用怎樣的語法讀呢？時間上只要按月讀數(shù)據(jù)，mysql會自動的基于partition讀，具體可以用命令：explain partition即可看到具有基于哪個partition
    讀大數(shù)據(jù)會十分耗時，對于數(shù)據(jù)進行到什么狀態(tài)，我們可能十分想了解，可以用命令：show status查看，我印象中主要是sending data，writting to net之類的。
    innodb引擎的性能較myIsam引擎到底如何？
    導庫實驗中導出并導入一個月的數(shù)據(jù)（8G的文本量，2500w條記錄），在myisam引擎下需要不到4h（測試環(huán)境為pc機），但是在innodb引擎下，卻需要32小時，改善索引之類的，也需要28h，性能有8倍之差。

在網(wǎng)上找到了高人關于innodb與myisam區(qū)別，說需要修改innodb_buffer_pool_size、innodb_flush_log_at_trx_commit

可保證沒有太大差別，嘗試了沒有明顯改善，在本機倒是可以，為什么呢？？？這個折騰了我好長時間

innodb_flush_log_at_trx_commit

是否為Innodb比MyISAM慢1000倍而頭大？看來也許你忘了修改這個參數(shù)了。默認值是 1，這意味著每次提交的更新事務（或者每個事務之外的語句）都會刷新到磁盤中，而這相當耗費資源，尤其是沒有電池備用緩存時。很多應用程序，尤其是從 MyISAM轉(zhuǎn)變過來的那些，把它的值設置為 2 就可以了，也就是不把日志刷新到磁盤上，而只刷新到操作系統(tǒng)的緩存上。日志仍然會每秒刷新到磁盤中去，因此通常不會丟失每秒1-2次更新的消耗。如果設置為 0 就快很多了，不過也相對不安全了 — MySQL服務器崩潰時就會丟失一些事務。設置為 2 只會丟失刷新到操作系統(tǒng)緩存的那部分事務。

innodb_buffer_pool_size

Innodb在默認的 innodb_buffer_pool_size 設置下跟蝸牛似的。由于Innodb把數(shù)據(jù)和索引都緩存起來，無需留給操作系統(tǒng)太多的內(nèi)存，因此如果只需要用Innodb的話則可以設置它高達 70-80% 的可用內(nèi)存。

最后千辛萬苦的、跋山涉水的，找到了另外兩個參數(shù)

innodb_log_file_size

在高寫入負載尤其是大數(shù)據(jù)集的情況下很重要。這個值越大則性能相對越高，但是要注意到可能會增加恢復時間。我經(jīng)常設置為 64-512MB，跟據(jù)服務器大小而異。

innodb_log_buffer_size
默認的設置在中等強度寫入負載以及較短事務的情況下，服務器性能還可以。如果存在更新操作峰值或者負載較大，就應該考慮加大它的值了。如果它的值設置太高了，可能會浪費內(nèi)存 — 它每秒都會刷新一次，因此無需設置超過1秒所需的內(nèi)存空間。通常 8-16MB 就足夠了。越小的系統(tǒng)它的值越小。

最終搞定，myisam與innodb的導數(shù)據(jù)的性能基本一致，2500的數(shù)據(jù)約需要3.5h，單庫讀數(shù)據(jù)需要2h，這個只是一個示意值僅供參考（pc上的測試），正式服務的上的測試結果更加明顯

性能調(diào)優(yōu)語句參考

復制代碼代碼如下:

set profiling = 1;
show profiles\G
SHOW profile CPU,BLOCK IO io FOR query 1;
show status
Show Processlist
explain

并行讀取是否會更快？

如果基于partition導數(shù)據(jù)，還是不能達到既定目標，我最終是通過編寫shell腳步，多進程并行基于partition導數(shù)據(jù)，即啟動多個mysql -uroot -p db < exp201201.sql 、mysql -uroot -p db < exp201202.sql，每個sql下按天做讀寫（事件環(huán)境下是按月做partition的）

復制代碼代碼如下:

SELECT IR_SID,IR_HKEY,IR_GROUPNAME,IR_SITENAME,IR_CHANNEL,IR_MID,IR_URLNAME,IR_STATUS_CONTENT,IR_CREATED_AT,date_format(IR_CREATED_AT,'%Y.%m.%d'),IR_LASTTIME,IR_VIA,IR_THUMBNAIL_PIC,IR_RTTCOUNT,IR_COMMTCOUNT,IR_UID,IR_SCREEN_NAME,IR_RETWEETED_UID,IR_RETWEETED_SCREEN_NAME,IR_RETWEETED_MID,IR_RETWEETED_URL,IR_STATUS_BODY 
INTO OUTFILE '/home/mysql/data/sinawb20120724/111101.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' 
LINES TERMINATED BY '\n'
FROM TB_SINA_STATUS  
WHERE ir_created_at >='2011-11-01 00:00:00' and ir_created_at <'2011-11-01 23:59:59'

LOAD DATA  LOCAL INFILE '/home/mysql/data/sinawb20120724/111101.txt' 
IGNORE INTO TABLE `NEW_TB_SINA_STATUS` 
CHARACTER SET UTF8  
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' 
LINES TERMINATED BY '\n'

您可能感興趣的文章:

Oracle 和 mysql的9點區(qū)別
這篇文章主要介紹了Oracle 和 mysql的9點區(qū)別,需要的朋友可以參考下
2014-04-04
分享下mysql各個主要版本之間的差異
因為mysql的版本較多，而且又被oracle公司收購，所有很多朋友不是很清楚各個版本的區(qū)別，這里簡單介紹下，方便需要的朋友
2013-06-06
win10 安裝 mysql 5.7 msi版的教程圖文詳解
這篇文章主要介紹了win10 安裝 mysql 5.7 msi版的教程，本文通過圖文并茂的形式給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下
2020-05-05
MySQL中冗余和重復索引的區(qū)別說明
這篇文章主要介紹了MySQL中冗余和重復索引的區(qū)別說明，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
2021-03-03
mysql命令導出表結構和數(shù)據(jù)的操作方法
在日常使用中,我們經(jīng)常需要對數(shù)據(jù)庫進行備份和復制,因此必須了解如何導出表結構和表數(shù)據(jù),以下是使用MySQL命令導出表結構和表數(shù)據(jù)的方法,感興趣的朋友一起看看吧
2023-11-11
MYSQL初始化數(shù)據(jù)目錄的實現(xiàn)步驟
本文主要介紹了MYSQL初始化數(shù)據(jù)目錄的實現(xiàn)步驟,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
2025-02-02
一文了解MYSQL三大范式和表約束
范式是"符合某一種級別的關系模式的集合,表示一個關系內(nèi)部各屬性之間的聯(lián)系的合理化程度",下面這篇文章主要給大家介紹了關于MYSQL三大范式和表約束的相關資料,需要的朋友可以參考下
2022-04-04
MySQL嵌套查詢實現(xiàn)子查詢的方法
本文主要介紹了MySQL嵌套查詢實現(xiàn)子查詢的方法，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下
2022-02-02
MySQL的鎖機制使用詳解
這篇文章主要介紹了MySQL的鎖機制使用方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
2024-04-04
SQL實現(xiàn)LeetCode(181.員工掙得比經(jīng)理多)
這篇文章主要介紹了SQL實現(xiàn)LeetCode(181.員工掙得比經(jīng)理多),本篇文章通過簡要的案例,講解了該項技術的了解與使用,以下就是詳細內(nèi)容,需要的朋友可以參考下
2021-08-08