为什么 SQLite 拒绝使用可用索引? [英] Why is SQLite refusing to use available indexes?

问题描述

这是在 SQLite 中创建两个表和一个视图的以下代码:

Here is the following code to create two tables and a view in SQLite:

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 SELECT id FROM bar;

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 WHERE id='123'; 的结果是:

SCAN TABLE foo (~1000000 rows)
SCAN TABLE bar (~1000000 rows)
COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)
SCAN SUBQUERY 1 (~200000 rows)

SQL 小提琴:http://sqlfiddle.com/#!7/b5e79/1(使用WebSQL)

SQL Fiddle: http://sqlfiddle.com/#!7/b5e79/1 (use WebSQL)

如您所见，当有一个完全可用的索引时，它会进行表扫描.为什么?如何解决此问题以使用索引?

As you can see, it is doing table scans when there is a perfectly usable index. Why? How do I fix this to use the index?

推荐答案

这里可能会发生两件事

• 桌子太小了.只有几行，所有数据都可以放在一个无论如何都要读取的块中.因此优化器认为使用索引没有任何优势.这不太可能，因为所需的所有列都在索引中，因此需要较少的字节来填充.

• The tables are too small. With just a few rows, all data fits in a block that is read anyway. So the optimizer sees no advantage in using an index. This is unlikely as all columns needed are in the index and therefore need less bytes to be fullfilled.

两个 selects 之间的 union 等于 union distinct 意味着所有在第一个和第二个中重复的行第二个选择被淘汰.要找到它们，数据库必须对两个结果集进行排序和合并.如果你使用 union all 这个排序步骤是不必要的，因为所有的行都将填充 where 子句放在结果集中.

The union between the two selects is equal to union distinct means that all rows that are duplicate in the first and the second select are eliminated. To find them, the database must sort and merge both result sets. If you di a union all this sort step is not necessary as all rows, that fullfill the where clause are put in the result set.

尝试union all.这应该使用索引.

Try union all. This should use the index.

这篇关于为什么 SQLite 拒绝使用可用索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！