问题描述
大批量数据删除时，导致插入失败
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大批量删除时导致插入失败》对你有帮助，请点赞、收藏，并留下你的观点哦！
