分区表
导学
当一张表的数据量达到千万甚至亿级时，全表扫描的代价会变得不可接受。即使加了索引，维护索引的成本、备份和恢复的时间都会成倍增长。**分区表（Partitioning）**将一张逻辑大表在物理上拆分为多个小分区，查询时只扫描相关分区，是 MySQL 5.7 中应对海量数据的重要手段。
定义
分区表：将一张表的数据根据分区键（Partition Key）的规则分散存储到多个独立的物理分区中。对应用层透明，仍然是一张逻辑表，但底层由多个物理文件组成。
分区类型
| 分区类型 | 分区键要求 | 适用场景 |
|---|---|---|
| RANGE | 连续区间（如日期、ID 范围） | 按时间归档（日志、订单） |
| LIST | 离散值列表（如地区编码） | 按枚举值分区（省、状态） |
| HASH | 整数表达式取模 | 均匀分散数据，无明确范围 |
| KEY | MySQL 内部哈希函数 | 类似 HASH，但由 MySQL 计算 |
| COLUMNS | RANGE/LIST 的多列扩展 | 多列组合分区键 |
SQL 示例
场景一：RANGE 分区（按时间范围）
执行语句：
-- 创建按月份 RANGE 分区的订单表
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
amount DECIMAL(10,2),
created_at DATETIME NOT NULL
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
PARTITION p202401 VALUES LESS THAN (202402),
PARTITION p202402 VALUES LESS THAN (202403),
PARTITION p202403 VALUES LESS THAN (202404),
PARTITION p202404 VALUES LESS THAN (202405),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
插入数据：
INSERT INTO orders (user_id, amount, created_at) VALUES
(1001, 199.99, '2024-01-15 10:00:00'),
(1002, 299.99, '2024-02-20 14:30:00'),
(1003, 99.99, '2024-03-05 09:00:00');
查看分区分布：
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'orders';
操作后结果：
| PARTITION_NAME | TABLE_ROWS |
|---|---|
| p202401 | 1 |
| p202402 | 1 |
| p202403 | 1 |
| p202404 | 0 |
| p_future | 0 |
结果解读：
PARTITION BY RANGE按YEAR*100+MONTH的整数值划分区间VALUES LESS THAN定义每个分区的上限（不包含上限值本身）p_future用MAXVALUE捕获所有超出已定义范围的数据- 查询
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'时，MySQL 只扫描p202401分区
场景二：分区裁剪（Partition Pruning）验证
当前数据状态：见上文 orders 表，3 条数据分布在 3 个分区。
执行语句：
-- 查询 2 月订单，验证是否只扫描一个分区
EXPLAIN PARTITIONS SELECT * FROM orders
WHERE created_at >= '2024-02-01' AND created_at < '2024-03-01';
操作后结果：
| id | select_type | table | partitions | type | rows |
|---|---|---|---|---|---|
| 1 | SIMPLE | orders | p202402 | ALL | 1 |
结果解读：
partitions = p202402表示 MySQL 只扫描了 2 月份的分区- 如果没有分区，同样条件的查询需要扫描全表所有数据
- 分区裁剪是分区表的核心价值：查询条件中包含分区键时，优化器自动排除无关分区
场景三：LIST 分区（按离散值）
执行语句：
-- 按部门编码 LIST 分区（模拟 employees 表）
CREATE TABLE employees_partitioned (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20),
dept_code INT,
score DECIMAL(5,2)
) ENGINE=InnoDB
PARTITION BY LIST (dept_code) (
PARTITION p_tech VALUES IN (1, 2), -- 技术部、研发部
PARTITION p_product VALUES IN (3, 4), -- 产品部、设计部
PARTITION p_ops VALUES IN (5, 6, 7) -- 运营、市场、销售
);
INSERT INTO employees_partitioned (emp_name, dept_code, score) VALUES
('大翔', 1, 100),
('白歌', 1, NULL),
('孔蓝', 3, 88);
查看分区数据：
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'employees_partitioned';
操作后结果：
| PARTITION_NAME | TABLE_ROWS |
|---|---|
| p_tech | 2 |
| p_product | 1 |
| p_ops | 0 |
结果解读：
LIST分区按离散值列表分配数据，适合状态、地区、部门等有限枚举值场景- 插入不在任何分区定义值中的数据会报错：
ERROR 1526 (HY000): Table has no partition for value ... - 必须确保所有可能的值都被某个分区覆盖，或使用
DEFAULT分区（MySQL 5.7 不支持 LIST 的 DEFAULT，需用 RANGE + MAXVALUE 或确保值全覆盖）
场景四：HASH 分区（均匀分散）
执行语句：
-- 按 user_id 取模 HASH 分区，均匀分散到 4 个分区
CREATE TABLE user_logs (
log_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
action VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB
PARTITION BY HASH(user_id)
PARTITIONS 4;
INSERT INTO user_logs (user_id, action) VALUES
(1001, 'login'),
(1002, 'logout'),
(1003, 'buy'),
(1004, 'view');
查看分区分布：
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'user_logs';
结果解读：
HASH分区使用user_id的哈希值对 4 取模，将数据均匀分散到 4 个分区- 适合没有明显范围特征、但需要分散 I/O 压力的场景
- 分区数建议选 2 的幂次方（如 4、8、16），使哈希分布更均匀
- 缺点：按范围查询（如
WHERE created_at > '2024-01-01'）无法利用分区裁剪，因为哈希值与时间无关
场景五：添加和删除分区
当前数据状态：基于场景一的 orders 表。
执行语句：
-- 添加新分区（2024年5月）
ALTER TABLE orders ADD PARTITION (
PARTITION p202405 VALUES LESS THAN (202406)
);
-- 删除旧分区（直接删除分区及其中所有数据，比 DELETE 快得多）
ALTER TABLE orders DROP PARTITION p202401;
操作后结果：
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'orders';
| PARTITION_NAME | TABLE_ROWS |
|---|---|
| p202402 | 1 |
| p202403 | 1 |
| p202404 | 0 |
| p202405 | 0 |
| p_future | 0 |
结果解读：
ADD PARTITION在 RANGE 分区末尾添加新分区（必须大于现有最大上限）DROP PARTITION直接删除整个分区文件，数据永久丢失，速度远快于DELETEDROP PARTITION是归档历史数据的常用手段（如删除 3 年前的日志分区）- 如果需要在中间插入分区，必须用
REORGANIZE PARTITION拆分现有分区
常见误区
| 误区 | 正解 |
|---|---|
| "分区表一定比普通表快" | 不一定。只有查询条件包含分区键时才能裁剪分区；否则扫描所有分区，比单表更慢（多文件开销）。 |
| "分区数越多越好" | 不是。分区过多（如 > 1024）会导致文件句柄耗尽、元数据管理开销增大。一般建议 10~100 个分区。 |
| "任何列都可以做分区键" | 不是。分区键必须是整数、日期或能转为整数的表达式。BLOB/TEXT、浮点数、某些函数结果不能作为分区键。 |
| "分区表的主键可以随便定义" | 不是。MySQL 5.7 要求分区键必须是主键/唯一键的一部分（或主键/唯一键包含分区键）。 |
| "HASH 分区支持范围查询裁剪" | 不支持。HASH 分区只支持等值查询（= 或 IN）的裁剪，范围查询需要扫描所有分区。 |
"DROP PARTITION 可以回滚" | 不能。DROP PARTITION 是 DDL，立即生效且不可回滚，数据永久删除。 |
面试考点
Q：MySQL 分区表的作用？
将大表在物理上拆分为多个小分区，查询时通过分区裁剪只扫描相关分区，提升查询性能；同时支持快速归档（
DROP PARTITION比DELETE快得多）。但对应用透明，仍然是一张逻辑表。
Q：RANGE 和 HASH 分区的区别？
RANGE 按连续区间分区（如时间范围），支持范围查询的分区裁剪，适合日志、订单等时间序列数据；HASH 按哈希取模分区，数据均匀分散，只支持等值查询裁剪，适合无范围特征但需要分散 I/O 的场景。
Q：分区表的主键有什么限制？
MySQL 5.7 中，分区键必须是主键（或唯一键）的一部分，或者主键/唯一键必须包含分区键。例如
PARTITION BY RANGE(id)时，主键只能是id或(id, ...)。这是为了确保唯一性检查只需在单个分区内进行。
Q：DROP PARTITION 和 DELETE 的区别？
DROP PARTITION直接删除分区文件，速度极快（类似DROP TABLE），但数据永久丢失不可回滚；DELETE逐行删除，可以回滚（在事务中），但大表删除极慢且会产生大量 undo log。历史数据归档优先用DROP PARTITION。
Q：分区表适合什么场景？不适合什么场景？
适合：1. 时间序列数据（日志、订单）按时间 RANGE 分区；2. 数据量巨大（千万级以上）且查询条件常包含分区键；3. 需要定期归档删除历史数据。不适合：1. 数据量小（百万级以下，分区 overhead 反而拖慢）；2. 查询条件不包含分区键；3. 需要跨分区频繁 JOIN（性能差）。
小结
- 分区表将大表物理拆分为多个分区，对应用透明
- RANGE 适合时间序列（支持范围裁剪），HASH 适合均匀分散（仅支持等值裁剪）
- 分区裁剪是核心性能收益来源，查询条件必须包含分区键
DROP PARTITION是快速归档历史数据的利器，但数据永久删除不可回滚- 分区键必须是主键/唯一键的一部分，分区数建议 10~100 个
下一章引子：分区表解决了大表的物理拆分问题，但性能诊断还需要更精细的工具。Performance Schema 是 MySQL 5.5+ 引入的低级性能监控引擎，能告诉你 SQL 执行过程中每一微秒花在了哪里。