MySQL erstellt schnell zig Millionen Testdaten

Hinweis: Das Datenvolumen dieses Artikels beträgt 100 W. Wenn Sie mehrere zehn Millionen benötigen, erhöhen Sie einfach die Menge. Verwenden Sie rand() oder uuid() jedoch nicht in großen Mengen , was zu Leistungseinbußen führt.

Hintergrund

Bei der Durchführung von Leistungstests von Abfragevorgängen oder der SQL-Optimierung müssen wir für unsere Tests häufig eine große Menge grundlegender Daten in der Offline-Umgebung erstellen Simulieren Sie die reale Online-Umgebung.

Unsinn, du kannst mich nicht online testen lassen, ich werde vom DBA zu Tode gehackt

Wie man Testdaten erstellt

    1. 编写代码，通过代码批量插库（本人使用过，步骤太繁琐，性能不高，不推荐）
    2. 编写存储过程和函数执行（本文实现方式1）
    3. 临时数据表方式执行 （本文实现方式2，强烈推荐该方式，非常简单，数据插入快速，100W，只需几秒）
    4. 一行一行手动插入，（WTF，去死吧）

Erstellen Sie die Grundlagen der Tabellenstruktur

Egal welche Methode verwendet wird, die Tabelle, die ich einfügen möchte, muss erstellt werden

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;

Methode 1: Gespeicherte Prozeduren und Speichertabellen verwenden

• Speichertabelle erstellen

利用 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;

• Funktionen und gespeicherte Prozeduren erstellen

# 创建随机字符串和随机时间的函数
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          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          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)

• Gespeicherte Prozedur aufrufen

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就好，生产环境不要乱尝试

• In gewöhnliche Tabelle aus Speichertabelle einfügen

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

Methode 2: Verwenden Sie die temporäre Tabelle

• , um eine temporäre Datentabelle tmp_table zu erstellen

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

• Verwenden Sie Python oder Bash, um eine zu generieren 100 W aufgezeichnete Datendatei (Python wird sofort generiert)

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

• Daten in die temporäre Tabelle tmp_table importieren

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秒插入完成

Hinweis: Beim Importieren von Daten wird möglicherweise ein Fehler gemeldet, da MySQL secure_file_priv standardmäßig nicht aktiviert (Dieser Parameter wird verwendet, um die Auswirkungen von Datenimport- und -exportvorgängen zu begrenzen, z. B. das Ausführen von LOAD DATA, SELECT... INTO OUTFILE-Anweisungen und LOAD_FILE()-Funktionen erfordern den Benutzer mit FILE-Berechtigung)

Lösung: Fügen Sie secure_file_priv = /Users/LJTjintao/temp/` in der MySQL-Konfigurationsdatei hinzu. my.ini oder my.conf) und starten Sie dann die MySQL-Lösung neu

• Verwenden Sie die temporäre Tabelle als Grunddaten, Beim Einfügen von Daten in t_user dauert das Einfügen von 100-W-Daten 10,37 Sekunden

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

• Aktualisieren Sie das Feld für die Erstellungszeit, um die Erstellungszeit der eingefügten Daten zufälliger zu gestalten

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)

Mehr zu MySQL. Für technische Artikel besuchen Sie bitte die Spalte MySQL-Tutorial, um mehr zu erfahren!

Das obige ist der detaillierte Inhalt vonMySQL erstellt schnell zig Millionen Testdaten. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!