您可以捐助,支持我们的公益事业。

1元 10元 50元





认证码:  验证码,看不清楚?请点击刷新验证码 必填



  求知 文章 文库 Lib 视频 iPerson 课程 认证 咨询 工具 讲座 Modeler   Code  
会员   
 
   
 
 
     
   
 订阅
  捐助
高级SQL优化(二)
 
来源:私塾在线 发布于 2016-9-7
  1873  次浏览      13
 

充分利用索引

索引的限制

1. 索引对不等号和NOT的限制

如果WHERE条件中出现!=或者<>,即使该列建立了索引,则该索引也不会被使用;如果不恰当的使用了NOT,则索引也不会被使用。

Oracle 10g起,在基于CBO的优化器模式下Oralce会进行自动优化,但在基于RBO(基于规则)的优化器模式下,依然保持此规则。

1. 索引对不等号和NOT的限制

RBO模式下,执行计划如下:

1. 索引对不等号和NOT的限制

RBO模式下,执行情况如下:

此时使用变通写法的耗费为:0.407/2.187=1.60%!

1. 索引对不等号和NOT的限制

CBO模式下,执行情况如下:

此时使用变通写法的耗费节约不到0.03秒,但依然更优,故此推荐此种变通写法,再看此时使用NOT:

1. 索引对不等号和NOT的限制

CBO模式下,在JYJE列的索引上使用NOT:

为使用<>的:0.156/0.329=47.42%!为变通写法的使用0.156/0.297=52.53%! 因 此这种写法最优!

1. 索引对不等号和NOT的限制

一般,WHERE条件中,如果索引列是字符列,使用NOT往往也不会使用索引:

结论:如果索引列是数字,则对于不等号的处理可以变更为NOT的方式或者(大于 OR 小于)的方式① ;对于确实无法不使用不等号的方式,可以使用默认值② ;如果可以建立位图索引则使用位图索引③ ;否则可以考虑使用分区等方法进行优化④ ,具体是情况而定。

2. 索引对IS NULL的限制

一般来说,如果WHERE子句基于的列是可空的列,且其建立了索引,如果使用了IS NULL,由于NULL的列本身不包含在索引中,因此无法利用索引。

所以一般对要建立索引的列不要设置为可空,如果确实含有空值,建议使用默认值代替空值,具体参见前面章节“SQL优化技巧”部分的“使用默认值”。

3. 索引对函数的限制

基于索引IDX_BIGTAB_OBJECTNAME,执行情况如下:

执行计划情况如下:

这是因为该索引是常规b-tree索引,对该列在WHERE子句中使用了函数,则不能使用索引。因此,对在WHERE子句中经常要使用函数时,应该建立基于函数的索引,且 只有当查询语句包含该函数或者表达式时,基于函数的索引才会被调 用。详情请参见索引部分的理论讲解!

创建并使用函数索引:

创建并使用函数索引:

此时使用基于函数的索引效率是原来的2.782/0.188=14.78!呗!

4. 索引对不匹配数据类型的限制

先看执行情况:

不匹配的类型执行的时间是匹配的类型的 2.187/0.266=8.2 倍!

再看执行计划:

原因分析:

因为ACCOUNT_TRADE表的字段YKKH是CHAR,因此在对其指定的值是数字时,Oracle虽然能隐式的执行数字和字符的转换,但不会调用其索引。而当对其指定

是字符时,则不存在此问题,索引可以调用。

注意:因为数据类型的不匹配和Oracle对数据类型的隐式转换,此种类型的低效代码在任何项目中均可能因为大意而存在,因此建议开发人员和管理人员要定期抽查相应的代码,以杜绝此类低效代码!

索引类型总结

访问路径

1. 全表扫描

全表扫描(FULL TABLE SCANS)时所有行、所有数据块均会被读到,是 效率最 低的一种,一般会在表 缺少索引、 读取大量数据、 访问小表或 高并发时发生。

2. ROWID扫描

ROWID扫描(ROWID SCANS)是通过ROWID中数据文件和块位置访问数据行。一般作为访问索引后的第二步,如果访问的列全部包含在索引中,则不会执行ROWID扫描。

作为索引访问后的第二步:

访问的列全部在索引中不再执行ROWID扫描

3. 索引扫描

索引扫描(INDEX SCANS)包含全索引扫描(full index scan、FIS)、快速全索引扫描(fast full index scan、FFIS)、索引范围扫描(index range scan)、索引唯一扫描 (index unique scan)、索引跳跃式扫描 (index skip scan)、位图索引扫描(bitmap index scan), 其中前5种在本系列课程的索引章 节部分已经讲解了其理论和示例。位图索引示例如下:

3. 索引扫描

为了 加深巩固前面的知识,本处对前五种索引扫描复习总结如下:

(1).全索引扫描

逐一读取索引中的所有块,由于索引中数据已按索引键排序,因此会忽略掉排序,可能发生的情况如下:

A. ORDER BY中的列全部在某个索引中

全部在某个索引中:

(1).全索引扫描

B. ORDER BY中列的顺序满足索引中前导列的顺序时

下面分别是满足和不满足前导列顺序时:

C. 使用GROUP BY且该子句中的列在索引中时

(2).快速全索引扫描

只扫描索引中的数据,不会扫描表中的数据;由于索引中数据未按索引键排序,因此不能忽略掉排序。当同时满足下列条件时,Oracle用FFIS替代FIS或FTS:

1.查询的所有列均包含在索引中

2.索引中的列至少一个具有not null约束(10g开始的,原低版本的系统中为查询的列中不包含任何null值)

全部列均在索引中:

