在MyTable(Id)上建立自动索引当字段是表的主键时

本文关键字：字段 索引 Id MyTable 建立 | 更新日期: 2023-09-27 18:03:44

我有一个复杂的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

EXPLAIN QUERY PLAN上的查询结果:

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是不够聪明的意识到，子查询不需要进入临时表?

是否有任何方法重写查询以避免子查询?

在MyTable(Id)上建立自动索引当字段是表的主键时

一个collate nocase列产生一个collate nocase索引。如果查找不使用相同的排序规则，则不能使用该索引。(与该列的比较默认使用nocase，但当与具有不同排序规则的另一列进行比较时，这不起作用。)

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

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

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

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

尝试做以下更改:

  • 使ID为主键为整数。这实际上是内部行的别名，而不是单独的列。
  • 将当前ID (varchar列)作为单独的列。如果很重要，可以选择添加唯一约束。
  • 在子表中，使用整数ID代替varchar列。此外，在列上添加外键。

注意事项:

  • 在MyTable中使用INTEGER主键并将varchar移动到具有唯一约束的单独列将不会对MyTable的大小产生影响。正如我上面提到的，主键列将是内部行列的别名，因此您实际上没有添加任何新列。
  • 通过将子表切换为使用整数作为外键，这将导致更小的表。即使添加了外键约束，您的数据库也会比现在小。这也应该使您的连接更快(无论如何，对于大型数据集)。
相关文章：