MySQL 分区表

分区是将大表的数据分散存储到多个物理文件中的技术，可以显著提升大表的查询性能和管理效率。本章将详细介绍 MySQL 分区表的使用方法。

分区概述

什么是分区？

分区是将一个表的数据按照一定规则分散存储到多个物理文件中，但对用户来说，分区表仍然是一个逻辑上的整体。

┌─────────────────────────────────────────────────────────────┐
│                    分区表逻辑视图                            │
├─────────────────────────────────────────────────────────────┤
│                                                            │
│   ┌─────────────────────────────────────────────────────┐ │
│   │                  orders 表（逻辑视图）                 │ │
│   │                    单一表名访问                        │ │
│   └─────────────────────────────────────────────────────┘ │
│                            │                                      │
│            ┌───────────────┼───────────────┐                  │
│            ▼               ▼               ▼                  │
│   ┌─────────────┐ ┌─────────────┐ ┌─────────────┐           │
│   │  Partition  │ │  Partition  │ │  Partition  │           │
│   │     p0      │ │     p1      │ │     p2      │           │
│   │  2024-01    │ │  2024-02    │ │  2024-03    │           │
│   │  orders.ibd │ │  orders.ibd │ │  orders.ibd │           │
│   └─────────────┘ └─────────────┘ └─────────────┘           │
│                                                            │
└─────────────────────────────────────────────────────────────┘

分区的优势

优势	说明
查询性能提升	只扫描相关分区，减少 I/O
管理便捷	可以单独操作分区（删除、归档）
数据加载快	可以快速加载新分区
并行处理	多个分区可以并行扫描

分区的限制

• 一个表最多 8192 个分区
• 分区表达式必须是整数或返回整数
• 主键/唯一键必须包含分区键
• 外键不支持分区表
• 全文索引不支持分区

分区类型

RANGE 分区

按连续范围分区，最常用的分区类型：

-- 创建 RANGE 分区表
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT,
    order_date DATE NOT NULL,
    customer_id INT,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, order_date)
) 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 pmax VALUES LESS THAN MAXVALUE
);

-- 说明：
-- p2022: 存储年份 < 2023 的数据（即 2022 年及之前）
-- p2023: 存储年份 < 2024 的数据（即 2023 年）
-- p2024: 存储年份 < 2025 的数据（即 2024 年）
-- pmax: 存储其他所有数据

RANGE COLUMNS 分区（支持多列和非整数）：

