尝试选择几行时慢行为

Question

我的sqlite数据库t1和t2中有两个表。t1有两个列a和b，t2有一个列，与t1中相同。我已经为两个表中的每一列建立了索引。我希望从表t1中选择t1.a存在于表t2中的所有行。所以我写了一个查询：

select t1.a, t1.b from t1 where t1.a in(select t2.a from t2) limit 10000000;

我使用限制来选择所有行，而不是前100行。

表t1包含6 000 000行，表t2包含100 000行。此查询执行1400。

但是，当我试图在这个查询中只选择t1.a时：

select t1.a from t1 where t1.a in(select t2.a from t2) limit 10000000;

它只有86毫秒。

解释查询计划：

SEARCH TABLE csv USING COVERING INDEX iin_idx (iin=?)
USING INDEX sqlite_autoindex_input_1 FOR IN-OPERATOR

问题：

1. 为什么sqlite有这样的行为？
2. 选择一个或几个列有什么区别？
3. 有可能加速这个查询吗？
4. 为什么sqlite使用自动索引而不是创建索引？

我想用155000行填充t1表，这样查询时间就会显著增加。

Answer 1

我想我找到了解决办法！我创建了一个额外的索引：

create index if not exists ab_b_idx on t1(a, b);

之后，选择a列和b列的速度与只选择一列一样快。

我在文档覆盖指数中找到了对这种行为的解释

但是，如果从表中提取的所有列都已在索引本身中可用，SQLite将使用索引中包含的值，并且永远不会查找原始表行。这样可以为每一行保存一个二进制搜索，并且可以使许多查询的运行速度提高一倍。

更新:在表t1中搜索10万行花费了550 on。

Answer 2

一种可能是磁盘缓存。从磁盘读取数据很慢，特别是在第一次运行查询时有硬盘驱动而不是固态驱动 (SSD)的情况下，缓存是“冷”的，数据必须从磁盘加载到内存中。第二次运行查询时，数据已经缓存在内存中，而且速度要快得多。

尝试运行几次查询，看看性能是否发生了变化。