delete大表时发生的一些有趣的现象
- 2019 年 11 月 22 日
- 筆記
Nov,2019
今天周三，这两天回家比较早，早点休息了，感觉身体还是最重要的。人上年龄了感觉记忆力衰退特别明显，经常干某个事情干到一般就忘了，希望早点休息能够让我返老还童，今天早点睡了，晚安同志们。。。
delete大表时发生的一些有趣的现象
最近，由于某个业务需求，要删除某个表中30天以前的数据，该表的数据已经保存了三四年了，整个表的大小也已经有110G了，说实话，这种的我之前都没有处理过。
首先这个问题可以有一些解决方法，比如通过rename语句，将表重命名，然后创建新表，再把最近一个月的数据导入到新表中去，这样，操作时间上会更短。但是需要业务方接受rename操作和创建新表操作的短暂延迟，预估在2s以内。
当然，还有第二种办法，就是笨一点，写个存储过程或者写个脚本去删除，花费的时间长一些，但是这种方法并不会真正的释放表的空间，而只是清除表中的记录，要想释放空间，可能还需要做optimize table或者alter table 的操作，这个优化的操作相当耗时，所以要在维护窗口做。
由于这样那样的原因吧，暂时选择了先delete的方法，每次删除表中的1000条数据，然后sleep 1s，接着删除，在删除到3000w条左右的时候，删除的操作就比较耗时了，于是我手动删除了一下，看看删除的效果，在删除表的过程中，看到了几个有意思的现象：
1、where条件不一样，花费的时间有显著差距。
mysql 09:42:41>>delete from user_XXXX where id < 35199000;
Query OK, 1000 rows affected (3.22 sec)
mysql 09:42:59>>select min(id) from user_XXXX;
+———-+
| min(id) |
+———-+
| 35199000 |
+———-+
1 row in set (2.29 sec)
mysql 09:44:54>>delete from user_XXXX where id >=35199000 and id < 35200000;
Query OK, 1000 rows affected (0.54 sec)
可以看到，表中的数据大概是到3000w左右了，接下来进行两个操作：
a、先进行一个delete的操作，删除了1000条记录，其中where条件是id<35199000,删除总共花费的时间是3.22s
b、查看当前的最小id值，发现是35199000，然后再删除1000条数据，这个时候，where条件里写上一个范围，指定最小和最大值，从结果中不难看出，也删除了1000条数据，但是花费的时间是0.54s，这个时间就很短了，比上面的少了6倍。
2、不同记录处，删除同样条数的记录，响应的时间长短不一致。例如在刚开始删除的时候，id为0~1000的记录删除，可能只需要10ms不到的时间，越往后时间越长，看看我截取的结果：
mysql 09:44:54>>delete from user_XXXX where id >=35199000 and id < 35200000;
Query OK, 1000 rows affected (0.54 sec)
mysql 09:45:54>>delete from user_XXXX where id >=35200000 and id < 35201000;
Query OK, 1000 rows affected (0.68 sec)
mysql 13:09:07>>delete from user_XXXX where id >=51031000 and id < 51032000;
Query OK, 1000 rows affected (5.52 sec)
mysql 13:10:35>>delete from user_XXXX where id >=51031000 and id < 51033000;
Query OK, 1000 rows affected (5.70 sec)
也就是说，类似的语句，在主键id是3000w左右的时候运行时长和id是5000w左右的时候运行时长是不一样的，而且差距有10倍之多。而且这个时间，多次查询，基本保持一致。
3、select min(id) 语法执行的时候，执行时间的差距非常大，及时id本身是主键。
mysql 13:45:14>>select max(id) from user_XXXX;
+———–+
| max(id) |
+———–+
| 322415101 |
+———–+
1 row in set (0.00 sec)
mysql 13:45:21>>select min(id) from user_XXXX;
+———-+
| min(id) |
+———-+
| 51033000 |
+———-+
1 row in set (2 min 9.08 sec)
这几个现象有些能够解释，有些我也没有想清楚。这里我抛出几个问题，心中大概有个答案，但是还有一些细节没有想清楚，等日后想清楚了再来回答。
求min(id)的时候，如果id本身是主键，不应该直接从聚集索引叶子节点的最左侧拿到相关记录么？为什么这个操作也能这么慢？innodb中查询优化器对于这种查询究竟会怎么处理？关于大表的数据删除，有没有更好的处理办法？（上百G的表），如果大家有好的办法，还请后台留言，不吝赐教。。。
