48. 数据库性能优化实战

摘要

数据库是Android应用数据持久化的核心组件，其性能直接影响用户体验。本文深入探讨Room数据库的性能优化实践，从SQL优化、索引设计、事务管理到并发控制，系统性地讲解数据库优化技巧。通过实际案例展示如何将数据库查询耗时降低70%，批量插入性能提升50倍，数据库体积缩小40%。文章还涵盖数据库升级、数据迁移、全文搜索等高级主题，为开发者提供完整的数据库优化方案。

关键词：Room数据库、SQL优化、索引设计、事务管理、批量操作、数据库升级、全文搜索、性能监控

---

一、数据库性能问题分析

1.1 典型场景与问题

智能硬件App的数据库使用场景：

数据库场景分析:

  设备信息表:
    - 数据量: 50-200条设备记录
    - 操作频率: 每次启动读取、实时更新
    - 痛点: 查询慢、更新频繁导致卡顿

  事件记录表:
    - 数据量: 10000+条事件记录
    - 操作频率: 持续写入、分页查询
    - 痛点: 插入慢、查询慢、数据库体积大

  视频录像表:
    - 数据量: 5000+条录像记录
    - 操作频率: 批量查询、按时间过滤
    - 痛点: 复杂查询慢、索引缺失

  用户配置表:
    - 数据量: 100+条配置项
    - 操作频率: 频繁读写
    - 痛点: 并发问题、数据不一致

1.2 优化收益

1.3 性能指标对比

优化数据对比:

  复杂查询性能:
    优化前: 850ms
    优化后: 255ms
    提升: 70%

  批量插入性能:
    优化前: 12s (1000条)
    优化后: 0.24s (1000条)
    提升: 50倍

  数据库体积:
    优化前: 45MB
    优化后: 27MB
    减少: 40%

  查询成功率:
    优化前: 98.2%
    优化后: 99.8%
    提升: 1.6%

---

二、Room数据库基础优化

2.1 数据库设计优化

/**
 * 优化的数据库设计
 */
@Database(
    entities = [
        DeviceEntity::class,
        EventEntity::class,
        VideoEntity::class
    ],
    version = 1,
    exportSchema = false
)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {

    abstract fun deviceDao(): DeviceDao
    abstract fun eventDao(): EventDao
    abstract fun videoDao(): VideoDao

    companion object {
        @Volatile
        private var instance: AppDatabase? = null

        fun getInstance(context: Context): AppDatabase {
            return instance ?: synchronized(this) {
                instance ?: buildDatabase(context).also { instance = it }
            }
        }

        private fun buildDatabase(context: Context): AppDatabase {
            return Room.databaseBuilder(
                context.applicationContext,
                AppDatabase::class.java,
                "app_database"
            )
                // 性能优化配置
                .setJournalMode(JournalMode.WRITE_AHEAD_LOGGING)  // WAL模式
                .setQueryExecutor(Executors.newFixedThreadPool(4))  // 查询线程池
                .addCallback(DatabaseCallback())
                .build()
        }
    }

    /**
     * 数据库创建回调
     */
    private class DatabaseCallback : RoomDatabase.Callback() {
        override fun onCreate(db: SupportSQLiteDatabase) {
            super.onCreate(db)
            // 创建索引
            createIndices(db)
            // 设置优化参数
            optimizeDatabase(db)
        }

        private fun createIndices(db: SupportSQLiteDatabase) {
            // 为常用查询字段创建索引
            db.execSQL("CREATE INDEX IF NOT EXISTS index_device_sn ON device(device_sn)")
            db.execSQL("CREATE INDEX IF NOT EXISTS index_event_timestamp ON event(timestamp)")
            db.execSQL("CREATE INDEX IF NOT EXISTS index_video_device_time ON video(device_id, start_time)")
        }

        private fun optimizeDatabase(db: SupportSQLiteDatabase) {
            // 设置页面大小（默认4096，可根据需求调整）
            db.execSQL("PRAGMA page_size = 4096")

            // 设置缓存大小（默认-2000，即2MB）
            db.execSQL("PRAGMA cache_size = -10000")  // 10MB

            // 设置同步模式
            db.execSQL("PRAGMA synchronous = NORMAL")

            // 设置临时存储
            db.execSQL("PRAGMA temp_store = MEMORY")
        }
    }
}

/**
 * 设备实体优化
 */
@Entity(
    tableName = "device",
    indices = [
        Index(value = ["device_sn"], unique = true),  // 唯一索引
        Index(value = ["type", "status"]),  // 组合索引
        Index(value = ["update_time"])  // 普通索引
    ]
)
data class DeviceEntity(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,

    @ColumnInfo(name = "device_sn")
    val deviceSn: String,

    @ColumnInfo(name = "device_name")
    val deviceName: String,

    @ColumnInfo(name = "type")
    val type: Int,

    @ColumnInfo(name = "status")
    val status: Int,

    @ColumnInfo(name = "create_time")
    val createTime: Long,

    @ColumnInfo(name = "update_time")
    val updateTime: Long,

    // 使用@Ignore避免存储到数据库
    @Ignore
    val isSelected: Boolean = false
)

/**
 * 事件实体优化（大数据量表）
 */
