如何在Room中将两个PagingSource查询合并为单个PagingSource

问题背景 #

我正在开发一个Android项目，使用Room管理数十亿条记录，包含Folder和File两个独立表的实体。目前通过两个Room查询分别获取它们的PagingSource，但需要将两个结果合并为单个PagingSource，并按名称、创建时间或大小统一排序。

现有Room查询代码：

@Query("""
    SELECT * FROM Folder 
    WHERE parentFolderId = :parentFolderId 
    ORDER BY 
    CASE 
        WHEN :sortBy = 'NAME' THEN name 
        WHEN :sortBy = 'TIME' THEN creationTime 
        WHEN :sortBy = 'SIZE' THEN size 
    END
""")
fun getFolders(parentFolderId: Long, sortBy: SortBy): PagingSource<Long, Folder>

@Query("""
    SELECT * FROM File 
    WHERE folderId = :parentFolderId 
    ORDER BY 
    CASE 
        WHEN :sortBy = 'NAME' THEN name 
        WHEN :sortBy = 'TIME' THEN creationTime 
        WHEN :sortBy = 'SIZE' THEN size
    END
""")
fun getFiles(parentFolderId: Long, sortBy: SortBy): PagingSource<Long, File>

实体定义：

@Serializable
@Entity(
    foreignKeys = [
        ForeignKey(
            entity = Folder::class,
            parentColumns = ["folderId"],
            childColumns = ["parentFolderId"],
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Folder::class,
            parentColumns = ["folderId"],
            childColumns = ["folderId"],
            onDelete = ForeignKey.CASCADE
        )
    ],
    indices = [Index(value = ["folderId"]), Index(value = ["parentFolderId"])],
    primaryKeys = ["folderId", "parentFolderId"]
)
data class Folder(
    val folderId: Long = UUID.randomUUID().mostSignificantBits and Long.MAX_VALUE,
    val parentFolderId: Long? = null,
    val name: String,
    val size: Long,
    val userTag: UserTag? = null,
    val creationTime: Long,
    val lastOpenedTime: Long? = null,
    val userId: String
)

@Serializable
@Entity(
    foreignKeys = [
        ForeignKey(
            entity = Folder::class,
            parentColumns = ["folderId"],
            childColumns = ["folderId"],
            onDelete = ForeignKey.CASCADE
        )
    ],
    indices = [Index(value = ["fileId"]), Index(value = ["folderId"])],
    primaryKeys = ["fileId", "folderId"]
)
data class File(
    val fileId: Long = UUID.randomUUID().mostSignificantBits and Long.MAX_VALUE,
    val name: String,
    val size: Long,
    val fileType: FileType,
    val userTag: UserTag? = null,
    val creationTime: Long,
    val lastOpenedTime: Long? = null,
    val userId: String,
    val folderId: Long? = null,
    val chunkCount: Int,
    val fileHash: String
)

高效解决方案：数据库层联合查询（推荐） #

针对数十亿数据量，内存合并会导致严重性能问题甚至OOM，最优方案是在数据库层面通过UNION ALL合并两个结果集并统一排序，利用SQL索引优化查询效率。

步骤1：定义统一数据模型 #

首先创建密封类用于封装合并后的文件夹和文件项，保留原实体的完整信息：

sealed class FileSystemItem {
    data class FolderItem(val folder: Folder) : FileSystemItem()
    data class FileItem(val file: File) : FileSystemItem()
}

再定义一个Room映射用的数据类，对应联合查询返回的字段：

// 仅用于Room查询结果映射，无需创建实际表
data class CombinedQueryItem(
    val itemType: String, // 标记是文件夹还是文件："FOLDER"或"FILE"
    val id: Long, // 对应Folder的folderId或File的fileId
    val name: String,
    val size: Long,
    val creationTime: Long,
    // Folder专属字段
    val parentFolderId: Long?,
    val folderUserTag: UserTag?,
    val folderLastOpenedTime: Long?,
    val folderUserId: String,
    // File专属字段
    val fileType: FileType?,
    val fileUserTag: UserTag?,
    val fileLastOpenedTime: Long?,
    val fileUserId: String,
    val chunkCount: Int?,
    val fileHash: String?
)

步骤2：编写Room联合查询 #

调整查询语句，用UNION ALL合并两个表的结果，确保返回字段一致，并统一排序：

@Query("""
    SELECT 
        'FOLDER' AS item_type,
        folderId AS id,
        name,
        size,
        creationTime,
        parentFolderId,
        userTag AS folder_user_tag,
        lastOpenedTime AS folder_last_opened_time,
        userId AS folder_user_id,
        NULL AS file_type,
        NULL AS file_user_tag,
        NULL AS file_last_opened_time,
        NULL AS file_user_id,
        NULL AS chunk_count,
        NULL AS file_hash
    FROM Folder 
    WHERE parentFolderId = :parentFolderId
    UNION ALL
    SELECT 
        'FILE' AS item_type,
        fileId AS id,
        name,
        size,
        creationTime,
        NULL AS parentFolderId,
        NULL AS folder_user_tag,
        NULL AS folder_last_opened_time,
        NULL AS folder_user_id,
        fileType,
        userTag AS file_user_tag,
        lastOpenedTime AS file_last_opened_time,
        userId AS file_user_id,
        chunkCount,
        fileHash
    FROM File 
    WHERE folderId = :parentFolderId
    ORDER BY 
        CASE 
            WHEN :sortBy = 'NAME' THEN name 
            WHEN :sortBy = 'TIME' THEN creationTime 
            WHEN :sortBy = 'SIZE' THEN size 
        END
""")
fun getCombinedQueryItems(parentFolderId: Long, sortBy: SortBy): PagingSource<Long, CombinedQueryItem>

