欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL批量刪除海量數(shù)據(jù)的幾種方法總結(jié)

 更新時(shí)間:2024年11月07日 11:01:36   作者:hsukk17  
在數(shù)據(jù)庫(kù)的日常維護(hù)中,我們經(jīng)常遇到需要?jiǎng)h除大量數(shù)據(jù)的場(chǎng)景,例如,刪除過(guò)期日志、清理歷史數(shù)據(jù)等,但如果一次性刪除大量數(shù)據(jù),可能會(huì)導(dǎo)致鎖表、事務(wù)日志暴增、影響數(shù)據(jù)庫(kù)性能等問(wèn)題,本文將介紹幾種高效批量刪除 MySQL 海量數(shù)據(jù)的方法,需要的朋友可以參考下

一、問(wèn)題分析

一次性刪除大量數(shù)據(jù)的主要問(wèn)題在于:

  1. 長(zhǎng)時(shí)間鎖表:大量刪除操作會(huì)導(dǎo)致數(shù)據(jù)庫(kù)長(zhǎng)時(shí)間加鎖,影響其他事務(wù)的正常操作。
  2. 事務(wù)日志暴增:MySQL 在刪除數(shù)據(jù)時(shí)會(huì)記錄事務(wù)日志,大量刪除操作可能導(dǎo)致日志文件過(guò)大,甚至撐滿磁盤。
  3. 影響性能:一次性刪除大量數(shù)據(jù)會(huì)占用大量的 CPU 和 IO 資源,對(duì)數(shù)據(jù)庫(kù)整體性能產(chǎn)生嚴(yán)重影響。

為避免這些問(wèn)題,可以考慮分批刪除等策略來(lái)減少對(duì)數(shù)據(jù)庫(kù)的壓力。

二、批量刪除海量數(shù)據(jù)的幾種方法

方法 1:使用 LIMIT 分批刪除

LIMIT 分批刪除是一種常用的處理海量數(shù)據(jù)的方式。每次刪除固定數(shù)量的數(shù)據(jù),循環(huán)執(zhí)行,直至刪除完畢。

示例 SQL:

假設(shè)我們要?jiǎng)h除 logs 表中創(chuàng)建時(shí)間在某個(gè)日期之前的所有數(shù)據(jù):

-- 設(shè)置每批刪除的行數(shù)
SET @BATCH_SIZE = 1000;
 
-- 分批刪除符合條件的數(shù)據(jù)
DELETE FROM logs 
WHERE create_time < '2023-01-01' 
LIMIT @BATCH_SIZE;

可以將上述語(yǔ)句放入存儲(chǔ)過(guò)程或在應(yīng)用層循環(huán)調(diào)用。每次刪除 BATCH_SIZE 行數(shù)據(jù),減少鎖表時(shí)間和日志生成量。

優(yōu)點(diǎn):

  • 控制單次刪除的量,減少鎖表時(shí)間和日志生成量。

缺點(diǎn):

  • 需要循環(huán)多次操作,邏輯稍復(fù)雜。

注意:

  • 分批刪除的 LIMIT 值可以根據(jù)實(shí)際環(huán)境調(diào)整。通常 500 到 5000 是較合理的選擇。

方法 2:通過(guò)主鍵范圍分批刪除

如果要?jiǎng)h除的數(shù)據(jù)在主鍵上是連續(xù)的(如自增 ID),可以按主鍵范圍分批刪除。這樣能夠避免 LIMIT 的偏移開銷,提高刪除效率。

示例 SQL:

假設(shè) logs 表的主鍵是 id

-- 設(shè)置每批刪除的范圍
SET @start_id = 0;
SET @end_id = 1000;
 
WHILE (@start_id < (SELECT MAX(id) FROM logs WHERE create_time < '2023-01-01')) DO
    DELETE FROM logs
    WHERE id BETWEEN @start_id AND @end_id
    AND create_time < '2023-01-01';
 
    -- 更新刪除范圍
    SET @start_id = @end_id + 1;
    SET @end_id = @end_id + 1000;
END WHILE;

優(yōu)點(diǎn):

  • 主鍵范圍分批避免了 LIMIT 偏移帶來(lái)的開銷。

缺點(diǎn):

  • 需要知道主鍵范圍,且適用于有連續(xù)主鍵的數(shù)據(jù)表。

方法 3:通過(guò)自定義批量刪除存儲(chǔ)過(guò)程

