目录

一、简介

二、表分区的好处

三、表分区的限制

四、分区类型

五、分区管理

六、分区的缺点

---

一、简介

MySQL分区能够根据一些规则将表中的数据分配给不同的物理分区。MySQL支持水平分区（将表中数据分配到不同的物理分区），不支持垂直分区（将表中的列分配给不同的物理分区），将数据分区后，查找数据时不需要查找整个磁盘，只要知道数据在哪一块，然后查找那一块就好了，当一个磁盘中数据过大时，就可以将数据进行分区

二、表分区的好处

1. 与单个磁盘或文件系统分区相比，可以存储更多的数据。
2. 为了更方便的管理数据，一些失去保存意义的数据，可以直接删除那个数据的分区
3. 提高查询效率，通过给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区
4. 涉及到例如SUM()和COUNT()这样聚合函数的查询，可以在各个分区并行处理，最终只需通过总计所有分区得到的结果

三、表分区的限制

1. 一个表最多只能有1024个分区，在5.7版本的时候可以支持8196个分区
2. MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。
3. 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
4. 不支持外键约束

四、分区类型

1. 范围分区（RANGE Partitioning）

利用列值在给定的范围进行分区，区间要连续并且不能互相重叠，每个分区是有顺序的，要从最低到最高

语法：

CREATE TABLE t1 (
    id 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)
);

上面的语法是创建了一张t1表，以store_id进去范围分区，值小于6的在p0分区，值小于11的在p1分区。创建了分区后数据库的存储位置也会多出分区的数据存储文件

如果在上述创建的分区插入store_id为12的数据会报错，因为没有定义11以上的值存放的位置，这种问题可以通过 LESS THAN MAXVALUE 来解决，MAXVALUE 表示最大的可能的整数值

CREATE TABLE t2 (
    id 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 MAXVALUE
);

可以使用函数进行分区，如：

CREATE TABLE t3 (
    id INT NOT NULL,
    separated DATE NOT NULL DEFAULT '9999-12-31'
)
PARTITION BY RANGE (YEAR(separated)) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

2.列表分区（LIST Partitioning）

类似于范围分区必须明确定义每个分区，区别在于列表分区是根据列值匹配一个定义好的集合中选择分区，并且没有顺序限制，如果列值无法在集合中找到分区，将会报错

语法：

CREATE TABLE t4 (
    id INT NOT NULL,
    store_id INT NOT NULL
) PARTITION BY LIST(store_id) (
    PARTITION p0 VALUES IN (3,5,6,9,17),
    PARTITION p1 VALUES IN (1,2,10,11,20,19)
);

3.列分区（COLUMNS Partitioning）

列分区可以认为是范围分区和列表分区的升级版，为解决只支持整数分区的问题，支持整数，日期时间，字符串三大数据类型，但不支持表达式

语法：

CREATE TABLE t5 (
    `id` int(11) DEFAULT NULL,
    `name` varchar(20) DEFAULT NULL
) PARTITION BY RANGE COLUMNS(id,name) (
    PARTITION p0 VALUES LESS THAN (1,'a'),
    PARTITION p1 VALUES LESS THAN (5,'e')
);

4.哈希分区（HASH Partitioning）

哈希分区能够确保数据在预先确定个数的分区中尽可能平均分布，基于定义的表达式返回的值来选择分区，这个表达式不能产生负整数值

语法：

CREATE TABLE t6 (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

哈希分区不适合用在分区经常变动的需求。因为新增或者减少分区时，大部分数据需要重新定义分区，为此MySQL提供了线性哈希分区（LINEAR HASH Partitioning）

语法：

CREATE TABLE t7 (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY LINEAR HASH(store_id)
PARTITIONS 4;

线性散列与常规散列的不同之处在于线性散列使用线性二次幂算法，而常规散列使用散列函数值的模数。优势：分区的添加、删除、合并和拆分速度更快，这在处理包含大量（TB）数据的表时非常有用；缺点：数据分布不太均衡

5.key分区（KEY Partitioning）

类似于哈希分区，不同之处在于哈希分区可以自定义表达式，key分区的hash函数由 MySQL 服务器提供，用于分区的列不限于整数或NULL值

语法：

CREATE TABLE t8 (
    id INT NOT NULL,
    name VARCHAR(32)
)
PARTITION BY KEY(name)
PARTITIONS 4;

没有将列名指定为分区键，如果有主键的情况下，则使用表的主键用于分区键，如果没有主键但有设置为NOT NULL的唯一键，则唯一键用于分区键

6.子分区（Subpartitioning）

在分区的基础上再分区

语法：

CREATE TABLE ts (
    id INT,
    purchased DATE
) PARTITION BY RANGE(YEAR(purchased))
    SUBPARTITION BY HASH(TO_DAYS(purchased))
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

五、分区管理

1.范围分区和列表分区

查询某分区下所有数据

 select * from t1 partition(p1);

删除一个分区，删除一个分区后，分区下的所有数据也会被删除

ALTER TABLE t1 DROP PARTITION p1;

添加一个分区，添加的分区值必须严格按照递增关系，否则会报错

ALTER TABLE t1 ADD PARTITION (
    PARTITION p2 VALUES LESS THAN (14),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

拆分分区

ALTER TABLE t1 REORGANIZE PARTITION p3 INTO (
    PARTITION s4 VALUES LESS THAN (40),
    PARTITION s5 VALUES LESS THAN MAXVALUE
);

合并分区

ALTER TABLE t1 REORGANIZE PARTITION s9,s10 INTO (
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

2.哈希分区和key分区

删除2个分区

ALTER TABLE t6 COALESCE PARTITION 2;

增加6个分区

ALTER TABLE t6 ADD PARTITION PARTITIONS 6;

六、分区的缺点

1. 分区列和索引列不匹配时，由于每个分区都会有自己的索引，针对这个索引的查询会遍历所有分区的索引树
2. 选择分区的成本可能会很高，线性搜索会随着分区数的增长代价也在上升
3. 打开和锁定分区代价可能很高
4. 维护分区的成本可能很高