数据库教程FGMT41‑MySQL性能优化之表分区管理

数据库教程FGMT41‑MySQL性能优化之表分区管理
## 前言
本套风哥教程面向DBA、数据库运维工程师、云计算运维、后端开发人员，完整讲解MySQL表分区全套知识，兼顾MySQL8.4与MySQL9.7两大版本，两套版本案例各占一半比重。风哥教程本文分为理论原理与实战操作两大模块，理论部分讲解分区概念、优缺点、限制条件、分区与分表差异、各类分区类型原理；实战部分基于主机`fgedu‑net‑cn1`、`fgedu‑net‑cn2`，硬件规格统一为**64G内存，8CPU**，数据根目录统一使用`/fgedudb`，数据库/实例名`fgedudb`，业务用户名`fgedu`，包含大量可直接复现SQL命令，覆盖建分区、新增、删除、重组、交换分区、子分区、分区元数据查看、故障排查。

风哥教程本文学习目标：掌握MySQL分区表设计规范，能够根据业务选择合适分区类型，独立完成分区表创建、日常运维维护，识别分区常见坑，理解分区裁剪原理，具备生产环境分区表落地与故障处理能力。

> 实操提示：所有SQL优先在测试环境执行，生产环境执行DDL前做好数据备份，评估锁表、元数据锁影响。
> 网上搜索风哥教程可以学习全套数据库教程

## 目录
1. MySQL表分区基础理论
1.1 什么是MySQL表分区
1.2 不使用分区表带来的业务问题
1.3 使用表分区带来的收益
1.4 MySQL表分区的硬性限制条件
1.5 子分区核心注意事项
1.6 逻辑分区表与物理分表的核心区别
1.7 MySQL8.4与MySQL9.7分区功能差异对比
2. MySQL分区类型原理详解
2.1 RANGE范围分区与RANGE COLUMNS范围列分区
2.2 LIST列表分区与LIST COLUMNS列表列分区
2.3 HASH哈希分区、LINEAR HASH线性哈希分区
2.4 KEY分区、LINEAR KEY线性KEY分区
2.5 子分区（复合分区）原理
2.6 分区数据存储位置、DATA DIRECTORY语法说明
2.7 分区裁剪（Partition Pruning）核心原理
3. MySQL8.4分区表实战操作案例
3.1 环境准备，数据库与账号初始化
3.2 RANGE、RANGE COLUMNS分区表创建实战
3.3 LIST、LIST COLUMNS分区表创建实战
3.4 HASH、KEY分区表创建实战
3.5 子分区复合分区表创建
3.6 分区元数据信息查看多种方式
3.7 添加分区、删除分区、TRUNCATE清空分区
3.8 REORGANIZE PARTITION重组分区、COALESCE PARTITION合并分区
3.9 重建分区、ANALYZE、OPTIMIZE、CHECK分区维护
3.10 EXCHANGE PARTITION交换分区完整实战
3.11 验证分区裁剪EXPLAIN PARTITIONS实战
4. MySQL9.7分区表实战操作案例
4.1 MySQL9.7环境初始化
4.2 RANGE COLUMNS按字符串、日期分区实战
4.3 LIST COLUMNS多列列表分区实战
4.4 9.7版本HASH/KEY分区实战
4.5 9.7子分区复合分区实战
4.6 9.7版本分区各类维护DDL操作
4.7 9.7交换分区实战，跨目录DATA DIRECTORY
4.8 9.7分区裁剪验证、分区统计信息更新
5. 分区表生产常见故障与坑点排查
5.1 主键唯一键不包含分区字段导致建表报错
5.2 查询没有触发分区裁剪，扫描全部分区
5.3 RANGE分区新增分区报错MAXVALUE陷阱
5.4 子分区语法错误、子分区不支持HASH再做子分区
5.5 EXCHANGE PARTITION交换分区失败常见原因
5.6 分区表alter table在线DDL锁表风险
5.7 分区数量过多导致性能下降
6. 风哥针对本文总结

## 1 MySQL表分区基础理论
> 风哥 itpux‑com

