怎樣進行MySQL中的批量初始化數據的對比測試

本篇文章為大家展示了怎樣進行MySQL中的批量初始化數據的對比測試，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

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

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

方案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秒，和第三種方案很相似，看起來略微復雜了或者啰嗦了一些。

上述內容就是怎樣進行MySQL中的批量初始化數據的對比測試，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。