MySQL 分析型查询优化：分区表与汇总表的选择决策树
一、问题的起点：报表越来越慢了
先看一个真实场景：电商订单表orders，每天新增10万条，已经跑了3年，总数据量超过1亿行。业务方要求做一个"按月看各品类的GMV趋势"的看板，SQL大概长这样：
-- 这个查询在1亿行的表上要跑多久？ SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, category_id, SUM(order_amount) AS gmv, COUNT(DISTINCT user_id) AS buyer_count FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2026-07-01' GROUP BY month, category_id ORDER BY month, category_id;没有索引优化的情况下，这个查询可能跑几十秒甚至几分钟。优化方案通常有三个方向：加索引、分区表、汇总表。但问题在于——该选哪个？
这个决策树就是我们今天要重点讨论的内容。
二、分区表：把大表"切"小
2.1 分区表的核心原理
MySQL的分区表本质上是一个逻辑上是一张表，物理上是多个独立文件的设计。
-- 创建按月份RANGE分区的订单表 CREATE TABLE orders_partitioned ( id BIGINT AUTO_INCREMENT, user_id BIGINT NOT NULL, category_id INT NOT NULL, order_amount DECIMAL(10, 2) NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id, created_at), -- 注意：分区键必须在主键中 INDEX idx_category (category_id), INDEX idx_user (user_id) ) PARTITION BY RANGE (TO_DAYS(created_at)) ( PARTITION p20260701 VALUES LESS THAN (TO_DAYS('2026-07-01')), PARTITION p20260801 VALUES LESS THAN (TO_DAYS('2026-08-01')), PARTITION p20260901 VALUES LESS THAN (TO_DAYS('2026-09-01')), PARTITION p20261001 VALUES LESS THAN (TO_DAYS('2026-10-01')), PARTITION p_future VALUES LESS THAN MAXVALUE -- 兜底分区，防止写入失败 );分区的核心效果叫分区裁剪（Partition Pruning）：
-- 这个查询只会扫描 p20260801 这一个分区， -- 其他分区的数据根本不会被读 EXPLAIN PARTITIONS SELECT * FROM orders_partitioned WHERE created_at BETWEEN '2026-07-15' AND '2026-07-20'; -- Extra: Using where; 只扫描p20260801分区2.2 分区表的适用场景
分区表最适合的场景是查询条件总是包含分区键的情况：
-- ✅ 好场景：WHERE条件包含created_at -- 分区裁剪生效，只扫一个分区，1000万行 → 100万行（10分区） SELECT category_id, SUM(order_amount) FROM orders_partitioned WHERE created_at >= '2026-07-01' AND created_at < '2026-08-01' GROUP BY category_id;-- ❌ 坏场景：WHERE条件不包含分区键 -- 无法分区裁剪，要扫描全部分区，和普通表一样慢 SELECT * FROM orders_partitioned WHERE user_id = 12345; -- 这句话会扫描所有10个分区！2.3 分区表的陷阱
-- 陷阱1: 分区键必须在所有主键和唯一索引中 -- 下面这个定义会报错： CREATE TABLE wrong_orders ( id BIGINT PRIMARY KEY, -- 主键是id created_at DATETIME NOT NULL, amount DECIMAL(10,2) ) PARTITION BY RANGE (TO_DAYS(created_at)) ( -- ERROR 1503: A PRIMARY KEY must include all columns in the partition function ); -- 正确做法：PRIMARY KEY (id, created_at) -- 但这也意味着不能保证id全局唯一了！这是分区表最大的坑。-- 陷阱2: 跨分区查询可能比不分区的表更慢 -- 假设按月份分了12个分区，WHERE条件不包含分区键： SELECT * FROM orders_partitioned WHERE category_id = 5 AND order_amount > 1000; -- 需要打开12个分区文件逐个扫描，文件打开关闭也有开销-- 陷阱3: 分区数量有上限 -- MySQL 5.7: 8192个分区 -- MySQL 8.0: 8192个分区 -- 按天分区 → 8192天 ≈ 22年，长期来看是够的 -- 但按时分区 → 8192小时 ≈ 341天，不够一年！为什么：分区键必须在所有主键和唯一索引中，这是 MySQL 底层架构的限制，不是语法糖。MySQL 的分区实现是在存储引擎层做的——每个分区是一个独立的物理文件，而主键的唯一性约束是全局的。如果分区键不在主键里，MySQL 就无法在 O(1) 的时间内判断一条新记录是不是与已有记录主键冲突——它需要扫全部分区才能确认，这让主键的唯一性检查退化为全表扫描。
PRIMARY KEY (id, created_at)带来的副作用也值得警惕：同一个id=123可以出现在 7 月分区和 8 月分区各一次——主键的"唯一性"退化成了"分区内的唯一性"。如果你的业务逻辑强依赖订单 ID 全局唯一，需要在应用层额外加一层去重校验，这是很多人上分区表后踩的第一个坑。
三、汇总表：空间换时间
3.1 汇总表的设计思路
汇总表的思想非常朴素：提前算好，查的时候直接取。
-- 原始查询（慢）：每次都要扫百万行数据 SELECT category_id, DATE_FORMAT(created_at, '%Y-%m') AS month, SUM(order_amount) AS gmv FROM orders WHERE created_at >= '2026-01-01' GROUP BY category_id, month; -- 换成汇总表（快）：只需要扫几百行 CREATE TABLE orders_monthly_summary ( id BIGINT AUTO_INCREMENT PRIMARY KEY, stat_month VARCHAR(7) NOT NULL, -- 统计月份，如 '2026-07' category_id INT NOT NULL, order_count INT NOT NULL DEFAULT 0, -- 订单数 total_amount DECIMAL(12, 2) NOT NULL, -- 总金额 unique_buyers INT NOT NULL DEFAULT 0, -- 独立购买用户数 UNIQUE KEY uk_month_category (stat_month, category_id), INDEX idx_category (category_id), INDEX idx_month (stat_month) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 汇总表的查询：几毫秒就出来 SELECT stat_month, SUM(total_amount) AS gmv FROM orders_monthly_summary WHERE stat_month >= '2026-01' GROUP BY stat_month;3.2 汇总表的维护：离线跑批更新
import pymysql from datetime import datetime, timedelta import logging class SummaryTableUpdater: """ 汇总表增量更新器 每天凌晨2点跑一次，更新前一天的汇总数据 """ def __init__(self, db_config: dict): self.db_config = db_config self.conn = pymysql.connect(**db_config) def update_daily(self, target_date: str = None): """ 增量更新指定日期的汇总数据 参数: target_date: 格式 'YYYY-MM-DD'，默认昨天 """ if target_date is None: target_date = (datetime.now() - timedelta(days=1)).strftime('%Y-%m-%d') # 提取年月 stat_month = target_date[:7] # '2026-07' try: # ========== 1. 删除该月的旧数据（UPSERT策略） ========== # 为什么删掉再插入而非UPDATE？ # UPDATE需要逐行对比，DELETE+INSERT用事务保证原子性更简单 with self.conn.cursor() as cursor: cursor.execute(""" DELETE FROM orders_monthly_summary WHERE stat_month = %s """, (stat_month,)) # ========== 2. 从原始表聚合数据 ========== with self.conn.cursor() as cursor: # 这是唯一一次扫描原始表的地方！ # 查询利用了created_at索引，效率相对可控 cursor.execute(""" INSERT INTO orders_monthly_summary (stat_month, category_id, order_count, total_amount, unique_buyers) SELECT DATE_FORMAT(created_at, '%%Y-%%m') AS stat_month, category_id, COUNT(*) AS order_count, SUM(order_amount) AS total_amount, COUNT(DISTINCT user_id) AS unique_buyers FROM orders WHERE DATE_FORMAT(created_at, '%%Y-%%m') = %s GROUP BY stat_month, category_id """, (stat_month,)) # ========== 3. 提交事务 ========== self.conn.commit() print(f"[{datetime.now()}] 汇总表更新完成: {target_date}") except Exception as e: self.conn.rollback() logging.error(f"汇总表更新失败: {e}") raise def close(self): self.conn.close()3.3 汇总表的适用场景
为什么：汇总表用 DELETE + INSERT 而不是 UPDATE，不是因为 UPDATE 语法难写，而是因为"全月重算"比"增行对比"更安全。想象一下这样的场景：昨天凌晨跑批时，有 20 万条订单因为消息延迟还没入仓，汇总表只算到 80 万条。今天这 20 万条延迟到了，如果做 UPDATE——你需要逐行对比哪些 category 增加了订单、增加了几条、金额增加多少——这个逻辑本身就可能出错。而 DELETE + INSERT 一把梭重算整个月，只要事务不丢，结果一定是对的。事务性（原子性）换来了正确性，代价是这个月的数据在凌晨 2 点到 3 点之间读不到。对于 T+1 的场景，这一个小时的窗口是可接受的。
四、分区表 vs 汇总表：选择决策框架
4.1 关键差异对比
| 维度 | 分区表 | 汇总表 |
|---|---|---|
| 查询性能 | 中等提升（裁剪分区） | 大幅提升（预计算） |
| 数据新鲜度 | 实时 | T+1（取决于刷新频率） |
| 存储开销 | 无额外存储 | 额外一张表 |
| 明细可见 | ✅ 保留明细 | ❌ 聚合后不可逆 |
| 灵活性 | 只能按分区键优化 | 预聚合什么就查什么 |
| 维护成本 | 需要管理分区生命周期 | 需要维护刷新任务 |
| 适用查询 | 包含分区键的范围查询 | 预定义的聚合查询 |
| DML影响 | 和普通表一样 | 需要更新汇总表 |
4.2 决策四象限
4.3 实战选择指南
def choose_optimization_strategy(table_size_million: float, query_type: str, query_contains_partition_key: bool, freshness_requirement: str, dimension_count: int) -> str: """ 根据业务特征返回推荐方案 参数： table_size_million: 表大小（百万行） query_type: 查询类型，'aggregation'或'detail' query_contains_partition_key: 查询条件是否包含分区键 freshness_requirement: 数据新鲜度要求，'realtime'或't_plus_1' dimension_count: 报表涉及维度数量 返回： 推荐方案 """ # 规则1：表本身不大，加索引就够了 # 1000万行以下，合理的索引足以支撑查询 if table_size_million < 10 and query_type == 'aggregation': return "加复合索引即可，分区表和汇总表都太重了" # 规则2：纯粹按时效范围的聚合查询 → 分区表 if query_contains_partition_key and query_type == 'aggregation': return "分区表（按时间RANGE分区），查询包含分区键可分区裁剪" # 规则3：固定报表 + T+1可接受 → 汇总表 if (freshness_requirement == 't_plus_1' and query_type == 'aggregation' and dimension_count <= 5): return "汇总表，离线预计算，查询毫秒级" # 规则4：实时 + 灵活维度 → 该换引擎了 if (freshness_requirement == 'realtime' and dimension_count > 5): return "MySQL不是最优选择，考虑ClickHouse或Doris" # 规则5：中间地带 → 分区表 + 汇总表组合 return "分区表 + 汇总表组合，分区表做明细查询，汇总表做聚合查询"为什么：决策树的第一个分支——"数据量是否大于 1000 万行"——不是拍脑袋定的数字。这是基于 B+ 树索引深度和内存缓存比计算的经验阈值。1000 万行表在 InnoDB 中大约占用 1.5GB 空间（含索引），MySQL 的 buffer pool 如果配置 4GB，可以在内存中缓存大部分热数据，索引扫描还是快的。但当数据量翻到 5000 万行时，buffer pool 命中率急剧下降，磁盘 IO 成为瓶颈，此时单靠索引用力已经不够了。这就是为什么决策树要在 1000 万行这里分叉——不是教条，是物理极限。
踩坑提醒
按天分区要预设自动化：很多人上线时手工建了 3 个月的分区，半年后发现
p_future分区里塞了 90% 的数据——因为没人记得提前建新分区。分区表必须配套自动化脚本或 MySQL Event Scheduler 提前创建未来分区。汇总表的"数据延迟"必须在看板页面明示：如果汇总表是 T+1 更新，看板顶部必须标注"数据更新至昨日"。否则运营看到今天的 GMV 是昨天的数字，会以为系统出 Bug，然后你来排查一个不是 Bug 的 Bug。
分区表不要和汇总表选同一个维度：如果分区表已经按日期做了物理拆分，汇总表没必要再按日期聚合一遍——汇总表应该解决的是分区表解决不了的事，比如跨月份的品类对比、用户等级汇总。二者是互补关系，不是互相替代。
五、总结
分区表和汇总表不是互相排斥的方案，而是各有侧重：
- 分区表解决"查得快"——通过分区裁剪缩小扫描范围，适合查询条件固定、包含分区键的场景。
- 汇总表解决"不用查"——把结果提前算好，直接取就行，适合固定维度、T+1可接受的报表场景。
- 选型的核心决策因子是三个：查询是否总带"范围条件"（决定分区表是否有效）、能否接受"非实时数据"（决定汇总表是否可行）、维度是否灵活多变（决定汇总表的适用性）。
- 当MySQL无法满足时，不要硬撑——该上ClickHouse上ClickHouse，该上Doris上Doris。用对工具比优化工具更重要。
记住这句话：分区是物理上的拆分（读的时候少读一些），汇总是逻辑上的聚合（提前算好，读的时候直接用）。理解了这个本质区别，选型就不会错。
