sql - 使用JDBC进行SQLite查询要比使用Firefox SqliteManager插件慢得多

Question

我在Firefox的SQLiteManager插件中测试过的查询存在问题。

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

对于我运行良好的其他查询，Firefox插件通常会快一点（顶部快50ms，有时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表使用了以下索引：列：“季节，名称，实名”（此查询中尚未使用实名）。

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

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

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

随意询问有关表，列，查询等的更多信息。

编辑

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使用整数主键，因此它完全忽略其中带有“名称”的索引。

Firefox插件的EXPLAIN QUERY PLAN是：

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在此 link中的5.3中的规定，该索引具有以下含义。是低质量指标，因为在230个不同的联赛中，赛季仅需采用4个不同的值。我已经在运行查询之前运行了ANALYZE命令，因此，根据该链接，它应该可以解决使用低质量索引的问题。

我尝试的另一件事是创建一个新索引，该索引也使用主键字段（例如：Id，Season，Name），但是查询计划程序不使用它。我什至不知道将主键作为用户创建的索引中的字段之一不是一个好主意。我只是想尽我所能想到的一切，因为在这里我迷茫了，因为我不了解两种运行查询方式之间的性能差异。

几乎相同的其他查询的其他信息

正如我前面提到的，我运行其他查询几乎相同，只是将l1.Name LIKE'Juniors％'替换为p1.Sex = 1或p1.Sex =2。这些查询在Firefox中的执行时间大约为62ms并且在Java中为52毫秒，这意味着查询计划者在此类似查询上做得很好。

在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 KEYs索引更有意义，就像在另一种情况下一样。

我刚刚检查了一下，在我的应用程序中还有其他执行缓慢的查询。所有慢速查询都是具有'l1.Name LIKE'Juniors％'的查询，其他所有查询的运行速度都非常快。

我已经阅读到使用LIKE的查询运行缓慢，这将使我改变设计某些表的方式，例如添加字段“ IsJuniorLeague”并与之进行比较，这可能会解决问题，但是由于我看到了能够使这些查询足够快，例如在Firefox插件中，我真的想了解幕后情况，因为我通常先在firefox中测试我的查询，然后再在我的应用程序中尝试它们，因为这样可以更快。

Answer 1

差异可能是由于不同的SQLite版本。
（用SELECT sqlite_version();检查。）

阅读optimizer checklist。
在此特定查询中，您可以通过以下方式强制使用索引：

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