Mysql分区表的使用

简单来说，分区表就是把物理表结构相同的几张表，通过一定算法，组成一张逻辑大表。这种算法叫“分区函数”，当前 MySQL 数据库支持的分区函数类型有 RANGE、LIST、HASH、KEY、COLUMNS。

无论选择哪种分区函数，都要指定相关列成为分区算法的输入条件，这些列就叫“分区列”。另外，在 MySQL 分区表中，主键也必须是分区列的一部分，不然创建分区表时会失败，比如：

CREATE TABLE t1
(
    a INT,
    b INT,
    c DATETIME(6),
    d VARCHAR(32),
    e INT,
    PRIMARY KEY (a, b)
)
    PARTITION BY RANGE COLUMNS (c) (
        PARTITION p0000 VALUES LESS THAN ('2019-01-01'),
        PARTITION p2019 VALUES LESS THAN ('2020-01-01'),
        PARTITION p2020 VALUES LESS THAN ('2021-01-01'),
        PARTITION p9999 VALUES LESS THAN (MAXVALUE)
        );

报错

A PRIMARY KEY must include all columns in the table's partitioning function

上面创建了表 t1，主键是复合索引，由列 a、b 组成。表 t 创建分区表的意图是根据列 c（时间列）拆分数据，把不同时间数据存放到不同分区中。

而我们可以从错误的提示中看到：分区表的主键一定要包含分区函数的列。所以，要创建基于列c 的数据分片的分区表，主键必须包含列 c，比如下面的建表语句：

CREATE TABLE t1
(
    a INT,
    b INT,
    c DATETIME,
    d VARCHAR(32),
    e INT,
    PRIMARY KEY (a, b, c),
    KEY idx_e (e)
)
    partition by range columns (c) (
        PARTITION p0000 VALUES LESS THAN ('2019-01-01'),
        PARTITION p2019 VALUES LESS THAN ('2020-01-01'),
        PARTITION p2020 VALUES LESS THAN ('2021-01-01'),
        PARTITION p9999 VALUES LESS THAN (MAXVALUE)
        );

创建完表后，在物理存储上会看到四个分区所对应 ibd 文件，也就是把数据根据时间列 c 存储到对应的 4 个文件中：

t#p#p0000.ibd  t#p#p2019.ibd  t#p#p2020.ibd  t#p#p9999.ibd

所以，你要理解的是：MySQL 中的分区表是把一张大表拆成了多张表，每张表有自己的索引，从逻辑上看是一张表，但物理上存储在不同文件中。

另外，对于唯一索引的实现，可能和你原本的理解有些不同，我们接着往下看。

分区表注意事项：唯一索引

在 MySQL 数据库中，分区表的索引都是局部，而非全局。也就是说，索引在每个分区文件中都是独立的，所以分区表上的唯一索引必须包含分区列信息，否则创建会报错，比如：

ALTER TABLE t1 ADD UNIQUE KEY idx_d(d);

A UNIQUE INDEX must include all columns in the table's partitioning function

你可以看到错误提示： 唯一索引必须包含分区函数中所有列。而下面的创建才能成功：

ALTER TABLE t1 ADD UNIQUE KEY idx_d(d,c);

但是，正因为唯一索引包含了分区列，唯一索引也就变成仅在当前分区唯一，而不是全局唯一了。那么对于上面的表 t1，插入下面这两条记录都是可以的：

INSERT INTO t1 VALUES 
(1,1,'2021-01-01','aaa',1),
(1,1,'2020-01-01','aaa',1);
SELECT * FROM t1;

你可以看到，列 d 都是字符串‘aaa’，但依然可以插入。这样带来的影响是列 d 并不是唯一的，所以你要由当前分区唯一实现全局唯一。

那如何实现全局唯一索引呢？ 和之前表结构设计时一样，唯一索引使用全局唯一的字符串（如类似 UUID 的实现），这样就能避免局部唯一的问题。

总结

• 分区表的主键一定要包含分区函数的列
• 分区表的唯一索引不是全局唯一,而是每个分区唯一