Clickhouse两种CTE查询的性能对比及优化方法咨询

一、处理逻辑有没有差异？ #

说白了，这两种CTE写法完全等价，处理逻辑没区别。ClickHouse的语法解析器不管你是把CTE名写在AS前面还是后面，都会把mpi当成同一个预定义的临时数据集来处理——先算出唯一的master id，再把这个值传给第二个CTE当筛选条件。

二、哪个性能更优？ #

既然逻辑等价，那性能上没本质差别。ClickHouse的查询优化器对这两种写法的处理是一模一样的，不会因为命名位置的不同生成不同的执行计划。真正影响性能的是mpi这个CTE本身的计算效率（比如从数十亿条数据里取唯一master id的耗时），和写法无关。

三、针对大表的优化方法 #

针对你这种数十亿数据量的product_report表，优化重点要放在减少数据扫描量和提升筛选效率上，给你几个实用方向：

1. 优化mpi的计算效率 #

• 如果master_id是表的主键或有唯一约束，直接用SELECT master_id FROM product_report LIMIT 1代替DISTINCT去重，不用扫全表找唯一值；
• 给master_id建二级索引（比如bloom_filter或minmax类型），这样查唯一master id时能通过索引快速定位，避免全表扫描；
• 如果master_id是固定值或者可以提前确定，直接把值写死在查询里，省掉CTE计算的步骤。

2. 优化主查询的筛选逻辑 #

• 用PREWHERE代替WHERE先过滤master_id——PREWHERE会先执行过滤，只加载符合条件的数据列，比WHERE更适合大表场景；
• 给product_report做分区（比如按时间或master_id分区），这样查询时只扫目标分区，不用碰全表；
• 确保其他筛选条件（比如时间范围）能和master_id的过滤结合，利用索引合并进一步减少扫描量。

3. 其他实用技巧 #

• 如果master_id不会频繁变化，开启ClickHouse的查询缓存，把mpi的计算结果缓存起来，重复查询直接复用；
• 根据服务器CPU核心数调整max_threads参数（比如SET max_threads = 32），提升并行处理能力；
• 如果查询不需要实时结果，建物化视图提前计算好基于master_id的聚合数据，查的时候直接读物化视图就行，不用每次都扫大表。

内容的提问来源于stack exchange，提问作者thuleduy