java - 复杂查询中的 Android SQLite 性能

Question

假设我有这种查询

String sql = "SELECT s.team_id, s.team_name, s.gp, s.w, s.t, s.l, s.go, s.ga, s.score, s.p FROM "
           + "(SELECT team_id, team_name, SUM (gp) gp, SUM (w) w, SUM (t) t, SUM (l) l, SUM (GO) go, SUM (GA) ga, SUM (GO)- SUM (GA) score, SUM (2*w+t) p FROM "
           + "(SELECT t._id team_id, t.name team_name, COUNT(CASE WHEN score_home IS NOT NULL THEN 1 END) gp, COUNT (CASE WHEN score_home > score_away THEN 1 END) w,"
           + " COUNT (CASE WHEN score_home = score_away THEN 1 END) t, COUNT (CASE WHEN score_home < score_away THEN 1 END) l,"
           + " SUM (score_home) go, SUM (score_away) ga"
           + " FROM team_table t LEFT OUTER JOIN match_table m ON m.team_home = t._id"
           + " WHERE t.tournament_id = ? GROUP BY t._id, t.name"
           + " UNION ALL"
           + " SELECT t._id team_id, t.name team_name, COUNT(CASE WHEN score_away IS NOT NULL THEN 1 END) gp, COUNT (CASE WHEN score_home < score_away THEN 1 END) w,"
           + " COUNT (CASE WHEN score_home = score_away THEN 1 END) t, COUNT (CASE WHEN score_home > score_away THEN 1 END) l,"
           + " SUM (score_away) go, SUM (score_home) ga"
           + " FROM team_table t LEFT OUTER JOIN match_table m ON m.team_away = t._id"
           + " WHERE t.tournament_id = ? GROUP BY t._id, t.name)"
           + " GROUP BY team_id, team_name) s"
           + " ORDER BY s.p DESC, s.score DESC, s.go ASC";

然后像这样使用

Cursor cursor = database.rawQuery(sql, args);

cursor.moveToFirst();
while (!cursor.isAfterLast()) {
    TeamStats stat = new TeamStats();

    stat.setTeamId(cursor.getLong(0));
    stat.setTeamName(cursor.getString(1));
    stat.setGamesPlayed(cursor.getInt(2));
    stat.setWins(cursor.getInt(3));
    stat.setTies(cursor.getInt(4));
    stat.setLoses(cursor.getInt(5));
    stat.setGoalsOwn(cursor.getInt(6));
    stat.setGoalsAgaist(cursor.getInt(7));
    stat.setScore(cursor.getInt(8));
    stat.setPoints(cursor.getInt(9));

    stats.add(stat);
    cursor.moveToNext();
}
cursor.close();

因此它从许多表中选择值，执行一些操作等。如您所见，查询非常复杂(非常难以调试)并且性能似乎没有我预期的那么好。我的问题是:

1. 我可以使用某种准备好的语句来提高性能吗？
2. 执行更简单的查询并使用一些自定义代码手动处理它们会更快吗？

Answer 1

如果我是你，我会将你的 sqlite 数据库复制到主机，然后尝试在一些 SQLite GUI 中手动执行它，同时用你拥有的实际变量值替换绑定(bind)变量 (?)。对于 Windows 上的 GUI，我非常喜欢 SQLite Expert Personal , 在 Linux 上 sqliteman 非常好。

在调试 SQL 时(在命令行或 GUI 中)，一定要通过在 EXPLAIN 和/或 EXPLAIN QUERY PLAN 下运行 SQL 语句来分析它们。 .注意表扫描。您应该尝试通过添加索引来消除昂贵的扫描。但不要索引所有内容——这可能会使事情变得更糟。通常，您可以通过使用复合(多列)索引获得巨大的性能提升。请注意，在任何给定的表上，SQLite 不能使用多个索引(在运行给定的 SQL 语句时)——因此，请明智地选择索引。 (另请参阅 Query Planning 中的基本解释。)

为了解决您对 Java 与 SQLite 中数据处理的担忧 - 我认为完全优化(使用适当的索引等)SQLite 查询针对关系数据将(几乎)总是比在 Java 中手动处理这些数据更快。在您的情况下尤其如此 - 您的所有数据基本上都是相关的。

不过有一点要注意:默认情况下，您使用 Java 的 Android APK 可以访问比 SQLite 更多的内存 - 您可能希望使用 setMaxSqlCacheSize() 增加数据库的 SQLite 缓存大小。 (相当于 PRAGMA cache_size)。 Android 默认值为 10(最大 100)，尝试增加它并查看是否对您的查询有任何影响。请注意，此设置的桌面 SQLite 默认值要高得多 - 2000。