Jquery中文网 > 数据库 > mysql > 正文 Mysql insert语句的优化总结

Mysql insert语句的优化总结

发布时间：2017-02-09 编辑：www.jquerycn.cn

jquery中文网为您提供Mysql insert语句的优化总结等资源，欢迎您收藏本站，我们将为您提供最新的Mysql insert语句的优化总结资源

insert是保存数据的命令在web开发中我们经常用到insert来对数据的操作了，但insert的性能并不是非常的好，如果大数据量我们需要进行一些优化处理，下面来看一篇关于Mysql insert语句的优化总结文章。

1) 如果你同时从同一客户插入很多行，使用多个值表的INSERT语句。这比使用分开INSERT语句快(在一些情况中几倍)。

Insert into test values(1,2),(1,3),(1,4)…

</td></tr></table>

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

常用的插入语句如：

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

修改成：

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

</td></tr></table>

Java实现：

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123");
// 关闭自动提交，默认情况下每执行一条sql提交一次
connection.setAutoCommit(false);
PreparedStatement statement = connection.prepareStatement("INSERT INTO insert_table VALUES(?, ?)");
//记录1
statement.setString(1, "2012-12-27 11:11:11");
statement.setString(2, "userid_0");
statement.setString(3, "content_0");
statement.setInt(4, 0);
statement.addBatch();
//记录2
statement.setString(1, "2012-12-27 12:12:12");
statement.setString(2, "userid_1");
statement.setString(3, "content_1");
statement.setInt(4, 1);
statement.addBatch();
//记录3
statement.setString(1, "2012-12-27 13:13:13");
statement.setString(2, "userid_2");
statement.setString(3, "content_2");
statement.setInt(4, 2);

statement.addBatch();
//批量执行上面3条语句.
int [] counts = statement.executeBatch();
//Commit
connection.commit();

</td></tr></table>

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

2) 如果你从不同客户插入很多行，能通过使用INSERT DELAYED语句得到更高的速度。Delayed的含义是让insert 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘；这比每条语句分别插入要快的多；LOW_PRIORITY刚好相反，在所有其他用户对表的读写完后才进行插入。

3) 将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）。

4) 如果进行批量插入，可以增加bulk_insert_buffer_size变量值的方法来提高速度，但是，这只能对myisam表使用。

5) 当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。

6) 根据应用情况使用replace语句代替insert。

7) 根据应用情况使用ignore关键字忽略重复记录。

8)锁定表可以加速用多个语句执行的INSERT操作：

<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy2739')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy2739>LOCK TABLES a WRITE;
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
UNLOCK TABLES;
</td></tr></table>

这样性能会提高，因为索引缓存区仅在所有INSERT语句完成后刷新到磁盘上一次。一般有多少INSERT语句即有多少索引缓存区刷新。如果能用一个语句插入所有的行，就不需要锁定。

对于事务表，应使用BEGIN和COMMIT代替LOCK TABLES来加快插入。

1.分析
插入一行分下面几个动作，括号后面是其大约比例额
Connecting(3)
Sendint query to server(2)
Parsing query(2)
Inserting row(1*size of row)
Inserting indexes(1*number of indexes)
Closing(1)
插入索引的速度随表的大小减慢，LogN

