sqlite中的复合主键

Question

PRIMARY KEY(col1, col2)

创建一个名为“ sqlite_autoindex_mytable_1”的索引。我在“ SQlite
Expert”中看到了它。在“要索引的字段”框中，它同时显示col1和col2。

在这篇文章中：https :
//dba.stackexchange.com/a/14260
它说如果我想在没有col1的JOIN查询中使用col2，则必须为col2创建单独的索引。

因此，我需要添加：

CREATE INDEX "myidx" ON "mytable"("col2");

如果我有此查询：

SELECT t2.* FROM mytable as t1 
INNER JOIN mytable2 as t2 ON t1.col2 = t2.id

我仍然需要col2的索引吗？我没有用col1它。

那这个查询呢：

SELECT t2.* FROM mytable as t1 
INNER JOIN mytable2 as t2 ON t1.col2 = t2.id WHERE t1.col1 = x

在这里我使用col1，但是在where子句中。它仍然需要col2索引吗？

Answer 1

• 在SQLite中，联接是作为 嵌套循环联接实现的 ，即SQLite遍历一个表的所有（可能是过滤的）记录，并针对每个表在另一表中查找匹配的记录。选择两个连接表中的哪一个作为外部表还是内部表取决于估计的查找速度。
• 在查询中，SQLite每张表最多使用一个索引。
• 仅当使用了所有最左边的列时，才可以将多列索引用于其子集的查找。例如，您的col1,col2索引可用于同时使用col1和的查找，也可用于col2仅使用的查找col1。

在您的第一个查询中，两列索引不能用于在上进行查找col2。如果id另一个表的列具有索引，SQLite只会将该表用作循环的内部表。如果id也未建立索引，则SQLite可能会为此查询创建一个临时索引。

在第二个查询中，SQLite可能t1用作外部表，因为WHERE过滤器将减少必须在另一个表中查找的记录数。两列索引可用于首先搜索匹配col1记录；然后将这些记录中的每一个都与连接在一起t2。

要检查查询使用了哪些索引（如果有），请执行EXPLAIN QUERY PLAN。