mysql insert慢_MySQL insert语句慢查询的优化

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

优化方法:

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

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

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

42A9C750AD89463B97C0C8F037895CDD

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
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL "insert" 语句产生SQL的情况通常与多种因素有关: ### 1. 表结构设计不佳 - **字段过多**:如果插入的数据量远远大于表中的索引列数,那么每次插入都需要扫描所有非主键列,这将极大地增加查询时间。 - **大量NULL值**:插入含有大量NULL值的记录会降低查询性能,因为数据库需要检查每个字段是否为NULL,并对NULL值执行额外的操作。 ### 2. 索引问题 - **不合适或过多的索引**:虽然索引提高了查找速度,但在某些情况下,它们可能会导致写操作变。例如,对于频繁更新的列,添加索引可能导致更多的索引文件大小增长,进而影响写入效率。 - **索引覆盖问题**:插入操作如果涉及到的字段正好位于某个索引上,这样的操作被称为索引覆盖查询。然而,在实际插入数据时,由于需要将新数据添加到行存储,这并不总是能够利用索引来优化查询过程,特别是当行变得过大以至于不能全部在索引中表示时。 ### 3. 资源限制 - **磁盘瓶颈**:磁盘I/O操作是数据库性能的一个关键因素。当插入大量的数据时,如果没有足够的磁盘读写带宽,就会成为瓶颈。 - **内存不足**:如果数据库缓冲池(如MyISAM的表缓存、InnoDB的临时空间等)内存分配不足,插入操作可能会更频繁地从磁盘读取数据,导致性能下降。 ### 4. 查询优化问题 - **不必要的JOIN**:如果你在插入操作之前进行了复杂的JOIN操作,这不仅会使插入语句本身复杂化,还可能导致性能下降。 - **错误的逻辑处理**:在INSERT之前未适当过滤或验证输入数据,可能导致无效的数据被插入到表中,甚至引起锁定或其他并发问题。 ### 解决方案 - **优化表结构**:减少不必要的字段,尤其是那些经常包含NULL值的字段;考虑使用外键和参照完整性约束来提高数据一致性。 - **合理使用索引**:评估哪些字段需要索引,避免过度索引,特别注意那些频繁更新的字段可能不需要索引。 - **监控资源使用**:定期检查服务器资源使用情况,包括CPU、内存和磁盘IO,必要时调整资源配置。 - **批处理**:对于大量数据的插入,可以考虑分批插入而非一次性插入,以减轻对系统的压力。 - **优化查询逻辑**:在进行插入操作前,尽量简化逻辑,比如提前剔除无效或非法数据。 --- ## 相关问题: 1. 如何识别并诊断MySQLSQL问题? 2. MySQL中如何有效管理索引以提升查询性能? 3. 批处理在MySQL插入大批次数据时的作用是什么?
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值