数据库分区表完全指南:从一次"分了区查询却没变快"看懂为什么分区键才是根本

2022 年我接手一个订单系统的查询优化——核心是一张 orders 表,几年下来积了好几亿行,各种按时间范围查订单的页面越来越慢,慢的甚至要十几秒。有人提醒我:这么大的表,该上分区表了。我一听觉得有道理,第一版做得很顺手:我把 orders 表改造成按 order_id 取模分成 16 个分区,心想几亿行的大表被拆成 16 份,每份只剩两千多万行,查询要扫的数据少了十几倍,这查询不就快了嘛——这优化稳了。可改造上线之后,一串问题冒了出来。第一种最先把我打懵:表明明分了区,那些慢查询的耗时却纹丝不动,还是十几秒,跟没分区时一模一样。第二种最难缠:我去看执行计划,发现几乎每一个查询,数据库都老老实实地把 16 个分区全扫了一遍——分了区,却等于没分。第三种最头疼:我又换了个分区键,改成按下单地区分区,结果数据严重不均,光"华东"一个分区就装了六成的订单,其他十几个分区加起来才四成,那个大分区慢得和原来的大表没区别。第四种最莫名其妙:我想用分区表顺便解决老数据归档——删掉两年前的订单,可 DELETE 那条老数据的语句,跑得还是和分区前一样慢、一样锁表。我盯着这一连串问题想了很久,才彻底想明白:第一版错在一个根本的认知上。我以为分区表就是把一张物理大表拆成好几张小表,查询的时候数据库会自动只到相关的那张小表里去找,所以表只要一分区,数据量被摊薄了,查询自然就快了——分区表就是一个"分了就快"的加速开关。可这个认知是错的。分区表本身不会让任何查询变快。它能带来的加速,完全来自一个叫"分区裁剪"(partition pruning)的机制:只有当一个查询的 WHERE 条件,能让数据库在执行前就推断出"我要的数据只可能落在某几个分区里",它才会跳过其余分区、只扫这几个。而分区裁剪能不能发生,死死地取决于两件事:分区键选得对不对、查询条件用没用上这个分区键。我第一版按 order_id 取模分区,可页面查询全是按 created_at 时间范围查的,查询条件里根本没有 order_id——数据库无法从"created_at 在某个范围"推断出"数据在哪个 order_id 取模分区",于是只能 16 个分区全扫。分区裁剪一次都没发生,分区表就退化成了一张多了 16 个碎片、却没有任何加速的普通大表。所以用好分区表,根上不是"把表拆开"这一个动作,而是一整套设计:要照着最高频的查询条件去选分区键、要让查询真的能命中分区裁剪、要选对分区方式、要防住数据倾斜、要利用 DROP PARTITION 这个分区表真正的杀手锏去做归档。本文从头梳理:为什么"分了区"查询却没变快,分区键到底该怎么选,RANGE、LIST、HASH 三种分区方式各适合什么,数据倾斜怎么防,以及一些把分区表做扎实要避开的工程坑。

问题背景

先把分区表这件事说清楚。分区表(partitioning)是数据库的一个特性:你在逻辑上还是面对"一张表",但数据库在物理上,把这张表的数据按你指定的规则(分区键 + 分区方式)切分成若干个分区,每个分区是一段独立存储的数据。你照常对这张逻辑表做增删改查,数据库自己决定一行数据落到哪个分区、一个查询要碰哪些分区。

错误认知是:分区表就是把大表拆小,数据量摊薄了查询自然就快。真相是:分区表本身不提供任何加速,它提供的是"分区裁剪"这个能力——而这个能力要被真正用上,前提是分区键和查询条件精确匹配。把这一点摊开,第一版的几类问题就都能解释了:

• 分了区查询没变快:查询条件命不中分区键,分区裁剪没发生,数据库照样全分区扫。
• 每个查询都扫全部分区:按 order_id 分区、按 created_at 查询,两者对不上,数据库无法裁剪。
• 某个分区奇大:分区键的取值分布不均匀,导致数据倾斜,大分区拖慢一切。
• 删老数据还是慢:用 DELETE 删数据,根本没用到分区表"按分区整块丢弃"的能力。

所以让分区表真正发挥作用,核心不是"把表分开",而是一整套设计工程:照查询选分区键、确认分区裁剪生效、选对分区方式、防住倾斜、用 DROP PARTITION 做归档。下面六节,就从第一版"分了就快"的想当然讲起。

