SQL 查询优化之索引扫描与全表扫描漫谈

来源:互联网
责任编辑:王嘉善
字体:

全表扫描的意思就是要把表中所有数据过一遍才能显示数据结果,索引扫描就是索引,只需要扫描一部分数据就可

SQL 查询优化之索引扫描与全表扫描漫谈

1.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全防采集。

阅读对象: 初级用户

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建防采集。

索引扫描---也许你有过这样的经历, 某日应邀第一次去朋友家, 朋友告诉你是开心家园132栋1102, 之后你就去了开心家园, 接下来你要从该小区中寻找132栋, 但是小区很大, 你可能会凭经验楼栋号是连续分布的, 依照进开心家园大门的几栋楼栋号的排列顺序往前找, 也许运气好, 你很容易就找到了, 但是有的小区楼栋号分布并不连续, 这个时候在你觉得下一栋就是你要找的楼栋号时, 走近一看发现却不是,这时你不得不再次花费时间来寻找. 那如何快速的找到你的楼栋号所处的位置呢? 物业已经为你制作了一个楼栋号分布图竖在大门口, 这时你只需要查看这个地图就能轻易的找到你要的楼栋号在该小区的位置, 接下来就直接奔往那个位置即可. 这种情况运用到数据库查询中, 我们可以发现, 整个小区相当于一个数据表, 各个楼栋是这个数据表中的数据, 这里我们使用到的楼栋号分布图就相当于这个数据表的索引, 因此当我们查找数据表中指定某一个数据时, 使用索引查找到数据所在的位置(ROWID), 然后直接根据位置就能够得到你要的数据了. 这就是索引在查询中加快查询速度的使用.

关键是,要看你的where条件列、数据统计情况、索引情况。dbms会根据上述评估使用哪个索引防采集。

全表扫描---那是不是使用索引查找数据就一定快了呢? 某日, 你被指派为水表抄表员, 你需要对该小区200栋楼的居民用户抄水表读数, 你知道一共从1到200号, 但我想你一定不会根据楼栋号分布图先查看第1栋的位置, 然后跑到第1栋的楼号去抄完表, 再根据第2号楼的位置到第2个楼去抄表, 你一定是根据小区的分布, 从第一排的楼栋的第一个楼号开始连续的从一栋抄完再到紧挨着的那一栋(楼栋号可能并不连续),第一排结束到第二排 这样顺序走完一遍即可. 这对于SQL查询就相当于全表扫描, 像这种查询数据表中全部(或者说大部分)数据的时候, 走全表扫描比走索引更加有效.

not null, DepartName NVARCHAR2(40) 防采集。

扩展阅读,根据您访问的内容系统为您准备了以下扩展内容,希望对您有帮助。

数据库中全表扫描和索引扫描的区别是?

全表扫描的意思就是要把表中所有数据过一遍才能显示数据结果,索引扫描就是索引,只需要扫描一部分数据就可以得到结果,

打个比方吧,在新华字典中,如果没有拼音或笔画索引,当我们查找“做”这个字就要从字典第一页一次往后查,一直插到Z开头的部分才能找到,即使找到也不确定后面是不是还有(假定字典是无序状态的),因此还得往后找,知道正本字典翻完,才确定“哦,原来刚才找到的那个记录就是想要的结果了”。索引扫描的意思就是我们预先知道“做”这个字在拼音的Z区域,然后根据前面目录查看"zuo"这个拼音在那一页,然后直接翻到那一页就能找到我们要的结果了,这样就能大大减少查询的时间。

如何查找Oracle中存在全表扫描的SQL语句

1. 对返回的行无任何限定条件,即没有where 子句

2. 未对数据表与任何索引主列相对应的行限定条件

例如:在City-State-Zip列创建了三列复合索引,那么仅对State列限定条件不能使用这个索引,因为State不是索引的主列。

3. 对索引的主列有限定条件,但是在条件表达式里使用以下表达式则会使索引失效,造成全表扫描:

(1)where子句中对字段进行函数、表达式操作,这将导致引擎放弃使用索引而进行全表扫描,

Demo:

where upper(city)='TokYo' 或 City || 'X' like 'TOKYO%',

select id from t where num/2=100 应改为: select id from t where num=100*2

select * from emp where to_char(hire_date,'yyyymmdd')='20080411' (不使用)

select * from emp where hire_date = to_char('20080411','yyyymmdd') (使用)

(2)查询字段is null时索引失效,引起全表扫描。

where City is null 或 ,where City is not null,

