如何加速尽管覆盖了所有表上的索引但速度很慢的 4 路 SQLITE 内部联接

Question

我有 4 个表需要很长时间才能加入 SQLITE(版本 3.31.1(。以下是涉及的 5 个表的简化视图(第一个"对象"不直接用于连接(：

Table Objects:
Objects.Id
Table Dates:  # Each day has 2 different values of date, some tables refer to one, some to the other
Dates.Date_cal1  # Calendar 1
Dates.Date_cal2  # Calendar 2
Table A:
A.Object_Id
A.Date_c1
A.Value_A
Table B:
B.Object_Id
B.Date_c1
B.Value_B
Table C:
C.Object_Id
C.Date_c2
C.Value_C

(Object_id，日期(对构成每个 A/B/C 的主键。我正在运行的查询是这样的：

SELECT Dates.Date_cal1, A.Value_A, B.Value_B, C.Value_C
FROM Dates
INNER JOIN A ON A.Date_c1 = Dates.Date_cal1
INNER JOIN B ON B.Date_c1 = Dates.Date_cal1 AND B.Object_Id = A.Object_Id
INNER JOIN C ON C.Date_c2 = Dates.Date_cal2 AND C.Object_Id = A.Object_Id
WHERE A.Object_Id=14
ORDER BY Dates.Date_cal1 ASC;

运行查询大约需要 1-2 分钟。一些注意事项：

1. 我知道其中一些看起来很愚蠢(例如。A 和 B 可以合并，如下所示(。假设真实案例有这一切的原因。
2. 我有 4 个表的覆盖索引：日期有 (Date_cal1，Date_Cal2( 和 (Date_cal2，Date_cal1(，A 和 B 和 C 各有一个按 (Object_Id，Date_c*，Value_* 排序，其中 * 是 1 或 2，A-或 B-或 C 视情况而定(。
就大小而言，Dates 有 360 行，
3. A 和 B 有 360 万行，C 有 1 亿行(C 更大，因为"cal2"是一个更细粒度的日历 - 你可以把它想象成 cal1 是每月一次，cal2 是每天一次(。
4. 数据非常规则，这意味着 10k object_ids中的每一个在 A/B/C 中使用大致相等的行数。

当我为上面的查询运行解释查询计划时，我得到：

QUERY PLAN
|--SEARCH TABLE B USING COVERING INDEX B_index (Object_Id=?)
|--SEARCH TABLE A USING PRIMARY KEY (Date_c1=? AND Object_Id=?)
|--SEARCH TABLE Dates USING PRIMARY KEY (Date_cal1=?)
|--SEARCH TABLE C USING PRIMARY KEY (Date_c2=? AND Object_Id=?)
`--USE TEMP B-TREE FOR ORDER BY

请注意，只有第一部分提到利用覆盖索引。我不确定 1 分钟是否合理，但我希望充分利用覆盖索引意味着 A/B/C 的日期和object_id过滤会快得多(因为对于 Object_Id=14，A 和 B 中总共有 360 行，C 中总共有 11k 行(。

感谢任何人可以提供的任何帮助！

Answer 1

只是发布一个答案来帮助将来偶然发现这个的任何人。正如@Shawn在上面的评论中所说，运行 ANALYZE 命令是我解决此问题的关键。即使有覆盖索引，SQLITE 也不会总是在没有 ANALYZE 运行的情况下使用它们。

Answer 2

首先，我将查询编写为：

FROM Dates JOIN
A
ON A.Date_c1 = Dates.Date_cal1 JOIN
B
ON B.Date_c1 = A.Date_c1 AND
B.Object_Id = A.Object_Id JOIN
C
ON C.Date_c2 = Dates.Date_cal2 AND C.Object_Id = A.Object_Id
WHERE A.Object_Id = 14

我建议索引：

• A(ObjectId, Date_c1)
• B(Object_Id, Date_c1)
• Dates(Date_cal1, Date_cal2)
• C(Objet_Id Date_C2)

这应该允许where子句使用索引过滤数据，其余的连接也使用索引。