当前位置：首页 > 文章列表 > 数据库 > MySQL > MySQL 8.4 分区表实战：为什么分了区还是全分区扫描

MySQL 8.4 分区表实战：为什么分了区还是全分区扫描

来源：17golang MySQL频道原创 2026-06-04 13:50:37 0浏览收藏

先说结论：分区表快不快，先看有没有发生分区裁剪

我见过不少项目把大表一分区，就以为慢查询自然会消失。结果上线后慢日志还是刷，甚至比单表更难看。原因很简单：MySQL 分区表真正能帮你的前提，是优化器能通过查询条件排除无关分区，也就是 partition pruning。裁剪失败时，分区表可能变成“多个小表一起扫”。

本文用订单历史表举例，重点讲 MySQL 8.x 里分区裁剪如何验证、哪些 SQL 写法会失效、分区内索引怎么配合，以及上线前我会怎么检查。

MySQL 分区裁剪排查思维导图 — 分区裁剪、分区内索引、分区运维是三件事，排查时要分开看。

业务场景：订单表按月分区后仍然慢

假设订单表按支付时间做月度 RANGE COLUMNS 分区：

CREATE TABLE orders (
  id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  paid_at DATETIME NOT NULL,
  status TINYINT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  PRIMARY KEY (id, paid_at),
  KEY idx_user_paid (user_id, paid_at)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(paid_at) (
  PARTITION p202605 VALUES LESS THAN ('2026-06-01'),
  PARTITION p202606 VALUES LESS THAN ('2026-07-01'),
  PARTITION p202607 VALUES LESS THAN ('2026-08-01'),
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

按理说，查 6 月订单应该只访问 p202606。但线上慢 SQL 常常写成这样：

SELECT id, amount
FROM orders
WHERE DATE(paid_at) = '2026-06-01'
  AND status = 1
ORDER BY id DESC
LIMIT 100;

问题在 DATE(paid_at)。分区键被函数包裹后，优化器很难在执行前判断到底哪些分区一定不需要访问。你以为按天查，实际可能多个分区都被扫一遍。

诊断第一步：EXPLAIN PARTITIONS

排分区表慢查询时，我第一眼看 EXPLAIN PARTITIONS，不是先看普通索引。

EXPLAIN PARTITIONS
SELECT id, amount
FROM orders
WHERE DATE(paid_at) = '2026-06-01'
  AND status = 1
ORDER BY id DESC
LIMIT 100;

如果 partitions 里出现了很多个月份，说明分区裁剪失败。此时就算 key 看起来用了某个索引，也只是每个被访问分区内各扫一遍，整体成本仍然可能很高。

MySQL 分区表慢查询诊断流程 — 我的排查顺序固定：先看 partitions，再看分区内索引，再看排序和 LIMIT。

正确写法：把条件写成分区键可推导的范围

把函数条件改成半开区间，通常更利于裁剪：

SELECT id, amount
FROM orders
WHERE paid_at >= '2026-06-01'
  AND paid_at

再次执行：

EXPLAIN PARTITIONS
SELECT id, amount
FROM orders
WHERE paid_at >= '2026-06-01'
  AND paid_at

这时候理想结果是 partitions 只包含 p202606。如果仍然访问多个分区，就继续查类型转换、时区边界、OR 条件和分区定义是否匹配。

MySQL 分区裁剪 SQL 对比案例 — 函数包裹分区键是最常见的裁剪失效原因之一，半开时间范围更稳。

踩坑原因：这些写法很容易让裁剪失效

函数包裹分区键。 比如 DATE(paid_at)、YEAR(paid_at)、TO_DAYS(paid_at) 包在查询侧，容易让裁剪变差。
隐式类型转换。 分区键是日期或整数，条件却传入格式不稳定的字符串，优化器判断会变复杂。
跨分区键的 OR。 paid_at 条件和其他字段用 OR 拼在一起，常常会扩大访问分区。
只建分区不建索引。 裁剪只负责少访问分区，进入目标分区以后仍然要靠普通索引完成过滤、排序和回表控制。

分区内索引也要重新设计

分区不是索引替代品。比如常见查询是按用户查近 30 天订单：

SELECT id, paid_at, amount
FROM orders
WHERE user_id = 88001
  AND paid_at >= '2026-06-01'
  AND paid_at

这类 SQL 既需要分区裁剪，也需要分区内的 (user_id, paid_at) 索引。没有后者，优化器即使只进一个月分区，也可能在目标分区里扫大量用户数据。

上线检查：分区表最怕没人管未来分区

我上线分区表一定会检查三件运维事。第一，未来分区有没有提前创建，避免数据落入 pmax 后难清理。第二，归档删除是不是用 DROP PARTITION 或 TRUNCATE PARTITION 这类明确动作，而不是大事务 delete。第三，慢 SQL 监控里要带上访问分区数，至少定期抽样 EXPLAIN PARTITIONS。

ALTER TABLE orders
ADD PARTITION (
  PARTITION p202608 VALUES LESS THAN ('2026-09-01')
);

如果业务已经大量写入 pmax，不要直接在线上硬拆，先评估数据量、复制延迟、DDL 影响和回滚方案。

个人经验：分区解决的是管理和裁剪，不是所有慢查询

分区表最适合两类收益：按时间快速清理历史数据，以及让明确时间范围查询少访问分区。它不适合被当成“万能大表优化按钮”。查询条件不带分区键、排序无法利用索引、热点集中在当前分区，这些问题不会因为分区自动消失。

我的做法是：先拿线上 TOP SQL 反推分区键；确认 80% 以上核心查询都能带上分区范围；再设计分区内索引和运维脚本。只为了“表太大看着不舒服”而分区，后面往往会付出更高维护成本。

总结

MySQL 8.x 分区表要想真正变快，必须同时满足三点：查询条件能触发分区裁剪；目标分区内有合适索引；分区生命周期有人维护。排查时先看 EXPLAIN PARTITIONS 的 partitions 字段，再看 key 和 rows。如果分区裁剪都没发生，别急着怪索引，先把 SQL 写法和分区键条件改正确。