如何在不使用OR和UNION ALL的场景下合理应用空间索引？

我正在基于Haversine公式重构空间索引逻辑，需要编写SQL查询获取距离POINT(127.0 37.5)10公里范围内的售卖帖子，同时要包含无距离信息（纬度90.0、经度0.0）的帖子。遇到的问题：

• 用OR连接两个条件时，空间索引直接失效
• 改用UNION ALL拆分两个查询后，虽然空间索引正常生效，但在5万条数据测试下性能没有明显提升
• 无法拆分API（前端不能重构），急需替代优化方案

当前使用的SQL代码：

select b1_0.id,
   b1_0.title,
   b1_0.item_time,
   b1_0.item_price,
   b1_0.created_date,
   b1_0.chat_count,
   b1_0.scrap_count,
   st_distance_sphere(b1_0.location, ST_GeomFromText('POINT(127.0 37.5)', 4326)),
   b1_0.address,
   b1_0.board_state,
   (select i1_0.stored_file_name
    from image i1_0
    where i1_0.board_id = b1_0.id
    order by i1_0.id LIMIT 1)
from board b1_0
where b1_0.board_type = 'SELL'
  and (ST_Contains(ST_Buffer(ST_GeomFromText('POINT(127.0 37.5)', 4326), 0.1), b1_0.location)
           OR (latitude = 90.0 AND longitude = 0.0))
order by b1_0.created_date desc
limit 0, 8;

优化方案 #

1. 缩小UNION ALL的数据处理范围 #

原查询会先把所有符合条件的数据查出来再合并排序，完全浪费了最终limit 0,8的限制。可以让两个子查询各自先按排序规则取足够多的候选数据（比如20条，远大于最终需要的8条），再合并后统一排序取结果，大幅减少数据量：

WITH filtered_posts AS (
    -- 取10公里范围内的候选帖子
    SELECT 
        b.id,
        b.title,
        b.item_time,
        b.item_price,
        b.created_date,
        b.chat_count,
        b.scrap_count,
        ST_Distance_Sphere(b.location, ST_GeomFromText('POINT(127.0 37.5)', 4326)) AS distance,
        b.address,
        b.board_state,
        (SELECT i.stored_file_name FROM image i WHERE i.board_id = b.id ORDER BY i.id LIMIT 1) AS cover_image
    FROM board b
    WHERE b.board_type = 'SELL'
      AND ST_Contains(ST_Buffer(ST_GeomFromText('POINT(127.0 37.5)', 4326), 0.1), b.location)
    ORDER BY b.created_date DESC
    LIMIT 20
    UNION ALL
    -- 取无位置信息的候选帖子
    SELECT 
        b.id,
        b.title,
        b.item_time,
        b.item_price,
        b.created_date,
        b.chat_count,
        b.scrap_count,
        NULL AS distance,
        b.address,
        b.board_state,
        (SELECT i.stored_file_name FROM image i WHERE i.board_id = b.id ORDER BY i.id LIMIT 1) AS cover_image
    FROM board b
    WHERE b.board_type = 'SELL'
      AND b.latitude = 90.0 AND b.longitude = 0.0
    ORDER BY b.created_date DESC
    LIMIT 20
)
SELECT *
FROM filtered_posts
ORDER BY created_date DESC
LIMIT 0, 8;

2. 替换空间查询条件，提升索引利用率 #

ST_Buffer按经纬度度数缓冲，不同纬度误差大，而且索引友好度不如ST_DWithin。直接用ST_DWithin指定10000米（10公里）的球面距离，精度更高且能更好触发空间索引：

-- 替换原空间条件
AND ST_DWithin(b.location, ST_GeomFromText('POINT(127.0 37.5)', 4326), 10000, true)

最后一个参数true表示启用球面距离计算，单位为米。

3. 给无位置数据的查询加复合索引 #

给board表建(board_type, latitude, longitude, created_date DESC)复合索引，让无位置数据的查询直接走索引，不用扫全表：

CREATE INDEX idx_board_sell_nolocation ON board(board_type, latitude, longitude, created_date DESC);

4. 优化封面图子查询，避免重复执行 #

原SQL里的封面图子查询会对每条帖子单独执行一次，改用LEFT JOIN + ROW_NUMBER()批量处理，减少查询次数：

WITH ranked_images AS (
    SELECT 
        board_id,
        stored_file_name,
        ROW_NUMBER() OVER (PARTITION BY board_id ORDER BY id) AS rn
    FROM image
),
filtered_posts AS (
    -- 近距帖子查询
    SELECT 
        b.id,
        b.title,
        b.item_time,
        b.item_price,
        b.created_date,
        b.chat_count,
        b.scrap_count,
        ST_Distance_Sphere(b.location, ST_GeomFromText('POINT(127.0 37.5)', 4326)) AS distance,
        b.address,
        b.board_state,
        ri.stored_file_name AS cover_image
    FROM board b
    LEFT JOIN ranked_images ri ON b.id = ri.board_id AND ri.rn = 1
    WHERE b.board_type = 'SELL'
      AND ST_DWithin(b.location, ST_GeomFromText('POINT(127.0 37.5)', 4326), 10000, true)
    ORDER BY b.created_date DESC
    LIMIT 20
    UNION ALL
    -- 无位置帖子查询
    SELECT 
        b.id,
        b.title,
        b.item_time,
        b.item_price,
        b.created_date,
        b.chat_count,
        b.scrap_count,
        NULL AS distance,
        b.address,
        b.board_state,
        ri.stored_file_name AS cover_image
    FROM board b
    LEFT JOIN ranked_images ri ON b.id = ri.board_id AND ri.rn = 1
    WHERE b.board_type = 'SELL'
      AND b.latitude = 90.0 AND b.longitude = 0.0
    ORDER BY b.created_date DESC
    LIMIT 20
)
SELECT *
FROM filtered_posts
ORDER BY created_date DESC
LIMIT 0, 8;

内容的提问来源于stack exchange，提问作者Jimin Lim