### 1.1 什么是MySQL表分区
MySQL表分区，是把一张逻辑大表，在数据库底层拆分成多个物理子片段，对外仍然呈现为一张完整数据表；上层SQL访问语法完全不变，数据库内核根据**分区键表达式**，自动把数据路由到对应的物理分区文件，对应用程序透明。

分区属于InnoDB存储引擎层能力，逻辑上是一张表，物理上由多个ibd分区文件组成；应用不需要修改业务SQL，只在建表或者alter table的时候定义分区规则。
逻辑表不变，物理拆分成多个独立分区，DML、DQL语法保持不变，这是分区与手动分表最大区别。

### 1.2 不使用分区表带来的业务问题
当业务单表数据量持续膨胀，达到千万、亿级数据量级，不做分区会遇到大量现实痛点：
1. **历史数据清理成本极高**：删除大量历史过期数据，执行delete会产生大量undo、redo日志，锁表，产生大量binlog，IO暴涨，业务卡顿；大批量delete之后还会产生大量碎片，需要optimize table，锁表时间很长。
2. **备份恢复效率低**：整张大表作为一个ibd文件，备份恢复必须处理全部数据，无法针对部分历史数据做单独备份恢复。
3. **查询性能退化**：单文件数据量巨大，索引体积膨胀，Buffer Cache缓存压力变大，大量冷数据与热数据混杂，缓存命中率下降。
4. **冷热数据无法隔离存储**：无法把历史冷数据放到低速存储，热数据放到高速SSD，全部数据只能在同一套存储介质。
5. **运维DDL代价巨大**：针对部分历史数据的维护操作，必须扫描整张大表，无法隔离操作范围。

> 网上搜索风哥教程可以学习全套数据库教程

### 1.3 使用表分区带来的收益
1. **历史数据快速清理**：使用`DROP PARTITION`直接丢弃过期历史分区，几乎不产生redo/undo日志，秒级删除海量历史数据，替代大批量delete删除。
2. **分区裁剪提升查询性能**：SQL where条件带上分区键，优化器触发分区裁剪，只扫描少数几个分区，跳过大量无关历史分区，减少IO扫描范围。
3. **冷热数据物理隔离**：通过`DATA DIRECTORY`语法，不同分区存放至不同磁盘目录，热数据SSD，冷数据归档低速磁盘，实现存储分层。
4. **运维粒度缩小**：optimize、analyze、check、truncate可以针对单个分区执行，不需要操作整张巨大逻辑表。
5. **交换分区快速数据迁移**：`EXCHANGE PARTITION`可以把普通表和分区直接交换元数据，实现秒级数据迁入迁出分区，适合数据归档、数据同步场景。
6. **备份粒度缩小**：可以针对个别分区做独立备份恢复，提升故障恢复效率。

### 1.4 MySQL表分区的硬性限制条件
1. **主键、唯一索引必须全部包含分区字段**。只要表存在主键或者任意唯一键，分区表达式里面用到的所有列，必须出现在每一个主键、唯一键字段集合里面。根源：MySQL分区引擎无法跨分区做唯一性校验，如果唯一键不包含分区字段，不同分区可以出现相同唯一值，破坏唯一性约束。
2. **InnoDB引擎才完整支持分区，MyISAM虽然语法支持，但生产不推荐**；NDB集群引擎有额外分区约束。
3. **不支持FULLTEXT全文索引、SPATIAL空间索引**，分区表上面不能创建全文索引、空间索引。
4. **临时表不能做分区**。
5. 分区表达式允许函数有限，仅支持官方允许的时间、数学函数，不支持存储过程、自定义函数、子查询作为分区表达式。
6. 单张分区表最大分区数量上限8192，分区不是越多越好，分区数量过多，元数据解析开销会上升。
7. TEXT、BLOB不能直接作为分区键；KEY分区类型例外。

> 风哥教程 113257174

### 1.5 子分区核心注意事项
子分区也就是复合分区，外层是一级分区，每一个一级分区再拆分成二级子分区。
硬性规则：
1. **只有RANGE、LIST类型可以做一级父分区；HASH、KEY不能作为父分区，不能再对子分区继续做子分区**。
2. 子分区只允许使用HASH / KEY类型，RANGE/LIST不能充当子分区。
3. 全部一级分区，子分区数量必须完全保持一致，不能部分分区2个子分区，另外分区4个子分区。
4. 主键、唯一键约束同样需要包含父分区字段+子分区字段。

