MySQL 快速構造一億條記錄的表

標籤：執行   沒有   mod   engine   表結構   call   null   pos   log   

 

在上一次朋友問我如何快速構造一億條記錄的表後，我理出了實行的辦法，見：http://blog.csdn.net/mchdba/article/details/52938114，但是因為錄入一億表需要2個小時，所以還是感覺速度慢了些，那有沒有啥辦法能加快這一步驟呢？

 

1、建一張通用的使用者表

建使用者表沒有啥變化，還是和上次一樣。

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、最佳化錄資料的預存程序

這裡有變化了，設定了set autocommit=0;了，這樣不用每次insert就commit一次，實現了30W後insert然後進行commit，這樣就是批量提交，提高了資料寫效率。

 

而且在執行commit的時候，為了驗證是否批量提交，就做了有小驗證，而且驗證的資訊寫入到臨時日誌表裡面uc_log表去，uc_log表結構如下：

CREATE TABLE `uc_log` (   `msg` varchar(1000) DEFAULT NULL comment ‘提交資訊記錄’,   `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

 

 

 

預存程序內容：

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`, `STAT`, `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(),‘超管‘,‘group1/M00/03/BC/wKi0d1QkFaWAHhEwAAAoJ58qOcg271.jpg‘,‘1‘,‘admin2014‘,‘01‘,‘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;  # 接下來判斷30W一批次就commit一回。                    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來源地址： http://blog.csdn.net/mchdba/article/details/52987852，博主mchdba（黃杉），謝絕轉載

 

 

 

 

3、錄入資料忽略binlog

開始啟用預存程序錄入一億條資料進表，命令call test.pro_test_data(0,100000000);，這裡時間會比較長一些，畢竟是一億條記錄。

 

# 錄入資料的時候，不寫入binlog，加快資料錄入 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 37 min34.57 sec)   # 看到用時是1 hours 37 min 34.57 sec，1個半小時左右。   mysql> mysql> select count(1) from test.`UC_USER_1`; +-----------+ | count(1)  | +-----------+ | 100000001 | +-----------+ 1 row in set (3 min 0.14 sec)   mysql>

 

 

 

MySQL 快速構造一億條記錄的表