2.优化方法
a. 一个客户端在一个时候要插多条数据，那么用多个values
insert into t1 values(...),(...),(...)
如果是往一个非空的表里插数据，可调节bulk_insert_buffer_size（缺省为8388608字节=8M）
b. 如果多个客户端在同时插许多条数据，那么用insert delayed语句
利：客户端马上返回，数据排成一队；数据整齐的写到一个块里，而不是分散。
弊：如果这个表被查获删数据，那么插入会变慢，另外，为这个表起一个handler线程来处理这些数据也要耗费一些额外资源
待插的数据放在内存里，一旦数据库被意外终止（如kill -9），那么数据会丢失。
这个方法只适用于myisam,memory,archive,blackhole引擎类表。
可调节delayed_insert_limit（缺省为一次100条）
delayed_insert_timeout（缺省为300）秒内，若无新的insert delayed语句，则handler线程退出。
delayed_queue_size（缺省为1000条）一旦满了，客户端的insert delayed会阻塞。
比第一个方法要慢。
而且对Myisam来说，在可以使用方法c时，不需用此方法b。
c. 对Myisam表来说，如果一个表中间没有删除过数据，那么，在Select语句执行时，可以同时执行insert语句将数据插在文件最后。
concurrent_insert必须为1（缺省就是1）
d. 从文本文件执行load data infile一般要比用insert语句快20倍。
如果表有索引，可以先去掉索引，load完后，再加上索引。可以提高速度（相比load同时建索引，可以减少disk seek）。
这个事后建索引的方法在msisam表为空时自动执行。
e. 如果插入多条语句，可以先lock tables t write，插入后再unlock tables(索引会只flush一次）; 但如果当中只有1条insert，那么不需要。
f. 要提高Myisam表的load data和insert速度，可提高key_buffer_size（缺省为8M）
如果机器有256M以上内存，那么可以设key_buffer_size为64M，table_open_cache可以调高为256（缺省为64）
如果有128M以上内存，可以设key_buffer_size为16M

3.测试情况：
表t(id int auto_increment primary key,content1 varchar(30),content2 int);
create index ind_of_t on t(content1);

a. 单条插入空表：
1千条，耗时:24秒
5千条，耗时:160秒
1万条，耗时:277秒

b. 一次1000个values list插空表
1千条，耗时:2秒
5千条，耗时:6秒
1万条，耗时:11秒
5万条，耗时:51秒
10万条，耗时:99秒

c. 10个线程同时插空表，一次1000个values list
4,Begin ...
8,Begin ...
6,Begin ...
0,Begin ...
7,Begin ...
9,Begin ...
3,Begin ...
5,Begin ...
2,Begin ...
6,1千条，耗时:6秒
3,1千条，耗时:5秒
9,1千条，耗时:6秒
8,1千条，耗时:7秒
2,1千条，耗时:7秒
5,1千条，耗时:7秒
0,1千条，耗时:10秒
1,Begin ...
4,1千条，耗时:12秒
1,1千条，耗时:2秒
7,1千条，耗时:17秒
6,5千条，耗时:27秒
2,5千条，耗时:28秒
5,5千条，耗时:30秒
4,5千条，耗时:31秒
0,5千条，耗时:34秒
8,5千条，耗时:35秒
7,5千条，耗时:36秒
1,5千条，耗时:30秒
9,5千条，耗时:46秒
3,5千条，耗时:49秒
2,1万条，耗时:49秒
8,1万条，耗时:60秒
7,1万条，耗时:61秒
0,1万条，耗时:63秒
5,1万条，耗时:65秒
6,1万条，耗时:67秒
1,1万条，耗时:61秒
4,1万条，耗时:79秒
3,1万条，耗时:78秒
9,1万条，耗时:84秒
6,5万条，耗时:275秒
1,5万条，耗时:285秒
0,5万条，耗时:306秒
8,5万条，耗时:314秒
2,5万条，耗时:316秒
4,5万条，耗时:330秒
5,5万条，耗时:351秒
3,5万条，耗时:364秒
9,5万条，耗时:377秒
7,5万条，耗时:403秒
6,10万条，耗时:552秒
6,End
0,10万条，耗时:558秒
0,End
1,10万条，耗时:573秒
1,End
4,10万条，耗时:615秒
4,End
3,10万条，耗时:615秒
3,End
5,10万条，耗时:623秒
5,End
8,10万条，耗时:625秒
8,End
7,10万条，耗时:643秒
7,End
9,10万条，耗时:648秒
9,End
2,10万条，耗时:654秒
2,End

d. 10个线程同时插表（已有100万条记录），一次1000个values list，再插900万条记录
5,Begin ...on 1236937010秒
1,Begin ...on 1236937010秒
8,Begin ...on 1236937010秒
4,Begin ...on 1236937010秒
7,Begin ...on 1236937010秒
2,Begin ...on 1236937010秒
3,Begin ...on 1236937010秒
9,Begin ...on 1236937010秒
0,Begin ...on 1236937011秒
6,Begin ...on 1236937011秒
8,10万条，耗时:499秒
0,10万条，耗时:518秒
3,10万条，耗时:519秒
7,10万条，耗时:556秒
9,10万条，耗时:565秒
2,10万条，耗时:578秒
5,10万条，耗时:654秒
1,10万条，耗时:709秒
0,20万条，耗时:1006秒
9,20万条，耗时:1070秒
3,20万条，耗时:1091秒
8,20万条，耗时:1141秒
5,20万条，耗时:1146秒
2,20万条，耗时:1157秒
7,20万条，耗时:1185秒
1,20万条，耗时:1291秒
0,30万条，耗时:1510秒
3,30万条，耗时:1616秒
9,30万条，耗时:1649秒
7,30万条，耗时:1690秒
8,30万条，耗时:1701秒
5,30万条，耗时:1767秒
1,30万条，耗时:1778秒
2,30万条，耗时:1898秒
0,40万条，耗时:2066秒
3,40万条，耗时:2109秒
8,40万条，耗时:2197秒
9,40万条，耗时:2213秒
1,40万条，耗时:2235秒
5,40万条，耗时:2266秒
0,50万条，耗时:2461秒
3,50万条，耗时:2502秒
9,50万条，耗时:2607秒
1,50万条，耗时:2655秒
8,50万条，耗时:2663秒
5,50万条，耗时:2739秒
3,60万条，耗时:2876秒
0,60万条，耗时:2921秒
1,60万条，耗时:3055秒
8,60万条，耗时:3101秒
5,60万条，耗时:3178秒
9,60万条，耗时:3201秒
3,70万条，耗时:3312秒
0,70万条，耗时:3358秒
1,70万条，耗时:3437秒
8,70万条，耗时:3523秒
9,70万条，耗时:3645秒
5,70万条，耗时:3694秒
3,80万条，耗时:3731秒
0,80万条，耗时:3799秒
8,80万条，耗时:3906秒
1,80万条，耗时:3915秒
5,80万条，耗时:4062秒
3,90万条，耗时:4101秒
3,End
0,90万条，耗时:4209秒
0,End
8,90万条，耗时:4227秒
8,End
1,90万条，耗时:4241秒
1,End
5,90万条，耗时:4288秒
5,End

您可能感兴趣的文章：
mysql数据库sql优化原则
 mysql优化insert语句的方法
 mysql优化group by语句的简单方法
 mysql中rollup和limit的用法 mysql中where子句的优化
 mysql insert的操作分享(DELAYED、IGNORE、ON DUPLICATE KEY UPDATE )
Mysql大指量插入数据时SQL语句的优化
 优化mysql insert性能的方法
 mysql 索引优化之btree、hash与rtree
影响MySQL性能的查询类型有哪些
 mysql having与where的用法区别在哪？

上一篇：Mysql join查询的优化方法下一篇：mysql修改最大连接数的几种方法介绍

[关闭]

Mysql insert语句的优化总结

最近更新

浏览排行