Sqlite3插入优化总结

---

背景

最近在完成矢量数据持久化到Spatialite数据库的功能，主要的应用场景，是为了通过Spatialite提供的基于RTree的空间索引能力，完成实时从db内请求某个范围内的矢量数据，获取其信息（点集、样式、字段），呈现在地图上，或者进行矢量信息处理相关的业务。

为什么选择Spatialite：

1. 本质上是基于Sqlite的轻量级文本型数据库，在之上提供的空间索引能力；
2. 本地文本型数据库，便于移植；
3. RTree的空间索引算法，满足大多数基于空间请求的地理数据应用需求；
4. 贴合GIS领域：提供API对于有PostGIS数据库经验来说很友好。

本章的重点，将主要放在N条矢量数据的插入效率的优化上面，本质就是Sqlite3插入效率的优化（任何关于矢量和Spatialite方面的都只是应用环境支撑，可以暂时忽略）。

---

一、前置准备

数据库表设计

（结合一个简单的应用环境验证，因为是结合GIS领域的开发，所以表设计和矢量信息表达有关）
提供一张名为【shape】的表，字段设计如下：
也就是说，后面的一条条数据将对应以上字段的结构插入。

数据导入

（因为数据是从*.shp文件中获取出来的，所以用到了GDAL-OGR的方法解析矢量数据，不过不是本章的重点，仅仅是为了说明数据来源）
导入文件：【街区.shp】-44.4MB 矢量个数：266311个，即插入条数：266311条。

1. 通过OGR解析出一个个OGRFeature作为矢量数据；
2. 随机生成一自增数作为UUID，写入uuid字段；
3. 字段puuid、字段type、字段createtime、字段modifytime忽略；
4. OGRFeature获取的name，写入name字段
5. OGRFeature的点集转换为wkb形式，写入geom字段。

