SQLite数据库插入数据优化(swift)

第一种讨论的是正常的插入:

func insertStu() -> Bool {
        //字符串类型,一定要加''
        let sql = "insert into t_person(name,age,score) values ('\(name)',\(age),\(score))"
        return SQLiteTool.shareinstance.execSQlite(sql)
    }

//执行语句

        // 参数1: 就是一个已经打开的数据库
        // 参数2: sql字符串
        // 参数3: 回调代码块 nil
        // 参数4: 参数3 中的参数1 nil
        // 参数5: 错误信息
    func execSQlite(sql : String) -> Bool {
        return (sqlite3_exec(db, sql, nil, nil, nil) == SQLITE_OK)
    }

如果有10000条数据需要加到数据库中,通过上面这个方式插入,实测时间为():

        5.88269501924515
        5.76592200994492
        5.82355201244354
        5.48133200407028
        6.22769200801849
        6.8999969959259(多次测试数据)

说明:不同的机器,测试时间不同,这是纵向的,在同一台机器上测试是横向的,不同的机器测试的时间不同并不能掩盖优化后的明显效果;

使用下面方法对插入10000条数据执行时间的计算:

let startTime = CFAbsoluteTimeGetCurrent()
let endTime = CFAbsoluteTimeGetCurrent()
print(endTime - startTime)

本文分为两层优化,第一层是使用sqlite3_exec内部实现原理进行直接插入数据,第二层是对事物的优化;

sqlite3_exec函数内部是对”准备语句”的包装,而接下来我们将使用参数绑定的方式,进行插入操作;

参数绑定:

1>创建准备语句
2>绑定参数
3>执行
4>重置
4>销毁

第二种插入方法:参数绑定

//0.固定写法
      let sql = "insert into t_person(name,age,score) values (?,?,?)"
      //1.创建准备语句
        //只有调用SQLiteTool就会开启一个数据库,这个数据库就是SQLiteTool.shareinstance.db
        let db = SQLiteTool.shareinstance.db
        var tempDB : COpaquePointer = nil
        //准备语句函数
        if sqlite3_prepare_v2(db, sql, -1, &tempDB, nil) != SQLITE_OK{
            print("编译失败")
        }else{
            print("编译成功")
        }
          let startTime = CFAbsoluteTimeGetCurrent()        
        //模拟大数据插入
        for _ in 0..<10000 {

        //2.绑定
            // 不安全的按位进行转换
            // 一定要注意,必须明确的确定, 最终的值是什么类型,才能进行转换
            // 否则,崩溃!
            // 参数1: 需要转换的额值
            // 参数2: 代表, 想要转换成为的类型
        let sqlites_type = unsafeBitCast(-1, sqlite3_destructor_type.self)
            // 绑定姓名
            // 参数1: 准备语句
            // 参数2: 绑定的索引(索引, 从1开始)
            // 参数3: 绑定的值
            // 参数4: 代表参数三取出多少长度 -1自动计算
            // 参数5: 代表, 参数的处理方式
            //   #define SQLITE_STATIC      ((sqlite3_destructor_type)0)
            // 代表, 把参数当做一个静态的值, 不会被释放(内部处理, 不对参数做任何的引用)
            //   #define SQLITE_TRANSIENT   (()-1)
            // 代表, 把参数当做是一个临时的值, 有可能会被释放, (内部处理, 对参数做一次引用, 然后, 在合适的地方, 释放掉)
        sqlite3_bind_text(tempDB, 1, name, -1, sqlites_type)
        //绑定年龄
        sqlite3_bind_int(tempDB, 2, age)
        //绑定分数
        sqlite3_bind_double(tempDB, 3, score)

        //3.执行
        if sqlite3_step(tempDB) != SQLITE_DONE{
            print("执行失败")
            return
        }

        //4.重置
        //将绑定的值清空
        sqlite3_reset(tempDB)
        }
        //任务执行完毕后将大门关闭
        SQLiteTool.shareinstance.commitTransaction()
        let enTime = CFAbsoluteTimeGetCurrent()

        print(enTime - startTime)
        //5.销毁
        sqlite3_finalize(tempDB)