解决方法:SQL语法中使用NULL会有很多麻烦,最好索引列都是NOT NULL的;对于is null,可以建立组合索引,nvl(字段,0),对表和索引analyse后,is null查询时可以重新启用索引查找,但是效率还不是值得肯定;is not null 时永远不会使用索引。一般数据量大的表不要用is null查询。

select id from t where num is null

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

select id from t where num=0

(3)查询条件中使用了不等于操作符(<>、!=)会*索引、引起全表扫描

Where city!='TOKYO'.

解决方法:通过把不等于操作符改成or,可以使用索引,避免全表扫描。例如,把column<>’aaa’,改成column<’aaa’ or column>’aaa’,就可以使用索引了。

(4)对索引的主列有限定条件,但是条件使用like操作以及值以‘%’开始或者值是一个赋值变量。例如:

where City like '%YOK%'

where City like: City_bind_Variable xl_rao

select * from emp where name like '%A' (不使用索引)

select * from emp where name like 'A%' (使用索引)

解决办法:首先尽量避免模糊查询,如果因为业务需要一定要使用模糊查询,则至少保证不要使用全模糊查询,对于右模糊查询,即like ‘…%’,是会使用索引的;左模糊like ‘%...’无法直接使用索引,但可以利用reverse + function index 的形式,变化成 like ‘…%’;全模糊是无法优化的,一定要的话考虑用搜索引擎。出于降低数据库服务器的负载考虑,尽可能地减少数据库模糊查询。

4. or语句使用不当会引起全表扫描

原因:where子句中比较的两个条件,一个有索引,一个没索引,使用or则会引起全表扫描。例如:where A=:1 or B=:2,A上有索引,B上没索引,则比较B=:2时会重新开始全表扫描

5.模糊查询效率很低:

原因:like本身效率就比较低,应该尽量避免查询条件使用like;对于like‘%...%’(全模糊)这样的条件,是无法使用索引的,全表扫描自然效率很低;另外,由于匹配算法的关系,模糊查询的字段长度越大,模糊查询效率越低。

解决办法:首先尽量避免模糊查询,如果因为业务需要一定要使用模糊查询,则至少保证不要使用全模糊查询,对于右模糊查询,即like‘…%’,是会使用索引的;左模糊like

‘%...’无法直接使用索引,但可以利用reverse + function index的形式,变化成like‘…%’;全模糊是无法优化的,一定要的话考虑用搜索引擎。出于降低数据库服务器的负载考虑,尽可能地减少数据库模糊查询。

6.查询条件中含有is null的select语句执行慢

原因:Oracle 中,查询字段is null时单索引失效,引起全表扫描。

解决方法:SQL语法中使用NULL会有很多麻烦,最好索引列都是NOT NULL的;对于is null,可以建立组合索引,nvl(字段,0),对表和索引analyse后,is null查询时可以重新启用索引查找,但是效率还不是值得肯定;is not null时永远不会使用索引。一般数据量大的表不要用is null查询。

7.查询条件中使用了不等于操作符(<>、!=)的select语句执行慢

原因:SQL中,不等于操作符会*索引,引起全表扫描,即使比较的字段上有索引

 解决方法:通过把不等于操作符改成or,可以使用索引,避免全表扫描。例如,把column<>’aaa’,改成column<’aaa’or column>’aaa’,就可以使用索引了。

8.使用组合索引,如果查询条件中没有前导列,那么索引不起作用,会引起全表扫描;但是从Oracle9i开始,引入了索引跳跃式扫描的特性,可以允许优化器使用组合索引,即便索引的前导列没有出现在WHERE子句中。例如:create index skip1 on emp5(job,empno); 全索引扫描select count(*) from emp5 where empno=7900; 索引跳跃式扫描select /*+ index(emp5 skip1)*/ count(*) from emp5 where empno=7900;前一种是全表扫描,后一种则会使用组合索引。

9. or语句使用不当会引起全表扫描

原因:where子句中比较的两个条件,一个有索引,一个没索引,使用or则会引起全表扫描。例如:where A=:1 or B=:2,A上有索引,B上没索引,则比较B=:2时会重新开始全表扫描。

10.组合索引,排序时应按照组合索引中各列的顺序进行排序,即使索引中只有一个列是要排序的,否则排序性能会比较差。例如:create index skip1 on emp5(job,empno,date); select job,empno from emp5 where job=’manager’and empno=’10’order by job,empno,date desc;实际上只是查询出符合job=’manager’and empno=’10’条件的记录并按date降序排列,但是写成order by date desc性能较差。

11.Update语句,如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志。

 12.对于多张大数据量的表JOIN,要先分页再JOIN,否则逻辑读会很高,性能很差。

 13.select count(*) from table;这样不带任何条件的count会引起全表扫描,并且没有任何业务意义,是一定要杜绝的。

