时间戳

首页 > 解决方案 > SQLite R*Tree 索引不与 DISTINCT 一起使用

indexing - SQLite R*Tree 索引不与 DISTINCT 一起使用

问题描述

在 SQLite 3.20.1 中，我创建了一个 R*Tree 索引 ( dog_bounds) 和一个临时表 ( frisbees)，如下所示：

-- Changes infrequently and has ~100k entries
CREATE VIRTUAL TABLE dog_bounds USING rtree (
    dog_id,
    min_x, max_x,
    min_y, max_y
);

-- Changes frequently and has ~100 entries
CREATE TEMPORARY TABLE frisbees (
    frisbee_id,
    min_x, max_x,
    min_y, max_y
);

使用此索引进行查询很快，如下所示：

EXPLAIN QUERY PLAN
SELECT 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;

0|0|1|SCAN TABLE frisbees AS f
0|1|0|SCAN TABLE dog_bounds AS db VIRTUAL TABLE INDEX 2:D1D3C0C2

但是，如果我 select DISTINCT(dog_id)，则不再使用索引，并且查询会变慢，即使在以下情况下也是如此ANALYZE：

EXPLAIN QUERY PLAN
SELECT DISTINCT(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;

0|0|0|SCAN TABLE dog_bounds AS db VIRTUAL TABLE INDEX 2:
0|1|1|SCAN TABLE frisbees AS f
0|0|0|USE TEMP B-TREE FOR DISTINCT

我怎样才能获得这里使用的 R*Tree 索引？复制狗将是一种耻辱！

标签： indexingsqliter-treequery-planner

解决方案

查询优化器认为不同的执行顺序更容易获得不同的dog_id值。

将 R-tree 查找移动到子查询中，以便查询优化器被迫分别做这两件事：

SELECT DISTINCT dog_id
FROM (SELECT 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);

查询计划
|--SCAN TABLE dog_bounds AS db VIRTUAL TABLE INDEX 2:
|--SCAN TABLE frisbees AS f
`--将 TEMP B-TREE 用于 DISTINCT

糟糕，查询优化器太聪明了，把子查询弄平了。但是有一些方法可以禁用它（规则 21）：

SELECT DISTINCT dog_id
FROM (SELECT 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
      LIMIT -1);

查询计划
|--协同程序 0x892A90
| |--SCAN TABLE frisbees AS f
| `--SCAN TABLE dog_bounds AS db 虚拟表索引 2:D1D3C0C2
|--扫描子查询 0x892A90
`--将 TEMP B-TREE 用于 DISTINCT

推荐阅读