MySQL中的批量初始化資料的對比測試(r12筆記第71天)

  一直以來對於MySQL的儲存過程效能還是頗有微詞的，說實話夠慢的。有時候想做一些對比測試，儲存過程初始化幾萬條資料都得好一會兒，這功夫Oracle類似的測試早都做完了，今天就趕個晚班車，把這個沒做完的任務完成了。

    我大體測試了一下，以100萬資料為基準，初始化效能的提升會從近8分鐘提升到10多秒鐘。

     我自己嘗試了以下4種方案。

     1.儲存過程批量匯入（近8分鐘）

     2.儲存過程批量匯入記憶體表，記憶體表匯入目標表（近5分鐘）

     3.使用shell指令碼生成資料，使用load data的方式匯入資料（近20秒）

     4.使用shell指令碼生成資料，使用load data的方式匯入記憶體表，記憶體表資料匯入目標表（近18秒）

方案1：儲存過程匯入

我們測試使用的表為users，InnoDB儲存引擎，計劃初始化資料為100萬。

create table users(
userid int(11) unsigned not null,
user_name varchar(64) default null,
primary key(userid)
)engine=innodb default charset=UTF8;使用如下的方式來初始化資料，我們就使用儲存過程的方式。

delimiter $$
drop procedure if exists proc_auto_insertdata$$
create procedure proc_auto_insertdata()
begin
    declare
    init_data integer default 1;
    while init_data<=100000 do
    insert into users values(init_data,concat('user'    ,init_data));
    set init_data=init_data+1;
    end while;
end$$
delimiter ;
call proc_auto_insertdata();因為我對這個過程還是信心不足，所以就抓取了十分之一的資料10萬條資料，測試的結果是執行了47秒鐘左右，按照這個資料量大概需要8分鐘左右。
> source create_proc.sql
Query OK, 0 rows affected, 1 warning (0.04 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 1 row affected (47.41 sec)    所以這個過程雖然是一步到位，但是效能還是差強人意，我看有 的同學在不同的配置下效能差別很大，有的同學達到了近50分鐘。這一點上以自己的測試環境為準，然後能夠得到一個梯度的資料就可以了。

   我們來看看第二個方案。

方案2：使用記憶體表

第二個方案，我們嘗試使用記憶體表來優化，這樣一來我們就需要建立一個記憶體表，比如名叫users_memory。

create table users_memory(
userid int(11) unsigned not null,
user_name varchar(64) default null,
primary key(userid)
)engine=memory default charset=UTF8;然後使用如下的儲存過程來匯入資料，其實邏輯和第一個儲存過程幾乎一樣，就表名不一樣而已，這個裡面資料是入到記憶體表中。

delimiter $$
drop procedure if exists proc_auto_insertdata$$
create procedure proc_auto_insertdata()
begin
    declare
    init_data integer default 1;
    while init_data<=1000000 do
    insert into users_memory values(init_data,concat('user'    ,init_data));
    set init_data=init_data+1;
    end while;
end$$
delimiter ;
call proc_auto_insertdata ;這個過程可能會丟擲table is full相關的資訊，我們可以適當調整引數tmpdir（修改需要重啟）,max_heap_table_size（線上修改），然後重試基本就可以了。
> source create_proc_mem.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (4 min 40.23 sec)這個過程用時近5分鐘，剩下的記憶體表資料匯入InnoDB表很快了，幾秒鐘即可搞定。
> insert into users select *from users_memory;
整個過程下來不到5分鐘，和第一種方案相比快了很多。

方案3：使用程式/指令碼生成資料，批量匯入

第三種方案只是拋磚引玉，如果你對php熟悉，可以完全用php來寫，對哪種語言指令碼熟悉，只要實現需求即可。比如我使用shell，也沒有使用什麼特別的技巧。
shell指令碼內容如下：

for i in {1..1000000}
do
echo  $i,user_$i
done > a.lst指令碼寫得很簡單，生成資料的過程大概耗時8秒鐘，檔案有18M左右。

# time sh a.sh
real    0m8.366s
user    0m6.312s
sys     0m2.039s然後使用load data來匯入資料，整個過程花費時間大概在8秒鐘左右，所以整個過程的時間在19秒以內。

> load data infile '/U01/testdata/a.lst'  into table users fields terminated by ','  ;
Query OK, 1000000 rows affected (8.05 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

方案4：記憶體表,外部檔案匯入混合

第四種方案是臨時想的，也是結合了這幾種方案的一些特點，當然不能說它就是最好的。

首先使用指令碼生成資料，還是和方案3一樣，估算為9秒鐘，匯入資料到記憶體表users_memory裡面。

> load data infile '/U01/testdata/a.lst'  into table users_memory fields terminated by ','  ;
Query OK, 1000000 rows affected (1.91 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0然後把記憶體表的資料匯入目標表users

> insert into users select *from users_memory;                                   
Query OK, 1000000 rows affected (7.48 sec)
Records: 1000000  Duplicates: 0  Warnings: 0整個過程耗時在18秒，和第三種方案很相似，看起來略微複雜了或者囉嗦了一些。

   以上幾種方案只是個人的一些簡單測試總結，如果你有好的方案，希望多提意見，多多溝通。

掃碼或者長按如下的圖片都可以關注我的公眾號，繼續努力中。。。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-2139656/，如需轉載，請註明出處，否則將追究法律責任。