delete - mysql innodb引擎 按照非唯一索引删除内容时 导致整个mysql实例变慢，所有表增、删、改、查全部变慢

Question

定时任务清理一个大表大概有4000W左右的数据量,每天定时按照 delete from tableXX where date='2015-09-08' date varchar 字段有索引，删除数据量百万级 导致整个db实例响应变慢，大概会影响2分钟左右，过程中所有表的增、删、改、查均变慢，响应时间从10ms级 变成10+s级别；
有没有了解MySQL innodb引擎的大神能帮忙解释一下

Answer 1

You can pay attention to the io load when executing this statement. Judging from your description, it will definitely be much larger. Although date has an index, as long as the number of records affected is large enough, it will definitely slow down the overall speed (in addition to io, there are also locks affected by other fields). If your table stores data for a month, deleting one day of data each time will directly affect 1/30 of the contents of the entire table. In fact, this amount is very large.

For your situation, one way is to spread the load, such as turning the query into many

 delete from tableXX where date='2015-09-08' limit N

In this way, each deletion is very fast, and then a sleep is performed between each execution. This will extend the overall running time a lot, but it can eliminate the load peak.