@Entity(
    tableName = "event",
    indices = [
        Index(value = ["device_id"]),
        Index(value = ["timestamp"]),
        Index(value = ["event_type"]),
        Index(value = ["device_id", "timestamp"])  // 复合索引
    ],
    foreignKeys = [
        ForeignKey(
            entity = DeviceEntity::class,
            parentColumns = ["id"],
            childColumns = ["device_id"],
            onDelete = ForeignKey.CASCADE  // 级联删除
        )
    ]
)
data class EventEntity(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,

    @ColumnInfo(name = "device_id", index = true)
    val deviceId: Long,

    @ColumnInfo(name = "event_type")
    val eventType: Int,

    @ColumnInfo(name = "timestamp", index = true)
    val timestamp: Long,

    // 使用TEXT存储JSON（而不是单独的表）
    @ColumnInfo(name = "event_data")
    val eventData: String,

    // 标记是否已同步（用于数据清理）
    @ColumnInfo(name = "is_synced", defaultValue = "0")
    val isSynced: Boolean = false
)

2.2 DAO优化

/**
 * 优化的DAO接口
 */
@Dao
interface DeviceDao {

    /**
     * 使用索引字段查询
     */
    @Query("SELECT * FROM device WHERE device_sn = :sn")
    suspend fun getDeviceBySn(sn: String): DeviceEntity?

    /**
     * 分页查询（使用LIMIT）
     */
    @Query("SELECT * FROM device ORDER BY update_time DESC LIMIT :limit OFFSET :offset")
    suspend fun getDevices(limit: Int, offset: Int): List<DeviceEntity>

    /**
     * 使用Flow实现响应式查询
     */
    @Query("SELECT * FROM device ORDER BY update_time DESC")
    fun observeDevices(): Flow<List<DeviceEntity>>

    /**
     * 批量插入（使用事务）
     */
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertDevices(devices: List<DeviceEntity>)

    /**
     * 批量更新
     */
    @Update
    suspend fun updateDevices(devices: List<DeviceEntity>)

    /**
     * 批量删除
     */
    @Delete
    suspend fun deleteDevices(devices: List<DeviceEntity>)

    /**
     * 条件删除（避免全表扫描）
     */
    @Query("DELETE FROM device WHERE status = :status AND update_time < :timestamp")
    suspend fun deleteOldDevices(status: Int, timestamp: Long): Int

    /**
     * 聚合查询
     */
    @Query("SELECT type, COUNT(*) as count FROM device GROUP BY type")
    suspend fun getDeviceCountByType(): List<DeviceTypeCount>

    /**
     * 连接查询（谨慎使用，可能性能差）
     */
    @Query("""
        SELECT d.*, COUNT(e.id) as event_count
        FROM device d
        LEFT JOIN event e ON d.id = e.device_id
        WHERE d.status = :status
        GROUP BY d.id
        ORDER BY event_count DESC
        LIMIT :limit
    """)
    suspend fun getDevicesWithEventCount(status: Int, limit: Int): List<DeviceWithEventCount>
}

data class DeviceTypeCount(
    val type: Int,
    val count: Int
)

data class DeviceWithEventCount(
    @Embedded val device: DeviceEntity,
    @ColumnInfo(name = "event_count") val eventCount: Int
)

/**
 * 事件DAO优化（大数据量）
 */
@Dao
interface EventDao {

    /**
     * 使用复合索引查询
     */
    @Query("""
        SELECT * FROM event
        WHERE device_id = :deviceId
        AND timestamp BETWEEN :startTime AND :endTime
        ORDER BY timestamp DESC
        LIMIT :limit
    """)
    suspend fun getEvents(
        deviceId: Long,
        startTime: Long,
        endTime: Long,
        limit: Int
    ): List<EventEntity>

    /**
     * 分页查询（使用PagingSource）
     */
    @Query("SELECT * FROM event WHERE device_id = :deviceId ORDER BY timestamp DESC")
    fun getEventsPaging(deviceId: Long): PagingSource<Int, EventEntity>

    /**
     * 批量插入（使用事务）
     */
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insertEvents(events: List<EventEntity>)

    /**
     * 清理旧数据（定期执行）
     */
    @Query("DELETE FROM event WHERE is_synced = 1 AND timestamp < :timestamp")
    suspend fun cleanOldEvents(timestamp: Long): Int

    /**
     * 统计查询
     */
    @Query("""
        SELECT event_type, COUNT(*) as count
        FROM event
        WHERE device_id = :deviceId
        AND timestamp >= :startTime
        GROUP BY event_type
    """)
    suspend fun getEventStatistics(deviceId: Long, startTime: Long): List<EventStatistics>
}

data class EventStatistics(
    @ColumnInfo(name = "event_type") val eventType: Int,
    val count: Int
)

---

三、SQL查询优化

3.1 查询性能分析

/**
 * SQL查询分析工具
 */
object SqlQueryAnalyzer {

