随着业务的发展,数据库数据规模不断增加,我们就要对数据库容量进行扩容,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各种数据统计优化(缩减表空间占用测试)(1)

查询一下 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';

mysql各种数据统计优化(缩减表空间占用测试)(2)

插入 10 万条数据

现在我们往测试表 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 万数据后,文件占用的大小如下图:

mysql各种数据统计优化(缩减表空间占用测试)(3)

mysql各种数据统计优化(缩减表空间占用测试)(4)

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

删除数据

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

delete from test;

删除后,数据文件大小如下图:

mysql各种数据统计优化(缩减表空间占用测试)(5)

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

mysql各种数据统计优化(缩减表空间占用测试)(6)

重建表

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

alter table test engine=InnoDB;

重建表后,数据库文件大小如下图:

mysql各种数据统计优化(缩减表空间占用测试)(7)

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

mysql各种数据统计优化(缩减表空间占用测试)(8)

结论

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

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

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

,