`
sjl599
  • 浏览: 17321 次
  • 性别: Icon_minigender_1
  • 来自: 北京
最近访客 更多访客>>
文章分类
社区版块
存档分类
最新评论

SQLite的查询优化

阅读更多
SQLite是个典型的嵌入式DBMS,它有很多优点,它是轻量级的,在编译之后很小,其中一个原因就是在查询优化方面比较简单,它只是运用索引机制来进行优化的:
一、影响查询性能的因素:

  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.
这个语句的执行过程是先将select A和select B执行并且排序,再对两个结果扫描处理,对上面四种操作是不同的,将执行过程分成七个子过程:

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;

分享到:
评论

相关推荐

    sqlite 优化资料

    SQLite是一款轻量级的数据库引擎,广泛应用于嵌入式设备和移动应用中。它无需单独的服务器进程,可以直接...通过深入理解SQLite的工作机制、优化查询和索引策略,以及调整系统设置,可以在Windows环境中最大化其潜力。

    SQLite 查询所有 分页查询 查询个数

    - 使用`EXPLAIN`:在查询语句前加上`EXPLAIN`,可以查看SQLite的查询计划,帮助分析和优化查询性能。 通过掌握以上技巧,你可以在SQLite中有效地进行分页查询和获取数据总数,同时确保应用的性能和响应速度。在实际...

    SQLite3的性能优化

    SQLite3 的性能优化 SQLite3 是一个轻量级的关系数据库管理系统,常用于嵌入式系统中。在嵌入式系统中,数据库的性能优化尤为重要。本文将主要讲述在嵌入式中使用 SQLite3 时,对相关的数据库的性能如何优化的问题...

    C# SQLite执行效率的优化教程

    C# SQLite执行效率优化教程 本文主要介绍了关于C# SQLite执行效率优化的相关资料,通过示例代码介绍的非常详细,对大家学习或者使用C# SQLite具有一定的参考学习价值。 一、SQLite简介 SQLite是一款轻型的嵌入式...

    SQLite3性能优化

    然而,随着数据量的增长和复杂查询的增加,性能优化变得至关重要。本文将探讨如何通过空间释放、磁盘同步和调整Cache大小等方式来提升SQLite3的性能。 一、空间释放 1. 清理无用数据:定期运行VACUUM命令可以帮助重...

    sqlite 查询分析器

    10. **日志和错误追踪**:查询分析器会记录执行的操作和出现的错误,帮助用户调试和优化 SQL 语句。 文件“Setup VisualSQLite.exe”是安装程序,用户可以通过运行这个文件在计算机上安装 SQLite 查询分析器。而...

    sqlite 查询分析工具

    总的来说,SQLite查询分析工具是开发人员、数据库管理员和数据分析人员的得力助手,它能够有效地管理和优化SQLite数据库,提高工作效率。通过使用像SQLiteStudio这样的工具,用户可以更轻松地进行数据操作,分析查询...

    sqlite 支持中文模糊查询

    5. **查询优化**:虽然LIKE操作符方便,但其性能可能不如全文搜索或其他特定的索引策略。对于大量数据和频繁的模糊查询,考虑创建全文索引或使用全文搜索引擎(如SQLite的FTS模块)可能会更高效。 总的来说,现代...

    sqlite优化方法比较

    SQLite优化对于提升应用性能至关重要,尤其是在处理大量数据或频繁读写操作时。以下是一些关于SQLite优化的关键知识点: 1. **数据库设计优化**: - **表结构设计**:合理设计数据库表结构,包括选择合适的数据...

    SQLite的查询优化[借鉴].pdf

    SQLite的查询优化主要依赖于索引,本文将深入探讨影响查询性能的因素,提供一些查询优化的转换策略,以及处理复合查询和子查询的方法。 一、影响查询性能的因素: 1. 表中行的检索数目:查询涉及的数据行越少,性能...

    sqlite3性能优化源代码 数据插入 开启事务 执行准备 性能提升 每秒百万条数据写入

    在本文中,我们将深入探讨如何通过优化SQLite3的源代码来提高数据插入性能,尤其是如何利用事务和执行准备来实现每秒百万条数据的写入速度。 首先,了解SQLite3的事务机制是至关重要的。在SQLite3中,事务用于确保...

    SQLite数据库资料

    优化查询不仅可以提升数据读写速度,还能降低资源消耗。 8. **VC2005 + SQLite 3.6.3 编译、测试与开发**:此文档可能详细介绍了在Visual Studio 2005环境下,如何编译特定版本(3.6.3)的SQLite源码,以及如何在...

    SQLite的查询优化[归纳].pdf

    3. LIKE操作符优化:`x LIKE 'abc%'`可以转换为`x &gt;= 'abc' AND x ,以利用索引来优化查询。在无索引的情况下,LIKE操作的效率通常低于转换后的表达式。 复合查询(如UNION、UNION ALL、EXCEPT、INTERSECT)的处理...

    Android开发环境中的SQLite性能优化.pdf

    使用索引可以提高 SQLite 的查询性能,因为 SQLite 可以快速定位到一组值,而不用扫遍全表。 使用事务和索引是 SQLite 性能优化的两个重要方法。通过使用事务和索引,我们可以提高 SQLite 的性能和资源占用,从而...

    sqlite可视化控件,仅支持查询(可模糊查询、分页),不可增删

    为了提高性能,查询可能需要优化,比如通过索引提升搜索速度,或者使用缓存机制减少不必要的数据库访问。 总的来说,DBWigdet是一个专注于查询功能的SQLite可视化控件,适合那些只需要读取数据而不涉及修改数据的...

    SQLite数据表查询

    标签"SQLite"表明了这个话题是关于SQLite数据库的,这包括但不限于创建表、插入数据、更新数据、删除数据,以及事务处理、索引优化、数据库升级等。 在名为"SQLiteQueryDemo"的压缩包文件中,很可能包含了示例代码...

    Inside SQLite(SQLite技术内幕) 原版+个人翻译版

    这可能是作者个人对SQLite的深入理解和研究,可能包含了SQLite的架构解析、存储机制、查询优化、事务处理、索引构建等核心知识点。文档可能会详细阐述SQLite如何在内存和磁盘之间管理数据,包括页缓存机制、B-Tree...

    sqliteodbc驱动sqlite3

    - 使用索引优化查询速度。 - 考虑使用批处理命令减少网络传输开销。 3. **兼容性问题**: - 检查所使用的ODBC版本与SQLiteODBC驱动是否兼容。 - 更新到最新版本的SQLiteODBC。 #### 六、总结 SQLiteODBC作为...

    SQLITE数据库 UPDATE慢

    综上所述,SQLITE数据库UPDATE慢的问题可以通过多个角度来解决,包括索引优化、事务管理、查询重构、硬件升级等。针对具体问题,分析日志,定位慢查询,然后采取针对性的措施,可以显著提升SQLite数据库的更新速度。...

    Android SQLite数据库进行查询优化的方法

    2.查询优化:使用 EXPLAIN 语句可以查看 SQLite 执行 SQL 语句的执行计划,从而优化查询性能。例如,在示例代码中,我们使用 EXPLAIN 语句来查看查询的执行计划,发现使用索引可以提高查询性能。 3.查询条件的优化...

Global site tag (gtag.js) - Google Analytics