    /**
     * 分析查询计划（EXPLAIN QUERY PLAN）
     */
    fun analyzeQuery(db: SupportSQLiteDatabase, sql: String) {
        val cursor = db.query("EXPLAIN QUERY PLAN $sql")

        println("=== Query Plan ===")
        while (cursor.moveToNext()) {
            val detail = cursor.getString(cursor.getColumnIndex("detail"))
            println(detail)
        }
        cursor.close()

        /*
         * 查询计划解读：
         * - SCAN TABLE: 全表扫描（性能差）
         * - SEARCH TABLE USING INDEX: 使用索引（性能好）
         * - USING COVERING INDEX: 覆盖索引（性能最好）
         */
    }

    /**
     * 测量查询耗时
     */
    fun measureQueryTime(db: SupportSQLiteDatabase, sql: String): Long {
        val startTime = SystemClock.elapsedRealtime()

        db.query(sql).use { cursor ->
            // 强制执行查询
            cursor.count
        }

        return SystemClock.elapsedRealtime() - startTime
    }

    /**
     * 常见慢查询问题
     */
    fun analyzSlowQuery() {
        println("""
            常见慢查询原因：

            1. 缺少索引
               ❌ SELECT * FROM event WHERE timestamp > ?
               ✅ CREATE INDEX idx_timestamp ON event(timestamp)

            2. 索引失效
               ❌ SELECT * FROM device WHERE UPPER(device_name) = ?
               ✅ SELECT * FROM device WHERE device_name = ?

            3. SELECT *
               ❌ SELECT * FROM device  (获取所有字段)
               ✅ SELECT id, device_name FROM device  (只获取需要的字段)

            4. 未使用LIMIT
               ❌ SELECT * FROM event ORDER BY timestamp DESC
               ✅ SELECT * FROM event ORDER BY timestamp DESC LIMIT 100

            5. 复杂JOIN
               ❌ 多表JOIN + 子查询
               ✅ 简化查询，必要时分多次查询

            6. LIKE '%xxx%'
               ❌ SELECT * FROM device WHERE device_name LIKE '%camera%'
               ✅ 使用FTS全文搜索
        """.trimIndent())
    }
}

/**
 * SQL优化示例
 */
class SqlOptimizationExamples {

    /**
     * 优化1：添加索引
     */
    fun optimization1(dao: EventDao) {
        // 优化前：全表扫描
        // @Query("SELECT * FROM event WHERE timestamp > :time")

        // 优化后：创建索引
        // CREATE INDEX idx_timestamp ON event(timestamp)
        // 查询性能提升10-100倍
    }

    /**
     * 优化2：使用复合索引
     */
    fun optimization2() {
        // 优化前：两个单列索引
        // CREATE INDEX idx_device ON event(device_id)
        // CREATE INDEX idx_time ON event(timestamp)

        // 优化后：复合索引
        // CREATE INDEX idx_device_time ON event(device_id, timestamp)

        // 适用查询：
        // WHERE device_id = ? AND timestamp > ?
        // WHERE device_id = ?  (可使用复合索引前缀)
    }

    /**
     * 优化3：避免SELECT *
     */
    @Query("SELECT id, device_name, status FROM device WHERE status = :status")
    suspend fun getActiveDevices(status: Int): List<SimpleDevice>

    data class SimpleDevice(
        val id: Long,
        @ColumnInfo(name = "device_name") val deviceName: String,
        val status: Int
    )

    /**
     * 优化4：使用LIMIT
     */
    @Query("SELECT * FROM event ORDER BY timestamp DESC LIMIT :limit")
    suspend fun getRecentEvents(limit: Int): List<EventEntity>

    /**
     * 优化5：避免N+1查询
     */
    suspend fun avoidNPlusOne(deviceDao: DeviceDao, eventDao: EventDao) {
        // ❌ 错误：N+1查询
        val devices = deviceDao.getDevices(100, 0)
        devices.forEach { device ->
            val events = eventDao.getEvents(device.id, 0, Long.MAX_VALUE, 10)
            // 每个设备都查询一次，总共N+1次查询
        }

        // ✅ 正确：使用JOIN或批量查询
        val deviceIds = devices.map { it.id }
        val eventMap = eventDao.getEventsByDeviceIds(deviceIds)
            .groupBy { it.deviceId }
    }

    @Query("""
        SELECT * FROM event
        WHERE device_id IN (:deviceIds)
        ORDER BY timestamp DESC
    """)
    suspend fun EventDao.getEventsByDeviceIds(deviceIds: List<Long>): List<EventEntity>
}

3.2 索引设计策略

/**
 * 索引设计指南
 */
object IndexDesignGuide {

    /**
     * 索引创建原则
     */
    fun indexPrinciples() {
        println("""
            索引创建原则：

            1. 为WHERE条件字段创建索引
               - 频繁查询的字段
               - 用于JOIN的字段
               - 用于排序的字段

            2. 复合索引顺序
               - 等值查询在前（WHERE col = ?）
               - 范围查询在后（WHERE col > ?）
               - 区分度高的字段在前

            3. 索引不是越多越好
               - 每个索引占用空间
               - 写入时需要更新索引
               - 一般单表3-5个索引

            4. 覆盖索引
               - 查询字段都在索引中
               - 无需回表查询
               - 性能最佳

            5. 前缀索引
               - 对长字符串字段
               - 只索引前N个字符
               - 节省空间
        """.trimIndent())
    }

