MySQL分区

一、分区概述

分区是将一张表的数据按照一定的规则来分成N部分，在逻辑层面还是一张表。MySQL从5.1版本开始支持分区功能。

分区的优点如下：

• 通过删除仅包含该数据的分区(或多个分区)，可以很容易地从分区表中删除失去其用途的那些数据。相反，在某些情况下，通过添加一个或多个分区来专门存储该数据，可以极大地促进添加新数据的过程。
• 一些查询可以得到极大的优化。在扫描操作中，如果MySQL的优化器知道哪个分区中才包含特定查询中的数据，它就能直接去扫描那些分区的数据，而不用浪费很多时间扫描不需要的地方了，即自动排除任何不需要的分区。这种排除不匹配分区(以及它们包含的任何行)的能力成为分区修剪。
• MySQL还支持显示分区选择，如select * from t partition (p0,p1) where c<5，该查询只会在分区p0、p1上查找而忽略其他任何分区，当您已经知道要检查的一个或多个分区时，这种指定分区的方式可以大大加快查询速度。

二、检查服务

• 检查MySQL服务器是否支持分区

mysql> SHOW PLUGINS;
+------------+----------+----------------+---------+---------+
| Name       | Status   | Type           | Library | License |
+------------+----------+----------------+---------+---------+
| binlog     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| partition  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| ARCHIVE    | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| BLACKHOLE  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| CSV        | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| FEDERATED  | DISABLED | STORAGE ENGINE | NULL    | GPL     |
| MEMORY     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| InnoDB     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| MRG_MYISAM | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| MyISAM     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| ndbcluster | DISABLED | STORAGE ENGINE | NULL    | GPL     |
+------------+----------+----------------+---------+---------+
11 rows in set (0.00 sec)

三、实践

• 创建分区

假设有张名为v_user的表，分别由name、pass_time两个字段，name类型为varchat(32)，pass_time类型为datetime(0)，我们希望在该表中创建一个以pass_time字段为分区键的分区。

ALTER TABLE v_user PARTITION BY RANGE (TO_DAYS(pass_time)) (
	PARTITION p20230314 VALUES LESS THAN (TO_DAYS('2023-03-15')),
	PARTITION p20230315 VALUES LESS THAN (TO_DAYS('2023-03-16'))
);

• 添加分区

-- 在表中添加两个分区
ALTER TABLE v_user ADD PARTITION (
	PARTITION p20230316 VALUES LESS THAN (TO_DAYS('2023-03-17')),
	PARTITION p20230317 VALUES LESS THAN (TO_DAYS('2023-03-18'))
);

• 查询分区信息

SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS FROM information_schema.`PARTITIONS` WHERE TABLE_NAME = 'table_name';

• 删除分区

-- 删除名为p20230315的分区（删除分区会同时删除该分区中的数据）
ALTER TABLE v_user DROP PARTITION p20230315;

• 查询指定分区的数据

-- 查询分区p20230301的数据
select * from v_user PARTITION(p20230301);

-- 在分区p20230301查询name为张三的数据
select * from v_user PARTITION(p20230301) WHERE NAME = "张三";