一、为什么"分了区"查询却没变快

第一版我的改造,就是把几亿行的 orders 表,按 order_id 取模分成 16 个分区。

-- 反面教材:按 order_id 取模分区,但页面查询全是按时间查的

CREATE TABLE orders (
    order_id    BIGINT NOT NULL,
    user_id     BIGINT NOT NULL,
    created_at  DATETIME NOT NULL,
    region      VARCHAR(20),
    amount      DECIMAL(10,2),
    PRIMARY KEY (order_id)
)
PARTITION BY HASH(order_id)      -- 分区键是 order_id
PARTITIONS 16;                   -- 切成 16 个分区

-- 我以为:几亿行拆成 16 份,每份两千多万,查询要扫的少了十几倍。
-- 可页面上的查询,长这样 ——
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';
-- 查询条件里只有 created_at,没有 order_id。

问题就藏在最后这条查询里。数据库要做分区裁剪,得能从查询条件推断出"数据在哪几个分区"。可这张表是按 HASH(order_id) 分区的,一行数据落在哪个分区,由 order_id 决定;而查询条件给的是 created_at 的范围——数据库没有任何办法,从"created_at 在一月份"推出"order_id 取模等于几"。于是它只能放弃裁剪,把 16 个分区全扫一遍。用 EXPLAIN 一看就清楚了。

-- 用 EXPLAIN 看分区裁剪到底有没有发生,关键看 partitions 这一列

-- 情况 A:按 order_id 分区,却按 created_at 查 —— 裁剪失败
EXPLAIN SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';
-- partitions 列显示:p0,p1,p2,...,p15  —— 16 个分区全在,全扫

-- 情况 B:查询条件带上了分区键 order_id —— 裁剪成功
EXPLAIN SELECT * FROM orders WHERE order_id = 8800001;
-- partitions 列显示:p1  —— 只扫 1 个分区

-- partitions 列,就是分区表性能的"体检报告":
-- 它列出的分区越少,裁剪越成功;列出全部,等于分区白分。

这一节要建立的认知是:分区表的加速,不来自"表被分小了"这个静态的事实,而来自"分区裁剪"这个发生在每一次查询时的动态判断——分区表给你的不是一份已经兑现的加速,而是一张需要查询条件去兑付的"支票"。第一版的想当然,是把分区表理解成了一种"存储结构上的优化":以为只要数据在物理上被拆小了,查询就自动受益,这是一次性的、一劳永逸的。但分区裁剪根本不是这样——它是数据库在执行每一条查询前,临时做的一次推断:"根据这条查询的 WHERE 条件,我能不能确定目标数据只在某几个分区?"能,就只扫那几个,这就是加速;不能,就退回到全分区扫,这时分区表和普通大表没有任何区别,甚至因为多了分区管理的开销而略慢。所以"分区表快不快"这个问题本身就问错了——分区表无所谓快慢,该问的是"我的这条查询,能不能触发分区裁剪"。而这个问题的答案,不在分区表的定义里,在你的分区键和查询条件的匹配关系里。这就把全部的重量,压到了"分区键怎么选"上——这是下一节的事。

二、分区键怎么选:让查询条件能命中它

既然分区裁剪要靠"查询条件命中分区键",那分区键的选法就只有一条铁律:照着你最高频、最在乎性能的那类查询的过滤条件来选。第一版的错,就是凭空选了个 order_id,而真实业务里几乎没有查询是单查一个 order_id 的。正确的做法,是先把业务查询摸清楚。

-- 选分区键前,先摸清楚:业务里最高频的查询,都按什么字段过滤?

-- 假设统计下来,orders 表上 90% 的查询长这几种样子:
--   1. 查某个时间段的订单(报表、对账)
SELECT ... FROM orders WHERE created_at >= ? AND created_at < ?;
--   2. 查某个时间段 + 某地区的订单
SELECT ... FROM orders WHERE created_at >= ? AND created_at < ? AND region = ?;
--   3. 查最近 N 天的订单
SELECT ... FROM orders WHERE created_at >= ?;

-- 结论一目了然:created_at 出现在几乎每一个高频查询的过滤条件里。
-- 那么分区键就该是 created_at —— 而不是凭感觉选的 order_id。

选定 created_at 之后,把表改造成按时间 RANGE 分区,之前那条慢查询就能吃到裁剪了。

-- 正确做法:按最高频查询条件 created_at 做 RANGE 分区

