SQLite的查询优化

SQLite优化之路——让SQLite油门到底 场景   服务需要不断接收数据,每次接收到数据都需要去数据库中寻找20分钟内该数据是否被接收并被处理过,然后根据是否处理过,对该数据再进行处理,最后将该数据再存入改数据库中。 简化该场景,即为 接收数据——> 在数据库中查找一次——>存数据一次。 开发过程中遇到的问题 这是一个很简单的需求,但是再实际过程中却遇到了很多问题 问题1:database is locked 产生这个问题... 阅读详情

本篇文章来源于:开发学院 http://edu.codepub.com   原文链接:http://edu.codepub.com/2009/0825/14358.php

 

SQLite是个典型的嵌入式DBMS,它有很多优点,它是轻量级的,在编译之后很小,其中一个原因就是在查询优化方面比较简单,它只是运用索引机制来进行优化的,经过对SQLite的查询优化的分析以及对源代码的研究,我将SQLite的查询优总结


SQLite是个典型的嵌入式DBMS,它有很多优点,它是轻量级的,在编译之后很小,其中一个原因就是在查询优化方面比较简单,它只是运用索引机制来进行优化的,经过对SQLite的查询优化的分析以及对源代码的研究,我将SQLite的查询优总结如下:

 

 

一、影响查询性能的因素:

1. 对表中行的检索数目,越小越好

2. 排序与否。

3. 是否要对一个索引。

4. 查询语句的形式

 

二、几个查询优化的转换

1. 对于单个表的单个列而言,如果都有形如T.C=expr这样的子句,并且都是用OR操作符连接起来,形如: x = expr1 OR expr2 = x OR x = expr3 此时由于对于OR,在SQLite中不能利用索引来优化,所以可以将它转换成带有IN操作符的子句:x IN(expr1,expr2,expr3)这样就可以用索引进行优化,效果很明显,但是如果在都没有索引的情况下OR语句执行效率会稍优于IN语句的效率。

2. 如果一个子句的操作符是BETWEEN,在SQLite中同样不能用索引进行优化,所以也要进行相应的等价转换: 如:a BETWEEN b AND c可以转换成:(a BETWEEN b AND c) AND (a>=b) AND (a<=c)。 在上面这个子句中, (a>=b) AND (a<=c)将被设为dynamic且是(a BETWEEN b AND c)的子句,那么如果BETWEEN语句已经编码,那么子句就忽略不计,如果存在可利用的index使得子句已经满足条件,那么父句则被忽略。

3. 如果一个单元的操作符是LIKE,那么将做下面的转换:x LIKE ‘abc%’,转换成:x>=‘abc’ AND x<‘abd’。因为在SQLite中的LIKE是不能用索引进行优化的,所以如果存在索引的话,则转换后和不转换相差很远,因为对LIKE不起作用,但如果不存在索引,那么LIKE在效率方面也还是比不上转换后的效率的。

 

三、 几种查询语句的处理(复合查询)
1.查询语句为:<SelectA> <operator> <selectB> ORDER BY <orderbylist> ORDER BY
     执行方法: is one of UNION ALL, UNION, EXCEPT, or INTERSECT. 这个语句的执行过程是先将selectA和selectB执行并且排序,再对两个结果扫描处理,对上面四种操作是不同的,将执行过程分成七个子过程:

   outA: 将selectA的结果的一行放到最终结果集中

   outB: 将selectA的结果的一行放到最终结果集中(只有UNION操作和UNION ALL操作,其它操作都不放入最终结果集中)

    AltB: 当selectA的当前记录小于selectB的当前记录

    AeqB: 当selectA的当前记录等于selectB的当前记录

    AgtB: 当selectA的当前记录大于selectB的当前记录

    EofA: 当selectA的结果遍历完

    EofB: 当selectB的结果遍历完

         下面就是四种操作的执行过程:


 执行顺序
 UNION ALL
 UNION
 EXCEPT
 INTERSECT
 
