分区表

一、分区表的核心原理

1.1 逻辑表 vs 物理分区

• 逻辑表：用户通过 CREATE TABLE ... PARTITION BY ... 创建的分区表，在 information_schema 中只有一条记录。
• 物理分区：每个分区对应一个独立的表空间文件（InnoDB 中是 .ibd 文件，MyISAM 中是 .MYD / .MYI）。数据按分区规则写入不同的物理文件。

当执行查询时，优化器会根据 WHERE 条件中的分区键，只扫描必要的分区，这个行为称为 分区裁剪（Partition Pruning）。

1.2 支持的存储引擎

• InnoDB（最常用，支持所有分区类型）
• MyISAM（已逐渐淘汰）
• NDB（用于 MySQL Cluster）

---

二、分区类型详解

MySQL 支持以下几种分区方式，每种方式适合不同的数据分布需求。

分区类型	语法 / 规则	适用场景	示例
RANGE分区	PARTITION BY RANGE(column) (PARTITION p1 VALUES LESS THAN (value), ...)	时间序列数据、有序范围	按年份：VALUES LESS THAN (2020), LESS THAN (2021)…
LIST分区	PARTITION BY LIST(column) (PARTITION p1 VALUES IN (value_list), ...)	离散枚举值，如地区、状态	按省份：VALUES IN ('bj','sh'), VALUES IN ('gz','sz')
HASH分区	PARTITION BY HASH(column) PARTITIONS n;	均匀分散写入，避免热点	按用户ID：HASH(user_id) PARTITIONS 16
KEY分区	PARTITION BY KEY(column) PARTITIONS n;	类似 HASH，但使用 MySQL 内部哈希函数，适用于字符串等非整数	按登录名：KEY(username) PARTITIONS 8
子分区（Subpartitioning）	SUBPARTITION BY HASH/KEY	在一级分区内再次细分	RANGE 按年，再 HASH 按用户ID，实现“年+随机分布”

2.1 RANGE 分区示例

