我的函数使用固定的有序键从以下字典输入创建过滤查询
input = {
"A": ["string"],
"B": [
"1234",
"4567"
],
"C": ["string"],
"D": ["string"]
}每个键代表SQLite文件的列名，值是要过滤的值。在sqlite db文件中还有另一个名为idx的索引列。
我最初为所有列组合创建了索引，但有人指出它太多了(如果需要，请参阅我前面的问题以供参考)
过滤查询通常如下所示：
1.SELECT * FROM result_table WHERE idx >= 1900 AND (B in (1234)) LIMIT 100
1.SELECT * FROM result_table WHERE idx >= 1900 AND (B in (1234,5678)) LIMIT 100
1.SELECT * FROM result_table WHERE idx >= 1900 AND (B in (1234) and D in ('somestring')) LIMIT 100
1.SELECT * FROM result_table WHERE idx >= 1900 AND (B in (1234,5678)) ORDER BY A ASC, ORDER BY D DESC LIMIT 100
1.SELECT * FROM result_table WHERE idx >= 1900 AND (B in (1234,5678) and C in ('somestring2')) ORDER BY A ASC, ORDER BY C DESC LIMIT 100
诸若此类
因此它是单个或多个WHERE和ORDER BY子句混合匹配
从我得到的上一个问题的评论中，我了解了当我不使用ORDER BY子句，但当我放置ORDER BY时，查询不使用我创建的任何索引时，我应该如何创建索引。
我创建的索引如下：
(with/without idx)
(idx,) A, B, C, D
(idx,) A, C, D
(idx,) A, D
(idx,) B, C, D
(idx,) B, D
(idx,) C, D
(idx,) D我应该创建更多哪些索引来申请WHERE和ORDER BY子句的组合查询？
1条答案
按热度按时间1cklez4t1#
查询1和2可以使用索引B、C、D或B、D。由于IDX受一个不等式的约束，因此它只能在索引中紧挨着B的右侧而不是之前使用。因此，IDX会是更好的选择，但IDX不是。
查询3的最佳索引是B、D、IDX(或D、B、IDX)。B，D应该是从您创建的索引中选择的那个。
要执行查询4，SQLite可以使用查询1或2中使用的相同索引来加快WHERE子句的执行速度，也可以使用索引A、D来加快ORDER BY子句的执行速度。Sqlite将根据它认为更好的计划进行选择，该选择将基于表中有多少行、WHERE BY子句的选择性有多大(B值为1234或5678的百分比)以及LIMIT子句的选择性有多大(100行比总行少吗？)。为了正确地做出这些假设，SQLite必须已经使用Analyze收集了有关表内容的统计信息。
类似地，查询5将使用索引B、C、D(B、C、IDX会更好)作为WHERE或使用索引A、C、D作为ORDER BY
引用SQLite文档：
SQLite使用基于成本的查询规划器。当有两种或两种以上的方法来解决同一查询时，SQLite会尝试使用每个计划来估计运行查询所需的总时间，然后使用估计成本最低的计划。成本主要是根据估计的时间计算的，因此根据表的大小和WHERE子句约束的可用范围等，这种情况可能会发生任何一种情况。但一般来说，如果没有其他原因，可能会选择索引排序，因为它在排序之前不需要在临时存储中累积整个结果集，因此使用的临时存储要少得多。
至于您的问题，最好的索引是可以同时满足WHERE和ORDER BY子句的索引。
如果有超过100条记录的值为1234或5678，那么对于查询4，最好的记录可能是(A，D，B，IDX)。否则，(B，A，D，IDX)可能是更好的选择。
对于查询5，最好的可能是(B，C，A，IDX)、(C，B，A，IDX)、(A，C，B，IDX)或这些列的一些其他奇怪的组合。
我的建议是下载最新的sqlite.exe(命令行界面)，然后在单独的行上使用
.expert --sample 100命令和SQL查询。如果这些特定查询存在于数据库中，“.Expert”命令将建议可能有助于这些特定查询的索引(参考：https://www.sqlite.org/cli.html#index_recommendations_sqlite_expert_).