因为创建准备语句和销毁只需要执行一次,所以只在第2步~第4步之间进行时间计算;实测时间为:

    5.37796801328659
    5.0898619890213
    5.47358798980713(多次测试数据)
    和使用sqlite3_exec函数的时间稍微好一点,好在的地方是不用每次执行操作都要创建准备语句和销毁,但是优化的效果不明显,接下来进行进一步优化;

SQLite数据库的事务优化:

在使用参数绑定这种方式插入数据的时候,对于每一条数据,系统都会默认开启一个事务进行处理,处理完毕后再关闭事务,处理下一条数据的时候,就会重新开启一个事务,而一个事务是可以处理很多数据的,这里是重复开启事务,浪费性能;

事务就像一扇大门,如果有一个人想要进去,就要开门,进去后关门,然后下一个人还要再开门关门;如果只开启一一次大门,等人都进去了再关门,就会节省很多时间;

当手动开启和关闭事务的时候,系统就不再默认开启关闭,一个注意点就是,手动开启和关闭要成对的;不然会出问题;

数据库工具类中定义两个方法用来实现’开门’和’关门’:

func benginTransaction() -> Void {
        let sql = "begin transaction"
        execSQlite(sql)
    }

    func commitTransaction() -> Void {
        let sql = "commit transaction"
        execSQlite(sql)
    }

接下来对上面的参数绑定代码进行改进:

注意:这里的关闭’大门’,其实是提交事务;

//0.准备语句
        let sql = "insert into t_person(name,age,score) values (?,?,?)"

        //1.创建准备语句
        //只有调用SQLiteTool就会开启一个数据库,这个数据库就是SQLiteTool.shareinstance.db
        let db = SQLiteTool.shareinstance.db
        var tempDB : COpaquePointer = nil

        if sqlite3_prepare_v2(db, sql, -1, &tempDB, nil) != SQLITE_OK{
            print("编译失败")
        }else{
            print("编译成功")
        }
        let startTime = CFAbsoluteTimeGetCurrent()
        //执行任务前,将'大门'打开
        //如果手动开启,则系统将不再自动开启关闭
        SQLiteTool.shareinstance.benginTransaction()
        //模拟大数据插入
        for _ in 0..<10000 {


        //2.绑定
            // 不安全的按位进行转换
            // 一定要注意,必须明确的确定, 最终的值是什么类型,才能进行转换
            // 否则,崩溃!
            // 参数1: 需要转换的额值
            // 参数2: 代表, 想要转换成为的类型
        let sqlites_type = unsafeBitCast(-1, sqlite3_destructor_type.self)
            // 绑定姓名
            // 参数1: 准备语句
            // 参数2: 绑定的索引(索引, 从1开始)
            // 参数3: 绑定的值
            // 参数4: 代表参数三取出多少长度 -1自动计算
            // 参数5: 代表, 参数的处理方式
            //   #define SQLITE_STATIC      ((sqlite3_destructor_type)0)
            // 代表, 把参数当做一个静态的值, 不会被释放(内部处理, 不对参数做任何的引用)
            //   #define SQLITE_TRANSIENT   (()-1)
            // 代表, 把参数当做是一个临时的值, 有可能会被释放, (内部处理, 对参数做一次引用, 然后, 在合适的地方, 释放掉)
        sqlite3_bind_text(tempDB, 1, name, -1, sqlites_type)
        //绑定年龄
        sqlite3_bind_int(tempDB, 2, age)
        //绑定分数
        sqlite3_bind_double(tempDB, 3, score)

        //3.执行
        if sqlite3_step(tempDB) != SQLITE_DONE{
            print("执行失败")
            return
        }

        //4.重置
        //将绑定的值清空
        sqlite3_reset(tempDB)
        }
        //任务执行完毕后将'大门'关闭
        SQLiteTool.shareinstance.commitTransaction()
        let enTime = CFAbsoluteTimeGetCurrent()

        print(enTime - startTime)
        //5.销毁
        sqlite3_finalize(tempDB)

手动开启事务和提交事务后测试插入10000条数据的时间为:

    0.0318340063095093
    0.0334579944610596
    0.0285890102386475
    效果是很突出的(多次实测数据)