2. 分区表创建与管理：创建分区表的DDL语法、添加/删除/合并/拆分分区、分区索引管理

好，咱们直接进入正题。分区表这东西，说白了就是把一张大表拆成多个小表来管。我刚开始接触分区时也觉得麻烦，后来发现，只要掌握了DDL语法和几个核心操作，其实就那么回事。

2.1 创建分区表：DDL语法详解

创建分区表，核心就是告诉数据库：按什么规则拆，拆成几块。我习惯先想清楚分区键，再动手写SQL。

基本语法结构：

CREATE TABLE 表名 (
    列名1 数据类型,
    列名2 数据类型,
    ...
) 
PARTITION BY RANGE (分区键) (
    PARTITION 分区名1 VALUES LESS THAN (值1),
    PARTITION 分区名2 VALUES LESS THAN (值2),
    PARTITION 分区名N VALUES LESS THAN (MAXVALUE)
);

举个例子，我做过一个订单表，按月份分区：

CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    customer_id INT,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date) * 100 + MONTH(order_date)) (
    PARTITION p202301 VALUES LESS THAN (202302),
    PARTITION p202302 VALUES LESS THAN (202303),
    PARTITION p202303 VALUES LESS THAN (202304),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

嗯，这里要注意：分区键的选择很关键。我见过有人用主键做分区键，结果查询效率反而下降了。你想想看，如果查询条件里不带分区键，数据库就得扫描所有分区，那分区还有什么意义？

分区类型速查表：

分区类型 适用场景 我常用的场景
RANGE分区 按时间、数值范围 订单表、日志表
LIST分区 按离散值（地区、状态） 区域数据表
HASH分区 均匀分布数据 用户表、流水表
KEY分区 类似HASH，但由MySQL内部处理 主键自增场景

2.2 分区管理：添加、删除、合并、拆分

表建好了，但业务是活的。数据量大了要加分区，旧数据要删分区，分区太碎要合并，分区太大要拆分。这些操作我都踩过坑，一个一个说。

2.2.1 添加分区

最常用的操作。比如到了2023年4月，得加个新分区：

ALTER TABLE orders ADD PARTITION (
    PARTITION p202304 VALUES LESS THAN (202305)
);

我曾经犯过一个错：直接加分区，忘了检查MAXVALUE分区是否存在。如果存在MAXVALUE分区，就不能直接ADD，得先处理它。

小技巧：我习惯在建表时就预留一个MAXVALUE分区，然后定期用REORGANIZE来拆分。这样不会因为忘记加分区导致数据插入失败。

2.2.2 删除分区

删除分区比DELETE快得多，因为它是直接干掉整个数据文件：

ALTER TABLE orders DROP PARTITION p202301;

注意：DROP PARTITION会同时删除分区数据和分区定义。如果只是想清空数据但保留分区结构，用TRUNCATE PARTITION：

ALTER TABLE orders TRUNCATE PARTITION p202301;

2.2.3 合并分区

分区太多也不好，管理成本高。合并两个相邻分区：

ALTER TABLE orders REORGANIZE PARTITION p202301, p202302 INTO (
    PARTITION p2023_q1 VALUES LESS THAN (202303)
);

嗯，这里有个坑：合并的分区必须是连续的。你不能把p202301和p202303合并，中间还隔着p202302，那不行。

2.2.4 拆分分区

分区太大，查询性能下降，就得拆。比如把p_future拆成两个：

ALTER TABLE orders REORGANIZE PARTITION p_future INTO (
    PARTITION p202304 VALUES LESS THAN (202305),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

警告：拆分和合并操作会锁表。我在生产环境做这类操作时，都会选在业务低峰期，并且先测试一遍。有一次我半夜操作，结果数据量太大，跑了40分钟才完成，差点耽误第二天的业务。

2.3 分区索引管理

分区索引有两种：全局索引和本地索引。说白了，本地索引是每个分区自己管自己的索引，全局索引是所有分区共用一个索引树。

本地索引（Local Index）：

CREATE INDEX idx_order_date ON orders(order_date) LOCAL;

本地索引的好处是：分区操作（DROP、TRUNCATE）不影响其他分区的索引。我一般默认用本地索引，省心。

全局索引（Global Index）：

CREATE INDEX idx_order_date ON orders(order_date) GLOBAL;

全局索引的查询效率更高，但维护成本也高。每次分区操作，全局索引都得重建。我记得有一次做分区合并，忘了重建全局索引，结果查询走了全表扫描，慢得让人抓狂。

我的选择建议：

  • 查询条件经常带分区键 → 本地索引就够了
  • 查询条件不带分区键，但需要唯一约束 → 全局索引
  • 分区操作频繁 → 本地索引，避免重建开销
  • 查询性能要求极高 → 全局索引，但要做好维护计划

2.4 知识体系总览

说了这么多，我画张图帮你理一理。分区表的核心就三块：怎么建、怎么管、怎么查。

分区表管理 创建分区表 管理分区 分区索引 RANGE LIST HASH KEY 添加 删除 合并 拆分 本地索引 全局索引 核心原则：分区键选好，索引类型选对，操作时机选准 —— 分区表用得好，DBA下班早

说实话，分区表这东西，用好了是神器，用不好是累赘。我见过太多人一上来就搞几十个分区，结果查询反而更慢。记住一个原则：分区不是越多越好，够用就行。

我的实战建议：

  • 单表数据量超过500万行，或者超过10GB，再考虑分区
  • 分区数量控制在50个以内，太多反而影响性能
  • 定期检查分区使用情况，及时清理无用分区
  • 索引和分区策略要一起设计，别等上线了再改

好了，这一章的内容就到这儿。分区表的创建、管理和索引，说白了就是三板斧：建表时选好分区键，日常维护做好增删改查，索引选对类型。把这些搞明白了，分区表这块基本就稳了。

可可精品资料库

← 上一章 📖 返回目录 下一章 →