Create tens of millions of test tables and mysql database test tables in the mysql database

Create tens of millions of test tables and mysql database test tables in the mysql database
Preface:

I have recently prepared to learn more about mysql, including the features, performance optimization, and database sharding of various engines. To facilitate testing performance and table sharding, you must first create a large data table. I am going to create a user table with 10 million records first.

Steps: 1. Create a data table (MYISAM stores and inserts data much faster than innodb)

Data table description

Data volume: 10 million

Field Type:

Id: No.

Uname: User Name

Ucreatetime: Creation Time

Age: age

CREATE TABLE usertb (  id serial,   uname  varchar(20) ,  ucreatetime  datetime  ,  age  int(11)   )  ENGINE=MYISAMDEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci  AUTO_INCREMENT=1  ROW_FORMAT=COMPACT; 

2. Create a stored procedure for data insertion

Delimiter $ set autocommit = 0 $ create procedure test1 () begindeclare v_cnt decimal (10) default 0; dd: loop insert into usertb values (null, 'user 1 ', '2017-01-01 00:00:00 ', 20), (null, 'user 2', '2017-01-01 00:00:00', 20), (null, 'user 3 ', '2017-01-01 00:00:00 ', 20), (null, 'user 4', '2017-01-01 00:00:00', 20), (null, 'user 5 ', '2017-01-01 00:00:00 ', 20), (null, 'user 6', '2017-01-01 00:00:00', 20), (null, 'user 7 ', '2017-01-01 00:00:00 ', 20), (null, 'user 8', '2017-01-01 00:00:00', 20), (null, 'user 9 ', '2017-01-01 00:00:00 ', 20), (null, 'user 0', '2017-01-01 00:00:00', 20); commit; set v_cnt = v_cnt + 10; if v_cnt = 10000000 then leave dd; end if; end loop dd; end; $ delimiter;

3. Execute the Stored Procedure

call test1;

Time consumed: it only takes 95 seconds to execute an i5 notebook.

4. Modify engineer as needed (non-essential steps. If conversion is not required, no operation is required)

alter table usertb engine=innodb;

Time consumed: it takes only 200 seconds to execute an i5 notebook.