近期发现阿里云的数据库备份一直在收钱,咨询发现是数据库大小超出免费限额,超出部分收取费用,不超出的免费。
然后我就查看了下数据库的大小以及每张表存储数据的大小
USE information_schema;
SELECT CONCAT(ROUND(SUM((data_length+index_length)/1024/1024),2),'MB') AS DATA FROM TABLES
WHERE table_schema = 'xxx_prod'
看到数据库总大小约17G
SELECT table_schema,table_name,data_free,
TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)',
TRUNCATE ( ( data_length + index_length ) / 1024 / 1024, 2 ) AS '总大小(MB)'
FROM information_schema.TABLES
ORDER BY ( data_length + index_length ) DESC
其中前几张表数据比较大的约4G,而且出现了索引的数据比数据本身占用了更多的存储。
1、首先解决方法是对没用的数据做了备份,数据容量少了一部分,单索引容量不明显。
2、再对表的索引进行了优化,过多重复数据的索引列取消了,再查看下发下还是没有变化。
3、检查到自增的主键已经达到千万级别
之前不用的数据已经清理或者备份了,若自增主键从1开始会节省很多的占用空间,所以先进行了更改下现有的主键
UPDATE table_name SET id = id - 最小id+1
让主键从1开始重新排列
再重置下自增主键
ALTER TABLE table_name AUTO_INCREMENT=最大ID-最小ID;
之后再查看下索引容量有明显下降,配置好备份策略就可以继续免费使用了