如何使用PRAGMA命令提升Kotlin Exposed库操作SQLite的性能

配置SQLite PRAGMA参数的两种实现方案 #

方案1：通过JDBC连接URL直接配置（推荐）

SQLite官方JDBC驱动支持直接将PRAGMA参数作为URL的查询参数传入，连接建立时会自动生效，无需额外执行语句。修改后的代码如下：

import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.transactions.TransactionManager
import org.jetbrains.exposed.sql.transactions.transaction
import java.io.File
import java.sql.Connection

private fun provideSqliteDatabase(databaseConfig: DatabaseConfig?): Database {
    val path = databaseConfig?.url ?: "./"
    File(path).mkdirs()

    logger.info("SQLite Database path: $path/database.db")
    // URL中直接追加PRAGMA参数，前缀加下划线，多个参数用&分隔
    return Database.connect("jdbc:sqlite:$path/database.db?_journal_mode=OFF&_synchronous=OFF", "org.sqlite.JDBC")
        .apply {
            TransactionManager.manager.defaultIsolationLevel =
                Connection.TRANSACTION_READ_UNCOMMITTED
            provideTables()
        }
}

所有PRAGMA参数在URL中配置时都需要加_前缀，例如PRAGMA cache_size=10000对应URL参数_cache_size=10000。

方案2：连接建立后手动执行PRAGMA语句

如果需要动态调整参数或者配置逻辑更复杂，可以在连接初始化完成后，通过事务执行PRAGMA命令：

return Database.connect("jdbc:sqlite:$path/database.db", "org.sqlite.JDBC")
    .apply {
        TransactionManager.manager.defaultIsolationLevel =
            Connection.TRANSACTION_READ_UNCOMMITTED
        // 执行PRAGMA配置
        transaction {
            exec("PRAGMA JOURNAL_MODE='OFF';")
            exec("PRAGMA SYNCHRONOUS='OFF';")
            exec("PRAGMA CACHE_SIZE=-20000;") // 缓存大小设置为20MB
        }
        provideTables()
    }

常用性能优化PRAGMA说明 #

• synchronous = OFF：关闭磁盘强制同步，大幅提升写入性能，仅适合存储可丢失、可重建的缓存类数据
• cache_size = -[数值]：设置页缓存大小，数值为负时单位为KB，例如-20000对应20MB缓存
• temp_store = MEMORY：临时表、临时索引全部存放在内存中，减少磁盘IO
• locking_mode = EXCLUSIVE：开启独占锁模式，减少多进程锁校验开销，仅适合单进程访问数据库的场景

注意事项 #

JOURNAL_MODE=OFF会完全关闭事务回滚日志，程序崩溃、系统掉电时会直接导致数据库文件损坏，仅可用于非核心、可重建的数据存储场景。Ubuntu 20.04系统默认携带的SQLite版本对上述所有参数均兼容，无需额外升级依赖。

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