sql - sqlite中的复合主键

Question

主键(col1, col2)

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

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

所以我需要添加:

在“mytable”(“col2”)上创建索引“myidx”；

如果我有这样的查询:

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 遍历一个表的所有(可能被过滤的)记录，并针对每个记录在另一张 table 。选择两个连接表中的哪一个作为外部表或内部表取决于估计哪个查找更快。
• 在一个查询中，SQLite 对每个表最多使用一个索引。
• 只有在使用了所有最左边的列时，多列索引才能用于查找其列的子集。例如，您的 col1,col2 索引可用于同时使用 col1 和 col2 的查找，或用于仅使用 的查找col1.

在您的第一个查询中，两列索引不能用于查找 col2。如果其他表的列 id 有索引，SQLite 将只使用该表作为循环的内表。如果 id 也没有被索引，SQLite 可能会为此查询创建一个临时索引。

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

要检查查询使用了哪些索引(如果有的话)，执行EXPLAIN QUERY PLAN .