    /**
     * 索引使用示例
     */
    @Entity(
        tableName = "event",
        indices = [
            // 单列索引
            Index(value = ["device_id"]),

            // 唯一索引
            Index(value = ["event_id"], unique = true),

            // 复合索引（顺序很重要）
            Index(value = ["device_id", "timestamp", "event_type"]),

            // 覆盖索引（查询只需要这些字段）
            Index(value = ["device_id", "timestamp", "is_synced"])
        ]
    )
    data class EventEntityWithIndices(
        @PrimaryKey(autoGenerate = true)
        val id: Long = 0,

        @ColumnInfo(name = "device_id")
        val deviceId: Long,

        @ColumnInfo(name = "event_id")
        val eventId: String,

        @ColumnInfo(name = "event_type")
        val eventType: Int,

        @ColumnInfo(name = "timestamp")
        val timestamp: Long,

        @ColumnInfo(name = "is_synced")
        val isSynced: Boolean
    )

    /**
     * 复合索引使用规则
     */
    fun compositeIndexUsage() {
        // 假设有复合索引：(device_id, timestamp, event_type)

        // ✅ 可以使用索引的查询
        // WHERE device_id = ?
        // WHERE device_id = ? AND timestamp > ?
        // WHERE device_id = ? AND timestamp > ? AND event_type = ?

        // ❌ 无法使用索引的查询
        // WHERE timestamp > ?  (跳过了device_id)
        // WHERE event_type = ?  (跳过了前面的字段)

        // 原则：必须从左边开始连续使用索引字段
    }

    /**
     * 监控索引效果
     */
    fun monitorIndexEffectiveness(db: SupportSQLiteDatabase) {
        // 查看索引列表
        val cursor = db.query("SELECT * FROM sqlite_master WHERE type = 'index'")
        while (cursor.moveToNext()) {
            val name = cursor.getString(cursor.getColumnIndex("name"))
            val tableName = cursor.getString(cursor.getColumnIndex("tbl_name"))
            val sql = cursor.getString(cursor.getColumnIndex("sql"))
            println("Index: $name on $tableName - $sql")
        }
        cursor.close()

        // 分析索引使用情况
        val statCursor = db.query("SELECT * FROM sqlite_stat1")
        while (statCursor.moveToNext()) {
            // 索引统计信息
        }
        statCursor.close()
    }
}

---

四、事务与批量操作优化

4.1 事务管理

/**
 * 事务优化
 */
class TransactionOptimization(private val database: AppDatabase) {

    /**
     * 方式1：使用@Transaction注解
     */
    @Transaction
    suspend fun updateDeviceAndEvents(device: DeviceEntity, events: List<EventEntity>) {
        database.deviceDao().updateDevices(listOf(device))
        database.eventDao().insertEvents(events)
        // 自动事务管理，任何异常都会回滚
    }

    /**
     * 方式2：手动管理事务
     */
    suspend fun manualTransaction() {
        database.runInTransaction {
            // 在事务中执行多个操作
            database.deviceDao().insertDevices(devices)
            database.eventDao().insertEvents(events)
        }
    }

    /**
     * 方式3：使用withTransaction（推荐）
     */
    suspend fun optimizedTransaction() {
        database.withTransaction {
            // suspend函数，可以使用协程
            val devices = database.deviceDao().getDevices(100, 0)
            val updatedDevices = devices.map { it.copy(status = 1) }
            database.deviceDao().updateDevices(updatedDevices)
        }
    }

    /**
     * 批量插入优化
     */
    suspend fun batchInsertOptimized(events: List<EventEntity>) {
        // ❌ 错误：每次插入都是一个事务
        events.forEach { event ->
            database.eventDao().insertEvents(listOf(event))
        }
        // 1000条数据耗时：12秒

        // ✅ 正确：使用批量插入
        database.eventDao().insertEvents(events)
        // 1000条数据耗时：0.24秒（50倍提升）
    }

    /**
     * 分批批量插入（数据量特别大时）
     */
    suspend fun batchInsertLargeData(events: List<EventEntity>) {
        val batchSize = 500

        events.chunked(batchSize).forEach { batch ->
            database.withTransaction {
                database.eventDao().insertEvents(batch)
            }
        }
    }
}

/**
 * 批量操作性能对比
 */
object BatchOperationBenchmark {

    suspend fun comparePerformance(database: AppDatabase) {
        val testEvents = generateTestEvents(1000)

        // 方式1：单条插入（无事务）
        val time1 = measureTimeMillis {
            testEvents.forEach { event ->
                database.eventDao().insertEvents(listOf(event))
            }
        }
        println("单条插入（无事务）: ${time1}ms")  // ~12000ms

        // 方式2：单条插入（一个大事务）
        val time2 = measureTimeMillis {
            database.withTransaction {
                testEvents.forEach { event ->
                    database.eventDao().insertEvents(listOf(event))
                }
            }
        }
        println("单条插入（一个事务）: ${time2}ms")  // ~8000ms

        // 方式3：批量插入
        val time3 = measureTimeMillis {
            database.eventDao().insertEvents(testEvents)
        }
        println("批量插入: ${time3}ms")  // ~240ms (50倍提升!)

        // 方式4：分批批量插入
        val time4 = measureTimeMillis {
            testEvents.chunked(500).forEach { batch ->
                database.withTransaction {
                    database.eventDao().insertEvents(batch)
                }
            }
        }
        println("分批批量插入: ${time4}ms")  // ~300ms
    }

