快速刪除數百萬行

我目前有一個具有以下結構的數據庫：

id->varchar(9)

時間->日期時間

val01 - val20->int(11)

val21 - val40->tinyint(1)

id 和 time 是主鍵。我還為時間定義了一個索引。

+-----+---------------------+--------+--------+--------+...+--------+--------+--------+
| id  | time                | val01  | val02  | val03  |...| val38  | val39  | val40  |
+-----+---------------------+--------+--------+--------+...+--------+--------+--------+
| #01 | 2014-02-26 12:25:00 |   56   |    9   |   10   |...|    0   |    0   |    0   |
| #01 | 2014-02-26 12:26:00 |   14   |   89       58   |...|    0   |    1   |    0   |
| #01 | 2014-02-26 12:27:00 |   52   |   91   |   68   |...|    0   |    1   |    1   |
| #02 | 2014-02-26 12:28:00 |   52   |   30   |   73   |...|    0   |    1   |    1   |
....................................................................................
| #01 | 2014-02-28 16:34:00 |   32   |   82   |   86   |...|    0   |    1   |    0   |
| #01 | 2014-02-28 16:35:00 |   28   |   14   |   93   |...|    1   |    1   |    1   |
| #02 | 2014-02-28 16:35:00 |   94   |   95   |   49   |...|    0   |    0   |    0   |
| #02 | 2014-02-28 16:36:00 |   76   |   64   |   30   |...|    1   |    0   |    1   |
+-----+---------------------+--------+--------+--------+...+--------+--------+--------+

問題是，我每天都會獲得數百萬條新行。當服務執行幾個月時，該表將有大約 18 億行，我想防止它變得更大。

因此我想刪除一些舊行，因為不再需要它們了。

例如：我只會保留每 5 條記錄，即超過 6 個月的記錄。那將是我每天可以刪除的大約 10 000 000 行。

我試圖用這個查詢來實現這一點：

SET @x := 0;

DELETE FROM mytable WHERE(id,time) IN(
   SELECT id, time FROM
   (
       SELECT id, time, (@x:=@x+1) AS x FROM mytable
       WHERE time < "2013-08-08 00:00:00" 
       ORDER BY time
   )t
   WHERE x MOD 5 != 0
)

不幸的是，這個查詢很慢。

我該如何改進它？還是有更好的不同方法？

正如 mustaccio 所說，對數據進行分區可能會有所幫助，儘管這可能不是一個快速的解決方案，而且您仍然需要稍微優化這些語句。

據說 mysql 在使用 IN 子句時效率特別低，在這種情況下，它可能會為每一行執行一次內部查詢，mytable而不是有效的。更好但仍遠非最佳，它可能正在執行內部查詢並將結果假離線到磁碟上的臨時表中，然後加入該表。

為避免 IN，您可以重新排列模式的操作：

DELETE FROM mytable1 WHERE value IN (SELECT key FROM mytable2 WHERE <filtering_condition>)

進入

DELETE t1 
FROM   mytable1 t1
INNER JOIN
      mytable2 t2 
ON     t2.key = ti.id 
WHERE  <filtering_condition>

（在您的情況下， mytable1 和 mytable2 都是同一張表，並且效果也一樣）

我不確定這將如何對您那裡的變數計數語法做出反應（我不是專門從事 mysql 的人，這在我經常使用的其他數據庫中看不到）。

如果您添加一個整數標識列（我假設您的 PK 是id, time因此目前整數 ID 不是唯一的），那麼簡單地根據模 5 檢查它可能是“均勻刪除 80%”的可接受近似值，如下所示：

DELETE t1 
FROM   mytable t1
WHERE  t1.time < "2013-08-08 00:00:00"
AND    t1.counter MOD 5 != 0

最初添加該列將是一個耗時的過程，但之後對其進行維護應該不是問題（數據庫會在每次插入時為您生成一個數字，只需確保您不將其包含在VALUES任何INSERT操作的列表中），但是你不需要使用IN或根本不需要JOIN。time, counter超過（而不是僅僅）的索引time可能有助於提高性能。我很想創建counter主鍵和id, time單獨的索引（以及timeor上的索引time, counter），但這在很大程度上取決於您對數據的其他操作。

當然，一旦您像這樣更改表結構，也要考慮分區選項。它會更複雜，但也可能在其他地方產生顯著的有益性能影響。

引用自：https://dba.stackexchange.com/questions/60104