sqlite - 单独的索引以进行选择优化

Question

我有一个带有列的表“数据”
id（auto_increment）id_device（integer）时间戳（数字）

我需要执行以下选择：

select * from data where id<10000000 and id_device=345
or
select * from data where id<10000000 and id_device=345 and timestamp>'2017-01-01 10:00:00' and timestamp<'2017-03-01 08:00:00'

首先选择：
分别为“ id”和“ id_device”创建索引是否更好？
还是使索引像INDEX id，id_device这样的性能更好？

第二选择：
更好地为“ id”创建单独的索引，为“ id_device”创建单独的索引，为“ timestamp”创建单独的索引？
还是对索引进行索引（例如INDEX id，id_device，timestamp）对性能更好？

Answer 1

我的简短回答：这取决于您的数据。

更长：如果id_device = 345为true，且行少于id <10000000，则id_device应该首先在多列索引中列出：... ON data（id_device，id）。同样，如果选择速度对您/您的用户比插入/更新/删除速度更重要，那么为什么不添加很多索引并将其留给查询计划者以选择要使用的索引：

create index i01_tbl on tbl(id);
create index i02_tbl on tbl(id_device);
create index i03_tbl on tbl(timestamp);
create index i04_tbl on tbl(id,id_device);
create index i05_tbl on tbl(id_device,id);
create index i06_tbl on tbl(timestamp,id);
create index i07_tbl on tbl(id,timestamp);
create index i08_tbl on tbl(id_device,timestamp);
create index i09_tbl on tbl(timestamp,id_device);
create index i10_tbl on tbl(id, id_device, timestamp);
create index i11_tbl on tbl(id_device, id, timestamp);
create index i12_tbl on tbl(id_device, timestamp, id);
create index i13_tbl on tbl(id, timestamp, id_device);
create index i14_tbl on tbl(timestamp, id_device, id);
create index i15_tbl on tbl(timestamp, id, id_device);

数据库中的查询计划程序算法（sqlite也有）在这方面通常是不错的选择。尤其是如果您定期或在更改大量数据之后运行ANALYZE sqlite命令。拥有多个索引的缺点是插入和删除（如果涉及索引列，则进行更新）和磁盘/内存使用量增加，因此速度较慢。在重要的SQL上使用解释计划（在速度上很重要），以检查是否使用了哪些索引。如果从不使用索引，或者仅在没有索引的情况下才使用索引，则可以删除这些索引。另请注意，数据库的较新版本（sqlite，oracle，postgresql）可以具有较新的查询计划程序算法，对于大多数SELECT来说，这些算法更好，但对于某些SELECT算法，则可能变得更糟。对真实数据集进行真实测试是最好的判断方法。创建哪个索引不是一门精确的科学，也没有适合所有情况的明确规则。