CREATE TABLE sales (
    id BIGINT AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    region VARCHAR(50),
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE COLUMNS (sale_date) (
    PARTITION p202401 VALUES LESS THAN ('2024-02-01'),
    PARTITION p202402 VALUES LESS THAN ('2024-03-01'),
    PARTITION p202403 VALUES LESS THAN ('2024-04-01'),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

LIST 分区

按离散值列表分区：

-- 创建 LIST 分区表
CREATE TABLE customers (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    region_id INT,
    PRIMARY KEY (id, region_id)
) PARTITION BY LIST (region_id) (
    PARTITION p_north VALUES IN (1, 2, 3, 4, 5),
    PARTITION p_south VALUES IN (6, 7, 8, 9, 10),
    PARTITION p_east VALUES IN (11, 12, 13, 14, 15),
    PARTITION p_west VALUES IN (16, 17, 18, 19, 20),
    PARTITION p_other VALUES IN (DEFAULT)
);

LIST COLUMNS 分区（支持字符串）：

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    city VARCHAR(50),
    PRIMARY KEY (id, city)
) PARTITION BY LIST COLUMNS (city) (
    PARTITION p_beijing VALUES IN ('北京', '天津'),
    PARTITION p_shanghai VALUES IN ('上海', '苏州', '杭州'),
    PARTITION p_guangzhou VALUES IN ('广州', '深圳'),
    PARTITION p_other VALUES IN (DEFAULT)
);

HASH 分区

按哈希函数均匀分布数据：

-- 创建 HASH 分区表
CREATE TABLE logs (
    id BIGINT AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(100),
    created_at TIMESTAMP,
    PRIMARY KEY (id)
) PARTITION BY HASH(id)
PARTITIONS 4;

-- 分区命名为 p0, p1, p2, p3
-- 数据根据 id % 4 分配到对应分区

LINEAR HASH 分区（线性哈希）：

CREATE TABLE logs_linear (
    id BIGINT AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(100),
    created_at TIMESTAMP,
    PRIMARY KEY (id)
) PARTITION BY LINEAR HASH(id)
PARTITIONS 4;

-- 线性哈希在添加/删除分区时更高效
-- 但数据分布可能不如普通哈希均匀

KEY 分区

类似 HASH 分区，但使用 MySQL 内置哈希函数：

-- 创建 KEY 分区表
CREATE TABLE sessions (
    session_id VARCHAR(64) PRIMARY KEY,
    user_id INT,
    data TEXT,
    expire_at TIMESTAMP
) PARTITION BY KEY(session_id)
PARTITIONS 4;

-- KEY 分区支持字符串类型的分区键

复合分区

MySQL 8.0 支持在分区内部再进行分区：

-- RANGE + HASH 复合分区
CREATE TABLE orders_sub (
    id BIGINT AUTO_INCREMENT,
    order_date DATE NOT NULL,
    customer_id INT,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (YEAR(order_date))
SUBPARTITION BY HASH(id)
SUBPARTITIONS 4 (
    PARTITION p2023 VALUES LESS THAN (2024) (
        SUBPARTITION s0,
        SUBPARTITION s1,
        SUBPARTITION s2,
        SUBPARTITION s3
    ),
    PARTITION p2024 VALUES LESS THAN (2025) (
        SUBPARTITION s4,
        SUBPARTITION s5,
        SUBPARTITION s6,
        SUBPARTITION s7
    )
);

分区管理

添加分区

-- RANGE 分区添加新分区
ALTER TABLE orders ADD PARTITION (
    PARTITION p2025 VALUES LESS THAN (2026)
);

-- LIST 分区添加新值
ALTER TABLE customers ADD PARTITION (
    PARTITION p_central VALUES IN (21, 22, 23)
);

-- HASH 分区增加分区数量
ALTER TABLE logs ADD PARTITION PARTITIONS 2;  -- 增加 2 个分区

删除分区

-- 删除分区（同时删除数据）
ALTER TABLE orders DROP PARTITION p2022;

-- 删除分区但保留数据
ALTER TABLE orders TRUNCATE PARTITION p2022;

-- 减少 HASH 分区数量
ALTER TABLE logs COALESCE PARTITION 2;  -- 减少 2 个分区

重组分区

-- 将多个分区合并为一个
ALTER TABLE sales REORGANIZE PARTITION p202401, p202402 INTO (
    PARTITION p2024_q1 VALUES LESS THAN ('2024-04-01')
);

-- 将一个分区拆分为多个
ALTER TABLE sales REORGANIZE PARTITION pmax INTO (
    PARTITION p202404 VALUES LESS THAN ('2024-05-01'),
    PARTITION p202405 VALUES LESS THAN ('2024-06-01'),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

分区维护

-- 分析分区
ALTER TABLE orders ANALYZE PARTITION p2024;

-- 检查分区
ALTER TABLE orders CHECK PARTITION p2024;

-- 优化分区
ALTER TABLE orders OPTIMIZE PARTITION p2024;

-- 重建分区
ALTER TABLE orders REBUILD PARTITION p2024;

-- 修复分区
ALTER TABLE orders REPAIR PARTITION p2024;

交换分区

将分区与普通表交换数据：

-- 创建交换表（结构必须与分区表相同）
CREATE TABLE orders_2022 LIKE orders;
ALTER TABLE orders_2022 REMOVE PARTITIONING;

-- 交换分区数据
ALTER TABLE orders EXCHANGE PARTITION p2022 WITH TABLE orders_2022;

-- 用途：快速归档历史数据

分区查询

查看分区信息

-- 查看表的分区信息
SELECT 
    PARTITION_NAME,
    PARTITION_METHOD,
    PARTITION_EXPRESSION,
    PARTITION_DESCRIPTION,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'orders';

-- 查看特定分区
SELECT * FROM orders PARTITION(p2024);

分区裁剪

分区裁剪是 MySQL 的优化机制，只扫描相关分区：

-- 创建分区表
CREATE TABLE access_logs (
    id BIGINT AUTO_INCREMENT,
    access_time DATETIME NOT NULL,
    url VARCHAR(500),
    ip VARCHAR(45),
    PRIMARY KEY (id, access_time)
) PARTITION BY RANGE (TO_DAYS(access_time)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 查询会自动裁剪到相关分区
EXPLAIN SELECT * FROM access_logs 
WHERE access_time BETWEEN '2024-02-01' AND '2024-02-15';
-- 只扫描 p202402 分区

-- 验证分区裁剪
EXPLAIN SELECT * FROM access_logs 
WHERE access_time >= '2024-02-01' AND access_time < '2024-03-01'\G
-- 查看 partitions 列

强制指定分区

-- 强制扫描特定分区
SELECT * FROM orders PARTITION(p2024) WHERE customer_id = 100;

-- 多个分区
SELECT * FROM orders PARTITION(p2023, p2024) WHERE amount > 1000;

分区索引

本地索引（分区索引）

每个分区有独立的索引：

-- 默认情况下，分区表使用本地索引
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT,
    order_date DATE NOT NULL,
    customer_id INT,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, order_date),
    KEY idx_customer (customer_id)  -- 本地索引
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 每个分区都有独立的 idx_customer 索引

全局索引（MySQL 8.0.13+）

MySQL 8.0.13 支持全局索引：

-- 创建全局索引
CREATE TABLE orders_global (
    id BIGINT AUTO_INCREMENT,
    order_date DATE NOT NULL,
    customer_id INT,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 添加全局索引
ALTER TABLE orders_global ADD GLOBAL INDEX idx_customer (customer_id);

分区设计最佳实践

选择合适的分区键

-- 1. 选择查询常用的条件列
-- 如果经常按日期范围查询，使用日期分区
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

-- 2. 分区键应该是主键的一部分
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT,
    order_date DATE NOT NULL,
    ...
    PRIMARY KEY (id, order_date)  -- order_date 是分区键
);

-- 3. 考虑数据分布均匀性
-- HASH 分区适合均匀分布
-- RANGE 分区适合时间序列数据

分区数量建议

-- 分区数量建议：
-- 1. 不要创建过多分区（建议 < 100 个）
-- 2. 每个分区应该有足够数据量
-- 3. 考虑维护成本

-- 不好的设计：每天一个分区（一年 365 个分区）
CREATE TABLE daily_logs (
    ...
) PARTITION BY RANGE (TO_DAYS(log_date)) (
    PARTITION p20240101 VALUES LESS THAN (TO_DAYS('2024-01-02')),
    PARTITION p20240102 VALUES LESS THAN (TO_DAYS('2024-01-03')),
    ... -- 太多分区
);

-- 好的设计：每月一个分区
CREATE TABLE monthly_logs (
    ...
) PARTITION BY RANGE (TO_DAYS(log_date)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    ...
);

定期维护分区

-- 定期添加新分区
-- 每月初执行
ALTER TABLE orders ADD PARTITION (
    PARTITION p202501 VALUES LESS THAN (2026)
);

-- 定期删除旧分区
-- 保留最近 3 年数据
ALTER TABLE orders DROP PARTITION p2021;

-- 或者使用事件自动执行
CREATE EVENT add_partition_event
ON SCHEDULE EVERY 1 MONTH
STARTS '2024-12-01 00:00:00'
DO
    ALTER TABLE orders ADD PARTITION (
        PARTITION p_next VALUES LESS THAN (YEAR(NOW() + INTERVAL 1 MONTH))
    );

分区表与普通表转换

普通表转分区表

-- 1. 检查表结构
SHOW CREATE TABLE orders;

-- 2. 确保主键包含分区键
ALTER TABLE orders DROP PRIMARY KEY, ADD PRIMARY KEY (id, order_date);

-- 3. 转换为分区表
ALTER TABLE orders PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

分区表转普通表

-- 移除分区
ALTER TABLE orders REMOVE PARTITIONING;

-- 注意：这会合并所有分区数据

小结

本章我们学习了：

1. 分区概念：将大表数据分散存储的技术
2. 分区类型：RANGE、LIST、HASH、KEY 分区
3. 分区管理：添加、删除、重组分区
4. 分区查询：分区裁剪和特定分区查询
5. 分区设计：分区键选择和最佳实践

练习

1. 创建一个按月分 RANGE 分区的日志表
2. 练习添加和删除分区操作
3. 使用 EXPLAIN 验证分区裁剪效果
4. 将历史数据分区交换到归档表

参考资源

• MySQL 官方文档 - 分区
• MySQL 官方文档 - 分区类型
• MySQL 官方文档 - 分区管理