背景：

之前对于数据库操作部分大多停留在会使用阶段，并未真正考虑过数据库sql语句的执行效率问题。近期响应项目组其他成员需要，协助手动修改数据库。为了提高工作效率编写了C#测试工程进行相关操作。由于数据库记录量较大（超过10K条），在编写程序过程中发现不同的操作执行效率相差甚远，特做记录如下，待后续有时间再深入分析各种数据库的sql语句的执行效率。
此次数据库修改要求涉及到删除、修改、添加等各种操作，为了简单对比各种方式下程序的执行效率，只选择“删除”操作进行分析。详情如下：

for循环逐条删除：

起初最直接的思路是通过for循环逐个删除符合条件的记录，代码如下所示:

执行效果如下:

【知识点补充】：在使用循环操作过程中，可以看到在数据库文件目录下生成了XXX.db3-journal的临时文件，且大小随时在变动。（关于journal文件的描述参见博文）journal是sqlite用于回滚操作的暂存文档，当数据库写入失败时，通过journal文档可以复原到未更改之前的数据，通过观察本机目录下的journal文件发现，其大小在随时变动因此可以猜测，代码中循环的每一步都在进行数据库打开、写入操作，因此导致耗时超出容忍极限。

使用事务（Transaction）：

sqlite缺省为每个操作启动一个事务，也就是上述循环代码中每一次DELETE以及每一次SELECT都启动了一个事务，如是“事务开启+sql执行+事务关闭”自然耗费了大量的时间。而手动显式调用sqliteTransaction将循环中的所有sql操作放到同一个事务中，此时只需要一次事务开启和关闭的时间即可。（参考博文）
另外sqlite的数据库本质上就是一个磁盘上的文件，所以一切的数据库操作都会转化为对文件的操作，而频繁的文件操作会是一个耗时的I/O过程，极大的影响数据库的存取速度。
显式调用Transaction的代码如下：

执行效果为：

由此可见，效率较之前“暴力”方式有了明显数量级的提升，而且在程序执行过程中看不到XXX.db3-journal临时文件的生成。

使用LIKE查询+组合sql：

再分析一下上述操作流程可以发现，之前的操作是分两部分完成的：第一步，SELECT查询满足要求的待删除数据；第二，使用for循环逐条DELETE之前SELECT的结果。这两次操作之间既开辟了多余的List变量，又重复操作了sqlite数据库文件，因此执行效率还有待提高。
这里用LIKE进行条件查询，然后使用组合sql语句直接删除满足条件的结果，具体代码如下：

执行效果如下：

由此可见在使用事务Transaction的基础上，通过改进程序整体流程又一次减少了数据库打开、关闭的次数，效率有了进一步的提升。对比上述三种操作效率，结果如下：

总结：

随着ORM技术的出现，对于数据库的操作越来越简单，之前也介绍过使用Telerik OpenAccess来进行数据库相关操作。正是由于诸多新技术的出现使得在项目实际过程中越来越不注重最底层的、最基本的执行效率，对于单条sql语句、单次操作往往不做任何考虑，直接使用最原始的“野蛮”方式。另外由于之前项目大多是单机版，不会出现WEB应用中海量用户多并发，因此对于数据库的执行效率感受就不够深刻。随着云计算、大数据、分布式的逐渐普及，数据库技术（尤其是执行效率）会越发重要，【切记】后续在此方面要多用心。




作者：zssure@163.com
时间：2015/08/16