编程 ClickHouse 26.10:7/2 默认还是 3.5,但开关能变 3,IN set 还会落盘

2026-11-21 20:02:57

ClickHouse 26.10:7/2 默认还是 3.5,但开关能变 3,IN set 还会落盘

ClickHouse 26.10 于 2026 年 11 月 6 日发布,包含 104 个新特性、225 项性能优化、739 个 bug 修复。下面不复述清单,只挑四处会直接影响现有 SQL 行为和运维参数的改动。

项目:github.com/ClickHouse/ClickHouse
变更日志:clickhouse.com/docs/resources/changelogs/oss/2026
发布说明:clickhouse.com/blog/clickhouse-release-26-10

① 整数除法默认返回 Float64,SQL 兼容行为要手动开

在 ClickHouse 里,两个整数相除一直返回浮点数:

SELECT 7 / 2, -7 / 2, 7 / 2.0, toTypeName(7 / 2);
divide(7, 2)=3.5, divide(-7, 2)=-3.5, divide(7, 2.)=3.5, Float64

PostgreSQL、SQL Server、SQLite 都会截断成 3。26.10 为此新增 sql_compatible_division_for_integers:

SET sql_compatible_division_for_integers = 1;
SELECT 7 / 2, -7 / 2, 7 / 2.0, toTypeName(7 / 2);
divide(7, 2)=3, divide(-7, 2)=-3, divide(7, 2.)=3.5, UInt8

结果是向零截断并保持整数类型(-7 / 2 得 -3 而不是 -4),除数是浮点时仍返回浮点。开关打开后除零不再返回 inf,而是直接报错:

SET sql_compatible_division_for_integers = 1;
SELECT 1 / 0;
Code: 153. DB::Exception: Division by zero: In scope SELECT divideExact(1, 0) AS `divide(1, 0)`. (ILLEGAL_DIVISION)

该设置默认关闭,且只影响新建查询:视图和其他存储定义保持创建时的除法行为,打开它不会改变已有 view 的结果。

② IN 子查询的 set 默认会 spill 到盘

IN 带子查询时,ClickHouse 先构建子查询结果的 set,再用外层行去比对,set 大了很吃内存。26.10 允许把这个 set 写到磁盘:执行开始时按适用的 server/user 内存限制算出可用内存,query 内存用到一半就开始 spill,默认 max_bytes_ratio_before_external_set = 0.5。也可以用 max_bytes_before_external_set 设绝对阈值——ratio 计算不把 max_memory_usage 算进去,所以想在撞上限之前就开始写盘,就用这个;两个阈值都设成 0 则保持在内存里。

用英国房价数据集统计 2024 年成交、且该地址在 2024 年前也有成交的记录数:

SELECT count() AS repeat_address_sales_2024
FROM uk_price_paid
WHERE date >= '2024-01-01' AND date < '2025-01-01'
  AND (postcode1, postcode2, addr1, addr2) IN
  (SELECT postcode1, postcode2, addr1, addr2 FROM uk_price_paid WHERE date < '2024-01-01')
SETTINGS max_threads = 2, max_memory_usage = '4Gi', max_bytes_before_external_set = 0;

4 GiB 内存、关掉 spill:

558458
1 row in set. Elapsed: 5.777 sec. Processed 60.90 million rows, 966.34 MB.
Peak memory usage: 1.96 GiB.

子查询读了 2930 万条更早的成交记录。把上限压到 512 MiB,仍然关掉 spill:

SETTINGS max_threads = 2, max_memory_usage = '512Mi', max_bytes_before_external_set = 0;

Elapsed: 0.978 sec.
Received exception:
Code: 241. DB::Exception: Query memory limit exceeded: would use 537.51 MiB (attempt to allocate chunk of 63.96 MiB), maximum: 512.00 MiB: While executing CreatingSetsTransform. (MEMORY_LIMIT_EXCEEDED)

同一个查询,总内存仍是 512 MiB,给 128 MiB 的 spill 阈值:

SETTINGS max_threads = 2, max_memory_usage = '512Mi', max_bytes_before_external_set = '128Mi';
558458
1 row in set. Elapsed: 3.185 sec. Processed 60.90 million rows, 966.48 MB
Peak memory usage: 203.11 MiB.

128 MiB 是「什么时候开始往盘上写」的阈值,不是硬上限;查询本身还要内存读数据、处理数据,所以它要设在总上限之下。查询运行时按空格可以看到 profile events:

SetsSpilledToDisk: 1
ExternalSetWritePart: 4
ExternalSetCompressedBytes: 470342352

三组结果对照:

内存上限spill 阈值结果峰值内存
4 GiB关闭558,458 行,5.777s1.96 GiB
512 MiB关闭Code 241 MEMORY_LIMIT_EXCEEDED—
512 MiB128 MiB558,458 行,3.185s203.11 MiB

③ 任意 ON 条件的 JOIN

26.10 新增 allow_block_nested_loop_join,JOIN 的 ON 条件不再局限于等值比较,不等式、表达式都能写。执行方式是 block nested loop:左表每个 block 逐个和右表比对,代价等同于 CROSS JOIN 再套一层 filter。它是 planner 的最后手段——存在等值条件时仍然会走 hash join,只有凑不出等值条件才会落在这里。写这类查询前先用 EXPLAIN 看一眼计划,两张较大的表之间用它,基本就是自己给自己找麻烦。