AltB:
 outA, nextA
 outA, nextA
 outA,nextA
 nextA
 
AeqB:
 outA, nextA
 nextA
 nextA
 outA, nextA
 
AgtB:
 outB, nextB
 outB, nextB
 nextB
 nextB
 
EofA:
 outB, nextB
 outB, nextB
 halt
 halt
 
EofB:
 outA, nextA
 outA, nextA
 outA,nextA
 halt
 

     2. 如果可能的话,可以把一个用到GROUP BY查询的语句转换成DISTINCT语句来查询,因为GROUP BY有时候可能会用到index,而对于DISTINCT都不会用到索引的。

 

四、子查询扁平化

     例子:SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5

     对这个SQL语句的执行一般默认的方法就是先执行内查询,把结果放到一个临时表中,再对这个表进行外部查询,这就要对数据处理两次,另外这个临时表没有索引,所以对外部查询就不能进行优化了,如果对上面的SQL进行处理后可以得到如下SQL语句:SELECT x+y AS a FROM t1 WHERE z<100 AND a>5,这个结果显然和上面的一样,但此时只需要对

数据进行查询一次就够了,另外如果在表t1上有索引的话就避免了遍历整个表。

运用flatten方法优化SQL的条件:

1.子查询和外查询没有都用集函数

2.子查询没有用集函数或者外查询不是个表的连接

3.子查询不是一个左外连接的右操作数

4.子查询没有用DISTINCT或者外查询不是个表的连接

5.子查询没有用DISTINCT或者外查询没有用集函数

6.子查询没有用集函数或者外查询没有用关键字DISTINCT

7.子查询有一个FROM语句

8.子查询没有用LIMIT或者外查询不是表的连接

9.子查询没有用LIMIT或者外查询没有用集函数
 10.子查询没有用集函数或者外查询没用LIMIT

11.子查询和外查询不是同时是ORDER BY子句

12.子查询和外查询没有都用LIMIT

13.子查询没有用OFFSET

14.外查询不是一个复合查询的一部分或者子查询没有同时用关键字ORDER BY和LIMIT

15.外查询没有用集函数子查询不包含ORDER BY

16.复合子查询的扁平化:子查询不是一个复合查询,或者他是一个UNION ALL复合查询,但他是都由若干个非集函数的查询构成,他的父查询不是一个复合查询的子查询,也没有用集函数或者是DISTINCT查询,并且在FROM语句中没有其它的表或者子查询,父查询和子查询可能会包含WHERE语句,这些都会受到上面11、12、13条件的限制。

例:   SELECT a+1 FROM (

              SELECT x FROM tab

              UNION ALL

              SELECT y FROM tab

               UNION ALL

               SELECT abs(z*2) FROM tab2

           ) WHERE a!=5 ORDER BY 1

转换为:

        SELECT x+1 FROM tab WHERE x+1!=5

           UNION ALL

           SELECT y+1 FROM tab WHERE y+1!=5

           UNION ALL

           SELECT abs(z*2)+1 FROM tab2 WHERE abs(z*2)+1!=5

           ORDER BY 1

17.如果子查询是一个复合查询,那么父查询的所有的ORDER BY语句必须是对子查询的列的简单引用

18.子查询没有用LIMIT或者外查询不具有WHERE语句

子查询扁平化是由专门一个函数实现的,函数为:

