概述

生产环境一张日志表占用达到166G，该表一直有数据写入，如果是直接delete/truncate时间过久，故下面采取drop+硬链方式进行删除。

一、删除表原理与优化

drop表时所有的mysql的相关进程都会停止，直到drop结束，mysql才会恢复执行。出现这个情况的原因就是因为，在drop table的时候，innodb维护了一个全局锁，drop完毕锁就释放了。这意味着，如果在白天，访问量非常大的时候，如果你在不做任何处理措施的情况下，执行了删大表的命令，整个mysql就挂在那了，在删表期间，QPS会严重下滑，然后产品经理就来找你喝茶了。

删除表原理上分为2部分：

1、buffer pool页面清除过程

在删除表的时候，Innodb 会将文件在buffer pool中对应的页面清除。

这会涉及到table_cache的lock，如果持有table_cache的lock，这将导致其他查询都无法执行。这种情况在没有innodb_per_table之前尤为严重。

另外，mysql5.5.23之后添加lazy drop table功能，这个功能用来解决mutex on the LRU list。

其中心思想就是加锁，找到需要被删除的page，删除1024个page之后释放锁让其他thread工作，之后loop。而percona的lazy drop处理起来更优雅一些，其会先加锁，然后找到需要被删除的page，标记，释放锁，后台慢慢删除。

对于删除表的页面清除，只需要将页面从flash队列中删除即可，而不需要去做flush操作，减小对系统的冲击。

1)问题1：如果buffer pool很大，或者是在buffer pool中有很多需要被flush的页面，那么此时遍历扫描页面时就会占用比较长的时间，导致其他事务在用到相应buffer pool实例时被阻塞，从而影响整个数据库性能。

优化：涉及源码，优化困难

2、删除ibd磁盘文件的过程

这步在大容量表的时候更为消耗时间，那就是在os上删除物理文件。

1）问题1：表文件过大，直接删除会瞬时占用大量IO，造成IO阻塞

优化：使用硬链接删除

原理：一个磁盘上的文件，可以由多个文件系统的文件引用，这多个文件的完全相同的，都指向同一个磁盘上的文件，当我们删除任何一个文件的时候，都不会影响真实的文件，只是会将其引用数据减1，只有当被引用数目变为1的时候，再次删除文件，才会真正被删除。删除时，这两种情况的区别很明显，一个是在减少被引用数目，一个是真正做IO来删除它

2)问题2：做完硬链，真正的大文件删除问题，直接rm 删除，会造成IO瞬时高峰

优化：使用工具，多次少量的删除

原理：利用系统文件的truncate，脚本工具为slowrm

二、drop大表实例（以下操作同时执行以下命令观察IO：iostat -d -x -k 5 > /tmp/iostat.txt）

基于以上，我们对于大表的删除，应当先建立硬链，drop table后，再删除表数据文件。

对于大表的数据文件，可能会达到10G，也可以是100G级别，甚至更大。在linux下，这样的大文件在使用rm时，无疑会导致IO资源被强行占用，表现为硬盘的io_util基本上是100%左右，会对其它IO操作造成阻塞。更可怕的是，rm单个文件的过程是个原子过程，无法使用kill或kill -9来杀死rm进程，只能乖乖的等待它结束。如果是在繁忙的线上服务所在的机器上做这样的删除操作，很可能会对线上服务产生影响。

1、查看表大小

可以看到需删除的表已经达到166G。

SELECT
	t1.table_schema,
	t1.table_name,
	`ENGINE`,
	table_rows,
	CAST( data_length / 1024.0 / 1024.0 AS DECIMAL ( 10, 2 ) ) `data_size(M)`,
	CAST( index_length / 1024.0 / 1024.0 AS DECIMAL ( 10, 2 ) ) `index_size(M)`,
	t2.ct col_count,
	t3.ct idx_count,
	create_time,
	table_comment 
FROM
	information_schema.TABLES t1
	LEFT JOIN -- 字段总数
	( SELECT table_name, COUNT( 1 ) ct FROM information_schema.COLUMNS GROUP BY table_name ) t2 ON t1.table_name = t2.table_name
	LEFT JOIN -- 索引总数
	( SELECT table_name, COUNT( DISTINCT index_name ) ct FROM information_schema.STATISTICS GROUP BY table_name ) t3 ON t1.table_name = t3.table_name 
WHERE
	t1.table_schema NOT IN ( 'mysql', 'information_schema', 'performance_schema' ) 
ORDER BY
	t1.data_length DESC;

2、备份表结构

--查询是否有其他表外键关联
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE  where REFERENCED_TABLE_NAME='FSL_RATE_LOG'
--备份表结构
create table fsl_rate_log_bak like fsl_rate_log;

3、建硬链接（如果是主从需在从库也设置硬链接）

在DB server上，找到fsl_rate_log表对应的文件，建立硬链接（一个 inode 号对应多个文件名）

cd /tmsdata/datafile/tms_prod
ls -lh fsl_rate_log.*
ln fsl_rate_log.frm  fsl_rate_log.frm.h
ln fsl_rate_log.ibd  fsl_rate_log.ibd.h
--可以发现多了fsl_rate_log.ibd.h/fsl_rate_log.ibd.h文件，且文件的innode均为2。
ls -lh fsl_rate_log.*

4、删除大表（这里不做rename切换）

1）删除大表时需在业务低峰时间再做删除

2）如果是主从实时同步，删完后需检查从库是否也删除了

drop table  fsl_rate_log;
alter table fsl_rate_log_bak rename to fsl_rate_log;
cd /tmsdata/datafile/tms_prod
ls -lh fsl_rate_log.*

5、删除真正的数据文件

如果是生产环境在需要低IO负载删除大文件时，可以使用slowrm。这个后面再介绍吧~

rm -f  fsl_rate_log.frm.h
rm -f  fsl_rate_log.ibd.h

其实想做到对生产环境基本没影响的话可以先对表做rename切换，把要删的表换个名字，然后在用drop+硬链方式删除即可。我这里是因为生产可以停机，所以就直接这样操作了。

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~