MYSQL 大批量資料插入

標籤：

最近在做MYSQL大批量資料的測試，就簡單總結一下遇到的問題：

首先我是簡單的寫了一個MYSQL的迴圈插入資料的SP，具體如下：

這是插入100W資料的過程和結果，可以看到是換了55min +20S約3320秒（約300rows/s）,看到之後我是只崩潰，就在網上查了些提速的方法：

0. 最快的當然是直接 copy 資料庫表的資料檔案（版本和平台最好要相同或相似）；
1. 設定 innodb_flush_log_at_trx_commit = 0 ，相對於 innodb_flush_log_at_trx_commit = 1 可以十分明顯的提升匯入速度；
2. 使用 load data local infile 提速明顯；
3. 修改參數 bulk_insert_buffer_size， 調大批量插入的緩衝；
4. 合并多條 insert 為一條: insert into t values(a,b,c),  (d,e,f) ,,,
5. 手動使用事物；

當資料量較大時，如上百萬甚至上千萬記錄時，向MySQL資料庫中匯入資料通常是一個比較費時的過程。通常可以採取以下方法來加速這一過程：

一、對於Myisam類型的表，可以通過以下方式快速的匯入大量的資料。 ALTER TABLE tblname DISABLE KEYS; loading the data ALTER TABLE tblname ENABLE KEYS; 這兩個命令用來開啟或者關閉Myisam表非唯一索引的更新。在匯入大量的資料到一個非空的Myisam表時，通過設定這兩個命令，可以提高匯入的效率。對於匯入大量資料到一個空的Myisam表，預設就是先匯入資料然後才建立索引的，所以不用進行設定。

二、對於Innodb類型的表，有以下幾種方式可以提高匯入的效率： ①因為Innodb類型的表是按照主鍵的順序儲存的，所以將匯入的資料按照主鍵的順序排列，可以有效提高匯入資料的效率。如果Innodb表沒有主鍵，那麼系統會預設建立一個內部列作為主鍵，所以如果可以給表建立一個主鍵，將可以利用這個優勢提高匯入資料的效率。

②在匯入資料前執行SET UNIQUE_CHECKS=0，關閉唯一性校正，在匯入結束後執行SET UNIQUE_CHECKS=1，恢複唯一性校正，可以提高匯入的效率。

③如果應用使用自動認可的方式，建議在匯入前執行SET AUTOCOMMIT=0，關閉自動認可，匯入結束後再執行SET AUTOCOMMIT=1，開啟自動認可，也可以提高匯入的效率。

而我建立的是Innodb類型的表，分了128個分區。而我依照以上的方法，設定如下：

插入百萬資料的SP如下：

可以明顯的看到插入百萬資料是100S左右，速度提升了33倍之多。

速度是提升了不少，那就加大插入的資料量，提升10倍，即插入千萬的資料量，具體的SP如下：

可以看到時間差不多是1200s左右，因為欄位加長了，可能也有影響插入的速度。

為了具體驗證，就按千萬行插入，欄位的長度為1000位元組，來查看結果，具體的SP和結果如下：

可以看到用時33min 51s月（約2031秒），即（4900row/s），速度下降很多，字元長度看來是用影響的。

varchar欄位

欄位的限制在欄位定義的時候有以下規則： 
a) 儲存限制 
varchar 欄位是將實際內容單獨儲存在聚簇索引之外，內容開頭用1到2個位元組表示實際長度（長度超過255時需要2個位元組），因此最大長度不能超過65535。 
b) 編碼長度限制 
字元類型若為gbk，每個字元最多佔2個位元組，最大長度不能超過32766; 
　　字元類型若為utf8，每個字元最多佔3個位元組，最大長度不能超過21845。 
　　對於英文比較多的論壇 ，使用GBK則每個字元佔用2個位元組，而使用UTF－8英文卻只佔一個位元組。 
　　若定義的時候超過上述限制，則varchar欄位會被強行轉為text類型，併產生warning。 
c) 行長度限制 
　　導致實際應用中varchar長度限制的是一個行定義的長度。 MySQL要求一個行的定義長度不能超過65535。若定義的表長度超過這個值，則提示 
　　ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to 　　　change some columns to TEXT or BLOBs。 
2、計算例子 
　　舉兩個例說明一下實際長度的計算。 
a) 若一個表只有一個varchar類型，如定義為 
create table t4(c varchar(N)) charset=gbk; 
則此處N的最大值為(65535-1-2)/2= 32766。 
減1的原因是實際行儲存從第二個位元組開始‘; 
減2的原因是varchar頭部的2個位元組表示長度; 
除2的原因是字元編碼是gbk。 

b) 若一個表定義為 
create table t4(c int, c2 char(30), c3 varchar(N)) charset=utf8; 
則此處N的最大值為 (65535-1-2-4-30*3)/3=21812 
減1和減2與上例相同; 
減4的原因是int類型的c佔4個位元組; 
減30*3的原因是char(30)佔用90個位元組，編碼是utf8。 
如果被varchar超過上述的b規則，被強轉成text類型，則每個欄位佔用定義長度為11位元組，當然這已經不是“varchar”了。

在mysql 中用"SHOW VARIABLES LIKE ‘%CHAR%‘"查看字元集：

再次升級插入的資料量，提升10倍，看插入的時間及佔用的記憶體，欄位的位元組同樣為1000，具體的SP和結果如下：

從可以清楚的看到，插入1億條資料的時間為5hours +20 min+ 56s=19256s，平均插入的條數為（5193 rows/s）。根上次插入1千萬條的時間差不多，再看所耗磁碟空間，用了98G的空間，跟上次插入千萬條資料時的（26G-17G=9G）也是成線性關係的。按照本機500G的磁碟空間，儲存1行1K位元組大小的資料，本機可以儲存理想極限情況下為5億條資料，保守為4~4.5億左右合適，以防其他的應用或者資料庫的UNDO，索引空間佔用。

最後再看一次查詢的時間，上次插入百萬數，查詢資料量的時間

因為建立了索引，在查百萬級的資料量時，時間是1秒左右，在資料量上升到千萬時，查詢1億5百萬時，時間為3Min 30S,再插入1億資料，查詢資料量，時間達到27min 43s,可見，不是線性關係，是幾何級增加的。

=====================================================================================================

現在描述叢集環境的測試

叢集：32G記憶體 ，500G硬碟，3台虛擬機器也就是3個節點：188.188.2.181（主節點，資料節點和SQL節點）、188.188.2.182（資料節點和SQL節點）和188.188.2.183（資料節點和SQL節點）。/root目錄分割磁碟空間200G（原先預設的是50G）、插入的資料量為8000KW，所佔磁碟空間為

插入前的記憶體：

插入後記憶體：

資料記憶體所佔空間：(910051-5)*32K=27.77G  -----300W條/G

索引記憶體所佔空間：54980*8K=430M

插入前的磁碟空間：

插入後的磁碟空間：

磁碟空間：200G*（34%-5%）=58G    -----143W條/G

插入前的條數：

插入後的條數：

條數的：82551267條

MYSQL 大批量資料插入