引导者## MySQL 删除大量数据
简介
在 MySQL 中，删除大量数据是一个常见的操作，但如果操作不当，可能会导致性能问题，甚至锁表，影响线上业务。本文将介绍几种删除大量数据的方法，并分析它们的优缺点，帮助你选择最合适的方式。
1. `DELETE` 语句
1.1 简单 DELETE
最简单的删除方式是直接使用 `DELETE` 语句，例如：```sqlDELETE FROM table_name WHERE condition;```这种方式适用于删除少量数据，但对于大量数据，会产生大量的日志，导致性能下降。 还会长时间持有锁，阻塞其他操作。
1.2 批量 DELETE
为了减少日志量和锁持有时间，可以将 `DELETE` 操作分批执行：```sqlDELETE FROM table_name WHERE condition LIMIT 10000;```通过 `LIMIT` 关键字限制每次删除的行数，循环执行直到所有符合条件的数据都被删除。 可以使用如下循环：```sqlDELIMITER //CREATE PROCEDURE delete_data_in_batches(IN batch_size INT)BEGINDECLARE done INT DEFAULT FALSE;DECLARE rows_affected INT DEFAULT 0;DECLARE cursor_name CURSOR FOR SELECT 1 FROM table_name WHERE condition;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;OPEN cursor_name;read_loop: LOOPFETCH cursor_name;IF done THENLEAVE read_loop;END IF;DELETE FROM table_name WHERE condition LIMIT batch_size;SET rows_affected = rows_affected + ROW_COUNT();COMMIT; -- 提交事务，释放锁END LOOP;CLOSE cursor_name;END //DELIMITER ;CALL delete_data_in_batches(10000);```
2. `TRUNCATE TABLE` 语句
`TRUNCATE TABLE` 语句可以快速清空整个表，比 `DELETE` 更高效，因为它会重置自增主键并释放表空间。```sql TRUNCATE TABLE table_name; ```
注意:
`TRUNCATE TABLE` 操作不可回滚，并且会重置自增主键。 如果需要保留自增主键，可以使用 `ALTER TABLE table_name AUTO_INCREMENT = 1;`
3. 创建新表并导入数据
如果只需要保留部分数据，可以创建一个新表，将需要保留的数据导入新表，然后删除旧表，并将新表重命名为旧表名。```sql CREATE TABLE new_table LIKE table_name; INSERT INTO new_table SELECT
FROM table_name WHERE condition; -- condition 为保留数据的条件 RENAME TABLE table_name TO old_table, new_table TO table_name; DROP TABLE old_table; ```这种方式对磁盘IO的压力较小，速度也很快。
4. 使用 `DROP PARTITION` (分区表)
如果表是分区表，可以删除特定的分区来删除数据。```sql ALTER TABLE table_name DROP PARTITION partition_name; ```这种方式非常高效，尤其适用于按日期分区的数据。
总结
选择哪种方式取决于具体的需求和数据量：
少量数据：直接使用 `DELETE`。
大量数据且需要保留部分数据：创建新表并导入数据。
清空整个表：使用 `TRUNCATE TABLE`。
分区表：使用 `DROP PARTITION`。
大量数据需要分批删除并且需要控制锁的影响：使用批量 `DELETE` 配合存储过程和 `COMMIT`。选择合适的方法可以有效提高删除效率，减少对数据库性能的影响。 记住在操作之前备份数据，以防止误操作造成数据丢失。希望这篇文章对你有所帮助!
MySQL 删除大量数据**简介**在 MySQL 中，删除大量数据是一个常见的操作，但如果操作不当，可能会导致性能问题，甚至锁表，影响线上业务。本文将介绍几种删除大量数据的方法，并分析它们的优缺点，帮助你选择最合适的方式。**1. `DELETE` 语句*** **1.1 简单 DELETE**最简单的删除方式是直接使用 `DELETE` 语句，例如：```sqlDELETE FROM table_name WHERE condition;```这种方式适用于删除少量数据，但对于大量数据，会产生大量的日志，导致性能下降。 还会长时间持有锁，阻塞其他操作。* **1.2 批量 DELETE**为了减少日志量和锁持有时间，可以将 `DELETE` 操作分批执行：```sqlDELETE FROM table_name WHERE condition LIMIT 10000;```通过 `LIMIT` 关键字限制每次删除的行数，循环执行直到所有符合条件的数据都被删除。 可以使用如下循环：```sqlDELIMITER //CREATE PROCEDURE delete_data_in_batches(IN batch_size INT)BEGINDECLARE done INT DEFAULT FALSE;DECLARE rows_affected INT DEFAULT 0;DECLARE cursor_name CURSOR FOR SELECT 1 FROM table_name WHERE condition;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;OPEN cursor_name;read_loop: LOOPFETCH cursor_name;IF done THENLEAVE read_loop;END IF;DELETE FROM table_name WHERE condition LIMIT batch_size;SET rows_affected = rows_affected + ROW_COUNT();COMMIT; -- 提交事务，释放锁END LOOP;CLOSE cursor_name;END //DELIMITER ;CALL delete_data_in_batches(10000);```**2. `TRUNCATE TABLE` 语句**`TRUNCATE TABLE` 语句可以快速清空整个表，比 `DELETE` 更高效，因为它会重置自增主键并释放表空间。```sql TRUNCATE TABLE table_name; ```**注意:** `TRUNCATE TABLE` 操作不可回滚，并且会重置自增主键。 如果需要保留自增主键，可以使用 `ALTER TABLE table_name AUTO_INCREMENT = 1;`**3. 创建新表并导入数据**如果只需要保留部分数据，可以创建一个新表，将需要保留的数据导入新表，然后删除旧表，并将新表重命名为旧表名。```sql CREATE TABLE new_table LIKE table_name; INSERT INTO new_table SELECT * FROM table_name WHERE condition; -- condition 为保留数据的条件 RENAME TABLE table_name TO old_table, new_table TO table_name; DROP TABLE old_table; ```这种方式对磁盘IO的压力较小，速度也很快。**4. 使用 `DROP PARTITION` (分区表)**如果表是分区表，可以删除特定的分区来删除数据。```sql ALTER TABLE table_name DROP PARTITION partition_name; ```这种方式非常高效，尤其适用于按日期分区的数据。**总结**选择哪种方式取决于具体的需求和数据量：* 少量数据：直接使用 `DELETE`。 * 大量数据且需要保留部分数据：创建新表并导入数据。 * 清空整个表：使用 `TRUNCATE TABLE`。 * 分区表：使用 `DROP PARTITION`。 * 大量数据需要分批删除并且需要控制锁的影响：使用批量 `DELETE` 配合存储过程和 `COMMIT`。选择合适的方法可以有效提高删除效率，减少对数据库性能的影响。 记住在操作之前备份数据，以防止误操作造成数据丢失。希望这篇文章对你有所帮助!
