MySQL insert语句慢查询的优化

原创
2018/10/25 15:13
阅读数 1W

近期发现MySQL slow log 里面有很多慢查询,而分析慢查询日志发现大量的慢查询是insert语句的。在高峰期(早上10点左右)半小时的慢查询量可以到28G左右。遂决定优化

 

优化方法:

1、 一个客户端在一个时候要插多条数据,那么用多个values

insert into t1 values(...),(...),(...)

如果是往一个非空的表里插数据,可调节bulk_insert_buffer_size(缺省为8388608字节=8M)

 

2、 如果多个客户端在同时插许多条数据,那么用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来说,在可以使用方法3时,不需用此方法2。

 

3、对Myisam表来说,如果一个表中间没有删除过数据,那么,在Select语句执行时,可以同时执行insert语句将数据插在文件最后。

concurrent_insert必须为1(缺省就是1)

 

4、从文本文件执行load data infile一般要比用insert语句快20倍。

如果表有索引,可以先去掉索引,load完后,再加上索引。可以提高速度(相比load同时建索引,可以减少disk seek)。

这个事后建索引的方法在msisam表为空时自动执行。

 

5、如果插入多条语句,可以先lock tables t write,插入后再unlock tables(索引会只flush一次); 但如果当中只有1条insert,那么不需要。

 

6、要提高Myisam表的load data和insert速度,可提高key_buffer_size(缺省为8M)

如果机器有256M以上内存,那么可以设key_buffer_size为64M,table_open_cache可以调高为256(缺省为64)

如果有128M以上内存,可以设key_buffer_size为16M

展开阅读全文
打赏
0
4 收藏
分享
加载中
更多评论
打赏
0 评论
4 收藏
0
分享
OSCHINA
登录后可查看更多优质内容
返回顶部
顶部