正文

全表查询速度（什么是全表查询）

发布时间：2023-04-13 23:18:28 稿源：创意岭阅读： 59

大家好！今天让创意岭的小编来大家介绍下关于全表查询速度的问题，以下是小编对此问题的归纳整理，让我们一起来看看吧。

开始之前先推荐一个非常厉害的Ai人工智能工具，一键生成原创文章、方案、文案、工作计划、工作报告、论文、代码、作文、做题和对话答疑等等

只需要输入关键词，就能返回你想要的内容，越精准，写出的就越详细，有微信小程序端、在线网页版、PC客户端

官网：https://ai.de1919.com。

创意岭作为行业内优秀的企业，服务客户遍布全球各地，如需了解SEO相关业务请拨打电话175-8598-2043，或添加微信：1454722008

本文目录:

1、这条sql语句如何能避免全表扫描，增快查询速度，下面4个字段都建立了索引
2、Oracle查询速度优化问题
3、oracle 多表查询速度太慢。求帮我优化一下
4、如何优化sqlite的查询速度

全表查询速度（什么是全表查询）

一、这条sql语句如何能避免全表扫描，增快查询速度，下面4个字段都建立了索引

避免用not in ,not like, <>等等操作

如果是4个字段的组合索引那么要注意使用时候的字段顺序

另外提醒下索引使用不当反而会导致查询效率低下

一句两句讲不清楚的,看你自己的经验了

二、Oracle查询速度优化问题

1. 选用适合的ORACLE优化器

ORACLE的优化器共有3种:

a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性)

设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQL句级或是会话(session)级对其进行覆盖.

为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性.

如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关. 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器.

在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器.

2. 访问Table的方式

ORACLE 采用两种访问表中记录的方式:

a. 全表扫描

全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描.

b. 通过ROWID访问表

你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息..ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.

3. 共享SQL语句

为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它

和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的

执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用.

可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询.

数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.

当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句.

这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等).

4.选择最有效率的表名顺序(只在基于规则的优化器中有效)

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

5. WHERE子句中的连接顺序．

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

6. SELECT子句中避免使用 ‘ * ‘

当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*' 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.

7. 减少访问数据库的次数

当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.

注意: 在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200.

8. 使用DECODE函数来减少处理时间

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.

9. 整合简单,无关联的数据库访问

如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)

10. 删除重复记录

最高效的删除重复记录方法 ( 因为使用了ROWID)

DELETE FROM EMP E

WHERE E.ROWID > (SELECT MIN(X.ROWID)

FROM EMP X

WHERE X.EMP_NO = E.EMP_NO);

11. 用EXISTS替代IN

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.

12. 用NOT EXISTS替代NOT IN

在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS

三、oracle 多表查询速度太慢。求帮我优化一下

你写表关联条件的时候使用了 OR

那就是说任何一个条件满足就会被检索出来，所以产生大量笛卡尔集（就是重复数据）

那么5表查询就会产生（tb_jbjxx的数据件数* tb_ajjbxx的数据件数*tb_sarjbxx的数据件数*tb_xyr的数据件数*tb_chengbaoxinxi的数据件数)件数据。

所以应该把表关联的条件修改为 AND

修改后SQL文如下：

select jbjxxid,anjianmingcheng,JBJXXSLBH,SARJBXXID,SARMC,JBDWMC

from tb_jbjxx,tb_ajjbxx,tb_sarjbxx,tb_xyr,tb_chengbaoxinxi

where tb_jbjxx.JBJXXID=tb_ajjbxx.JBJXXSLBH

AND tb_ajjbxx.QTRYXXSLBH=tb_sarjbxx.SARJBXXID

AND tb_sarjbxx.XXLYYWID=tb_xyr.XYRLYYWID

AND tb_chengbaoxinxi.XXLYYWID=tb_ajjbxx.XXLYYWID

AND tb_jbjxx. like '东城建国%';

如果你想要的是满足任何一个条件就要检索出来的话，那么一定要有一个主表，

就是查询主表的所有信息，再用其他表的字段和主表关联，有数据显示，没有数据不显示

分析你的SQL文，我推测tb_ajjbxx表是主表的概率比较高，那么SQL文的修改如下:

select jbjxxid,anjianmingcheng,JBJXXSLBH,SARJBXXID,SARMC,JBDWMC

from tb_jbjxx,tb_ajjbxx,tb_sarjbxx,tb_xyr,tb_chengbaoxinxi

where tb_ajjbxx.JBJXXSLBH = tb_jbjxx.JBJXXID(+)

AND tb_ajjbxx.QTRYXXSLBH = tb_sarjbxx.SARJBXXID(+)

AND tb_ajjbxx.XXLYYWID = tb_chengbaoxinxi.XXLYYWID(+)

AND tb_sarjbxx.XXLYYWID = tb_xyr.XYRLYYWID

AND tb_jbjxx. like '东城建国%';

--1）where条件的顺序修改了，为了提高效率

--2）where条件中=号左右内容部分交换了，为了增强可读性

--3）增加外关联符号（+）

最后，关于多表关联，表关联条件必须使用"AND"关键字，否则无效。

用你做成的SQL文检索，哪怕5个表都只有10条数据也会检索出1万件结果，如果各个表数据超过100件，你的检索结果集会是天文数字。。。如果我的回答没有直接解决问题，可以追问。

四、如何优化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

nextA

AeqB:

outA, nextA

nextA

outA, nextA

AgtB:

outB, nextB

nextB

EofA:

outB, nextB

halt

EofB:

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) 多列索引