MySQL 删除大量数据

效果

操作

数据量太大 无法count

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='new_message_db'
order by data_length desc, index_length desc;

---

存储过程 分批 批量删除

CREATE DEFINER=`dbmanager`@`%` PROCEDURE `DelMessageData`()
BEGIN
	#Routine body goes here...
	DECLARE COILNO VARCHAR(255);
	DECLARE COILNO_CURSOR_COUNT INT DEFAULT 1;
	
    -- 先查出删除条件 in 里面的值（我的是DELIVERYCOILNO），放到游标里
	DECLARE COILNO_CURSOR CURSOR FOR
	SELECT sMessageId FROM new_message_db.t_message WHERE _iDeleteTime > 0 AND _iDeleteTime < UNIX_TIMESTAMP( '20220301' ) limit 100000;
    -- 设置循环的次数（游标的大小）
	DECLARE EXIT HANDLER FOR NOT FOUND SET COILNO_CURSOR_COUNT = 0;
	
	OPEN COILNO_CURSOR;
	
	-- 循环游标
	WHILE COILNO_CURSOR_COUNT <> 0 DO
		FETCH COILNO_CURSOR INTO COILNO;
		-- INSERT INTO qualitydb.test(val2) VALUES(COILNO);
		DELETE FROM new_message_db.t_message WHERE sMessageId = COILNO;
	END WHILE;
	
	CLOSE COILNO_CURSOR;
END

为什么使用存储过程

分析

• 在不切换表的前提下，直接delete 大量数据 ，会产生大量的行锁。例如直接删除一千万数据，会锁一千万条数据，如果中途取消操作，MySQL会回滚，回滚会耗费几倍于删除的时间。所以绝对不能直接删除。
• 使用存储过程，先将要删除的数据主键查询出，存放于游标中，然后在循环中 使用Delete语句 以主键为条件，逐行删除。这样不会产生大量的行锁，及长时间未提交的事务。 在业务请求低峰可以进行百万级数据的删除。

测试数据

使用存储过程批量删除数据 （1核1G的阿里云RDS）
-- 10万条 5min 
-- 100万条 50min
-- 1000万条 8.3H （推测值）
-- 1000条/s

---

优化空间

optimize table new_message_db.t_message

注意

• OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。
• 使用 table 复制方法重建 table
• 执行过程会锁表

OPTIMIZE TABLE将online DDL用于常规 table 和已分区的InnoDBtable，这减少了并发 DML操作的停机时间。由OPTIMIZE TABLE触发并由ALTER TABLE … FORCE进行隐藏的 table 重建已就位。排他table 锁定仅在操作的准备阶段和提交阶段短暂进行。在准备阶段，将更新元数据并创建一个中间 table。在提交阶段，将提交 table元数据更改。

• 区分存储引擎

对于myisam可以直接使用 optimize table table.name, 当是InnoDB引擎时，会报“Table does not support optimize, doing recreate + analyze instead”，一般情况下，由myisam转成innodb，会用alter table table.name engine='innodb’进行转换，优化也可以用这个。所以当是InnoDB引擎时我们就用alter table table.name engine='innodb’来代替optimize做优化就可以。

参考：

optimize table