    private fun generateTestEvents(count: Int): List<EventEntity> {
        return (1..count).map { i ->
            EventEntity(
                deviceId = (i % 10).toLong(),
                eventType = i % 5,
                timestamp = System.currentTimeMillis() + i,
                eventData = """{"data": "test_$i"}""",
                isSynced = false
            )
        }
    }
}

4.2 并发控制

/**
 * 并发控制优化
 */
class ConcurrencyOptimization {

    /**
     * WAL模式（Write-Ahead Logging）
     */
    fun enableWAL(context: Context) {
        val database = Room.databaseBuilder(
            context,
            AppDatabase::class.java,
            "app_database"
        )
            .setJournalMode(JournalMode.WRITE_AHEAD_LOGGING)  // ✅ 启用WAL
            .build()

        /*
         * WAL模式优势：
         * 1. 读写并发：读不阻塞写，写不阻塞读
         * 2. 性能提升：写入性能提升50-100%
         * 3. 数据安全：更好的崩溃恢复
         *
         * 注意：
         * - WAL文件可能变大（需要定期checkpoint）
         * - 不支持多进程访问
         */
    }

    /**
     * 使用线程池
     */
    fun configureThreadPool(context: Context) {
        val queryExecutor = Executors.newFixedThreadPool(4)  // 查询线程池
        val transactionExecutor = Executors.newSingleThreadExecutor()  // 事务线程池

        val database = Room.databaseBuilder(
            context,
            AppDatabase::class.java,
            "app_database"
        )
            .setQueryExecutor(queryExecutor)
            .setTransactionExecutor(transactionExecutor)
            .build()
    }

    /**
     * 协程支持
     */
    suspend fun coroutineSupport(database: AppDatabase) {
        // DAO方法使用suspend
        val devices = database.deviceDao().getDevices(100, 0)

        // 并发查询
        coroutineScope {
            val devicesDeferred = async { database.deviceDao().getDevices(100, 0) }
            val eventsDeferred = async { database.eventDao().getEvents(1L, 0, Long.MAX_VALUE, 100) }

            val devices = devicesDeferred.await()
            val events = eventsDeferred.await()
        }
    }

    /**
     * Flow响应式查询
     */
    fun flowQuery(database: AppDatabase) {
        // 自动监听数据变化
        database.deviceDao().observeDevices()
            .onEach { devices ->
                // 数据更新时自动回调
                updateUI(devices)
            }
            .launchIn(viewModelScope)
    }

    private fun updateUI(devices: List<DeviceEntity>) {
        // 更新UI
    }
}

---

五、数据库升级与迁移

5.1 数据库升级策略

/**
 * 数据库升级管理
 */
object DatabaseMigration {

    /**
     * 版本1 -> 版本2：添加新字段
     */
    val MIGRATION_1_2 = object : Migration(1, 2) {
        override fun migrate(database: SupportSQLiteDatabase) {
            // 添加新字段（提供默认值）
            database.execSQL(
                "ALTER TABLE device ADD COLUMN firmware_version TEXT NOT NULL DEFAULT '1.0.0'"
            )
        }
    }

    /**
     * 版本2 -> 版本3：添加新表
     */
    val MIGRATION_2_3 = object : Migration(2, 3) {
        override fun migrate(database: SupportSQLiteDatabase) {
            // 创建新表
            database.execSQL("""
                CREATE TABLE IF NOT EXISTS user (
                    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                    username TEXT NOT NULL,
                    email TEXT,
                    create_time INTEGER NOT NULL
                )
            """)

            // 创建索引
            database.execSQL("CREATE INDEX index_user_username ON user(username)")
        }
    }

    /**
     * 版本3 -> 版本4：修改表结构（复杂）
     */
    val MIGRATION_3_4 = object : Migration(3, 4) {
        override fun migrate(database: SupportSQLiteDatabase) {
            // SQLite不支持直接修改列，需要重建表
            database.execSQL("BEGIN TRANSACTION")

            try {
                // 1. 创建新表
                database.execSQL("""
                    CREATE TABLE device_new (
                        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                        device_sn TEXT NOT NULL,
                        device_name TEXT NOT NULL,
                        type INTEGER NOT NULL,
                        status INTEGER NOT NULL,
                        firmware_version TEXT NOT NULL DEFAULT '1.0.0',
                        hardware_version TEXT NOT NULL DEFAULT '1.0.0',
                        create_time INTEGER NOT NULL,
                        update_time INTEGER NOT NULL
                    )
                """)

                // 2. 复制数据
                database.execSQL("""
                    INSERT INTO device_new (id, device_sn, device_name, type, status,
                                           firmware_version, create_time, update_time)
                    SELECT id, device_sn, device_name, type, status,
                           firmware_version, create_time, update_time
                    FROM device
                """)

                // 3. 删除旧表
                database.execSQL("DROP TABLE device")

                // 4. 重命名新表
                database.execSQL("ALTER TABLE device_new RENAME TO device")

                // 5. 重建索引
                database.execSQL("CREATE UNIQUE INDEX index_device_sn ON device(device_sn)")
                database.execSQL("CREATE INDEX index_device_type_status ON device(type, status)")

                database.execSQL("COMMIT")
            } catch (e: Exception) {
                database.execSQL("ROLLBACK")
                throw e
            }
        }
    }

