Android Paging3 + Room 性能问题：清除筛选后UI卡顿

核心问题分析 #

清除筛选返回全列表时，Room生成的SQL为SELECT * FROM ( SELECT * FROM wordle_people ) LIMIT 90 OFFSET 0，SQLite会先全表扫描生成临时结果集，再对结果集应用分页。10万条数据下，临时集的生成和处理会占用大量CPU和内存，引发GC并阻塞UI线程。

具体解决步骤 #

1. 给全表查询添加主键排序，利用索引避免全表扫描

修改Dao中的查询方法，给所有分页查询添加ORDER BY id ASC：

@Dao
abstract class WordlePersonDao : BaseDao<WordlePerson>("wordle_people") {
    // 修改全表查询，添加主键排序
    @Query("SELECT * FROM wordle_people ORDER BY id ASC")
    abstract fun pagingSource(): PagingSource<Int, WordlePerson>

    // 筛选查询也同步添加排序，保持逻辑一致性
    @Query("SELECT * FROM wordle_people where gender IN (:genderFilter) ORDER BY id ASC")
    abstract fun pagingSourceFilterGender(genderFilter: List<Gender>): PagingSource<Int, WordlePerson>

    @Query("SELECT * FROM wordle_people where color IN (:colorFilter) ORDER BY id ASC")
    abstract fun pagingSourceFilterColor(colorFilter: List<Color>): PagingSource<Int, WordlePerson>

    @Query("SELECT * FROM wordle_people where color IN (:colorFilter) AND gender IN (:genderFilter) ORDER BY id ASC")
    abstract fun pagingSourceFilterGenderAndColor(genderFilter: List<Gender>, colorFilter: List<Color>): PagingSource<Int, WordlePerson>
}

原理：主键id自带索引，添加排序后SQLite会直接通过索引定位分页位置，无需生成全表临时集，查询性能会大幅提升。

2. 优化PagingConfig配置，减少不必要的计算

调整ViewModel中的Pager配置，关闭占位符并合理设置加载参数：

Pager(
    config = PagingConfig(
        pageSize = 30,
        prefetchDistance = 15, // 提前预取下一页，避免滑动时卡顿
        initialLoadSize = 60, // 初始加载2页，降低单次加载压力
        enablePlaceholders = false // 关闭占位符，避免Room执行全表计数
    ),
    pagingSourceFactory = {
        // ... 原筛选逻辑
    }
).flow

原理：关闭enablePlaceholders后，Room不会执行COUNT(*)全表扫描操作；合理设置预取距离和初始加载量，让数据加载更平滑。

3. 统一筛选查询接口，减少PagingSource切换开销

将所有筛选逻辑合并为一个Dao方法，避免多次创建不同的PagingSource：

@Dao
abstract class WordlePersonDao : BaseDao<WordlePerson>("wordle_people") {
    @Query("""
        SELECT * FROM wordle_people 
        WHERE (:genderFilter IS NULL OR gender IN (:genderFilter))
          AND (:colorFilter IS NULL OR color IN (:colorFilter))
        ORDER BY id ASC
    """)
    abstract fun pagingSourceWithFilters(
        genderFilter: List<Gender>?,
        colorFilter: List<Color>?
    ): PagingSource<Int, WordlePerson>
}

修改ViewModel中的pagingSourceFactory：

pagingSourceFactory = {
    val genderList = it.genderSet.takeIf { it.isNotEmpty() }?.toList()
    val colorList = it.colorSet.takeIf { it.isNotEmpty() }?.toList()
    wordlePeopleDao.pagingSourceWithFilters(genderList, colorList)
}

原理：复用同一个查询方法，Room可以缓存查询计划，减少对象创建和GC触发概率，同时简化代码逻辑。

4. 确保数据库操作在IO线程执行

显式指定Dispatcher，避免数据库操作意外阻塞UI线程：

pagingSourceFactory = Dispatchers.IO.asExecutor() {
    val genderList = it.genderSet.takeIf { it.isNotEmpty() }?.toList()
    val colorList = it.colorSet.takeIf { it.isNotEmpty() }?.toList()
    wordlePeopleDao.pagingSourceWithFilters(genderList, colorList)
}

验证效果 #

修改后，Room生成的全表分页SQL会变为：

SELECT * FROM wordle_people ORDER BY id ASC LIMIT 90 OFFSET 0

SQLite会直接利用主键索引获取分页数据，避免全表扫描，清除筛选时的卡顿问题会得到明显缓解。

内容的提问来源于stack exchange，提问作者johngray1965