MySQL 大表关联删除

在处理大型MySQL表关联删除时，可以考虑以下步骤和策略：

1. 分析表结构，确定关联关系。

2. 使用临时表或者中间表存储需要删除的数据ID。

3. 按照合适的数据量大小批量删除数据。

4. 考虑使用事务来保证数据一致性。

-- 假设有两个表，orders（主表）和order_details（子表）通过order_id关联
 
-- 1. 开始事务
START TRANSACTION;
 
-- 2. 创建临时表存储需要删除的order_id
CREATE TEMPORARY TABLE temp_orders_to_delete (
  order_id INT PRIMARY KEY
);
 
-- 3. 分批获取需要删除的order_id
INSERT INTO temp_orders_to_delete (order_id)
SELECT o.id
FROM orders o
JOIN order_details od ON o.id = od.order_id
WHERE od.some_condition = true; -- 替换为你的条件
 
-- 4. 分批删除主表和子表数据
DECLARE handler_cnt INT DEFAULT 0;
DECLARE continue_handler INT DEFAULT 1;
 
-- 循环删除，直到临时表中没有更多数据
REPEAT
  -- 每次批次删除一部分数据，可以根据需要调整批次大小
  DELETE o FROM orders o JOIN temp_orders_to_delete tod ON o.id = tod.order_id LIMIT 1000;
  DELETE d FROM order_details d JOIN temp_orders_to_delete tod ON d.order_id = tod.order_id LIMIT 1000;
  
  SET handler_cnt = ROW_COUNT();
  UNTIL handler_cnt = 0
END REPEAT;
 
-- 5. 提交事务
COMMIT;
 
-- 6. 删除临时表
DROP TEMPORARY TABLE IF EXISTS temp_orders_to_delete;

 注意：

• 批次大小（即每次DELETE的LIMIT值）应根据服务器性能和数据库负载情况进行调整。

• 这个脚本假设orders和order_details表之间存在外键关系，如果没有，你需要先删除子表中的关联数据。

• 在执行删除操作之前，请确保你有适当的备份和恢复策略，以及足够的权限来执行这些操作。

 分批删除

DELIMITER //
CREATE PROCEDURE batch_delete_data()
BEGIN
  DECLARE deleted_rows INT DEFAULT 1;
  WHILE deleted_rows > 0 DO
    DELETE FROM your_table WHERE your_condition LIMIT 1000;
    SET deleted_rows = ROW_COUNT();
    COMMIT;
  END WHILE;
END //
DELIMITER ;

CALL batch_delete_data();

在上面的代码中，your_table是需要删除数据的表名，your_condition是确定哪些数据需要被删除的条件。每次删除1000行数据，通过ROW_COUNT()函数获取被删除的行数，当没有数据被删除时退出循环。

 分区表删除

 如果你的表数据量非常庞大，并且数据的删除条件与时间有关，那么可以考虑使用分区表的方式进行数据删除。分区表可以根据某个字段将数据分散到不同的分区中，当需要删除数据时，直接删除整个分区，这样的性能将远远高于删除单条记录。

-- 增加分区
ALTER TABLE your_table PARTITION BY RANGE (TO_DAYS(your_date_column)) (
    PARTITION p0 VALUES LESS THAN (TO_DAYS('2023-01-01')),
    PARTITION p1 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    ...
);

-- 删除分区
ALTER TABLE your_table DROP PARTITION p0;

在上面的代码中，your_table是需要删除数据的表名，your_date_column是表中的日期字段，根据这个字段进行分区。通过ALTER TABLE语句添加或删除分区。 

导出-清理-导入

# 导出数据
mysqldump -u username -p dbname your_table > your_table.sql

# 使用文本编辑工具或脚本对your_table.sql文件进行数据清理

# 导入数据
mysql -u username -p dbname < your_table.sql