c# - SQLite "automatic index on MyTable(Id)"当字段是表的主键时

Question

我有一个复杂的 C# 程序，它使用动态构建的查询从 SQLite 数据库中读取数据。我注意到当我在调试器下运行程序时，我得到很多输出，例如:

SQLite warning (284): automatic index on MyTable(Id)

我查看了 MyTable 的架构，并将 Id 指定为主键，如下所示:

CREATE TABLE MyTable (Id varchar(50) collate nocase primary key not null,
Name varchar(50) not null, 
(etc)

我认为 SQLite 无论如何都会为主键创建索引，那么为什么它还要创建另一个索引呢？

另外，在相关说明中，我收到了很多有关子查询的自动索引警告。例如，在查询中:

SELECT MyTable.Id, MyTable.Name, Amount
FROM MyTable
LEFT JOIN (SELECT ArrangementId, Amount, AgreementDate FROM SubTable 
JOIN Organisations ON Organisations.Id = SubTable.OrganisationId AND Organisations.Direction = 1
) AS MyJoin ON MyJoin.ArrangementId = MyTable.Id
ORDER BY Id

哪里

MyTable has Id as the primary key
Organisations has Id as the primary key
SubTable has a unique index on ArrangementId, OrganisationId

对查询结果解释查询计划:

1|0|0|SCAN TABLE SubTable
1|1|1|SEARCH TABLE Organisations USING INDEX sqlite_autoindex_Organisations_1 (Id=?)
0|0|0|SCAN TABLE Arrangements USING INDEX sqlite_autoindex_Arrangements_1
0|1|1|SEARCH SUBQUERY 1 AS MyJoin USING AUTOMATIC COVERING INDEX (ArrangementId=?)

我猜 SQLite 不够聪明，没有意识到子查询不需要进入临时表？

有没有办法重写查询以避免子查询？

Answer 1

collat​​e nocase 列会生成 collat​​e nocase 索引。如果查找不使用相同的排序规则，则无法使用该索引。(默认情况下，与该列的比较使用 nocase，但是当与具有不同排序规则的另一列进行比较时，这没有帮助。)

如果此查询很重要，请考虑使用正确的排序规则创建第二个索引。

<小时/>

在第二个查询中，数据库必须使用临时表计算子查询，因为它是左外连接 ( rule 3 ) 的右操作数。

如果您确定含义保持不变，您可以尝试将查询重写为一系列简单的连接:

FROM MyTable
LEFT JOIN SubTable ON MyTable.Id = SubTable.ArrangementId
LEFT JOIN Organisations ON Organisations.Id = SubTable.OrganisationId
                       AND Organisations.Direction = 1