iOS-Objective C中SQLite批量读写的性能优化方案咨询

针对你遇到的7136条Rooms数据处理耗时18秒的问题，除了给QMSRoomId添加索引外，我整理了几个能有效提升SQL操作效率的方案，结合你的代码逐一说明：

当前代码与问题分析 #

你的核心逻辑是循环遍历每条数据，先执行SELECT判断记录是否存在，再执行UPDATE或INSERT。这种方式每条数据要和数据库交互两次，加上循环内重复编译SQL、ResultSet资源未及时释放等问题，是导致耗时较长的主要原因。

先贴出补全后的你的代码（修复了databaseName字符串拼接问题，添加了遗漏的ResultSet关闭操作）：

- (void)saveRoomsToDB:(NSArray *)room{ 
    NSString *dbpath = [[self documentDirectoryPath] stringByAppendingFormat:@"/%@", databaseName]; 
    database = [FMDatabase databaseWithPath:dbpath]; 
    if([database open]){ 
        [database beginTransaction]; 
        for (Room *roomData in room) { 
            FMResultSet *result = [database executeQuery:@"SELECT RoomDesc FROM Room WHERE QMSRoomId = ?" withArgumentsInArray:@[@(roomData.RoomId)]]; 
            if ([result next]){
                [database executeUpdate:@"UPDATE Room SET QMSSubSectionId = ?,RoomDesc = ?,LastEditedDate = ?,RoomType = ?, isTrue = ?, CyclePerformed = ? WHERE QMSRoomId = ?" withArgumentsInArray:@[@(roomData.QMSSubSectionId), roomData.RoomDesc, roomData.LastEditDate, roomData.rt_description, @(roomData.isTrue), @(roomData.CyclePerformed), @(roomData.RoomId)]]; 
            } else{
                [database executeUpdate:@"INSERT INTO Room (QMSSubSectionId, QMSRoomId,RoomDesc, LastEditedDate, RoomNumber,RoomType, CyclePerformed,isTrue) VALUES (?, ?, ?, ?, ?, ?, ?, ?);" withArgumentsInArray:@[@(roomData.QMSSubSectionId), @(roomData.RoomId), roomData.RoomDesc, roomData.LastEditDate, roomData.RoomNumber, roomData.rt_description, @(roomData.CyclePerformed), @(roomData.isTrue)]]; 
            }
            [result close]; // 原代码遗漏，必须关闭ResultSet避免资源泄漏
        } 
        [database commit]; 
        [database close]; 
    } 
}

日志信息：

2018-05-23 17:47:49.702 SterileTrakks[656:74128] no of records for Rooms : 7136
2018-05-23 17:48:07.153 SterileTrakks[656:74128] Insertion for Rooms finished

具体优化方案 #

1. 用INSERT OR REPLACE合并"查询+更新/插入"逻辑

SQLite的INSERT OR REPLACE语句可以在一条SQL中完成"存在则替换（等价于更新），不存在则插入"的操作，直接把每条数据的两次数据库交互减少到一次，这是最有效的优化手段之一。

前提：你的Room表必须给QMSRoomId设置唯一约束（比如设为主键，或者添加UNIQUE约束），这样SQLite才能通过QMSRoomId判断记录是否存在。

修改后的代码逻辑：

- (void)saveRoomsToDB:(NSArray *)room{ 
    NSString *dbpath = [[self documentDirectoryPath] stringByAppendingFormat:@"/%@", databaseName]; 
    FMDatabase *db = [FMDatabase databaseWithPath:dbpath]; 
    if([db open]){ 
        [db beginTransaction]; 
        // 定义一次SQL语句，避免循环内重复创建字符串
        NSString *replaceSql = @"INSERT OR REPLACE INTO Room (QMSSubSectionId, QMSRoomId, RoomDesc, LastEditedDate, RoomNumber, RoomType, CyclePerformed, isTrue) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        
        for (Room *roomData in room) { 
            NSArray *args = @[@(roomData.QMSSubSectionId), @(roomData.RoomId), 
                             roomData.RoomDesc ?: @"", // 处理空值避免SQL报错
                             roomData.LastEditDate ?: @"", 
                             roomData.RoomNumber ?: @"", 
                             roomData.rt_description ?: @"", 
                             @(roomData.CyclePerformed), 
                             @(roomData.isTrue)];
            if (![db executeUpdate:replaceSql withArgumentsInArray:args]) {
                NSLog(@"执行失败: %@", [db lastError]);
                [db rollback];
                [db close];
                return;
            }
        } 
        
        BOOL commitSuccess = [db commit];
        if (!commitSuccess) {
            NSLog(@"事务提交失败: %@", [db lastError]);
            [db rollback];
        }
        [db close]; 
    } 
}

2. 使用预处理语句（Prepared Statement）减少SQL编译开销

循环中重复执行相同的SQL时，每次都会重新编译SQL语句，这会带来额外开销。使用FMDB的FMStatement可以预先编译SQL，循环中只需要绑定参数并执行，能显著提升效率。

示例代码：

