一、數(shù)據(jù)插入思路

如果一條一條插入普通表的話，效率太低下，但內(nèi)存表插入速度是很快的，可以先建立一張內(nèi)存表，插入數(shù)據(jù)后，在導(dǎo)入到普通表中。

1、創(chuàng)建內(nèi)存表

?View Code

2、創(chuàng)建普通表

普通表參數(shù)設(shè)置和內(nèi)存表相同，否則從內(nèi)存表往普通標(biāo)導(dǎo)入數(shù)據(jù)會(huì)報(bào)錯(cuò)。

?View Code

3、創(chuàng)建存儲(chǔ)函數(shù)

產(chǎn)生偽隨機(jī)碼user_id 要用到存儲(chǔ)函數(shù)。

?View Code

4、創(chuàng)建存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程是保存起來(lái)的可以接受和返回用戶提供的參數(shù)的Transact-SQL 語(yǔ)句的集合，可以創(chuàng)建一個(gè)過(guò)程供永久使用。

?View Code

 5、調(diào)用存儲(chǔ)過(guò)程

call 就是調(diào)用存儲(chǔ)過(guò)程或者函數(shù)，這里調(diào)用存儲(chǔ)過(guò)程1000000次

CALL add_vote_memory(1000000)

6、導(dǎo)入數(shù)據(jù)

將內(nèi)存表中的數(shù)據(jù)導(dǎo)入普通表。

INSERT into vote_record SELECT * from vote_record_memory

7、內(nèi)存不足

如果報(bào)錯(cuò)內(nèi)存滿了，報(bào)錯(cuò)信息如下：

?CALL add_vote_memory(1000000)
> 1114 - The table 'vote_record_memory' is full
?> 時(shí)間: 74.61s

則可以使用命令查看內(nèi)存表和臨時(shí)表允許寫入的最大值：

show variables like '%%table_size%'

MySQL默認(rèn)16M：

 修改默認(rèn)內(nèi)存配置：

set session tmp_table_size=1024*1024*1024;?
set session max_heap_table_size=1024*1024*1024;?

配置修改后，再執(zhí)行上述調(diào)用存儲(chǔ)過(guò)程和數(shù)據(jù)導(dǎo)入步驟。

8、查看結(jié)果

查看記錄，是否有插入100W條數(shù)據(jù)。

select count(*) AS total from vote_record

9、插入800W條數(shù)據(jù)

測(cè)試插入800W條數(shù)據(jù)，call 調(diào)用存儲(chǔ)過(guò)程800W次。

 查看結(jié)果：

 800W條測(cè)試數(shù)據(jù)插入OK，想插入多少條測(cè)試數(shù)據(jù)就調(diào)用n次存儲(chǔ)過(guò)程，CALL add_vote_memory(n)。

二、MySQL深度分頁(yè)

所謂的深度分頁(yè)問(wèn)題，涉及到mysql分頁(yè)的原理。通常情況下，mysql的分頁(yè)是這樣寫的：

select id, user_id, vote_id, group_id from vote_record limit 200, 10

SQL意思就是從vote_reccord 表里查200到210這10條數(shù)據(jù)即【201,210】，mysql會(huì)把前210條數(shù)據(jù)都查出來(lái)，拋棄前200條，返回10條。當(dāng)分頁(yè)所以深度不大的時(shí)候當(dāng)然沒(méi)問(wèn)題，隨著分頁(yè)的深入，sql可能會(huì)變成這樣：

select id, user_id, vote_id, group_id from vote_record limit 7999900, 10

這個(gè)時(shí)候，mysql會(huì)查出來(lái)7999920條數(shù)據(jù)，拋棄前7999900條，如此大的數(shù)據(jù)量，速度一定快不起來(lái)。

那如何解決呢？一般情況下，最簡(jiǎn)單的方式是增加一個(gè)條件，利用表的覆蓋索引來(lái)加速分頁(yè)查詢：

select id, user_id, vote_id, group_id from vote_record where id > 7999900 limit 10

我們都知道，利用了索引查詢的語(yǔ)句中如果只包含了那個(gè)索引列（覆蓋索引），那么這種情況會(huì)查詢很快。

因?yàn)槔盟饕檎矣袃?yōu)化算法，且數(shù)據(jù)就在查詢索引上面，不用再去找相關(guān)的數(shù)據(jù)地址了，這樣節(jié)省了很多時(shí)間。上述vote_record 表的id字段是主鍵，自然就包含了默認(rèn)的主鍵索引，這樣，mysql會(huì)走主鍵索引，直接連接到7999900處，然后查出來(lái)10條數(shù)據(jù)。但是這個(gè)方式需要接口的調(diào)用方配合改造，把上次查詢出來(lái)的最大id以參數(shù)的方式傳給接口提供方，會(huì)有一定溝通成本。

1、測(cè)試深度分頁(yè)

優(yōu)化前，查詢耗時(shí)2.362s，隨著數(shù)據(jù)的增大耗時(shí)會(huì)更多，limit語(yǔ)句的查詢時(shí)間與起始記錄的位置成正比。

 優(yōu)化后，耗時(shí)0.012s，性能提升了196.8倍。

到此這篇關(guān)于MySQL如何快速創(chuàng)建800w條測(cè)試數(shù)據(jù)表的文章就介紹到這了,更多相關(guān)MySQL創(chuàng)建測(cè)試數(shù)據(jù)表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！