Mysql 生成亿级测试数据

• 先建数据表

CREATE TABLE `card1` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `number` varchar(254) NOT NULL DEFAULT '' COMMENT '卡号',
 `user` varchar(60) NOT NULL DEFAULT '' COMMENT '用户',
 `password` varchar(254) NOT NULL DEFAULT '' COMMENT '密码',
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

• 使用存储过程生成测试数据，修改n的值可以得到任意条数的测试数据

DROP PROCEDURE IF EXISTS proc1;
DELIMITER $$
SET AUTOCOMMIT = 0$$
CREATE  PROCEDURE proc1()
BEGIN
DECLARE n DECIMAL (10)  DEFAULT 0 ;
dd:LOOP
          INSERT  INTO card1(number,user,password) VALUES (UUID(),concat('user-',n),password(n));
                  COMMIT;
                    SET n = n+1 ;
                           IF  n = 100000000 THEN LEAVE dd;
                          END IF;
         END LOOP dd ;
END;$$
DELIMITER ;

• 这里先把 n 改成了1000W，近5分钟就成功生成了测试数据

mysql> call proc1;
Query OK, 0 rows affected (4 min 47.15 sec)

mysql> select count('id') from card;
+-------------+
| count('id') |
+-------------+
|    10000000 |
+-------------+
1 row in set (0.01 sec)


mysql> select * from card where id =1234567;
+---------+--------------------------------------+-------------+-------------------------------------------+
| id      | number                               | user        | password                                  |
+---------+--------------------------------------+-------------+-------------------------------------------+
| 1234567 | 3d3d1209-948c-11e8-b070-0800272d882f | user1234566 | *DBEE43222EB6DDDECB028FAF6C4909E502562D67 |
+---------+--------------------------------------+-------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> desc select * from card where id =1234567;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | card  | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)





mysql>  select * from card where user='user1234567';
+---------+--------------------------------------+-------------+-------------------------------------------+
| id      | number                               | user        | password                                  |
+---------+--------------------------------------+-------------+-------------------------------------------+
| 1234568 | 3d3d12f3-948c-11e8-b070-0800272d882f | user1234567 | *6A7A490FB9DC8C33C2B025A91737077A7E9CC5E5 |
+---------+--------------------------------------+-------------+-------------------------------------------+
1 row in set (2.05 sec)

mysql> desc select * from card where user='user1234567';
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | card  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10000000 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

---

• 这里测试生成1亿条数据，生成速度极为缓慢，大概需要50分钟左右

mysql> call proc1;
Query OK, 0 rows affected (50 min 35.22 sec)

mysql> select count('id') from card;
+-------------+
| count('id') |
+-------------+
|   100000000 |
+-------------+
1 row in set (0.01 sec)

mysql> select * from card where id=1;
+----+--------------------------------------+--------+-------------------------------------------+
| id | number                               | user   | password                                  |
+----+--------------------------------------+--------+-------------------------------------------+
|  1 | 3daf67c2-948e-11e8-b070-0800272d882f | user-0 | *B12289EEF8752AD620294A64A37CD586223AB454 |
+----+--------------------------------------+--------+-------------------------------------------+
1 row in set (0.01 sec)

---

• 修改存储引擎

mysql> ALTER TABLE card  ENGINE=InnoDB;
Query OK, 100000000 rows affected (18 min 24.62 sec)
Records: 100000000  Duplicates: 0  Warnings: 0

mysql> select count('id') from card;
+-------------+
| count('id') |
+-------------+
|   100000000 |
+-------------+
1 row in set (1 min 7.50 sec)