问题描述
大批量数据删除时,导致插入失败
deletefrom tableNamewhere create_time < timePoint使用此语句删除一千四百余万条数据,耗时两个多小时DeleteFromTime:-03-09 01:00:00.004DeleteToTime:-03-09 03:09:54.757AllDeletedRows:14368263TimeCost:7794753ms同时引发了插入异常:Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
原因分析:
删除时,因为是范围删除且数据量大,未走索引,从而锁住全表,造成后续插入失败
解决方案:
更改删除逻辑,在删除时,添加limit 10000限制,代码里循环删除:
delete
from tableName
where create_time < timePoint
limit 10000
有文章表明,如果数据量小于全表数据量的20%(也有说30%的),where子句如果是范围条件,也会走索引
经实践表明:范围查询结果集为700w数据量的的SQL,limit在50w及以下时能够走索引,limit500w索引失效
同时是否走索引也受总数据量的影响:如果表的总数据量较小,哪怕limit超过了总数据量的50%,也会走索引
在线修改缓存大小(InnioDB):set global innodb_buffer_pool_size = 10737418240(10G,服务器内存16G)
innodb_buffer_pool_instances提示只读,无法修改。查阅他人文字发现该参数对性能影响不大,参考链接:/30135314/viewspace-2711910/
set global innodb_buffer_pool_size = 10737418240需要写入f以便于下次重启服务器自动设置
show variables like ‘innodb_buffer_pool%’;
show global status like ‘%innodb_buffer_pool_pages%’;
这两句SQL可以查看buffer pool一些信息
MyISAM索引缓存可参阅链接
/docs/zh/mysql/5.7/reference/cache-index.html
如果觉得《MySQL大批量删除时导致插入失败》对你有帮助,请点赞、收藏,并留下你的观点哦!