怎樣進(jìn)行MySQL中的批量初始化數(shù)據(jù)的對(duì)比測(cè)試

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

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

我大體測(cè)試了一下，以100萬數(shù)據(jù)為基準(zhǔn)，初始化性能的提升會(huì)從近8分鐘提升到10多秒鐘。

方案1：存儲(chǔ)過程導(dǎo)入

我們測(cè)試使用的表為users，InnoDB存儲(chǔ)引擎，計(jì)劃初始化數(shù)據(jù)為100萬。

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

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();因?yàn)槲覍?duì)這個(gè)過程還是信心不足，所以就抓取了十分之一的數(shù)據(jù)10萬條數(shù)據(jù)，測(cè)試的結(jié)果是執(zhí)行了47秒鐘左右，按照這個(gè)數(shù)據(jù)量大概需要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)    所以這個(gè)過程雖然是一步到位，但是性能還是差強(qiáng)人意，我看有 的同學(xué)在不同的配置下性能差別很大，有的同學(xué)達(dá)到了近50分鐘。這一點(diǎn)上以自己的測(cè)試環(huán)境為準(zhǔn)，然后能夠得到一個(gè)梯度的數(shù)據(jù)就可以了。

   我們來看看第二個(gè)方案。

方案2：使用內(nèi)存表

第二個(gè)方案，我們嘗試使用內(nèi)存表來優(yōu)化，這樣一來我們就需要?jiǎng)?chuàng)建一個(gè)內(nèi)存表，比如名叫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;然后使用如下的存儲(chǔ)過程來導(dǎo)入數(shù)據(jù)，其實(shí)邏輯和第一個(gè)存儲(chǔ)過程幾乎一樣，就表名不一樣而已，這個(gè)里面數(shù)據(jù)是入到內(nèi)存表中。

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 ;這個(gè)過程可能會(huì)拋出table is full相關(guān)的信息，我們可以適當(dāng)調(diào)整參數(shù)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)這個(gè)過程用時(shí)近5分鐘，剩下的內(nèi)存表數(shù)據(jù)導(dǎo)入InnoDB表很快了，幾秒鐘即可搞定。
> insert into users select *from users_memory;
整個(gè)過程下來不到5分鐘，和第一種方案相比快了很多。

方案3：使用程序/腳本生成數(shù)據(jù)，批量導(dǎo)入

第三種方案只是拋磚引玉，如果你對(duì)php熟悉，可以完全用php來寫，對(duì)哪種語言腳本熟悉，只要實(shí)現(xiàn)需求即可。比如我使用shell，也沒有使用什么特別的技巧。
shell腳本內(nèi)容如下：

for i in {1..1000000}
do
echo  $i,user_$i
done > a.lst腳本寫得很簡(jiǎn)單，生成數(shù)據(jù)的過程大概耗時(shí)8秒鐘，文件有18M左右。

# time sh a.sh
real    0m8.366s
user    0m6.312s
sys     0m2.039s然后使用load data來導(dǎo)入數(shù)據(jù)，整個(gè)過程花費(fèi)時(shí)間大概在8秒鐘左右，所以整個(gè)過程的時(shí)間在19秒以內(nèi)。

> 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：內(nèi)存表,外部文件導(dǎo)入混合

第四種方案是臨時(shí)想的，也是結(jié)合了這幾種方案的一些特點(diǎn)，當(dāng)然不能說它就是最好的。

首先使用腳本生成數(shù)據(jù)，還是和方案3一樣，估算為9秒鐘，導(dǎo)入數(shù)據(jù)到內(nèi)存表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然后把內(nèi)存表的數(shù)據(jù)導(dǎo)入目標(biāo)表users

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

上述內(nèi)容就是怎樣進(jìn)行MySQL中的批量初始化數(shù)據(jù)的對(duì)比測(cè)試，你們學(xué)到知識(shí)或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備，歡迎關(guān)注億速云行業(yè)資訊頻道。