mysql分区表总结

什么是分区表

这种表结构在server层看来是一张表，而在引擎层看来是多张不同的表，目的是为了分散一张表的存储数据的压力(比如如果不分区的话，一张表业务数据可能会达到几千万甚至上亿，就会导致表的查询，修改特别慢);

#PS
它其实是水平分表的一种(分库分表也是)；

分区表优缺点

优点：

• 分散表存储数据的压力，加快数据存储和读取速度；
• 分区表对业务透明，只需要维护一张表的访问(如果是分表得对接多张表)；
• 删除或者清空某个特定的分区很方便(alter table t drop/truncate partition p202209)；

缺点：

• 查询时要指定分区或者where条件中存在分区字段，否则会访问所有分区；
• 分区需要提前建好，不满足分区条件的会报错；

mysql分区类型及使用方法

• range分区
  range分区是基于给定的连续区间，在插入数据时，根据分区键所属范围确定要插入的分区；
  #不使用函数转换为区间值

create table t (
    id varchar(40),
    name varchar(100),
    birthday datetime,
    part_year int(4),
    remark varchar(200),
    primary key(id,part_year)
) engine=innodb default charset=utf8 comment '用户信息表'
partition by range(part_year)(
    partition p0 values less than (1990),
    partition p1 values less than (2000),
    partition p2 values less than (2010),
    partition p3 values less than (2020),
    partition p4 values less than (2030)
);

insert into t values (‘1001’, ‘zhs’, ‘1989-09-10’, 1989, ‘zhs’),(‘1002’, ‘lisi’, ‘1999-09-10’, 1999, null),(‘1003’, ‘wangw’, ‘2019-09-10’, 2019, null);

#使用一些特定日期函数(比如year(),month(),unix_timestamp()，其他的比如convert(),cast(),crc32()等都不允许创建分区表)

create table t (
    id varchar(40),
    name varchar(100),
    birthday datetime,
    remark varchar(200),
    primary key(id,birthday)
) engine=innodb default charset=utf8 comment '用户信息表'
partition by range(year(birthday))(
    partition p0 values less than (1990),
    partition p1 values less than (2000),
    partition p2 values less than (2010),
    partition p3 values less than (2020),
    partition p4 values less than (2030)
);

insert into t values (‘1001’, ‘zhs’, ‘1989-09-10’, ‘zhs’),(‘1002’, ‘lisi’, ‘1999-09-10’, null),(‘1003’, ‘wangw’, ‘2019-09-10’, null);

• list分区
  range是不同的分区对应不同的取值范围，而list分区是不同的分区对应不同的确定值(这点感觉有些类似于switch…case)；
  #注意：这里list各个分区的values in 值 只允许int类型(可以为负数)；
  eg:

create table foo_list
(
	empno varchar(20) not null comment '员工编号',
	empname varchar(20) comment '员工名称',
	gender int(2) comment '性别'
)
partition by list(gender)
(
    partition p1 values in (1,111),
    partition p2 values in (2,222),
    partition p3 values in (0,-1)
);

#插入数据

insert into foo_list values (‘1001’, ‘zhs’, 1),(‘1002’,
‘lisi’, 222),(‘1003’, ‘lisi02’, -1);

• hash分区
  把分区键通过一个函数计算一个非负整数，当然也可以不经过转换，直接使用分区键本身(它本身得是非负整数)，再经过mysql的hash取模，自动计算应该放入哪个分区(hash这个东西很常用，目的就是把原值通过hash函数转换(一般是取模)，映射到另一个值的过程，nginx里面有ip_hash)；
  eg:

create table foo_hash
(empno varchar(20) not null ,
empname varchar(20),
deptno varchar(10),
birthdate date not null,
salary int
)
partition by hash(year(birthdate))
(
	partition p01,
	partition p02,
	partition p03,
	partition p04
);

#分区的定义也可以简写为
partition by hash(year(birthdate)) partitions 4;

• key分区
  和hash分区类似，只不过这里的hash函数是mysql自己内部定义的，并且分区字段上不允许使用函数

drop table if exists foo_key;
create table foo_key(
    empno varchar(20) not null ,
    empname varchar(20),
    deptno varchar(10),
    birthdate date not null,
    salary int
)
partition by key(salary)
(
	partition p01,
	partition p02,
	partition p03,
	partition p04
);

有一点要注意的是：partition by key() 里面的分区字段既可以是varchar(比如可以是empname),还可以是date(birthdate)，没有做限制；

操作表的分区sql写法

• 查询指定分区

select * from t partition(p202208,p202209,p202210);
或者查询条件中携带分区字段
select * from t where part_month = 202209; #此时它会根据part_month来寻找相应的分区

• 删除特定分区

alter table t drop partition p202209;

• 清空特定分区

alter table t truncate partition p202209;

• 新增一个分区(注意：新增分区只能加在原有分区的最后面，加在中间或者其他地方都不被允许);

alter table t add partition(partition p202210 values less than (202210))

• 重新定义分区(不会删除表中数据，可能会使得数据重新插入分区)(注意：分区p0原有的1990这个限制范围不能随意，否则mysql不让修改)

alter table t reorganize partition p0(
partition p0x values less than (1980),
partition p0 values less than (1990)
)

• 查询表的分区信息

select * from information_schema.partitions where table_schema = ‘test’ and table_name = ‘t’;
当然 show create table t 也可以;

• 删除表分区变成普通表(不会删除表中数据)

alter table t remove partitioning;

注意事项

• 第一次访问该分区表时会加载所有的分区（初始化）；
• 查询时最好带着分区字段或者指定分区;
• 创建分区时分区字段要在唯一键或者主键当中(主要是为了方便查询是否唯一键冲突，避免遍历所有分区，现在分区键加入了主键当中，在这个分区里就可以判断，而不需要遍历所有分区)，并且分区字段不能使用函数；
• oracle和mysql创建分区的方式差不多；

主要参考文章

https://developer.aliyun.com/article/708095