### 1.7 MySQL8.4与MySQL9.7分区功能差异对比
> 上51CTO搜索风哥可以学习全套数据库教程

## 2 MySQL分区类型原理详解
### 2.1 RANGE范围分区与RANGE COLUMNS范围列分区
**RANGE分区**：根据表达式返回的整数值范围划分分区，`VALUES LESS THAN()`定义每个分区上限，适合时间、自增ID这类连续有序字段，最常用于按月份、按年份归档历史数据。
注意RANGE分区的值域不能重叠，分区范围连续递增；如果新增数据超过全部分区定义范围，直接报错，一般会增加MAXVALUE兜底分区。

**RANGE COLUMNS**：是RANGE的增强版本，**不需要写函数表达式，可以直接使用列本身，支持多列、日期、字符串，不需要转换成整数**，8.4/9.7强烈优先推荐RANGE COLUMNS，规避函数表达式带来的分区裁剪失效风险。

### 2.2 LIST列表分区与LIST COLUMNS列表列分区
LIST分区，每一个分区对应离散的枚举值集合，`VALUES IN (val1,val2)`，适合地域、业务类型、状态码这类离散有限枚举字段。
LIST COLUMNS增强：支持多列、字符串、日期，不需要表达式，多列元组匹配，不需要必须整数。
LIST分区如果插入不在任何list枚举集合内的值，直接报错，没有MAXVALUE兜底机制。

### 2.3 HASH哈希分区、LINEAR HASH线性哈希分区
HASH分区，根据分区键表达式哈希取模，均匀打散数据分布到各个分区，不关心数据范围，目标把数据均匀分散。
`LINEAR HASH`使用线性哈希算法，新增删除分区代价更低，但是数据分布均匀度会略差；普通HASH重分区会全部重新哈希数据。

### 2.4 KEY分区、LINEAR KEY线性KEY分区
KEY分区类似HASH，但是哈希算法由MySQL内部提供，**可以直接使用非整数列，不需要写表达式**；如果不指定列，默认使用主键列。
`LINEAR KEY`对应线性版本。
> 风哥数据库教程 itpux‑com

### 2.5 子分区（复合分区）原理
子分区，父分区RANGE/LIST，每个父分区下面再拆分成HASH或者KEY子分区。
例如按年份RANGE做一级分区，每一年的数据再按user_id HASH拆分成多个子分区。
物理存储：每一个子分区对应独立ibd文件。
注意：所有父分区子分区数量必须完全相等。

### 2.6 分区存储位置 DATA DIRECTORY语法说明
InnoDB分区表，默认所有分区ibd文件统一放在实例datadir数据库目录；
使用`DATA DIRECTORY=’/fgedudb/partition_archive’`，可以为单个分区指定独立存储目录，实现冷热数据分磁盘存放。
注意：目录必须mysql用户可读可写，需要开启innodb_file_per_table，不能用于共享表空间ibdata1。

### 2.7 分区裁剪（Partition Pruning）核心原理
分区裁剪是分区表性能收益的核心。
当SQL查询where条件带上分区键过滤条件，MySQL优化器解析条件，计算出只需要访问哪几个分区，直接跳过其余全部分区文件，减少IO读取。

**失效场景**：
1. where条件分区键包裹函数运算，例如`where YEAR(create_time)=’2026’`，分区键字段上做函数运算，优化器无法推导分区；推荐直接字段做范围比较。
2. where条件不带任何分区键过滤条件，必须扫描全部分区。
3. join关联条件，无法下推分区键过滤。

使用`EXPLAIN PARTITIONS SELECT …`可以查看执行计划的partitions字段，确认实际扫描哪些分区，验证裁剪是否生效。

