Mysql5.7分区表使用

最后更新： 2021/8/10

分区键要求

every unique key on the table must use every column in the table's partitioning expression.This also includes the table's primary key, since it is by definition a unique key.

参考：https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-partitioning-keys-unique-keys.html

分区表

Range分区表

CREATE TABLE `trade_hislog` (
        `logid` INT(30) NOT NULL AUTO_INCREMENT COMMENT '日志id，主键',
        `sys_date` DATE NULL DEFAULT NULL,
        `create_date` VARCHAR(30) NULL DEFAULT NULL,
        UNIQUE INDEX `PK_TRADE_HISLOG` (`logid`,sys_date) USING BTREE
)
COMMENT='日志历史表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
partition by range (YEAR(sys_date))
(
partition p2019 values less than(2020),
partition p2020 values less than(2021),
partition p2021 values less than(2022),
partition p_all values less than maxvalue
);

# SQL执行计划，查询有做分区裁剪，只扫描P2020分区，符合预期
mysql> explain select * from trade_hislog a where a.sys_date=date'2020-09-06';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | a     | p2020      | ALL  | NULL          | NULL | NULL    | NULL | 2142570 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

参考

https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-functions.html
https://dev.mysql.com/doc/refman/5.7/en/partitioning-range.html