1、需求

老項目，實時數(shù)據(jù)還是用mysql去存儲的，而且又沒有定時去清理數(shù)據(jù)的程序。目前這些實時數(shù)據(jù)只需保留近半年的即可。

其他幾張實時表數(shù)據(jù)量沒有這么夸張。

我隨即用

delete from table_name where id < 39000000 limit 10000

但是幾億條記錄用這個方法太慢了。

2、方案

雖然之前也處理過類似問題，但還是隨手問了一下AI，有無更優(yōu)解。

在MySQL中刪除一個大表（例如含有幾億條記錄）中的數(shù)據(jù)時，需要特別注意操作的性能和對系統(tǒng)的影響。如果您的目標是刪除半年前的數(shù)據(jù)，您可以采用以下幾種策略來高效地完成這項任務(wù)：

1. 使用 DELETE 語句分批刪除

一種常見的方法是使用 DELETE 語句，并通過分批刪除數(shù)據(jù)來減輕數(shù)據(jù)庫的壓力。這可以通過在 DELETE 語句中添加 LIMIT 子句來實現(xiàn)。

示例 SQL 語句

-- 定義每批刪除的數(shù)量
SET @batch_size = 100000; -- 每次刪除 10 萬條記錄
SET @counter = 0;

-- 確定半年前的日期
SET @six_months_ago = DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

-- 分批刪除數(shù)據(jù)
REPEAT
    DELETE FROM your_table
    WHERE time < @six_months_ago
    LIMIT @batch_size;
    SET @counter = @counter + 1;
UNTIL @counter = 0 END REPEAT;

2. 使用 INPLACE ALTER TABLE

對于InnoDB表，可以使用 ALTER TABLE 來刪除數(shù)據(jù)，這通常比 DELETE 更快。然而，此方法要求您能夠確定刪除的行不會被引用（例如通過外鍵）。此外，這種方法可能不是所有版本的MySQL都支持。

示例 SQL 語句

-- 確定半年前的日期
SET @six_months_ago = DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

-- 創(chuàng)建一個臨時表，只包含需要保留的數(shù)據(jù)
CREATE TABLE temp_table AS SELECT * FROM your_table WHERE time >= @six_months_ago;

-- 刪除原始表
DROP TABLE your_table;

-- 重命名臨時表為原始表的名字
RENAME TABLE temp_table TO your_table;

3. 使用 TRUNCATE TABLE 和重新插入數(shù)據(jù)

如果您可以接受刪除所有數(shù)據(jù)并重新插入數(shù)據(jù)，那么可以使用 TRUNCATE TABLE 來清空整個表，然后只插入最近六個月的數(shù)據(jù)。這種方法適用于那些可以承受完全清空表的場景。

-- 確定半年前的日期
SET @six_months_ago = DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

-- 清空表
TRUNCATE TABLE your_table;

-- 重新插入最近六個月的數(shù)據(jù)
INSERT INTO your_table
SELECT * FROM your_table
WHERE time >= @six_months_ago;

注意事項

• 備份數(shù)據(jù)：在執(zhí)行任何大規(guī)模刪除操作之前，一定要做好數(shù)據(jù)備份以防萬一。
• 監(jiān)控性能：在執(zhí)行刪除操作時，應(yīng)密切監(jiān)控MySQL服務(wù)器的性能指標，如CPU使用率、磁盤I/O等。
• 禁用外鍵檢查：如果表上有外鍵約束，可以暫時禁用外鍵檢查。
• 禁用自動提交：在開始刪除操作之前，可以關(guān)閉自動提交，然后在刪除完成后手動提交事務(wù)。

示例 SQL 腳本

以下是一個完整的SQL腳本示例，使用分批刪除的方式刪除半年前的數(shù)據(jù)：

-- 設(shè)置變量
SET @batch_size = 100000; -- 每次刪除 10 萬條記錄
SET @counter = 0;

-- 確定半年前的日期
SET @six_months_ago = DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

-- 分批刪除數(shù)據(jù)
REPEAT
    DELETE FROM your_table
    WHERE time < @six_months_ago
    LIMIT @batch_size;
    SET @counter = @counter + 1;
UNTIL @counter = 0 END REPEAT;

總結(jié)

選擇哪種方法取決于您的具體需求和資源。如果數(shù)據(jù)量非常大，建議先在一個較小的數(shù)據(jù)集上測試所選方法的性能，然后再應(yīng)用到整個表。此外，在執(zhí)行此類操作時，最好在業(yè)務(wù)低峰期進行，以減少對應(yīng)用程序的影響。

看來也沒有更好的辦法，還是這些老辦法。

3、實戰(zhàn)

先新建一張copy的表。

注意：新建表的自增索引ID，還是會從1開始的。

CREATE TABLE new_table_name LIKE existing_table_name;

接下來二個方案

1、實時數(shù)據(jù)不能停的，先改名，把名稱互換。等數(shù)據(jù)插入一波，再啟動遷移。主要是為了插入一波后自增索引自動調(diào)整。避免沖突

RENAME TABLE temp TO users;

2、實時數(shù)據(jù)能停的，那么就先停掉。再名稱互換，再改動自增索引起點值。這時候其實可以開啟插入程序了。

3.1轉(zhuǎn)移

一張表近半年的數(shù)據(jù)是從390000000差不多開始。那么就好處理了

幾千萬條數(shù)據(jù)也是比較快的。

另一張表更大，有近1-2個億的數(shù)據(jù)，那么我是2kw-3kw 批量一次，也就半小時。即可完成。

到此這篇關(guān)于Mysql刪除幾億條數(shù)據(jù)表中的部分數(shù)據(jù)的方法實現(xiàn)的文章就介紹到這了,更多相關(guān)Mysql刪除億條數(shù)據(jù)表部分數(shù)據(jù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論