Mysql学习-第二章（使用分区表）

分区表涉及表结构设计，也设计索引的设计，是否需要设计使用分区表

分区表的使用

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

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

CREATE table part(
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)
);

出现错误

[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).

修改为如下语句

要创建基于列c 的数据分片的分区表，主键必须包含列 c，比如下面的建表语句：

CREATE table part(
a int,
b int,
c DATETIME(6),
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文件

show variables like '%datadir%';

对应数据库下，查看ibd文件

可以看到存在4个分区对应的ibd文件

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

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

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

alter table part add UNIQUE KEY idx_d(d);

由于没有分区的键

A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).

唯一索引必须包含分区函数中所有列。示例：

alter table part add UNIQUE KEY idx_d(d,c);

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

INSERT into part VALUES(1,1,'2021-01-01','aaa',1),(1,1,'2020-01-01','aaa',1);
SELECT * from part;

a	b	c	                        d	e
1	1	2020-01-01 00:00:00.000000	aaa	1
1	1	2021-01-01 00:00:00.000000	aaa	1

仅有c和d才能组成唯一索引。

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

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

误区

分区表技术不是用于提升 MySQL 数据库的性能，而是方便数据的管理。

，B+ 树的高度为 4 能存放数十亿的数据，一次查询只需要占用 4 次 I/O，速度非常快。

但是当你使用分区之后，效果就不一样了，比如上面的表 t，我们根据时间拆成每年一张表，这时，虽然 B+ 树的高度从 4 降为了 3，但是这个提升微乎其微。

除此之外，分区表还会引入新的性能问题，比如非分区列的查询。非分区列的查询，即使分区列上已经创建了索引，但因为索引是每个分区文件对应的本地索引，所以要查询每个分区。

explain 
 select * from part where d='aaa';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	part	p0000,p2019,p2020,p9999	ref	idx_d	idx_d	99	const	2	100	

上述 SQL 需要访问 4 个分区，假设每个分区需要 3 次 I/O，则这条 SQL 总共要 12 次 I/O。但是，如果使用普通表，记录数再多，也就 4 次的 I/O 的时间。

所以，分区表设计时，务必明白你的查询条件都带有分区字段，否则会扫描所有分区的数据或索引。所以，分区表设计不解决性能问题，更多的是解决数据迁移和备份的问题。

业务设计

以电商中的订单表 Orders 为例，如果在类似淘宝的海量互联网业务中，Orders 表的数据量会非常巨大，假设一天产生 5000 万的订单，那么一年表 Orders 就有近 18 亿的记录。

所以对于订单表，在数据库中通常只保存最近一年甚至更短时间的数据，而历史订单数据会入历史库。除非存在 1 年以上退款的订单，大部分订单一旦完成，这些数据从业务角度就没用了。

那么如果你想方便管理订单表中的数据，可以对表 Orders 按年创建分区表，如：

CREATE TABLE `orders` (
`o_orderkey` int NOT NULL,
`O_CUSTKEY` int NOT NULL,
`O_ORDERSTATUS` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`O_TOTALPRICE` decimal(15,2) NOT NULL,
`O_ORDERDATE` date NOT NULL,
`O_ORDERPRIORITY` char(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`O_CLERK` char(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`O_SHIPPRIORITY` int NOT NULL,
`O_COMMENT` varchar(79) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`o_orderkey`,`O_ORDERDATE`),
KEY `orders_fk1` (`O_CUSTKEY`),
KEY `idx_orderdate` (`O_ORDERDATE`)
)ENGINE = InnoDB
PARTITION BY RANGE COLUMNS(o_orderdate)
(
PARTITION p0000 VALUES LESS THAN ('1992-01-01')ENGINE = InnoDB,
PARTITION p1992 VALUES LESS THAN ('1993-01-01')ENGINE = InnoDB,
PARTITION p1993 VALUES LESS THAN ('1994-01-01')ENGINE = InnoDB,
PARTITION p1994 VALUES LESS THAN ('1995-01-01')ENGINE = InnoDB,
PARTITION p1995 VALUES LESS THAN ('1996-01-01')ENGINE = InnoDB,
PARTITION p1996 VALUES LESS THAN ('1997-01-01')ENGINE = InnoDB,
PARTITION p1997 VALUES LESS THAN ('1998-01-01')ENGINE = InnoDB,
PARTITION p1998 VALUES LESS THAN ('1999-01-01')ENGINE = InnoDB,
PARTITION p9999 VALUES LESS THAN (MAXVALUE)
);

这时 Orders 表的主键修改为了(o_orderkey,O_ORDERDATE)，数据按照年进行分区存储。那么如果要删除 1 年前的数据，比如删除 1998 年的数据，之前需要使用下面的 SQL，比如:

DELETE from orders 
where o_orderdate >= '1998-01-01'
and o_orderdate < '1999-01-01';

可这条 SQL 的执行相当慢，产生大量二进制日志，在生产系统上，也会导致数据库主从延迟的问题。

使用清空分区命令

ALTER table orders
TRUNCATE PARTITION p1998;

上述 SQL 执行速度非常快，因为实际执行过程是把分区文件删除和重建。另外产生的日志也只有一条 DDL 日志，也不会导致主从复制延迟问题。
知识点来自学习-姜承尧老师拉钩网教导内容。（mysql8.0已验证）