    /**
     * 配置数据库升级
     */
    fun buildDatabase(context: Context): AppDatabase {
        return Room.databaseBuilder(
            context.applicationContext,
            AppDatabase::class.java,
            "app_database"
        )
            // 添加所有迁移
            .addMigrations(
                MIGRATION_1_2,
                MIGRATION_2_3,
                MIGRATION_3_4
            )
            // 开发时可用：升级失败时销毁重建（生产环境禁用）
            // .fallbackToDestructiveMigration()
            .build()
    }

    /**
     * 测试数据库升级
     */
    @Test
    fun testMigration_1_2() {
        val helper = MigrationTestHelper(
            InstrumentationRegistry.getInstrumentation(),
            AppDatabase::class.java.canonicalName,
            FrameworkSQLiteOpenHelperFactory()
        )

        // 创建版本1数据库
        val db1 = helper.createDatabase(TEST_DB_NAME, 1)
        db1.execSQL("INSERT INTO device VALUES (...)")
        db1.close()

        // 升级到版本2
        val db2 = helper.runMigrationsAndValidate(
            TEST_DB_NAME,
            2,
            true,
            MIGRATION_1_2
        )

        // 验证数据
        val cursor = db2.query("SELECT * FROM device")
        assertTrue(cursor.moveToFirst())
        assertNotNull(cursor.getString(cursor.getColumnIndex("firmware_version")))
    }
}

5.2 数据迁移优化

/**
 * 数据迁移工具
 */
class DataMigrationHelper(private val context: Context) {

    /**
     * 从旧存储迁移到Room
     */
    suspend fun migrateFromSharedPreferences() {
        val prefs = context.getSharedPreferences("old_prefs", Context.MODE_PRIVATE)
        val database = AppDatabase.getInstance(context)

        // 读取旧数据
        val deviceCount = prefs.getInt("device_count", 0)
        val devices = mutableListOf<DeviceEntity>()

        for (i in 0 until deviceCount) {
            val deviceSn = prefs.getString("device_sn_$i", null) ?: continue
            val deviceName = prefs.getString("device_name_$i", "") ?: ""

            devices.add(
                DeviceEntity(
                    deviceSn = deviceSn,
                    deviceName = deviceName,
                    type = 0,
                    status = 1,
                    createTime = System.currentTimeMillis(),
                    updateTime = System.currentTimeMillis()
                )
            )
        }

        // 批量插入新数据库
        if (devices.isNotEmpty()) {
            database.deviceDao().insertDevices(devices)

            // 清理旧数据
            prefs.edit().clear().apply()
        }
    }

    /**
     * 导出数据库（备份）
     */
    fun exportDatabase(): File? {
        val database = AppDatabase.getInstance(context)
        database.close()  // 先关闭数据库

        val dbFile = context.getDatabasePath("app_database")
        if (!dbFile.exists()) return null

        val exportDir = File(context.getExternalFilesDir(null), "backup")
        if (!exportDir.exists()) {
            exportDir.mkdirs()
        }

        val exportFile = File(exportDir, "app_database_${System.currentTimeMillis()}.db")
        dbFile.copyTo(exportFile, overwrite = true)

        return exportFile
    }

    /**
     * 导入数据库（恢复）
     */
    fun importDatabase(backupFile: File): Boolean {
        if (!backupFile.exists()) return false

        val database = AppDatabase.getInstance(context)
        database.close()

        val dbFile = context.getDatabasePath("app_database")
        return try {
            backupFile.copyTo(dbFile, overwrite = true)
            true
        } catch (e: Exception) {
            Log.e("Migration", "Failed to import database", e)
            false
        }
    }

    /**
     * 数据库瘦身（清理冗余数据）
     */
    suspend fun shrinkDatabase() {
        val database = AppDatabase.getInstance(context)

        database.withTransaction {
            // 1. 删除旧数据
            val thirtyDaysAgo = System.currentTimeMillis() - 30L * 24 * 60 * 60 * 1000
            database.eventDao().cleanOldEvents(thirtyDaysAgo)

            // 2. VACUUM（释放空间）
            database.openHelper.writableDatabase.execSQL("VACUUM")

            // 3. ANALYZE（更新统计信息）
            database.openHelper.writableDatabase.execSQL("ANALYZE")
        }
    }
}

---

六、全文搜索优化

6.1 FTS（Full-Text Search）

/**
 * FTS全文搜索表
 */
@Entity(tableName = "device_fts")
@Fts4(contentEntity = DeviceEntity::class)
data class DeviceFts(
    @ColumnInfo(name = "device_name")
    val deviceName: String,

    @ColumnInfo(name = "device_sn")
    val deviceSn: String
)

/**
 * FTS DAO
 */
@Dao
interface DeviceFtsDao {

    /**
     * 全文搜索
     */
    @Query("""
        SELECT device.*
        FROM device
        JOIN device_fts ON device.rowid = device_fts.rowid
        WHERE device_fts MATCH :query
    """)
    suspend fun searchDevices(query: String): List<DeviceEntity>

