mysql大表使用optimize优化碎片空间

mysql在互联网行业应用非常广泛，小到几个人的创业公司、大到一些巨头公司都在用。在运维mysql时，经常会遇到大表问题；当业务比较小的时候，一般采用一主一备+单表进行存储，随着业务发展，某些业务表中的记录就会膨胀的厉害，出现几百万、甚至几千万、上亿的行数也是会出现的。这时候就要考虑对表做改造了，常规方法都是分库分表，按业务垂直拆分或者水平拆分。

这两天公司内有个测试平台的库基本满了，有个库占了几百G的空间，需要清理一下了。

1. 先看看哪些表占用空间大

   SELECT table_name AS 表名
   , ROUND((data_length + index_length) / 1024 / 1024, 2) AS 总大小_MB
   , ROUND(data_length / 1024 / 1024, 2) AS 数据大小_MB
   , ROUND(index_length / 1024 / 1024, 2) AS 索引大小_MB
   , ROUND(data_free / 1024 / 1024, 2) AS 碎片空间_MB
   , table_rows AS 行数, ROUND(avg_row_length, 2) AS 平均行长
   FROM information_schema.tables
   WHERE table_schema = 'your_table_name'
   ORDER BY 总大小_MB DESC;

   
   可以看到case_run表行数100多万，占用空间200G，由于该表存储的是测试用例的执行结果，并不是什么重要的业务数据，所以可以执行删掉，然后清理磁盘碎片即可。注：如果是线上业务，一般是不会直接delete的。

2. delete比较老的数据，只留下最近几周的即可
   由于表里只有主键id有索引，其他字段都没有索引，所以想查看具体行数非常慢select count(1) from your_table_name，这语句会扫全表，执行效率很低，且浪费机器资源。可以直接查最大id和最小id，来大致判断库里有多少数据，select max(id), min(id) from your_table_name，然后直接delete from your_table_name where id < XXX删掉即可。
   如果要删的表数据很多，最好是分段删除。

3. 清理磁盘碎片
   注意，delete表数据之后，mysql是不会直接清掉磁盘空间的，而是把删除的数据打上标记，等到后续再来记录的时候，会覆盖这些记录，所以如果想立刻回收磁盘空间，还需要对mysql表做清理，即optimize table your_table_name。
   在mysql innodb引擎，5.7版本以后，optimize命令已经不会对整个表加锁了，而是只在开始和复制后短暂加锁，理论上只会影响很短时间，所以不影响其他增删改查。但是，对于生产环境，流量很大的库表，还是要慎重，最好选择晚上流量少的时间，并且要做演练和预案。
   删除了大部分数据之后，整个表小了很多
   

4. 如果业务能允许直接drop或者truncate表是简单的，秒级。不过要注意一点，如果是innodb引擎，并且没有开启innodb_file_per_table的话，是不是立刻释放磁盘的，也需要执行optimize table your_table_name。

Tags: mysql