MYSQL批量插入数据库 PHP使用MYSQL批量插入数据库语句实现性能分析

使用MYSQL中经常会遇到需要大批量往数据库中插入数据的操作，如同时插入几百万条数据，那到底是用什么方式来插入这么大量的数据效率最高呢？

我们做个测试：

假设MYSQL数据库表结构如下

CREATE TABLE example (
example_id INT NOT NULL,
name VARCHAR( 50 ) NOT NULL,
value VARCHAR( 50 ) NOT NULL,
other_value VARCHAR( 50 ) NOT NULL
)

通常插入单条数据的sql语句我们会这么写:

INSERT INTO example
(example_id, name, value, other_value)
VALUES
(100, 'Name 1', 'Value 1', 'Other 1');

其实mysql允许我们在一条sql语句中批量插入数据,方式如下sql语句:

INSERT INTO example
(example_id, name, value, other_value)
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');

如果我们插入列的顺序和表中列的顺序一致的话,还可以省去列名的定义,如下sql

INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');

接下来进入正题，看看我们上面提到的方法，效率能提高多少。

我们设定一个目标，往一个空的数据表插入200W条数据

第一种方法:使用insert into +for循环插入,代码如下:

$params = array('value'=>'50');
set_time_limit(0);
echo date("H:i:s");
for($i=0;$i<2000000;$i++){
$connect_mysql->insert($params);
};
echo date("H:i:s");

 最后显示为:23:25:05 01:32:05 也就是花了2个小时多!

第二种方法:使用事务提交,批量插入数据库(每隔10W条提交下)最后显示消耗的时间为:22:56:13 23:04:00 ,一共8分13秒 ,代码如下:

echo date("H:i:s");
$connect_mysql->query('BEGIN');
$params = array('value'=>'50');
for($i=0;$i<2000000;$i++){ 
$connect_mysql->insert($params);
if($i%100000==0){
$connect_mysql->query('COMMIT');
$connect_mysql->query('BEGIN');
}
}
$connect_mysql->query('COMMIT');
echo date("H:i:s");

第三种方法:就是我们一开始讲到的使用优化SQL语句:将SQL语句进行拼接,使用 insert into table () values (),(),(),()然后再一次性插入

如果要插入的内容字符串太长,则需要配置下MYSQL,在mysql 命令行中运行 :set global max_allowed_packet = 2*1024*1024*10;

这个测试消耗时间为:11:24:06 11:25:06;插入200W条测试数据仅仅用了1分钟!代码如下:

$sql= "insert into twenty_million (value) values";
for($i=0;$i<2000000;$i++){
$sql.="('50'),";
};
$sql = substr($sql,0,strlen($sql)-1);
$connect_mysql->query($sql);

从上面测试我们可以发现，第一种用FOR循环的方式，一条条插入无疑效率是最差的，而第二种方法使用事务处理，效率高很多，而要最快则是第三种方法，不过要记得设置允许提交的数据包数量（max_allowed_packet）