MySQL安全删除大表

安全地删除大表

一般有两种方法去删除大表

• 删除表空间文件
• delete+where条件进行分批操作
• delete，truncate也可以实现，delete直接删除最坏情况可能是跑崩数据库。

删除表空间文件

MySQL里面直接对大表执行drop table删除有可能导致MySQL Hang住，对业务造成影响。删除超大表的前提是该表是独立表空间，然后按照如下步骤删除才能避免引起业务故障

表中数据量（假设后边还有4个0）

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  1100000 |
+----------+
1 row in set (0.16 sec)

创建ibd,frm硬连接

ln t.ibd t.ibd.hl
ln t.frm t.frm.hl

关闭自适应哈希索引

InnodB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升,则建立哈希索引,称之为自适应哈希索引(Adaptive Hash Index,AHI) AHI是通过缓冲池的B+树页构造而来,因此建立的速度很快,而且不需要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。
drop过程中InnoDB引擎还会删除表对应的AHI（自适应哈希索引）。而这个过程需要持有一把数据字典的互斥锁，可以在DROP TABLE的时候关闭AHI功能。

mysql> set global innodb_adaptive_hash_index=OFF;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GLOBAL VARIABLES LIKE '%HASH%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| innodb_adaptive_hash_index       | OFF   |
| innodb_adaptive_hash_index_parts | 8     |
| metadata_locks_hash_instances    | 8     |
+----------------------------------+-------+
3 rows in set (0.00 sec)

drop 表

此时drop执行速度会非常快,会自动清理.ibd和.frm文件

mysql> drop table t;
Query OK, 0 rows affected (0.95 sec)

ionice工具删除硬连接的文件，truncate工具也可以

[root@DESKTOP-54S04AH testcompress]# ionice -c 2 -n 6 rm t.ibd.hl
[root@DESKTOP-54S04AH testcompress]# ionice -c 2 -n 6 rm t.frm.hl

delete 进行批量删除

例如

delete from table where id < 10000;
sleep 5;
delete from table where id < 20000;
.
.
.
.

pt-archiver可以归档，也可以删除

参数信息：

pt-archiver --help 
--progress 每多少行打印进度信息
--limit  限制select返回的行数
--sleep  指定select语句休眠时间
--txn-size 指定多少行提交一次事务
--bulk-delete 用单个DELETE语句批量删除每个行块。该语句删除块的第一行和最后一行之间的每一行，隐含--commit-each
--dry-run 打印查询，不做任何操作后退出

删除命令：

# 打印查询
$ pt-archiver --source h=192.168.175.230,P=3306,u=root,p='123',D=sbtest,t=t--purge --charset=utf8mb4 --where "id <= 400000" --progress=200  --limit=1000 --sleep=1 --txn-size=1000 --statistics  --dry-run
# 解释：删除sbtest库，sbtest1表数据，字符集为utf8mb4，删除条件是 id <= 1100000 ，每次取出200行进行处理，每处理200行则进行一次提交，每完成一次处理sleep 1s

SELECT /*!40001 SQL_NO_CACHE */ `id`,`k`,`c`,`pad` FROM `sbtest`.`t` FORCE INDEX(`PRIMARY`) WHERE (id <= 400000) AND (`id` < '23132073') ORDER BY `id` LIMIT 1000
SELECT /*!40001 SQL_NO_CACHE */ `id`,`k`,`c`,`pad` FROM `sbtest`.`t` FORCE INDEX(`PRIMARY`) WHERE (id <= 400000) AND (`id` < '23132073') AND ((`id` >= ?)) ORDER BY `id` LIMIT 1000
DELETE FROM `sbtest`.`sbtest1` WHERE (`id` = ?)

# 打开会话保持功能
screen -S archiver


# 执行删除
$ pt-archiver --source h=192.168.175.230,P=3306,u=root,p='123',D=sbtest,t=t--purge --charset=utf8mb4 --where "id <= 1100000 " --progress=200  --limit=1000 --sleep=1 --txn-size=1000 --statistics

小结：

delete操作之后，数据并不会马上删除，是标记为删除，相应空间也不会释放。
如果delete操作没有使用where 索引条件，还会扫描全表，加表锁。
delete过程中产生大量的日志文件。
如果表中数据需要保留一部分数据，可以使用pt-archiver工具进行归档处理，这是最安全的方法，当然要保证操作的表有主键。
如果表中所有数据都不要了，还是通过硬连接的方式，去删除.ibd文件更为高效。