mysql分区表占用大量容量处理（优化）及归档分区表

背景

生产环境中，某个分区表两三年了，占用磁盘1T多，需要对前几年的数据做归档

点击查看代码

SELECT
  table_schema as '数据库',
  table_name as '表名',
  table_rows as '记录数',
  truncate(data_length/1024/1024, 2) as '数据容量(MB)',
  truncate(index_length/1024/1024, 2) as '索引容量(MB)',
  truncate(DATA_FREE/1024/1024, 2) as '碎片占用(MB)'
from 
  information_schema.tables
where 
  table_schema='库名'and table_name='表名'
order by 
  data_length desc, index_length desc;

分析

碎片占用大量的容量，很多分区表都没数据

一种可能是统计信息没更新。

TABLE_ROWS 列显示的是表或分区的行数估计值，而不是实际行数。这个估计值可能不准确，尤其是在表数据频繁变化的情况下。该表从生产上线之后就没有优化。可能就存在该问题。

一种可能是数据页未释放。

即使分区中的数据已经被删除，数据页可能还没有被完全释放，因此 数据容量 和 索引容量 仍然显示占用空间。

解决方法

通过ANALYZE TABLE xxxx,来分析和优化表

该命令主要用于收集表的统计信息，这些信息对于查询优化器来说非常重要，因为优化器会根据这些统计信息来选择最合适的执行计划。
它具体完成了以下任务：
1.更新索引统计信息：这个命令会扫描表中的数据，并重新计算索引的分布情况，如键值的分布等。这对于优化查询性能非常有帮助，因为查询优化器可以根据最新的统计信息来做出更好的决策。
2.检查并修复某些类型的表损坏：虽然 ANALYZE TABLE 并不是一个专门用来修复损坏表的工具，但它可以在一定程度上帮助识别表中可能存在的问题。如果表确实存在一些轻微的问题，该命令可能会尝试解决这些问题。
3.输出表的状态信息：执行 ANALYZE TABLE 后，MySQL 会返回一个结果集，其中包含关于表的状态信息，例如是否成功进行了分析、是否有错误发生等。

通过OPTIMIZE TABLE xxxx，来优化表

用于对表进行优化，主要目的是通过重组表的数据文件和索引文件来提高表的性能。当表经历了大量的插入、删除或更新操作后，数据文件和索引文件可能会变得碎片化，这会影响查询效率。OPTIMIZE TABLE 命令可以帮助减少这种碎片化，回收未使用的空间，重新组织数据和索引，从而提升表的读写性能。
它具体完成了以下任务：
1.回收空间：当表中的行被删除时，这些行占用的空间并不会立即归还给操作系统，而是被标记为可用空间供未来的插入操作使用。OPTIMIZE TABLE 可以回收这些未使用的空间，并将它们归还给操作系统。
2.重组数据文件：随着表的使用，数据文件可能会变得碎片化。OPTIMIZE TABLE 会创建一个新的数据文件，将旧的数据文件中的数据按顺序写入新文件中，然后删除旧的数据文件。这个过程有助于减少数据文件的碎片化，提高磁盘的读取效率。
3.重组索引文件：与数据文件类似，索引文件也可能变得碎片化。OPTIMIZE TABLE 还会对索引文件进行重组，确保索引树更加平衡，从而加快索引查找的速度。
4.更新统计信息：在某些存储引擎中，OPTIMIZE TABLE 也会更新存储引擎内部使用的统计信息，这有助于查询优化器更好地选择执行计划。

排查

mysql> SELECT COUNT(*) AS actual_rows FROM 表名 PARTITION (p_2022_11_04);
mysql> SELECT * FROM 表名 PARTITION (p_2022_11_04) LIMIT 10;

通过检查实际行数和数据，可以看出该分区确实没有数据。

注意：

ANALYZE TABLE可能会导致短暂的表锁定，尤其是在高并发环境下。且ANALYZE TABLE和OPTIMIZE TABLE分析和优化大表可能消耗较多的CPU和I/O资源。
建议如下：
1.选择低峰时段：尽量在业务低峰时段执行这些操作，以减少对生产系统的影响。
2.监控系统资源：在执行这些操作时，监控系统的CPU、内存和I/O使用情况，确保系统资源充足。
3.测试环境：如果可能，先在测试环境中执行这些操作，评估其影响。
4.分批次处理：对于非常大的表，可以考虑分批次进行优化和分析。

处理：

分区容量

点击查看代码

SELECT 
    TABLE_NAME, 
    PARTITION_NAME, 
    TABLE_ROWS, 
    DATA_LENGTH, 
    INDEX_LENGTH, 
    (DATA_LENGTH + INDEX_LENGTH) AS TOTAL_SIZE,
    DATA_LENGTH / 1073741824.0 AS DATA_SIZE_GB,
    INDEX_LENGTH / 1073741824.0 AS INDEX_SIZE_GB,
    (DATA_LENGTH + INDEX_LENGTH) / 1073741824.0 AS TOTAL_SIZE_GB
FROM 
    INFORMATION_SCHEMA.PARTITIONS 
WHERE 
    TABLE_SCHEMA = '库名' 
    AND TABLE_NAME = '表名';

低峰期优化

检查优化后分区表各分区大小

检查优化后表的总大小

删除没有数据的分区

ALTER TABLE 表名 DROP PARTITION p_2023_01_26;

自此优化结束，一下子释放了快1.9个T的容量，大快人心！！！

备份思路

如果有数据需要归档备份，备份思路如下：
创建一个临时表，再往里面插入特定分区的数据，再通过mysqldump导出归档

表结构（脱敏）

show create table abc
| abc | CREATE TABLE `abc` (
  `APP_ID` varchar(32) DEFAULT NULL COMMENT 'app编号',
  `ABC_CODE` varchar(512) DEFAULT NULL COMMENT '二维码号',
  `CARD_ID` varchar(64) DEFAULT NULL COMMENT '卡ID',
  `CODE_TYPE` varchar(32) DEFAULT NULL COMMENT '二维码类型',
  `CRT_DATE` varchar(32) NOT NULL DEFAULT '0' COMMENT '日期',
  `CRT_TIME` varchar(32) DEFAULT NULL COMMENT '时间',
.......
  PRIMARY KEY (`CRT_DATE`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(CRT_DATE)
(PARTITION p_2022_11_04 VALUES LESS THAN ('20221105') ENGINE = InnoDB,
 PARTITION p_2022_11_05 VALUES LESS THAN ('20221106') ENGINE = InnoDB,
 PARTITION p_2022_11_06 VALUES LESS THAN ('20221107') ENGINE = InnoDB,
 PARTITION p_2022_11_07 VALUES LESS THAN ('20221108') ENGINE = InnoDB,
 PARTITION p_2022_11_08 VALUES LESS THAN ('20221109') ENGINE = InnoDB,
 PARTITION p_2022_11_09 VALUES LESS THAN ('20221110') ENGINE = InnoDB,
.......

备份方案1：

创建临时表

CREATE TABLE abc_2022_11_04 LIKE abc;

插入特定分区数据

INSERT INTO abc_2022_11_04 SELECT * FROM abc PARTITION (p_2022_11_04);

通过mysqldump导出来

mysqldump -u your_username -p 库名 abc_2022_11_04 > abc_p_2022_11_04.sql

删除临时表

DROP TABLE abc_2022_11_04

备份方案2：

mysqldump -u root -p 库名 表名 --where="CRT_DATE < '20221105'" > abc_p_2022_11_04.sql

注意：如果表中有大量数据，建议在低峰时段进行备份，以减少对生产环境的影响。