CREATE TABLE orders (
    id INT NOT NULL,
    order_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

2.2 LIST 分区示例

CREATE TABLE sales (
    id INT,
    region VARCHAR(10),
    amount INT
) PARTITION BY LIST COLUMNS(region) (
    PARTITION p_north VALUES IN ('bj', 'tj'),
    PARTITION p_south VALUES IN ('gz', 'sz', 'sh')
);

注意：MySQL 5.5+ 支持 LIST COLUMNS 和 RANGE COLUMNS，可以直接使用多列或非整数类型作为分区键，例如 VARCHAR、DATE。

2.3 HASH 分区示例

CREATE TABLE user_log (
    user_id INT,
    action VARCHAR(20),
    created DATETIME
) PARTITION BY HASH(user_id) PARTITIONS 16;

数据通过 MOD( HASH(user_id), 16 ) 分布在 16 个分区中。

---

三、分区表的运维管理

分区表的一大优势是 管理方便，尤其是对历史数据的清理和归档。

3.1 添加分区

ALTER TABLE orders ADD PARTITION (PARTITION p2023 VALUES LESS THAN (2024));

3.2 删除分区（快速清空数据）

ALTER TABLE orders DROP PARTITION p2019;

DROP PARTITION 比 DELETE 快几个数量级，并且几乎不产生 undo log，适合定期清理历史数据。

3.3 重组分区（拆分或合并）

-- 将 p_future 拆分成两个分区
ALTER TABLE orders REORGANIZE PARTITION p_future INTO (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

3.4 检查分区数据分布

SELECT PARTITION_NAME, TABLE_ROWS, DATA_LENGTH 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME = 'orders';

---

四、分区表查询优化（关键）

4.1 分区裁剪（Partition Pruning）

当查询 WHERE 条件中包含 分区键 且使用了可确定范围的表达式时，优化器会跳过不相关的分区。

有效裁剪的例子：

-- 只扫描 p2022 和 p2023 分区
SELECT * FROM orders WHERE order_date BETWEEN '2022-01-01' AND '2023-12-31';

-- 只扫描 p2022 分区（= 也是明确范围）
SELECT * FROM orders WHERE YEAR(order_date) = 2022;

无效裁剪（全表扫描所有分区）的例子：

-- 对分区键使用函数，但无法反向推导出范围
SELECT * FROM orders WHERE YEAR(order_date) + 1 = 2023;  -- 不会裁剪
SELECT * FROM orders WHERE order_date = '2022-05-01'    -- 这其实可以裁剪，因为直接比较列

技巧：尽量直接使用分区列进行范围比较，而不是在列上包裹函数。

4.2 聚合查询优化

如果每个分区的数据量不大，可以对每个分区进行聚合，然后再合并结果。例如：

-- 按月分区，统计每年金额
SELECT YEAR(order_date) AS year, SUM(amount) 
FROM orders 
GROUP BY YEAR(order_date);

优化器会自动对每个分区做局部聚合，最后汇总。

4.3 跨分区查询的代价

• 查询涉及多个分区时，需要扫描并合并结果，效率低于单分区查询。
• 如果 没有指定分区键，则会扫描 所有分区，性能可能比普通表还差（因为分区数量多导致上下文切换开销）。

建议：所有重要查询都应该带上分区键条件。

---

五、分区表的限制（非常重要）

限制项	说明
唯一索引（包括主键）必须包含分区键	如果表有主键或唯一键，那么分区键必须是这些键的一部分（可以是复合键之一）。例如主键是 (id, order_date)，则分区键可以是 order_date。
不支持外键	分区表的外键约束不被支持，即使表是 InnoDB 引擎。
分区数量上限	最多 8192 个分区（MySQL 8.0 允许子分区后总分区数仍受限制）。
全文索引不支持分区	MyISAM 可以但已废弃，InnoDB 不支持对分区表使用 FULLTEXT。
空间索引（GIS）不支持分区	不能对 GEOMETRY 列创建 SPATIAL 索引。
某些存储引擎的限制	例如 MERGE、CSV、FEDERATED 不支持分区。
锁定问题	DDL 操作（如添加/删除分区）可能会锁表（取决于 MySQL 版本和算法，8.0 后有所改善）。

---

六、适用场景与典型案例

6.1 典型适用场景

1. 时间序列日志 / 订单 / 事件表
   • 数据量大，但查询通常集中在最近一段时间。
   • 按时间 RANGE 分区，既能加速查询（裁剪到最近几个分区），又能轻松删除旧数据（DROP PARTITION）。
2. 大表的周期性归档
   • 不需要使用分库分表，只需要按时间分区，每月或每年切换。
3. 均匀分散写入热点（HASH / KEY 分区）
   • 例如用户动作日志，用 user_id 做 HASH 分区，避免写入集中在一个物理文件（降低 InnoDB 的 page 锁竞争）。

6.2 不适合分区的场景

• 表很小（< 100 万行）：分区不会带来性能提升，反而增加管理开销。
• 唯一索引无法包含分区键：例如订单表主键是 order_id，但你想按 created_time 分区，这不被允许（除非你把 created_time 加入主键）。
• 频繁跨分区查询：如果查询总是跨多个分区且无法带上分区键，性能会下降。
• 需要外键约束：分区表不支持外键。

6.3 真实案例

场景：电商订单表 orders，数据量 3 亿行，按年增长 6000 万行。业务查询主要是最近 3 个月的订单，同时需要保留 5 年数据，清理旧数据。

方案：按 order_date 按月 RANGE 分区。

• 查询 WHERE order_date > '2025-01-01' 只扫描 5 个分区，速度极快。
• 删除 5 年前的数据：ALTER TABLE orders DROP PARTITION p201901, p201902 ...
• 每月新增分区：提前创建未来 2 个月的分区。

---

七、分区表 vs 分表（Sharding）

维度	分区表	分表（应用程序分片）
对应用透明性	✅ 完全透明，SQL 不变	❌ 需要中间件或修改代码
跨分区查询	优化器自动处理，但效率可能下降	需要应用层聚合，实现复杂
跨库事务	不涉及，所有分区在同一实例	可能涉及分布式事务
扩展性	受限于单机磁盘 I/O 和 CPU	可水平扩展到多台服务器
运维复杂度	低（MySQL 自带管理）	高（需要中间件、数据迁移工具）
适用规模	单机 TB 级数据，千万~亿级行	单机无法承受的超大规模（>5亿行、>5TB）

结论：能先用分区表解决的，就不要上分库分表。分库分表是最后的手段，会引入极大的复杂性。

---

八、最佳实践与注意事项

1. 分区键的选择
   • 选择高选择性的列，使得数据尽量均匀分布（HASH 分区）或符合业务访问模式（RANGE 分区）。
   • 如果表有主键 / 唯一键，分区键必须是这些键的子集。
2. 分区数量不宜过多
   • 建议每个表分区数不超过 100 个。太多分区会导致文件句柄过多、INFORMATION_SCHEMA 查询变慢。
3. 定期管理分区
   • 对于 RANGE 分区，提前创建未来分区（比如提前 2 个月），避免插入数据时因无对应分区而报错。
   • 使用事件调度器（Event Scheduler）自动增加 / 删除分区。

九、分区表索引情况

当你对分区表创建索引（包括主键索引、唯一索引、二级索引）时，MySQL 会为每一个分区单独构建一份索引文件

• 分区裁剪：优化器先根据 WHERE 条件确定需要扫描哪些分区。
• 独立索引查找：对每一个需要扫描的分区，使用该分区自己的索引结构进行检索。
• 结果合并：如果是跨分区的查询，还需要将各分区返回的结果合并（比如 UNION 或 ORDER BY 需要归并）。