mysql各种数据统计优化（缩减表空间占用测试）

随着业务的发展，数据库数据规模不断增加，我们就要对数据库容量进行扩容，RDS 一般支持在线扩容，但是数据库成本还是比较高的，一般我们需要分析一下数据的增长，并确认数据的保留周期。如果有些数据不需要一直保留，例如保留 3 个月就可以了，那么，我们就可以对这部分数据进行备份，然后删除这部分数据。下面做一个测试。

创建 test 测试表，包含三个字段，两个二级索引。

CREATE TABLE `test` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `b` (`b`));

然后查看一下，数据文件的大小，如下图：

查询一下 MySQL 提供的统计信息，命令及结果如下：

SELECT CONCAT( table_schema, '.', table_name ) AS 'Table Name', CONCAT( ROUND( table_rows / 1000, 4 ), 'K' ) AS 'Number of Rows', CONCAT( ROUND( data_length /( 1024 ), 4 ), 'K' ) AS 'Data Size', CONCAT( ROUND( index_length /( 1024 ), 4 ), 'K' ) AS 'Index Size', CONCAT( ROUND(( data_length index_length )/( 1024 ), 4 ), 'K' ) AS 'Total' FROM information_schema.TABLES WHERE table_schema LIKE 'mysql_study' and table_name like 'test';

现在我们往测试表 test 中，插入 10 万条数据。

delimiter ;; create procedure idata() begin declare i int; set i=1; while(i<=10000)do insert into test values(i, i, i); set i=i 1; end while; end;; delimiter ; call idata();

插入 10 万数据后，文件占用的大小如下图：

Number of Rows 为 9.98K，不是 10 K。information_schema.TABLES 中数据为统计值，存在偏差。

我们尝试使用 delete 删除 test 表中数据。

delete from test;

删除后，数据文件大小如下图：

由上图可以发现，文件没有变小，统计信息如下图，它发生了变化。

现在我们尝试重建表，使用下面的命令

alter table test engine=InnoDB;

重建表后，数据库文件大小如下图：

由上图可以发现，文件变小了，恢复到，表新建时的大小，达到了我们缩减表空间的目的。 下图是此时的 MySQL 的统计信息。

1、information_schema.TABLES 数据为统计值；

2、表空间占用要看实际文件大小；

3、重建表可以缩减表空间占用。