## 3 MySQL8.4分区表实战操作案例
> 实验主机`fgedu‑net‑cn1`，MySQL8.4，硬件规格64G内存8CPU；实例数据目录`/fgedudb/fgedudb`，数据库`fgedudb`，账号`fgedu`。
登录数据库：
“`bash
mysql -S /fgedudb/fgedudb/mysql.sock
“`
### 3.1 环境准备，数据库与账号初始化
“`sql
CREATE DATABASE IF NOT EXISTS fgedudb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE fgedudb;
CREATE USER IF NOT EXISTS ‘fgedu’@’%’ IDENTIFIED BY ‘Fg@123456’;
GRANT ALL PRIVILEGES ON fgedudb.* TO ‘fgedu’@’%’;
FLUSH PRIVILEGES;
“`

### 3.2 RANGE、RANGE COLUMNS分区表创建实战
#### RANGE分区（按年份函数）
“`sql
CREATE TABLE `operate_log_range` (
id BIGINT NOT NULL AUTO_INCREMENT,
operate_user VARCHAR(64),
operate_content TEXT,
create_time DATETIME NOT NULL,
PRIMARY KEY(id,YEAR(create_time))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (YEAR(create_time)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p_max VALUES LESS THAN MAXVALUE
);
“`
> 注意主键必须带上分区表达式字段YEAR(create_time)，否则8.4会报建表失败。

#### RANGE COLUMNS 推荐用法，直接使用datetime列
“`sql
CREATE TABLE `operate_log_rc` (
id BIGINT NOT NULL AUTO_INCREMENT,
operate_user VARCHAR(64),
operate_content TEXT,
create_time DATETIME NOT NULL,
PRIMARY KEY(id,create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE COLUMNS(create_time) (
PARTITION p2023 VALUES LESS THAN (‘2024-01-01 00:00:00’),
PARTITION p2024 VALUES LESS THAN (‘2025-01-01 00:00:00’),
PARTITION p2025 VALUES LESS THAN (‘2026-01-01 00:00:00’),
PARTITION p_max VALUES LESS THAN MAXVALUE
);
“`
插入测试数据：
“`sql
INSERT INTO operate_log_rc(operate_user,operate_content,create_time) VALUES
(‘user01′,’login’,’2023‑05‑10 10:20:00′),
(‘user02′,’modify’,’2024‑08‑11 14:30:00′),
(‘user03′,’logout’,’2025‑02‑03 09:10:00′);
“`

> 网上搜索风哥教程可以学习全套数据库教程

### 3.3 LIST、LIST COLUMNS分区表创建实战
LIST分区，按业务状态枚举：
“`sql
CREATE TABLE `biz_order_list` (
order_id BIGINT NOT NULL AUTO_INCREMENT,
user_name VARCHAR(32),
order_status TINYINT NOT NULL COMMENT ‘1待支付 2已支付 3已取消 4已退款’,
amount DECIMAL(18,2),
PRIMARY KEY(order_id,order_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LIST(order_status)(
PARTITION p_pay_wait VALUES IN (1),
PARTITION p_payed VALUES IN (2),
PARTITION p_cancel VALUES IN (3,4)
);
“`

LIST COLUMNS多列字符串示例：
“`sql
CREATE TABLE `biz_order_lc` (
order_id BIGINT NOT NULL AUTO_INCREMENT,
region_code VARCHAR(16) NOT NULL,
order_status VARCHAR(16) NOT NULL,
amount DECIMAL(18,2),
PRIMARY KEY(order_id,region_code,order_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LIST COLUMNS(region_code,order_status)(
PARTITION p_shenzhen VALUES IN ((‘SZ’,’PAY’),(‘SZ’,’CANCEL’)),
PARTITION p_beijing VALUES IN ((‘BJ’,’PAY’),(‘BJ’,’CANCEL’))
);
“`

### 3.4 HASH、KEY分区表创建实战
普通HASH分区：
“`sql
CREATE TABLE `user_hash` (
uid BIGINT NOT NULL AUTO_INCREMENT,
nickname VARCHAR(64),
phone VARCHAR(20),
PRIMARY KEY(uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY HASH(uid)
PARTITIONS 4;
“`

LINEAR HASH线性HASH：
“`sql
CREATE TABLE `user_lhash` (
uid BIGINT NOT NULL AUTO_INCREMENT,
nickname VARCHAR(64),
PRIMARY KEY(uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LINEAR HASH(uid)
PARTITIONS 4;
“`