CREATE TABLE orders (
    order_id    BIGINT NOT NULL,
    user_id     BIGINT NOT NULL,
    created_at  DATETIME NOT NULL,
    region      VARCHAR(20),
    amount      DECIMAL(10,2),
    PRIMARY KEY (order_id, created_at)   -- 主键须含分区键,见第六节
)
PARTITION BY RANGE COLUMNS(created_at) (
    PARTITION p202401 VALUES LESS THAN ('2024-02-01'),
    PARTITION p202402 VALUES LESS THAN ('2024-03-01'),
    PARTITION p202403 VALUES LESS THAN ('2024-04-01'),
    PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

-- 现在再 EXPLAIN 那条按一月份查的语句:
-- partitions 列显示:p202401  —— 只扫 1 个分区,裁剪成功!
-- 同一条查询、同样的数据量,分区键选对了,结果天差地别。

这一节的认知是:分区键不是一个"数据库层面的技术选型",它是一个"业务查询层面的决策"——你不是在为这张表选一个键,你是在为"你最想加速的那一类查询"选一个键。第一版选 order_id,错就错在它是"从表的视角"选的:order_id 是主键、是天然唯一、取模能分得很均匀,从表自身看,它是个挑不出毛病的分区键。但分区键的好坏,从来不由表自身决定,而由"查询会不会用到它"决定。一个从表的视角看完美、却从不出现在任何 WHERE 条件里的分区键,是一个零价值的分区键——它把数据分得再均匀,也触发不了一次裁剪。所以选分区键的正确姿势,是先把脸转向业务:把这张表上的查询拉出来,按频率和性能敏感度排序,看最靠前的那几类查询都拿什么字段做过滤——那个反复出现的字段,才是分区键。这意味着分区键的选择必须发生在"你已经了解查询模式"之后,而不能在建表时拍脑袋定下。选对了分区键,下一个问题是:用哪种方式去分——RANGE、LIST 还是 HASH。

三、分区方式:RANGE / LIST / HASH 各适合什么

分区键确定了"按哪个字段分",分区方式则决定"按这个字段的值怎么切"。主流数据库提供三种,各有各的适用场景。先看 RANGE——按值的区间切,最适合时间这种连续、有序的字段。

-- RANGE 分区:按值的"区间"切,适合时间、自增 ID 这类连续有序的键

PARTITION BY RANGE COLUMNS(created_at) (
    PARTITION p202401 VALUES LESS THAN ('2024-02-01'),
    PARTITION p202402 VALUES LESS THAN ('2024-03-01'),
    PARTITION p202403 VALUES LESS THAN ('2024-04-01'),
    PARTITION pmax    VALUES LESS THAN (MAXVALUE)   -- 兜底,接住未来的数据
);

-- RANGE 的精髓:范围查询(created_at BETWEEN ... AND ...)能被
-- 干净地裁剪成"连续的几个分区"。时间序数据几乎都该用 RANGE。
-- 注意 pmax 兜底分区:没有它,插入超出最大边界的数据会直接报错。

再看 LIST——按值的"枚举集合"切,适合地区、类型、状态这种取值是有限离散集合的字段。

-- LIST 分区:按值的"枚举"切,适合地区、类型这类离散取值的键

PARTITION BY LIST COLUMNS(region) (
    PARTITION p_east  VALUES IN ('shanghai', 'jiangsu', 'zhejiang'),
    PARTITION p_north VALUES IN ('beijing', 'hebei', 'tianjin'),
    PARTITION p_south VALUES IN ('guangdong', 'fujian'),
    PARTITION p_other VALUES IN ('other')
);

-- LIST 的精髓:WHERE region = 'shanghai' 能被精确裁剪到 p_east。
-- 适用前提:这个字段的取值是一个"已知的、有限的"集合 ——
-- 一旦出现枚举之外的新值,要么报错,要么得有 p_other 兜着。

最后是 HASH——按哈希值取模均匀打散,适合那种取值离散、无明显区间、你只想"分得均匀"的字段(比如 user_id)。

-- HASH 分区:按哈希取模均匀打散,适合只求"分得匀"的离散键

PARTITION BY HASH(user_id)
PARTITIONS 16;

-- HASH 的精髓:数据分布天然均匀,不容易倾斜。
-- 但它的代价是:HASH 打散后,值的"区间"概念没了 ——
-- WHERE user_id = 123 能裁剪(算得出哈希),
-- 但 WHERE user_id BETWEEN 100 AND 200 无法裁剪(区间被打散到各分区)。
-- 所以 HASH 只服务"等值查询",不服务"范围查询"。

这一节的认知是:三种分区方式不是"三个并列的选项任你挑",而是分别对应三种不同的查询形态——选哪种分区方式,本质上是在回答"我的高频查询,是范围查询、枚举查询,还是等值查询"。很多人选分区方式,是看哪个"听起来更高级"或"分得更均匀",于是常常默认选了 HASH——因为 HASH 分得最匀,最不容易倾斜。但这个选法漏掉了最关键的一点:分区方式必须能服务你的查询。HASH 把值打散得很均匀,代价是彻底摧毁了值的"区间"概念——一段连续的 user_id 会被哈希到所有分区里去,所以 HASH 分区上的范围查询,一个分区都裁剪不掉。如果你的高频查询是时间范围查询,你却用了 HASH,那就和第一版一样,分区裁剪从头到尾不会发生。正确的对应关系很清晰:高频查询是范围查询(时间段、ID 段),用 RANGE;是离散枚举查询(地区、类型),用 LIST;是纯等值查询、且你只想分匀,用 HASH。先看清自己的查询是哪一类,分区方式自然就定了。而无论选哪种,都还有一个共同的敌人要防——数据倾斜。

四、数据倾斜:别让一个分区扛下所有数据

第一版第三种问题——按地区分区后,"华东"一个分区装了六成订单——就是典型的数据倾斜:分区在逻辑上分了,但数据没有被均匀地分到各个分区里,某一个分区奇大。一个奇大的分区,查询命中它时,慢得和没分区的大表没两样。所以分区做完,第一件事是体检,看每个分区到底装了多少行;看到倾斜再针对成因去治——如果是 RANGE 区间划得不合理(早期数据少、近期数据暴涨),就把区间重新规划得更细;如果是 LIST 里某个枚举值本身数据量远超其他值,那它根本不适合单独做分区键。

-- 给分区表做体检:看每个分区实际装了多少行,有没有倾斜

SELECT
    partition_name,
    table_rows
FROM information_schema.partitions
WHERE table_name = 'orders'
ORDER BY table_rows DESC;

-- 健康的结果:各分区行数大致在一个量级。
-- 倾斜的结果(第一版的真实情况):
--   p_east   3.6 亿     <-- 一个分区扛了六成数据
--   p_north  1.1 亿
--   p_south  0.8 亿
--   p_other  0.5 亿
-- p_east 这一个分区,本身就是一张需要再优化的大表。

-- ===== 治倾斜:RANGE 区间按"数据密度"划,而不是按"时间均匀"划 =====

-- 反面:早期一个月数据才几十万,近期一个月几千万,
-- 却都用"一个月一分区",近期分区必然奇大。
-- 正面:按数据量划分区边界 —— 早期可以一季度一分区,
--       近期数据密集,改成半月甚至更细一分区。

PARTITION BY RANGE COLUMNS(created_at) (
    PARTITION p2022_h1 VALUES LESS THAN ('2022-07-01'),  -- 早期半年一分区
    PARTITION p2022_h2 VALUES LESS THAN ('2023-01-01'),
    PARTITION p2024_01a VALUES LESS THAN ('2024-01-16'), -- 近期半月一分区
    PARTITION p2024_01b VALUES LESS THAN ('2024-02-01'),
    PARTITION pmax      VALUES LESS THAN (MAXVALUE)
);

-- 而对地区这种天生就倾斜的键(华东永远比西北订单多),
-- 与其按地区分区,不如改用 HASH(user_id) 把数据强行打匀,
-- 地区则退回成一个普通的二级索引字段。

这一节的认知是:"把表分成 N 个分区"和"数据被均匀地分到 N 个分区"是两件完全不同的事——前者是你写在建表语句里的意图,后者是数据实际分布说了算的结果,而真正决定查询性能的,是后者。第一版按地区分区时,心里默认"分了 16 个区,数据就摊成了 16 份"。但分区数量只是格子的数量,数据会不会均匀地填进这些格子,取决于分区键的取值在真实数据里的分布。地区这个键,它的取值分布天生就是倾斜的——华东的订单量本来就远多于西北,这不是 bug,是业务现实。你按一个分布本就倾斜的键去分区,得到的必然是倾斜的分区。所以防倾斜的关键,是在选分区键和分区方式的时候,就把"这个键的真实取值分布"考虑进去:分布均匀的键(像哈希后的 user_id)适合直接分;分布天生倾斜、但你又必须按它查的键(像时间——近期数据总是更密),就要靠"不等宽的区间"去抵消倾斜,把数据密集的地方切得更细;而分布严重倾斜、且无法靠区间补救的键(像地区),就干脆别拿它做分区键。分区前先问一句"这个键的数据,真的分得匀吗",你才不会建出一个看着分了、实则没分的表。

五、分区表真正的杀手锏:快速归档与删除

到这里,分区表用于"查询加速"的部分讲完了。但分区表还有一个常被忽略、却极其强大的能力,正好能解第一版第四种问题:删老数据。第一版用 DELETE 删两年前的订单,慢得要命——因为 DELETE 要逐行地标记删除、写大量 undo/redo 日志、还会长时间锁住数据。而分区表有一个根本不同的删法。

-- 分区表的杀手锏:删老数据不用 DELETE,直接 DROP 掉整个分区

-- 反面教材:用 DELETE 删两年前的订单
DELETE FROM orders WHERE created_at < '2022-01-01';
-- 几千万行逐行删除:写满 undo/redo 日志、长时间持锁、慢到离谱,
-- 还会留下一堆碎片空间,迟迟不归还给磁盘。

-- 正解:如果按时间 RANGE 分区,两年前的数据正好独占某几个分区,
-- 直接把这些分区整个丢掉 ——
ALTER TABLE orders DROP PARTITION p2021_h1, p2021_h2;
-- 这是一个近乎"瞬时"的元数据操作:数据库只是把这几个分区的
-- 文件整体摘掉,不逐行删、不写大量日志、不长时间锁表。
-- 几千万行数据,一瞬间就没了,空间也立刻归还。

但 DROP PARTITION 要好用,有个前提:老数据必须正好"干净地"落在某几个完整的分区里。这就要求分区表配合一套"滚动维护"——定期在头部加新分区、从尾部丢旧分区。

-- 配套:滚动分区维护,定期"头部加新分区、尾部删旧分区"

-- 每月初,做两件事(可以用数据库事件 EVENT 或外部定时脚本来跑):

-- 1. 加一个下个月的新分区(从 pmax 里"拆"出来,REORGANIZE)
ALTER TABLE orders REORGANIZE PARTITION pmax INTO (
    PARTITION p202405 VALUES LESS THAN ('2024-06-01'),
    PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

-- 2. 丢掉一个超出保留期(比如只留 24 个月)的最老分区
ALTER TABLE orders DROP PARTITION p202205;

-- 这样表就始终维持着"固定数量的分区",
-- 新数据有地方落、老数据被定期干净地清走,
-- 归档从一个"跑几小时还锁表的 DELETE 大作业",
-- 变成了一个"一瞬间完成的 DROP"。

这一节的认知是:分区表的价值有两个维度,一个是"查询加速",另一个是"数据生命周期管理"——第一版只盯着前者,而对很多有时间序数据的系统来说,后者才是分区表更稳、更不可替代的价值。"查询加速"这个价值,前面讲过,是有条件的——要分区键选对、要查询命中裁剪,稍有不慎就落空。但"数据生命周期管理"这个价值,几乎是无条件的:只要你按时间做了 RANGE 分区,那么"删除某个时间段之前的所有老数据"这件事,就从一个昂贵、危险、要锁表几小时的 DELETE 大作业,变成了一个 DROP PARTITION 的瞬时元数据操作。这个差别是数量级的。逐行 DELETE 几千万行,要写海量日志、要长时间持锁、删完还留一堆不还给磁盘的碎片;而 DROP PARTITION 只是把一个分区的文件整体摘除,几乎不耗时、不锁表、空间立刻回收。对日志表、订单表、流水表这类"数据有明确保留期、老数据要定期清理"的系统,分区表带来的这个能力,价值往往比查询加速还大。所以评估"要不要上分区表",不能只算查询会不会变快这一笔账——还要算上:你有没有定期归档老数据的需求。如果有,分区表几乎是必选项。

把一个查询进来后,数据库怎么决定要扫哪些分区的决策流程画出来,就是下面这张图:

[mermaid]
flowchart TD
A[一个查询进来] --> B{WHERE 条件里有分区键吗}
B -->|没有| C[无法裁剪 扫描全部分区]
B -->|有| D{条件形态和分区方式匹配吗}
D -->|范围查询遇上 HASH 分区| C
D -->|匹配| E[分区裁剪 只锁定相关分区]
E --> F[只在这几个分区里走索引和扫描]
C --> G[性能等同甚至略差于普通大表]
F --> H[查询要扫的数据量大幅下降]

六、把分区表做扎实,要避开的工程坑

前面五节讲清了分区表的核心:分区裁剪、分区键、分区方式、防倾斜、归档。但要在生产里真正用稳,还有几个工程坑得专门讲。第一个,也是最容易在建表时就踩中的:分区表对主键和唯一索引有一条硬性约束——它们必须包含分区键。

-- 坑一:分区表的主键 / 唯一索引,必须包含分区键

-- 这样建会直接报错:主键是 order_id,分区键是 created_at,
-- 主键里不含分区键 ——
CREATE TABLE orders (
    order_id BIGINT NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (order_id)
) PARTITION BY RANGE COLUMNS(created_at) ( ... );
-- 报错:A PRIMARY KEY must include all columns in the partitioning function

-- 正确:把分区键 created_at 一并放进主键
PRIMARY KEY (order_id, created_at)

-- 原因:分区表的索引是"本地"的(每个分区有自己的索引),
-- 数据库无法跨分区保证唯一性。要让"唯一"成立,
-- 它必须能从索引列里推断出分区,所以索引必须含分区键。
-- 这条约束会反过来影响你的表设计,建表前就要想清楚。

第二个坑,是误以为"分区"能替代"索引"。它们解决的是完全不同维度的问题,缺一不可。

-- 坑二:分区不是索引的替代品,两者要配合用

-- 分区裁剪:把"要扫的分区"从 24 个缩到 1 个 —— 这是粗筛
-- 分区内索引:在选中的那 1 个分区里,靠索引快速定位行 —— 这是精筛

-- 一个查询的理想路径,是两级筛选都用上:
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'  -- 触发裁剪
  AND user_id = 12345;                                          -- 走索引

-- 所以分区表上,该建的二级索引一个都不能少。
-- 给分区内常用的过滤字段(user_id、region)建索引:
ALTER TABLE orders ADD INDEX idx_user (user_id);

-- 只分区不建索引:裁剪到 1 个分区后,在那个还有几千万行的
-- 分区里全表扫,照样慢。分区管"扫哪些区",索引管"区里怎么找"。

还有几个坑值得点一下。其一,分区数量不是越多越好——分区过多(成百上千个),数据库维护分区元数据本身就有开销,且每次全分区扫描会更慢,通常几十个分区是个合理范围。其二,跨分区的查询(尤其是跨分区的 JOIN、聚合)并不会因为分区而变快,反而可能因为要合并多个分区的结果而变慢,分区表不擅长这类查询。其三,把一张已有的巨大的表在线改造成分区表,本身是一个重操作(往往要重建整张表),要在低峰期做、要做好备份、大表建议用专门的在线 DDL 工具。下面把三种分区方式集中对照一下:

三种分区方式对照

  方式     按什么切            适合的高频查询        典型场景
  --------------------------------------------------------------
  RANGE    值的连续区间        范围查询 BETWEEN      时间序数据 订单 日志
  LIST     值的离散枚举集合    枚举等值 region IN    地区 类型 状态
  HASH     哈希取模均匀打散    单值等值 id 等于      只求分匀的离散键

  共同前提:查询条件必须命中分区键,否则三种都退化成全分区扫
  共同收益:按 RANGE 分区时 DROP PARTITION 可瞬时归档老数据

这一节这几个坑,串起来是同一个意思:分区表不是一个"打开就生效"的开关,它是一个会反过来约束你整张表设计的结构性决策。它要求你的主键和唯一索引必须包含分区键——这可能逼你改掉原本简洁的主键设计;它不替代索引,你该建的二级索引一个都不能省;它对跨分区查询并不友好,如果你的业务有大量跨时间段的聚合,分区表帮不上忙甚至添乱;把一张生产大表改造成分区表,本身就是一次需要谨慎规划的重型手术。这些约束表面上各不相同,根子上指向同一件事:分区表改变的是数据在物理上的组织方式,而物理组织方式一变,主键、索引、查询、维护操作全都要跟着重新考虑。所以决定用分区表,不能是"这表有点大,顺手分个区"——它是一个要在建表设计阶段就想清楚、并且会贯穿这张表整个生命周期的决策。把分区表当成一个严肃的表结构设计来对待,而不是一个事后补救的优化开关,你才能真正用稳它。

关键概念速查

避坑清单

1. 不要以为分了区查询就快:加速来自分区裁剪,不来自拆表本身。
2. 不要凭表的视角选分区键:要照最高频查询的过滤条件来选。
3. 不要查询条件不带分区键:命不中分区键,裁剪不会发生。
4. 不要给范围查询用 HASH 分区:HASH 打散了区间,范围查裁剪不掉。
5. 不要分区后不体检:用 information_schema 看每个分区的行数。
6. 不要拿天生倾斜的键分区:地区这类键分出来必然倾斜。
7. 不要用 DELETE 删老数据:按时间分区时 DROP PARTITION 才是正解。
8. 不要忘了滚动维护:定期加新分区、删旧分区,别让 pmax 撑爆。
9. 不要让主键不含分区键:分区表对主键和唯一索引有硬性约束。
10. 不要用分区替代索引:分区管扫哪些区,索引管区里怎么找。

总结

回头看第一版那个"按 order_id 取模分 16 区"的改造,它的失败很典型。它不在某一行代码,而在一个对分区表的根本误解:以为分区表是一个"分了就快"的加速开关,数据量被拆小,查询自然受益。真相是,分区表本身不提供任何加速,它提供的是"分区裁剪"这个能力;而这个能力要兑现,必须分区键选得对、查询条件命中它。第一版按 order_id 分区、却按 created_at 查询,两者对不上,16 个分区每次全扫——分区表退化成了一张多了碎片、却没有加速的普通大表。

而把分区表用对,工程量并不小。它不是"给表加一句 PARTITION BY"那么简单,而是要先摸清业务的高频查询、照查询条件去选分区键、按查询形态选对 RANGE/LIST/HASH、用 EXPLAIN 确认分区裁剪真的发生、给分区表做倾斜体检、用 DROP PARTITION 配合滚动维护去做归档,还要处理好主键约束、二级索引这些结构性的约束。一套真正发挥作用的分区表,是这些环节一个不少地拼起来的。

这件事其实很像一座大型图书馆怎么摆放它的几百万册藏书。第一版的想法是"书太多了,分成 16 个库房放,每个库房书少了,找起来自然就快"。可如果这 16 个库房是按"书的进馆流水号"末位来分的,而读者来找书,报的全是书名、作者——管理员拿着书名,根本推不出这本书的流水号末位是几,只能 16 个库房挨个翻,分库房等于白分,这就是分区键和查询对不上。聪明的图书馆,会照着读者最常用的检索方式来分库——按学科分,文学一区、科技一区,读者一报学科,管理员立刻知道该去哪个库房,这就是照查询选分区键。在每个库房内部,还得有书架编号和索引卡,光知道在哪个库房、不知道在哪个架子上照样找不到,这就是分区配合索引。而到了年底要清掉一批过期的旧报刊,聪明的做法不是一本本从书架上抽出来,而是直接把存放旧报刊的那整个库房腾空——这就是 DROP PARTITION。藏书要好找,靠的从来不是"分成多少个库房",而是"库房的划分方式,正好贴合读者找书的方式"。

这类问题还有一个共同的麻烦:它在开发和测试时几乎暴露不出来。你自己测,造几万行数据,无论分不分区、分区键选得对不对,查询都是毫秒级返回,你根本看不出分区裁剪有没有生效,会觉得"分区表嘛,加一句 PARTITION BY 就完事"。真正会把问题撑爆的,是上线后的真实数据量:几亿行数据压上来,一个命不中裁剪的查询会被这个量级放大成十几秒的慢查询,一个倾斜的分区会膨胀成一张几亿行的隐形大表,而你想删老数据时才发现 DELETE 在生产库上要锁表几个小时。所以如果你正在为一张大表设计分区方案,别等它上了生产、慢查询告警响了,才回头怀疑你的分区键。在写下 PARTITION BY 的那一刻就想清楚:我的高频查询按什么字段过滤、这个查询能不能命中分区裁剪、用 EXPLAIN 验证过没有、数据按这个键分得匀不匀、将来归档老数据走不走得了 DROP PARTITION——把"把表分了区"和"让分区表在真实数据量下真正起作用"当成两件必须分别去做的事,这是这篇文章最想留给你的一句话。

—— 别看了 · 2026