背景
生产环境中,某个分区表两三年了,占用磁盘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
注意:如果表中有大量数据,建议在低峰时段进行备份,以减少对生产环境的影响。
标签:11,PARTITION,abc,04,mysql,分区表,2022,归档,TABLE From: https://www.cnblogs.com/haiyoyo/p/18534877