MySQL Quick construct 100 million record table

after my last friend asked me how to quickly construct a table of 100 million records, I put the method in place, see: http://blog.csdn.net/mchdba/article/details/52938114, but because it takes 2 to enter 100 million tables hours, so still feel slower, there is no way to speed up this step?

1, build a common user table

The user table does not change, or the same as the last time.

Use test; CREATE TABLE ' Uc_user ' ( ' ID ' BIGINT (20), ' User_name ' VARCHAR (400), ' User_pwd ' VARCHAR (800), ' BIRTHDAY ' DATETIME, ' NAME ' VARCHAR (800), ' User_icon ' VARCHAR (2000), ' SEX ' CHAR (4), ' Nickname ' VARCHAR (800), ' STAT ' VARCHAR (40), ' User_mall ' BIGINT (20), ' Last_login_date ' DATETIME, ' Last_login_ip ' VARCHAR (400), ' src_open_user_id ' BIGINT (20), ' EMAIL ' VARCHAR (800), ' MOBILE ' VARCHAR (200), ' Is_del ' CHAR (4), ' Is_email_confirmed ' VARCHAR (4), ' Is_phone_confirmed ' VARCHAR (4), ' Creater ' BIGINT (20), ' Create_date ' DATETIME, ' Update_date ' DATETIME, ' Pwd_intensity ' VARCHAR (4), ' MOBILE_TGC ' VARCHAR (256), ' MAC ' VARCHAR (256), ' SOURCE ' VARCHAR (4), ' ACTIVATE ' VARCHAR (4), ' Activate_type ' VARCHAR (4), ' Is_life ' VARCHAR (4) ) Engine=innodb;

2, optimizing stored procedures for recording data

There is a change, set autocommit=0; , so that you do not have to commit each time the insert, the implementation of 30W after the insert and then commit, this is the batch submission, improve the data write efficiency.

And in the execution of the commit, in order to verify whether the bulk commit, do have a small authentication, and the verification of information written to the temporary log table inside the Uc_log table, the Uc_log table structure is as follows:

CREATE TABLE ' Uc_log ' ( ' msg ' varchar (+) DEFAULT NULL Comment ' Commit information record ', ' id ' int (one) DEFAULT NULL ) Engine=innodb DEFAULT CHARSET=UTF8MB4

Stored Procedure content:

DELIMITER $$ Use ' Test ' $$ DROP PROCEDURE IF EXISTS ' pro_test_data_1 ' $$ CREATE definer= ' dev_user ' @ '% ' PROCEDURE ' pro_test_data_1 ' (pos_begin int,pos_end INT) BEGIN DECLARE i INT; SET I=pos_begin; SET autocommit=0; While I>=pos_begin && i<= pos_end do                     INSERT into Test. ' Uc_user_1 ' (' ID ', ' user_name ', ' user_pwd ', ' BIRTHDAY ', ' NAME ', ' User_icon ', ' SEX ', ' nickname ', ' S TAT ', ' user_mall ', ' last_login_date ', ' last_login_ip ', ' src_open_user_id ', ' EMAIL ', ' MOBILE ', ' Is_del ', ' Is_email_ ' Confirmed ', ' is_phone_confirmed ', ' creater ', ' create_date ', ' update_date ', ' pwd_intensity ', ' mobile_tgc ', ' MAC ', ' SOURCE ', ' ACTIVATE ', ' activate_type ', ' Is_life ') VALUES (i, ' admin ', ' 1ba613b3676a4a06d6204b407856f374 ', now (), ' super tube ', ' Group1/m00/03/bc/wki0d1qkfawahhewaaaoj58qocg271.jpg ', ' 1 ', ' admin2014 ', ' A ', ' 1 ', now (), ' 192.168.121.103 ', NULL, ', ' 10099990001 ', ' 0 ', ' 1 ', ' 0 ', null,null,null, ' 1 ', ' e5f10caa4ebb44c4b23726cbbd3ac413 ', ' 1-3 ', ' 0 ', ' 2 ', ' 2 ', ' 1 '); SET i=i + 1; # Next Judge 30W A group of times on Commit back. IF MOD (i,300000) <=0 Then Insert into Test.uc_log (id,msg) VALUES (i, ' begin to commmit a group insert SQL data. '); COMMIT; END IF; END while; end$$ DELIMITER;

Blog Source address: http://blog.csdn.net/mchdba/article/details/52987852, Bo master mchdba (Douglas Fir), declined reprint

3, input data ignoreBinlog

Start to enable stored procedure input 100 million data into the table, command call Test.pro_test_data (0,100000000), here time will be longer, after all, is 100 million records.

# when data is entered, it is not written Binlog , accelerate data entry Mysql> set sql_log_bin=0; Query OK, 0 rows Affected (0.00 sec) Mysql> Mysql> call Test.pro_test_data_1 (0,100000000); Query OK, 1 row affected (1 hour PNS min34.57 sec) # See time is 1 hours PNs min 34.57 sec, 1 a half hour or so. Mysql> Mysql> Select COUNT (1) from Test. ' Uc_user_1 '; +-----------+ | COUNT (1) | +-----------+ | 100000001 | +-----------+ 1 row in Set (3 min 0.14 sec) Mysql>

MySQL Quick construct 100 million record table