背景

MySQL在插入數據的時候，應該怎么插入速度最快，效率最高？

我們經常使用的插入方式有如下幾種：

第一種：直接一行一個insert語句，如下所示：

insert into user(name, birthday) values('A','19950902');
...... /*此處省略*/
insert into user(name, birthday) values('C','19950904');

第二種：使用begin和commit事務包裹起來的每一行一個插入語句。

begin;
insert into user(name, birthday) values('A','19950902');
...... /*此處省略*/
insert into user(name, birthday) values('C','19950904');
commit;

第三種：values關鍵字后面跟著很多行數據，一起批量插入。

insert into user(name, birthday) values('A','19950902'),('B','19950903'),('C','19950904');
...... /*此處省略*/
insert into user(name, birthday) values('A','19950902'),('B','19950903'),('C','19950904');

第四種：使用begin和commit事務包裹起來的批量插入的語句。

begin;
insert into user(name, birthday) values('A','19950902'),('B','19950903'),('C','19950904');
...... /*此處省略*/
insert into user(name, birthday) values('A','19950902'),('B','19950903'),('C','19950904');
end;

以上幾種插入的語句，哪個SQL更快？下面讓我們驗證一下。

環境準備

準備表

我們在本地的MySQL中準備4張表，這樣可以避免網絡延遲導致的測試結果不準確的因素，本地直接連接MySQL數據庫，網絡因素就可以排除了。這四張表結構一樣，然后針對這4張表，我們分別使用前面提到的4張插入數據的方式，分別測試。

4張表的結構如下，表名稱不同，表結構一樣。

CREATE TABLE `user_info_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `cellphone` varchar(11) DEFAULT NULL,
  `address` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='第一種插入方式使用的表';

CREATE TABLE `user_info_2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `cellphone` varchar(11) DEFAULT NULL,
  `address` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='第二種插入方式使用的表';

CREATE TABLE `user_info_3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `cellphone` varchar(11) DEFAULT NULL,
  `address` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='第三種插入方式使用的表';

CREATE TABLE `user_info_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `cellphone` varchar(11) DEFAULT NULL,
  `address` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='第四種插入方式使用的表';

主備數據

初始化數據的時候，大家可以參考這個存儲過程，這個存儲過程的作用就是在user_info表中生成指定行數的測試數據，該存儲過程如下：

CREATE PROCEDURE `initial_user_info`(max_id int)
begin
    declare m int default 0;
    declare _username varchar(64) default '';
    declare _age int default 0;
    declare _cellphone varchar(11) default '';
    declare _address varchar(255) default '';
		
		start transaction;
		
			while m < max_id do
					set m = m + 1;
					set _username = substring( uuid(), 1, floor(8 + rand() * (29)));
					set _age = floor(18 + rand() * (43));
					set _cellphone = substr(rand(),-11);
					set _address = concat(substring( uuid(), 1, floor(8 + rand() * (29))), substring( uuid(), 1, floor(8 + rand() * (29))));
					insert into user_info (user_name, age, cellphone, address) values(_username, _age, _cellphone, _address);
			end while;
		
		commit;

end

如下的SQL是向user_info表中插入24W測試數據。

CREATE TABLE `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `cellphone` varchar(11) DEFAULT NULL,
  `address` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用于存儲生成的測試數據的表';

call initial_user_info(240000);

當user_info表中的數據準備好之后，我們可以把這表中的數據用數據庫客戶端工具導出來，導出的時候可以導出兩種格式：一種是一行一行的insert語句，一種是批量insert的語句。基于導出來的這兩種SQL文件，我們再手動patch成下面四個SQL文件。

準備待導入的SQL文件的時候，我們按照前面提到的4種方式，分別為user_info_1.sql、user_info_2.sql、user_info_3.sql、user_info_4.sql四個SQL文件，每一個文件中待導入的記錄數都是24W行數據。它們里面的內容也是按照四中插入方式準備的。

第一種，user_info_1.sql文件。里面的內容是一行一行的insert語句，每一行數據一個insert。沒有使用事務begin和commit包裹起來。
第二種，user_info_2.sql文件。里面的內容是一行一行的insert語句，每一行數據一個insert。與第一個SQL文件的區別是，在文件第一行有begin，在文件的最后一行有commit。文件中的所有的語句使用一個事務begin和commit包裹起來。
第三種，user_info_3.sql文件。里面的內容是一行一行的insert語句，每一個insert語句后面跟著大概700~800個values值。是批量插入的一種方式，但是所有的批量插入的語句，沒有使用事務begin和commit包裹起來。
第四種，user_info_4.sql文件。里面的內容是一行一行的insert語句，每一個insert語句后面跟著大概700~800個values值。是批量插入的一種方式，但是文件的開頭是begin，文件的最后一行是commit。所有的批量插入的語句，使用一個事務begin和commit包裹起來。

為了方便記錄每一個SQL導入消耗的實際，我們使用一個簡單的shell腳本來導入SQL文件，腳本內如下：

echo "`date '+%Y-%m-%d %H:%M:%S'`，開始導入..."
mysql -uroot -proot insert_by_batch_test < $1
echo "`date '+%Y-%m-%d %H:%M:%S'`，導入結束..."

執行導入的結果如下：

?  ~ ./mysql_import.sh ~/Downloads/user_info_1.sql
2021-07-16 15:08:15，開始導入...
mysql: [Warning] Using a password on the command line interface can be insecure.
2021-07-16 15:09:33，導入結束...
?  ~ #耗時 78s
?  ~
?  ~ ./mysql_import.sh ~/Downloads/user_info_2.sql
2021-07-16 15:10:24，開始導入...
mysql: [Warning] Using a password on the command line interface can be insecure.
2021-07-16 15:11:20，導入結束...
?  ~ #耗時 56s
?  ~
?  ~ ./mysql_import.sh ~/Downloads/user_info_3.sql
2021-07-16 15:11:37，開始導入...
mysql: [Warning] Using a password on the command line interface can be insecure.
2021-07-16 15:11:39，導入結束...
?  ~ #耗時 2s
?  ~
?  ~ ./mysql_import.sh ~/Downloads/user_info_4.sql
2021-07-16 15:11:46，開始導入...
mysql: [Warning] Using a password on the command line interface can be insecure.
2021-07-16 15:11:47，導入結束...
?  ~ #耗時 1s
?  ~

通過上面的實驗過程可以發現，第一種導入的效率最差，最后一種導入的效率最高。四種導入方式的效率為：

第一種方式 < 第二種方式 < 第三種方式 < 第四種方式

總結

第一種每一次都需要進行語法解析和語法判斷，數據量特別大的時候每次解析的時間累計起來就很耗時，這是它慢的主要原因。
第二種相對一對一種有一些優化，主要是對日志的操作有原先的一個SQL語句一個事務日志改為所有的SQL語句一個事務日志，這里的日志主要是binlog日志和回滾日志。
第三種執行效率高的主要緣由是合并了SQL語句，采用批量的插入方式，降低了SQL語句解析的次數。此時的日志量binlog日志和回滾日志也合并在一起，下降日志刷盤的數據量和頻率，從而提升效率。
和第三種相比，這種方式把所有的操作合并為一個事務，日志合并后，降低了日志刷盤次數。所以此時效率更高。

所以在初始化一些數據的時候，我們盡量使用批量insert的方式，與此同時在批量insert語句前后使用事務包裹起來。當然事務包裹的SQL語句的大小也要合適，不能無限制地使用一個特別大的事務，這樣效率也不高。