MySQL分区表技术：管理海量数据的利器
分区表概述
分区表是将一张逻辑上的大表，按照某种规则分割成多个物理上的小表存储。
分区的优势：
1. 提升查询性能（分区裁剪，只扫描相关分区）
2. 便于数据管理（直接删除旧分区代替DELETE）
3. 分区数据可以分散到不同磁盘
4. 提升维护效率（只需对单个分区做OPTIMIZE等操作）
适用场景：单表数据量超过千万行，且有明显的分区键（时间、地区等）。
注意：分区表不是万能的，对于随机写入的OLTP场景效果有限，更适合OLAP/归档场景。
RANGE分区
RANGE分区按照列值的范围来分区，最常用于时间字段。
-- 按年份分区
CREATE TABLE orders (
id BIGINT NOT NULL AUTO_INCREMENT,
user_id INT NOT NULL,
amount DECIMAL(10,2),
create_time DATETIME NOT NULL,
PRIMARY KEY (id, create_time) -- 分区键必须包含在主键中
) PARTITION BY RANGE (YEAR(create_time)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
-- 查询时自动分区裁剪
SELECT * FROM orders WHERE YEAR(create_time)=2023; -- 只扫描p2023分区
LIST分区
LIST分区按照列值的离散集合来分区，适合按类别、地区等分区。
-- 按地区分区
CREATE TABLE sales (
id BIGINT NOT NULL,
region_id INT NOT NULL,
amount DECIMAL(10,2),
PRIMARY KEY (id, region_id)
) PARTITION BY LIST (region_id) (
PARTITION p_north VALUES IN (1, 2, 3, 4), -- 华北地区
PARTITION p_south VALUES IN (5, 6, 7, 8), -- 华南地区
PARTITION p_east VALUES IN (9, 10, 11), -- 华东地区
PARTITION p_west VALUES IN (12, 13, 14) -- 华西地区
);
注意：插入的值必须在某个LIST中，否则报错。使用LIST COLUMNS可以支持多列和字符串。
HASH分区和KEY分区
HASH分区通过对分区键取模，将数据均匀分散到指定数量的分区。
-- HASH分区（基于表达式）
CREATE TABLE users (
id INT NOT NULL,
name VARCHAR(50),
PRIMARY KEY (id)
) PARTITION BY HASH(id)
PARTITIONS 8;
-- KEY分区（MySQL内部HASH函数，支持多列）
CREATE TABLE sessions (
session_id VARCHAR(32) NOT NULL,
user_id INT NOT NULL,
data TEXT,
PRIMARY KEY (session_id)
) PARTITION BY KEY(session_id)
PARTITIONS 16;
适用场景：数据无明显范围特征，需要均匀分布。
缺点：无法进行分区裁剪（每次查询需扫描所有分区）。
分区管理操作
-- 添加分区
ALTER TABLE orders ADD PARTITION (
PARTITION p2025 VALUES LESS THAN (2026)
);
-- 删除分区（同时删除该分区数据！）
ALTER TABLE orders DROP PARTITION p2022;
-- 清空分区数据（保留分区结构）
ALTER TABLE orders TRUNCATE PARTITION p2022;
-- 重组分区（合并）
ALTER TABLE orders REORGANIZE PARTITION p2024, p_future INTO (
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
-- 查看分区信息
SELECT * FROM information_schema.PARTITIONS
WHERE TABLE_NAME='orders';
分区表的限制与注意事项
MySQL分区表的主要限制：
1. 分区键必须包含在所有唯一键（包括主键）中
2. 分区表不支持外键
3. 全文索引在分区表上不可用
4. 最多支持1024个分区（MySQL 8.0提升至8192）
5. 分区表的查询必须包含分区键才能利用分区裁剪
常见误区：
- 分区不等于索引，两者结合使用效果更佳
- 分区过多反而影响性能（DDL操作更慢）
- 查询条件不包含分区键时会全分区扫描
建议：分区数量控制在100以内，结合业务查询模式设计分区策略。
分区表最佳实践
实践1：时间分区+自动轮转
-- 每月创建新分区，删除12个月前的分区
-- 可通过定时任务自动执行，实现数据自动老化
实践2：利用分区做数据归档
-- 业务数据在主表（分区表），定期将旧分区数据
-- 交换（EXCHANGE PARTITION）到归档表
ALTER TABLE orders EXCHANGE PARTITION p2022 WITH TABLE orders_archive;
实践3：分区+覆盖索引
-- 在分区表的常用查询列上建立覆盖索引
-- 查询既可利用分区裁剪又可利用索引
实践4：监控分区大小
SELECT PARTITION_NAME, TABLE_ROWS,
DATA_LENGTH/1024/1024 AS data_mb
FROM information_schema.PARTITIONS
WHERE TABLE_NAME='orders'
ORDER BY PARTITION_ORDINAL_POSITION;