SQLite查询性能时间

Question

我有一个查询执行起来花费了太多时间（因为最后一个小时仍在运行）：

select RL.[LINK_ID] as LINK_ID, RPA.[POSTAL_AREA_ID] as POSTAL_AREA_ID, RRN.[STREET_NAME] as STREET_NAME 
from RDF_LINK as RL, RDF_POSTAL_AREA as RPA, RDF_ROAD_LINK as RRL, RDF_ROAD_NAME as RRN 
where RRL.[ROAD_NAME_ID] = RRN.[ROAD_NAME_ID]
AND RPA.[POSTAL_AREA_ID] IN (RL.[LEFT_POSTAL_AREA_ID], RL.[RIGHT_POSTAL_AREA_ID]) 
AND RL.[LINK_ID] = RRL.[LINK_ID] 

作为查询一部分的所有列都被编入索引
ANALYZE命令已经。在数据库上执行
该数据库在RDF_ROAD_LINK表中有大约7300万条记录，在其他表中有相同数量的记录。

是否有其他方法可以编写此查询？

EXPLAIN QUERY PLAN
select RL.[LINK_ID] as LINK_ID, RPA.[POSTAL_AREA_ID] as POSTAL_AREA_ID, RRN.[STREET_NAME] as STREET_NAME 
from RDF_LINK as RL, RDF_POSTAL_AREA as RPA, RDF_ROAD_LINK as RRL, RDF_ROAD_NAME as RRN 
where RRL.[ROAD_NAME_ID] = RRN.[ROAD_NAME_ID]
AND RPA.[POSTAL_AREA_ID] IN (RL.[LEFT_POSTAL_AREA_ID], RL.[RIGHT_POSTAL_AREA_ID]) 
AND RL.[LINK_ID] = RRL.[LINK_ID]

输出：：

0   0   3   SCAN TABLE RDF_ROAD_NAME AS RRN
0   1   2   SEARCH TABLE RDF_ROAD_LINK AS RRL USING INDEX IND_ROAD_NAME_ID (ROAD_NAME_ID=?)
0   2   0   SEARCH TABLE RDF_LINK AS RL USING INDEX sqlite_autoindex_RDF_LINK_1 (LINK_ID=?)
0   3   1   SEARCH TABLE RDF_POSTAL_AREA AS RPA USING COVERING INDEX sqlite_autoindex_RDF_POSTAL_AREA_1 (POSTAL_AREA_ID=?)
0   0   0   EXECUTE LIST SUBQUERY 1

Answer 1

此查询返回所有7300万条记录，并且必须从其他表中查找相应的记录。这不可能很快，因为有太多的数据要缓存（在这种大小下，甚至可能连索引都不适合缓存）。

在两个表之间的联接中，数据库遍历第一个表的所有行，并查找第二个表的相应行。这意味着第一个表总是以SCAN结尾，因为使用索引是没有意义的（当您无论如何都需要加载所有行时，通过索引不会更快）。

在这种情况下，只有当索引列（WHERE STREET_NAME = 'My Street'）上有额外的筛选器，或者结果必须按索引列排序（ORDER BY ROAD_NAME_ID）时，才可能对RDF_ROAD_NAME使用索引。

如果表中有许多列没有在该查询中使用，则可以通过使用覆盖索引来加快查询速度（如果所需的所有数据都已在索引中，则数据库不需要查找相应的表行）：

CREATE INDEX ... ON RDF_ROAD_LINK(ROAD_NAME_ID, LINK_ID);
CREATE INDEX ... ON RDF_LINK(LINK_ID, LEFT_POSTAL_AREA_ID, RIGHT_POSTAL_AREA_ID);