可以將批量刪除邏輯封裝成存儲(chǔ)過(guò)程,利用存儲(chǔ)過(guò)程自動(dòng)控制批量刪除過(guò)程。

示例 SQL:

DELIMITER $$
 
CREATE PROCEDURE batch_delete_logs()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE batch_size INT DEFAULT 1000;
 
    WHILE NOT done DO
        DELETE FROM logs 
        WHERE create_time < '2023-01-01' 
        LIMIT batch_size;
 
        -- 檢查是否還有剩余數(shù)據(jù)
        IF ROW_COUNT() < batch_size THEN
            SET done = TRUE;
        END IF;
    END WHILE;
END $$
 
DELIMITER ;

執(zhí)行存儲(chǔ)過(guò)程:

CALL batch_delete_logs();

優(yōu)點(diǎn):

  • 存儲(chǔ)過(guò)程實(shí)現(xiàn)自動(dòng)化,邏輯清晰,避免多次手動(dòng)執(zhí)行 SQL。

缺點(diǎn):

  • 適用于支持存儲(chǔ)過(guò)程的場(chǎng)景,對(duì)小批量刪除非常適合。

方法 4:創(chuàng)建臨時(shí)表替換舊表

在某些情況下,刪除大表中的大量數(shù)據(jù)可以通過(guò)創(chuàng)建新表的方法完成。即先將需要保留的數(shù)據(jù)轉(zhuǎn)移到新表,再刪除舊表。這種方法可以減少鎖表時(shí)間和日志開銷。

步驟:

  1. 創(chuàng)建一個(gè)新表(結(jié)構(gòu)與舊表相同)。
  2. 將需要保留的數(shù)據(jù)插入新表。
  3. 刪除舊表,重命名新表為原表名。

示例 SQL:

-- 創(chuàng)建新表
CREATE TABLE logs_new LIKE logs;
 
-- 插入需要保留的數(shù)據(jù)
INSERT INTO logs_new
SELECT * FROM logs WHERE create_time >= '2023-01-01';
 
-- 刪除舊表并重命名新表
DROP TABLE logs;
RENAME TABLE logs_new TO logs;

優(yōu)點(diǎn):

  • 避免了大規(guī)模的刪除操作,減少了鎖表時(shí)間和日志。

缺點(diǎn):

  • 需要額外的磁盤空間來(lái)存放新表數(shù)據(jù)。
  • 在業(yè)務(wù)量大的情況下,可能需要進(jìn)行額外的鎖機(jī)制控制。

三、性能優(yōu)化建議

  1. 避免在業(yè)務(wù)高峰期進(jìn)行大規(guī)模刪除,可以選擇在夜間等業(yè)務(wù)低峰期執(zhí)行。
  2. 適當(dāng)設(shè)置批量大小。批量刪除時(shí),LIMIT 的大小需要根據(jù)實(shí)際情況調(diào)整,不宜過(guò)大,防止長(zhǎng)時(shí)間鎖表。
  3. 關(guān)閉不必要的日志。在某些極端情況下,可以關(guān)閉 MySQL 的二進(jìn)制日志(binlog)來(lái)減少日志開銷,但此操作有風(fēng)險(xiǎn),應(yīng)在充分了解后謹(jǐn)慎使用。

總結(jié)

方法適用場(chǎng)景優(yōu)點(diǎn)缺點(diǎn)
LIMIT 分批刪除需要簡(jiǎn)單分批刪除邏輯簡(jiǎn)單,減少鎖表時(shí)間需循環(huán)操作
主鍵范圍分批刪除有連續(xù)主鍵的表高效,無(wú)偏移開銷需手動(dòng)指定范圍
自定義批量刪除存儲(chǔ)過(guò)程小批量刪除自動(dòng)化操作需要數(shù)據(jù)庫(kù)支持存儲(chǔ)過(guò)程
臨時(shí)表替換刪除數(shù)據(jù)量非常大避免鎖表,減少日志開銷需要額外磁盤空間

根據(jù)不同的業(yè)務(wù)場(chǎng)景和需求,選擇合適的批量刪除方式可以提高 MySQL 的刪除效率,減少對(duì)數(shù)據(jù)庫(kù)的影響。希望本文對(duì)大家在 MySQL 的數(shù)據(jù)清理和維護(hù)上有所幫助!

以上就是MySQL批量刪除海量數(shù)據(jù)的幾種方法總結(jié)的詳細(xì)內(nèi)容,更多關(guān)于MySQL批量刪除數(shù)據(jù)的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!

