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

Question

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

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

对于我的其他运行良好的查询来说，Firefox插件通常会快一点(顶部快50毫秒，有时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

显然，慢的部分是"LIKE‘Juniors%’"，但这并不能解释为什么它在Java中慢，而在插件中慢。

如果我执行EXPLAIN QUERY PLAN，我会看到firefox插件使用以下Leagues表索引：Columns:"Season，Name，RealName"(RealName尚未在该查询中使用)。

如果我在Java中执行EXPLAIN QUERY PLAN，那么Leagues表使用的索引就是INTEGER PRIMARY KEY索引，我认为这就是问题所在。

在java中，我运行了上面的查询，然后使用相同的连接，我又运行了两次相同的查询，但第二次将l1.NameLIKE'Juniors%部分替换为p1.Sex=1和p1.Sex=2。最后两个查询在这两种情况下都很快，这进一步证明了问题来自l1.Name LIKE'Juniors%'

我在所有表上都有主键，在所有需要它的列上都有外键。我还有很多其他索引，因为我正在从头开始重新设计旧数据库，因为有很多重复的字段，我决定添加索引以使其更快，但在这种特殊情况下，我陷入了困境，尤其是因为它在一种情况下有效，但在另一种情况中无效。是否可能是我对表的索引过于激进，导致管理器更难选择正确的索引？

请随时询问有关表、列、查询等的更多信息。

编辑

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

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

这是Java查询执行的详细EXPLAIN QUERY PLAN，其中的列按顺序为：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

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

Firefox插件的解释查询计划是：

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中都使用PRIMARY KEY索引，而它们在Java中使用的似乎是"更好"的索引，我认为这很奇怪。

*在JOIN Leagues l1之后，我尝试使用INDEXED BY idxLeaguesRealName*，但性能保持不变(可能是因为SEARCH TABLE Leagues在Java中位于底部，而不是第一个表)。

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

我尝试过的另一件事是创建一个新的索引，它也使用主键字段(例如：Id、Season、Name)，但Query Planner不使用它。我甚至不知道把主键作为用户创建的索引中的字段之一是否是个好主意我正在尝试我能想到的一切，因为我不明白这两种运行查询的方式之间的性能差异，所以我在这里不知所措。

关于几乎相同的其他查询的额外信息

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

在JDBC中，EXPLAIN QUERY PLAN给出以下输出：

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

这与原始查询的计划有很大不同，因为这个查询使用的索引似乎比其他情况下只使用PRIMARY KEY索引更有意义。

我刚刚检查过，我的应用程序中还有其他查询执行缓慢。所有慢速查询都是具有'l1.NameLIKE'Juniors%'的查询，其他所有查询都运行得非常快。

我读到使用LIKE的查询运行缓慢，这会让我改变设计一些表的方式，比如添加一个字段"IsJuniorLeague"并与之进行比较，这可能会解决问题，但由于我已经看到可以使这些查询足够快，比如在Firefox插件中，我真的很想了解幕后发生了什么，因为我通常先在firefox中测试我的查询，然后再在应用程序中尝试，因为这样会更快。

Answer 1

这些差异可能是由于不同的SQLite版本造成的。(用SELECT sqlite_version();检查。)

阅读优化器检查表
在这个特定的查询中，您可以通过写以下内容强制使用索引：

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