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)带来的副作用也值得警惕同一个id123可以出现在 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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 汇总表的查询几毫秒就出来 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(days1)).strftime(%Y-%m-%d) # 提取年月 stat_month target_date[:7] # 2026-07 try: # 1. 删除该月的旧数据UPSERT策略 # 为什么删掉再插入而非UPDATE # UPDATE需要逐行对比DELETEINSERT用事务保证原子性更简单 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 点之间读不到。对于 T1 的场景这一个小时的窗口是可接受的。四、分区表 vs 汇总表选择决策框架4.1 关键差异对比维度分区表汇总表查询性能中等提升裁剪分区大幅提升预计算数据新鲜度实时T1取决于刷新频率存储开销无额外存储额外一张表明细可见✅ 保留明细❌ 聚合后不可逆灵活性只能按分区键优化预聚合什么就查什么维护成本需要管理分区生命周期需要维护刷新任务适用查询包含分区键的范围查询预定义的聚合查询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固定报表 T1可接受 → 汇总表 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 提前创建未来分区。汇总表的数据延迟必须在看板页面明示如果汇总表是 T1 更新看板顶部必须标注数据更新至昨日。否则运营看到今天的 GMV 是昨天的数字会以为系统出 Bug然后你来排查一个不是 Bug 的 Bug。分区表不要和汇总表选同一个维度如果分区表已经按日期做了物理拆分汇总表没必要再按日期聚合一遍——汇总表应该解决的是分区表解决不了的事比如跨月份的品类对比、用户等级汇总。二者是互补关系不是互相替代。五、总结分区表和汇总表不是互相排斥的方案而是各有侧重分区表解决查得快——通过分区裁剪缩小扫描范围适合查询条件固定、包含分区键的场景。汇总表解决不用查——把结果提前算好直接取就行适合固定维度、T1可接受的报表场景。选型的核心决策因子是三个查询是否总带范围条件决定分区表是否有效、能否接受非实时数据决定汇总表是否可行、维度是否灵活多变决定汇总表的适用性。当MySQL无法满足时不要硬撑——该上ClickHouse上ClickHouse该上Doris上Doris。用对工具比优化工具更重要。记住这句话分区是物理上的拆分读的时候少读一些汇总是逻辑上的聚合提前算好读的时候直接用。理解了这个本质区别选型就不会错。