Large Database Queries on Android[译]

SQLiteCursor and CursorAdapter

SQLiteCursor是Android SQLite数据库查询返回的类型。我们可以耗费固定的初始化加载成本来检索大数据量的查询结果。首次查询将初始化一个通常为2MB大小的缓冲窗口CursorWindow，用于存放从数据库中查询的结果。当我们所要查询的行不位于该缓冲区时，SQLiteCursor就会重新查询数据库并刷新窗口。通过这种方式，SQLiteCursor实现了一种大小固定的分页方式。

Android API 1 时就提供了一个叫CursorAdapter的类，提供一种简便的方式来方便从Cursor中读取数据绑定到ListView的items中。它的功能是挺好的，但每次需要加载新数据时，它都是直接在UI线程上查询的数据库，违背了现代的响应式App思想。那么有人可能会问：我们就不能改造一个在异步线程加载数据的新CursorAdapter吗？毕竟SQLiteCursor已经内置了分页功能。

Problems with paging within SQLiteCursor

其实，不能基于SQLiteCursor分页的归根结底的原因正是由于它使用了固定缓存大小的窗口来对查询结果进行分页。以下是我们在尝试使用基于SQLiteCursor内部分页的Paging Library时罗列的一些问题：

1. SQLiteCursor doesn’t hold any database transaction open

在准备深入了解Paging前，其实我对Android中的SQLite尤其是Cursors不是太了解，所以我只得假设SQLiteCursor在加载了一个窗口后就暂停，当需要时（下个窗口）再恢复。这样的话，访问第10个窗口与访问第1个窗口是一样的效率，其实最终证明这是非常错误的。每次需要读取一个新的窗口时，底层查询都是从0开始并跳过我们不需要的行，最后将结果填充到窗口的。这是因为SQLiteCursor无法恢复一个查询。

也就是说，假如我们想访问集合中的1000到1050项，我们不得不跳过大量的项，才能访问到下一页，这样子下来，每个后续窗口必须跳过越来越多的查询，严重降低查询速度。这相当于使用SQL OFFSET关键字跳过内容，尽管不是对内容分页最有效的方式，但利用SQLiteCursor来进行分页时无法避免。

2. SQLiteCursor.getCount() is required, and scans entire query

在读取第一行之前，SQLiteCursor需要通过调用getCount()来进行边界检测，但是SQLite必须检索所查询的整个结果集才能知道其大小（like a linked list），无形加大了开销。如果我们将本地的一组大数据集逐渐分页显示到UI中，用以响应用户的滚动，我们可能根本就不需要知道查询结果的整个大小，那么前面所说的getCount()无疑增加了不必要的前期工作。

3. QLiteCursor.getCount() always loads the first window of rows

在上面边界检测步骤中，检索查询结果集时，如果有用，SQLiteCursor同时还会自动地从0开始填充缓冲窗口。

预加载这些数据，以便它可以提前知道大约多少行对一个窗口适合（下面的更多内容）。如果我们从查询的位置0开始展示数据，则此这种预加载机制是合理的，但是如果触发保存的实例保存和恢复机制，那么恢复的位置可能会从离列表位置0的很远处开始检索，而这个位置已经超出了初始的窗口。如果想要显示第三个内容窗口的数据，则必须首先加载并丢弃2MB数据。

// code is here

@Override
public int getCount() {
    if (mCount == NO_COUNT) {
        fillWindow(0);
    }
    return mCount;
}

4. SQLiteCursor may load data you didn’t ask for

Cursor.moveToPosition()保证所请求的行在窗口中。但是SQLiteCursor并不会在请求发起的位置处开始填充窗口。因为SQLiteCursor不能保证App就一定是向后读取，因此它实际上是从离结果集的目标位置大约1/3处的取出开始填充窗口。这意味着当一个窗口加载完成后，CursorAdapter向后滚动几行时不会触发刷新（重新填充）窗口。这也意味着在第一次加载之后加载的每个2MB数据窗口都加载了约650KB或更多的我们已经看到的数据。

// code is here

