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

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

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

?View Code

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

普通表參數(shù)設置和內(nèi)存表相同，否則從內(nèi)存表往普通標導入數(shù)據(jù)會報錯。

?View Code

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

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

?View Code

4、創(chuàng)建存儲過程

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

?View Code

 5、調(diào)用存儲過程

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

CALL add_vote_memory(1000000)

6、導入數(shù)據(jù)

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

INSERT into vote_record SELECT * from vote_record_memory

7、內(nèi)存不足

如果報錯內(nèi)存滿了，報錯信息如下：

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

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

show variables like '%%table_size%'

MySQL默認16M：

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

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

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

8、查看結(jié)果

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

select count(*) AS total from vote_record

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

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

 查看結(jié)果：

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

二、MySQL深度分頁

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

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會把前210條數(shù)據(jù)都查出來，拋棄前200條，返回10條。當分頁所以深度不大的時候當然沒問題，隨著分頁的深入，sql可能會變成這樣：

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

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

那如何解決呢？一般情況下，最簡單的方式是增加一個條件，利用表的覆蓋索引來加速分頁查詢：

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

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

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

1、測試深度分頁

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

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

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

最新評論