MySQL如何快速建立800w條測試資料表

2022-03-17 10:00:08

一、資料插入思路

如果一條一條插入普通表的話，效率太低下，但記憶體表插入速度是很快的，可以先建立一張記憶體表，插入資料後，在匯入到普通表中。

1、建立記憶體表

 View Code

2、建立普通表

普通表引數設定和記憶體表相同，否則從記憶體表往普通標匯入資料會報錯。

 View Code

3、建立儲存函數

產生偽隨機碼user_id 要用到儲存函數。

 View Code

4、建立儲存過程

儲存過程是儲存起來的可以接受和返回使用者提供的引數的Transact-SQL 語句的集合，可以建立一個過程供永久使用。

 View Code

5、呼叫儲存過程

call 就是呼叫儲存過程或者函數，這裡呼叫儲存過程1000000次

CALL add_vote_memory(1000000)

6、匯入資料

將記憶體表中的資料匯入普通表。

INSERT into vote_record SELECT * from vote_record_memory

7、記憶體不足

如果報錯記憶體滿了，報錯資訊如下：

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

則可以使用命令檢視記憶體表和臨時表允許寫入的最大值：

show variables like '%%table_size%'

MySQL預設16M：

修改預設記憶體設定：

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

設定修改後，再執行上述呼叫儲存過程和資料匯入步驟。

8、檢視結果

檢視記錄，是否有插入100W條資料。

select count(*) AS total from vote_record

9、插入800W條資料

測試插入800W條資料，call 呼叫儲存過程800W次。

檢視結果：

800W條測試資料插入OK，想插入多少條測試資料就呼叫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條資料即【201,210】，mysql會把前210條資料都查出來，拋棄前200條，返回10條。當分頁所以深度不大的時候當然沒問題，隨著分頁的深入，sql可能會變成這樣：

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

這個時候，mysql會查出來7999920條資料，拋棄前7999900條，如此大的資料量，速度一定快不起來。

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

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

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

因為利用索引查詢有優化演演算法，且資料就在查詢索引上面，不用再去找相關的資料地址了，這樣節省了很多時間。上述vote_record 表的id欄位是主鍵，自然就包含了預設的主鍵索引，這樣，mysql會走主鍵索引，直接連線到7999900處，然後查出來10條資料。但是這個方式需要介面的呼叫方配合改造，把上次查詢出來的最大id以引數的方式傳給介面提供方，會有一定溝通成本。