KEY分区，不指定列默认主键：
“`sql
CREATE TABLE `user_key` (
uid BIGINT NOT NULL AUTO_INCREMENT,
nickname VARCHAR(64),
PRIMARY KEY(uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY KEY()
PARTITIONS 4;
“`

### 3.5 子分区复合分区表创建
RANGE作为父分区，子分区HASH：
“`sql
CREATE TABLE `log_subpart` (
id BIGINT NOT NULL AUTO_INCREMENT,
user_id BIGINT NOT NULL,
msg TEXT,
create_time DATETIME NOT NULL,
PRIMARY KEY(id,create_time,user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE COLUMNS(create_time)
SUBPARTITION BY HASH(user_id) SUBPARTITIONS 2
(
PARTITION p2024 VALUES LESS THAN (‘2025‑01‑01’),
PARTITION p2025 VALUES LESS THAN (‘2026‑01‑01′),
PARTITION p_max VALUES LESS THAN MAXVALUE
);
“`

### 3.6 分区元数据信息查看多种方式
查询information_schema.partitions系统表，最常用：
“`sql
SELECT
PARTITION_NAME,SUBPARTITION_NAME,TABLE_ROWS,PARTITION_EXPRESSION,SUBPARTITION_EXPRESSION
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA=’fgedudb’ AND TABLE_NAME=’operate_log_rc’;
“`

show create table查看完整分区定义：
“`sql
SHOW CREATE TABLE operate_log_rc;
“`

验证分区裁剪，EXPLAIN PARTITIONS：
“`sql
EXPLAIN PARTITIONS SELECT * FROM operate_log_rc WHERE create_time>=’2024‑01‑01′ AND create_time <‘2025‑01‑01’;
“`

### 3.7 添加分区、删除分区、TRUNCATE清空分区
> ⚠️DROP PARTITION会直接删除分区内全部数据，谨慎操作。

给RANGE COLUMNS表新增分区：
“`sql
ALTER TABLE operate_log_rc ADD PARTITION (
PARTITION p2026 VALUES LESS THAN (‘2027‑01‑01 00:00:00’)
);
“`

删除分区：
“`sql
ALTER TABLE operate_log_rc DROP PARTITION p2023;
“`

清空分区内全部数据，保留分区定义：
“`sql
ALTER TABLE operate_log_rc TRUNCATE PARTITION p2024;
“`

### 3.8 REORGANIZE PARTITION重组分区、COALESCE PARTITION合并分区
REORGANIZE PARTITION：拆分RANGE/LIST分区，把一个大分区拆成多个小分区，数据自动迁移。
“`sql
ALTER TABLE operate_log_rc REORGANIZE PARTITION p_max INTO (
PARTITION p2027 VALUES LESS THAN (‘2028‑01‑01’),
PARTITION p_future_max VALUES LESS THAN MAXVALUE
);
“`

COALESCE PARTITION：用于HASH/KEY分区，减少分区数量。例如4个hash分区缩减成2个：
“`sql
ALTER TABLE user_hash COALESCE PARTITION 2;
“`

### 3.9 重建分区、ANALYZE、OPTIMIZE、CHECK分区维护
更新分区统计信息，优化器依靠统计信息生成执行计划：
“`sql
ANALYZE TABLE operate_log_rc PARTITION (p2024,p2025);
“`

检查分区数据文件完整性：
“`sql
CHECK TABLE operate_log_rc PARTITION (p2024);
“`

OPTIMIZE，整理分区碎片：
“`sql
OPTIMIZE TABLE operate_log_rc PARTITION (p2024);
“`

### 3.10 EXCHANGE PARTITION交换分区完整实战
交换分区：把普通InnoDB表和分区表的一个分区交换元数据，几乎秒级，**两张表结构、字段、索引、主键必须完全一致**；数据不会拷贝，只是元数据交换。

1、准备普通表：
“`sql
CREATE TABLE tmp_operate_2026 LIKE operate_log_rc;
— 向临时表灌入数据
INSERT INTO tmp_operate_2026(operate_user,operate_content,create_time)
VALUES(‘u05′,’test’,’2026‑03‑01 11:00:00′);
“`