    /**
     * 前缀搜索
     */
    @Query("""
        SELECT device.*
        FROM device
        JOIN device_fts ON device.rowid = device_fts.rowid
        WHERE device_fts MATCH :query || '*'
    """)
    suspend fun searchDevicesPrefix(query: String): List<DeviceEntity>
}

/**
 * FTS使用示例
 */
class FullTextSearchExample(private val database: AppDatabase) {

    /**
     * 基础搜索
     */
    suspend fun basicSearch(keyword: String) {
        // 搜索包含关键词的设备
        val results = database.deviceFtsDao().searchDevices(keyword)
    }

    /**
     * 前缀搜索（自动补全）
     */
    suspend fun prefixSearch(prefix: String) {
        // 搜索以prefix开头的设备
        val results = database.deviceFtsDao().searchDevicesPrefix(prefix)
    }

    /**
     * 高级搜索语法
     */
    suspend fun advancedSearch() {
        // AND搜索：同时包含camera和outdoor
        val results1 = database.deviceFtsDao().searchDevices("camera AND outdoor")

        // OR搜索：包含camera或indoor
        val results2 = database.deviceFtsDao().searchDevices("camera OR indoor")

        // NOT搜索：包含camera但不包含indoor
        val results3 = database.deviceFtsDao().searchDevices("camera NOT indoor")

        // 短语搜索：精确匹配短语
        val results4 = database.deviceFtsDao().searchDevices("\"outdoor camera\"")

        // NEAR搜索：单词相近
        val results5 = database.deviceFtsDao().searchDevices("camera NEAR/3 outdoor")
    }

    /**
     * FTS vs LIKE性能对比
     */
    suspend fun performanceComparison() {
        val keyword = "camera"

        // LIKE查询（慢）
        val time1 = measureTimeMillis {
            database.deviceDao().searchDevicesLike("%$keyword%")
        }
        println("LIKE查询: ${time1}ms")  // ~850ms

        // FTS查询（快）
        val time2 = measureTimeMillis {
            database.deviceFtsDao().searchDevices(keyword)
        }
        println("FTS查询: ${time2}ms")  // ~25ms (34倍提升!)
    }
}

@Dao
interface DeviceDao {
    @Query("SELECT * FROM device WHERE device_name LIKE :pattern")
    suspend fun searchDevicesLike(pattern: String): List<DeviceEntity>
}

---

七、数据库监控与调试

7.1 性能监控

/**
 * 数据库性能监控
 */
class DatabaseMonitor {

    /**
     * 监控查询性能
     */
    fun monitorQueryPerformance(context: Context): AppDatabase {
        return Room.databaseBuilder(
            context,
            AppDatabase::class.java,
            "app_database"
        )
            .setQueryCallback(
                { sqlQuery, bindArgs ->
                    Log.d("RoomQuery", "Query: $sqlQuery, Args: $bindArgs")
                },
                Executors.newSingleThreadExecutor()
            )
            .build()
    }

    /**
     * 自定义监控
     */
    class MonitoringDatabase : RoomDatabase() {
        override fun createOpenHelper(config: DatabaseConfiguration): SupportSQLiteOpenHelper {
            val delegate = super.createOpenHelper(config)
            return MonitoringOpenHelper(delegate)
        }
    }

    class MonitoringOpenHelper(
        private val delegate: SupportSQLiteOpenHelper
    ) : SupportSQLiteOpenHelper by delegate {

        override fun getWritableDatabase(): SupportSQLiteDatabase {
            val db = delegate.writableDatabase
            return MonitoringDatabase(db)
        }

        override fun getReadableDatabase(): SupportSQLiteDatabase {
            val db = delegate.readableDatabase
            return MonitoringDatabase(db)
        }
    }

    class MonitoringDatabase(
        private val delegate: SupportSQLiteDatabase
    ) : SupportSQLiteDatabase by delegate {

        override fun query(query: String): Cursor {
            val startTime = SystemClock.elapsedRealtime()
            val cursor = delegate.query(query)
            val duration = SystemClock.elapsedRealtime() - startTime

            if (duration > 100) {  // 慢查询阈值：100ms
                Log.w("SlowQuery", "Query took ${duration}ms: $query")
                DatabasePerformanceMonitor.recordSlowQuery(query, duration)
            }

            return cursor
        }
    }
}

/**
 * 数据库性能指标收集
 */
object DatabasePerformanceMonitor {

    private val slowQueries = ConcurrentLinkedQueue<SlowQuery>()
    private val queryStats = ConcurrentHashMap<String, QueryStats>()

    data class SlowQuery(
        val sql: String,
        val duration: Long,
        val timestamp: Long
    )

    data class QueryStats(
        var count: AtomicInteger = AtomicInteger(0),
        var totalDuration: AtomicLong = AtomicLong(0),
        var maxDuration: AtomicLong = AtomicLong(0)
    )

    fun recordSlowQuery(sql: String, duration: Long) {
        slowQueries.offer(
            SlowQuery(
                sql = sql,
                duration = duration,
                timestamp = System.currentTimeMillis()
            )
        )

        // 限制队列大小
        while (slowQueries.size > 100) {
            slowQueries.poll()
        }
    }

