[MYSQL] mysql空间问题案例分享

原创

大大刺猬

发布于 2025-02-20 14:21:08

3810

文章被收录于专栏：大大刺猬大大刺猬

导读

某环境自上线以来, 空间使用越来越多. 总是扩空间也不是办法啊. 于是只能看能不能从数据库层面来释放一部分空间了.

分析思路

首先想到的是归档, 即把不需要的历史数据备份到其它地方. 这个需要业务层配合, 实施难度大.
对表进行压缩也是个方法, 但是这就影响到读写速度了. 牺牲性能来保证空间. 先做保留.
还有个办法就是干掉未使用的索引. 可行性较高, 先查询下

我这里没得碎片问题, 实际场景可能还有碎片问题,需要注意下.

处理过程

查询数据量

我们登录数据库查询下, 数据量和索引量的比较

SELECT 
    ROUND(SUM(data_length) / 1024 / 1024 / 1024, 2) AS DATA_GB,
    ROUND(SUM(index_length) / 1024 / 1024 / 1024,
            2) AS INDEX_GB,
    ROUND(SUM(data_free) / 1024 / 1024 / 1024, 2) AS FREE_GB
FROM
    information_schema.tables
WHERE
    table_schema NOT IN ('sys' , 'performance_schema',
        'mysql',
        'information_schema');

实际环境查询出来数据和索引大小差不多, data_free基本上为空

生产环境不方便截图, 我模拟的环境, 量太小,不好看....

查询未使用的索引量

也就是说可能存在未使用的索引的, 然后我们查询下sys.schema_unused_indexes看下未使用的索引

select count(*) from sys.schema_unused_indexes;

查询出来发现有上万个未使用的索引... 数据库启动时间已经超过半年, 说明这部分索引超过半年未使用, 是可以释放的.

其实看对应的表名就能猜出来这部分表是应用程序自动建的, 索引应该也是自动建的, 而建这部分索引的时候并没有考虑后续是否使用...

查询未使用的索引大小

虽然删除这部分索引不会释放空间(OS层面), 但是后续对该表的数据更改操作,会先使用这释放出来的那部分空间(数据库层面). 那我们就来统计下这部分空间有多大吧.

我们可以查询mysql.innodb_index_stats表看对应索引的大小, 虽然是统计信息, 但误差不会太大.

主键索引不算在索引大小里面, 而是算在数据大小里面的

我们查询stat_name=size的就是索引的page数量, 再乘上@@innodb_page_size就是索引大小了. 实际上information_schema.tables就是这么计算的.

-- information_schema.tables 中索引的大小
select data_length,index_length from information_schema.tables where table_schema='db1' and table_name='sbtest1';

-- mysql.innodb_index_stats 中索引大小
select sum(stat_value)*@@innodb_page_size from mysql.innodb_index_stats where database_name='db1' and table_name='sbtest1' and stat_name='size' and index_name = 'PRIMARY';

-- mysql.innodb_index_stats 中数据大小
select sum(stat_value)*@@innodb_page_size from mysql.innodb_index_stats where database_name='db1' and table_name='sbtest1' and stat_name='size' and index_name != 'PRIMARY';

其实我们看下information_schema.tables的表结构就知道了.

