MySQL教程 / 第 130 节

第13章：分区表

提高大表查询性能的利器

13.1 分区表概述

13.1.1 什么是分区表？

定义：

将一个大表分成多个物理分区
逻辑上仍是一个表
提高查询性能和管理效率

优点：

✅ 提高查询性能（分区裁剪）
✅ 便于数据管理（删除旧分区）
✅ 提高并发性能

缺点：

❌ 增加复杂度
❌ 某些查询可能变慢

13.2 分区类型 ⭐⭐⭐⭐

13.2.1 RANGE分区（范围分区）

按范围分区：

-- 按年份分区
CREATE TABLE orders (
    id INT NOT NULL,
    user_id INT NOT NULL,
    amount DECIMAL(10,2),
    create_time DATE NOT NULL
)
PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_max VALUES LESS THAN MAXVALUE
);

-- 按ID范围分区
CREATE TABLE users (
    id INT NOT NULL,
    name VARCHAR(100),
    age INT
)
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (1000000),
    PARTITION p1 VALUES LESS THAN (2000000),
    PARTITION p2 VALUES LESS THAN (3000000),
    PARTITION p_max VALUES LESS THAN MAXVALUE
);

13.2.2 LIST分区（列表分区）

按列表分区：

-- 按地区分区
CREATE TABLE users (
    id INT NOT NULL,
    name VARCHAR(100),
    province VARCHAR(50)
)
PARTITION BY LIST COLUMNS(province) (
    PARTITION p_north VALUES IN ('北京', '天津', '河北'),
    PARTITION p_east VALUES IN ('上海', '江苏', '浙江'),
    PARTITION p_south VALUES IN ('广东', '福建', '海南'),
    PARTITION p_west VALUES IN ('四川', '重庆', '云南')
);

13.2.3 HASH分区（哈希分区）

按哈希分区：

-- 按用户ID哈希分区（4个分区）
CREATE TABLE orders (
    id INT NOT NULL,
    user_id INT NOT NULL,
    amount DECIMAL(10,2),
    create_time DATETIME
)
PARTITION BY HASH(user_id)
PARTITIONS 4;

-- 数据分布：
-- user_id % 4 = 0 → p0
-- user_id % 4 = 1 → p1
-- user_id % 4 = 2 → p2
-- user_id % 4 = 3 → p3

13.2.4 KEY分区

按KEY分区：

-- 类似HASH分区，但使用MySQL内部哈希函数
CREATE TABLE orders (
    id INT NOT NULL,
    user_id INT NOT NULL,
    amount DECIMAL(10,2)
)
PARTITION BY KEY(user_id)
PARTITIONS 4;

13.3 分区管理

13.3.1 查看分区

查看分区信息：

-- 查看分区
SELECT 
    PARTITION_NAME,
    PARTITION_EXPRESSION,
    PARTITION_DESCRIPTION,
    TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';

-- 查看分区定义
SHOW CREATE TABLE orders;

13.3.2 添加分区

添加RANGE分区：

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

添加HASH分区：

-- 增加分区数量（从4个增加到8个）
ALTER TABLE orders
ADD PARTITION PARTITIONS 4;

13.3.3 删除分区

删除分区：

-- 删除2021年分区（数据也会被删除）
ALTER TABLE orders
DROP PARTITION p2021;

-- 删除多个分区
ALTER TABLE orders
DROP PARTITION p2021, p2022;

13.3.4 合并分区

合并分区：

-- 合并p0和p1为p01
ALTER TABLE orders
REORGANIZE PARTITION p0, p1 INTO (
    PARTITION p01 VALUES LESS THAN (2000000)
);

13.3.5 拆分分区

拆分分区：

-- 拆分p_max为p2025和新的p_max
ALTER TABLE orders
REORGANIZE PARTITION p_max INTO (
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_max VALUES LESS THAN MAXVALUE
);

13.4 分区裁剪

什么是分区裁剪？

查询时只扫描相关分区
提高查询性能

示例：

-- 查询2023年的订单
SELECT * FROM orders WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';

-- 只扫描p2023分区
EXPLAIN PARTITIONS SELECT * FROM orders WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';

-- 输出：
-- partitions: p2023

13.5 分区表最佳实践

13.5.1 何时使用分区表

适用场景：

✅ 表非常大（>1000万行）
✅ 按时间查询（日志表、订单表）
✅ 需要定期删除旧数据
✅ 数据有明显的分区特征

不适用场景：

❌ 表不大（<1000万行）
❌ 查询不涉及分区键
❌ 需要频繁跨分区查询

13.5.2 分区键选择

原则：

选择查询中常用的字段
数据分布均匀
便于管理

示例：

-- ✅ 好的分区键：create_time（按时间查询多）
PARTITION BY RANGE (YEAR(create_time))

-- ❌ 不好的分区键：status（数据分布不均）
PARTITION BY LIST (status)

13.5.3 注意事项

限制：

分区键必须是主键或唯一索引的一部分
最多1024个分区
不支持外键
不支持全文索引

示例：

-- ❌ 错误：分区键不是主键的一部分
CREATE TABLE orders (
    id INT PRIMARY KEY,
    create_time DATE
)
PARTITION BY RANGE (YEAR(create_time)) (...);

-- ✅ 正确：分区键是主键的一部分
CREATE TABLE orders (
    id INT,
    create_time DATE,
    PRIMARY KEY (id, create_time)
)
PARTITION BY RANGE (YEAR(create_time)) (...);

13.6 分区表实战案例

案例：日志表分区

-- 创建日志表（按月分区）
CREATE TABLE access_logs (
    id BIGINT NOT NULL AUTO_INCREMENT,
    user_id INT,
    url VARCHAR(500),
    ip VARCHAR(50),
    create_time DATETIME NOT NULL,
    PRIMARY KEY (id, create_time)
)
PARTITION BY RANGE (TO_DAYS(create_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 p_max VALUES LESS THAN MAXVALUE
);

-- 每月添加新分区
ALTER TABLE access_logs
REORGANIZE PARTITION p_max INTO (
    PARTITION p202404 VALUES LESS THAN (TO_DAYS('2024-05-01')),
    PARTITION p_max VALUES LESS THAN MAXVALUE
);

-- 删除旧分区（删除3个月前的数据）
ALTER TABLE access_logs
DROP PARTITION p202401;

13.7 本章总结

本章学习内容：

✅ 分区表概述
✅ 分区类型（RANGE、LIST、HASH、KEY）⭐⭐⭐⭐
✅ 分区管理（添加、删除、合并、拆分）
✅ 分区裁剪
✅ 分区表最佳实践

重点掌握：

RANGE分区：按范围（时间、ID）
HASH分区：数据均匀分布
分区裁剪：只扫描相关分区
分区键必须是主键的一部分

分区类型：

RANGE：按范围（时间、ID）
LIST：按列表（地区、状态）
HASH：按哈希（均匀分布）
KEY：类似HASH

面试重点：

什么是分区表
分区类型有哪些
何时使用分区表
分区键的选择原则
分区表的限制

下一章预告： 字符集与排序规则

练习题

什么是分区表？有什么优点？
分区类型有哪些？
RANGE分区和HASH分区有什么区别？
如何创建按年份分区的表？
如何添加和删除分区？
什么是分区裁剪？
何时使用分区表？
分区键有什么限制？
分区表最多有多少个分区？
实战：创建一个日志表并按月分区

继续学习： 第14章：字符集与排序规则