- (void)saveRoomsToDB:(NSArray *)room{ 
    NSString *dbpath = [[self documentDirectoryPath] stringByAppendingFormat:@"/%@", databaseName]; 
    FMDatabase *db = [FMDatabase databaseWithPath:dbpath]; 
    if([db open]){ 
        [db beginTransaction]; 
        // 预处理SQL语句
        FMStatement *statement = [db prepareStatement:@"INSERT OR REPLACE INTO Room (QMSSubSectionId, QMSRoomId, RoomDesc, LastEditedDate, RoomNumber, RoomType, CyclePerformed, isTrue) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"];
        
        if (!statement) {
            NSLog(@"预处理语句创建失败: %@", [db lastError]);
            [db rollback];
            [db close];
            return;
        }
        
        for (Room *roomData in room) { 
            // 绑定参数（注意列索引从0开始）
            [statement bindInt:(int)roomData.QMSSubSectionId forColumnIndex:0];
            [statement bindInt:(int)roomData.RoomId forColumnIndex:1];
            [statement bindString:roomData.RoomDesc ?: @"" forColumnIndex:2];
            [statement bindString:roomData.LastEditDate ?: @"" forColumnIndex:3];
            [statement bindString:roomData.RoomNumber ?: @"" forColumnIndex:4];
            [statement bindString:roomData.rt_description ?: @"" forColumnIndex:5];
            [statement bindInt:(int)roomData.CyclePerformed forColumnIndex:6];
            [statement bindInt:(int)roomData.isTrue forColumnIndex:7];
            
            // 执行语句
            if (![statement step]) {
                NSLog(@"执行语句失败: %@", [db lastError]);
                [db rollback];
                [statement close];
                [db close];
                return;
            }
            
            // 重置语句，准备下一次绑定参数
            [statement reset];
        }
        
        [statement close]; // 释放预处理语句资源
        BOOL commitSuccess = [db commit];
        if (!commitSuccess) {
            NSLog(@"事务提交失败: %@", [db lastError]);
            [db rollback];
        }
        [db close]; 
    } 
}

3. 关闭FMDB调试日志

FMDB默认会输出调试日志，当处理大量数据时，日志输出会占用不少CPU资源。可以在初始化数据库前关闭日志：

// 在App启动时或数据库初始化前调用
[FMDatabase setLoggingLevel:0];

4. 提前预处理数据，减少循环内的计算

把循环中需要重复转换的属性（比如将基本数据类型转成NSNumber）提前处理好，避免在循环中重复创建对象：

// 提前转换所有数据，减少循环内的操作
NSMutableArray *processedRooms = [NSMutableArray arrayWithCapacity:room.count];
for (Room *roomData in room) {
    NSDictionary *roomDict = @{
        @"QMSSubSectionId": @(roomData.QMSSubSectionId),
        @"QMSRoomId": @(roomData.RoomId),
        @"RoomDesc": roomData.RoomDesc ?: @"",
        @"LastEditedDate": roomData.LastEditDate ?: @"",
        @"RoomNumber": roomData.RoomNumber ?: @"",
        @"RoomType": roomData.rt_description ?: @"",
        @"CyclePerformed": @(roomData.CyclePerformed),
        @"isTrue": @(roomData.isTrue)
    };
    [processedRooms addObject:roomDict];
}

// 后续循环直接使用预处理好的字典
for (NSDictionary *roomDict in processedRooms) {
    [statement bindInt:[roomDict[@"QMSSubSectionId"] intValue] forColumnIndex:0];
    [statement bindInt:[roomDict[@"QMSRoomId"] intValue] forColumnIndex:1];
    // ... 其他参数绑定
}

5. 使用FMDatabaseQueue保证线程安全并优化性能

如果这个数据库操作可能在多线程环境下执行，FMDatabaseQueue不仅能保证线程安全，其内部的连接池优化也能提升一定的处理效率：

- (void)saveRoomsToDB:(NSArray *)room{ 
    NSString *dbpath = [[self documentDirectoryPath] stringByAppendingFormat:@"/%@", databaseName]; 
    FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:dbpath];
    
    [queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMStatement *statement = [db prepareStatement:@"INSERT OR REPLACE INTO Room (QMSSubSectionId, QMSRoomId, RoomDesc, LastEditedDate, RoomNumber, RoomType, CyclePerformed, isTrue) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"];
        if (!statement) {
            NSLog(@"预处理语句创建失败: %@", [db lastError]);
            *rollback = YES;
            return;
        }
        
        for (Room *roomData in room) { 
            [statement bindInt:(int)roomData.QMSSubSectionId forColumnIndex:0];
            [statement bindInt:(int)roomData.RoomId forColumnIndex:1];
            [statement bindString:roomData.RoomDesc ?: @"" forColumnIndex:2];
            [statement bindString:roomData.LastEditDate ?: @"" forColumnIndex:3];
            [statement bindString:roomData.RoomNumber ?: @"" forColumnIndex:4];
            [statement bindString:roomData.rt_description ?: @"" forColumnIndex:5];
            [statement bindInt:(int)roomData.CyclePerformed forColumnIndex:6];
            [statement bindInt:(int)roomData.isTrue forColumnIndex:7];
            
            if (![statement step]) {
                NSLog(@"执行失败: %@", [db lastError]);
                *rollback = YES;
                [statement close];
                return;
            }
            [statement reset];
        }
        
        [statement close];
    }];
}

额外提醒 #

• 原代码中遗漏了FMResultSet的关闭操作，这会导致数据库游标资源泄漏，即使优化后不再使用SELECT，也要养成及时关闭ResultSet的习惯。
• 事务处理一定要包含rollback逻辑，避免在执行失败时数据库处于不一致状态。

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