SQLite中使用DISTINCT时R*Tree索引未生效的解决方法

首先咱们得先搞明白为啥加了DISTINCT之后，查询优化器就放弃RTree索引了——SQLite的查询规划器在处理DISTINCT时，可能会优先选择“先获取所有匹配数据再去重”的路径，反而忽略了RTree索引能快速过滤空间交集的优势，尤其是当临时表frisbees数据量很小（只有100条）的时候，优化器的判断出现了偏差。

下面给你几个亲测在SQLite 3.20.1中有效的解决办法：

1. 用子查询先筛选空间交集，再外层去重 #

把空间关联的逻辑放到子查询里，引导优化器先利用R*Tree索引过滤出符合条件的dog_id，再在外层做去重操作。写法如下：

SELECT DISTINCT dog_id
FROM (
  SELECT db.dog_id
  FROM dog_bounds AS db, frisbees AS f
  WHERE db.max_x >= f.min_x 
    AND db.max_y >= f.min_y 
    AND db.min_x < f.max_x 
    AND db.min_y < f.max_y
);

这个写法会让子查询优先执行原来的索引扫描逻辑，外层只负责去重，执行计划应该会回到先扫frisbees、再用R*Tree索引扫dog_bounds的高效路径。

2. 改用EXISTS子查询替代笛卡尔关联 #

把空间交集的逻辑用EXISTS来表达，这样优化器更容易识别出可以用R*Tree索引快速判断存在性，同时DISTINCT的成本也会降低：

SELECT DISTINCT db.dog_id
FROM dog_bounds AS db
WHERE EXISTS (
  SELECT 1 
  FROM frisbees AS f
  WHERE db.max_x >= f.min_x 
    AND db.max_y >= f.min_y 
    AND db.min_x < f.max_x 
    AND db.min_y < f.max_y
);

这种写法的执行计划会先遍历dog_bounds的R*Tree索引，然后对每个条目检查是否存在匹配的frisbees记录，同时完成去重，效率会比全表扫描高很多。

3. 强制指定索引（应急可用，不推荐长期依赖） #

如果上面两种写法都没生效，可以尝试用INDEXED BY强制指定使用R*Tree索引，但注意这个写法依赖索引的内部名称，可能在不同环境或版本中变化：

SELECT DISTINCT db.dog_id
FROM dog_bounds AS db INDEXED BY dog_bounds, frisbees AS f
WHERE db.max_x >= f.min_x 
  AND db.max_y >= f.min_y 
  AND db.min_x < f.max_x 
  AND db.min_y < f.max_y;

这个方法灵活性较差，优先推荐前两种写法。

额外小提示 #

因为frisbees是临时表且数据量很小（仅100条），你还可以考虑先把frisbees的空间范围合并成一个或多个大的范围，再和dog_bounds关联，这样能进一步减少关联次数，提升查询效率。

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