安卓应用程序的SQLite优化

Question

我们的Android应用程序中大约有7-8个表，每个表平均有8列。读和写操作都是在数据库上执行的，我正在试验并试图找到提高DataAccess层性能的方法。因此，到目前为止，我尝试了以下几点：

1. 在where子句中使用位置参数(原因:以便sqlite使用相同的执行计划)
2. 用事务封装插入和更新(原因:默认情况下，每个db操作都包含在事务中。这样做将减少开销)
3. 索引:除了在主键和唯一键列上默认创建的索引外，我没有创建任何显式索引。(原因:索引会提高查找时间)

我在妄想中提到了我的假设，如果我错了，请纠正我。

问题：

1. 我可以在这个列表中添加其他内容吗？我在某个地方读到，避免使用db日志可以提高更新的性能？这是神话还是事实？如果再来一次，怎么做呢？
2. 在SQLite3中允许嵌套事务吗？它们如何影响性能？问题是，我有一个在循环中运行更新的函数，因此，我将循环封装在一个事务块中。有时，这个函数是从其他函数内部的另一个循环调用的。调用函数还将循环封装在事务块中。这样的事务嵌套是如何影响性能的？
3. 我的查询中的where子句使用多个列来构建谓词。这些列不一定由主键或唯一列组成。我也应该在这些列上创建索引吗？为这样一个表创建多个索引是个好主意吗？

Answer 1

1. 准确地列出您需要优化的查询。获取一个典型数据库的副本，并使用REPL对查询进行计时。在优化过程中，使用此方法对任何收益进行基准测试。
2. 使用ANALYZE可以使SQLite的查询计划器更有效地工作。
3. 对于SELECT和UPDATE，索引可以提高，但是只有当您创建的索引能够被需要加速的查询实际使用时。在查询中使用EXPLAIN QUERY PLAN查看将使用哪个索引，或者查询是否需要完整的表扫描。对于大型表，完整的表扫描是错误的，您可能需要索引。对于任何给定的查询，只使用一个索引。如果您有多个谓词，那么将使用的索引是预期将最大程度地减少结果集的索引(基于ANALYZE)。可以有包含多个列的索引(用于使用多个谓词辅助查询)。如果有具有多列的索引，则只有当谓词从左到右匹配索引时才能使用，没有空白处(但末尾未使用的列是可以使用的)。如果使用排序谓词(<、<=、>等)，则需要在索引的最后一列中使用该谓词。同时使用WHERE谓词和ORDER BY都需要索引，而SQLite只能使用一个索引，因此这可能会影响性能。您拥有的索引越多，INSERT的速度就越慢，因此您必须为您的情况制定最佳的折衷方案。
4. 如果您有更复杂的查询无法使用您可能创建的任何索引，则可以将模式去规范化，将数据结构化，使查询更简单，并且可以使用索引来回答。
5. 如果您正在执行大量的INSERT，请尝试删除索引并在结束时重新创建它们。您需要对此进行基准测试。
6. SQLite 是否支持嵌套事务？使用保存点，但我不确定您在那里是否会获得任何性能上的好处。
7. 你可以通过牺牲数据完整性来获得大量的速度。如果您可以自己从数据库损坏中恢复，那么这可能对您有用。您可能只能在进行密集操作时才能执行此操作，而这些操作可以手动恢复。

我不知道你能从Android应用程序那里得到多少。在一般的更详细的指南文档中，有一个用于优化SQLite的SQLite。

Answer 2

下面是一些代码，可以从运行中的安卓应用程序将EXPLAIN QUERY PLAN结果输入到Android中。我从SQLiteOpenHelper dbHelper和SQLiteQueryBuilder qb开始。

String sql = qb.buildQuery(projection,selection,selectionArgs,groupBy,having,sortOrder,limit);
android.util.Log.d("EXPLAIN",sql + "; " + java.util.Arrays.toString(selectionArgs));
Cursor c = dbHelper.getReadableDatabase().rawQuery("EXPLAIN QUERY PLAN " + sql,selectionArgs);
if(c.moveToFirst()) {
    do {
        StringBuilder sb = new StringBuilder();
        for(int i = 0; i < c.getColumnCount(); i++) {
            sb.append(c.getColumnName(i)).append(":").append(c.getString(i)).append(", ");
        }
        android.util.Log.d("EXPLAIN",sb.toString());
    } while(c.moveToNext());
}
c.close();

我把它放到了我的ContentProvider.query()中，现在我可以确切地看到所有查询是如何执行的。(在我的例子中，问题似乎是查询太多，而不是索引使用不当；但这可能会帮助其他人.)

Answer 3

我想补充以下几点：

1. 在某些情况下，使用rawQuery()代替使用ContentValues构建将加快速度。当然，编写原始查询有点乏味。
2. 如果您有大量的字符串/文本类型数据，请考虑使用全文搜索(FTS3)创建虚拟表，这样可以运行更快的查询。你可以在谷歌搜索准确的速度提高。