2、执行交换，把tmp_operate_2026数据交换进入p2026分区：
“`sql
ALTER TABLE operate_log_rc EXCHANGE PARTITION p2026 WITH TABLE tmp_operate_2026;
“`

交换完成后，原来临时表变为空，分区p2026拥有这批数据。

> 风哥教程 113257174

## 4 MySQL9.7分区表实战操作案例
> 实验主机`fgedu‑net‑cn2`，MySQL9.7，硬件规格64G内存8CPU；实例数据目录`/fgedudb/fgedudb`，数据库`fgedudb`，账号`fgedu`。
MySQL9.7分区语法大体兼容8.4，报错提示更加完善，部分DDL支持更多inplace算法。

登录数据库：
“`bash
mysql -S /fgedudb/fgedudb/mysql.sock
“`

### 4.1 MySQL9.7环境初始化
“`sql
CREATE DATABASE IF NOT EXISTS fgedudb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE fgedudb;
CREATE USER IF NOT EXISTS ‘fgedu’@’%’ IDENTIFIED BY ‘Fg@123456’;
GRANT ALL PRIVILEGES ON fgedudb.* TO ‘fgedu’@’%’;
FLUSH PRIVILEGES;
“`

### 4.2 RANGE COLUMNS按字符串、日期分区实战
9.7 RANGE COLUMNS支持字符串、datetime直接做分区键，不需要函数。
“`sql
CREATE TABLE `order_97_rc` (
order_id BIGINT NOT NULL AUTO_INCREMENT,
order_no VARCHAR(48),
create_dt DATETIME NOT NULL,
city_code VARCHAR(24),
amount DECIMAL(18,2),
PRIMARY KEY(order_id,create_dt,city_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE COLUMNS(create_dt,city_code)(
PARTITION p2024_sz VALUES LESS THAN (‘2025‑01‑01′,’SZ’),
PARTITION p2024_bj VALUES LESS THAN (‘2025‑01‑01′,’ZZ’),
PARTITION p_future VALUES LESS THAN MAXVALUE
);

INSERT INTO order_97_rc(order_no,create_dt,city_code,amount) VALUES
(‘ORD97001′,’2024‑06‑01 08:30:00′,’SZ’,120.50),
(‘ORD97002′,’2024‑07‑02 09:20:00′,’BJ’,88.00);
“`

### 4.3 LIST COLUMNS多列列表分区实战
“`sql
CREATE TABLE `biz_97_lc` (
id BIGINT NOT NULL AUTO_INCREMENT,
prov_code VARCHAR(16),
pay_type VARCHAR(16),
cnt INT,
PRIMARY KEY(id,prov_code,pay_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LIST COLUMNS(prov_code,pay_type)(
PARTITION p_gd VALUES IN ((‘GD’,’ALI’),(‘GD’,’WX’)),
PARTITION p_js VALUES IN ((‘JS’,’ALI’),(‘JS’,’WX’))
);
“`

### 4.4 9.7版本HASH/KEY分区实战
“`sql
CREATE TABLE `user_97_hash` (
uid BIGINT NOT NULL AUTO_INCREMENT,
uname VARCHAR(64),
PRIMARY KEY(uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY HASH(uid)
PARTITIONS 4;
“`

### 4.5 9.7子分区复合分区实战
RANGE COLUMNS父分区，子分区KEY：
“`sql
CREATE TABLE `log_97_sub` (
id BIGINT NOT NULL AUTO_INCREMENT,
uid BIGINT NOT NULL,
msg_content TEXT,
ct DATETIME NOT NULL,
PRIMARY KEY(id,ct,uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE COLUMNS(ct)
SUBPARTITION BY KEY(uid) SUBPARTITIONS 2
(
PARTITION p2024 VALUES LESS THAN (‘2025‑01‑01’),
PARTITION p2025 VALUES LESS THAN (‘2026‑01‑01’),
PARTITION p_max VALUES LESS THAN MAXVALUE
);
“`

