简单来说,分区表就是把物理表结构相同的几张表,通过一定算法,组成一张逻辑大表
。这种算法叫“分区函数”,当前 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 的实现),这样就能避免局部唯一的问题。
总结
- 分区表的主键一定要包含分区函数的列
- 分区表的唯一索引不是全局唯一,而是每个分区唯一