Android SQLite 在复杂查询中的性能

假设我有这样的查询

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. 执行更简单的查询并使用一些自定义代码手动处理它们会更快吗？

如果我是你，我会将你的 sqlite 数据库复制到主机，然后尝试在某些 SQLite GUI 中手动执行它，同时替换绑定变量（?）与您拥有的实际变量值。对于 Windows 上的 GUI，我真的很喜欢SQLite 专家个人，以及在 Linux 上sqliteman还不错。

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

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

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