1、需求

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

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

我隨即用

delete from table_name where id < 39000000 limit 10000

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

2、方案

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

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

1. 使用 DELETE 語句分批刪除

一種常見的方法是使用 DELETE 語句，并通過分批刪除數(shù)據(jù)來減輕數(shù)據(jù)庫(kù)的壓力。這可以通過在 DELETE 語句中添加 LIMIT 子句來實(shí)現(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

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

示例 SQL 語句

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

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

-- 刪除原始表
DROP TABLE your_table;

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

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

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

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

-- 清空表
TRUNCATE TABLE your_table;

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

注意事項(xiàng)

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

示例 SQL 腳本

以下是一個(gè)完整的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ù)量非常大，建議先在一個(gè)較小的數(shù)據(jù)集上測(cè)試所選方法的性能，然后再應(yīng)用到整個(gè)表。此外，在執(zhí)行此類操作時(shí)，最好在業(yè)務(wù)低峰期進(jìn)行，以減少對(duì)應(yīng)用程序的影響。

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

3、實(shí)戰(zhàn)

先新建一張copy的表。

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

CREATE TABLE new_table_name LIKE existing_table_name;

接下來二個(gè)方案

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

RENAME TABLE temp TO users;

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

3.1轉(zhuǎn)移

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

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

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

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

最新評(píng)論