delete一张大表引发的一点思考
01
delete一张大表引发的一点思考
今天上班的时候接收到了一个业务方的反馈，说是一个数据库在删除表的时候报错了，我让他截给我日志看看，日志中的内容如下：
语句：delete from XXXXX_log where log_time < FROM_UNIXTIME(1563353579) limit 300000;
报错：MySqlConnection Error Lock wait timeout exceeded; try restarting transaction
从错误的日志中分析，是在删除一张表的时候出现了锁等待超时问题，系统提示"尝试重新开启事务"。
看到这个问题，第一反应是查看数据量，于是我使用explain语句查看了这个语句的执行计划，如下：
mysql> explain delete from XXXXX_log where log_time < FROM_UNIXTIME(1563353579) limit 300000;
+----+-------------+---------------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | DELETE | XXXXXXXXX_log | NULL | ALL | NULL | NULL | NULL | NULL | 47888261 | 100.00 | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set (0.00 sec)
可以看到type是all，而rows是4788万行，这说明这个语句走了全表扫描，没有走索引，扫面的行数大约是4788万行，所以出现了锁等待超时的问题。
于是我查看了这张表的表结构，发现它只有id字段设置了主键，而log_time字段没有设置索引，所以进行删除的时候是使用了全表扫描的方法。那么这个问题也就变得明确了，就是我们如何优化这个delete语句，让他可以快速的执行完而不影响线上的业务。
这里需要说明一下delete大表的时候带来的影响，delete一张大表的时候，如果记录数太多，则需要锁住很多数据，这个操作将占满整个事务日志，耗尽系统资源，阻塞很多小的但是很重要的查询语句。如果在主从环境下执行，则可能导致主从延迟加大。
解决这个问题方法大概有两种：
1、在delete的时候将limit后面的值设置的更小一点，每次删除一小部分内容，而且删除之后，都暂停一小会儿再做下一次删除，这样可以讲服务器上原本一次性的压力分散到一个很长的时间段中，就可以大大降低对服务器的影响，可以大大减少删除时锁的持有时间。
2、优化删除的SQL，在这个例子中，其实id是有主键的，我当时想到的是这个日志表是按照时间的顺序增长的，而id也是增长的，如果我们知道删除某一段时间的日志的SQL，可以通过查询时间和id的对应关系，将它转化为删除某一个区间内id的SQL，这样根据id进行删除，不就方便多了么。
实际操作的时候也是这么做的，我改成了先选取max(id)，再计算要删到哪个id，最后删除，同时减小一次性删除的总数量，删除语句是这样的：
delete from XXXXXXX_log where id < 650919135 limit 100000;
再来查看这个SQL的执行计划，可以看到：
mysql> explain delete from XXXXXXX_log where id < 650919135 limit 100000;
+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+----------+----------+-------------+
| 1 | DELETE | XXXXXXXXX_log | NULL | range | PRIMARY | PRIMARY | 4 | const | 23394148 | 100.00 | Using where |
+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+----------+----------+-------------+
1 row in set (0.00 sec)
这个时候的删除语句就已经走了主键了，这样过滤的记录数也变小了，不再是4788万行了，进行删除的时候也没有报错了。
这个问题是暂时结束了，但是可能大家对这种delete的操作没有很多概念，于是我是用线上一个已经停止使用的阿里云rds备份log库做了一个实验，查看一下这个库在一次delete操作的时候需要的时间：
mysql@rm-2zeXXXXXXXXXXi.mysql.rds.aliyuncs.com:log_bak 10:12:40>>delete from w_log where id<110000000;
Query OK, 70789994 rows affected (7 min 59.44 sec)
mysql@rm-2zeXXXXXXXXXXi.mysql.rds.aliyuncs.com:log_bak 10:23:32>>delete from w_log where id<120000000;
Query OK, 10000000 rows affected (2 min 38.41 sec)
在一张大约一亿五千万条数据的表里面，删除七千多万条数据，阿里云rds的执行时间大约是8分钟，而删除一千万数据大约时间是2分钟40秒，这个时间只是让大家大概有个概念，如果你要一次性删除三四千万的数据，算上服务器配置，你可以预估一下操作时间，大约得三四分钟左右吧，土豪服务器请忽略，也就意味着你进行delete操作的时候，你的服务得要接受三四分钟不可用的状态。如果你接受不了，那可能得要重新评估一下这个删除操作的可用性了。
是否可以每次少量删除？
是否可以优化一下删除语句？
使用rename操作将表进行重命名，等到业务维护窗口时再进行操作？
这些问题，可能都需要考虑了。