相關(guān)文章

  • MySQL數(shù)據(jù)庫(kù)的約束限制詳解

    MySQL數(shù)據(jù)庫(kù)的約束限制詳解

    這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)的約束限制詳解,數(shù)據(jù)庫(kù)的約束是對(duì)表中數(shù)據(jù)進(jìn)行的一種限制,為了保證數(shù)據(jù)的正確性、有效性、完整性,下文簡(jiǎn)單介紹需要的朋友可以參考一下
    2022-08-08
  • MySQL表的CURD使用

    MySQL表的CURD使用

    文章主要介紹了MySQL數(shù)據(jù)庫(kù)操作的基本方法,包括創(chuàng)建表、插入數(shù)據(jù)、查詢數(shù)據(jù)、更新數(shù)據(jù)和刪除數(shù)據(jù)等,涵蓋了表結(jié)構(gòu)設(shè)計(jì)、數(shù)據(jù)插入、查詢條件、排序、分頁(yè)、更新和刪除操作,以及聚合函數(shù)和GROUP BY子句的使用
    2025-02-02
  • MySQL如何用分隔符分隔字符串

    MySQL如何用分隔符分隔字符串

    這篇文章主要介紹了MySQL如何用分隔符分隔字符串,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
    2022-08-08
  • MySql帶OR關(guān)鍵字的多條件查詢語(yǔ)句

    MySql帶OR關(guān)鍵字的多條件查詢語(yǔ)句

    MySQL帶OR關(guān)鍵字的多條件查詢,與AND關(guān)鍵字不同,OR關(guān)鍵字,只要記錄滿足任意一個(gè)條件,就會(huì)被查詢出來(lái)。即AND的優(yōu)先級(jí)高于OR
    2017-07-07
  • 關(guān)于Mysql查詢帶單引號(hào)及插入帶單引號(hào)字符串問(wèn)題

    關(guān)于Mysql查詢帶單引號(hào)及插入帶單引號(hào)字符串問(wèn)題

    本文主要介紹的是用mysql_real_escape_string對(duì)用戶提交的表單數(shù)據(jù)進(jìn)行轉(zhuǎn)義處理和通過(guò)addslashes以及mysql_escape_string這3個(gè)類似功能的函數(shù)用法區(qū)別
    2013-04-04
  • mysql prompt的用法詳解

    mysql prompt的用法詳解

    本篇文章是對(duì)mysql中prompt的用法進(jìn)行了詳細(xì)的分析介紹,需要的朋友參考下
    2013-06-06
  • MYSQL建立外鍵失敗幾種情況記錄Can''t create table不能創(chuàng)建表

    MYSQL建立外鍵失敗幾種情況記錄Can''t create table不能創(chuàng)建表

    當(dāng)你試圖在mysql中創(chuàng)建一個(gè)外鍵的時(shí)候,這個(gè)出錯(cuò)會(huì)經(jīng)常發(fā)生,這是非常令人沮喪的。
    2011-08-08
  • Mysql實(shí)現(xiàn)合并多個(gè)分組(GROUP_CONCAT及其平替函數(shù))

    Mysql實(shí)現(xiàn)合并多個(gè)分組(GROUP_CONCAT及其平替函數(shù))

    MySQL 中提供了多種合并字符串的函數(shù)和操作方法,包括 GROUP_CONCAT、CONCAT_WS 和 CONCAT 等,本文介紹了 MySQL 中 GROUP_CONCAT 函數(shù)以及 CONCAT_WS、CONCAT 函數(shù)并通過(guò)示例代碼演示了它們的用法,感興趣的可以了解一下
    2023-10-10
  • 如何修改MySQL字符集

    如何修改MySQL字符集

    這篇文章主要介紹了如何修改MySQL字符集,幫助大家更好的理解和使用MySQL數(shù)據(jù)庫(kù),感興趣的朋友可以了解下
    2020-09-09
  • MySQL如何使用視圖插入修改刪除數(shù)據(jù)

    MySQL如何使用視圖插入修改刪除數(shù)據(jù)

    視圖除了進(jìn)行查詢記錄外,也可以利用視圖進(jìn)行插入、更新、刪除記錄的操作,減少對(duì)基表中信息的直接操作,提高了數(shù)據(jù)的安全性,下面這篇文章主要給大家介紹了關(guān)于MySQL如何使用視圖插入修改刪除數(shù)據(jù)的相關(guān)資料,需要的朋友可以參考下
    2023-06-06

最新評(píng)論