static int flattenSubquery(

 Parse *pParse,       /* Parsing context */

 Select *p,           /* The parent or outer SELECT statement */

 int iFrom,           /* Index in p->pSrc->a[] of the inner subquery */

 int isAgg,           /* True if outer SELECT uses aggregate functions */

 int subqueryIsAgg    /* True if the subquery uses aggregate functions */

)

       它是在Select.c文件中实现的。显然对于一个比较复杂的查询,如果满足上面的条件时对这个查询语句进行扁平化处理后就可以实现对查询的优化。如果正好存在索引的话效果会更好!

 

 五、连接查询

       在返回查询结果之前,相关表的每行必须都已经连接起来,在SQLite中,这是用嵌套循环实现的,在早期版本中,最左边的是最外层循环,最右边的是最内层循环,连接两个或者更多的表时,如果有索引则放到内层循环中,也就是放到FROM最后面,因为对于前面选中的每行,找后面与之对应的行时,如果有索引则会很快,如果没有则要遍历整个表,这样效率就很低,但在新版本中,这个优化已经实现。

       优化的方法如下:

       对要查询的每个表,统计这个表上的索引信息,首先将代价赋值为SQLITE_BIG_DBL(一个系统已经定义的常量):

1)    如果没有索引,则找有没有在这个表上对rowid的查询条件:

1.如果有Rowid=EXPR,如果有的话则返回对这个表代价估计,代价计为零,查询得到的记录数为1,并完成对这个表的代价估计,

2.如果没有Rowid=EXPR 但有rowid IN (...),而IN是一个列表,那么记录返回记录数为IN列表中元素的个数,估计代价为NlogN,

3.如果IN不是一个列表而是一个子查询结果,那么由于具体这个子查询不能确定,所以只能估计一个值,返回记录数为100,代价为200。

4.如果对rowid是范围的查询,那么就估计所有符合条件的记录是总记录的三分之一,总记录估计为1000000,并且估计代价也为记录数。

5.如果这个查询还要求排序,则再另外加上排序的代价NlogN

6.如果此时得到的代价小于总代价,那么就更新总代价,否则不更新。

2)    如果WHERE子句中存在OR操作符,那么要把这些OR连接的所有子句分开再进行分析。

1. 如果有子句是由AND连接符构成,那么再把由AND连接的子句再分别分析。
2. 如果连接的子句的形式是X<op><expr>,那么就再分析这个子句。

3. 接下来就是把整个对OR操作的总代价计算出来。

4. 如果这个查询要求排序,则再在上面总代价上再乘上排序代价NlogN

5. 如果此时得到的代价小于总代价,那么就更新总代价,否则不更新。

3)    如果有索引,则统计每个表的索引信息,对于每个索引:

1. 先找到这个索引对应的列号,再找到对应的能用到(操作符必须为=或者是IN(…))这个索引的WHERE子句,如果没有找到,则退出对每个索引的循环,如果找到,则判断这个子句的操作符是什么,如果是=,那么没有附加的代价,如果是IN(sub-select),那么估计它附加代价inMultiplier为25,如果是IN(list),那么附加代价就是N(N为list的列数)。

2. 再计算总的代价和总的查询结果记录数和代价。

3. nRow = pProbe->aiRowEst[i] * inMultiplier;/*计算行数*/

4. cost = nRow * estLog(inMultiplier);/*统计代价*/

5. 如果找不到操作符为=或者是IN(…)的子句,而是范围的查询,那么同样只好估计查询结果记录数为nRow/3,估计代价为cost/3。

6. 同样,如果此查询要求排序的话,再在上面的总代价上加上NlogN

7. 如果此时得到的代价小于总代价,那么就更新总代价,否则不更新。

4)    通过上面的优化过程,可以得到对一个表查询的总代价(就是上面各个代价的总和),再对第二个表进行同样的操作,这样如此直到把FROM子句中所有的表都计算出各自的代价,最后取最小的,这将作为嵌套循环的最内层,依次可以得到整个嵌套循环的嵌套顺序,此时正是最优的,达到了优化的目的。

5)    所以循环的嵌套顺序不一定是与FROM子句中的顺序一致,因为在执行过程中会用索引优化来重新排列顺序。

 

六、索引

   在SQLite中,有以下几种索引:

1)    单列索引

2)    多列索引

3)    唯一性索引

4)    对于声明为:INTEGER PRIMARY KEY的主键来说,这列会按默认方式排序,所以虽然在数据字典中没有对它生成索引,但它的功能就像个索引。所以如果在这个主键上在单独建立索引的话,这样既浪费空间也没有任何好处。

