第13章：分区表
第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章：字符集与排序规则