(OGR读取，点集转Blob数据，测试后不消耗时间，可以忽略）

基本导入流程

Load -> TravelShpFile -> Unload

• Load：通过OGR加载*.shp文件；打开指定的db文件（sqlite3_open_v2），关联spatialite；
• TravelShpFile：对于解出的每一条矢量数据进行insert操作，后面将从该函数的具体不同实现做对比；
• Unload：销毁打开文件的Handler，关闭db操作的Handler（sqlite3_close）。

关于Sqlite3的插入sql操作

sqlite3提供了以下2种操作插入sql的方式：

• sqlite3_exec()函数直接调用sql语句字符串，每执行一次该函数，其实是封装了“词法分析”和“语法分析”的过程；
• sqlite3提供的“执行准备”功能，事先把sql语句编译成系统能够理解的语言，然后一步一步执行，相当于细分开了“词法分析”和“语法分析”。
  preparer -> [reset -> bind -> step] x n -> finalize

---

二、数据插入

关于Sqlite3插入优化的对比实践，将分为以下3种情况对比：

1.不开启事务，一个函数内封装[preparer -> reset -> bind -> step -> finalize] ，n次调用

因为表字段有blob字段，所以没有直接使用sqlite3_exec()调用来进行分析;
以上的操作，可以理解为该函数封装成sqlite3_exec()；
批量操作时，不推荐的一种调用方式，这里为了测试数据而封装。

• 封装函数如下：

bool CShapeGeometryDB::InsertShape(const StShapeInfo& stShapeInfo, unsigned char* pBlob, int nBlobSize)
{
	std::string strSql = "INSERT INTO 'main'.'shape'('uuid', 'puuid', 'type', 'name', 'createtime', 'modifytime', 'geom') VALUES(?, ?, ?, ?, ?, ?, ?)";
	if (NULL == m_pDB)
	{
		return false;
	}

	sqlite3_stmt* stmt;
	int ret = sqlite3_prepare_v2(m_pDB, strSql.data(), strSql.size(), &stmt, NULL);
	if (ret != SQLITE_OK)
	{
		std::cout << "INSERT prepare Error! " << sqlite3_errmsg(m_pDB) << std::endl;
		return false;
	}

	sqlite3_bind_text(stmt, 1, stShapeInfo.m_strUUID.data(), stShapeInfo.m_strUUID.size(), NULL);
	sqlite3_bind_text(stmt, 2, stShapeInfo.m_strPUUID.data(), stShapeInfo.m_strPUUID.size(), NULL);
	sqlite3_bind_int(stmt, 3, stShapeInfo.m_nType);
	sqlite3_bind_text(stmt, 4, stShapeInfo.m_strName.data(), stShapeInfo.m_strName.size(), NULL);
	sqlite3_bind_text(stmt, 5, NULL, 0, NULL);
	sqlite3_bind_text(stmt, 6, NULL, 0, NULL);
	sqlite3_bind_blob(stmt, 7, pBlob, nBlobSize, free);

	bool bOk = true;
	ret = sqlite3_step(stmt);
	if (ret != SQLITE_DONE && ret != SQLITE_ROW)
	{
		std::cout << "InsertShape sqlite3_step Error! " << sqlite3_errmsg(m_pDB) << std::endl;
		bOk = false;
	}

	sqlite3_finalize(stmt);
	return bOk;
}

• 调用逻辑：N x InsertShape(xxx);

共耗时：
4985046ms （约83.0841分钟）

---

2. 开启事务后，执行同上函数：

• 事务相关代码：

bool CShapeGeometryDB::Transaction()
{
        if (NULL == m_pDB)
        {
                return false;
        }

        char* err_msg = NULL;
        std::string strSql = "BEGIN";	// 开启事务
        int ret = sqlite3_exec(m_pDB, strSql.data(), NULL, NULL, &err_msg);
        if (ret != SQLITE_OK)
        {
                if (NULL != err_msg)
                {
                        std::cout << "BEGIN Error! " << err_msg << std::endl;
                        sqlite3_free(err_msg);
                }
                return false;
        }

        return true;
}

bool CShapeGeometryDB::Commit()
{
        if (NULL == m_pDB)
        {
                return false;
        }

        char* err_msg = NULL;
        std::string strSql = "COMMIT";	// 提交事务
        int ret = sqlite3_exec(m_pDB, strSql.data(), NULL, NULL, &err_msg);
        if (ret != SQLITE_OK)
        {
                if (NULL != err_msg)
                {
                        std::cout << "COMMIT Error! " << err_msg << std::endl;
                        sqlite3_free(err_msg);
                }
                return false;
        }

        return true;
}

bool CShapeGeometryDB::RollBack()
{
        if (NULL == m_pDB)
        {
                return false;
        }

        char* err_msg = NULL;
        std::string strSql = "ROLLBACK";	// 回滚事务
        int ret = sqlite3_exec(m_pDB, strSql.data(), NULL, NULL, &err_msg);
        if (ret != SQLITE_OK)
        {
                if (NULL != err_msg)
                {
                        std::cout << "COMMIT Error! " << err_msg << std::endl;
                        sqlite3_free(err_msg);
                }
                return false;
        }

        return true;
}

• 调用逻辑：Transaction() -> N x InsertShape(xxx) -> Commit()

共耗时：
66234ms （约1.1039分钟）

---

3. 开启事务后，使用“执行准备”的方式

使用 sqlite3_stmt* 的成员变量来串起这次批量操作的执行；
Begin和End标志这次批量的开始和结束，也是sqlite3提供的“执行准备”，一次完整的闭环。

• 代码相关：

成员变量，初始化列表：NULL
sqlite3_stmt* m_stmt;

bool CShapeGeometryDB::BeginForBatch()
{
	Transaction();
	return true;
}

bool CShapeGeometryDB::InsertForBatch(const StShapeInfo& stShapeInfo, unsigned char* pBlob, int nBlobSize)
{
	// reset
	if (NULL != m_stmt)
	{
		sqlite3_reset(m_stmt);
		sqlite3_clear_bindings(m_stmt);
	}

	// prepare
	if (NULL == m_stmt)
	{
		std::string strSql = "INSERT INTO 'main'.'shape'('uuid', 'puuid', 'type', 'name', 'createtime', 'modifytime', 'geom') VALUES(?, ?, ?, ?, ?, ?, ?)";
		int ret = sqlite3_prepare_v2(m_pDB, strSql.data(), strSql.size(), &m_stmt, NULL);
		if (ret != SQLITE_OK)
		{
			std::cout << "INSERT prepare Error! " << sqlite3_errmsg(m_pDB) << std::endl;
			return false;
		}
	}

	// bind
	sqlite3_bind_text(m_stmt, 1, stShapeInfo.m_strUUID.data(), stShapeInfo.m_strUUID.size(), NULL);
	sqlite3_bind_text(m_stmt, 2, stShapeInfo.m_strPUUID.data(), stShapeInfo.m_strPUUID.size(), NULL);
	sqlite3_bind_int(m_stmt, 3, stShapeInfo.m_nType);
	sqlite3_bind_text(m_stmt, 4, stShapeInfo.m_strName.data(), stShapeInfo.m_strName.size(), NULL);
	sqlite3_bind_text(m_stmt, 5, NULL, 0, NULL);
	sqlite3_bind_text(m_stmt, 6, NULL, 0, NULL);
	sqlite3_bind_blob(m_stmt, 7, pBlob, nBlobSize, free);

	// step
	bool bOk = true;
	int ret = sqlite3_step(m_stmt);
	if (ret != SQLITE_DONE && ret != SQLITE_ROW)
	{
		std::cout << "InsertShape sqlite3_step Error! " << sqlite3_errmsg(m_pDB) << std::endl;
		bOk = false;
	}

	return bOk;
}

bool CShapeGeometryDB::EndForBatch()
{
	if (NULL == m_stmt)
	{
		return false;
	}

	// finalize
	sqlite3_finalize(m_stmt);
	m_stmt = NULL;

	Commit();

	return true;
}

• 调用逻辑：BeginForBatch() -> N x InsertForBatch(xxx) -> EndForBatch()

共耗时：
33640ms

---

三、对比总结

方法一：不开启事务，一个函数内封装[preparer -> reset -> bind -> step -> finalize] ，n次调用。
方法二：开启事务后，执行同上函数。
方法三：开启事务后，使用“执行准备”的方式。

综上所述：开启事务和不开启事务的区别是相当大的，毕竟频繁开启和关闭I/O的操作是最不推荐的，而开启了事务，相当于一次开启后，批量写入，然后一次关闭。
“执行准备”也相较普通的exec()操作快了一点，因为这是sqlite3提供的更细分的API来支持“准备 -> 执行 x N -> 编译”。

更大的尝试

用方法三，导入1.02GB的shp文件，即6994448条数据插入：
共耗时：
1583000ms (26.38分钟)
每50000条插入统计：10125ms-15265ms范围浮动，没有逐步递增的规律

---

其它

关于Sqlite的插入优化，还有其它的从para等配置的方式，改变一些机制来支持，比如：
Sqlite3的synchronous的模式选择

bool BuildParamaSynchronous()
{
	std::string strSql = "PRAGMA synchronous = OFF";
	return SQLITE_OK == sqlite3_exec(m_pHandle, strSql.data(), NULL, NULL, NULL);
}

有三种模式：
PRAGMA synchronous = FULL; (2)
PRAGMA synchronous = NORMAL; (1)
PRAGMA synchronous = OFF; (0)
可参考：https://blog.csdn.net/chinaclock/article/details/48622243