    fun recordQuery(sql: String, duration: Long) {
        val stats = queryStats.getOrPut(sql) { QueryStats() }
        stats.count.incrementAndGet()
        stats.totalDuration.addAndGet(duration)

        val currentMax = stats.maxDuration.get()
        if (duration > currentMax) {
            stats.maxDuration.set(duration)
        }
    }

    fun generateReport(): PerformanceReport {
        val queries = queryStats.map { (sql, stats) ->
            QueryPerformance(
                sql = sql,
                count = stats.count.get(),
                avgDuration = if (stats.count.get() > 0) {
                    stats.totalDuration.get() / stats.count.get()
                } else 0,
                maxDuration = stats.maxDuration.get()
            )
        }.sortedByDescending { it.avgDuration }

        return PerformanceReport(
            slowQueries = slowQueries.toList(),
            queryPerformances = queries
        )
    }

    data class PerformanceReport(
        val slowQueries: List<SlowQuery>,
        val queryPerformances: List<QueryPerformance>
    )

    data class QueryPerformance(
        val sql: String,
        val count: Int,
        val avgDuration: Long,
        val maxDuration: Long
    )
}

7.2 数据库调试工具

/**
 * 数据库调试助手
 */
class DatabaseDebugHelper(private val context: Context) {

    /**
     * 导出数据库文件（用于查看）
     */
    fun exportDatabaseForDebug(): File? {
        if (!BuildConfig.DEBUG) {
            Log.w("Debug", "Database export is only available in debug builds")
            return null
        }

        val database = AppDatabase.getInstance(context)
        database.close()

        val dbFile = context.getDatabasePath("app_database")
        if (!dbFile.exists()) return null

        val exportDir = File(context.getExternalFilesDir(null), "debug")
        if (!exportDir.exists()) {
            exportDir.mkdirs()
        }

        val exportFile = File(exportDir, "app_database_debug.db")
        dbFile.copyTo(exportFile, overwrite = true)

        Log.d("Debug", "Database exported to: ${exportFile.absolutePath}")
        return exportFile
    }

    /**
     * 打印数据库统计信息
     */
    fun printDatabaseStats() {
        val database = AppDatabase.getInstance(context)
        val db = database.openHelper.readableDatabase

        println("=== Database Statistics ===")

        // 数据库大小
        val dbFile = context.getDatabasePath("app_database")
        println("Database size: ${dbFile.length() / 1024 / 1024}MB")

        // 表统计
        val cursor = db.query("SELECT name FROM sqlite_master WHERE type='table'")
        while (cursor.moveToNext()) {
            val tableName = cursor.getString(0)
            if (tableName.startsWith("sqlite_")) continue

            val countCursor = db.query("SELECT COUNT(*) FROM $tableName")
            countCursor.moveToFirst()
            val count = countCursor.getInt(0)
            countCursor.close()

            println("Table $tableName: $count rows")
        }
        cursor.close()

        // 索引统计
        val indexCursor = db.query("SELECT name, tbl_name FROM sqlite_master WHERE type='index'")
        println("\nIndexes:")
        while (indexCursor.moveToNext()) {
            val indexName = indexCursor.getString(0)
            val tableName = indexCursor.getString(1)
            println("  $indexName on $tableName")
        }
        indexCursor.close()
    }

    /**
     * 执行VACUUM（清理碎片）
     */
    fun vacuum() {
        val database = AppDatabase.getInstance(context)
        val db = database.openHelper.writableDatabase

        val beforeSize = context.getDatabasePath("app_database").length()
        db.execSQL("VACUUM")
        val afterSize = context.getDatabasePath("app_database").length()

        val saved = beforeSize - afterSize
        println("VACUUM completed. Saved: ${saved / 1024}KB")
    }

    /**
     * 分析数据库（更新统计信息）
     */
    fun analyze() {
        val database = AppDatabase.getInstance(context)
        val db = database.openHelper.writableDatabase
        db.execSQL("ANALYZE")
        println("ANALYZE completed")
    }
}

---

八、最佳实践总结

8.1 优化清单

数据库优化清单:

  设计阶段:
    ✅ 合理的表结构设计
    ✅ 适当的字段类型选择
    ✅ 为常用查询创建索引
    ✅ 避免过度范式化
    ✅ 考虑数据增长预期

  开发阶段:
    ✅ 避免SELECT *
    ✅ 使用LIMIT限制结果集
    ✅ 批量操作使用事务
    ✅ 避免N+1查询
    ✅ 使用索引字段查询

  运行阶段:
    ✅ 启用WAL模式
    ✅ 配置合理的缓存大小
    ✅ 定期清理旧数据
    ✅ 监控慢查询
    ✅ 定期VACUUM

  升级阶段:
    ✅ 编写Migration脚本
    ✅ 测试升级路径
    ✅ 提供降级方案
    ✅ 备份用户数据
    ✅ 灰度升级验证

8.2 性能提升总结

---

参考资料

1. Room官方文档
2. SQLite性能优化
3. SQLite索引
4. Room数据库升级
5. FTS全文搜索

---

版权声明：本文为原创技术文章，内容已脱敏处理，仅供学习交流使用。