书籍 / MySQL 性能优化完全指南
第 17 章

分区表实战

MySQL 分区表完全指南

分区将大型表分成较小的、更易于管理的部分。这提高了查询性能，启用了有效的归档，并为有数百万行的表简化了维护。

1. 分区类型


RANGE分区（最常见）：

CREATE TABLE orders (
  id INT,
  order_date DATE
) PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

优势：
├─ 查询只扫描相关分区
├─ 删除旧数据：DROP PARTITION（毫秒级）
├─ 易于维护：按日期管理数据

LIST分区：
按列表值分配（如：国家代码）

HASH分区：
均匀分布（用于并行，不用于剪枝）

2. 分区维护


添加分区：
ALTER TABLE orders ADD PARTITION (
  PARTITION p2024 VALUES LESS THAN (2025)
);

删除分区（秒级）：
ALTER TABLE orders DROP PARTITION p2020;

传统DELETE对比：
DELETE FROM orders WHERE YEAR(order_date) = 2020;
├─ 耗时：数分钟（百万级行数）
├─ 日志：撤销日志+重做日志
└─ 影响：锁定写入，占用内存

DROP PARTITION对比：
├─ 耗时：毫秒（元数据操作）
├─ 日志：最小
└─ 影响：无

3. 分区剪枝


分区剪枝：查询优化器跳过无关分区

常数条件（MySQL所有版本）：
SELECT * FROM orders WHERE order_date >= '2023-01-01';
├─ 只扫描：p2023, pmax
└─ 跳过：p2020, p2021, p2022

验证剪枝：
EXPLAIN SELECT * ... WHERE order_date >= '2023-01-01'\G
partitions列显示：p2023, pmax ✓

INFORMATION_SCHEMA：
SELECT PARTITION_NAME, PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'orders';

4. 限制与注意事项


唯一约束：

唯一键必须包含分区键！

❌ 错误：
CREATE TABLE orders (
  id INT UNIQUE,
  order_date DATE
) PARTITION BY RANGE (YEAR(order_date)) (...);
-- 错误：无分区键的唯一约束！

✓ 正确：
CREATE TABLE orders (
  id INT,
  order_date DATE,
  UNIQUE (id, order_date)  -- 包含分区键
) PARTITION BY RANGE (YEAR(order_date)) (...);

外键：

❌ 分区表不能作为外键父表
❌ 分区表引用外键（支持有限）

全文索引：
❌ 分区表不支持

空间索引：
❌ 分区表不支持

4. 实际应用：时间序列数据


场景：10亿行日志表，按日期分区

CREATE TABLE logs (
  id BIGINT AUTO_INCREMENT,
  timestamp DATETIME,
  data TEXT,
  PRIMARY KEY (id, timestamp)
) PARTITION BY RANGE (TO_DAYS(timestamp)) (
  PARTITION p_day_1 VALUES LESS THAN (738000),
  ...（365日分区）
  PARTITION p_max VALUES LESS THAN MAXVALUE
);

优势：

日志删除：
ALTER TABLE logs DROP PARTITION p_day_1;
├─ 速度：毫秒级
└─ 传统DELETE：数小时

查询优化：
SELECT * FROM logs WHERE timestamp >= '2024-01-01';
├─ 扫描：~31分区（1月份）
├─ 跳过：~334分区
└─ 性能：8%的全表扫描时间

自动管理：
Cron每天添加新分区

6. 分区 vs 分片


分区（单服务器）：
├─ 所有数据在一个MySQL实例
├─ MySQL自动处理（透明）
├─ 与非分区表相同SQL
└─ 受服务器资源限制

分片（多服务器）：
├─ 数据分布在多个实例
├─ 手动应用逻辑
├─ 每分片不同查询
└─ 水平扩展超越单服务器

何时使用分区：
├─ 表适合单服务器存储
├─ 读/写QPS单服务器可处理
└─ 运维复杂度可接受

何时使用分片：
├─ 数据超过单服务器容量
├─ QPS超过单服务器能力
├─ 需要高可用
└─ 需要全球分布

5. 最佳实践

总结

分区适合管理非常大的表（>10GB），特别是时间序列数据。基于日期的每日或每月分区通常效果最好。自动化分区生命周期管理以避免手动开销。

本章评分
4.9  / 5  (16 评分)

💬 留言讨论