首页 > 数据库 >mysql分区表占用大量容量处理(优化)及归档分区表

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

时间:2024-11-08 13:19:26浏览次数:1  
标签:11 PARTITION abc 04 mysql 分区表 2022 归档 TABLE

背景

生产环境中,某个分区表两三年了,占用磁盘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

相关文章

  • MySQL面试题
    文章目录目录文章目录1.什么是内连接、外连接、交叉连接、笛卡尔积呢?2.那MySQL的内连接、左连接、右连接有有什么区别?3.说一下数据库的三大范式?4.varchar与char的区别?5.blob和text有什么区别?6.DATETIME和TIMESTAMP的异同?7.MySQL中in和exists的区别?8.MyS......
  • MySQL储存过程控制语句
    一.条件语句1.if-then-else语句DECLARE局部变量    DEFAULT默认值语法:IF条件THEN语句列表;(如果第一条为true,就执行成功)ELSEIF条件 THEN语句列表;(如果第一条为false,就执行第二条)ELSE语句列表; (如果上面都是false,这句话就是否则,最终执行)ENDIF;(这是......
  • Kubernetes 中实现 MySQL 的读写分离
    Kubernetes中实现MySQL的读写分离在Kubernetes中实现MySQL的读写分离,可以通过主从复制架构来实现。在这种架构中,MySQL主节点(Master)负责处理所有写操作,而MySQL从节点(Slave)负责处理所有读操作。下面是一个详细的步骤指南:步骤1:创建Kubernetes集群确保你有一个运行良......
  • Ubuntu 24.04 二进制安装 MySQL 8.0.20
    相关资料操作系统:Ubuntu24.04数据库下载地址:https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz部署#安装必要的依赖apt-yinstallnumactllibaio1t64-yln-sv/usr/lib/x86_64-linux-gnu/libtinfo.so.6.4/usr/lib/x86_64-li......
  • MySQL 正则表达式
    简介MySQL正则表达式概述MySQL正则表达式是一种强大的文本匹配工具,允许执行复杂的字符串搜索和处理。它使用REGEXP、RLIKE和REGEXP_LIKE()函数进行模式匹配,提供了灵活的方式来处理各种文本数据。正则表达式特别适用于模糊查询、模式匹配和文本分析场景,在数据库管理和应......
  • Mysql使用between and查询时间区间不包括右边界问题
    结论:Mysql数据库中的betweenand查询是包含右边界的,但如果字段是datetime类,数据格式则会被转换为:2018-10-0100:00:00,那么2018-10-01当天的数据就查询不到,所以就会出现不包含右边界的这种问题,而数据类型本身是date则不会出现上述问题。举例:在Mysql中有如下select语句:SELECT*FR......
  • linq to mysql 查询_LinQ to SQL 查询(转)
    LINQtoSQL是将对象关系映射到.NET框架中的一种实现。它可以将关系数据库映射为.NETFramework中的一些类。然后,开发人员就可以通过使用LINQtoSQL对数据库中的数据进行查询、修改、插入、删除等操作。LINQtoSQL类映射到SQLServer数据库中的表,这些类被称做“实体类”,实体......
  • MySQL索引详解
    MySQL索引详解索引介绍索引是一种用于快速查询和检索数据的数据结构,其本质可以看成是一种排序好的数据结构。索引的作用就相当于书的目录。打个比方:我们在查字典的时候,如果没有目录,那我们就只能一页一页的去找我们需要查的那个字,速度很慢。如果有目录了,我们只需要先去目录里......
  • MySql基本常用语句
    查询语句select*(要查找的内容)fromtable(表名)逻辑运算符--and与--or或--not取反--in包含select*(要查找的内容)fromtable(表名)where*and*select*(要查找的内容)fromtable(表名)where*or*select*(要查找的内容)fromtable(表名)where*in(......
  • php毕业设计流浪动物领养系统宠物领养网站PHP+MySQL+HTML计算机毕业设计PHP源码获取ph
    一、功能介绍基于php+mysql+html前台:网站首页宠物推荐最新宠物新闻咨询宠物分类宠物资讯评论登录/注册加入购物车、领养、个人中心后台:各种增删改查系统设置分类管理宠物管理领养管理评价管理新闻资讯用户管理二、效果展示三、代码展示CREATE......