sqlite中的复合主键 [英] Composite primary keys in sqlite

问题描述

主键(col1, col2)

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

creates a index called "sqlite_autoindex_mytable_1". I saw it in "SQlite Expert". And in the "fields to index" box it shows both col1 and col2.

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

In this post: https://dba.stackexchange.com/a/14260 it says that I have to create separate indexes for col2 if I want to use col2 in JOIN queries without col1.

所以我需要补充:

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

如果我有这个查询:

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

我还需要 col2 的索引吗?我没有在其中使用 col1.

do I still need a index for col2? I'm not using col1 in it.

那么这个查询呢:

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

这里我使用 col1，但在 where 子句中.还需要col2索引吗?

Here I'm using col1, but in the where clause. Does it still need the col2 index?

推荐答案

• 在 SQLite 中，连接被实现为嵌套循环连接，即 SQLite 遍历一个表的所有(可能是过滤的)记录，并为每个记录查找匹配的记录另一张桌子.选择两个连接表中的哪一个作为外部或内部表取决于估计哪个查找更快.
• 在查询中，SQLite 每个表最多使用一个索引.
• 只有在最左边的所有列都被使用的情况下，多列索引才能用于在其列的子集上进行查找.例如，您的 col1,col2 索引可用于同时使用 col1 和 col2 的查找，或仅使用 的查找col1.
• In SQLite, joins are implemented as nested loop joins, i.e., SQLite goes through all (maybe filtered) records of one table, and for each one, looks up the matching record(s) in the other table. Which of the two joined tables is chosen as the outer or the inner one depends on which lookup is estimated to be faster.
• In a query, SQLite uses at most one index per table.
• A multi-column index can be used for lookups on a subset of its columns only if all the leftmost columns are used. For example, your col1,col2 index can be used for lookups that use both col1 and col2, or for lookups that use only col1.

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

In your first query, the two-column index cannot be used for the lookup on col2. If the column id of the other table has an index, SQLite will just use that table as the inner table of the loop. If id is not indexed either, SQLite is likely to create a temporary index for this query.

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

In your second query, SQLite is likely to use t1 as the outer table because the WHERE filter will reduce the number of records that must be looked up in the other table. The two-column index can be used to search for matching col1 records first; then each of those records is joined with t2.

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

To check what indexes (if any) are used by a query, execute EXPLAIN QUERY PLAN.

这篇关于sqlite中的复合主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！