分区表
? 环境说明:
公司生产库中, 有一张大表, 占用了很大一部分空间, 大小约 240G 左右, 现在的策略是, 每隔一段时间删除一些表中的数据, 但是由于 mysql innoDB 引擎的优化机制, 删除数据后还有优化表的操作非常麻烦, 所以改为分区表, 按照时间来存储数据, 以后在进行删除的时候直接删除分区即可
第 1 章 分区准备:
1.1 查看旧表的建表语句:
- show create table `t_interface_questionnaire`;
- CREATE TABLE `t_interface_questionnaire` (`id` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
- `questionnaire` mediumtext,
- `hosp_code` varchar(50) DEFAULT NULL,
- `hug_id` varchar(32) DEFAULT NULL,
- `send_time` bigint(19) DEFAULT NULL,
- `questionnaire_id` varchar(32) DEFAULT NULL,
- `begincontent` mediumtext COMMENT '内容头文字',
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1.2 说明:
? 目前已经在测试环境安装好了测试库. 5.6 版本
? 备份已经找到了, 是 xtrabackup 的物理备份
? 生产库是阿里云 RDS,mysql 5.6
第 2 章 分区计划实施步骤
2.1 实施步骤一: 搭建测试库
1. 在测试库中使用旧的建表语句, 删除表空间, 将物理备份的表空间导入测试库
2. 把 18 年后的数据用 mysqldump 导出, 然后删除数据库
3. 使用分区表建表语句, 创建新表
4. 将 18 年后的数据导入, 用 mysqldump 导出数据, 留存
5. 删除生产库中的大表
6. 使用分区表建表语句, 导入数据
2.2 实施步骤二: 在线修改表结构
1. 删除 t 表的 id 主键
- alter table t_interface_questionnaire drop primary key;
- mysql> desc t_interface_questionnaire;
- +------------------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +------------------+-------------+------+-----+---------+-------+
- | id | varchar(32) | NO | | | |
- | questionnaire | mediumtext | YES | | NULL | |
- | hosp_code | varchar(50) | YES | | NULL | |
- | hug_id | varchar(32) | YES | | NULL | |
- | send_time | bigint(19) | YES | | NULL | |
- | questionnaire_id | varchar(32) | YES | | NULL | |
- | begincontent | mediumtext | YES | | NULL | |
- +------------------+-------------+------+-----+---------+-------+
2. 添加主键, 添加 send_time 字段为主键, 因为分区需要
alter table t_interface_questionnaire add primary key(send_time);
3. 修改表结构, 添加以时间为基准的分区
- mysql> alter table t_interface_questionnaire PARTITION BY RANGE (send_time) (
- -> partition p0 values less than(1514736000000),
- -> partition p1 values less than(1517414400000),
- -> partition p2 values less than(1519833600000),
- -> partition p3 values less than(1522512000000),
- -> partition p4 values less than(1525104000000),
- -> partition p5 values less than(1527782400000),
- -> partition p6 values less than(1530374400000),
- -> partition p7 values less than(1533052800000),
- -> partition p8 values less than(1535731200000)
- -> );
2.3 实时步骤三: 备份原表
1. 将原表备份, 名为 t_interface_questionnaire_bak
rename t_interface_questionnaire to t_interface_questionnaire_yang;
2. 创建分区表, 名字为 t_interface_questionnaire, 分区表语句在下面
3. 将备份表的数据在线导入新表中
insert into t_interface_questionnaire select * from t_interface_questionnaire_bak;
时间长短无法预算, 需要向阿里申请下增加临时空间
? 注:
这里与到的问题就是我在测试库中在线导入一点问题没有, 但是在阿里云上进行在线导入的时候, 不成功, 空间会被占用, 但是数据查不到, 破片率也很大, 过一段时间, 空间还会恢复, 在线试了两次, 都没有成功, 最后是在备份库中导出 sql 语句, 然后导入到新表中
2.4 分区表语句:
- CREATE TABLE `t_interface_questionnaire` (
- `id` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
- `questionnaire` mediumtext,
- `hosp_code` varchar(50) DEFAULT NULL,
- `hug_id` varchar(32) DEFAULT NULL,
- `send_time` bigint(19) DEFAULT NULL,
- `questionnaire_id` varchar(32) DEFAULT NULL,
- `begincontent` mediumtext COMMENT '内容头文字',
- PRIMARY KEY (`id`,`send_time`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
- PARTITION BY RANGE (send_time) (
- partition p0 values less than(1514736000000),
- partition p1 values less than(1517414400000),
- partition p2 values less than(1519833600000),
- partition p3 values less than(1522512000000),
- partition p4 values less than(1525104000000),
- partition p5 values less than(1527782400000),
- partition p6 values less than(1530374400000),
- partition p7 values less than(1533052800000),
- partition p8 values less than(1535731200000)
- );
第 3 章 分区表基本操作
删除分区:
alter table t_interface_questionnaire drop PARTITION p0;
添加分区:
alter table t_interface_questionnaire add PARTITION (PARTITION p0 VALUES LESS THAN (1533052800000));
数据备份:
mysqldump -uroot -t --triggers -w 'send_time> 1514736000000' lanniu t_interface_questionnaire>/tmp/jiang.sql
利用 mysqldump 将 2018 年后的数据导出, 等生产库中表结构创建好以后, 导入数据
来源: http://www.bubuko.com/infodetail-2650622.html