上个月接了一个需求业务方要求在 ClickHouse 里做地理空间匹配让一批区域围栏和日增几十亿条的位置点做关联产出每个点命中的区域标签。当时心里想的是ClickHouse 不是有 geospatial 函数吗拿 pointInPolygon 不就完事了结果被现实教育了一整轮。原生函数确实在但把它落实到匹配适配这件事上中间隔着一个完整的工程层网格化降维、索引设计、自定义函数补齐、甚至要和外部空间引擎做协同。这篇文章就是那次项目从选型到落地的完整记录把你可能也要踩的坑、可以直接抄的 SQL 和表结构都放在里面。适合数据工程、后端开发以及所有准备在 ClickHouse 上做地理围栏、区域关联、邻域搜索的朋友。1. 先把原生能力摸透ClickHouse到底能做哪些空间运算1.1 原生空间函数盘点哪些开箱即用ClickHouse 不是专业的 GIS 数据库但它内置的空间函数数量其实比我预想的多。这里先把我们当时盘点过的、真正有用的一批函数列出来省得你再翻一遍文档函数作用注意点distance(x1, y1, x2, y2)平面直角坐标的欧氏距离直接传经纬度得到的是度不是米这个坑见后文geoDistance(lon1, lat1, lon2, lat2)WGS-84 球面距离单位米性能较好适合大规模粗筛greatCircleDistance(lon1, lat1, lon2, lat2)大圆距离更符合球面几何比 geoDistance 慢短距离场景精度差异不大pointInPolygon((lon, lat), [(x1,y1), ...])判断点是否在多边形内多边形顶点需按顺序闭合给出geohashEncode / geohashDecodeGeohash 编码与解码字符串类型前缀匹配可做粗筛geoToH3(lon, lat, resolution)经纬度映射到 H3 六边形网格返回 UInt64 的cell编号h3kRing(cell, k)取某cell周围的 K 环所有cell邻域查询最常用的函数h3ToGeo / h3ToGeoBoundary取cell中心点坐标、边界坐标可视化或精确几何重建时用h3ToChildren / h3ToParent网格下钻/上钻多级网格索引必备这些函数都是 ClickHouse 自带的不需要额外安装插件。如果你只是做零星的点面判断、距离计算它们已经够用。但请注意这些函数能帮你算帮不了你匹配。真正的匹配场景里表是百亿行级别的区域是几万甚至几十万个多边形你要解决的问题不是怎么算一个点是否在面里而是怎么让这种判断不在全表扫描的前提下完成。1.2 H3 网格ClickHouse 里最被低估的空间能力在 ClickHouse 的空间函数全家桶里H3 一族是最值得单独拎出来说的。H3 是多层级的六边形网格体系早期由出行领域推广开现在很多开源地理库都在用。它的核心优势有两个一是六边形在任何方向的邻居距离是均匀的不会出现四边形网格里对角邻居和上下邻居距离不一致的问题二是它有完整的层级关系上级网格可以准确拆成若干下级网格这让先粗筛、再精算的适配方案有了天然的数据基础。在 ClickHouse 里H3 相关函数覆盖得相当全。geoToH3(lon, lat, res)能在写入阶段就把一个经纬度点映射成 64 位整数网格编号h3kRing(cell, k)能一行代码取出中心网格周围 K 圈的所有网格h3ToChildren、h3ToParent能灵活地在不同分辨率之间上下钻取。我们后面所有适配方案几乎都是围绕 H3 展开的。这里给个最简单的示例感受一下SELECT geoToH3(113.1523, 23.1234, 9) AS cell, h3ToGeo(cell) AS center_point;输出会是一个整数 cell 编号和它对应的中心点经纬度。这个 cell 编号可以直接存成列写入时物化查询时用它做等值匹配、IN 过滤性能和走普通整数列没有区别。1.3 原生能力的边界为什么简单的函数满足不了真实匹配原生函数真正的问题不是功能少而是它没有解决规模这件事。第一ClickHouse 没有传统意义上的空间索引。你以为拿 pointInPolygon 去过滤一个大表实际效果是每一行都调用一次射线法判断。几千万行还能勉强跑几亿行、几十亿行的时候单次查询要读的数据量足够拖垮集群的 IO。第二没有 KNN最近邻能力。你想找某个点周边 500 米内的订单原生函数只能全表算距离再排序这在大表上是灾难。正确做法是先用网格缩小候选集再对小候选集做精确排序。第三没有面面相交、缓冲区拓扑这类几何运算。ClickHouse 的多边形是用数组表示的它能告诉你一个点是否在面里但它不能告诉你两个面是否相交、相交面积多大、一个面的缓冲区范围是什么。这些是 GIS 引擎的核心列式 OLAP 数据库短期内不会去实现。说直白点原生函数是零件不是解决方案。我们需要的是一层适配把空间关系转换成 ClickHouse 擅长处理的关系模型也就是网格等值匹配 join 过滤再用原生空间函数做最后一公里的精算。这个思路贯穿了后面所有章节。2. 真实业务里的三种匹配模式分别怎么落到SQL2.1 点面归属判定从全表 pointInPolygon到网格 join 后精算点面匹配是地理空间里最基础也最常见的需求位置点打行人判断它属于哪个区域围栏。我们一开始的直观想法是给区域表建一个三角形/多边形列然后直接 join 后调用 pointInPolygon。写出来大概是这样-- 不建议的生产写法区域表逐行与事件表走笛卡尔积 SELECT e.event_id, r.region_id FROM geo_events e JOIN region_polygons r ON pointInPolygon((e.lon, e.lat), r.polygon) WHERE e.event_time today();这个 SQL 在小数据量演示时没问题但真正跑在数亿行的表上它会让每一个区域多边形都匹配一遍所有点查询响应直接以分钟计。问题不在于函数慢而在于没有任何粗筛机制。正确的做法是把面提前网格化。我们在外部把每个区域多边形转换成若干个 H3 网格的集合存入一张叫region_cells的关联表。然后查询改成-- 第一步网格等值 join快速缩小候选集 WITH matched AS ( SELECT e.event_id, e.lon, e.lat, rc.region_id FROM geo_events e JOIN region_cells rc ON rc.h3_cell_id e.h3_cell_id WHERE e.event_time today() AND e.event_time today() INTERVAL 1 DAY ) -- 第二步对候选集做精确点面判断 SELECT m.event_id, m.region_id FROM matched m JOIN region_polygons r ON r.region_id m.region_id WHERE pointInPolygon((m.lon, m.lat), r.polygon);这个写法把点和所有区域算一遍变成了点和区域网格做一次整数等值 join。ClickHouse 对整数 join 的优化非常成熟能把扫描量砍掉两三个数量级。第二步的精确判断只会发生在少数候选行上性能压力完全可控。这套粗筛 精算的适配模式是我们整个项目的核心骨架后面几种匹配场景全是它的变体。2.2 点邻域查询司机、骑手、附近设备的半径搜索第二种典型场景是邻域查询给一个中心点找出周边若干公里范围内的对象。直接方案是geoDistance过滤但跟 pointInPolygon 一样它需要对全表做距离运算。我们用的还是 H3 的 kRing 思路。先把中心点映射成目标分辨率的 H3 网格再用h3kRing一次性取出周围 K 环的所有网格用IN条件先去查询表里捞候选最后才做精确的距离排序和截断WITH center AS ( SELECT geoToH3(113.1523, 23.1234, 9) AS cell ) SELECT e.event_id, e.lon, e.lat, geoDistance(113.1523, 23.1234, e.lon, e.lat) AS dist_m FROM geo_events e WHERE e.h3_cell_id IN (SELECT h3kRing(cell, 2) FROM center) AND e.event_time now() - INTERVAL 10 MINUTE ORDER BY dist_m LIMIT 200;这里的核心逻辑是kRing 取值越大候选范围越大、召回越全但后续精算的行数也越多k 越小则反之。我们当时在 9 级分辨率下取 k2候选范围大致能覆盖中心点周边几百米的距离对附近的骑手/订单这种业务已经足够。如果你要做 2 公里以上的邻域建议把分辨率降到 8 级再去取 ring而不是在 9 级里硬把 k 调大否则候选集会膨胀得很难看。2.3 轨迹与区域、面面覆盖用网格数组做集合运算第三类需求比前两种复杂一些但网格化后反而变得很关系化。轨迹与区域匹配本质是判断一条轨迹是不是穿过了某片区域、穿过了多少网格。我们当时把轨迹点表做了 H3 映射然后用聚合函数去重统计SELECT traj_id, countDistinct(h3_cell_id) AS visited_cells FROM trajectory_points WHERE h3_cell_id IN ( SELECT h3_cell_id FROM region_cells WHERE region_id 2001 ) AND ts BETWEEN 2024-06-01 00:00:00 AND 2024-06-01 23:59:59 GROUP BY traj_id HAVING visited_cells 5;HAVING visited_cells 5相当于给穿过区域设了一个最低网格覆盖数能有效过滤掉只是擦边的轨迹。这个阈值要根据你的网格分辨率调我们在 9 级分辨率下取 35 个网格比较合理如果换成 10 级阈值要相应调大。面面覆盖匹配就更有意思了。两个区域各自被网格化成两张表它们是否存在重叠区域直接对两张表的 cell 做内连接查交集SELECT a.region_id AS region_a, b.region_id AS region_b, count() AS inter_cell_count FROM region_cells a JOIN region_cells b ON a.h3_cell_id b.h3_cell_id WHERE a.region_id ! b.region_id GROUP BY region_a, region_b HAVING inter_cell_count 0;这个查询跑得非常快因为两边都是整数列等值连接。要注意的是精度问题两个面如果只是边界擦边可能会因为网格粒度而误报或漏报。解决方式是网格粗筛 边界精算也就是交集网格对应用到的多边形再做一次真正的几何相交判断这一步可以丢给外部 GIS 组件做法在第四章里会讲。3. 适配层设计H3网格化降维是性价比最高的起点3.1 为什么是 H3而不是 GeoHash 或四叉树网格化降维的思路本身并不新很多方案都能做但选型时我们仔细对比过三种主流网格最终锁定 H3 是有充分理由的。方案邻居查询层级操作类型适用性GeoHash需要自己做 8 邻域变形麻烦前缀字符串操作字符串兼容性最好但邻域计算繁琐H3h3kRing一行取出 K 环h3ToParent/Children直接上下钻整型ClickHouse 原生支持完善自定义四叉树自己实现自己实现自定灵活但工程成本高GeoHash 最大的问题是字符串编码前缀匹配可以做范围粗筛但取周边所有网格这种操作你需要自己写一套 8 邻域变形和前缀回退逻辑很容易在边界网格上出错。H3 用整型编码h3kRing直接返回一个数组h3ToGeoBoundary能拿到边界坐标所有操作都是现成的。整型还有一个额外的好处ClickHouse 对 UInt64 的等值匹配、IN 过滤、join 优化的效率和普通业务主键没有差别。3.2 网格映射的完整实践物化列、分区与索引网格映射听起来简单就是把经纬度换成网格编号但落地到表结构设计上有不少细节值得展开。我们当时的点事件表是这样设计的CREATE TABLE geo_events ( event_id UInt64, lon Float64, lat Float64, event_time DateTime, h3_cell_id UInt64 MATERIALIZED geoToH3(lon, lat, 9) ) ENGINE MergeTree PARTITION BY toYYYYMMDD(event_time) ORDER BY (event_time, h3_cell_id);关键点是MATERIALIZED geoToH3(lon, lat, 9)这是一列物化列。写入时 ClickHouse 自动根据经纬度算好网格编号查询时不需要再对每一行执行geoToH3而且物化列可以正常参与分区裁剪、索引过滤和 join。这是我们适配方案里性价比最高的一步写入多消耗一次计算查询少扫描几个数量级的数据。分区方面我们按天分区没有把空间字段放进分区键。原因是空间网格随时间变化不大但业务查询绝大多数都带时间范围按时间分区能保证最稳定的裁剪效果。排序键我们选了(event_time, h3_cell_id)这样时间范围过滤后同一时间片内的网格是局部聚集的空间 join 的磁盘读取是顺序的。如果业务里按区域查全量时间场景特别多可以针对性地在 h3_cell_id 上建一个布隆过滤器索引ALTER TABLE geo_events ADD INDEX idx_cell_bloom h3_cell_id TYPE bloom_filter(0.02) GRANULARITY 1;这种索引专治等值/IN 条件但无法利用排序键的场景。GRANULARITY 1表示按每个 granule默认 8192 行建一个布隆过滤器粒度越细过滤越精准但索引体积越大。我们生产环境调成 4兼顾了大小和过滤效果。3.3 匹配查询的完整演进先网格、后精算、再扩大把网格化适配从简单到复杂完整捋一遍实际上是一个三层演进很多团队卡在第二层和第三层的衔接上。第一层是纯网格匹配直接用h3_cell_id做等值 join 或 IN 过滤。这层最快但有一个天然缺陷——目标区域边界的点可能落在区域网格集合之外的紧邻网格里于是被漏掉。第二层就涉及扩大召回了。最直接的方法是增大 kRing 的环数从 k0 扩到 k1、k2。更精细的做法是用上钻下钻组合查询时把中心网格先用h3ToParent上钻一格再让候选表用h3ToChildren下钻回原精度这样相当于在目标精度上做了一次膨胀边缘漏配的概率会显著下降。-- 上钻再下钻扩大匹配范围 SELECT e.event_id, rc.region_id FROM geo_events e JOIN ( SELECT DISTINCT h3ToChildren(h3ToParent(geoToH3(113.1523, 23.1234, 9), 8), 9) AS expanded_cell ) expand ON expand.expanded_cell e.h3_cell_id JOIN region_cells rc ON rc.h3_cell_id e.h3_cell_id;第三层是最后兜底所有网格匹配都只负责候选最终的业务判定仍然要回到精确计算上。点面匹配用 pointInPolygon距离筛选用 geoDistance轨迹重叠交给外部 GIS。网格越粗候选集越大精算压力越高网格越细候选集越小但建索引的存储开销和网格转换误差会上升。所以粗筛 精算中的粗细搭配才是适配效果好坏的分水岭。3.4 网格粒度怎么选分辨率与误召回率H3 的分辨率从 0 到 15每升一级六边形面积大约缩小为上一级的七分之一。不用去记完整表格掌握这些常用级别就够了分辨率平均边长量级适用场景7约 1.2 km城市级圈选、跨城区域过滤8约 460 m商圈级匹配、邻域粗筛9约 174 mPOI 级、门店围栏、订单匹配10约 66 m楼宇级、车道级匹配我们默认用了 9 级理由是大部分围栏和点数据的精度都在这个量级9 级单网格覆盖半径对判断一个点是否路过这家店来说是足够的。如果要做的业务偏向附近 3 公里的人车货我们会在 8 级上做网格 join得到候选后再用 geoDistance 做 3 公里截断。网格粒度和误召回率是一对天然矛盾粒度越粗一个网格里可能同时包含大量不在业务范围内的点精算开销变大粒度越细一个多边形需要生成的网格数量剧增构建 region_cells 表的时间也会变长。我的经验是宁可让网格粗一点也不要过于相信细网格。因为细网格对多边形边界的拟合虽然更精确但代价是布隆过滤器膨胀、region_cells 表体积变大而粗网格那点精算开销在列式数据库里反而不值一提。4. 真正需要适配的脏活自定义函数与外部空间引擎协同4.1 可执行 UDF用一个 Python 脚本扩展空间算法网格化能解决 90% 的匹配需求但总有ClickHouse 没内置的测算需要补比如某一对坐标点的方位角、某个点在复杂曲线上的投影距离。这种量级的算法需求不值得引入一整套 GIS 服务但也不能让业务每次都在外面批量算。ClickHouse 的可执行 UDF 正好补上这个空档。简单说可执行 UDF 就是让 ClickHouse 在执行查询时把每行数据喂给一个外部程序比如 Python 脚本再把程序的输出当作函数返回值。生产环境推荐用executable_pool类型它会维护一个常驻子进程池避免每个查询都重新启动一个 Python 解释器。配置一个 UDF 只需要两步。第一步在/etc/clickhouse-server/config.d/udf_azimuth.xml里写函数声明clickhouse functions function typeexecutable_pool/type nameazimuth/name return_typeFloat64/return_type argument typeFloat64/type namelon1/name /argument argument typeFloat64/type namelat1/name /argument argument typeFloat64/type namelon2/name /argument argument typeFloat64/type namelat2/name /argument formatTabSeparated/format commandpython3 /opt/udf/azimuth.py/command /function /functions /clickhouse第二步写对应的 Python 脚本。它从 stdin 读入 TabSeparated 格式的行处理完把结果写到 stdout#!/usr/bin/env python3 import sys import math def azimuth(lon1, lat1, lon2, lat2): phi1 math.radians(lat1) phi2 math.radians(lat2) d_lon math.radians(lon2 - lon1) x math.sin(d_lon) * math.cos(phi2) y (math.cos(phi1) * math.sin(phi2) - math.sin(phi1) * math.cos(phi2) * math.cos(d_lon)) return (math.degrees(math.atan2(x, y)) 360.0) % 360.0 for line in sys.stdin: parts line.rstrip(\n).split(\t) if len(parts) ! 4: continue lon1, lat1, lon2, lat2 map(float, parts) print(f{azimuth(lon1, lat1, lon2, lat2):.6f}, flushTrue)改完配置后执行SYSTEM RELOAD FUNCTIONS就能直接在查询里用azimuth(lon1, lat1, lon2, lat2)了。不过我要泼一盆冷水UDF 是最后手段不是优先手段。它的行级调用开销决定了它只适合轻量计算一旦 UDF 脚本复杂到几十毫秒才能出一行结果你的查询就会明显变慢。能用geoDistance、pointInPolygon表达的绝不上 UDF必须用 UDF 的场景也要控制在简单映射级别。4.2 混合架构ClickHouse 负责存储与聚合外部空间引擎负责复杂几何计算网格化方案无力处理的问题是真正的几何计算面面相交的准确判断、多边形的缓冲区生成、拓扑关系修复、路网距离计算。这些功能连很多成熟的商业数据库都做不好指望 ClickHouse 更不现实。所以我们在适配层上做了一个非常明确的边界划分ClickHouse 只处理两类事一是海量点数据的存储和查询二是基于网格的等值匹配、聚合和粗筛。所有复杂几何计算全部交给外部空间组件我们用 PostGIS完成计算完的结果再以网格集合或匹配结果表的形式写回 ClickHouse。具体配合方式是区域多边形从 PostGIS 里导出来经过几何转网格的算法生成region_cells表落到 ClickHouse。查询阶段 ClickHouse 做网格 join 粗筛如果业务需要精确相交多边形列表把候选点对发回 PostGIS 做最终几何判定。这个回路里PostGIS 处理的不是海量原始数据而是经过 ClickHouse 压缩后的候选集两边各干各擅长的活。这种混合架构的落地代价是数据同步。小表比如区域表可以通过 ClickHouse 的 PostgreSQL 表引擎直连访问低频读没问题高频 join 的还是建议把结果表定期导入 ClickHouse 的 MergeTree避免每次查询都走一遍外部网络。4.3 空间匹配结果回填与异步补偿匹配不是一个纯实时动作。大多数业务场景里你需要在某个时间窗口结束后对一批事件做批量匹配然后把结果写回 ClickHouse供报表、推荐、运营后来查。我们在这一层踩到的最大的坑是回填任务失败后的重复计算。回填表的幂等设计一定要做。我们最终在匹配结果表里加了一个match_id维度每个批处理任务生成唯一 match_id回填时统一先清理该批次的旧记录再写入新结果CREATE TABLE match_result ( match_id UInt64, event_id UInt64, region_id UInt32, match_type String, process_time DateTime ) ENGINE MergeTree PARTITION BY toYYYYMMDD(process_time) ORDER BY (process_time, region_id); -- 回填任务幂等清理 ALTER TABLE match_result DELETE WHERE match_id 20240601001; -- 然后批量 INSERT 新结果如果匹配链路是实时的异步补偿就更有必要。我们当时的做法是网格粗筛在 ClickHouse 内实时完成返回的候选结果直接进入下游精确几何匹配如果偶尔超时把它丢到一个延迟队列由独立的补偿任务在低峰期补算补算结果通过match_id合并进主表。这一步保证了实时链路的可用性又不会因为个别复杂多边形把整个查询拖垮。5. 生产环境的调优记录与常见坑5.1 排序键与布隆过滤器让空间查询从扫全表到查子集网格化适配做对了SQL 也写对了最后还差一道工序让 ClickHouse 的存储层能配合你的空间查询。我们线上有两张 10 亿行以上的事件表同样的匹配 SQL排序键设计合理与否反应时间差了 5 倍以上。经验是排序键的字段顺序要跟业务查询里的过滤强度排。我们大部分空间匹配查询都同时带时间条件和网格条件所以ORDER BY (event_time, h3_cell_id)是首选。如果你有一套固定网格集合需要频繁全量匹配比如按商圈划分的常驻候选区可以考虑把排序键调成(h3_cell_id, event_time)牺牲时间过滤的裁剪效率换空间等值匹配的极致性能。布隆过滤器索引的命中情况可以用EXPLAIN indexes 1验证。比如EXPLAIN indexes 1 SELECT event_id FROM geo_events WHERE h3_cell_id IN (SELECT h3kRing(geoToH3(113.1523, 23.1234, 9), 2)) AND event_time today();看输出里Index那颗树有没有走到idx_cell_bloom。如果走到了说明索引建得对路。如果没走到大概率是排序键里已经通过时间裁剪把扫描范围压得很小索引收益不明显那就不用纠结。5.2 坐标系混用、边界点与距离函数选错这类问题不会让系统报错但会让匹配结果在看起来没毛病的情况下产生系统性偏差。我们遇到过的三个典型案例值得逐个说。第一个是坐标系混用。ClickHouse 的geoDistance默认按 WGS-84 椭球体计算。但如果你的部分上游数据来自某些地图服务经纬度可能在入库前已经被做了加密偏移导致同一个真实点位在不同来源里的坐标差几百米。这个几百米的误差对网格匹配是致命的——9 级网格边长才 170 米左右直接导致区域归属错乱。处理办法没有捷径入库前统一坐标系所有外部数据先经过坐标转换服务转换成统一的 WGS-84 经纬度再进 ClickHouse。第二个是距离函数选错的隐蔽问题。distance()这个函数在很多人手里就是算两个经纬度点的距离但实际上它算的是平面直角坐标的欧几里得距离。你传经纬度进去得到的结果单位是度不是米。纬度越高1 度经度对应的真实距离越短在纬度 60 度时1 度经度大约只有 55.8 公里直接用 111 公里去换算会差出一倍。如果你需要的是地球上两点的真实距离要么用geoDistance要么确保数据是投影坐标系下的平面坐标再使用distance。第三个是网格边界漏匹配。点正好落在两个网格交界处或者落在区域网格集合最外侧网格的边界上你的等值 join 就会漏掉它。网格不是万能的它把连续空间离散化了离散化就必然有边界问题。统一做法是查询侧对候选网格做h3kRing(cell, 1)扩张一圈再 join。代价是候选行数增加但召回率的提升是实打实的。我们当时为了排查为什么一个明显在商圈内的点没匹配上花了整整一天最后发现就是边界网格的问题。5.3 性能实测与容量规划一点参考数字与教训最后放一组我们实测的参考数据数字本身因数据分布和集群配置而异但数量级可以给你一个心理预期。我们的一个典型场景是 10 亿行轨迹点和 2 万个区域围栏做归属匹配。优化前区域多边形直接 join 全部数据 pointInPolygon 逐行判断每次查询要扫描完整表14 个节点的集群也要跑将近 20 秒而且会把磁盘 IO 打满旁边其他查询跟着遭殃。优化后区域表预先生成 9 级 H3 网格集合匹配走h3_cell_id整数等值 join命中行数降到千万级90% 以上的查询在 500 毫秒内返回。加上布隆过滤器索引后带时间过滤的查询基本在 100 毫秒级别。容量规划上网格化映射本身是有成本的每个事件多一个 8 字节的 UInt64 物化列再加上布隆过滤器索引整表存储开销增加大约 10%20%。这笔开销换来的是空间查询能力从不可用变成秒级非常划算。另外region_cells表虽然看起来是小表但当区域数量多、网格分辨率高的时候也会膨胀阿里 9 级、2 万个区域生成的网格集合可能上千万行建议对它也做分区和排序键规划不要只给主事件表上索引。这点我特别想强调很多人做适配方案的时候只考虑业务主表怎么设计把辅助表当成临时表随便建。实际上 region_cells 这类辅助表在每次匹配查询里都要被高效读取它的存储布局直接影响 join 性能。我们在生产环境把 region_cells 的排序键设置为(h3_cell_id, region_id)让相同网格的所有区域连续存放join 时的缓存命中率立刻上了一个台阶。回看这次 ClickHouse 地理空间匹配适配的项目我最大的体会是做空间数据在 OLAP 引擎上的落地不要试图让 ClickHouse 变成一个 GIS 数据库而是要把空间问题翻译成它擅长处理的整数关系问题。H3 网格就是这个翻译器。网格粗筛、函数精算再到外部引擎兜底复杂几何这一层适配做完百亿行表上的空间匹配才能真正跑出列式数据库该有的速度。如果你也在做类似的事情建议先从一张事件表、一张网格关联表、三套 SQL 模式开始跑通之后再考虑 UDF 和混合架构步子太大容易把自己绕进去。
上个月接了一个需求业务方要求在 ClickHouse 里做地理空间匹配让一批区域围栏和日增几十亿条的位置点做关联产出每个点命中的区域标签。当时心里想的是ClickHouse 不是有 geospatial 函数吗拿 pointInPolygon 不就完事了结果被现实教育了一整轮。原生函数确实在但把它落实到匹配适配这件事上中间隔着一个完整的工程层网格化降维、索引设计、自定义函数补齐、甚至要和外部空间引擎做协同。这篇文章就是那次项目从选型到落地的完整记录把你可能也要踩的坑、可以直接抄的 SQL 和表结构都放在里面。适合数据工程、后端开发以及所有准备在 ClickHouse 上做地理围栏、区域关联、邻域搜索的朋友。1. 先把原生能力摸透ClickHouse到底能做哪些空间运算1.1 原生空间函数盘点哪些开箱即用ClickHouse 不是专业的 GIS 数据库但它内置的空间函数数量其实比我预想的多。这里先把我们当时盘点过的、真正有用的一批函数列出来省得你再翻一遍文档函数作用注意点distance(x1, y1, x2, y2)平面直角坐标的欧氏距离直接传经纬度得到的是度不是米这个坑见后文geoDistance(lon1, lat1, lon2, lat2)WGS-84 球面距离单位米性能较好适合大规模粗筛greatCircleDistance(lon1, lat1, lon2, lat2)大圆距离更符合球面几何比 geoDistance 慢短距离场景精度差异不大pointInPolygon((lon, lat), [(x1,y1), ...])判断点是否在多边形内多边形顶点需按顺序闭合给出geohashEncode / geohashDecodeGeohash 编码与解码字符串类型前缀匹配可做粗筛geoToH3(lon, lat, resolution)经纬度映射到 H3 六边形网格返回 UInt64 的cell编号h3kRing(cell, k)取某cell周围的 K 环所有cell邻域查询最常用的函数h3ToGeo / h3ToGeoBoundary取cell中心点坐标、边界坐标可视化或精确几何重建时用h3ToChildren / h3ToParent网格下钻/上钻多级网格索引必备这些函数都是 ClickHouse 自带的不需要额外安装插件。如果你只是做零星的点面判断、距离计算它们已经够用。但请注意这些函数能帮你算帮不了你匹配。真正的匹配场景里表是百亿行级别的区域是几万甚至几十万个多边形你要解决的问题不是怎么算一个点是否在面里而是怎么让这种判断不在全表扫描的前提下完成。1.2 H3 网格ClickHouse 里最被低估的空间能力在 ClickHouse 的空间函数全家桶里H3 一族是最值得单独拎出来说的。H3 是多层级的六边形网格体系早期由出行领域推广开现在很多开源地理库都在用。它的核心优势有两个一是六边形在任何方向的邻居距离是均匀的不会出现四边形网格里对角邻居和上下邻居距离不一致的问题二是它有完整的层级关系上级网格可以准确拆成若干下级网格这让先粗筛、再精算的适配方案有了天然的数据基础。在 ClickHouse 里H3 相关函数覆盖得相当全。geoToH3(lon, lat, res)能在写入阶段就把一个经纬度点映射成 64 位整数网格编号h3kRing(cell, k)能一行代码取出中心网格周围 K 圈的所有网格h3ToChildren、h3ToParent能灵活地在不同分辨率之间上下钻取。我们后面所有适配方案几乎都是围绕 H3 展开的。这里给个最简单的示例感受一下SELECT geoToH3(113.1523, 23.1234, 9) AS cell, h3ToGeo(cell) AS center_point;输出会是一个整数 cell 编号和它对应的中心点经纬度。这个 cell 编号可以直接存成列写入时物化查询时用它做等值匹配、IN 过滤性能和走普通整数列没有区别。1.3 原生能力的边界为什么简单的函数满足不了真实匹配原生函数真正的问题不是功能少而是它没有解决规模这件事。第一ClickHouse 没有传统意义上的空间索引。你以为拿 pointInPolygon 去过滤一个大表实际效果是每一行都调用一次射线法判断。几千万行还能勉强跑几亿行、几十亿行的时候单次查询要读的数据量足够拖垮集群的 IO。第二没有 KNN最近邻能力。你想找某个点周边 500 米内的订单原生函数只能全表算距离再排序这在大表上是灾难。正确做法是先用网格缩小候选集再对小候选集做精确排序。第三没有面面相交、缓冲区拓扑这类几何运算。ClickHouse 的多边形是用数组表示的它能告诉你一个点是否在面里但它不能告诉你两个面是否相交、相交面积多大、一个面的缓冲区范围是什么。这些是 GIS 引擎的核心列式 OLAP 数据库短期内不会去实现。说直白点原生函数是零件不是解决方案。我们需要的是一层适配把空间关系转换成 ClickHouse 擅长处理的关系模型也就是网格等值匹配 join 过滤再用原生空间函数做最后一公里的精算。这个思路贯穿了后面所有章节。2. 真实业务里的三种匹配模式分别怎么落到SQL2.1 点面归属判定从全表 pointInPolygon到网格 join 后精算点面匹配是地理空间里最基础也最常见的需求位置点打行人判断它属于哪个区域围栏。我们一开始的直观想法是给区域表建一个三角形/多边形列然后直接 join 后调用 pointInPolygon。写出来大概是这样-- 不建议的生产写法区域表逐行与事件表走笛卡尔积 SELECT e.event_id, r.region_id FROM geo_events e JOIN region_polygons r ON pointInPolygon((e.lon, e.lat), r.polygon) WHERE e.event_time today();这个 SQL 在小数据量演示时没问题但真正跑在数亿行的表上它会让每一个区域多边形都匹配一遍所有点查询响应直接以分钟计。问题不在于函数慢而在于没有任何粗筛机制。正确的做法是把面提前网格化。我们在外部把每个区域多边形转换成若干个 H3 网格的集合存入一张叫region_cells的关联表。然后查询改成-- 第一步网格等值 join快速缩小候选集 WITH matched AS ( SELECT e.event_id, e.lon, e.lat, rc.region_id FROM geo_events e JOIN region_cells rc ON rc.h3_cell_id e.h3_cell_id WHERE e.event_time today() AND e.event_time today() INTERVAL 1 DAY ) -- 第二步对候选集做精确点面判断 SELECT m.event_id, m.region_id FROM matched m JOIN region_polygons r ON r.region_id m.region_id WHERE pointInPolygon((m.lon, m.lat), r.polygon);这个写法把点和所有区域算一遍变成了点和区域网格做一次整数等值 join。ClickHouse 对整数 join 的优化非常成熟能把扫描量砍掉两三个数量级。第二步的精确判断只会发生在少数候选行上性能压力完全可控。这套粗筛 精算的适配模式是我们整个项目的核心骨架后面几种匹配场景全是它的变体。2.2 点邻域查询司机、骑手、附近设备的半径搜索第二种典型场景是邻域查询给一个中心点找出周边若干公里范围内的对象。直接方案是geoDistance过滤但跟 pointInPolygon 一样它需要对全表做距离运算。我们用的还是 H3 的 kRing 思路。先把中心点映射成目标分辨率的 H3 网格再用h3kRing一次性取出周围 K 环的所有网格用IN条件先去查询表里捞候选最后才做精确的距离排序和截断WITH center AS ( SELECT geoToH3(113.1523, 23.1234, 9) AS cell ) SELECT e.event_id, e.lon, e.lat, geoDistance(113.1523, 23.1234, e.lon, e.lat) AS dist_m FROM geo_events e WHERE e.h3_cell_id IN (SELECT h3kRing(cell, 2) FROM center) AND e.event_time now() - INTERVAL 10 MINUTE ORDER BY dist_m LIMIT 200;这里的核心逻辑是kRing 取值越大候选范围越大、召回越全但后续精算的行数也越多k 越小则反之。我们当时在 9 级分辨率下取 k2候选范围大致能覆盖中心点周边几百米的距离对附近的骑手/订单这种业务已经足够。如果你要做 2 公里以上的邻域建议把分辨率降到 8 级再去取 ring而不是在 9 级里硬把 k 调大否则候选集会膨胀得很难看。2.3 轨迹与区域、面面覆盖用网格数组做集合运算第三类需求比前两种复杂一些但网格化后反而变得很关系化。轨迹与区域匹配本质是判断一条轨迹是不是穿过了某片区域、穿过了多少网格。我们当时把轨迹点表做了 H3 映射然后用聚合函数去重统计SELECT traj_id, countDistinct(h3_cell_id) AS visited_cells FROM trajectory_points WHERE h3_cell_id IN ( SELECT h3_cell_id FROM region_cells WHERE region_id 2001 ) AND ts BETWEEN 2024-06-01 00:00:00 AND 2024-06-01 23:59:59 GROUP BY traj_id HAVING visited_cells 5;HAVING visited_cells 5相当于给穿过区域设了一个最低网格覆盖数能有效过滤掉只是擦边的轨迹。这个阈值要根据你的网格分辨率调我们在 9 级分辨率下取 35 个网格比较合理如果换成 10 级阈值要相应调大。面面覆盖匹配就更有意思了。两个区域各自被网格化成两张表它们是否存在重叠区域直接对两张表的 cell 做内连接查交集SELECT a.region_id AS region_a, b.region_id AS region_b, count() AS inter_cell_count FROM region_cells a JOIN region_cells b ON a.h3_cell_id b.h3_cell_id WHERE a.region_id ! b.region_id GROUP BY region_a, region_b HAVING inter_cell_count 0;这个查询跑得非常快因为两边都是整数列等值连接。要注意的是精度问题两个面如果只是边界擦边可能会因为网格粒度而误报或漏报。解决方式是网格粗筛 边界精算也就是交集网格对应用到的多边形再做一次真正的几何相交判断这一步可以丢给外部 GIS 组件做法在第四章里会讲。3. 适配层设计H3网格化降维是性价比最高的起点3.1 为什么是 H3而不是 GeoHash 或四叉树网格化降维的思路本身并不新很多方案都能做但选型时我们仔细对比过三种主流网格最终锁定 H3 是有充分理由的。方案邻居查询层级操作类型适用性GeoHash需要自己做 8 邻域变形麻烦前缀字符串操作字符串兼容性最好但邻域计算繁琐H3h3kRing一行取出 K 环h3ToParent/Children直接上下钻整型ClickHouse 原生支持完善自定义四叉树自己实现自己实现自定灵活但工程成本高GeoHash 最大的问题是字符串编码前缀匹配可以做范围粗筛但取周边所有网格这种操作你需要自己写一套 8 邻域变形和前缀回退逻辑很容易在边界网格上出错。H3 用整型编码h3kRing直接返回一个数组h3ToGeoBoundary能拿到边界坐标所有操作都是现成的。整型还有一个额外的好处ClickHouse 对 UInt64 的等值匹配、IN 过滤、join 优化的效率和普通业务主键没有差别。3.2 网格映射的完整实践物化列、分区与索引网格映射听起来简单就是把经纬度换成网格编号但落地到表结构设计上有不少细节值得展开。我们当时的点事件表是这样设计的CREATE TABLE geo_events ( event_id UInt64, lon Float64, lat Float64, event_time DateTime, h3_cell_id UInt64 MATERIALIZED geoToH3(lon, lat, 9) ) ENGINE MergeTree PARTITION BY toYYYYMMDD(event_time) ORDER BY (event_time, h3_cell_id);关键点是MATERIALIZED geoToH3(lon, lat, 9)这是一列物化列。写入时 ClickHouse 自动根据经纬度算好网格编号查询时不需要再对每一行执行geoToH3而且物化列可以正常参与分区裁剪、索引过滤和 join。这是我们适配方案里性价比最高的一步写入多消耗一次计算查询少扫描几个数量级的数据。分区方面我们按天分区没有把空间字段放进分区键。原因是空间网格随时间变化不大但业务查询绝大多数都带时间范围按时间分区能保证最稳定的裁剪效果。排序键我们选了(event_time, h3_cell_id)这样时间范围过滤后同一时间片内的网格是局部聚集的空间 join 的磁盘读取是顺序的。如果业务里按区域查全量时间场景特别多可以针对性地在 h3_cell_id 上建一个布隆过滤器索引ALTER TABLE geo_events ADD INDEX idx_cell_bloom h3_cell_id TYPE bloom_filter(0.02) GRANULARITY 1;这种索引专治等值/IN 条件但无法利用排序键的场景。GRANULARITY 1表示按每个 granule默认 8192 行建一个布隆过滤器粒度越细过滤越精准但索引体积越大。我们生产环境调成 4兼顾了大小和过滤效果。3.3 匹配查询的完整演进先网格、后精算、再扩大把网格化适配从简单到复杂完整捋一遍实际上是一个三层演进很多团队卡在第二层和第三层的衔接上。第一层是纯网格匹配直接用h3_cell_id做等值 join 或 IN 过滤。这层最快但有一个天然缺陷——目标区域边界的点可能落在区域网格集合之外的紧邻网格里于是被漏掉。第二层就涉及扩大召回了。最直接的方法是增大 kRing 的环数从 k0 扩到 k1、k2。更精细的做法是用上钻下钻组合查询时把中心网格先用h3ToParent上钻一格再让候选表用h3ToChildren下钻回原精度这样相当于在目标精度上做了一次膨胀边缘漏配的概率会显著下降。-- 上钻再下钻扩大匹配范围 SELECT e.event_id, rc.region_id FROM geo_events e JOIN ( SELECT DISTINCT h3ToChildren(h3ToParent(geoToH3(113.1523, 23.1234, 9), 8), 9) AS expanded_cell ) expand ON expand.expanded_cell e.h3_cell_id JOIN region_cells rc ON rc.h3_cell_id e.h3_cell_id;第三层是最后兜底所有网格匹配都只负责候选最终的业务判定仍然要回到精确计算上。点面匹配用 pointInPolygon距离筛选用 geoDistance轨迹重叠交给外部 GIS。网格越粗候选集越大精算压力越高网格越细候选集越小但建索引的存储开销和网格转换误差会上升。所以粗筛 精算中的粗细搭配才是适配效果好坏的分水岭。3.4 网格粒度怎么选分辨率与误召回率H3 的分辨率从 0 到 15每升一级六边形面积大约缩小为上一级的七分之一。不用去记完整表格掌握这些常用级别就够了分辨率平均边长量级适用场景7约 1.2 km城市级圈选、跨城区域过滤8约 460 m商圈级匹配、邻域粗筛9约 174 mPOI 级、门店围栏、订单匹配10约 66 m楼宇级、车道级匹配我们默认用了 9 级理由是大部分围栏和点数据的精度都在这个量级9 级单网格覆盖半径对判断一个点是否路过这家店来说是足够的。如果要做的业务偏向附近 3 公里的人车货我们会在 8 级上做网格 join得到候选后再用 geoDistance 做 3 公里截断。网格粒度和误召回率是一对天然矛盾粒度越粗一个网格里可能同时包含大量不在业务范围内的点精算开销变大粒度越细一个多边形需要生成的网格数量剧增构建 region_cells 表的时间也会变长。我的经验是宁可让网格粗一点也不要过于相信细网格。因为细网格对多边形边界的拟合虽然更精确但代价是布隆过滤器膨胀、region_cells 表体积变大而粗网格那点精算开销在列式数据库里反而不值一提。4. 真正需要适配的脏活自定义函数与外部空间引擎协同4.1 可执行 UDF用一个 Python 脚本扩展空间算法网格化能解决 90% 的匹配需求但总有ClickHouse 没内置的测算需要补比如某一对坐标点的方位角、某个点在复杂曲线上的投影距离。这种量级的算法需求不值得引入一整套 GIS 服务但也不能让业务每次都在外面批量算。ClickHouse 的可执行 UDF 正好补上这个空档。简单说可执行 UDF 就是让 ClickHouse 在执行查询时把每行数据喂给一个外部程序比如 Python 脚本再把程序的输出当作函数返回值。生产环境推荐用executable_pool类型它会维护一个常驻子进程池避免每个查询都重新启动一个 Python 解释器。配置一个 UDF 只需要两步。第一步在/etc/clickhouse-server/config.d/udf_azimuth.xml里写函数声明clickhouse functions function typeexecutable_pool/type nameazimuth/name return_typeFloat64/return_type argument typeFloat64/type namelon1/name /argument argument typeFloat64/type namelat1/name /argument argument typeFloat64/type namelon2/name /argument argument typeFloat64/type namelat2/name /argument formatTabSeparated/format commandpython3 /opt/udf/azimuth.py/command /function /functions /clickhouse第二步写对应的 Python 脚本。它从 stdin 读入 TabSeparated 格式的行处理完把结果写到 stdout#!/usr/bin/env python3 import sys import math def azimuth(lon1, lat1, lon2, lat2): phi1 math.radians(lat1) phi2 math.radians(lat2) d_lon math.radians(lon2 - lon1) x math.sin(d_lon) * math.cos(phi2) y (math.cos(phi1) * math.sin(phi2) - math.sin(phi1) * math.cos(phi2) * math.cos(d_lon)) return (math.degrees(math.atan2(x, y)) 360.0) % 360.0 for line in sys.stdin: parts line.rstrip(\n).split(\t) if len(parts) ! 4: continue lon1, lat1, lon2, lat2 map(float, parts) print(f{azimuth(lon1, lat1, lon2, lat2):.6f}, flushTrue)改完配置后执行SYSTEM RELOAD FUNCTIONS就能直接在查询里用azimuth(lon1, lat1, lon2, lat2)了。不过我要泼一盆冷水UDF 是最后手段不是优先手段。它的行级调用开销决定了它只适合轻量计算一旦 UDF 脚本复杂到几十毫秒才能出一行结果你的查询就会明显变慢。能用geoDistance、pointInPolygon表达的绝不上 UDF必须用 UDF 的场景也要控制在简单映射级别。4.2 混合架构ClickHouse 负责存储与聚合外部空间引擎负责复杂几何计算网格化方案无力处理的问题是真正的几何计算面面相交的准确判断、多边形的缓冲区生成、拓扑关系修复、路网距离计算。这些功能连很多成熟的商业数据库都做不好指望 ClickHouse 更不现实。所以我们在适配层上做了一个非常明确的边界划分ClickHouse 只处理两类事一是海量点数据的存储和查询二是基于网格的等值匹配、聚合和粗筛。所有复杂几何计算全部交给外部空间组件我们用 PostGIS完成计算完的结果再以网格集合或匹配结果表的形式写回 ClickHouse。具体配合方式是区域多边形从 PostGIS 里导出来经过几何转网格的算法生成region_cells表落到 ClickHouse。查询阶段 ClickHouse 做网格 join 粗筛如果业务需要精确相交多边形列表把候选点对发回 PostGIS 做最终几何判定。这个回路里PostGIS 处理的不是海量原始数据而是经过 ClickHouse 压缩后的候选集两边各干各擅长的活。这种混合架构的落地代价是数据同步。小表比如区域表可以通过 ClickHouse 的 PostgreSQL 表引擎直连访问低频读没问题高频 join 的还是建议把结果表定期导入 ClickHouse 的 MergeTree避免每次查询都走一遍外部网络。4.3 空间匹配结果回填与异步补偿匹配不是一个纯实时动作。大多数业务场景里你需要在某个时间窗口结束后对一批事件做批量匹配然后把结果写回 ClickHouse供报表、推荐、运营后来查。我们在这一层踩到的最大的坑是回填任务失败后的重复计算。回填表的幂等设计一定要做。我们最终在匹配结果表里加了一个match_id维度每个批处理任务生成唯一 match_id回填时统一先清理该批次的旧记录再写入新结果CREATE TABLE match_result ( match_id UInt64, event_id UInt64, region_id UInt32, match_type String, process_time DateTime ) ENGINE MergeTree PARTITION BY toYYYYMMDD(process_time) ORDER BY (process_time, region_id); -- 回填任务幂等清理 ALTER TABLE match_result DELETE WHERE match_id 20240601001; -- 然后批量 INSERT 新结果如果匹配链路是实时的异步补偿就更有必要。我们当时的做法是网格粗筛在 ClickHouse 内实时完成返回的候选结果直接进入下游精确几何匹配如果偶尔超时把它丢到一个延迟队列由独立的补偿任务在低峰期补算补算结果通过match_id合并进主表。这一步保证了实时链路的可用性又不会因为个别复杂多边形把整个查询拖垮。5. 生产环境的调优记录与常见坑5.1 排序键与布隆过滤器让空间查询从扫全表到查子集网格化适配做对了SQL 也写对了最后还差一道工序让 ClickHouse 的存储层能配合你的空间查询。我们线上有两张 10 亿行以上的事件表同样的匹配 SQL排序键设计合理与否反应时间差了 5 倍以上。经验是排序键的字段顺序要跟业务查询里的过滤强度排。我们大部分空间匹配查询都同时带时间条件和网格条件所以ORDER BY (event_time, h3_cell_id)是首选。如果你有一套固定网格集合需要频繁全量匹配比如按商圈划分的常驻候选区可以考虑把排序键调成(h3_cell_id, event_time)牺牲时间过滤的裁剪效率换空间等值匹配的极致性能。布隆过滤器索引的命中情况可以用EXPLAIN indexes 1验证。比如EXPLAIN indexes 1 SELECT event_id FROM geo_events WHERE h3_cell_id IN (SELECT h3kRing(geoToH3(113.1523, 23.1234, 9), 2)) AND event_time today();看输出里Index那颗树有没有走到idx_cell_bloom。如果走到了说明索引建得对路。如果没走到大概率是排序键里已经通过时间裁剪把扫描范围压得很小索引收益不明显那就不用纠结。5.2 坐标系混用、边界点与距离函数选错这类问题不会让系统报错但会让匹配结果在看起来没毛病的情况下产生系统性偏差。我们遇到过的三个典型案例值得逐个说。第一个是坐标系混用。ClickHouse 的geoDistance默认按 WGS-84 椭球体计算。但如果你的部分上游数据来自某些地图服务经纬度可能在入库前已经被做了加密偏移导致同一个真实点位在不同来源里的坐标差几百米。这个几百米的误差对网格匹配是致命的——9 级网格边长才 170 米左右直接导致区域归属错乱。处理办法没有捷径入库前统一坐标系所有外部数据先经过坐标转换服务转换成统一的 WGS-84 经纬度再进 ClickHouse。第二个是距离函数选错的隐蔽问题。distance()这个函数在很多人手里就是算两个经纬度点的距离但实际上它算的是平面直角坐标的欧几里得距离。你传经纬度进去得到的结果单位是度不是米。纬度越高1 度经度对应的真实距离越短在纬度 60 度时1 度经度大约只有 55.8 公里直接用 111 公里去换算会差出一倍。如果你需要的是地球上两点的真实距离要么用geoDistance要么确保数据是投影坐标系下的平面坐标再使用distance。第三个是网格边界漏匹配。点正好落在两个网格交界处或者落在区域网格集合最外侧网格的边界上你的等值 join 就会漏掉它。网格不是万能的它把连续空间离散化了离散化就必然有边界问题。统一做法是查询侧对候选网格做h3kRing(cell, 1)扩张一圈再 join。代价是候选行数增加但召回率的提升是实打实的。我们当时为了排查为什么一个明显在商圈内的点没匹配上花了整整一天最后发现就是边界网格的问题。5.3 性能实测与容量规划一点参考数字与教训最后放一组我们实测的参考数据数字本身因数据分布和集群配置而异但数量级可以给你一个心理预期。我们的一个典型场景是 10 亿行轨迹点和 2 万个区域围栏做归属匹配。优化前区域多边形直接 join 全部数据 pointInPolygon 逐行判断每次查询要扫描完整表14 个节点的集群也要跑将近 20 秒而且会把磁盘 IO 打满旁边其他查询跟着遭殃。优化后区域表预先生成 9 级 H3 网格集合匹配走h3_cell_id整数等值 join命中行数降到千万级90% 以上的查询在 500 毫秒内返回。加上布隆过滤器索引后带时间过滤的查询基本在 100 毫秒级别。容量规划上网格化映射本身是有成本的每个事件多一个 8 字节的 UInt64 物化列再加上布隆过滤器索引整表存储开销增加大约 10%20%。这笔开销换来的是空间查询能力从不可用变成秒级非常划算。另外region_cells表虽然看起来是小表但当区域数量多、网格分辨率高的时候也会膨胀阿里 9 级、2 万个区域生成的网格集合可能上千万行建议对它也做分区和排序键规划不要只给主事件表上索引。这点我特别想强调很多人做适配方案的时候只考虑业务主表怎么设计把辅助表当成临时表随便建。实际上 region_cells 这类辅助表在每次匹配查询里都要被高效读取它的存储布局直接影响 join 性能。我们在生产环境把 region_cells 的排序键设置为(h3_cell_id, region_id)让相同网格的所有区域连续存放join 时的缓存命中率立刻上了一个台阶。回看这次 ClickHouse 地理空间匹配适配的项目我最大的体会是做空间数据在 OLAP 引擎上的落地不要试图让 ClickHouse 变成一个 GIS 数据库而是要把空间问题翻译成它擅长处理的整数关系问题。H3 网格就是这个翻译器。网格粗筛、函数精算再到外部引擎兜底复杂几何这一层适配做完百亿行表上的空间匹配才能真正跑出列式数据库该有的速度。如果你也在做类似的事情建议先从一张事件表、一张网格关联表、三套 SQL 模式开始跑通之后再考虑 UDF 和混合架构步子太大容易把自己绕进去。
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
