微信扫一扫OceanBase 地理半径检索优化
「附近 3 公里有什么」这类查询，默认写法是 WHERE ST_Distance_Sphere(...) < 3000，
在 OceanBase 上就是全表扫 + 逐行算球面距离 + 全局排序。数据量到百万级，P99 直接从毫秒掉到秒级。
本 Skill 给出的做法是空间编码 + 索引 range 扫描 + 应用层精算， 并且给出了一条容易被忽略但决定成败的规则：任何加速方案都必须先过召回率对拍。
实测背景：30k POI / 200 次随机查询 / 北纬 39.9°。 固定"中心格 + 8 邻域"在 1km 半径下 recall 只有 0.934，却因为扫描行数最少（65 行）看起来"最快"； 自适应覆盖方案 recall = 1.000，扫描 91 行。压测里最漂亮的数字往往来自错的方案。 （recall 会随圆心在格子内的相对位置浮动，1km/L6 复测值在 0.93～0.96 之间。）
何时使用
- 用户要做 POI / 门店 / 网点 / 设备的"附近 N 公里"查询优化。
- 用户抱怨地理查询 QPS 低、P99 高、
scanned_rows接近表行数。 - 用户说"结果少了几条"、"某些点查不到"——典型漏召回，用本 Skill 的对拍流程定位。
- 需要为经纬度字段设计表结构、索引、分区。
何时不要用
- 只是普通 SQL 慢查询（join 顺序、统计信息、执行计划）→ 走通用 SQL 调优。
- 查询是"地图可视范围矩形框"而不是圆半径 → 直接用 bbox 双列 range 即可，不必上 geohash。
- 数据量 < 10 万且无 SLO 要求 → 全表扫也能接受，别过度设计。
总体流程
Step 0 单位断言（10 分钟，做错方向全错）
Step 1 表结构：物化 geohash 前缀列 + 索引
Step 2 选精度、算覆盖格子（用 scripts/geohash_cells.py，别手推）
Step 3 查询改写：四条路线 A/B/C/D 选一条
Step 4 验证：recall 对拍 + EXPLAIN + 真实扫行
Step 0 单位断言（必做，不可跳过）
在写任何 SQL 之前，先确认数据库里"1 度 = 多少米"。
-- 在目标库执行，结果应该接近 111000，不是 1 附近，也不是 6371000
SELECT ST_Distance_Sphere(POINT(116.0, 39.0), POINT(116.0, 39.01));
判定：
| 返回值 | 含义 | 处理 |
|---|---|---|
| ≈ 1111 | 单位是米 | 正常，继续 |
| ≈ 0.01 | 单位是度 | 半径必须换算成度，否则 < 5000 等于匹配全表 |
| 报错不存在 | 该版本无此函数或无 SRS | 退回应用层算距离，或先导入 SRS 脚本 |
同时断言：经纬度顺序统一为 POINT(lng, lat)。
写反了不会报错，只会安静地查到另一个城市——这是这类 bug 最常见的来源。
Step 1 表结构：把前缀物化成一列
CREATE TABLE poi (
id BIGINT PRIMARY KEY,
name VARCHAR(128),
category INT,
lng DECIMAL(10,6) NOT NULL,
lat DECIMAL(10,6) NOT NULL,
gh6 CHAR(6) NOT NULL, -- 物化 geohash 前 6 位，写入时算好
KEY idx_gh6 (gh6),
KEY idx_cat_gh6 (category, gh6) -- route D 用
);
三条硬规则：
- 必须物化列。
LEFT(ghash, 6)、SUBSTR(ghash,1,6)、CONCAT(...)都会让 B 树索引失效， 退化成全表扫，后面所有格子优化白做。 - 长度必须统一。有的存 5 位、有的存 6 位，
IN精确匹配就会漏数据，recall 永远到不了 1。 - 精度按查询半径固定，不要同一列混存多级前缀；需要多级就建
gh5、gh6、gh7多列或按场景拆表。
大批量导入后必须 ANALYZE TABLE poi;，否则 rows 估算离谱，优化器会选错计划，
EXPLAIN 和真实执行差距极大。
Step 2 选精度 + 算覆盖格子
不要手写"中心格 + 8 邻域"——它在高纬度会漏。原因是 geohash 格子不是正方形：
经度方向格子宽度随纬度按 cos(lat) 收缩。
格子实际尺寸（米）：
| 级别 | 纬度 0° | 纬度 30° | 纬度 40° | 纬度 50° | |---|---|---|---|---| | L4 | 39.1 × 19.5 km | 33.9 × 19.5 | 30.0 × 19.5 | 25.1 × 19.5 | | L5 | 4.89 × 4.86 km | 4.23 × 4.86 | 3.75 × 4.86 | 3.14 × 4.86 | | L6 | 1.22 × 0.61 km | 1.06 × 0.61 | 0.94 × 0.61 | 0.79 × 0.61 | | L7 | 153 × 152 m | 132 × 152 | 117 × 152 | 98 × 152 |
（左为经度方向，右为纬度方向；w_lon = 40075km / 2^lon_bits * cos(lat)）
覆盖格子数 ≈ ceil(2r / w_lon) × ceil(2r / h_lat)，再加一格边界冗余。
直接让脚本算，别口算：
python scripts/geohash_cells.py --lat 39.9 --lng 116.4 --radius 1000 --level 6
输出：覆盖格子列表（可直接塞进 IN (...)）、格子数、扫描面积放大倍数，
并自动跑 500 个随机点的召回自检。
实测参考（39.9°N）：
| 半径 | 精度 | 方案 | 格子数 | recall | 候选行 | |---|---|---|---|---|---| | 1 km | L5 | 固定 9 格 | 9 | 1.000 | 1750 | | 1 km | L6 | 固定 9 格 | 9 | 0.93～0.96 ❌ | 65 | | 1 km | L6 | 自适应覆盖 | 约 13～15 | 1.000 | 91 | | 5 km | L5 | 固定 9 格 | 9 | 0.990 ❌ | 1750 | | 5 km | L5 | 自适应覆盖 | 约 10～11 | 1.000 | 1971 |
自适应覆盖相对圆面积只放大 1.6×～2.8×；而"必须凑满 9 格"的粗前缀方案放大 50×～67×。
精度选择口诀：选满足 格子短边 ≥ 半径/2 的最粗一级。
1km → L6；3～5km → L5；10km 以上 → L4。
Step 3 查询改写：四条路线
Route A：只换 WHERE（最小改动，先拿基线）
SELECT id, name, lng, lat
FROM poi
WHERE gh6 IN ('wx4g0e','wx4g0f', ... ) -- Step 2 生成的列表
AND ST_Distance_Sphere(POINT(lng, lat), POINT(116.4, 39.9)) <= 1000
ORDER BY ST_Distance_Sphere(POINT(lng, lat), POINT(116.4, 39.9))
LIMIT 20;
Route B：格子 + bbox 预过滤（少算距离）
WHERE gh6 IN (...) AND lat BETWEEN ? AND ? AND lng BETWEEN ? AND ?
bbox 用 dlat = r/111320、dlon = r/(111320*cos(lat)) 算，别忘 cos。
Route C：Index 只做召回，距离与排序放应用层（推荐）
SELECT id, name, category, lng, lat
FROM poi
WHERE gh6 IN (...) AND category = ?; -- 不要 LIMIT，不要 ORDER BY
拿到候选行后在 Python/Go 里算 haversine、过滤、排序、截断。 好处：去掉 DB 内每行函数调用和全局排序，P99 更稳；坏处：网络回传行数变多。
Route D：category 下推 + 覆盖索引
-- KEY idx_cat_gh6 (category, gh6, lat, lng) → 覆盖索引，免回表
SELECT id, lng, lat FROM poi WHERE category = ? AND gh6 IN (...);
真实业务几乎都带品类筛选，选择性再上一层。
三条禁忌
- Route C/D 加
LIMIT→ 多区间IN时会在前几个格子就停止扫描，后面的格子被截断，静默漏召回。 - DB 内
ST_Distance与应用层haversine混用做对拍 → 球面/椭球差异带来米级误差，阈值要留余量（±5m）或全程只用一种实现。 LEFT(ghash,6)/SUBSTR→ 见 Step 1 第 1 条，索引失效。
Step 4 验证（这一步决定分数）
4.1 召回对拍（最重要）
python scripts/geohash_cells.py --lat 39.9 --lng 116.4 --radius 1000 --level 6 --selftest 500
自检逻辑：在圆内随机撒点，检查每个点的 geohash 前缀是否落在覆盖集合里。
出现 recall < 1.0 就直接加格子，不要靠压测数字好看就放过。
DB 侧对拍：同一批查询分别用"全表 ST_Distance"和"格子方案"跑，比对 id 集合，
差异必须为空。差异非空时按顺序查：前缀列长度是否统一 → 覆盖格子是否够 → IN 是否被 LIMIT 截断。
4.2 真实扫行数，别看 EXPLAIN
EXPLAIN 是估算。真实扫行看审计视图：
SELECT query_sql, plan_type, ROW_COUNT, ELAPSED_TIME
FROM oceanbase.GV$OB_SQL_AUDIT
WHERE query_sql LIKE '%gh6 IN%'
ORDER BY request_time DESC LIMIT 20;
（部分版本列名为 RETURN_ROWS / EXECUTE_TIME，先 DESC 一下。）
4.3 压测指标
必须同时记录：QPS、P50/P95/P99、scanned_rows、recall、索引体积。 只有 QPS 没有 recall 的报告，等于没做验证。
单线程压不上去时，先分清是 DB 满还是客户端满——Python 压测端有 GIL， 多进程化之前不要下"数据库到顶了"的结论。
反模式清单
| 现象 | 根因 | 修法 |
|---|---|---|
| 加了 gh6 IN 还是很慢 | 用了 LEFT() 包裹，索引失效 | 物化列 |
| recall 0.93 左右 | 固定 9 格 + 高纬度经度收缩 | 自适应覆盖 |
| 结果比预期少几条 | IN + LIMIT 截断后续格子 | 去掉 LIMIT，应用层截断 |
| 半径写 5000 却匹配全表 | ST_Distance 返回"度" | 单位断言 |
| 偶发 P99 尖刺 | 压测期间撞上合并/合并冻结 | 报告里说明或避开窗口 |
| 优化器不选索引 | 导入后没 ANALYZE TABLE | 补统计信息 |
自检清单（交付前逐条打勾）
- [ ]
ST_Distance单位已断言（米 / 度 / 报错） - [ ] 经纬度顺序全链路统一为
(lng, lat) - [ ] geohash 前缀是物化列，且有索引；长度统一
- [ ] 覆盖格子由脚本生成，recall 自检 = 1.000
- [ ] Route C/D 里没有
LIMIT - [ ] DB 侧与应用层距离算法只用一种，或阈值留 ±5m 余量
- [ ] 报告同时给了 QPS / P99 / scanned_rows / recall 四项
- [ ] 说明了压测窗口是否避开合并
使用示例
示例 1：从零搭建
用户：「我有 200 万条门店数据，要做附近 3 公里的店，现在查一次 2 秒。」
流程：Step 0 断言单位 → Step 1 建 gh5 列 + idx_gh5 → Step 2 跑
geohash_cells.py --radius 3000 --level 5（北京约 12 格）→ Route C 改写 →
对拍 recall → 压测。预期从全表扫降到千级候选行。
示例 2：排查漏召回
用户：「1 公里内的店明明是 8 家，接口只返回 7 家。」
先跑 --selftest 500，如果自检通过，再检查：IN 里是否配了 LIMIT、
前缀列是否混了长度、category 是否误过滤、半径是否用了直线距离近似。
最后用"全表扫"对拍 id 集合，差集就是漏掉的那一家。
示例 3：只想要一个数字
用户：「1 公里、北纬 40 度、L6，需要几个格子？」
ceil(2*1000/936) × ceil(2*1000/610) = 3 × 4 = 12 格，加边界冗余约 12～20 格，
扫描面积约为圆面积的 2 倍。
目录
oceanbase-geo-radius-search/
├── SKILL.md
├── scripts/geohash_cells.py # 覆盖格子计算 + 召回自检，零依赖
├── references/
│ ├── geohash-cell-table.md # 各级格子的精确尺寸与推导
│ ├── oceanbase-pitfalls.md # 10 个真实踩过的坑与检测方法
│ └── benchmark-playbook.md # 压测阶梯与报告结构
└── examples/nearby_poi.sql # 建表 / 索引 / 四条路线 SQL