mysql 批量插入 性能_MySQL批量插入数据性能比较

目前遇到比较多的开发同学来问有关insert批量插入相关的事情，现在测试了下两种批量insert写法的效率。

本次测试只比较相同环境下insert单条插入和合并插入的效率问题，不考虑类似“机器性能对mysql插入的影响”或者“每秒能插多少条数据”这种课题

表结构如下

root:test> show create table t_insert_single\G

*************************** 1. row ***************************

Table: t_insert_single

Create Table: CREATE TABLE `t_insert_single` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`col1` int(11) DEFAULT NULL,

`col2` varchar(50) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.01 sec)

root:test> show create table t_insert_mult\G

*************************** 1. row ***************************

Table: t_insert_mult

Create Table: CREATE TABLE `t_insert_mult` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`col1` int(11) DEFAULT NULL,

`col2` varchar(50) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

单条插入：

insert into t_insert_single(col1,col2) values(1,'dsadkhjhfsfiuofdgfdsfdsfdsfdsfdf');

insert into t_insert_single(col1,col2) values(2,'dsadkhjhfsfiuofdgfdsfdsfdsfdsfdf');

合并插入：

insert into t_insert_mult(col1,col2) values (1,'dsadkhjhfsfiuofdgfdsfdsfdsfdsfdf'),(2,'dsadkhjhfsfiuofdgfdsfdsfdsfdsfdf');

测试结果

Paste_Image.png

结论：

1、insert合并插入的效率高于单条插入。

2、insert一条语句一个事务的效率低于多条语句一个事务的效率。

3、在现有的测试情况下，单条插入(N个事务)和合并插入(一个事务)相差的效率最高能达到10倍。

4、在一个事务中，单条插入和合并插入效率平均相差2~3倍。

建议

1、日常多条insert语句，采用合并插入的写法

2、一次性插入批量数控制在500或1000左右(如果有大字段要再小点)

附上测试脚本

单条插入(N个事务)脚本

#!/bin/sh

insert_sql=''

for i in {1..10000}

do

#echo $i

insert_sql=$insert_sql"insert into t_insert_single(col1,col2) values($i,'dsadkhjhfsfiuofdgfdsfdsfdsfdsfdf');"

done

echo $insert_sql>a.sql

time mysql -uroot -p123 test -e"source a.sql"

单条插入(1个事务)脚本

#!/bin/sh

insert_sql=''

for i in {1..10000}

do

#echo $i

insert_sql=$insert_sql"insert into t_insert_single(col1,col2) values($i,'dsadkhjhfsfiuofdgfdsfdsfdsfdsfdf');"

done

echo $insert_sql>a.sql

time mysql -uroot -p123 test -e"set autocommit=0;source a.sql;commit;"

合并插入脚本(1个事务)

#!/bin/sh

insert_sql=''

for i in {1..9999}

do

#echo $i

insert_sql=$insert_sql",($i,'dsadkhjhfsfiuofdgfdsfdsfdsfdsfdf')"

done

insert_sql="insert into t_insert_mult(col1,col2) values (0,'dsadkhjhfsfiuofdgfdsfdsfdsfdsfdf')"$insert_sql";"

echo $insert_sql > b.sql

time mysql -uroot -p123 test -e"source b.sql"