MySQL如何快速的創(chuàng)建千萬級(jí)測(cè)試數(shù)據(jù)
備注：
此文章的數(shù)據(jù)量在100W，如果想要千萬級(jí)，調(diào)大數(shù)量即可,但是不要大量使用rand() 或者uuid() 會(huì)導(dǎo)致性能下降
背景
在進(jìn)行查詢操作的性能測(cè)試或者sql優(yōu)化時(shí)，我們經(jīng)常需要在線下環(huán)境構(gòu)建大量的基礎(chǔ)數(shù)據(jù)供我們測(cè)試，模擬線上的真實(shí)環(huán)境。
廢話，總不能讓我去線上去測(cè)試吧，會(huì)被DBA砍死的
創(chuàng)建測(cè)試數(shù)據(jù)的方式
1. 編寫代碼，通過代碼批量插庫(kù)（本人使用過，步驟太繁瑣，性能不高，不推薦）
2. 編寫存儲(chǔ)過程和函數(shù)執(zhí)行（本文實(shí)現(xiàn)方式1）
3. 臨時(shí)數(shù)據(jù)表方式執(zhí)行 （本文實(shí)現(xiàn)方式2，強(qiáng)烈推薦該方式，非常簡(jiǎn)單，數(shù)據(jù)插入快速，100W，只需幾秒）
4. 一行一行手動(dòng)插入，（WTF，去死吧）
創(chuàng)建基礎(chǔ)表結(jié)構(gòu)
不管用何種方式，我要插在那張表總要?jiǎng)?chuàng)建的吧
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： 采用存儲(chǔ)過程和內(nèi)存表
創(chuàng)建內(nèi)存表
利用 MySQL 內(nèi)存表插入速度快的特點(diǎn)，我們先利用函數(shù)和存儲(chǔ)過程在內(nèi)存表中生成數(shù)據(jù)，然后再?gòu)膬?nèi)存表插入普通表中
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;
創(chuàng)建函數(shù)和存儲(chǔ)過程
# 創(chuàng)建隨機(jī)字符串和隨機(jī)時(shí)間的函數(shù) 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 ; # 創(chuàng)建插入數(shù)據(jù)存儲(chǔ)過程 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)
調(diào)用存儲(chǔ)過程
mysql> CALL add_t_user_memory(1000000); ERROR 1114 (HY000): The table 't_user_memory' is full
出現(xiàn)內(nèi)存已滿時(shí)，修改 max_heap_table_size 參數(shù)的大小，我使用64M內(nèi)存，插入了22W數(shù)據(jù)，看情況改，不過這個(gè)值不要太大，默認(rèn)32M或者64M就好，生產(chǎn)環(huán)境不要亂嘗試
從內(nèi)存表插入普通表
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： 采用臨時(shí)表
創(chuàng)建臨時(shí)數(shù)據(jù)表tmp_table
CREATE TABLE tmp_table ( id INT, PRIMARY KEY (id) );
用 python或者bash 生成 100w 記錄的數(shù)據(jù)文件（python瞬間就會(huì)生成完）
python(推薦): python -c "for i in range(1, 1+1000000): print(i)" > base.txt
bash(不推薦，會(huì)比較慢): bash i=1; while [ $i -le 1000000 ]; do echo $i; let i+=1; done > base.txt
導(dǎo)入數(shù)據(jù)到臨時(shí)表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
千萬級(jí)數(shù)據(jù) 20秒插入完成
注意： 導(dǎo)入數(shù)據(jù)時(shí)有可能會(huì)報(bào)錯(cuò)，原因是mysql默認(rèn)沒有開secure_file_priv（ 這個(gè)參數(shù)用來限制數(shù)據(jù)導(dǎo)入和導(dǎo)出操作的效果，例如執(zhí)行LOAD DATA、SELECT … INTO OUTFILE語句和LOAD_FILE()函數(shù)。這些操作需要用戶具有FILE權(quán)限。 ）
解決辦法：在mysql的配置文件中（my.ini 或者 my.conf）中添加 secure_file_priv = /Users/LJTjintao/temp/` , 然后重啟mysql 解決
以臨時(shí)表為基礎(chǔ)數(shù)據(jù)，插入數(shù)據(jù)到t_user中，100W數(shù)據(jù)插入需要10.37s
mysql> INSERT INTO t_user
-> SELECT
-> id,
-> uuid(),
-> CONCAT('userNickName', id),
-> FLOOR(Rand() * 1000),
-> FLOOR(Rand() * 100),
-> NOW()
-> FROM
-> tmp_table;
Query OK, 1000000 rows affected (10.37 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
更新創(chuàng)建時(shí)間字段讓插入的數(shù)據(jù)的創(chuàng)建時(shí)間更加隨機(jī)
UPDATE t_user SET create_time=date_add(create_time, interval FLOOR(1 + (RAND() * 7)) year); Query OK, 1000000 rows affected (5.21 sec) Rows matched: 1000000 Changed: 1000000 Warnings: 0 mysql> UPDATE t_user SET create_time=date_add(create_time, interval FLOOR(1 + (RAND() * 7)) year); Query OK, 1000000 rows affected (4.77 sec) Rows matched: 1000000 Changed: 1000000 Warnings: 0
mysql> select * from t_user limit 30; +----+--------------------------------------+----------------+---------------+-----------+---------------------+ | id | c_user_id | c_name | c_province_id | c_city_id | create_time | +----+--------------------------------------+----------------+---------------+-----------+---------------------+ | 1 | bf5e227a-7b84-11e9-9d6e-751d319e85c2 | userNickName1 | 84 | 64 | 2015-11-13 21:13:19 | | 2 | bf5e26f8-7b84-11e9-9d6e-751d319e85c2 | userNickName2 | 967 | 90 | 2019-11-13 20:19:33 | | 3 | bf5e2810-7b84-11e9-9d6e-751d319e85c2 | userNickName3 | 623 | 40 | 2014-11-13 20:57:46 | | 4 | bf5e2888-7b84-11e9-9d6e-751d319e85c2 | userNickName4 | 140 | 49 | 2016-11-13 20:50:11 | | 5 | bf5e28f6-7b84-11e9-9d6e-751d319e85c2 | userNickName5 | 47 | 75 | 2016-11-13 21:17:38 | | 6 | bf5e295a-7b84-11e9-9d6e-751d319e85c2 | userNickName6 | 642 | 94 | 2015-11-13 20:57:36 | | 7 | bf5e29be-7b84-11e9-9d6e-751d319e85c2 | userNickName7 | 780 | 7 | 2015-11-13 20:55:07 | | 8 | bf5e2a4a-7b84-11e9-9d6e-751d319e85c2 | userNickName8 | 39 | 96 | 2017-11-13 21:42:46 | | 9 | bf5e2b58-7b84-11e9-9d6e-751d319e85c2 | userNickName9 | 731 | 74 | 2015-11-13 22:48:30 | | 10 | bf5e2bb2-7b84-11e9-9d6e-751d319e85c2 | userNickName10 | 534 | 43 | 2016-11-13 22:54:10 | | 11 | bf5e2c16-7b84-11e9-9d6e-751d319e85c2 | userNickName11 | 572 | 55 | 2018-11-13 20:05:19 | | 12 | bf5e2c70-7b84-11e9-9d6e-751d319e85c2 | userNickName12 | 71 | 68 | 2014-11-13 20:44:04 | | 13 | bf5e2cca-7b84-11e9-9d6e-751d319e85c2 | userNickName13 | 204 | 97 | 2019-11-13 20:24:23 | | 14 | bf5e2d2e-7b84-11e9-9d6e-751d319e85c2 | userNickName14 | 249 | 32 | 2019-11-13 22:49:43 | | 15 | bf5e2d88-7b84-11e9-9d6e-751d319e85c2 | userNickName15 | 900 | 51 | 2019-11-13 20:55:26 | | 16 | bf5e2dec-7b84-11e9-9d6e-751d319e85c2 | userNickName16 | 854 | 74 | 2018-11-13 22:07:58 | | 17 | bf5e2e50-7b84-11e9-9d6e-751d319e85c2 | userNickName17 | 136 | 46 | 2013-11-13 21:53:34 | | 18 | bf5e2eb4-7b84-11e9-9d6e-751d319e85c2 | userNickName18 | 897 | 10 | 2018-11-13 20:03:55 | | 19 | bf5e2f0e-7b84-11e9-9d6e-751d319e85c2 | userNickName19 | 829 | 83 | 2013-11-13 20:38:54 | | 20 | bf5e2f68-7b84-11e9-9d6e-751d319e85c2 | userNickName20 | 683 | 91 | 2019-11-13 20:02:42 | | 21 | bf5e2fcc-7b84-11e9-9d6e-751d319e85c2 | userNickName21 | 511 | 81 | 2013-11-13 21:16:48 | | 22 | bf5e3026-7b84-11e9-9d6e-751d319e85c2 | userNickName22 | 562 | 35 | 2019-11-13 20:15:52 | | 23 | bf5e3080-7b84-11e9-9d6e-751d319e85c2 | userNickName23 | 91 | 39 | 2016-11-13 20:28:59 | | 24 | bf5e30da-7b84-11e9-9d6e-751d319e85c2 | userNickName24 | 677 | 21 | 2016-11-13 21:37:15 | | 25 | bf5e3134-7b84-11e9-9d6e-751d319e85c2 | userNickName25 | 50 | 60 | 2018-11-13 20:39:20 | | 26 | bf5e318e-7b84-11e9-9d6e-751d319e85c2 | userNickName26 | 856 | 47 | 2018-11-13 21:24:53 | | 27 | bf5e31e8-7b84-11e9-9d6e-751d319e85c2 | userNickName27 | 816 | 65 | 2014-11-13 22:06:26 | | 28 | bf5e324c-7b84-11e9-9d6e-751d319e85c2 | userNickName28 | 806 | 7 | 2019-11-13 20:17:30 | | 29 | bf5e32a6-7b84-11e9-9d6e-751d319e85c2 | userNickName29 | 973 | 63 | 2014-11-13 21:08:09 | | 30 | bf5e3300-7b84-11e9-9d6e-751d319e85c2 | userNickName30 | 237 | 29 | 2018-11-13 21:48:17 | +----+--------------------------------------+----------------+---------------+-----------+---------------------+ 30 rows in set (0.01 sec)
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)本站的支持。
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信