sqlite - 如何提高sqlite SELECT性能？

Question

一些SELECT语句需要几秒钟的时间才能返回数据，我想知道是否以及如何提高性能。数据库通常很小（〜10-40MB），但是它越大，花费的时间越长。
一个需要很长时间的示例查询如下：

SELECT intf_id FROM interfaces 
WHERE intfType IN (SELECT intfType FROM interfaces 
WHERE intf_id=39151) 
AND macAddress IN (SELECT l2_addr FROM neighbor 
INNER JOIN nlink ON nlink.neighbor_neighbor_id=neighbor.neighbor_id 
INNER JOIN interfaces ON interfaces.intf_id=nlink.interfaces_intf_id 
WHERE interfaces.intf_id=39151) 
AND status LIKE 'UP' AND phl=1 AND intf_id <> 39151 

也许是因为嵌套的SELECT语句？

数据库布局如下：


说明查询计划输出：


说明查询计划csv：

"0","0","0","SCAN TABLE interfaces USING COVERING INDEX ii1"
"0","0","0","EXECUTE LIST SUBQUERY 1"
"1","0","0","SEARCH TABLE interfaces USING INTEGER PRIMARY KEY (rowid=?)"
"0","0","0","EXECUTE LIST SUBQUERY 2"
"2","0","2","SEARCH TABLE interfaces USING INTEGER PRIMARY KEY (rowid=?)"
"2","1","0","SCAN TABLE neighbor"
"2","2","1","SEARCH TABLE nlink USING COVERING INDEX sqlite_autoindex_nlink_1 (neighbor_neighbor_id=? AND interfaces_intf_id=?)"

Answer 1

您可以尝试在interfaces.macAddress上创建索引并使用以下联接的Query代替吗？在这种情况下，子查询似乎较慢。

SELECT interface_RH.intf_id FROM interfaces AS interface_LH
           INNER JOIN nlink ON nlink.interfaces_intf_id = interface_LH.intf_id
           INNER JOIN neighbor ON nlink.neighbor_neighbor_id = neighbor.neighbor_id
           INNER JOIN interfaces AS interface_RH ON interface_RH.macAddress = neighbor.l2_addr
WHERE
           interface_LH.intf_id=39151 
           AND interface_RH.status LIKE 'UP'
           AND interface_RH.phl = 1
           AND interface_RH.intf_id <> 39151
           AND interface_RH.intfType = interface_LH.intfType