📌 关键词： MySQL分区表、Range分区、List分区、Hash分区、分区裁剪、大表优化、水平拆分、数据库架构
大家好呀！我是数据库小学妹👋
前面我们学了很多优化技巧：索引、执行计划、慢查询……但是，当一张表的数据量达到几千万甚至几亿时，即使索引命中，查询也可能因为扫描范围太大而变慢，维护操作（如删除旧数据、重建索引）也会变得非常耗时。
有没有一种方法，能把一张大表物理拆分成多个小区域，但对上层查询来说仍然是同一张表？
答案就是今天我们要讲的MySQL分区表（Partitioning）。它就像是给大衣柜装了几个隔板，让你能按季节或类别快速找到衣服，不用翻遍整个衣柜。
一、 什么是分区表？（逻辑一张表，物理多张表）
简单来说，分区表就是把一个大表在物理存储上切分成多个小的子集（分区）。
- 对应用层：它依然是一张表，SQL不需要改。
- 对数据库：它是多个独立的物理片段。
🚩核心价值：分区裁剪（Partition Pruning）。当查询条件能命中分区键时，MySQL只会扫描对应的那个小分区，而不是全表。在海量数据下，性能提升是毁灭级的（从秒级降到毫秒级）。
二、三种分区方式（新手掌握这三种就够了）
🔖Range 分区（按范围切）—— 最常用
适用：按时间序列存储数据（如日志、订单）。
场景： 把订单表按年份切分，2024年的数据放一个分区，2025年的放另一个分区。
🎯示例：
CREATE TABLE orders (
id INT NOT NULL,
order_date DATE
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2024 VALUES LESS THAN (2025), -- 存放 year(order_date) < 2025 的数据
PARTITION p2025 VALUES LESS THAN (2026), -- 存放 2025年 的数据
PARTITION p_future VALUES LESS THAN MAXVALUE
);
优势： 极其方便的数据归档。如果要删除2024年的数据，直接 ALTER TABLE ... DROP PARTITION p2024，瞬间完成，而不是慢吞吞的 DELETE。
🔖Hash 分区（按散列切）—— 最均匀
适用：数据没有明显时间特征，需要均匀分布。
场景： 用户表，你想把数据均匀打散到4个物理文件中。
🎯示例：
PARTITION BY HASH(id) PARTITIONS 4;
优势： 数据分布均匀，能有效解决单表物理文件过大的问题。
🔖List 分区（按列表切）—— 最灵活
适用：按特定的离散值切分（如地区、状态）。
场景： 想把“四川、重庆”的用户放在一个分区（为了本地化服务），把“北京、上海”放在另一个分区。
🎯示例：
PARTITION BY LIST(store_id) (
PARTITION p_west VALUES IN (1, 2), -- 假设1是成都，2是重庆
PARTITION p_east VALUES IN (3, 4) -- 假设3是北京，4是上海
);
三、实战：创建和查询分区表（Range分区）
📚1.创建RANGE分区表（按年份）
CREATE TABLE orders (
id INT NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
- 2022年的数据存入
p2022分区，2023年存入p2023，以此类推 MAXVALUE兜底，存放2026年及之后的数据
📚2. 查询时自动分区裁剪
EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
执行计划中 partitions 列会显示只扫描 p2023 分区，其他分区不扫描，这就是分区裁剪。
📚3. 查看分区信息
SELECT * FROM information_schema.partitions WHERE table_name = 'orders';
四、分区的核心优势
| 优势 | 说明 |
|---|---|
| 查询性能提升 | 分区裁剪只扫描相关分区，减少IO |
| 维护更方便 | 单独删除、重建、备份某个分区（如删除旧数据直接DROP PARTITION） |
| 并行处理 | 对分区表执行聚合操作，可并行扫描多个分区 |
对比：传统删除大量旧数据用 DELETE FROM ... WHERE ...，耗时长且产生大量binlog；而分区表可以：
ALTER TABLE orders DROP PARTITION p2022;
瞬间删除，速度极快！
五、分区表的避坑指南
💣不是所有大表都适合分区
- 坑： 你的表只有10万行，却去搞分区。
- 后果： 负优化。分区本身有元数据管理的开销，小表分区反而会让查询变慢。
- 建议： 单表数据量超过 1000万行 或 物理文件超过 10GB 时，再考虑分区。
💣分区键（Partition Key）选错，全表扫描依旧
- 坑： 你按
order_date做了 Range 分区，但业务查询总是用user_id。 - 后果： MySQL无法进行“分区裁剪”，它必须去查所有的分区（All partitions），性能甚至不如单表。
- 建议： 分区键必须是查询条件中最常用、且能过滤掉大量数据的字段（通常是时间或地域）。
💣唯一索引的陷阱
- 坑： 在分区表中，主键或唯一索引必须包含分区键。
- 原因： 数据库要保证唯一性，如果分区键不在索引里，校验唯一性时就得跨所有分区查询，这就失去了分区的意义。
- 报错场景： 如果你强行创建一个不包含分区键的唯一索引，MySQL会直接报错
ERROR 1503。
六、实战场景：订单表按月份分区
CREATE TABLE orders_month (
id INT NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(10,2)
)
PARTITION BY RANGE COLUMNS(order_date) (
PARTITION p202501 VALUES LESS THAN ('2025-02-01'),
PARTITION p202502 VALUES LESS THAN ('2025-03-01'),
PARTITION p202503 VALUES LESS THAN ('2025-04-01'),
PARTITION p_max VALUES LESS THAN MAXVALUE
);
RANGE COLUMNS可以直接用日期类型，更直观- 每月一个分区，需要定期增加新分区或使用自动化脚本
七、总结
- 分区表物理拆分、逻辑统一，适合大表的时间范围查询和快速归档
- RANGE分区最常用，配合分区裁剪提升查询性能
- 分区不是万能的，小表不需要，索引设计仍是基础
学会了分区，你就能在单表数据量达到亿级时，优雅地应对查询和维护的挑战。
👋 我是数据库小学妹一个用设计师思维学数据库的转行人。你的工作中用过分区表吗？遇到过什么坑？欢迎一起交流。
本文示例基于 MySQL 8.0。分区表版本差异较大，低版本可能有更多限制，请查阅官方文档。
