SQLite使用JDBC查询的速度比火狐SqliteManager插件慢得多。

Question

我在火狐的SQLiteManager插件中测试的一个查询有问题。

在Firefox插件中，这个特定的查询在大约60 ms内执行，但是当我使用最新的Sqlite驱动程序在Java中使用相同的查询和相同的数据库时，执行时间高达3.7秒。

Firefox插件对于我的其他运行良好的查询来说通常要快一些(顶部50‘s，有时JDBC更快)，但这可能是创建连接和将结果添加到列表中的开销，但是这个特定查询的性能差异是荒谬的。

以下是查询：

SELECT p1.Id, p1.FirstName || ' ' || p1.LastName AS PlayerName, sch1.LaneNum, l1.Name AS LeagueName, l1.Season, SUM(s1.Score) AS Series, e1.Date FROM Scores s1
JOIN SchedulePlayers sp1 ON  s1.SchedulePlayerId = sp1.Id
JOIN Schedules sch1 ON sp1.ScheduleId = sch1.Id
JOIN Players p1 ON sp1.PlayerId = p1.Id
JOIN TeamEncounters te1 ON sch1.TeamEncounterId = te1.Id
JOIN Encounters e1 ON te1.EncounterId = e1.Id
JOIN Leagues l1 ON e1.LeagueId = l1.Id

WHERE s1.GameNum < 4 AND l1.Name LIKE 'Juniors%' AND l1.Season = 2013 AND (sch1.LaneNum = 1 OR  sch1.LaneNum = 2) AND s1.IsBowlout = 0
GROUP BY p1.Id, l1.Id, e1.Id
ORDER BY Series DESC LIMIT 0,20

显然，慢的部分是“喜欢‘初中生%’”，但这并不能解释为什么它在Java中慢，而不是在插件中。

如果我执行解释查询计划，我会发现firefox插件对联赛表使用了以下索引:列：“RealName，Name，RealName”(在这个查询中还没有使用)。

如果我在Java中执行EXPLAIN查询计划，用于Leagues表的索引就是整数主键索引，这就是我认为问题所在的地方。

在java中，我运行上述查询，然后使用相同的连接再运行两次相同的查询，但第二次将l1.Name替换为'Juniors% part‘改为p1.Sex =1和p1.Sex =2。

我在所有表上都有主键，在需要它的所有列上有外键。我还有很多其他索引，因为我从头开始重新设计旧数据库，因为有很多重复的字段，我决定添加索引以使它更快，但在这种情况下，我被困住了，特别是因为它在一种情况下工作，而不是另一种情况。我是否可能过于积极地对表进行索引，使Manager更难选择正确的索引？

可以自由地询问有关表、列、查询等的更多信息。

编辑

火狐插件使用SQLite 3.7.17，JDBC驱动程序使用SQLite 3.8.0。我尝试使用3.7.20 JDBC驱动程序(找不到到3.7.17驱动程序的下载链接)，我也遇到了相同的性能问题，而其他一些查询的性能更差，所以我切换到3.8.0。

我编辑了性能时间，因为我在基准测试时犯了一个错误:上一次是为了多次运行查询。因此，在Firefox中，执行一次查询需要大约60 ms，而在Java中则需要3600 ms，因此这比我的应用程序多60倍，这对我的应用程序来说是不可接受的。

这里是Java执行的详细解释查询计划，其中的列依次为: SelectId、Order、From、Detail：

0 0 0 SEARCH TABLE Scores AS s1 USING INDEX idxScoresGameNumScore (GameNum<?)
0 1 1 SEARCH TABLE SchedulePlayers AS sp1 USING INTEGER PRIMARY KEY (rowid=?)
0 2 3 SEARCH TABLE Players AS p1 USING INTEGER PRIMARY KEY (rowid=?)
0 3 2 SEARCH TABLE Schedules AS sch1 USING INTEGER PRIMARY KEY (rowid=?)
0 0 0 EXECUTE LIST SUBQUERY 1
0 4 4 SEARCH TABLE TeamEncounters AS te1 USING INTEGER PRIMARY KEY (rowid=?)
0 5 5 SEARCH TABLE Encounters AS e1 USING INTEGER PRIMARY KEY (rowid=?)
0 6 6 SEARCH TABLE Leagues AS l1 USING INTEGER PRIMARY KEY (rowid=?)
0 0 0 USE TEMP B-TREE FOR GROUP BY
0 0 0 USE TEMP B-TREE FOR ORDER BY

正如您所看到的，联赛使用整数主键，因此它完全忽略了其中包含“Name”的索引。

火狐插件的解释查询计划是：

