表分区：mysql partition

mysql支持对表进行水平分区（partition），对表进行水平拆分（sharding），用户可以根据指定规则（分区函数）进行数据分片管理（每个分区相当于一个独立的表，物理上独立的数据文件）。
要创建分区表，就需要使用支持分区的存储引擎，同一个分区表的所有分区必须使用相同的存储引擎。在mysql8中，唯一支持分区的存储引擎是InnoDB和NDB。本文使用MySQL8&&InnoDB，先来看一个简单示例：

create table t_parti(id bigint,amount decimal(10,2),tdate date)
partition by hash ( month(tdate) )
partitions 12;
1
2
3

创建一个具有12个分区的表（未指定分区数时默认为1），分区规则使用表达式month取月份，然后进行hash计算（根据表达式和分区数量进行模运算）。可以看下对应的数据文件，如图：

可以看到每个分区都是一个独立的ibd文件。下面插入几条示例数据，看下数据分区情况：

insert into t_parti(id,amount,tdate) values(1,12.11,'2021-01-05');
insert into t_parti(id,amount,tdate) values(2,22.11,'2021-02-04');
...
insert into t_parti(id,amount,tdate) values(12,15.11,'2021-12-12');
1
2
3
4

如果知道数据在哪个分区，我们就可以直接查询指定分区数据，比如要查询2021-12月的数据（通常可以只保留一年的数据，历史数据进行backup）。根据分区规则，hash（month（tdate）））== mod（12，12），数据在第0个分区上，所以可以直接查询p0分区，然后再进行条件过滤。

注意如果表中有主键或唯一key，分区表达式中使用的列必须是唯一key的一部分（确保每个分区唯一约束）。如上表如果id为主键，则分区无法创建成功。通常会使用range范围分区，按照某一列或某几列的取值范围进行分区，如：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

其他分区类型包括：LIST分区和KEY分区，两种分区方式本质上是RANGE分区和HASH的扩展。
分区修剪优化，mysql可以针对分区列进行扫描优化（不去扫描没有匹配值的分区）。只要WHERE条件可以简化为以下两种情况之一，优化器就可以执行修剪 ：

partition_column = constant
partition_column IN (constant1, constant2, ..., constantN)
1
2

所以使用表分区后，注意where条件中应该尽量包含分区列，提高检索速度。这里需要注意表分区规则及分区字段类型，否则很可能出现无法用到分区修剪优化。最后看一下查询表分区信息：

select t.PARTITION_NAME, t.TABLE_ROWS, PARTITION_EXPRESSION
  from information_schema.PARTITIONS t
 where t.TABLE_NAME = 'xxxx'
1
2
3