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中手动执行它，同时用?您拥有的实际变量值替换绑定变量（）。对于Windows上的GUI，我真的很喜欢SQLite Expert
Personal
，而在Linux sqliteman上非常不错。

在调试SQL（在命令行或GUI中）时，请确保通过在EXPLAIN和/或下运行它们来分析SQL语句EXPLAIN QUERY PLAN。当心表扫描。您应该尝试通过添加索引来消除昂贵的扫描。但是不要索引所有内容-
可能会使情况更糟。通常，通过使用复合（多列）索引，可以大大提高性能。注意，在任何给定的表上，SQLite不能仅使用一个索引（在运行给定的SQL语句时）-因此，明智地选择索引。（另请参阅查询计划中的基本说明。）

为了解决您对Java和SQLite中数据处理的担忧-我认为 针对关系数据进行
完全优化（使用适当的索引等）的SQLite查询将（几乎）总是比在Java中手动处理此数据要快。在您的情况下，这尤其必须正确-您的所有数据基本上都是关系型的。

不过，请注意以下几点：默认情况下，使用Java的Android APK可能比SQLite拥有更多的内存访问权限-
您可能想使用setMaxSqlCacheSize()（相当于PRAGMA cache_size）来增加数据库的SQLite缓存大小。Android的默认值为10（最大为100），请尝试增加该值，看看是否对您的查询有所影响。请注意，此设置的桌面SQLite默认默认值更高-2000。