版本:
mysql-5.7.32
前言:
对于业务繁忙的数据库来说，在运行了一定时间后，往往会产生一些数据量较大的表，特别是对于每天新增数据较多的日志表或者流水表，大表对于日常的运维非常的不方便，特别是数据的清理、迁移，表的访问性能也会随着数据量的增大而受到影响，因此，对于大表我们需要进行优化拆分，通常拆分的方案有
所以，通常选择分区表改造方案的主要原因都是为了避免应用层面的改造，对应用层面透明，以及方便日常的运维，前提是表具备改造分区条件。
改造分区前期条件:
参考文档
mysql官方文档
1 根据业务的场景以及数据的分布，确认是否有匹配的分区表类型以及分区列
- 对于日志表，流水表这种按日期类型进行操作的，可以选择进行range分区
- 对于按用户id类型的进行操作的，可以选择进行hash，key分区
- 对于按渠道，类型的进行操作的，可以选择进行list分区
2 应用涉及的sql，需要90%以上的操作都包含分区列，按分区操作，如果sql没有包含分区条件，扫描全分区，性能会出现下降。
查询表sql操作历史
select db,query,exec_count from sys.x$statement_analysis where lower(query) like '%%'order by exec_count;
3 主键必须包含分区键
4 分区键表达式只支持部分函数，存储过程，不支持二级制操作符以及/
5 分区不支持外键
查询表外键
select * from information_schema.key_column_usage where constraint_schema ='' and referenced_table_schema is not null\g
6 不支持查询缓存
7 5.7版本单个表分区最大支持8192个，并且会话第一次访问分区表，都需要打开全部的分区表，所以避免建立过多的分区
8 数据库最大文件打开数open_files_limit要设置足够大以满足表，分区的打开数量
9 数据库大文件large_files_support设置为on
10 分区列支持null值(对于rang分区，null值总小于任何的一个非null值，即存放在最左边的分区;对于list分区需要显示指定null值条件)，但从数据管理以及规范来看，不建议分区列存放null值，并且如果表有主键，则分区列不能为null值，因为分区列需要作为主键的一部分，不能为null
12 mysql目前没有自动分区功能，所以需要监控分区的使用情况，通过job自动或者定时手动添加新分区
13 确定数据保留期限，定期归档分区数据
分区改造案例:
以下是一张数据量为766万的大表xxxx_user. xxxx_tab,计划将其改造为范围分区，按月存放。
select table_schema,table_name,table_rows,data_length/1024/1024/1024 from information_schema.tables where table_name='xxxx_tab';
表结构
create table `xxxx_tab` ( `role_seq` bigint(20) not null , `prd_id` varchar(64) not null , `make_right` varchar(1) default '0' , `check_right` varchar(1) default '0' , `auth_right` varchar(1) default '0' , `auth_group` varchar(4) default null , `release_right` varchar(1) default '0' , `create_user_seq` bigint(20) default null , `create_dept_seq` bigint(20) default null , `create_time` datetime default null , `update_user_seq` bigint(20) default null , `update_dept_seq` bigint(20) default null , `update_time` datetime default null , primary key (`prd_id`,`role_seq`), key `xxxx_tab_idx01` (`role_seq`) ) engine=innodb default charset=utf8mb4
分区列create_time日期最大值，最小值，根据这个范围按月创建分区
select max(create_time),min(create_time) from xxxx_tab;
分区列null值，对于存在的null值，需要应用对null数据进行处理，并且程序上需要确保数据写入not null
select count(*) from xxxx_tab where create_time is null;
主键重建添加分区列
alter table xxxx_tab drop primary key,add primary key (`prd_id`,`role_seq`,`create_time`);
由于主键没有包含分区列，需要重建主键添加分区列，对于主键重建我采取的是官方的online ddl功能，这种ddl操作会造成主从延时，但是不会产生大量的binlog，对于主从实时性要求高的，可以采用第三方的在线工具pt-osc,gh-ost
表转化为分区表
采用pt-osc在线将表转化为分区表，对于partition by 官方是不支持online ddl的，所以需要采用第三方的在线工具
./pt-online-schema-change --user=xxx --password=xxx --charset=utf8 d=xxxx_user,t=xxxx_tab --alter "partition by range columns(create_time)
(partition p200001 values less than ('2000-02-01 00:00:00') engine = innodb,
partition p200101 values less than ('2001-02-01 00:00:00') engine = innodb,
partition p201707 values less than ('2017-08-01 00:00:00') engine = innodb,
partition p201708 values less than ('2017-09-01 00:00:00') engine = innodb,
partition p201709 values less than ('2017-10-01 00:00:00') engine = innodb,
partition p201710 values less than ('2017-11-01 00:00:00') engine = innodb,
partition p201711 values less than ('2017-12-01 00:00:00') engine = innodb,
partition p201712 values less than ('2018-01-01 00:00:00') engine = innodb,
partition p201801 values less than ('2018-02-01 00:00:00') engine = innodb,
partition p201802 values less than ('2018-03-01 00:00:00') engine = innodb,
partition p201803 values less than ('2018-04-01 00:00:00') engine = innodb,
partition p201804 values less than ('2018-05-01 00:00:00') engine = innodb,
partition p201805 values less than ('2018-06-01 00:00:00') engine = innodb,
partition p201806 values less than ('2018-07-01 00:00:00') engine = innodb,
partition p201807 values less than ('2018-08-01 00:00:00') engine = innodb,
partition p201808 values less than ('2018-09-01 00:00:00') engine = innodb,
partition p201809 values less than ('2018-10-01 00:00:00') engine = innodb,
partition p201810 values less than ('2018-11-01 00:00:00') engine = innodb,
partition p201811 values less than ('2018-12-01 00:00:00') engine = innodb,
partition p201812 values less than ('2019-01-01 00:00:00') engine = innodb,
partition p201901 values less than ('2019-02-01 00:00:00') engine = innodb,
partition p201902 values less than ('2019-03-01 00:00:00') engine = innodb,
partition p201903 values less than ('2019-04-01 00:00:00') engine = innodb,
partition p201904 values less than ('2019-05-01 00:00:00') engine = innodb,
partition p201905 values less than ('2019-06-01 00:00:00') engine = innodb,
partition p201906 values less than ('2019-07-01 00:00:00') engine = innodb,
partition p201907 values less than ('2019-08-01 00:00:00') engine = innodb,
partition p201908 values less than ('2019-09-01 00:00:00') engine = innodb,
partition p201909 values less than ('2019-10-01 00:00:00') engine = innodb,
partition p201910 values less than ('2019-11-01 00:00:00') engine = innodb,
partition p201911 values less than ('2019-12-01 00:00:00') engine = innodb,
partition p201912 values less than ('2020-01-01 00:00:00') engine = innodb,
partition p202001 values less than ('2020-02-01 00:00:00') engine = innodb,
partition p202002 values less than ('2020-03-01 00:00:00') engine = innodb,
partition p202003 values less than ('2020-04-01 00:00:00') engine = innodb,
partition p202004 values less than ('2020-05-01 00:00:00') engine = innodb,
partition p202005 values less than ('2020-06-01 00:00:00') engine = innodb,
partition p202006 values less than ('2020-07-01 00:00:00') engine = innodb,
partition p202007 values less than ('2020-08-01 00:00:00') engine = innodb,
partition p202008 values less than ('2020-09-01 00:00:00') engine = innodb,
partition p202009 values less than ('2020-10-01 00:00:00') engine = innodb,
partition p202010 values less than ('2020-11-01 00:00:00') engine = innodb,
partition p202011 values less than ('2020-12-01 00:00:00') engine = innodb,
partition p202012 values less than ('2021-01-01 00:00:00') engine = innodb,
partition p202101 values less than ('2021-02-01 00:00:00') engine = innodb,
partition p202102 values less than ('2021-03-01 00:00:00') engine = innodb,
partition p202103 values less than ('2021-04-01 00:00:00') engine = innodb,
partition p202104 values less than ('2021-05-01 00:00:00') engine = innodb,
partition p202105 values less than ('2021-06-01 00:00:00') engine = innodb,
partition p202106 values less than ('2021-07-01 00:00:00') engine = innodb,
partition p202107 values less than ('2021-08-01 00:00:00') engine = innodb,
partition p202108 values less than ('2021-09-01 00:00:00') engine = innodb,
partition p202109 values less than ('2021-10-01 00:00:00') engine = innodb,
partition p202110 values less than ('2021-11-01 00:00:00') engine = innodb,
partition p202111 values less than ('2021-12-01 00:00:00') engine = innodb,
partition p202112 values less than ('2022-01-01 00:00:00') engine = innodb,
partition p202201 values less than ('2022-02-01 00:00:00') engine = innodb,
partition p202202 values less than ('2022-03-01 00:00:00') engine = innodb,
partition p202203 values less than ('2022-04-01 00:00:00') engine = innodb,
partition p202204 values less than ('2022-05-01 00:00:00') engine = innodb,
partition p202205 values less than ('2022-06-01 00:00:00') engine = innodb,
partition p202206 values less than ('2022-07-01 00:00:00') engine = innodb,
partition p202207 values less than ('2022-08-01 00:00:00') engine = innodb,
partition p202208 values less than ('2022-09-01 00:00:00') engine = innodb,
partition p202209 values less than ('2022-10-01 00:00:00') engine = innodb,
partition p202210 values less than ('2022-11-01 00:00:00') engine = innodb,
partition p202211 values less than ('2022-12-01 00:00:00') engine = innodb,
partition p202212 values less than ('2023-01-01 00:00:00') engine = innodb,
partition p202301 values less than ('2023-02-01 00:00:00') engine = innodb,
partition p202302 values less than ('2023-03-01 00:00:00') engine = innodb,
partition p202303 values less than ('2023-04-01 00:00:00') engine = innodb,
partition p202304 values less than ('2023-05-01 00:00:00') engine = innodb,
partition p202305 values less than ('2023-06-01 00:00:00') engine = innodb,
partition p202306 values less than ('2023-07-01 00:00:00') engine = innodb,
partition p202307 values less than ('2023-08-01 00:00:00') engine = innodb,
partition p202308 values less than ('2023-09-01 00:00:00') engine = innodb,
partition p202309 values less than ('2023-10-01 00:00:00') engine = innodb,
partition p202310 values less than ('2023-11-01 00:00:00') engine = innodb,
partition p202311 values less than ('2023-12-01 00:00:00') engine = innodb,
partition p202312 values less than ('2024-01-01 00:00:00') engine = innodb,
partition pmax values less than (maxvalue) engine = innodb)" --recursion-method hosts --max-lag 600 --nodrop-old-table --print --statistics --execute
分区后表模型
create table `xxxx_tab` (
`role_seq` bigint(20) not null ,
`prd_id` varchar(64) not null ,
`make_right` varchar(1) default '0' ,
`check_right` varchar(1) default '0' ,
`auth_right` varchar(1) default '0' ,
`auth_group` varchar(4) default null ,
`release_right` varchar(1) default '0' ,
`create_user_seq` bigint(20) default null ,
`create_dept_seq` bigint(20) default null ,
`create_time` datetime not null ,
`update_user_seq` bigint(20) default null ,
`update_dept_seq` bigint(20) default null ,
`update_time` datetime default null ,
primary key (`prd_id`,`role_seq`,`create_time`),
key `xxxx_tab_idx01` (`role_seq`)
) engine=innodb default charset=utf8mb4
partition by range columns(create_time)
(partition p200001 values less than ('2000-02-01 00:00:00') engine = innodb,
partition p200101 values less than ('2001-02-01 00:00:00') engine = innodb,
partition p201707 values less than ('2017-08-01 00:00:00') engine = innodb,
partition p201708 values less than ('2017-09-01 00:00:00') engine = innodb,
partition p201709 values less than ('2017-10-01 00:00:00') engine = innodb,
partition p201710 values less than ('2017-11-01 00:00:00') engine = innodb,
partition p201711 values less than ('2017-12-01 00:00:00') engine = innodb,
partition p201712 values less than ('2018-01-01 00:00:00') engine = innodb,
partition p201801 values less than ('2018-02-01 00:00:00') engine = innodb,
partition p201802 values less than ('2018-03-01 00:00:00') engine = innodb,
partition p201803 values less than ('2018-04-01 00:00:00') engine = innodb,
partition p201804 values less than ('2018-05-01 00:00:00') engine = innodb,
partition p201805 values less than ('2018-06-01 00:00:00') engine = innodb,
partition p201806 values less than ('2018-07-01 00:00:00') engine = innodb,
partition p201807 values less than ('2018-08-01 00:00:00') engine = innodb,
partition p201808 values less than ('2018-09-01 00:00:00') engine = innodb,
partition p201809 values less than ('2018-10-01 00:00:00') engine = innodb,
partition p201810 values less than ('2018-11-01 00:00:00') engine = innodb,
partition p201811 values less than ('2018-12-01 00:00:00') engine = innodb,
partition p201812 values less than ('2019-01-01 00:00:00') engine = innodb,
partition p201901 values less than ('2019-02-01 00:00:00') engine = innodb,
partition p201902 values less than ('2019-03-01 00:00:00') engine = innodb,
partition p201903 values less than ('2019-04-01 00:00:00') engine = innodb,
partition p201904 values less than ('2019-05-01 00:00:00') engine = innodb,
partition p201905 values less than ('2019-06-01 00:00:00') engine = innodb,
partition p201906 values less than ('2019-07-01 00:00:00') engine = innodb,
partition p201907 values less than ('2019-08-01 00:00:00') engine = innodb,
partition p201908 values less than ('2019-09-01 00:00:00') engine = innodb,
partition p201909 values less than ('2019-10-01 00:00:00') engine = innodb,
partition p201910 values less than ('2019-11-01 00:00:00') engine = innodb,
partition p201911 values less than ('2019-12-01 00:00:00') engine = innodb,
partition p201912 values less than ('2020-01-01 00:00:00') engine = innodb,
partition p202001 values less than ('2020-02-01 00:00:00') engine = innodb,
partition p202002 values less than ('2020-03-01 00:00:00') engine = innodb,
partition p202003 values less than ('2020-04-01 00:00:00') engine = innodb,
partition p202004 values less than ('2020-05-01 00:00:00') engine = innodb,
partition p202005 values less than ('2020-06-01 00:00:00') engine = innodb,
partition p202006 values less than ('2020-07-01 00:00:00') engine = innodb,
partition p202007 values less than ('2020-08-01 00:00:00') engine = innodb,
partition p202008 values less than ('2020-09-01 00:00:00') engine = innodb,
partition p202009 values less than ('2020-10-01 00:00:00') engine = innodb,
partition p202010 values less than ('2020-11-01 00:00:00') engine = innodb,
partition p202011 values less than ('2020-12-01 00:00:00') engine = innodb,
partition p202012 values less than ('2021-01-01 00:00:00') engine = innodb,
partition p202101 values less than ('2021-02-01 00:00:00') engine = innodb,
partition p202102 values less than ('2021-03-01 00:00:00') engine = innodb,
partition p202103 values less than ('2021-04-01 00:00:00') engine = innodb,
partition p202104 values less than ('2021-05-01 00:00:00') engine = innodb,
partition p202105 values less than ('2021-06-01 00:00:00') engine = innodb,
partition p202106 values less than ('2021-07-01 00:00:00') engine = innodb,
partition p202107 values less than ('2021-08-01 00:00:00') engine = innodb,
partition p202108 values less than ('2021-09-01 00:00:00') engine = innodb,
partition p202109 values less than ('2021-10-01 00:00:00') engine = innodb,
partition p202110 values less than ('2021-11-01 00:00:00') engine = innodb,
partition p202111 values less than ('2021-12-01 00:00:00') engine = innodb,
partition p202112 values less than ('2022-01-01 00:00:00') engine = innodb,
partition p202201 values less than ('2022-02-01 00:00:00') engine = innodb,
partition p202202 values less than ('2022-03-01 00:00:00') engine = innodb,
partition p202203 values less than ('2022-04-01 00:00:00') engine = innodb,
partition p202204 values less than ('2022-05-01 00:00:00') engine = innodb,
partition p202205 values less than ('2022-06-01 00:00:00') engine = innodb,
partition p202206 values less than ('2022-07-01 00:00:00') engine = innodb,
partition p202207 values less than ('2022-08-01 00:00:00') engine = innodb,
partition p202208 values less than ('2022-09-01 00:00:00') engine = innodb,
partition p202209 values less than ('2022-10-01 00:00:00') engine = innodb,
partition p202210 values less than ('2022-11-01 00:00:00') engine = innodb,
partition p202211 values less than ('2022-12-01 00:00:00') engine = innodb,
partition p202212 values less than ('2023-01-01 00:00:00') engine = innodb,
partition p202301 values less than ('2023-02-01 00:00:00') engine = innodb,
partition p202302 values less than ('2023-03-01 00:00:00') engine = innodb,
partition p202303 values less than ('2023-04-01 00:00:00') engine = innodb,
partition p202304 values less than ('2023-05-01 00:00:00') engine = innodb,
partition p202305 values less than ('2023-06-01 00:00:00') engine = innodb,
partition p202306 values less than ('2023-07-01 00:00:00') engine = innodb,
partition p202307 values less than ('2023-08-01 00:00:00') engine = innodb,
partition p202308 values less than ('2023-09-01 00:00:00') engine = innodb,
partition p202309 values less than ('2023-10-01 00:00:00') engine = innodb,
partition p202310 values less than ('2023-11-01 00:00:00') engine = innodb,
partition p202311 values less than ('2023-12-01 00:00:00') engine = innodb,
partition p202312 values less than ('2024-01-01 00:00:00') engine = innodb,
partition pmax values less than (maxvalue) engine = innodb)
总结
到此这篇关于mysql普通表如何转换成分区表的文章就介绍到这了,更多相关mysql普通表转分区表内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！
声明：如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。