运用索引的注意事项:

1)    对于一个很小的表来说没必要建立索引

2)    在一个表上如果经常做的是插入更新操作,那么就要节制使用索引

3)    也不要在一个表上建立太多的索引,如果建立太多的话那么在查询的时候SQLite可能不会选择最好的来执行查询,一个解决办法就是建立聚蔟索引

索引的运用时机:

1)    操作符:=、>、<、IN等

2)    操作符BETWEEN、LIKE、OR不能用索引,

       如BETWEEN:SELECT * FROM mytable WHERE myfield BETWEEN 10 and 20;

       这时就应该将其转换成:

       SELECT * FROM mytable WHERE myfield >= 10 AND myfield <= 20;

       此时如果在myfield上有索引的话就可以用了,大大提高速度

        再如LIKE:SELECT * FROM mytable WHERE myfield LIKE 'sql%';

       此时应该将它转换成:

       SELECT * FROM mytable WHERE myfield >= 'sql' AND myfield < 'sqm';

       此时如果在myfield上有索引的话就可以用了,大大提高速度

       再如OR:SELECT * FROM mytable WHERE myfield = 'abc' OR myfield = 'xyz';

       此时应该将它转换成:

       SELECT * FROM mytable WHERE myfield IN ('abc', 'xyz');

       此时如果在myfield上有索引的话就可以用了,大大提高速度

3)    有些时候索引都是不能用的,这时就应该遍历全表(程序演示)

       SELECT * FROM mytable WHERE myfield % 2 = 1;

       SELECT * FROM mytable WHERE substr(myfield, 0, 1) = 'w';

       SELECT * FROM mytable WHERE length(myfield) < 5;

