sqlite - 何时包含索引(自动启发式)

Question

我有一个软件，它接受数据库，并使用它根据用户想要的内容生成图表(主要是 SELECT AVG(<input1>) AS x, AVG(<intput2>) as y FROM <input3> WHERE <key> IN (<vals..> AND ... 形式的查询)。这效果很好。

我有一个简单的脚本，它传递了许多(通常是大量)文件，每个文件描述一行

name=foo
x=12
y=23.4
....... etc.......

该脚本会遍历每个文件，保存变量名称和 INSERT查询每个。然后它加载变量名称 sort | uniq是他们，并制作了 CREATE TABLE语句(有趣的是，sqlite 可以让所有列都为 NUMERIC ，即使它们实际上最终包含文本数据)。一旦完成，它就会执行 INSERTS (在单笔交易中，否则需要很长时间)。

为了提高性能，我在每一行上添加了一个基本索引。但是，这会在一定程度上显着增加数据库大小，并且仅提供适度的改进。

数据分为三种基本类型:

1. 单个值，表示程序版本等。
2. 一些值 (<10)，指示使用的输入参数等内容
3. 许多值 (>1000)，主要是输出数据。

第一种类型显然不需要索引，因为它永远不会被排序。第二种类型应该有一个索引，因为它通常会被过滤。第三种类型可能不需要索引，因为它将在输出中使用。在将特定值放入数据库之前确定其类型会很烦人，但这是可能的。

我的问题有两个:

1. 除了我所看到的大小增加之外，无关索引是否还存在一些隐性成本？
2. 是否有更好的方法来为 WHERE foo IN (5) AND bar IN (12,14,15) 形式的过滤查询建立索引？请注意，除了知道它将是类型 2 列之外，我不知道用户将选择哪些列。

Answer 1

阅读相关文档: Query Planning ; Query Optimizer Overview ; EXPLAIN QUERY PLAN .

优化查询最重要的是避免 I/O，因此少于 10 行的表不应该建立索引，因为无论如何所有数据都适合单个页面，因此拥有索引只会迫使 SQLite 读取另一个页面为索引。

当您在大表中查找记录时，索引非常重要。

1. 无关索引会使表更新速度变慢，因为每个索引也需要更新。

2. SQLite 在查询中每个表最多可以使用一个索引。通过在 foo 和 bar 两列上使用单个索引，可以最好地优化此特定查询。但是，为查找列的所有可能组合创建此类索引很可能不值得。如果查询是动态生成的，最好的想法可能是为每一列创建一个具有良好选择性的索引，并依靠 SQLite 选择最好的索引。

并且不要忘记运行 ANALYZE .