數(shù)據(jù)庫操作優(yōu)化

數(shù)據(jù)庫耗時(shí)操作

主要是往數(shù)據(jù)庫執(zhí)行寫操作

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

// 更新數(shù)據(jù)
[db executeUpdate:@"UPDATE t_PetInfo SET name = ?", @"fffffancy"];

// 刪除數(shù)據(jù)
[db executeUpdate:@"DELETE FROM t_PetInfo WHERE petNo > ?", @(20)];

耗時(shí)操作優(yōu)化
- 利用事務(wù)提交可以有效的減少耗時(shí)
- 在子線程處理耗時(shí)操作

耗時(shí)操作實(shí)驗(yàn)

插入數(shù)據(jù)

開啟事務(wù)耗時(shí): 0.097206
不開啟事務(wù)耗時(shí):38.051225
插入10000條數(shù)據(jù), 不開啟事務(wù)耗時(shí)是開啟事務(wù)的391倍


// 1.0 開啟事務(wù)
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); // 耗時(shí): 0.097206

// 2.0 不開啟事務(wù)
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); // 耗時(shí): 38.051225

查詢耗時(shí)實(shí)驗(yàn)
- 開啟事務(wù)耗時(shí): 0.037018
- 不開啟事務(wù)耗時(shí):0.037175
- 兩者基本一樣, 因?yàn)樽x操作只需數(shù)據(jù)庫一次, 頻繁的開啟事務(wù), 提交事務(wù)
- 當(dāng)然, 當(dāng)有大量讀操作時(shí), 依舊推薦使用事務(wù)

    
    // 開啟事務(wù)
    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];


// 不開啟事務(wù)
    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];

更新操作耗時(shí)實(shí)驗(yàn)
- 開啟事務(wù)耗時(shí): 5.725228
- 不開啟事務(wù)耗時(shí):15.122401
- 因?yàn)楸韮?nèi)有10000條數(shù)據(jù), 又執(zhí)行修改100次, 耗時(shí)都較多

// 開啟事務(wù)
    // 1.0 創(chuàng)建數(shù)據(jù)庫
    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];

   // 不開啟事務(wù)
    
    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];

刪除數(shù)據(jù)耗時(shí)實(shí)驗(yàn)
- 開啟事務(wù)耗時(shí): 0.608012
- 不開啟事務(wù)耗時(shí):4.768991

    // 開啟事務(wù)
    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

在子線程處理耗時(shí)操作
- FMDatabase是多線程不安全的
- 推薦使用FMDatabaseQueue, 使用全局單列, 在子線成執(zhí)行耗時(shí)操作
耗時(shí)來由
- 數(shù)據(jù)庫以文件的形式存在磁盤中, 每次訪問時(shí)都要打開一次文件狠怨，一切的數(shù)據(jù)庫操作其實(shí)都會(huì)轉(zhuǎn)化為對文件的操作
- 如果對數(shù)據(jù)庫進(jìn)行大量的寫操作, 則耗時(shí)較大
- 每次執(zhí)行sqlite3_exec, 默認(rèn)都會(huì)開啟一個(gè)隱藏事務(wù), 當(dāng)執(zhí)行完操作, 就會(huì)提交事務(wù); 每次都會(huì)操作文件(數(shù)據(jù)庫)
```
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 */
);
```
手動(dòng)開啟事務(wù)解如何解決耗時(shí)操作?
- 當(dāng)我們手動(dòng)開啟事務(wù)時(shí), 系統(tǒng)就不再默認(rèn)開啟隱藏事務(wù)
- 開始事務(wù)后惧磺，進(jìn)行的大量操作語句都保存在內(nèi)存中，當(dāng)提交時(shí)才全部寫入數(shù)據(jù)庫饼灿，此時(shí)夷家，數(shù)據(jù)庫文件也只用打開一次;