# MySQL分区类型
1. RANGE分区
– 基于范围值分区
– 适合时间序列数据
– 支持分区裁剪
– 易于管理历史数据

2. LIST分区
– 基于离散值分区
– 适合枚举类型数据
– 支持分区裁剪
– 明确的数据分布

3. HASH分区
– 基于哈希算法分区
– 数据均匀分布
– 适合负载均衡
– 不支持分区裁剪

4. KEY分区
– 基于MySQL哈希算法
– 自动选择分区键
– 数据均匀分布
– 不支持分区裁剪

# RANGE分区示例
1. 创建RANGE分区表
CREATE TABLE orders_range (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_no VARCHAR(32) NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
amount DECIMAL(10,2) NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p2026 VALUES LESS THAN (2027),
PARTITION pmax VALUES LESS THAN MAXVALUE
);

输出示例：
Query OK, 0 rows affected (0.05 sec)

2. 查看分区信息
SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = ‘orders_range’;

输出示例：
+—————-+——————+———————-+———————–+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+—————-+——————+———————-+———————–+
| p2023 | RANGE | YEAR(created_at) | 2024 |
| p2024 | RANGE | YEAR(created_at) | 2025 |
| p2025 | RANGE | YEAR(created_at) | 2026 |
| p2026 | RANGE | YEAR(created_at) | 2027 |
| pmax | RANGE | YEAR(created_at) | MAXVALUE |
+—————-+——————+———————-+———————–+
5 rows in set (0.01 sec)

# LIST分区示例
1. 创建LIST分区表
CREATE TABLE orders_list (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_no VARCHAR(32) NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
region_id TINYINT UNSIGNED NOT NULL,
amount DECIMAL(10,2) NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id, region_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LIST (region_id) (
PARTITION p_north VALUES IN (1, 2, 3),
PARTITION p_south VALUES IN (4, 5, 6),
PARTITION p_east VALUES IN (7, 8, 9),
PARTITION p_west VALUES IN (10, 11, 12),
PARTITION p_other VALUES IN (0)
);

输出示例：
Query OK, 0 rows affected (0.03 sec)

2. 查看分区信息
SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = ‘orders_list’;

输出示例：
+—————-+——————+———————-+———————–+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+—————-+——————+———————-+———————–+
| p_north | LIST | region_id | 1,2,3 |
| p_south | LIST | region_id | 4,5,6 |
| p_east | LIST | region_id | 7,8,9 |
| p_west | LIST | region_id | 10,11,12 |
| p_other | LIST | region_id | 0 |
+—————-+——————+———————-+———————–+
5 rows in set (0.01 sec)

# HASH分区示例
1. 创建HASH分区表
CREATE TABLE orders_hash (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_no VARCHAR(32) NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
amount DECIMAL(10,2) NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY HASH(user_id)
PARTITIONS 8;

输出示例：
Query OK, 0 rows affected (0.02 sec)

2. 查看分区信息
SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = ‘orders_hash’;

输出示例：
+—————-+——————+———————-+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION |
+—————-+——————+———————-+
| p0 | HASH | user_id |
| p1 | HASH | user_id |
| p2 | HASH | user_id |
| p3 | HASH | user_id |
| p4 | HASH | user_id |
| p5 | HASH | user_id |
| p6 | HASH | user_id |
| p7 | HASH | user_id |
+—————-+——————+———————-+
8 rows in set (0.01 sec)

# KEY分区示例
1. 创建KEY分区表
CREATE TABLE orders_key (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_no VARCHAR(32) NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
amount DECIMAL(10,2) NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY KEY()
PARTITIONS 6;

输出示例：
Query OK, 0 rows affected (0.02 sec)

2. 查看分区信息
SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = ‘orders_key’;

输出示例：
+—————-+——————+———————-+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION |
+—————-+——————+———————-+
| p0 | KEY | id |
| p1 | KEY | id |
| p2 | KEY | id |
| p3 | KEY | id |
| p4 | KEY | id |
| p5 | KEY | id |
+—————-+——————+———————-+
6 rows in set (0.01 sec)