ClickHouse匹配表排序的LIMIT 1查询为何扫描过多行？

原查询及执行信息 #

SELECT * FROM comp_intel_scrapes
order by
    client_slug,
    client_hotel_id,
    argset_id, 
    scrape_datetime,
    preferred_country,
    preferred_currency,
    adults, 
    children,
    nights,
    min_checkin_date,
    max_checkin_date
limit 1   

Elapsed: 0.004s
Read: 54 rows (8.84KB)

原因分析 #

ClickHouse表的排序键仅保证分区内数据有序，而非全局有序：

• 表数据被拆分为多个独立的存储分区（part），每个分区内按排序键有序，但跨分区的排序键没有全局顺序。
• 当执行全局ORDER BY LIMIT 1时，ClickHouse需要检查每个分区的排序键边界（min/max值），如果无法通过边界直接确定哪个分区包含全局第一行，就会读取每个分区的前若干行来做比较，这就是你看到扫描54行的原因——这些数据来自多个分区的头部，用于筛选全局最小/最大值对应的行。

解决办法 #

• 优化分区策略
  将排序键的前缀设为分区键（比如用client_slug作为分区键），这样每个分区内的后续排序键是有序的。查询时可直接定位到排序键最小的分区，仅在该分区内读取第一行，大幅减少扫描行数。

• 启用optimize_read_in_order参数
  在查询中添加该设置，让ClickHouse优先按排序键顺序读取数据，尽可能只读取必要部分：

  SELECT * FROM comp_intel_scrapes
  order by
      client_slug,
      client_hotel_id,
      argset_id, 
      scrape_datetime,
      preferred_country,
      preferred_currency,
      adults, 
      children,
      nights,
      min_checkin_date,
      max_checkin_date
  limit 1   
  SETTINGS optimize_read_in_order=1
  

• 全局排序表（谨慎使用）
  如果业务需要频繁查询全局TOP 1，可创建全局排序表（创建时指定ORDER BY且不设置PARTITION BY，或使用GLOBAL ORDER BY），但这种表写入性能会显著下降，仅适合写入频率低、以全局排序查询为主的场景。

• 预查询定位分区
  先找到包含全局最小排序键的分区，再在该分区内查询：

  -- 第一步：获取排序键最小的分区
  SELECT
      _partition_id,
      min(tuple(client_slug, client_hotel_id, argset_id, scrape_datetime, preferred_country, preferred_currency, adults, children, nights, min_checkin_date, max_checkin_date)) as min_sort_key
  FROM comp_intel_scrapes
  GROUP BY _partition_id
  ORDER BY min_sort_key
  LIMIT 1;
  
  -- 第二步：在目标分区内取第一行
  SELECT * FROM comp_intel_scrapes
  WHERE _partition_id = '目标分区ID'
  ORDER BY
      client_slug,
      client_hotel_id,
      argset_id, 
      scrape_datetime,
      preferred_country,
      preferred_currency,
      adults, 
      children,
      nights,
      min_checkin_date,
      max_checkin_date
  LIMIT 1;
  

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