clickhouse 查询优化_ClickHouse最近点查询优化

最新推荐文章于 2025-06-20 12:13:40 发布

原创最新推荐文章于 2025-06-20 12:13:40 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#clickhouse 查询优化

本文介绍了三种ClickHouse中优化最近点查询的方法。方案一使用`greatCircleDistance`直接排序并限制结果；方案二引入GeoHash进行预处理，通过MergeTree引擎优化查询；方案三结合GeoHash和分区策略进一步提升查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

方案一

select Lon,Lat from pntsnew order by greatCircleDistance(Lon,Lat,-120.419219,34.889755999999998) limit 1

方案二

CREATE TABLE pntsnew ENGINE = MergeTree() order by (geohash,Lon,Lat) AS select geohashEncode(Lon, Lat,4) geohash, Lon,Lat from pnts

CREATE TABLE geohashP ENGINE = MergeTree() order by (geohash,lon,lat) AS select geohash,tupleElement(geohashDecode(geohash ),1) lon,tupleElement(geohashDecode(geohash ),2) lat from pntsnew group by　geohash

select Lon,Lat from pntsnew where geohash in (select geohash from geohashP order by greatCircleDistance(lon,lat ,-120.419219,34.889755999999998) limit 1) order by greatCircleDistance(Lon,Lat ,-120.419219,34.889755999999998) limit 1

select geoToH3( toFloat64(Lon), toFloat64(Lat),3) geoh3,toFloat64(Lon) Lon, toFloat64(Lat) Lat,id from pnts group by geohash

CREATE TABLE pntsh3 ENGINE = MergeTree() order by (h3,Lon,Lat) AS select geoToH3(Lon, Lat,4) h3, Lon,Lat from pnts

方案三

CREATE TABLE pntsnew ENGINE = MergeTree() PARTITION BY (geohash) order by (geohash,Lon,Lat) AS select geohashEncode(Lon, Lat,3) geohash, Lon,Lat from pnts