-- show create table information_schema.tables; -- mysql-8.0.28
SELECT 
    `cat`.`name` AS `TABLE_CATALOG`,
    `sch`.`name` AS `TABLE_SCHEMA`,
    `tbl`.`name` AS `TABLE_NAME`,
    `tbl`.`type` AS `TABLE_TYPE`,
    IF((`tbl`.`type` = 'BASE TABLE'),
        `tbl`.`engine`,
        NULL) AS `ENGINE`,
    IF((`tbl`.`type` = 'VIEW'), NULL, 10) AS `VERSION`,
    `tbl`.`row_format` AS `ROW_FORMAT`,
    IF((`tbl`.`type` = 'VIEW'),
        NULL,
        INTERNAL_TABLE_ROWS(`sch`.`name`,
                `tbl`.`name`,
                IF((`tbl`.`partition_type` IS NULL),
                    `tbl`.`engine`,
                    ''),
                `tbl`.`se_private_id`,
                (`tbl`.`hidden` <> 'Visible'),
                `ts`.`se_private_data`,
                COALESCE(`stat`.`table_rows`, 0),
                COALESCE(CAST(`stat`.`cached_time` AS UNSIGNED),
                        0))) AS `TABLE_ROWS`,
    IF((`tbl`.`type` = 'VIEW'),
        NULL,
        INTERNAL_AVG_ROW_LENGTH(`sch`.`name`,
                `tbl`.`name`,
                IF((`tbl`.`partition_type` IS NULL),
                    `tbl`.`engine`,
                    ''),
                `tbl`.`se_private_id`,
                (`tbl`.`hidden` <> 'Visible'),
                `ts`.`se_private_data`,
                COALESCE(`stat`.`avg_row_length`, 0),
                COALESCE(CAST(`stat`.`cached_time` AS UNSIGNED),
                        0))) AS `AVG_ROW_LENGTH`,
    IF((`tbl`.`type` = 'VIEW'),
        NULL,
        INTERNAL_DATA_LENGTH(`sch`.`name`,
                `tbl`.`name`,
                IF((`tbl`.`partition_type` IS NULL),
                    `tbl`.`engine`,
                    ''),
                `tbl`.`se_private_id`,
                (`tbl`.`hidden` <> 'Visible'),
                `ts`.`se_private_data`,
                COALESCE(`stat`.`data_length`, 0),
                COALESCE(CAST(`stat`.`cached_time` AS UNSIGNED),
                        0))) AS `DATA_LENGTH`,
    IF((`tbl`.`type` = 'VIEW'),
        NULL,
        INTERNAL_MAX_DATA_LENGTH(`sch`.`name`,
                `tbl`.`name`,
                IF((`tbl`.`partition_type` IS NULL),
                    `tbl`.`engine`,
                    ''),
                `tbl`.`se_private_id`,
                (`tbl`.`hidden` <> 'Visible'),
                `ts`.`se_private_data`,
                COALESCE(`stat`.`max_data_length`, 0),
                COALESCE(CAST(`stat`.`cached_time` AS UNSIGNED),
                        0))) AS `MAX_DATA_LENGTH`,
    IF((`tbl`.`type` = 'VIEW'),
        NULL,
        INTERNAL_INDEX_LENGTH(`sch`.`name`,
                `tbl`.`name`,
                IF((`tbl`.`partition_type` IS NULL),
                    `tbl`.`engine`,
                    ''),
                `tbl`.`se_private_id`,
                (`tbl`.`hidden` <> 'Visible'),
                `ts`.`se_private_data`,
                COALESCE(`stat`.`index_length`, 0),
                COALESCE(CAST(`stat`.`cached_time` AS UNSIGNED),
                        0))) AS `INDEX_LENGTH`,
    IF((`tbl`.`type` = 'VIEW'),
        NULL,
        INTERNAL_DATA_FREE(`sch`.`name`,
                `tbl`.`name`,
                IF((`tbl`.`partition_type` IS NULL),
                    `tbl`.`engine`,
                    ''),
                `tbl`.`se_private_id`,
                (`tbl`.`hidden` <> 'Visible'),
                `ts`.`se_private_data`,
                COALESCE(`stat`.`data_free`, 0),
                COALESCE(CAST(`stat`.`cached_time` AS UNSIGNED),
                        0))) AS `DATA_FREE`,
    IF((`tbl`.`type` = 'VIEW'),
        NULL,
        INTERNAL_AUTO_INCREMENT(`sch`.`name`,
                `tbl`.`name`,
                IF((`tbl`.`partition_type` IS NULL),
                    `tbl`.`engine`,
                    ''),
                `tbl`.`se_private_id`,
                (`tbl`.`hidden` <> 'Visible'),
                `ts`.`se_private_data`,
                COALESCE(`stat`.`auto_increment`, 0),
                COALESCE(CAST(`stat`.`cached_time` AS UNSIGNED),
                        0),
                `tbl`.`se_private_data`)) AS `AUTO_INCREMENT`,
    `tbl`.`created` AS `CREATE_TIME`,
    IF((`tbl`.`type` = 'VIEW'),
        NULL,
        INTERNAL_UPDATE_TIME(`sch`.`name`,
                `tbl`.`name`,
                IF((`tbl`.`partition_type` IS NULL),
                    `tbl`.`engine`,
                    ''),
                `tbl`.`se_private_id`,
                (`tbl`.`hidden` <> 'Visible'),
                `ts`.`se_private_data`,
                COALESCE(CAST(`stat`.`update_time` AS UNSIGNED),
                        0),
                COALESCE(CAST(`stat`.`cached_time` AS UNSIGNED),
                        0))) AS `UPDATE_TIME`,
    IF((`tbl`.`type` = 'VIEW'),
        NULL,
        INTERNAL_CHECK_TIME(`sch`.`name`,
                `tbl`.`name`,
                IF((`tbl`.`partition_type` IS NULL),
                    `tbl`.`engine`,
                    ''),
                `tbl`.`se_private_id`,
                (`tbl`.`hidden` <> 'Visible'),
                `ts`.`se_private_data`,
                COALESCE(CAST(`stat`.`check_time` AS UNSIGNED), 0),
                COALESCE(CAST(`stat`.`cached_time` AS UNSIGNED),
                        0))) AS `CHECK_TIME`,
    `col`.`name` AS `TABLE_COLLATION`,
    IF((`tbl`.`type` = 'VIEW'),
        NULL,
        INTERNAL_CHECKSUM(`sch`.`name`,
                `tbl`.`name`,
                IF((`tbl`.`partition_type` IS NULL),
                    `tbl`.`engine`,
                    ''),
                `tbl`.`se_private_id`,
                (`tbl`.`hidden` <> 'Visible'),
                `ts`.`se_private_data`,
                COALESCE(`stat`.`checksum`, 0),
                COALESCE(CAST(`stat`.`cached_time` AS UNSIGNED),
                        0))) AS `CHECKSUM`,
    IF((`tbl`.`type` = 'VIEW'),
        NULL,
        GET_DD_CREATE_OPTIONS(`tbl`.`options`,
                IF((IFNULL(`tbl`.`partition_expression`,
                            'NOT_PART_TBL') = 'NOT_PART_TBL'),
                    0,
                    1),
                IF((`sch`.`default_encryption` = 'YES'),
                    1,
                    0))) AS `CREATE_OPTIONS`,
    INTERNAL_GET_COMMENT_OR_ERROR(`sch`.`name`,
            `tbl`.`name`,
            `tbl`.`type`,
            `tbl`.`options`,
            `tbl`.`comment`) AS `TABLE_COMMENT`
