很多同学会认为,分区表是把一张大表拆分成了多张小表,所以这样 MySQL 数据库的性能会有大幅提升。这是错误的认识!如果你寄希望于通过分区表提升性能,那么我不建议你使用分区,因为做不到。
分区表技术不是用于提升 MySQL 数据库的性能,而是方便数据的管理。
从表格中可以看到,B+ 树的高度为 4 能存放数十亿的数据,一次查询只需要占用 4 次 I/O,速度非常快。
但是当你使用分区之后,效果就不一样了,比如上面的表 t,我们根据时间拆成每年一张表,这时,虽然 B+ 树的高度从 4 降为了 3,但是这个提升微乎其微。
除此之外,分区表还会引入新的性能问题,比如非分区列的查询
。非分区列的查询,即使分区列上已经创建了索引,但因为索引是每个分区文件对应的本地索引,所以要查询每个分区。
接着,我们看一下这条 SQL 以及它的执行计划:
SELECT * FROM t WHERE d = 'aaa'
******** 1. row ********
id: 1
select_type: SIMPLE
table: t
partitions: p0000,p2019,p2020,p9999
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 50.00
Extra: Using where
通过执行计划我们可以看到:上述 SQL 需要访问 4 个分区,假设每个分区需要 3 次 I/O,则这条 SQL 总共要 12 次 I/O。但是,如果使用普通表,记录数再多,也就 4 次的 I/O 的时间。
所以,分区表设计时,务必明白你的查询条件都带有分区字段,否则会扫描所有分区的数据或索引
。所以,分区表设计不解决性能问题,更多的是解决数据迁移和备份的问题。
而为了让你更好理解分区表的使用,我们继续看一个真实业务的分区表设计。
分区表在业务上的设计
以电商中的订单表 Orders 为例,如果在类似淘宝的海量互联网业务中,Orders 表的数据量会非常巨大,假设一天产生 5000 万的订单,那么一年表 Orders 就有近 180 亿的记录。
所以对于订单表,在数据库中通常只保存最近一年甚至更短时间的数据,而历史订单数据会入历史库。除非存在 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`)
)
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_par
TRUNCATE PARTITION p1998
上述 SQL 执行速度非常快,因为实际执行过程是把分区文件删除和重建。另外产生的日志也只有一条 DDL 日志,也不会导致主从复制延迟问题。
# at 425
#210328 12:10:12 server id 8888 end_log_pos 549 Query thread_id=9 exec_time=0 error_code=0 Xid = 10
SET TIMESTAMP=1619583012/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
ALTER TABLE orders TRUNCATE PARTITION p1998
/*!*/;
总结
- 分区表不能提升性能