-- 創(chuàng)建時間分區(qū)表
CREATE TABLE logs (
    id BIGINT AUTO_INCREMENT,
    event TEXT,
    log_time DATETIME
) PARTITION BY RANGE (YEAR(log_time)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022)
);

-- 直接刪除整個分區(qū)（秒級完成）
ALTER TABLE logs DROP PARTITION p2020;

-- 每次刪除10萬條，循環(huán)執(zhí)行
WHILE (EXISTS (SELECT 1 FROM orders WHERE create_time < '2025-01-01' LIMIT 1)) DO
    START TRANSACTION;
    DELETE FROM orders 
    WHERE create_time < '2025-01-01' 
    ORDER BY id 
    LIMIT 100000;
    COMMIT;
    DO SLEEP(0.5); -- 避免鎖競爭
END WHILE;

-- 創(chuàng)建臨時表存儲待刪主鍵
CREATE TEMPORARY TABLE tmp_ids 
ENGINE=Memory
SELECT id FROM large_table WHERE condition LIMIT 100000;

-- 通過主鍵關聯刪除
DELETE FROM large_table
WHERE id IN (SELECT id FROM tmp_ids);

-- 將歷史數據歸檔到獨立表
CREATE TABLE archive_table LIKE original_table;
INSERT INTO archive_table 
SELECT * FROM original_table 
WHERE create_time < '2025-01-01';

-- 清空原表后重建
TRUNCATE TABLE original_table;

-- 創(chuàng)建內存索引加速查詢
ALTER TABLE huge_table ADD INDEX idx_temp (create_time) USING BTREE;
DELETE FROM huge_table WHERE create_time < '2025-01-01';

-- 查看當前刪除進度
SHOW PROCESSLIST;
-- 監(jiān)控鎖等待
SELECT * FROM information_schema.INNODB_TRX;
-- 觀察redo log寫入量
SHOW ENGINE INNODB STATUS;

-- 創(chuàng)建恢復點
SAVEPOINT delete_savepoint;
-- 錯誤時回滾
ROLLBACK TO delete_savepoint;

-- 通過binlog實現延遲刪除
SET @binlog_pos = (SELECT position FROM mysql.binlog WHERE event_type = 'delete');
-- 誤刪后回滾
mysqlbinlog --stop-position=@binlog_pos binlog.000001 | mysql -u root

[mysqld]
innodb_buffer_pool_size = 128G  # 占物理內存80%
innodb_log_file_size = 4G       # 減少日志刷盤頻率
max_allowed_packet = 256M       # 避免大事務報錯