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的表），如果大家有好的辦法，還請後台留言，不吝賜教。。。
