如何优化MySQL insert性能

对于一些数据量较大的系统，面临的问题除了是查询效率低下，还有一个很重要的问题就是插入时间长。我们就有一个业务系统，每天的数据导入需要4-5个钟。

这种费时的 *** 作其实是很有风险的，假设程序出了问题，想重跑 *** 作那是一件痛苦的事情。因此，提高大数据量系统的MySQL

insert效率是很有必要的。

1. 一条SQL语句插入多条数据。

INSERTINTO`insert_table` (`datetime`, `uid`, `content`, `type`) VALUES('0', 'userid_0', 'content_0', 0)

INSERTINTO`insert_table` (`datetime`, `uid`, `content`, `type`) VALUES('1', 'userid_1', 'content_1', 1)

修改成：

INSERTINTO`insert_table` (`datetime`, `uid`, `content`, `type`) VALUES('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1)

修改后的插入 *** 作能够提高程序的插入效率。这里第二种SQL执行效率高的主要原因有两个，一是减少SQL语句解析的 *** 作， 只需要解析一次就能进行数据的插入 *** 作，二是SQL语句较短，可以减少网络传输的IO。

这里提供一些测试对比数据，分别是进行单条数据的导入与转化成一条SQL语句进行导入，分别测试1百、1千、1万条数据记录。

性能测试：这里提供了同时使用上面两种方法进行INSERT效率优化的测试。即多条数据合并为同一个SQL，并且在事务中进行插入。

2. 在事务中进行插入处理。把插入修改成：

START TRANSACTION

INSERTINTO`insert_table` (`datetime`, `uid`, `content`, `type`) VALUES('0', 'userid_0', 'content_0', 0)

INSERTINTO`insert_table` (`datetime`, `uid`, `content`, `type`) VALUES('1', 'userid_1', 'content_1', 1)

...

COMMIT

使用事务可以提高数据的插入效率，这是因为进行一个INSERT *** 作时，MySQL内部会建立一个事务，在事务内进行真正插入处理。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交 *** 作。

这里也提供了测试对比，分别是不使用事务与使用事务在记录数为1百、1千、1万的情况。

如何优化MySQL insert性能

浏览：546

|

更新：

2013-10-24 15:09

|

标签：mysql

百度经验:jingyan.baidu.com

对于一些数据量较大的系统，面临的问题除了是查询效率低下，还有一个很重要的问题就是插入时间长。我们就有一个业务系统，每天的数据导入需要4-5个钟。

这种费时的 *** 作其实是很有风险的，假设程序出了问题，想重跑 *** 作那是一件痛苦的事情。因此，提高大数据量系统的MySQL

insert效率是很有必要的。

1. 一条SQL语句插入多条数据。

常用的插入语句如：

INSERTINTO`insert_table` (`datetime`, `uid`, `content`, `type`) VALUES('0', 'userid_0', 'content_0', 0)

INSERTINTO`insert_table` (`datetime`, `uid`, `content`, `type`) VALUES('1', 'userid_1', 'content_1', 1)

修改成：

INSERTINTO`insert_table` (`datetime`, `uid`, `content`, `type`) VALUES('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1)

修改后的插入 *** 作能够提高程序的插入效率。这里第二种SQL执行效率高的主要原因有两个，一是减少SQL语句解析的 *** 作， 只需要解析一次就能进行数据的插入 *** 作，二是SQL语句较短，可以减少网络传输的IO。

这里提供一些测试对比数据，分别是进行单条数据的导入与转化成一条SQL语句进行导入，分别测试1百、1千、1万条数据记录。

性能测试：这里提供了同时使用上面两种方法进行INSERT效率优化的测试。即多条数据合并为同一个SQL，并且在事务中进行插入。

2. 在事务中进行插入处理。把插入修改成：

START TRANSACTION

INSERTINTO`insert_table` (`datetime`, `uid`, `content`, `type`) VALUES('0', 'userid_0', 'content_0', 0)

INSERTINTO`insert_table` (`datetime`, `uid`, `content`, `type`) VALUES('1', 'userid_1', 'content_1', 1)

...

COMMIT

使用事务可以提高数据的插入效率，这是因为进行一个INSERT *** 作时，MySQL内部会建立一个事务，在事务内进行真正插入处理。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交 *** 作。

这里也提供了测试对比，分别是不使用事务与使用事务在记录数为1百、1千、1万的情况。

从测试结果可以看到，insert的效率大概有50倍的提高，这个一个很客观的数字。

注意事项：

1. SQL语句是有长度限制，在进行数据合并在同一SQL中务必不能超过SQL长度限制，通过max_allowed_packet配置可以修改，默认是1M。

2. 事务需要控制大小，事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项，超过这个值会日志会使用磁盘数据，这时，效率会有所下降。所以比较好的做法是，在事务大小达到配置项数据级前进行事务提交。

假定我们的表结构如下

代码如下

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')

上面看上去没什么问题，下面我来使用sql语句优化的小技巧，下面会分别进行测试，目标是插入一个空的数据表200W条数据

第一种方法：使用insert

into

插入，代码如下：

代码如下

$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)

最后总结下，在插入大批量数据时，第一种方法无疑是最差劲的，而第二种方法在实际应用中就比较广泛，第三种方法在插入测试数据或者其他低要求时比较合适，速度确实快。

---