基于主键列表的高效sqlite查询

Question

针对基于一组ID（即不同的主键）查询sqlite表，我使用以下语句（示例基于 Chinook Database ）：

SELECT * FROM Customer WHERE CustomerId IN (1,2,3,8,20,35)

然而，我的实际ID列表可能会变得相当大（>1000）。因此，我想知道使用 IN 语句的这种方法是否是最有效的，或者是否有更好/优化的方式根据一组主键查询sqlite表。

- Johannes

Answer 1

如果IN中的元素数量足够大，SQLite会为它们构建一个临时索引。这比手动创建临时表更有效率。

IN列表的长度仅受SQL语句的最大长度和内存的限制。

- CL.

---

你能提供文档链接吗？关于大型IN查询自动使用临时表的问题。我的经验是在Android上使用SQLite时，遇到了SQLITE_MAX_VARIABLE_NUMBER的限制，它的值为999。 - Jeff Lockhart

@JeffLockhart，你的Android可能有一个较旧的SQLite版本。这些细节的唯一文档是源代码。 - CL.

有趣。如果您可能指出此优化大约发生在源代码的哪个位置，我或许能够缩小到引入该优化的版本，并与运行在各种Android平台版本上的版本进行比较：https://dev59.com/aXE95IYBdhLWcg3wMa51。几年前我首次遇到这个问题，所以我很想知道这种行为是否已经改变。 - Jeff Lockhart

@JeffLockhart，“IN（...）”的内容在“parse.y”中使用“exprlist（A）”进行解析。临时表是在“expr.c”中的“sqlite3FindInIndex（）”的最后部分生成的。无论如何，如果您正在使用参数，则无法避免遇到SQLITE_MAX_VARIABLE_NUMBER。 - CL.

感谢提供信息。通过查看源代码，发现在 expr.c 中有这样的描述：无论是 'x IN(SELECT...)' 还是 'x IN(<exprlist>)' 表达式，处理方式都是相同的。一个临时表会被填充，其中包含来自 SELECT 或 <exprlist> 的结果的索引键。 - Jeff Lockhart

Answer 2

因为你编写的语句没有任何指示 SQLite 如何查找所需行，因此“优化”这个概念实际上不存在——没有什么可优化的。规划检索数据的最佳算法是 SQLite 查询优化器的任务。

有些数据库的查询优化器存在问题可能会导致性能问题，但我不认为 SQLite 在查找包含大量 IN 列表的简单查询时会遇到任何麻烦。只有在发现性能问题时才需要尝试引导查询优化器到另一种执行计划。

- Larry Lustig

Answer 3

SQLite优化器概述

IN (expression-list)如果可用，会使用索引。

除此之外，我无法从中获得任何保证，所以下面的内容都需要进行性能测量。

方案一：如何传递expression-list

• 将其硬编码为字符串。这样做会增加整数转换为字符串和字符串解析为整数的开销。
• 绑定参数（即语句为... WHERE CustomerID in (?,?,?,?,?,?,?,?,?,?....)，这比硬编码的值更容易从预定义字符串构建）。可以避免整数→字符串→整数的转换，但默认参数数量限制为999。可以通过SQLITE_LIMIT_VARIABLE_NUMBER来增加，但可能会导致过多的分配。
• 临时表。在准备好语句后，可能比上述任何方法都要低效，但如果大部分时间都花在准备语句上，这并没有帮助。

---

轴2：语句优化

如果相同的表达式列表在多个针对不同CustomerIDs的查询中使用，以下预处理语句或许可以帮助：

• 重用一个带有硬编码值的预处理语句（即不要传递1001个参数）
• 为CustomerIDs创建一个带索引的临时表（这样索引只会被创建一次，而不是每次查询都动态创建）

如果每个查询的表达式列表都不同，那么最好还是让SQLite自己处理。以下方法或许可以改进：

• 为表达式列表创建一个临时表
• 使用union all批量插入表达式列表元素
• 使用子查询

（根据我对SQLite的经验，我预计它的性能应该与原来相当或略有下降）

---

轴 3 向 Richard 提问

SQLite 邮件列表（是的，我知道，这项技术甚至比旋转电话还要古老！）非常活跃，通常会提供出色的建议，包括 SQLite 的作者。有 90% 的机会有人会认为你在提问之前应该“先测量”，还有 10% 的机会有人会给你详细的见解。

- peterchen