步骤3：转换为统一模型 #

在Repository层将查询结果转换为FileSystemItem密封类，供上层使用：

fun getCombinedPagingSource(parentFolderId: Long, sortBy: SortBy): PagingSource<Long, FileSystemItem> {
    return dao.getCombinedQueryItems(parentFolderId, sortBy).map { queryItem ->
        if (queryItem.itemType == "FOLDER") {
            FileSystemItem.FolderItem(
                Folder(
                    folderId = queryItem.id,
                    parentFolderId = queryItem.parentFolderId,
                    name = queryItem.name,
                    size = queryItem.size,
                    userTag = queryItem.folderUserTag,
                    creationTime = queryItem.creationTime,
                    lastOpenedTime = queryItem.folderLastOpenedTime,
                    userId = queryItem.folderUserId
                )
            )
        } else {
            FileSystemItem.FileItem(
                File(
                    fileId = queryItem.id,
                    name = queryItem.name,
                    size = queryItem.size,
                    fileType = queryItem.fileType!!,
                    userTag = queryItem.fileUserTag,
                    creationTime = queryItem.creationTime,
                    lastOpenedTime = queryItem.fileLastOpenedTime,
                    userId = queryItem.fileUserId!!,
                    folderId = parentFolderId,
                    chunkCount = queryItem.chunkCount!!,
                    fileHash = queryItem.fileHash!!
                )
            )
        }
    }
}

性能优化要点 #

• 确保排序字段（name、creationTime、size）在Folder和File表上都创建了索引，加速排序查询：

  // 在Folder实体的indices中添加
  Index(value = ["name"]),
  Index(value = ["creationTime"]),
  Index(value = ["size"]),
  // 在File实体的indices中添加
  Index(value = ["name"]),
  Index(value = ["creationTime"]),
  Index(value = ["size"])
  

• UNION ALL比UNION更高效，因为它不会去重，符合文件夹和文件ID不重复的业务逻辑。

备选方案：内存合并PagingSource（不推荐大数据量） #

如果因特殊需求无法修改数据库查询，可以在内存中合并两个PagingSource，但仅适合小数据量场景，大数据量下会有严重性能问题：

class CombinedPagingSource(
    private val folderSource: PagingSource<Long, Folder>,
    private val fileSource: PagingSource<Long, File>,
    private val sortBy: SortBy
) : PagingSource<Long, FileSystemItem>() {

    override suspend fun load(params: LoadParams<Long>): LoadResult<Long, FileSystemItem> {
        // 分别加载两个数据源的当前页数据
        val folderLoadResult = folderSource.load(params)
        val fileLoadResult = fileSource.load(params)

        // 处理错误
        if (folderLoadResult is LoadResult.Error || fileLoadResult is LoadResult.Error) {
            return LoadResult.Error(
                folderLoadResult.exceptionOrNull() ?: fileLoadResult.exceptionOrNull()!!
            )
        }

        // 转换为统一模型
        val folderItems = (folderLoadResult as LoadResult.Page).data.map { FileSystemItem.FolderItem(it) }
        val fileItems = (fileLoadResult as LoadResult.Page).data.map { FileSystemItem.FileItem(it) }

        // 统一排序
        val sortedList = (folderItems + fileItems).sortedWith(
            when (sortBy) {
                SortBy.NAME -> compareBy { item ->
                    when (item) {
                        is FileSystemItem.FolderItem -> item.folder.name
                        is FileSystemItem.FileItem -> item.file.name
                    }
                }
                SortBy.TIME -> compareBy { item ->
                    when (item) {
                        is FileSystemItem.FolderItem -> item.folder.creationTime
                        is FileSystemItem.FileItem -> item.file.creationTime
                    }
                }
                SortBy.SIZE -> compareBy { item ->
                    when (item) {
                        is FileSystemItem.FolderItem -> item.folder.size
                        is FileSystemItem.FileItem -> item.file.size
                    }
                }
            }
        )

        // 返回合并后的分页结果（注意：此逻辑仅为示例，实际页码处理需更严谨，避免数据重复/遗漏）
        return LoadResult.Page(
            data = sortedList,
            prevKey = folderLoadResult.prevKey ?: fileLoadResult.prevKey,
            nextKey = folderLoadResult.nextKey ?: fileLoadResult.nextKey
        )
    }

    override fun getRefreshKey(state: PagingState<Long, FileSystemItem>): Long? {
        return state.anchorPosition?.let { anchorPosition ->
            state.closestPageToPosition(anchorPosition)?.prevKey?.plus(1)
                ?: state.closestPageToPosition(anchorPosition)?.nextKey?.minus(1)
        }
    }
}

该方案的问题 #

• 每一页都需要加载两个数据源的数据到内存，合并排序，内存占用高。
• 分页逻辑复杂，容易出现数据重复或遗漏，无法保证大数据量下的稳定性。

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