我有一个需要 4 秒才能完成的查询:
SELECT MAX(Date), Bond_Id, Sell_Price FROM Quotes GROUP BY Bond_Id;
该表有大约 31K 条记录:
CREATE TABLE Quotes (
_id INTEGER PRIMARY KEY AUTOINCREMENT,
Bond_Id INTEGER NOT NULL,
Date TEXT NOT NULL,
Buy_Yield REAL NOT NULL,
Sell_Yield REAL NOT NULL,
Buy_Price REAL NOT NULL,
Sell_Price REAL NOT NULL,
Base_Price REAL NOT NULL,
FOREIGN KEY (Bond_Id) REFERENCES Bonds(_id));
CREATE INDEX QuotesNdx ON Quotes(Bond_Id);
我设法将查询时间从 7 秒减少到 4 秒,这仍然是不可接受的。我尝试ANALYZE
,其他复合索引并删除外键。
EXPLAIN
输出:
0 Trace 0 0 0 explain select max(date),bond_id,sell_price from quotes group by bond_id; 00
1 Noop 0 0 0 00
2 Integer 0 6 0 00
3 Integer 0 5 0 00
4 Goto 0 20 0 00
5 Integer 1 6 0 00
6 Return 0 0 0 00
7 IfPos 5 9 0 00
8 Return 0 0 0 00
9 AggFinal 1 1 0 max(1) 00
10 SCopy 1 9 0 00
11 SCopy 2 10 0 00
12 SCopy 3 11 0 00
13 ResultRow 9 3 0 00
14 Return 0 0 0 00
15 Null 0 2 0 00
16 Null 0 3 0 00
17 Null 0 4 0 00
18 Null 0 1 0 00
19 Return 0 0 0 00
20 Gosub 0 15 0 00
21 Goto 0 48 0 00
22 SetNumColumns 0 7 0 00
23 OpenRead 0 6 0 00
24 SetNumColumns 0 2 0 00
25 OpenRead 2 7 0 keyinfo(1,BINARY) 00
26 Rewind 2 44 13 0 00
27 Noop 2 -7 13 0 01
28 IdxRowid 2 16 0 00
29 MoveGe 0 0 16 00
30 Column 2 0 8 00
31 Eq 7 36 8 collseq(BINARY) 10
32 Move 8 7 0 00
33 Gosub 0 7 0 00
34 IfPos 6 47 0 00
35 Gosub 0 15 0 00
36 Column 0 2 17 00
37 CollSeq 0 0 0 collseq(BINARY) 00
38 AggStep 0 17 1 max(1) 01
39 SCopy 7 2 0 00
40 Column 0 6 3 00
41 RealAffinity 3 0 0 00
42 Integer 1 5 0 00
43 Next 2 27 0 00
44 Close 0 0 0 00
45 Close 2 0 0 00
46 Gosub 0 7 0 00
47 Halt 0 0 0 00
48 Transaction 0 0 0 00
49 VerifyCookie 0 9 0 00
50 TableLock 0 6 0 Quotes 00
51 Goto 0 22 0 00
可以通过创建覆盖索引来优化此特定查询;列必须按它们用于查找的顺序排列:
CREATE INDEX whatever ON Quotes(Bond_ID, Date, Sell_Price);
谢谢大家的回答。实际上,我查询中的罪犯是"GROUP BY"。我设法通过阅读SQLite的SELECT(http://sqlite.org/lang_select.html)文档中的这一特定段落找到了解决方案:
"如果 SELECT 语句是带有 GROUP BY 子句的聚合查询,则针对数据集的每一行计算指定为 GROUP BY 子句一部分的每个表达式。然后根据结果将每一行分配给一个"组";计算 GROUP BY 表达式的结果相同的行将分配给同一组。出于对行进行分组的目的,NULL 值被视为相等。在计算 GROUP BY 子句中的表达式时,通常使用规则来比较文本值。GROUP BY 子句中的表达式不必是出现在结果中的表达式。GROUP BY 子句中的表达式可能不是聚合表达式。
因此,解决方案是创建一个包含(Date,Bond_Id)的复合索引,并将我的查询替换为以下内容:
SELECT Date, Bond_Id, Sell_Price FROM Quotes
WHERE Bond_Id=Bonds._id
AND Date=(SELECT MAX(Date) FROM Quotes);
现在,此查询只需不到 1 秒即可完成,这很棒!
您的查询:
选择 最大(日期), Bond_Id, Sell_Price 从 报价 分组 按Bond_Id;
第一:您的查询不正确。不应使用保留字作为字段名称。在您的情况下是一个字段"日期"
如果您使用GROUP BY中的任何字段,则还应将SELECT中的所有其他字段与任何分组功能(最小/最大/计数/等)一起使用。
相关查询应为:
选择最大(日期),Bond_Id 从报价组中按Bond_Id;
或
从报价组中选择 Bond_Id,MAX(Sell_Price),Bond_Id具有"日期"= MAX("日期");
第二:
您需要为 MIN/MAX/... 中使用的每个字段创建索引。和分组依据