数据库操作优化

数据库耗时操作

主要是往数据库执行写操作

// 插入数据
[db executeUpdate:@"INSERT INTO t_PetInfo(petNo, age, name, weight, hoster) VALUES(?, ?, ?, ?, ?);", @(i), @(2), @"fancy", @(15.0), @"derain"];

// 更新数据
[db executeUpdate:@"UPDATE t_PetInfo SET name = ?", @"fffffancy"];

// 删除数据
[db executeUpdate:@"DELETE FROM t_PetInfo WHERE petNo > ?", @(20)];

耗时操作优化
- 利用事务提交可以有效的减少耗时
- 在子线程处理耗时操作

耗时操作实验

插入数据

开启事务耗时: 0.097206
不开启事务耗时:38.051225
插入10000条数据, 不开启事务耗时是开启事务的391倍


// 1.0 开启事务
CGFloat beginTime = [NSDate timeIntervalSinceReferenceDate];

[db beginTransaction];
for (NSInteger i = 0; i < 10000; i++) {
    
    NSString *name = (i % 2) ? @"fancy" : nil;
     [db executeUpdate:@"INSERT INTO t_PetInfo(petNo, age, name, weight, hoster) VALUES(?, ?, ?, ?, ?);", @(i), @(2), name, @(15.0), @"derain"];
}
[db commit];
 CGFloat endTime = [NSDate timeIntervalSinceReferenceDate];

NSLog(@"%f", endTime - beginTime); // 耗时: 0.097206

// 2.0 不开启事务
CGFloat beginTime = [NSDate timeIntervalSinceReferenceDate];

for (NSInteger i = 0; i < 10000; i++) {
    
    NSString *name = (i % 2) ? @"fancy" : nil;
     [db executeUpdate:@"INSERT INTO t_PetInfo(petNo, age, name, weight, hoster) VALUES(?, ?, ?, ?, ?);", @(i), @(2), name, @(15.0), @"derain"];
}
CGFloat endTime = [NSDate timeIntervalSinceReferenceDate];

NSLog(@"%f", endTime - beginTime); // 耗时: 38.051225

查询耗时实验
- 开启事务耗时: 0.037018
- 不开启事务耗时:0.037175
- 两者基本一样, 因为读操作只需数据库一次, 频繁的开启事务, 提交事务
- 当然, 当有大量读操作时, 依旧推荐使用事务

    
    // 开启事务
    CGFloat beginTime2 = [NSDate timeIntervalSinceReferenceDate];
    
    [db beginTransaction];
    
    for (NSInteger i = 0; i < 1000; i++) {
        
       FMResultSet *result = [db executeQuery:@"SELECT * FROM t_PetInfo;"];
    }

    [db commit];
    CGFloat endTime2 = [NSDate timeIntervalSinceReferenceDate];
    
    NSLog(@"time2 - %f", endTime2 - beginTime2);
    
    [db close];


// 不开启事务
    CGFloat beginTime2 = [NSDate timeIntervalSinceReferenceDate];
      for (NSInteger i = 0; i < 1000; i++) {
        
       FMResultSet *result = [db executeQuery:@"SELECT * FROM t_PetInfo;"];
    }

   CGFloat endTime2 = [NSDate timeIntervalSinceReferenceDate];
    
    NSLog(@"time2 - %f", endTime2 - beginTime2);
    
    [db close];

更新操作耗时实验
- 开启事务耗时: 5.725228
- 不开启事务耗时:15.122401
- 因为表内有10000条数据, 又执行修改100次, 耗时都较多

// 开启事务
    // 1.0 创建数据库
    NSString *dbPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject stringByAppendingPathComponent:@"tmps.db"];
    
    FMDatabase *db = [FMDatabase databaseWithPath:dbPath];
    
    [db open];

    [db beginTransaction];
    
    CGFloat beginTime2 = [NSDate timeIntervalSinceReferenceDate];
    
    for (NSInteger i = 0; i < 1000; i++) {
        
        [db executeUpdate:@"UPDATE t_PetInfo SET name = ?, age = ?;", @"funny", @(i)];
    }

    [db commit];
    CGFloat endTime2 = [NSDate timeIntervalSinceReferenceDate];
    
    NSLog(@"time2 - %f", endTime2 - beginTime2); // 5.725228
    
    [db close];

   // 不开启事务
    
    NSString *dbPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject stringByAppendingPathComponent:@"tmps.db"];
    
    FMDatabase *db = [FMDatabase databaseWithPath:dbPath];
    
    [db open];
    
    CGFloat beginTime2 = [NSDate timeIntervalSinceReferenceDate];
    
    for (NSInteger i = 0; i < 1000; i++) {
        
        [db executeUpdate:@"UPDATE t_PetInfo SET name = ?, age = ?;", @"funny", @(i)];
    }
    
    CGFloat endTime2 = [NSDate timeIntervalSinceReferenceDate];
    
    NSLog(@"time2 - %f", endTime2 - beginTime2); // 15.122401
     
    [db close];

删除数据耗时实验
- 开启事务耗时: 0.608012
- 不开启事务耗时:4.768991

    // 开启事务
    NSString *dbPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject stringByAppendingPathComponent:@"tmps.db"];
    
    FMDatabase *db = [FMDatabase databaseWithPath:dbPath];
    
    [db open];

    [db beginTransaction];
    
    CGFloat beginTime2 = [NSDate timeIntervalSinceReferenceDate];
    
    for (NSInteger i = 0; i < 1000; i++) {
        
        [db executeUpdate:@"DELETE FROM t_PetInfo WHERE petNo = ?", @(i)];
    }
    
    [db commit];
    CGFloat endTime2 = [NSDate timeIntervalSinceReferenceDate];
    
    NSLog(@"time2 - %f", endTime2 - beginTime2);
    
    [db close]; // 0.608012

    NSString *dbPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject stringByAppendingPathComponent:@"tmps.db"];
    
    FMDatabase *db = [FMDatabase databaseWithPath:dbPath];
    
    [db open];

   // [db beginTransaction];
    
    CGFloat beginTime2 = [NSDate timeIntervalSinceReferenceDate];
    
    for (NSInteger i = 0; i < 1000; i++) {
        
        [db executeUpdate:@"DELETE FROM t_PetInfo WHERE petNo = ?", @(i)];
    }
    
   //[db commit];
    CGFloat endTime2 = [NSDate timeIntervalSinceReferenceDate];
    
    NSLog(@"time2 - %f", endTime2 - beginTime2);
    
    [db close]; // 4.768991

在子线程处理耗时操作
- FMDatabase是多线程不安全的
- 推荐使用FMDatabaseQueue, 使用全局单列, 在子线成执行耗时操作

耗时来由

数据库以文件的形式存在磁盘中, 每次访问时都要打开一次文件，一切的数据库操作其实都会转化为对文件的操作
如果对数据库进行大量的写操作, 则耗时较大
每次执行sqlite3_exec, 默认都会开启一个隐藏事务, 当执行完操作, 就会提交事务; 每次都会操作文件(数据库)

int sqlite3_exec(
sqlite3* ppDb,                             /* An open database */
const char *sql,                           /* SQL to be evaluated */
int (*callback)(void*,int,char**,char**),  /* Callback function */
void *,                                    /* 1st argument to callback */
char **errmsg                              /* Error msg written here */
);

手动开启事务解如何解决耗时操作?
- 当我们手动开启事务时, 系统就不再默认开启隐藏事务
- 开始事务后，进行的大量操作语句都保存在内存中，当提交时才全部写入数据库，此时，数据库文件也只用打开一次;