④ 函数组合运算符 f | g 与 _ / _1 占位符

高阶函数里的 lambda 嵌套几层之后很难读。26.10 加了函数组合运算符,用 | 把函数串成管道。

下面的例子把 URL 转小写、去掉 www、取第一个有效子域、再转大写:

WITH ['https://WWW.ClickHouse.com/docs', 'https://GitHub.com/ClickHouse/ClickHouse', 'https://www.anthropic.com/news'] AS urls
SELECT
    arrayMap(x -> upper(firstSignificantSubdomain(domainWithoutWWW(lower(x)))), urls) AS with_lambda,
    arrayMap(lower | domainWithoutWWW | firstSignificantSubdomain | upper, urls) AS with_composition
FORMAT Vertical;
with_lambda: ['CLICKHOUSE','GITHUB','ANTHROPIC']
with_composition: ['CLICKHOUSE','GITHUB','ANTHROPIC']

两列结果一样,组合写法更像一串步骤。这些函数只接收管道传下来的那个值,所以不用写参数位置。

要把值放到函数的其他参数位置,用 _ 占位。比如 substring(_, 1, 3) 取传进来字符串的前三个字符:

WITH ['ClickHouse', 'Anthropic', 'OpenAI'] AS companies
SELECT
    arrayMap(x -> substring(upper(x), 1, 3), companies) AS with_lambda,
    arrayMap(upper | substring(_, 1, 3), companies) AS with_composition
FORMAT Vertical;
with_lambda: ['CLI','ANT','OPE']
with_composition: ['CLI','ANT','OPE']

高阶函数还可以同时接收多个等长数组,按位置逐个处理。这时 _1 表示第一个数组的元素,_2 表示第二个数组对应位置的元素,以此类推:

WITH
    [49.99, 189.00, 19.50] AS prices,
    [2, 1, 4] AS quantities
SELECT
    arrayMap(multiply(_1, _2) | toString | concat('$', _), prices, quantities) AS line_totals;
┌─line_totals─────────────┐
│ ['$99.98','$189','$78'] │
└─────────────────────────┘

占位符也可以脱离 | 单独使用,作为 lambda 的简写。_1 * _2 等价于 (price, quantity) -> price * quantity:

WITH
    ['Keyboard', 'Monitor', 'Mouse'] AS products,
    [49.99, 189.00, 19.50] AS prices,
    [2, 1, 4] AS quantities
SELECT
    arrayMap(_1 * _2, prices, quantities) AS line_totals,
    arrayMap(concat(_1, ' x', toString(_2)), products, quantities) AS order_lines
FORMAT Vertical;
line_totals: [99.98,189,78]
order_lines: ['Keyboard x2','Monitor x1','Mouse x4']

| 在查询分析阶段就会被展开成普通 lambda,用 EXPLAIN SYNTAX run_query_tree_passes = 1 能看到:

EXPLAIN SYNTAX run_query_tree_passes = 1
SELECT arrayMap(
    lower | domainWithoutWWW | firstSignificantSubdomain | upper,
    ['https://WWW.ClickHouse.com/docs']
) AS sites;
SELECT arrayMap(lambda(tuple(__composed_arg_1), upper(firstSignificantSubdomain(domainWithoutWWW(lower(__composed_arg_1))))), _CAST(['https://WWW.ClickHouse.com/docs'], 'Array(String)')) AS sites
FROM system.one AS __table1

组合链最终变成一个嵌套表达式相同的 lambda,两种写法执行方式完全一致,用哪种只看可读性。注意这里的 | 不是按位或;按位或仍是 bitOr。

三件不单独展开的改动

LowCardinality 多列 GROUP BY 快约 30%。 26.10 改为直接从字典序列化分组键,并缓存重复键组合的聚合状态查询:

SELECT county, district, town, postcode1, count() AS sales, avg(price) AS average_price
FROM uk_price_paid
GROUP BY county, district, town, postcode1
SETTINGS use_query_cache = 0
FORMAT Null;

26.9 三次 0.289s / 0.269s / 0.255s,峰值 170–178 MiB;26.10 三次 0.242s / 0.181s / 0.171s,峰值 124–128 MiB。最好成绩 255 ms 对 171 ms。

SQLite 格式读写。 新增对 SQLite 文件的读和写,小结果集可以直接落成单文件数据库,或反过来直接从 SQLite 文件查询。

quota 的 written_rows。 配额现在能按写入行数限制摄取:

CREATE QUOTA ingest_limit
FOR INTERVAL 1 minute MAX written_rows = 100000, query_inserts = 250
TO ingest;

一分钟内第二次插 6 万行就会超限:

Code: 201. DB::Exception: Quota for user `ingest` for 60s has been exceeded: written_rows = 120000/100000. Interval will end at 2026-10-23 14:00:00. Name of quota template: `ingest_limit`. (QUOTA_EXCEEDED)

被拒的插入不写入任何数据,表里仍是 6 万行。

升级前先查 system.query_log,筛一遍对整数做 / 的查询和带大 IN 子查询的语句,确认新的除法默认值、以及 set 落盘后的内存水位不会影响它们。

复制全文 生成海报 ClickHouse SQL 数据库 查询优化 OLAP

推荐文章

程序员茄子在线接单