MySQL 快速創建千萬級測試數據

作者：IKNOW本尊
http://juejin.im/post/5ce372c36fb9a07ef63fb191

推薦閱讀(點擊即可跳轉閱讀)

1. SpringBoot內容聚合

2. 面試題內容聚合

3. 設計模式內容聚合

4. 排序演算法內容聚合

5. 多線程內容聚合

備註：此文章的數據量在100W，如果想要千萬級，調大數量即可,但是不要大量使用rand() 或者uuid() 會導致性能下降

背景

在進行查詢操作的性能測試或者sql優化時，我們經常需要在線下環境構建大量的基礎數據供我們測試，模擬線上的真實環境。

廢話，總不能讓我去線上去測試吧，會被DBA砍死的

創建測試數據的方式

編寫代碼，通過代碼批量插庫（本人使用過，步驟太繁瑣，性能不高，不推薦）
編寫存儲過程和函數執行（本文實現方式1）
臨時數據表方式執行（本文實現方式2，強烈推薦該方式，非常簡單，數據插入快速，100W，只需幾秒）
一行一行手動插入，（WTF，去死吧）

創建基礎表結構

不管用何種方式，我要插在那張表總要創建的吧

CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c_user_id` varchar(36) NOT NULL DEFAULT , `c_name` varchar(22) NOT NULL DEFAULT , `c_province_id` int(11) NOT NULL, `c_city_id` int(11) NOT NULL, `create_time` datetime NOT NULL, PRIMARY KEY (`id`), KEY `idx_user_id` (`c_user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

方式1：採用存儲過程和內存表

創建內存表

利用 MySQL 內存表插入速度快的特點，我們先利用函數和存儲過程在內存表中生成數據，然後再從內存表插入普通表中

CREATE TABLE `t_user_memory` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c_user_id` varchar(36) NOT NULL DEFAULT , `c_name` varchar(22) NOT NULL DEFAULT , `c_province_id` int(11) NOT NULL, `c_city_id` int(11) NOT NULL, `create_time` datetime NOT NULL, PRIMARY KEY (`id`), KEY `idx_user_id` (`c_user_id`) ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

創建函數和存儲過程

# 創建隨機字元串和隨機時間的函數 mysql> delimiter $$ mysql> CREATE DEFINER=`root`@`%` FUNCTION `randStr`(n INT) RETURNS varchar(255) CHARSET utf8mb4 -> DETERMINISTIC -> BEGIN -> DECLARE chars_str varchar(100) DEFAULT abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789; -> DECLARE return_str varchar(255) DEFAULT ; -> DECLARE i INT DEFAULT 0; -> WHILE i < n DO -> SET return_str = concat(return_str, substring(chars_str, FLOOR(1 + RAND() * 62), 1)); -> SET i = i + 1; -> END WHILE; -> RETURN return_str; -> END$$ Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DEFINER=`root`@`%` FUNCTION `randDataTime`(sd DATETIME,ed DATETIME) RETURNS datetime
-> DETERMINISTIC
-> BEGIN
-> DECLARE sub INT DEFAULT 0;
-> DECLARE ret DATETIME;
-> SET sub = ABS(UNIX_TIMESTAMP(ed)-UNIX_TIMESTAMP(sd));
-> SET ret = DATE_ADD(sd,INTERVAL FLOOR(1+RAND()*(sub-1)) SECOND);
-> RETURN ret;
-> END $$

mysql> delimiter ;

# 創建插入數據存儲過程
mysql> CREATE DEFINER=`root`@`%` PROCEDURE `add_t_user_memory`(IN n int)
-> BEGIN
-> DECLARE i INT DEFAULT 1;
-> WHILE (i <= n) DO
-> INSERT INTO t_user_memory (c_user_id, c_name, c_province_id,c_city_id, create_time) VALUES (uuid(), randStr(20), FLOOR(RAND() * 1000), FLOOR(RAND() * 100), NOW());
-> SET i = i + 1;
-> END WHILE;
-> END
-> $$
Query OK, 0 rows affected (0.01 sec)

調用存儲過程

mysql> CALL add_t_user_memory(1000000); ERROR 1114 (HY000): The table t_user_memory is full

出現內存已滿時，修改 max_heap_table_size 參數的大小，我使用64M內存，插入了22W數據，看情況改，不過這個值不要太大，默認32M或者64M就好，生產環境不要亂嘗試

從內存表插入普通表

mysql> INSERT INTO t_user SELECT * FROM t_user_memory; Query OK, 218953 rows affected (1.70 sec) Records: 218953 Duplicates: 0 Warnings: 0

方式2：採用臨時表

創建臨時數據表tmp_table

CREATE TABLE tmp_table ( id INT, PRIMARY KEY (id) );

用 python或者bash 生成 100w 記錄的數據文件（python瞬間就會生成完）

python(推薦):

python -c "for i in range(1, 1+1000000): print(i)" > base.txt

導入數據到臨時表tmp_table中

mysql> load data infile /Users/LJTjintao/temp/base.txt replace into table tmp_table; Query OK, 1000000 rows affected (2.55 sec) Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 0

千萬級數據 20秒插入完成

注意：導入數據時有可能會報錯，原因是mysql默認沒有開secure_file_priv（這個參數用來限制數據導入和導出操作的效果，例如執行LOAD DATA、SELECT … INTO OUTFILE語句和LOAD_FILE()函數。這些操作需要用戶具有FILE許可權。）

解決辦法：在mysql的配置文件中（my.ini 或者 my.conf）中添加 secure_file_priv = /Users/LJTjintao/temp/`, 然後重啟mysql 解決