为什么 SQLite 在添加 JOIN 时拒绝使用可用索引? [英] Why is SQLite refusing to use available indexes when adding a JOIN?
问题描述
创建数据库的查询是:
CREATE TABLE foo(id TEXT);
CREATE INDEX `foo.index` ON foo(id);
CREATE TABLE bar(id TEXT);
CREATE INDEX `bar.index` ON bar(id);
CREATE VIEW baz AS SELECT id FROM foo UNION ALL SELECT id FROM bar;
CREATE TABLE bam(id TEXT, value TEXT);
INSERT INTO foo VALUES('123');
INSERT INTO foo VALUES('1123');
INSERT INTO foo VALUES('2123');
INSERT INTO foo VALUES('3123');
INSERT INTO bar VALUES('44123');
INSERT INTO bar VALUES('441123');
INSERT INTO bar VALUES('442123');
INSERT INTO bar VALUES('443123');
EXPLAIN QUERY PLAN SELECT * FROM baz LEFT JOIN bam ON baz.id=bam.id WHERE baz.id IN ('123', '234'); 的结果是:>
The result of EXPLAIN QUERY PLAN SELECT * FROM baz LEFT JOIN bam ON baz.id=bam.id WHERE baz.id IN ('123', '234'); is:
SCAN TABLE foo (~1000000 rows)
SCAN TABLE bar (~1000000 rows)
COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
SCAN SUBQUERY 1 (~2000000 rows)
EXECUTE LIST SUBQUERY 4
SEARCH TABLE bam USING AUTOMATIC COVERING INDEX (id=?) (~7 rows)
有趣的是，如果我执行 EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM baz WHERE baz.id IN ('123', '234')) AS t LEFT JOIN ON t.id=bam.id ; 它仍然不使用索引，但如果我这样做 EXPLAIN QUERY PLAN SELECT * FROM baz WHERE baz.id IN ('123', '234'); 它确实如此.这是怎么回事?
Interestingly enough if I do EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM baz WHERE baz.id IN ('123', '234')) AS t LEFT JOIN bam ON t.id=bam.id ; it still doesnt use the index but if i do EXPLAIN QUERY PLAN SELECT * FROM baz WHERE baz.id IN ('123', '234'); it does. What is going on?
为什么不使用 foo 和 bar 上的索引?它确实使用没有 JOIN 部分的索引，如链接问题所示.
Why isnt it using the indexes on foo and bar?? It does use the indexes without the JOIN section as apparent in the linked question.
SQL 小提琴:http://sqlfiddle.com/#!7/32af2/14(使用 WebSQL)
SQL Fiddle: http://sqlfiddle.com/#!7/32af2/14 (use WebSQL)
推荐答案
索引不用，因为不需要；他们不会加快查询速度.
The indexes are not used because they are not needed; they would not speed up the query.
在 SQLite 中，连接被实现为嵌套循环连接，即数据库遍历一个表的所有记录，并为每个记录查找另一个表中的匹配记录.只有在第二个表中的查找需要一个索引；只需遍历第一个表的所有记录不需要索引.
In SQLite, joins are implemnted as nested loop joins, that is, the database goes through all records of one table, and for each record, looks up the matching record(s) in the other table. Only the lookup in the second table needs an index; just going through all the records of the first table does not need an index.
使用内连接，查询优化器可以在循环中选择哪个表是外表或内表(如果只有一个表有索引，它应该是内表).但是，有了左外连接，就没有选择了，左表必须是外表.
With an inner join, the query optimizer can choose which table is the outer or the inner table in the loop (if only one table has an index, it should be the inner table). However, with a left outer join, there is no choice, and the left table must be the outer table.
为了优化左外连接，(仅)右侧的表需要一个索引.
To optimize a left outer join, (only) the table on the right side needs an index.
这篇关于为什么 SQLite 在添加 JOIN 时拒绝使用可用索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
