ClickHouse大表按visit_id取Top20行的性能差异原因解析

你面对的是一张规模为6000万行、包含500个字段的宽表，其中visit_id是高基数、几乎无重复的字段，且该字段没有配置任何索引、排序键或主键。你在实际测试中观测到了三种差异极大的性能表现：

• 直接执行全字段排序取Top20的查询：

SELECT *
FROM large_table
ORDER BY visit_id DESC
LIMIT 20;

单条查询耗时长达25秒。

• 仅查询visit_id字段，执行完全相同的排序取Top20逻辑：

SELECT visit_id
FROM large_table
ORDER BY visit_id DESC
LIMIT 20;

查询仅需200ms即可完成。

• 将原全字段查询改写为WHERE IN关联子查询的形式：

SELECT *
FROM large_table a1 WHERE a1.visit_id IN(
  SELECT a2.visit_id
  FROM large_table a2
  ORDER BY a2.visit_id DESC
  LIMIT 20
);

改写后查询耗时仅600ms，性能较原语句提升了40倍。

这一系列表现是ClickHouse列式存储架构的执行逻辑、查询优化器的默认规则共同作用的结果，具体可以拆解为三层逻辑：

1. 原全字段查询慢的本质 #

ClickHouse是典型的列式存储引擎，每个字段的数据是独立按块存储在磁盘上的，查询时只会读取SQL中明确指定需要返回的字段对应的存储块。
但当你执行SELECT *语句时，查询执行引擎会直接判定：当前查询需要返回全表所有500个字段，因此从执行计划生成的第一步，就会规划“读取全表所有列的全量数据块到内存”的操作。哪怕后续你加了ORDER BY和LIMIT 20的截断规则，引擎也会先把6000万行对应的所有列数据全部读入内存，再统一执行全局排序，最后只保留前20行的结果。
这个过程里99.9999%的读盘开销都是完全无效的——最后你只需要20行的全字段数据，引擎却读了6000万行的所有列数据，累计需要读取的数据量可能达到几十甚至上百GB，自然要耗时25秒。

2. 单查visit_id速度快的原因 #

当你只查visit_id单个字段时，引擎只需要读取visit_id这一列对应的磁盘块。哪怕是6000万行的单列数据，整体体量通常也就几百MB级别，全部加载到内存后做一次全局排序取前20行，全程没有多余的读盘开销，自然只需要200ms就能完成。

3. 为什么改写为WHERE IN结构就能提速 #

这种写法相当于手动把优化器本该自动完成的步骤拆成了两个独立执行的阶段，强制触发了最高效的执行路径：

• 首先执行内层的子查询：逻辑和单查visit_id的场景完全一致，仅读取单列数据，200ms就能拿到排好序的20个visit_id值，整个子查询的结果集只有20个元素，体量可以忽略不计。
• 再执行外层查询：引擎拿到这20个目标visit_id之后，不会全量读取所有列的磁盘块，而是先扫描visit_id这一列，定位到这20个值对应的物理存储行号，再仅针对这20个行的位置，去读取其他499个字段对应的存储块。整个外层查询需要读取的其他列数据，只有20行对应的体量，和全量读6000万行所有列的开销相比差了几个数量级，因此整体600ms就能完成。

本质不是技术上做不到“先排序取目标行、再回表读其他列”的操作，而是当前版本的查询优化器的默认规则，没有为SELECT * + ORDER BY + LIMIT的场景触发这类优化：

• 优化器的成本模型默认判断：如果查询需要返回全表所有列，反正所有列的数据都要读，不如一次性把全量列读入内存再排序，省掉后续二次回表的开销。但这个判断完全没有考虑LIMIT的截断效应——当只需要取不到十万分之一的数据行时，二次回表带来的收益（不用读全量列）远大于回表本身的开销，这个逻辑覆盖并没有被加入到默认的优化规则中。
• 你写的WHERE IN子查询结构，相当于绕过了优化器对全字段查询的默认判断逻辑，强制让引擎先在仅单列的场景下完成排序截断的步骤，再针对极小的目标行集合做回表取数，自然就能实现数倍到数十倍的性能提升。

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