sql - 提高 SQLite 中 SQL 选择和连接的效率

Question

我正在从事一个专注于分析数据库中的文本的个人项目。我的目的是做一些有趣的事情并学习 SQL 和 sqlite。因此，考虑到我的新手能力，我想获得有关更有效地执行此操作的建议。

例如，我想在文章 A 中挑选出食物的种类。我解析我的文章，如果我找到食物 F，然后我将 F 添加到表 items。然后我将 A.id 和 F.id 添加到 results。当我解析我的文章并发现 G 已经存在于 items 中时，我所做的就是添加 A.id 和 G .id 到结果。

所以我的模式看起来像下面这样:

• 文章: id, 文章
• 结果: id, item_id, article_id
• 项目: id, foodtype, food

如果我想找到所有关于 oranges 和 grapes 以及任何 vegetable 的文章，那么我会从类似这个:

SELECT * 
  FROM articles 
INNER JOIN results ON articles.id = results.article_id  
INNER JOIN items ON results.item_id = items.id

并添加:

WHERE foodtype='vegetable' OR food='orange' OR food='grape'

实际上，我的数据库要大得多。有数千篇文章和超过十万种提取的“食物”。我加入 3 个表的这些查询中的大多数都不会返回，即使我将结果限制为 100 个。我已经尝试在我的 WHERE 子句中常​​见的字段上创建索引，例如 food 和 foodtype，但没有看到任何改进。

我可以对我的数据库或查询进行改进吗？

Answer 1

只检索你需要的列

查询的第一个问题是 SELECT * 返回查询中连接的所有表 中的所有 列。这意味着 JOIN 标准中的值，在评估的两边，都将被返回。最好写出您需要的实际列，因为您列出的所有三个列都有一个 id 列——这会使正确的值检索变得复杂，除非使用顺序位置(不是一个好的做法——改变位置，数据检索不应该是这样的)。

使用表别名可以最大限度地减少引用特定表所需的内容:

SELECT a.article 
  FROM ARTICLES a
  JOIN RESULTS r ON r.article_id = a.id
  JOIN ITEMS i ON i.id = r.item_id

索引

索引外键——您用于 JOIN 标准的索引，应该是列表中仅次于表主键的第二件事。

然后你必须定期运行 ANALYZE command因为统计数据是...

...not automatically updated as the content of the database changes. If the content of the database changes significantly, or if the database schema changes, then one should consider rerunning the ANALYZE command in order to update the statistics.

这些统计信息是优化器用于其查询决策的，以及索引的存在。

众所周知，OR 的性能很差

您可以尝试重写查询，使其不使用带有 UNION 的 OR:

SELECT a.article 
  FROM ARTICLES a
  JOIN RESULTS r ON r.article_id = a.id
  JOIN ITEMS i ON i.id = r.item_id
 WHERE i.foodtype = 'vegetable'
UNION 
SELECT a.article 
  FROM ARTICLES a
  JOIN RESULTS r ON r.article_id = a.id
  JOIN ITEMS i ON i.id = r.item_id
 WHERE i.food IN ('orange', 'grape')

请注意 UNION 比 UNION ALL 慢，因为 UNION 会删除重复项。 UNION ALL 更快，因为它不会删除重复项。