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

2020-01-18 23:29:08

字體：大中小

供稿：網(wǎng)友

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

亂打亂撞

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

復(fù)制代碼代碼如下:

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引擎到底如何？
    導(dǎo)庫實驗中導(dǎo)出并導(dǎo)入一個月的數(shù)據(jù)（8G的文本量，2500w條記錄），在myisam引擎下需要不到4h（測試環(huán)境為pc機），但是在innodb引擎下，卻需要32小時，改善索引之類的，也需要28h，性能有8倍之差。

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

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

innodb_flush_log_at_trx_commit

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

innodb_buffer_pool_size

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

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

innodb_log_file_size

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

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

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

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

復(fù)制代碼代碼如下:

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

并行讀取是否會更快？

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

復(fù)制代碼代碼如下:

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'