OLTP 与 OLAP：报表查询什么时候该搬出 MySQL
业务库跑得好好的，某天加了一个「按月统计销售额」的报表，source（主库）的 CPU 就开始间歇性打满。问题不在 SQL 写得不好，而在于这类查询和业务读写需要的存储结构本来就相反。
联机事务处理（Online Transaction Processing，OLTP）和联机分析处理（Online Analytical Processing，OLAP）的定义很好找，但工程上真正需要回答的是：两类负载差在哪里、差多少，以及什么时候值得为分析查询单独维护一套存储。本文用同一份 500 万行订单数据，在 MySQL 和列式数据库 ClickHouse 上分别跑了三条查询。
一、先说结论
- 两类负载的访问模式相反。 OLTP 是「按主键或索引读写少量整行」，OLAP 是「扫描大量行、只用其中几列做聚合」。
- 存储结构决定了各自的上限。 行存（如 InnoDB）把一行的所有列放在一起，适合整行读写；列存把同一列放在一起，适合只读几列的大范围扫描，并且压缩率高得多。
- 实测差距在两个方向上都是数量级。 500 万行上做聚合，两边都限制 2 个 CPU 时，ClickHouse 比 MySQL 快 37—53 倍；按客户查最近 10 单，MySQL 走索引只要 0.33ms，ClickHouse 要 7ms，还读了 110 万行。
- 在业务 source 上跑分析查询，代价不只是慢。 大范围扫描会挤占 Buffer Pool、拉高 CPU，长时间的一致性读还会阻碍 Undo 清理，影响在线请求。
- 拆分有成本：数据同步链路、延迟、口径一致性。数据量不大、报表不多时，只读 replica（从库）加汇总表往往就够了。
二、两类负载到底差在哪
| 维度 | OLTP | OLAP |
|---|---|---|
| 典型操作 | 下单、支付、查订单详情 | 按月统计销售额、漏斗分析、多维报表 |
| 每次涉及的行数 | 几行到几百行 | 几十万到几十亿行 |
| 每次涉及的列数 | 通常是整行 | 通常只有几列 |
| 读写比例 | 读写都多，写入要求强一致 | 以读为主，数据批量或近实时导入 |
| 延迟要求 | 毫秒级 | 秒级可以接受 |
| 并发 | 高并发短请求 | 低并发长查询 |
| 数据模型 | 规范化，减少冗余 | 宽表或星型模型，允许冗余 |
| 典型存储 | MySQL、PostgreSQL | ClickHouse、Apache Doris、StarRocks 等列式数据库 |
表里最关键的是第二、三行：行数多、列数少的查询，天然适合列存。
三、行存与列存：同一个查询读了多少数据
InnoDB 的数据按主键组织在 B+ 树中，每个叶子页里存放的是完整的行。统计金额时，即使只需要 status 和 amount 两列，也必须把整行所在的页读进内存。
列存把每一列单独存储：
- 只读用到的列。 实测中这个聚合查询在 ClickHouse 里读取了 500 万行、共 110MB 数据，而 MySQL 的这张表有 870MB。
- 同一列的数据类型相同、取值相近，压缩率高。 实测 120 字节的备注列内容高度重复，577MB 压缩到 24MB；
status列只有 4 种取值，但在这份数据里按时间排序后是打散的，33MB 只压缩到 11MB。压缩率取决于数据分布和排序键，不是列存天然附带的固定倍数。 - 按列批量计算。 一次处理一批值，能更好地利用 CPU 缓存和 SIMD 指令。
代价是按主键取整行、单行更新都很低效，这正是 OLTP 最常见的操作。
四、实测：500 万行上的三条查询
4.1 测试环境
| 项 | MySQL | ClickHouse |
|---|---|---|
| 版本 | 8.4.11 | 25.8.33.6 |
| 表结构 | InnoDB，主键 id，索引 (customer_id, created_at) | MergeTree，排序键 (created_at, customer_id) |
| 数据 | 500 万行，20 万客户，时间跨度 600 天，含一个 120 字节的备注列 | 同一份数据 |
| 内存 | innodb_buffer_pool_size 调到 2GB，数据全部缓存后再计时 | 默认配置 |
| 资源 | 容器限制 2 CPU、3 GB | 同左 |
| 机器 | 同一台机器上的 Docker，镜像固定 digest | 同左 |
两边的数据用同一套公式分别生成，三条查询的结果逐字节相同。备注列内容重复，压缩效果会比真实数据好得多。数字只用来说明数量级和方向，不代表两个产品在生产环境中的性能对比。
4.2 结果
| 查询 | MySQL | ClickHouse | ClickHouse 读取量 |
|---|---|---|---|
| ① 按状态统计订单数与金额（全表聚合） | 1,635—1,662ms | 41—45ms | 500 万行，110.0MB |
| ② 按月统计半年销售额（范围聚合） | 995—1,011ms | 18—20ms | 179 万行，21.5MB |
| ③ 查某个客户最近 10 单 | 0.29—0.35ms | 7—8ms | 110 万行，5.5MB |
-- ① 全表聚合
SELECT status, COUNT(*), SUM(amount) FROM orders_big GROUP BY status;
-- ② 范围聚合
SELECT DATE_FORMAT(created_at, '%Y-%m') AS m, SUM(amount)
FROM orders_big
WHERE created_at >= '2025-06-01' AND created_at < '2026-01-01'
GROUP BY m;
-- ③ 点查
SELECT * FROM orders_big WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 10;几个值得注意的细节：
- 聚合查询差 37—53 倍，即使 MySQL 的数据已经全部在内存中。瓶颈不在磁盘，而在于逐行处理整行数据。
- 点查方向完全相反。 MySQL 通过
(customer_id, created_at)索引直接定位到 10 行；ClickHouse 的排序键以created_at开头，按customer_id查询只能扫描大量数据块。它依然只花了 7ms，是因为扫描本身很快，但读取量说明这不是它擅长的访问方式。 - 存储占用：MySQL 中数据 870MB、索引 147MB；ClickHouse 压缩后 117MB（受重复备注列影响，真实数据的压缩比会低很多）。
每条查询各预热 3 次、采样 7 次，表中是最小值到最大值。MySQL 的计时在同一个会话内用 NOW(6) 前后相减得到，ClickHouse 的计时取自 system.query_log 的 query_duration_ms。
五、在 source 上跑报表的真实代价
报表慢只是表面现象，更危险的是它对在线业务的影响：
- 挤占 Buffer Pool：大范围扫描会把大量冷数据读进内存，挤走热点数据页，在线请求的缓存命中率下降。
- CPU 争抢：单条聚合查询占满一个核几秒钟，多个报表并发时，在线请求的延迟会明显上升。
- 阻碍 Undo 清理：一条跑几分钟的一致性读会持有旧快照，期间产生的 Undo 无法被 purge，原理见 InnoDB MVCC 与隔离级别。
- 锁与复制延迟：
INSERT ... SELECT生成汇总表时会对源表加锁；replica 上跑重查询会拖慢复制回放，实测见 MySQL 复制与延迟。
六、分阶段的演进路径
不必一开始就引入 OLAP 数据库，按数据量和报表复杂度逐步演进：
| 阶段 | 做法 | 适用 | 主要问题 |
|---|---|---|---|
| 1. 汇总表 | 定时任务把明细聚合成日报、月报表 | 报表固定、维度少 | 口径变化要重跑历史；新维度要改表 |
| 2. 只读 replica | 报表查询走 replica，与在线流量隔离 | 数据量千万级以内，查询几秒可以接受 | 聚合本身依然慢；重查询会拖慢复制 |
| 3. 列式分析库 | 通过 CDC 读取 Binlog，近实时同步到 ClickHouse、Doris、StarRocks 等 | 数据量上亿、维度多、需要自助分析 | 同步链路的运维、延迟与数据校验 |
判断是否进入第 3 阶段，可以看这几个信号：
- replica 上的报表查询经常超过 10 秒，或者需要不断加汇总表来应付新需求；
- replica 因为报表查询出现明显的复制延迟；
- 分析需求需要关联多个业务库的数据。
6.1 引入分析库之后要额外处理的事
- 同步方式：变更数据捕获（Change Data Capture，CDC）工具（如 Debezium、Canal、Flink CDC）读取 Binlog，要求
binlog_format=ROW，MySQL 8.4 默认就是。 - 数据延迟：分析库的数据通常落后秒级到分钟级，不适合用来做业务判断，比如库存校验。
- 更新与删除：列存对单行更新不友好，通常用「追加新版本 + 后台合并」的方式处理，查询时要注意去重语义。
- 口径校验：定期对比两边的行数和关键金额汇总，发现同步丢失或重复。
- 模型设计：把常用的关联提前做成宽表，避免在分析库里做大量多表关联。
七、常见误区
- 「OLAP 就是数据量大」：决定性因素是访问模式。大表上按主键点查依然是 OLTP 负载。
- 「给 MySQL 加足内存就能跑好报表」：实测数据全在内存中时，聚合查询依然比列存慢 30 倍以上。
- 「列式数据库什么都比 MySQL 快」：点查和单行更新正好是它的弱项。
- 「只读 replica 就能解决报表问题」：它隔离了影响，但没有让聚合变快，而且重查询会拖慢复制。
- 「分析库可以替代业务库查询」：分析库有同步延迟，也不提供业务需要的事务保证。
小结
OLTP 与 OLAP 的区别，归根到底是「少量整行」和「大量行的少数列」这两种访问模式，行存和列存分别为其中一种做了优化。数据量小时，汇总表和只读 replica 足以隔离报表对业务的影响；当聚合查询动辄十几秒、报表需求不断变化时，再通过 CDC 引入列式分析库，并为同步延迟和口径校验留出设计。
执行计划的读法与索引失效排查，见 读懂 Explain。
配套实验
- codesphere-labs/storage/mysql-oltp-olap：同一公式生成的 500 万行在 MySQL 与 ClickHouse 上的三条查询，结果逐字节比对，各采样 7 次，并记录 ClickHouse 的读取量与各列压缩大小（验证记录）
参考资料