ClickHouse最近点查询优化

方案一

    暴力全表扫描

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

image

方案二

    使用geohash进行表分区，然后搜索最近的geohash，最后扫描对应geohash表里最近点。使用分区后得到比较可观的性能提升

-- 分区表根据geohash分区，分区键要在主键或者排序中
CREATE TABLE pntsnew ENGINE = MergeTree()  PARTITION BY (geohash) order by (geohash,Lon,Lat) AS select geohashEncode(Lon, Lat,3) geohash, Lon,Lat from pnts

image

-- 创建geohash汇总表
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

image

--搜索最近的geohash，然后扫描对应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  

image