使用FMDB向SQLite插入大数据集时应用卡顿崩溃求助

问题描述 #

我在用FMDB向SQLite数据库上传大型数据集，频繁在前后台切换时应用卡顿甚至被系统终止，用Instruments检测发现主线程卡顿。以下是当前使用的代码及崩溃日志，求多线程使用的解决方案。

当前代码 #

sharedQueue.inDeferredTransaction { (db, rollback) in
    do {
        if !sharedDatabase.isOpen {
            sharedDatabase.open()
        }
        try sharedDatabase.executeUpdate(sqlStatement, values: values)
        completion(true, nil)
        sharedDatabase.close()
        print("FMDB INSERT Success")
    } catch {
        print("FMDB INSERT failed: \(error.localizedDescription)")
        completion(false, error)
        sharedDatabase.close()
    }
}

崩溃日志 #

CrashReporter Key:  113c7cbb69fb95c4d22805670b7048e73e4e72d7

Hardware Model:     iPhone11,8

Process:            PurplePlayer

Identifier:...

Version:            5.0

Role:               Foreground

OS Version:         iOS 16.5.1

App Hang: The app was terminated while unresponsive

0  libsystem_kernel.dylib +0x295c _guarded_pwrite_np

1  libsqlite3.dylib +0x8e50c      0x1abfc250c (0x1abfc2348 + 452)

2  libsqlite3.dylib +0x4bb68      0x1abf7fb68 (0x1abf7fab8 + 176)

3  libsqlite3.dylib +0x53acc      0x1abf87acc (0x1abf87980 + 332)

4  libsqlite3.dylib +0x47a8c      0x1abf7ba8c (0x1abf7b7d4 + 696)

5  libsqlite3.dylib +0x4b4d0      0x1abf7f4d0 (0x1abf7f3ac + 292)

6  libsqlite3.dylib +0x4ad74      0x1abf7ed74 (0x1abf7e520 + 2132)

7  libsqlite3.dylib +0x32984      0x1abf66984 (0x1abf6002c + 26968)

8  libsqlite3.dylib +0x2b5b0      _sqlite3_step

9  FMDB +0x9994                   -[FMDatabase executeUpdate:error:withArgumentsInArray:orDictionary:orVAList:]

10 FMDB +0xa150                   -[FMDatabase executeUpdate:values:error:]

11 PurplePlayer +0x18f1e8         closure #1 in static FMDBDatabase.update(sqlStatement:values:completion:)

12 PurplePlayer +0x29678          thunk for @callee_guaranteed (@guaranteed FMDatabase, @unowned UnsafeMutablePointer<ObjCBool>) -> ()

13 PurplePlayer +0x296f8          thunk for @escaping @callee_guaranteed (@guaranteed FMDatabase, @unowned UnsafeMutablePointer<ObjCBool>) -> ()

14 FMDB +0x10370                  __46-[FMDatabaseQueue beginTransaction:withBlock:]_block_invoke

15 libdispatch.dylib +0x6034      _dispatch_client_callout

16 libdispatch.dylib +0x17810     _dispatch_lane_barrier_sync_invoke_and_complete

17 FMDB +0x10214                  -[FMDatabaseQueue beginTransaction:withBlock:]

18 FMDB +0x10498                  -[FMDatabaseQueue inDeferredTransaction:]

19 PurplePlayer +0x18ef78         static FMDBDatabase.update(sqlStatement:values:completion:)

20 PurplePlayer +0x18f7b0         static FMDBDatabase.insert(values:sqlStatement:completion:)

21 PurplePlayer +0x1837c0         LiveTvFMDBTable.insertLiveTVData(tbl:liveTvModel:categoryName:playlistId:)

22 PurplePlayer +0x61f04          closure #1 in FetchingDataViewModel.StoreRetriveAndReloadTable(livetvData:tbl:playlistId:)

23 PurplePlayer +0x61f70          partial apply for closure #1 in FetchingDataViewModel.StoreRetriveAndReloadTable(livetvData:tbl:playlistId:)

24 libswiftCore.dylib +0x16604    Collection.map<A>(_)

25 PurplePlayer +0x61afc          FetchingDataViewModel.StoreRetriveAndReloadTable(livetvData:tbl:playlistId:)

26 PurplePlayer +0x61770          closure #1 in FetchingDataViewModel.insertDataintoDatabase(livetvData:playlistId:tbl:completion:)

27 PurplePlayer +0x617a4          partial apply for closure #1 in FetchingDataViewModel.insertDataintoDatabase(livetvData:playlistId:tbl:completion:)

28 libswiftCore.dylib +0x16604    Collection.map<A>(_)

29 PurplePlayer +0x616b4          FetchingDataViewModel.insertDataintoDatabase(livetvData:playlistId:tbl:completion:)

30 PurplePlayer +0x5e150          closure #1 in closure #1 in closure #1 in FetchingDataViewModel.callLiveTVApis(_:)