0 0 6 SEARCH TABLE Leagues AS l1 USING COVERING INDEX idxLeaguesRealName (Season=?) (~19 rows)
0 1 5 SEARCH TABLE Encounters AS e1 USING INDEX idxEncounters (LeagueId=?) (~16 rows)
0 2 4 SEARCH TABLE TeamEncounters AS te1 USING AUTOMATIC COVERING INDEX (EncounterId=?) (~6 rows)
0 3 2 SEARCH TABLE Schedules AS sch1 USING INDEX sqlite_autoindex_Schedules_1 (TeamEncounterId=?) (~1 rows)
0 4 1 SEARCH TABLE SchedulePlayers AS sp1 USING COVERING INDEX idxSchedulePlayers (ScheduleId=?) (~6 rows)
0 5 3 SEARCH TABLE Players AS p1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0 6 0 SEARCH TABLE Scores AS s1 USING INDEX sqlite_autoindex_Scores_1 (SchedulePlayerId=? AND GameNum<?) (~1 rows)
0 0 0 USE TEMP B-TREE FOR GROUP BY
0 0 0 USE TEMP B-TREE FOR ORDER BY

正如您所看到的，表的顺序也不一样，实际上，所有表都使用了Java中的主键索引，而它们在Java中使用的似乎是“更好”的索引，，我认为这很奇怪。

*我尝试使用idxLeaguesRealName *在加入l1之后使用索引，但是性能保持不变(可能是因为搜索表联赛在Java中位于底部，而不是第一个表)。

idxLeaguesRealName是一个关于季节、名称、RealName的索引，根据@CL发布的链接中的5.3，这是一个索引。是一个低质量的指数，因为赛季只需要4个不同的数值，230不同的联赛。不过，在运行查询之前，我已经运行了ANALYZE命令，因此根据该链接，它应该解决使用低质量索引的问题。

我尝试过的另一件事是创建一个新的索引，它也使用主键字段(例如: Id、季节、名称)，但是查询规划器不使用它。--我甚至不知道在用户创建的索引中把主键作为字段之一是否是个好主意。我只是在尝试我能想到的一切，因为我不理解这两种运行查询方式之间的性能差异。

关于几乎相同的其他查询的附加信息

正如我前面提到的，我运行的其他查询几乎是相同的，除了l1.Name类似于'Juniors%‘被替换为p1.Sex =1或p1.Sex = 2。这些查询分别在Firefox中执行62 ms，在Java中执行52 ms，这意味着查询计划器在这个类似的查询中做得很好。

在JDBC中，EXPLAIN查询计划提供了以下输出：

0 0 4 SCAN TABLE TeamEncounters AS te1 USING COVERING INDEX idxTeamEncounters
0 1 5 SEARCH TABLE Encounters AS e1 USING INTEGER PRIMARY KEY (rowid=?)
0 2 2 SEARCH TABLE Schedules AS sch1 USING INDEX sqlite_autoindex_Schedules_1 (TeamEncounterId=?)
0 0 0 EXECUTE LIST SUBQUERY 1
0 3 6 SEARCH TABLE Leagues AS l1 USING INTEGER PRIMARY KEY (rowid=?)
0 4 1 SEARCH TABLE SchedulePlayers AS sp1 USING COVERING INDEX idxSchedulePlayers (ScheduleId=?)
0 5 3 SEARCH TABLE Players AS p1 USING INTEGER PRIMARY KEY (rowid=?)
0 6 0 SEARCH TABLE Scores AS s1 USING INDEX sqlite_autoindex_Scores_1 (SchedulePlayerId=? AND GameNum<?)
0 0 0 USE TEMP B-TREE FOR GROUP BY
0 0 0 USE TEMP B-TREE FOR ORDER BY

这与原始查询的计划有很大不同，因为这一次使用的索引似乎比只使用主键索引更有意义，比如在另一种情况下。

我刚刚检查了，我的应用程序中还有其他查询执行得很慢。所有慢速查询都是具有'l1.Name‘类似于’少年%‘的查询，其他所有查询都运行得非常快。

我读过那些使用慢速运行的查询，这会让我切换设计一些表的方式，比如添加一个字段“IsJuniorLeague”并与之进行比较，这可能会解决这个问题，但是既然我已经看到能够足够快地执行这些查询，就像在Firefox插件中一样，我真的很想了解幕后发生了什么，因为我通常先在firefox中测试我的查询，然后再在我的应用程序中尝试它们，因为这样做更快。

Answer 1

这种差异可能是由于不同的SQLite版本造成的。(向SELECT sqlite_version();查询。)

读优化检查表。

在这个特定的查询中，您可以通过以下方式强制使用索引：

... JOIN Leagues l1 INDEXED BY MyThreeColumnIndex ON ...