public static int cursorPickFillWindowStartPosition(
            int cursorPosition, int cursorWindowCapacity) {
    return Math.max(cursorPosition - cursorWindowCapacity / 3, 0);
}

5. SQLiteCursor loading position can be unpredictable

前面说到SQLiteCursor尝试加载指定位置数据时，会尝试向前倒推1/3往窗口填充数据，这就意味着它必须猜测一个缓冲窗口大致可以容纳多少行数据，因此它会将查询出所有的行先填充到第一个窗口。但不幸的是，如果我们所查询的内容的行数是不确定的（例如用户评论），那么这种方式就不可靠，SQLiteCursor可能还没到到达指定位置，窗口就填满了，那么就必须丢弃掉已填充的然后再次填充新的。例如假如我们查询的一大段数据，触发重新填充窗口，就有可能加载到窗口的数据只有一小部分。

6. Cursors require closure

Cursor必须显式地调用close()进行关闭，所以无论哪里，都必须在不再需要时调用方法以释放。尤其是CursorAdapter，它内部并没有帮我们实现这些，而是把这个事情交给了开发者，那么为了写入或重用Cursor，就必须编写额外代码来处理情况，例如当Activity stopping时。

7. SQLiteCursor does not know data has changed

在窗口首次填充完成后，CursorAdapter并不会track数据库是否改变，也就是说如果我们新增或删除了一些数据，SQLiteCursor’s的缓存就是脏数据了，那么当我们移动到一个未知的行位置时就可能导致Exception或在某些情况下得到不一致的数据。例如假如我们已经加载了N行数据，此时在位置0处插入了一条新的数据，那么当我们尝试读取第N+1行时，结果会导致重复加载第N行。

Avoiding the Problems

由以上所有的问题可知，SQLCursor适用的场景非常有限。不过幸运的是这些问题都有一个简单的解决方案：小查询（small query）。采用刚好装满CursorWindow的查询就可以避免以上问题，这就是为什么我们如此喜欢Paging and Room的原因。通常我们建议一页大小配置为10到20个，并且每次只查询这么多。

尽管如此，选择每页大小也有一些需要了解的：较大的窗口查询通常可以提高性能，较小的可以减少等待时间并节约内存。如果DB不是瓶颈的话，对于大item一般每次10个差不多，如果每项数据很小的话，每次300可能会更好点，否则的话查询就很浪费了。

如果正好有谁想使用SQLiteCursor内部的分页机制来懒加载一组大量的数据的打算，我们建议你可以考虑另外的方案：要么使用Paging Library，它可以与Room完美配合；要么就自己实现并且需要确保每次查询的结果足够小，以刚好适配单个CursorWindow的大小。

如果使用Room和Paging Library将一个大查询分页为多个小查询，我们需要将以下代码：

@Dao
interface UserDao {
    // regular list query — falls over with too much data
    @Query(“SELECT * FROM user ORDER BY mAge DESC”)
    LiveData<List<User>> loadUsersByAgeDesc();
}

改为：

@Dao
interface UserDao {
    // paged query — handles arbitrarily large queries
    @Query(“SELECT * FROM user ORDER BY mAge DESC”)
    DataSource.Factory<Integer, User> loadUsersByAgeDesc();
}

然后通过LivePagedListBuilder包装，得到一个可以处理任意大小的结果集LiveData<PagedList>：

1 2	LiveData<PagedList<User>> users = new LivePagedListBuilder<>( userDao.loadUsersByAgeDesc(), /page size/ 20).build();

上面的代码最终得到的分页结果LiveData，在数据库数据变化时会实时同步给所有已订阅的观察者。

最后附上参考用例：Google’s Paging sample on Github

Android Platform

“ 我们目前正在考虑在未来的Android版本中改进SQLiteCursor的一些行为。例如将CursorWindow的大小和1/3的加载机制修改为可配置的方案，因为实际使用现状看起来与当时设计SQLiteCursor和CursorAdapter时所想的截然不同。当我们决定更改时，我们同时一定会更新Room Persistence Library以使这些新特性保持查询效率 ”。

linjiang's blog