### 4.6 9.7版本分区各类维护DDL操作
新增分区：
“`sql
ALTER TABLE order_97_rc ADD PARTITION (
PARTITION p2025_sz VALUES LESS THAN (‘2026‑01‑01′,’SZ’)
);
“`

重组分区：
“`sql
ALTER TABLE order_97_rc REORGANIZE PARTITION p_future INTO (
PARTITION p2025_rest VALUES LESS THAN (‘2026‑01‑01′,’ZZ’),
PARTITION p_97_max VALUES LESS THAN MAXVALUE
);
“`

删除分区：
“`sql
ALTER TABLE order_97_rc DROP PARTITION p2024_sz;
“`

truncate清空分区：
“`sql
ALTER TABLE order_97_rc TRUNCATE PARTITION p2024_bj;
“`

hash分区合并：
“`sql
ALTER TABLE user_97_hash COALESCE PARTITION 2;
“`

统计信息、完整性检查：
“`sql
ANALYZE TABLE order_97_rc PARTITION(p2024_bj);
CHECK TABLE order_97_rc PARTITION(p2024_bj);
“`

### 4.7 9.7交换分区实战，跨目录DATA DIRECTORY
普通临时表：
“`sql
CREATE TABLE tmp_97_order LIKE order_97_rc;
INSERT INTO tmp_97_order(order_no,create_dt,city_code,amount)
VALUES(‘ORD97099′,’2025‑04‑05 10:10:00′,’SZ’,233.00);
“`

执行交换分区：
“`sql
ALTER TABLE order_97_rc EXCHANGE PARTITION p2025_sz WITH TABLE tmp_97_order;
“`

创建带独立存储目录分区示例，9.7对目录权限校验严格，目录提前创建，mysql属主：
“`sql
mkdir -p /fgedudb/part_archive_97
chown mysql:mysql /fgedudb/part_archive_97
“`
“`sql
ALTER TABLE order_97_rc ADD PARTITION (
PARTITION p_archive_97 VALUES LESS THAN (‘2028‑01‑01′,’ZZ’)
DATA DIRECTORY=’/fgedudb/part_archive_97′
);
“`

> 风哥数据库教程 itpux‑com

### 4.8 9.7分区裁剪验证、分区统计信息更新
“`sql
EXPLAIN PARTITIONS SELECT * FROM order_97_rc WHERE create_dt>=’2024‑01‑01′ AND create_dt <‘2025‑01‑01′;

SELECT PARTITION_NAME,TABLE_ROWS FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA=’fgedudb’ AND TABLE_NAME=’order_97_rc’;
“`

## 5 分区表生产常见故障与坑点排查
### 5.1 主键唯一键不包含分区字段导致建表报错
**现象**：创建分区表报 `A PRIMARY KEY must include all columns in the table’s partitioning function`。
**根因**：主键、唯一索引里面没有包含分区表达式全部字段，MySQL分区引擎无法跨分区校验唯一性。
**解决方案**：修改主键/唯一键，把分区字段加入主键；业务允许情况下删除唯一约束。

### 5.2 查询没有触发分区裁剪，扫描全部分区
排查步骤：
1. 使用`EXPLAIN PARTITIONS`查看partitions字段，如果列出全部分区，代表裁剪失效。
2. 检查where条件，分区字段外面是否包裹函数运算，例如`WHERE YEAR(create_time)=’2025’`，改为直接字段范围比较`create_time >= ‘2025‑01‑01’ AND create_time < ‘2026‑01‑01’`。
3. 确认SQL确实携带分区键过滤条件；完全不带分区键条件，必然扫描全部分区。
4. 确认统计信息准确，执行`ANALYZE TABLE`更新分区统计。

### 5.3 RANGE分区新增分区报错MAXVALUE陷阱
现象：已经定义`PARTITION p_max VALUES LESS THAN MAXVALUE`，此时直接`ADD PARTITION`会报错。
原因：MAXVALUE分区已经接纳全部值域，不能直接add；需要使用`REORGANIZE PARTITION`拆分MAXVALUE分区，拆分出新分区，保留新的MAXVALUE兜底分区。

