database - 尝试选择多行时，Sqlite Select 行为缓慢

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 使用自动索引而不是创建一个？

我想用 155 000 000 行填充 t1 表，因此查询时间会显着增加。

标签： databasesqliteselect

Answer 1

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

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