SQLITE3插入、查询效率的优化方法 插入篇: 1、sqlite3_exec() 通常,我们使用sqlite3_exec()函数来处理数据的插入操作,该函数直接调用sql语句对数据进行插入,所以使用起来很方便,插入100w条数据 for( i = 0; i < 1000000; i++) { snprintf(sqlcmd, sizeof(sqlcmd), "insert into testinfo values(%d, %d, '%d', '%s')", i, i*2, i*10, buf); sqlite3_exe... 阅读详情

相关推荐

elmos 524系列芯片笔记

芯片的工作工作原理:集成芯片Elmos524.03有发射回路和接收回路,手册有关的内部原理图,大致如下: 芯片会产生固有频率的电压信号,信号通过引脚DRV1、2输出给外部压电陶瓷片,产生固定频率的机械波,也就是所谓的超声波,超声波遇到障碍物时会反射,反射信号经过AINS、D引脚进入芯片,在芯片内部先后经过GAIN放大,滤波然后进入比较器,滤波后的信号(signal)同预先设定的阈值(threshold)比较产生信号输出。此信号最终会连接MCU处理器,需要重点关注。 基于这个内部的工作原

qq_38230916的博客 2493

详细讲解SQLite数据库查询优化方案

SQLite是个典型的嵌入式DBMS,它有很多优点,它是轻量级的,在编译之后很小,其中一个原因就是在查询优化方面比较简单,它只是运用索引机制来进行优化的,经过对SQLite的查询优化的分析以及对源代码的研究,我将SQLite的查询优总结如下: 一、影响查询性能的因素: 1. 对表中行的检索数目,越小越好 2. 排序与否。 3. 是否要对一个索引。 4. 查询语句的形式 二、几个查询优化的...

weixin_30340745的博客 350

智能车竞赛飞檐走壁组赛道制作全攻略:从电磁线铺设到立体元素搭建

本文提供了智能车竞赛飞檐走壁组赛道制作的完整指南。从电磁线选材、电流控制与精准铺设等基础入手,详细解析了垂直墙面、圆筒、跷跷板等核心立体元素的构建技巧与过渡处理。重点分享了确保赛道稳定、符合规则且便于调试的实战经验,帮助参赛队伍搭建可靠的立体赛道,为车模算法调试奠定坚实基础。

weixin_29292535的博客 1300

SQLITE3过程中,是否遇到过模糊查询(Like)的麻烦

字号  树型  打印  收藏  帖子主题:大家在用SQLITE3过程中,是否遇到过模糊查询(Like)的麻烦?... tty1a 等级:略有小成 积分:289 金钱:150 帖

wzwind的专栏 1万+

从CORDIC到DDS:手把手拆解《Verilog传奇》第9章的实战项目(附可跑通的代码)

本文详细解析了如何从CORDIC算法到DDS系统的完整实现过程,基于《Verilog传奇》第9章的实战项目,提供了可运行的Verilog代码。内容涵盖CORDIC算法的硬件实现、DDS系统架构设计、完整系统集成与验证,以及实际部署中的性能调优技巧,适合数字IC和FPGA开发者深入学习。

weixin_42623010的博客 417

FPGA控制DDR读写(AXI4总线接口)

FPGA控制DDR读写

qq_42712308的博客 1万+

php高性能sqllite,PHP中 sqlite3数据批量插入性能优化

以下测试得出的时间仅供参考,实际时间因服务器的配置不同会有差异传统方式,一万条数据插入耗时(46s)$sqlite_db = new PDO('sqlite:sqlitedata.db', 'zheng', 'zheng', []);$start = time();$n = 0;while ($n < 100000) {$n ++;$sql = "INSERT INTO `result_da...

weixin_39553653的博客 407

WVP+ZLM国标平台实战:从设备注册失败到视频播放黑屏,我遇到的5个典型问题与解法

部署WVP+ZLM国标视频平台时,设备注册失败、视频播放黑屏等问题常常让运维人员头疼不已。本文将基于真实运维场景,剖析五个最具代表性的故障案例,提供从网络配置到版本兼容性的全链路解决方案。

weixin_33178839的博客 362

sqlite 速度 php,php和asp.net下SQLite3的执行速度对比

当看到这样的结果时,我很诧异。我以为php会更快一点儿,但事实是asp.net更胜一筹。不解!php 页面:0.5秒 asp.net 页面 :0.4秒 php页面源码/*** Simple function to replicate PHP 5 behaviour*/function microtime_float(){list($usec, $sec) = explode(" ", microt...

weixin_35826493的博客 347

某物data魔改与变种算法还原(含加解密Py纯算)

某物data加解密、设备指纹魔改、变异、自定义加密分析与纯算还原

吴秋霖的博客 1283

视觉Transformer实战 | Token-to-Token Vision Transformer(T2T-ViT)详解与实现

Vision Transformer (ViT) 在计算机视觉领域取得了巨大成功,但标准的 ViT 存在一些局限性,如需要大规模预训练数据、对局部结构建模不足等。Token-to-Token ViT (T2T-ViT) 通过引入渐进式分词过程改进了原始 ViT,使其能够在中小型数据集上取得更好的性能。本节将详细介绍 T2T-ViT 的技术原理,并使用 PyTorch 从零开始实现 T2T-ViT。

盼小辉丶的博客 4万+

SQLite查询优化

本文阐述SQLite的查询计划任务如何选择最小化磁盘I/O和CPU开销的算法。

陆业聪 1774

SAP 系统中的数字签名解决方案及应用案例分析

在当前企业信息化及数字化转型的大背景下,数据安全与身份认证显得尤为重要。数字签名技术作为保障数据真实性和完整性的重要手段,在 SAP 系统中得到了广泛应用。本文将详细探讨 SAP 系统中部分应用场景如何利用数字签名技术满足业务需求,涵盖 SAP Single Sign-On、质量管理、过程工业生产计划以及 SAP ArchiveLink 内容服务器等方面,力求通过深入解读,帮助读者更好地理解和应用这一关键技术。

2007 年 ~ 2025 年,深耕 SAP 技术 18 年 530

SQLite查询优化性能要点

数据库查询优化

wukunting的专栏 7909

Data-Structures-and-Algorithm-Analysis-in-Java.pdf

作者: Weiss, Mark A. 出版社: Addison Wesley Data Structures and Algorithm Analysis in Java is an “advanced algorithms” book that fits between traditional CS2 and Algorithms Analysis courses. In the old ACM Curriculum Guidelines, this course was known as CS7. This text is for readers who want to learn good programming and algorithm analysis skills simultaneously so that they can develop such programs with the maximum amo...

Camera2Demo

在Camera2 的基础上进行封装,使得Camera2一堆初始化逻辑与Activity分离,降低耦合度,完成预览拍照并保存的操作

Android SQLite的查询优化(三)

http://www.eoeandroid.com/thread-83610-1-1.html   五、连接查询         在返回查询结果之前,相关表的每行必须都已经连接起来,在SQLite中,这是用嵌套循环实现的,在早期版本中,最左边的是最外层循环,最右边的是最内层循环,连接两个或者更多的表时,如果有索引则放到内层循环中,也就是放到FROM最后面,因为对于前面选中的每行,找后面与之对应

lgb861127的专栏 4273

汉书HE8,好用的汇编代码编写工具

汉书HE8,好用的汇编代码编写工具早期编写汇编代码所用功能强大

SQLite查询处理及优化

<br />http://edu.codepub.com/2010/1125/27429.php<br /> <br /> <br />查询处理及优化是关系数据库得以流行的根本原因,也是关系数据库系统最核心的技术之一。SQLite的查询处理模块非常的精致,而且很容易移植到不支持SQL的存储引擎,Berkeley DB最新的版本已经将其完整的移植过来。本文将简要<br />查询处理及优化是关系数据库得以流行的根本原因,也是关系数据库系统最核心的技术之一。SQLite的查询处理模块非常的精致,而且很容易移植到不

zenwanxin的专栏 1347

SQLite 查询优化器概述(九)

本文档概述了查询规划器和优化器如何 用于 SQLite 工作。 给定一个 SQL 语句,可能有几十个、几百个甚至 实现该语句的数千种方法,具体取决于复杂性 语句本身和基础数据库架构。这 查询规划器的任务是选择最小化的算法 磁盘 I/O 和 CPU 开销。 索引教程文档中提供了其他背景信息。 从 3.8.0 版 (2013-08-26) 开始, SQLite查询规划器被重新实现为下一代查询规划器或“NGQP”。所有功能、技术、 本文档中描述的算法适用于 3.8.0 之前的旧版查询规划器和 NGQP。欲了

2320

Android SQLite的查询优化(一)

http://www.eoeandroid.com/thread-83601-1-1.html  SQLite是个典型的嵌入式DBMS,它有很多优点,它是轻量级的,在编译之后很小,其中一个原因就是在查询优化方面比较简单,它只是运用索引机制来进行优化的,经过对SQLite的查询优化的分析以及对源代码的研究,我将SQLite的查询优总结如下:         一、影响查询性能的因素:  

lgb861127的专栏 4268

SQLite数据库查询优化

SQLite是个典型的嵌入式DBMS,它有很多优点,它是轻量级的,在编译之后很小,其中一个原因就是在查询优化方面比较简单,它只是运用索引机制来进行优化的,经过对SQLite的查询优化的分析以及对源代码的研究,我将SQLite的查询优总结如下: 一、影响查询性能的因素: 1. 对表中行的检索数目,越小越好 2. 排序与否。 3. 是否要对一个索引。 4. 查询语句的形式 二、几个查询优化...

weixin_30770495的博客 94
上一篇: 在VC环境中调试ISAPI程序
下一篇: MySQL大战SQLite(PostgreSQL强势乱入)
UU_Yang
博客等级 码龄23年 926粉丝 213原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值