### 5.4 子分区语法错误、子分区不支持HASH再做子分区
**坑1**：HASH、KEY不能作为一级父分区，不能对子分区继续嵌套子分区；只有RANGE、LIST允许作为父分区。
**坑2**：各个父分区子分区数量必须全部相同，不能有的2个子分区、有的4个子分区。

### 5.5 EXCHANGE PARTITION交换分区失败常见原因
1. 两张表结构不一致：字段、字段类型、索引、主键、字符集、排序规则不一致。
2. 临时表里面存在数据，但是分区定义不允许容纳这批数据，数据值不在分区值域范围内。
3. 包含FULLTEXT、SPATIAL索引，分区表不支持。
4. 分区表使用DATA DIRECTORY跨目录，临时表没有对应配置。

### 5.6 分区表alter table在线DDL锁表风险
`ALTER TABLE … PARTITION BY …` 把普通大表直接改成分区表，**8.4/9.7会重建整张表，会锁表，数据量大生产严禁直接执行**。
> 生产落地建议：新建空分区表，业务双写，数据迁移完成之后rename切换表名；或者在备库完成改造，主备切换上线，避免线上锁表。

### 5.7 分区数量过多导致性能下降
单张表分区数量不要无限制创建，上限8192；分区数量几百上千，每次SQL解析、元数据加载开销会显著上升。
生产建议，按业务周期规划，按月/按年分区，不要按天产生几千个分区。

> 上51CTO搜索风哥可以学习全套数据库教程

## 6 风哥针对本文总结
风哥教程本文完整讲解MySQL表分区整套知识，兼顾MySQL8.4、MySQL9.7两个版本理论与完整实操，硬件环境统一64G内存8CPU，数据目录`/fgedudb`，数据库`fgedudb`。

核心要点梳理：
1. MySQL分区表逻辑一张表，底层拆分成多个物理分区文件，业务SQL基本不用修改；区分分区表和业务层手动分表，分区是数据库内核原生能力，手动分表属于业务/中间件层实现。
2. 分区类型分为RANGE、RANGE COLUMNS、LIST、LIST COLUMNS、HASH、KEY；**优先推荐COLUMNS系列分区，直接使用列，不写函数表达式，大幅降低分区裁剪失效风险**。子分区父分区只能是RANGE/LIST，子分区只能HASH/KEY。
3. 硬性约束：主键、所有唯一键必须包含分区表达式全部字段；分区表不能使用全文索引、空间索引；临时表不能分区。
4. 分区最大价值来自两点：①DROP PARTITION秒级清理海量历史过期数据，替代大批量delete；②分区裁剪，where条件携带分区键，扫描少量分区，减少IO。分区不是银弹，不是大表就必须分区，如果查询永远不带分区键条件，分区裁剪完全无法生效，分区不会带来性能收益。
5. 运维常用DDL：ADD新增分区、DROP删除分区（删除数据）、TRUNCATE PARTITION清空分区保留定义、REORGANIZE拆分分区、COALESCE合并HASH/KEY分区、EXCHANGE PARTITION交换分区元数据实现秒级迁移数据。使用`EXPLAIN PARTITIONS`验证分区裁剪效果，查询`information_schema.PARTITIONS`查看分区元数据。
6. 生产落地注意：不要直接alter table把千万级大表原地转换成分区表，会重建全表，锁表；推荐新建分区表，双写迁移，rename切换。RANGE分区存在MAXVALUE陷阱，有MAXVALUE不能直接ADD PARTITION，要用REORGANIZE拆分。控制分区数量，不要无限制疯狂新建分区，避免元数据性能退化。
7. MySQL8.4和MySQL9.7分区语法大部分兼容，9.7主要增强报错提示、部分DDL支持更多INPLACE在线算法，子分区元数据统计信息更加完善。
8. 生产上线分区表，前期做好测试：验证分区裁剪、模拟过期分区删除、模拟交换分区迁移，业务SQL全部做兼容性验证，上线之后定期维护分区，提前新增未来分区，避免新数据插入直接报错。

分区是运维优化手段，需要匹配业务访问模式，如果业务SQL绝大多数查询不带分区过滤条件，分区无法发挥收益，就不建议使用分区表。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

数据库教程FGMT41‑MySQL性能优化之表分区管理

相关推荐

联系我们