FROM
    (((((`mysql`.`tables` `tbl`
    JOIN `mysql`.`schemata` `sch` ON ((`tbl`.`schema_id` = `sch`.`id`)))
    JOIN `mysql`.`catalogs` `cat` ON ((`cat`.`id` = `sch`.`catalog_id`)))
    LEFT JOIN `mysql`.`collations` `col` ON ((`tbl`.`collation_id` = `col`.`id`)))
    LEFT JOIN `mysql`.`tablespaces` `ts` ON ((`tbl`.`tablespace_id` = `ts`.`id`)))
    LEFT JOIN `mysql`.`table_stats` `stat` ON (((`tbl`.`name` = `stat`.`table_name`)
        AND (`sch`.`name` = `stat`.`schema_name`))))
WHERE
    ((0 <> CAN_ACCESS_TABLE(`sch`.`name`, `tbl`.`name`))
        AND (0 <> IS_VISIBLE_DD_OBJECT(`tbl`.`hidden`)))

哈哈, 扯远了. 我们回到刚才的问题: 查看未使用的索引的大小.

-- 查询未使用的索引的大小明细
SELECT 
    a.object_schema,
    a.object_name,
    a.index_name,
    b.stat_value * @@innodb_page_size / 1024 / 1024 AS IDX_SIZE_MB
FROM
    sys.schema_unused_indexes a
        JOIN
    mysql.innodb_index_stats b ON a.object_schema = b.database_name
        AND a.object_name = b.table_name
        AND a.index_name = b.index_name
WHERE
    b.stat_name = 'size';

如果要总和的话, 改为sum即可.

-- 查询未使用的索引的大小之和
SELECT round(sum(b.stat_value * @@innodb_page_size)/1024/1024/1024,2) as IDX_SIZE_GB
FROM
    sys.schema_unused_indexes a
        JOIN
    mysql.innodb_index_stats b ON a.object_schema = b.database_name
        AND a.object_name = b.table_name
        AND a.index_name = b.index_name
WHERE
    b.stat_name = 'size';

这部分表很多其实都没得主键, 所以还涉及到主键的新增, 而主键的新增就涉及到表的重建了, 那么索引那部分空间就能释放出来了. 可能会有小伙伴问: (不新增字段)添加主键的话, 空间会不会增加啊? 答:不会, 甚至会减少, 因为没得索引的时候, mysql会自动新增个rowid来作为主键, 而人工指定主键的话, 就不会有这个rowid字段, 所以空间就会减少下来.

然后将上面的结果发给开发,让他们去做即可.

啰嗦一句

再啰嗦一句, 上面的mysql.innodb_index_stats中的信息我们我们也可以使用ibd2sql_web去验证. 该工具可以在浏览器上查看ibd的结构, 表中描述为Number of leaf pages in the index的page实际上是PAGE_LEVEL=1的PAGE, 其实该算为LEAF PAGE的....