MySQL在线分区

业务查询慢，需要对表进行分区，当前MySQL未开启归档，经调研决定采用pt-osc在线分区，pt-osc在线DDL不需要归档

推荐下载网址，比官网方便很多
https://mirrors.tuna.tsinghua.edu.cn/percona/tools/yum/release/7/RPMS/x86_64/

创建测试表，插入数据

use test;
create table pttest(id int,namechar(30));
alter table `pttest` add primary key ( `id` );
insert into pttest(id,name) values (1,'name1');
insert into pttest(id,name) values (10,'name1');
insert into pttest(id,name) values (20,'name1');
insert into pttest(id,name) values (30,'name1');
insert into pttest(id,name) values (40,'name1');
insert into pttest(id,name) values (50,'name1');

dry-run不会真实执行

pt-online-schema-change  D=test,t=pttest -uroot -proot  --alter '
partition by range(`id`)
(
PARTITION p01 VALUES LESS THAN (10),
PARTITION p02 VALUES LESS THAN (20),
PARTITION p04 VALUES LESS THAN (30),
PARTITION p05 VALUES LESS THAN (40),
PARTITION p06 VALUES LESS THAN MAXVALUE 
);' --dry-run

执行分区操作

pt-online-schema-change  D=test,t=pttest -uroot -proot  --alter '
partition by range(`id`)
(
PARTITION p01 VALUES LESS THAN (10),
PARTITION p02 VALUES LESS THAN (20),
PARTITION p04 VALUES LESS THAN (30),
PARTITION p05 VALUES LESS THAN (40),
PARTITION p06 VALUES LESS THAN MAXVALUE 
);' --excute

查看分区情况

use test;
select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from information_schema.partitions where
table_schema = schema()
and table_name='pttest ';

MySQL5.6版本开始支持按分区查询，可分别查询分区行数

select count(*) from pttest partition (p01) ;

300GB的表用了24小时