31 PurplePlayer +0x612c4          specialized closure #1 in FetchingDataViewModel.callLiveTVAPi(action:_:)

32 PurplePlayer +0x883d4          closure #1 in APIManager.request<A>(urlString:method:parameters:isShowLoader:completion:)

33 Alamofire +0xddf94             closure #1 in closure #1 in closure #3 in closure #1 in DataRequest._response<A>(queue:responseSerializer:completionHandler:)

34 Alamofire +0x208ec             thunk for @escaping @callee_guaranteed () -> ()

35 libdispatch.dylib +0x451c      _dispatch_call_block_and_release

36 libdispatch.dylib +0x6034      _dispatch_client_callout

37 libdispatch.dylib +0x16794     _dispatch_main_queue_drain

38 libdispatch.dylib +0x162d8     _dispatch_main_queue_callback_4CF

39 CoreFoundation +0x98c24        ___CFRUNLOOP_IS_SERVICING_THE_MAIN_DISPATCH_QUEUE__

40 CoreFoundation +0x7a55c        ___CFRunLoopRun

41 CoreFoundation +0x7f3e8        _CFRunLoopRunSpecific

42 GraphicsServices +0x1358       _GSEventRunModal

43 UIKitCore +0x39d6e4            -[UIApplication _run]

44 UIKitCore +0x39d348            _UIApplicationMain

45 libswiftUIKit.dylib +0x350ac   UIApplicationMain(_:_:_:_:)

46 PurplePlayer +0x3f4ca8         static UIApplicationDelegate.main()

47 PurplePlayer +0x3f4c20         static AppDelegate.$main()

48 PurplePlayer +0x3f502c         main

49 dyld +0x15de8                  start

问题分析 #

从崩溃日志和代码能看出核心问题：

1. 错误使用全局sharedDatabase而非FMDatabaseQueue传入的线程安全实例，违背FMDB的线程安全设计，导致线程冲突
2. 每次插入手动打开/关闭数据库，频繁IO操作拖慢性能
3. 单条循环插入未利用事务批量提交优势，IO次数过多
4. 事务闭包内直接调用completion，若涉及UI操作会间接阻塞主线程

解决方案 #

1. 正确使用FMDatabaseQueue提供的数据库实例 #

FMDatabaseQueue的事务闭包会传入线程安全的db实例，必须用该实例执行操作，无需手动管理数据库的打开/关闭：

sharedQueue.inDeferredTransaction { (db, rollback) in
    do {
        try db.executeUpdate(sqlStatement, values: values)
        // 确保UI相关操作在主线程执行
        DispatchQueue.main.async {
            completion(true, nil)
        }
        print("FMDB INSERT Success")
    } catch {
        print("FMDB INSERT failed: \(error.localizedDescription)")
        rollback.pointee = true // 事务失败必须标记回滚
        DispatchQueue.main.async {
            completion(false, error)
        }
    }
}

2. 批量插入优化 #

对于大型数据集，将多条插入合并到一个事务中，减少SQLite IO次数：

// valuesArray为包含所有插入数据的数组
sharedQueue.inDeferredTransaction { (db, rollback) in
    do {
        let sql = "INSERT INTO your_table (col1, col2) VALUES (?, ?)"
        for values in valuesArray {
            try db.executeUpdate(sql, values: values)
        }
        
        DispatchQueue.main.async {
            completion(true, nil)
        }
    } catch {
        print("BATCH INSERT failed: \(error.localizedDescription)")
        rollback.pointee = true
        DispatchQueue.main.async {
            completion(false, error)
        }
    }
}

也可使用executeStatements方法执行批量SQL，进一步提升效率。

3. 隔离主线程与数据库操作 #

确保所有数据库操作都在FMDatabaseQueue的后台线程执行，网络请求回调若默认在主线程，需将数据库操作切到后台：

APIManager.request(...) { result in
    switch result {
    case .success(let data):
        // 切换到FMDatabaseQueue执行插入
        sharedQueue.async {
            self.insertDataintoDatabase(livetvData: data, ...) { success, error in
                DispatchQueue.main.async {
                    // 仅在主线程处理UI更新
                }
            }
        }
    case .failure(let error):
        DispatchQueue.main.async {
            // 处理错误
        }
    }
}

4. 分批次插入控制内存 #

若数据集过大，分批次插入避免内存暴涨：

• 每次插入100-500条数据（根据单条数据大小调整）
• 完成一批后再处理下一批，避免一次性加载所有数据到内存

5. 数据库配置优化 #

调整SQLite参数提升写入性能：

sharedQueue.inDatabase { db in
    // 开启WAL模式提升并发性能
    try? db.executeUpdate("PRAGMA journal_mode = WAL", values: nil)
    // 设置缓存大小（示例为20MB）
    try? db.executeUpdate("PRAGMA cache_size = -20000", values: nil)
    // 按需禁用同步日志（注意：极端情况可能丢失数据，需根据业务场景选择）
    // try? db.executeUpdate("PRAGMA synchronous = OFF", values: nil)
}

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