有列不在索引中:

删除该索引,创建新索引,两个列均为可空:

此时即使全部列在该索引中, 也不会发生FFIS

索引范围扫描是访问选择性数据最常用的扫描方式;按顺序的对某个索引进行扫描,返回数据是升序排列的,可以使用唯一索引和非唯一索引;如果对索引列使用ORDER BY/GROUP BY则可省略排序。

下列情形中会发生索引范围扫描:

A.在唯一索引上使用范围操作符(>、<、>=、<=、<>、BETWEEN)

B.在组合索引上使用部分列进行查询,导致查出多行

示例请参考本系列课程的索引章节部分

(4).索引唯一扫描

当使用主键或唯一索引时发生。 示例请参考本系列课程的索引章节部分。

(5).索引跳跃扫描

复合索引中前导列的取值是枚举的从而可以分拆为多个子索引,并且查询条件中不含前导列时。示例如下:

create table customers as select * from sh.customers;

CREATE INDEX customers_gender_email

ON customers (cust_gender, cust_email);

(5).索引跳跃扫描

没进行表分析前:

进行表分后:

analyze table customers compute statistics;

何时需要索引

一般地,对于从表的总行中的大部分查询只查询不到10%数据(有的称为2%-4%)的表,可以考虑创建索引。一般考虑的索引的原则包括:

l对于经常以查询关键字为基础的表,并且该表中的数据行是均匀分布的

l以查询关键字为基础,表中的数据行随机排序

l表中包含的列数相对比较少(仅仅是相对,需要根据实际情况确定)

l表中的大多数查询都包含相对简单的WHERE子句

l表的记录数比较少的,不建议使用索引,如数据不超过1万行的表不要建立索

为索引选择列和表达式

一般遵循的原则包括:

l经常在WHERE子句中使用的列

lSQL语句中经常用于表之间连接的列

l重复性少(可选择性高)的关键字,如主键

l不宜将经常UPDATE的列作为索引列

l不宜将经常在WHERE子句中使用,但与函数或操作符相结合的列作为索引列

l对于取值较少的列,应考虑建立位图索引,而不应该采用B树索引

l如果经常访问的列上要使用函数,应使用基于函数的索引

本处举例说明取值较少的列使用bitmap索引和b-tree的对比分析,B-tree时:

bitmap时:

使用复合索引

多个列联合起来组成的索引称为复合索引、或联合索引或者组合索引,往往联合索引比单个索引具有更好的性能。创建联合索引一般遵循的原则包括:

l经常在WHERE子句中使用的列且这些列之间使用AND连接

l查询条件可能包括n个列的AND关系,而大多数情况下使用m个列是(n>m),应该考虑复合索引,且n个列为前导列

l某几个列联合起来能够组成唯一索引,应坚决建立联合唯一索引

l复合索引中,建议至少一个不能为null,且如果可能尽量将只是存在null的列对其null值采用其它默认值代替

本处举例说明Where中包含AND时使用多个索引性能低于联合索引的示例,使用多个索引时:

本处举例说明Where中包含AND是使用多个索引性能低于联合索引的示例,使用复合索引时:

结论:

可见,此时复合索引是多个索引的效率的 四倍以上!

监视索引的使用情况

u正确合适的索引是查询优化性能的首选

u索引是表的索引列排序后的小型化拷贝,会增加存储开销,因此会带来Insert、Update、Delete的额外开销

u一个表可以有一个索引,也可以有多个索引,往往过多的索引或不恰当的索引带来的负面性能更多

u表索引的设计初衷,往往在40%甚至更高的情况下与最终的实际使用情况不符合,此举视设计人员对业务和Oracle的理解不同而不同

u监视索引的实际使用情况,尤其在表具有多个索引的情况下,就显得尤为重要,对经常不使用的索引采用合并为复合索引或删除是优化的工作之一

示例如下:

1.创建索引

2.启用所以监视

3.执行SQL

4.查看索引使用情况

我们可以根据一个持续时间的对索引的监控结果决定如何合并及删除不恰当的索引。

5.停止监视索引

   
1873 次浏览       13
相关文章

基于EA的数据库建模
数据流建模(EA指南)
“数据湖”:概念、特征、架构与案例
在线商城数据库系统设计 思路+效果
 
相关文档

Greenplum数据库基础培训
MySQL5.1性能优化方案
某电商数据中台架构实践
MySQL高扩展架构设计
相关课程

数据治理、数据架构及数据标准
MongoDB实战课程
并发、大容量、高性能数据库设计与优化
PostgreSQL数据库实战培训
最新课程计划
信息架构建模(基于UML+EA)3-21[北京]
软件架构设计师 3-21[北京]
图数据库与知识图谱 3-25[北京]
业务架构设计 4-11[北京]
SysML和EA系统设计与建模 4-22[北京]
DoDAF规范、模型与实例 5-23[北京]

MySQL索引背后的数据结构
MySQL性能调优与架构设计
SQL Server数据库备份与恢复
让数据库飞起来 10大DB2优化
oracle的临时表空间写满磁盘
数据库的跨平台设计
更多...   

并发、大容量、高性能数据库
高级数据库架构设计师
Hadoop原理与实践
Oracle 数据仓库
数据仓库和数据挖掘
Oracle数据库开发与管理

GE 区块链技术与实现培训
航天科工某子公司 Nodejs高级应用开发
中盛益华 卓越管理者必须具备的五项能力
某信息技术公司 Python培训
某博彩IT系统厂商 易用性测试与评估
中国邮储银行 测试成熟度模型集成(TMMI)
中物院 产品经理与产品管理
更多...