14.sql的where条件要绑定变量,比如where column=:1,不要写成where column=‘aaa’,这样会导致每次执行时都会重新分析,浪费CPU和内存资源。

15.不要使用in操作符,这样数据库会进行全表扫描,

推荐方案:在业务密集的SQL当中尽量不采用IN操作符

16.not in 使用not in也不会走索引

推荐方案:用not exists或者(外联结+判断为空)来代替

17.> 及 < 操作符(大于或小于操作符)

大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,如一个表有100万记录,一个数值型字段 A,30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了,因为A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。

18.UNION操作符

UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION。如:

select * from gc_dfys

union

select * from ls_jg_dfys

这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。

推荐方案:采用UNION ALL操作符替代UNION,因为UNION ALL操作只是简单的将两个结果合并后就返回。

19.WHERE后面的条件顺序影响

WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响,如

Select * from zl_yhjbqk where dy_dj = '1K以下' and xh_bz=1

Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1K以下'

以上两个SQL中dy_dj及xh_bz两个字段都没进行索引,所以执行的时候都是全表扫描,第一条SQL的dy_dj = '1KV以下'条件在记录集内比率为99%,而xh_bz=1的比率只为0.5%,在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较,而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较,以此可以得出第二条SQL的CPU占用率明显比第一条低。

20.查询表顺序的影响

在FROM后面的表中的列表顺序会对SQL执行性能影响,在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接,由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。(注:如果对表进行了统计分析,ORACLE会自动先进小表的链接,再进行大表的链接)

索引全扫描什么时候优于全表扫描?

这个问题大致可以分为两种情况:

1 只使用索引就能够返回查询结果的查询,比如聚合查询中的函数count,max,min。

2 查询结果的记录数量小于表中记录一定比例的时候。这个主要是由于索引扫描后要利用索引中的指针去逐一访问记录,假设每个记录都使用索引访问,则读取磁盘的次数是查询包含的记录数T,而如果表扫描则读取磁盘的次数是存储记录的块数B,如果T>B 的话索引就没有优势了。对于大多数数据库来说,这个比例是10%(oracle,postgresql等),即先对结果数量估算,如果小于这个比例用索引,大于的话即直接表扫描。

www.powayart.com true http://www.powayart.com/exploit/160/1601294.html report 66129 SQL查询优化之索引扫描与全表扫描漫谈,SQL查询优化之索引扫描与全表扫描漫谈阅读对象:初级用户索引扫描---也许你有过这样的经历,某日应邀第一次去朋友家,朋友告诉你是开心家园132栋1102,之后你就去了开心家园,接下来你要从该小区中寻找132栋,但是小区很大,你可能会凭经验楼栋号是连续...
娱乐时尚
历史文化
真视界
旅游美食
精彩图文
我爱我车
母婴健康
关于本站 | 广告服务 | 手机版 | 商务合作 | 免责申明 | 招聘信息 | 联系我们
Copyright © 2004-2018 book1234.com All Rights Reserved. 布客网 版权所有
京ICP备10044368号-1 京公网安备11010802011102号
教育考试: 学历财经建筑 医药公考资格外语电脑作文招聘中小学留学 文档 移民 文库专栏23问答中心z资讯z资讯1资讯涨资讯涨资讯1资讯问答图书馆知识IT编程数码信息解决方案信息中心IT科技问答新闻中心软件教室设计大全网络相关英语学习开发编程考试中心参考范文管理文库营销中心站长之家IT信息中心商学院数码大全硬件DIY企业服务网吧在线百科硬件知识手机平板汽车游戏家电精彩摄影现代家居IT女人经验健康养生猎奇创业攻略教育学习历史时尚潮流最近更新涨知识
广东11选5 广东11选5 传奇私服_中国 传奇私服考查询 最新的传奇私服发布网 北京快3 江苏快3 北京快3 吉林快三 搜狐彩票网 天津快乐十分开奖结果 广西快3 搜狐彩票网 贵州快三 获嘉县| 渭源县| 光泽县| 河南省| 洪湖市| 泽州县| 铜川市| 东城区| 汕头市| 会宁县| 泗阳县| 沙雅县| 清徐县| 土默特左旗| 宾川县| 天气| 微山县| 汉川市| 夏津县| 通许县| 大余县| 江阴市| 迁安市| 攀枝花市| 天全县| 岳池县| 惠安县| 盐池县| 湖州市| 五台县| 中牟县| 隆子县| 东莞市| 民和| 铁岭县| 廉江市| 光泽县| 瑞昌市|