Oracle 索引的使用规则与性能调优.docx
- 文档编号:6451191
- 上传时间:2023-01-06
- 格式:DOCX
- 页数:9
- 大小:24.50KB
Oracle 索引的使用规则与性能调优.docx
《Oracle 索引的使用规则与性能调优.docx》由会员分享,可在线阅读,更多相关《Oracle 索引的使用规则与性能调优.docx(9页珍藏版)》请在冰豆网上搜索。
Oracle索引的使用规则与性能调优
Oracle索引的使用规则与性能调优
索引分类
逻辑上:
Unique唯一索引
物理上:
B-tree:
Normal正常型B树
Bitmap位图索引
索引结构:
B-tree:
适合与大量的增、删、改(OLTP);
不能用包含OR操作符的查询;
适合高基数的列(唯一值多)
典型的树状结构;
每个结点都是数据块;
大多都是物理上一层、两层或三层不定,逻辑上三层;
叶子块数据是排序的,从左向右递增;
在分支块和根块中放的是索引的范围;
Bitmap:
适合与决策支持系统;
做UPDATE代价非常高;
非常适合OR操作符的查询;
基数比较少的时候才能建位图索引;
树型结构:
索引头
开始ROWID,结束ROWID(先列出索引的最大范围)
BITMAP
每一个BIT对应着一个ROWID,它的值是1还是0,如果是1,表示着BIT对应的ROWID有值;
B*tree索引的话通常在访问小数据量的情况下比较适用,比如你访问不超过表中数据的5%,当然这只是个相对的比率,适用于一般的情况。
bitmap的话在数据仓库中使用较多,用于低基数列,比如性别之类重复值很多的字段,基数越小越好。
索引就好象一本字典的目录。
凭借字典的目录,我们可以非常迅速的找到我们所需要的条目。
数据库也是如此。
凭借Oracle数据库的索引,相关语句可以迅速的定位记录的位置,而不必去定位整个表。
虽然说,在表中是否创建索引,不会影响到Oracle数据库的使用,也不会影响数据库语句的使用。
这就好像即使字典没有目录的话,用户仍然可以使用它一样。
可是,若字典没有目录,那么可想而知,用户要查某个条目的话,其不得不翻遍整本字典。
数据库也是如此。
若没有建立相关索引的话,则数据库在查询记录的时候,不得不去查询整个表。
当表中的记录比较多的时候,其查询效率就会很低。
所以,合适的索引,是提高数据库运行效率的一个很好的工具。
不过,并不是说表上的索引越多越好。
过之而不及。
故在数据库设计过程中,还是需要为表选择一些合适的索引。
宁缺勿滥,这是建立索引时的一个遵循标准。
在理论上,虽然一个表可以设置无限的索引。
但是,数据库管理员需要知道,表中的索引越多,维护索引所需要的开销也就越大。
每当数据表中记录有增加、删除、更新变化的时候,数据库系统都需要对所有索引进行更新。
故数据库表中的索引绝对不是多多益善。
具体来说,在索引建立上,笔者对大家有如下建议。
建议一:
在基数小的字段上要善于使用位图索引。
基数是位图索引中的一个基本的定义,它是指数据库表中某个字段内容中不重复的数值。
如在员工信息表中的性别字段,一般就只有男跟女两个值,所以,其基数为2;婚姻状况字段的话,则其只有已婚、未婚、离婚三种状态,其基数就为3;民族一览内也是只有有限的几个值。
对于要查询基数小的字段,如现在用户想查找所有婚姻状况为“已婚”的“女性”时,利用位图索引可以提高查询的效率。
这主要是因为标准索引是通过在索引中保存排序过的索引列以及对应的ROWID来实现的。
若我们在基数小的列上建立标准索引的话,则其会返回大量的记录。
而当我们在创建位图索引的时候,在Oracle会对整个表进行扫描,并且会为索引列的每个取值建立一个位图。
若内容相同,则在位图上会以一个相同的数字表示。
此时,若这个字段的基数比较小的话,则若需要实现对整个字段的查询的话,效率就会非常的高。
因为此时,数据库只要位图中数字相同的内容找出来即可。
除了在数据表某列基数比较小的情况下,采用位图索引外,我们往往在一些特殊的情况下,也会建议采用位图索引。
最常见的情况是,在Where限制条件中,若我们多次采用AND或者OR条件时,也建议采用位图索引。
因为当一个查询饮用了一些部署了位图索引的列的时候,这些位图可以很方便的与AND或者Or运算符操作结合以快速的找出用户所需要的记录。
但是,这里要注意,不是在条件语句中包含运算符的时候,采用位图索引都能够提供比较高的效率。
一般来说,只有AND或者OR运算符的时候,位图索引才会比较具有优势。
若此时用户采用大于号或者不等于号作为条件语句中的限制条件的时候,则往往采用标准索引具有更大的优势。
所以,笔者在数据库设置中,一般只有在三种情况下才采用位图索引。
一是列的基数比较小,而有可能需要根据这些字段的内容查找相关的记录;二是在条件语句中,用到了AND或者OR运算符的时候。
除了这两种情况外,最好能够采用其他适合的索引。
第三种情况是,需要用到NULL作为查询的限制条件。
因为标准查询一般情况下,会忽略所有的NULL值列。
也就是说,若需要查询“所有没有身份证号码”的员工的信息的时候,标准索引并不能够起到加速查询速度的作用。
此时,就需要采用位图索引。
因为位图索引会记录相关的NULL值列信息。
建议二:
创建索引的一些限制条件。
并不说,表或者列建立的索引越多越好。
相反,索引建的越多,有时会反而会影响数据库运行的整体性能。
所以,在建立索引的时候,仍然会有一些限制条件。
一是不要对一些记录内容比较少的表建立索引。
在一个应用系统设计的时候,如设计一个ERP系统的数据库,其虽然有几千张表。
但是,并不是每张表都有大量记录的。
相反,其中有近一半左右的数据表,可能其存储的数据不会超过百条。
如员工登陆帐户密码表、企业部门信息表等等。
对于这些记录内容比较少的表,我们建立最好不要为其建立索引。
无论是表上的,还是字段上,都不要建立索引。
二是若表中的内容比较大,但是,这个表基本上不怎么查询的时候,则只需要在表上建立索引即可;而不需要在字段上建立索引。
如现在在ERP系统中,有一张表是“AD_Table”。
其存储的是这个数据库中相关表的信息。
这张表只有在数据库设计的时候才会用到。
故这张表中的记录虽然比较多,但是由于用户用的比较少,所以,一般没有必要为这张表建立列级别上的索引。
而直接用表索引来代替。
三是在一些NULL字段上,要根据实际情况来判断是否要建立索引。
如现在有一张人事档案的表格,其上面有两个字段,分别为“身份证号码”与“地区”。
有时会为了某个原因,企业需要所有员工都在系统中登记他们的身份证号码,以方便他们办工资卡、社会保险等等。
所以人事管理可能需要经常的查询系统,看看有没有没有身份证号码的员工信息。
此时,就需要利用条件“ISNULL”来查询我们所需要的记录。
故为了提高查询效率,若某个记录可能为空,并且经常需要以NULL为条件进行查询的时候,则最好给这个字段添加一个索引,并且最好建立位图索引。
相反,若虽然可能会以NULL这个条件作为查询的限制语句,但是,用的不是很多的时候,则就没有必要为其建立索引。
建议三:
多表连接查询的索引设计。
如现在有一个人事管理系统。
人事经理想知道员工的社保缴纳情况。
他需要知道员工的姓名、职务、户籍性质(农民户口跟居民户口费用不一样)、缴纳的情况等等。
但是,这些信息包含在不同的表中。
因为为了提高数据库的性能,在表中存储的可能只是某些序号,而不是具体的内容。
如在社保表中,存储的是员工对应的编号,而不是员工的名字。
所以,要得到这份报表的话,就可能需要关联员工基本信息表、公司组织结构表等表格,才能够查询到用户所需要的内容。
为此,就需要利用Join语句,把这些表格关联起来。
为了提高数据库的查询效率,这些用来关联的字段,最好能够建立索引。
这可以显著的提高查询的速度。
建议四:
在表的更新速度与查询速度之间寻求一个平衡点。
众所周知,索引本身并不影响数据库的使用,其主要是为了提高数据库的查询效率。
但是,由于当数据库的表中的数据更新的时候,包括记录的增加、删除、更改等等,都会对虽有的索引进行更新。
很明显,索引虽然可以提高查询速度。
但是,也会对一些表的更新操作产生不良的影响。
当在表中建立的索引越多,这个不利影响也会越大。
故数据库管理员在设置索引的时候,还需要注意,在这两个之间需要一个均衡点。
按照一般的理论来说,当某个表多数用来查询、更新相对来说比较上的话,则要多多采用索引。
相反,当某个表记录更新居主导,查询相对来说比较少的话,则不要建立太多的索引,避免对更新的速度差生不利影响。
在实际工作中,若某个表频繁的被视图所调用的话,则最好就好设置比较多的索引了。
Oracle索引使用规则
首先,我们要确定数据库运行在何种优化模式下,相应的参数是:
optimizer_mode。
可在svrmgrl或者pl\sql命令模式中运行“showparameteroptimizer_mode"来查看。
ORACLEV7以来缺省的设置应是"choose",即如果对已分析的表查询的话选择CBO,否则选择RBO。
如果该参数设为“rule”,则不论表是否分析过,一概选用RBO,除非在语句中用hint强制。
查找原因的步骤
首先,我们要确定数据库运行在何种优化模式下,相应的参数是:
optimizer_mode。
可在svrmgrl中运行“showparameteroptimizer_mode"来查看。
ORACLEV7以来缺省的设置应是"choose",即如果对已分析的表查询的话选择CBO,否则选择RBO。
如果该参数设为“rule”,则不论表是否分析过,一概选用RBO,除非在语句中用hint强制。
其次,检查被索引的列或组合索引的首列是否出现在PL/SQL语句的WHERE子句中,这是“执行计划”能用到相关索引的必要条件。
第三,看采用了哪种类型的连接方式。
ORACLE的共有SortMergeJoin(SMJ)、HashJoin(HJ)和NestedLoopJoin(NL)。
在两张表连接,且内表的目标列上建有索引时,只有NestedLoop才能有效地利用到该索引。
SMJ即使相关列上建有索引,最多只能因索引的存在,避免数据排序过程。
HJ由于须做HASH运算,索引的存在对数据查询速度几乎没有影响。
第四,看连接顺序是否允许使用相关索引。
假设表emp的deptno列上有索引,表dept的列deptno上无索引,WHERE语句有emp.deptno=dept.deptno条件。
在做NL连接时,emp做为外表,先被访问,由于连接机制原因,外表的数据访问方式是全表扫描,emp.deptno上的索引显然是用不上,最多在其上做索引全扫描或索引快速全扫描。
第五,是否用到系统数据字典表或视图。
由于系统数据字典表都未被分析过,可能导致极差的“执行计划”。
但是不要擅自对数据字典表做分析,否则可能导致死锁,或系统性能下降。
第六,索引列是否函数的参数。
如是,索引在查询时用不上。
第七,是否存在潜在的数据类型转换。
如将字符型数据与数值型数据比较,ORACLE会自动将字符型用to_number()函数进行转换,从而导致第六种现象的发生。
第八,是否为表和相关的索引搜集足够的统计数据。
对数据经常有增、删、改的表最好定期对表和索引进行分析,可用SQL语句“analyzetablexxxxcomputestatisticsforallindexes;"。
ORACLE掌握了充分反映实际的统计数据,才有可能做出正确的选择。
第九,索引列的选择性不高。
我们假设典型情况,有表emp,共有一百万行数据,但其中的emp.deptno列,数据只有4种不同的值,如10、20、30、40。
虽然emp数据行有很多,ORACLE缺省认定表中列的值是在所有数据行均匀分布的,也就是说每种deptno值各有25万数据行与之对应。
假设SQL搜索条件DEPTNO=10,利用deptno列上的索引进行数据搜索效率,往往不比全表扫描的高,ORACLE理所当然对索引“视而不见”,认为该索引的选择性不高。
但我们考虑另一种情况,如果一百万数据行实际不是在4种deptno值间平均分配,其中有99万行对应着值10,5000行对应值20,3000行对应值30,2000行对应值40。
在这种数据分布图案中对除值为10外的其它deptno值搜索时,毫无疑问,如果索引能被应用,那么效率会高出很多。
我们可以采用对该索引列进行单独分析,或用analyze语句对该列建立直方图,对该列搜集足够的统计数据,使ORACLE在搜索选择性较高的值能用上索引。
第十,索引列值是否可为空(NULL)。
如果索引列值可以是空值,在SQL语句中那些需要返回NULL值的操作,将不会用到索引,如COUNT(*),而是用全表扫描。
这是因为索引中存储值不能为全空。
第十一,看是否有用到并行查询(PQO)。
并行查询将不会用到索引。
第十二,看PL/SQL语句中是否有用到bind变量。
由于数据库不知道bind变量具体是什么值,在做非相等连接时,如“<”,“>”,“like”等。
ORACLE将引用缺省值,在某些情况下会对执行计划造成影响。
当WHERE条件中的字段是类似column1isnull或者column1isnotnull是,即便column1上面本来有索引也不会用到
比如wherecolumn1=‘aaa’是可以用到索引的
下面的情况都用不到索引
column1||column2=‘aaabbb’
Substr(column1,1)=‘aaa’
Column1||’b’=‘aaab’
Column3+1>:
a
Like的字符串中第一个字符如果是‘%’则用不到索引
Column1like‘aaa%’是可以的
Column1like‘%aaa%’用不到
<>也用不到索引
有时可以采取适当措施改写后可以用到索引
例:
column1有3个值(‘A’,’B’,’C’),三个值的分布为‘A’10%,‘B’80%,’C’10%,则column1<>‘B’可以改写为column1in(‘A’,’C’)
尽管In写法要比exists简单一些,exists一般来说性能要比In要高的多
用In还是用Exists的时机
当in的集合比较小的时候,或者用Exists无法用到选择性高的索引的时候,用In要好,否则就要用Exists
例:
selectcount(*)fromperson_infowherexbin(selectxb_idfromdic_sex);
Selectcount(*)fromn_acntbasicawhereshbxdjm=:
aandexists(select1fromperson_infowherepid=a.pidand…);
Select*fromperson_infowherezjhm=3101….;将会对person_info全表扫描
Select*fromperson_infowherezjhm=‘3101…’才能用到索引
假定TEST表的dt字段是date类型的并且对dt建了索引。
如果要查‘20041010’一天的数据.下面的方法用不到索引
Select*fromtestwhereto_char(dt,’yyyymmdd’)=‘20041010’;
而select*fromtestwheredt>=to_date(‘20041010’,’yyyymmdd’)anddt 如果能不用到排序,则尽量避免排序。 用到排序的情况有 集合操作。 Union,minus,intersect等,注: unionall是不排序的。 Orderby Groupby Distinct In有时候也会用到排序 确实要排序的时候也尽量要排序小数据量 尽量让排序在内存中执行,有文章说,内存排序的速度是硬盘排序的1万倍 性能调优 在索引表的ROWID中存储的病不是记录的实际物理地址,而是逻辑的物理地址。 故有些数据库管理员把索引表中的ROWID列称为ROWID伪主键列,他存放的是表的主键信息。 一、索引表与标准表的差异 索引表与标准表的差异主要体现在四个方面。 一是索引表中的ROWID列存放的时主键信息,使逻辑的物理地址。 而在标准表的ROWID伪列中则存储的是真实的物理地址。 这是两者之间最本质的区别。 另外其他三个方面的差异都是因为有这个差异存在而存在。 也可以说,他是索引表优势的根源。 二是索引表对记录的访问是基于主键的,也就是说,根据逻辑的ROWID.而标准表在访问记录的时候,则是通过物理的ROWID地址。 三是表的扫描方式不同。 若采用索引表的话,则数据库是通过全索引扫描方式反问相关的记录;而若采用标准表的话,则是通过顺序扫描的方式返回相关的记录。 这两者访问方式在性能上有很大的差异。 全索引扫描方式,可能提供更高的查询性能。 四是对于主键的要求不同。 在建立标准表的时候,不一定要指定主键。 但是,若用户在建立索引表的时候,则必须给表建立主键,使用主键来唯一表示一行记录。 很简单,在索引表中,ROWID伪列中就存储着主键信息。 若不指定具体的主键的话,数据访问时就不能够对记录进行定位。 不过,索引表与标准表的差异只是停留在数据库开发的层面,或者说,只是内部存储结构上有一定的差异。 但是,在用户使用它们的时候,没有任何的差异。 在前台应用程序设计的时候,用户可以向普通表那样访问索引表。 这就给索引表的应用排除了使用上的障碍。 二、索引表的优势 索引表的优势主要体现在数据查询上。 而且,这个优势是非常明显的。 一是索引表能够获得比标准表更快的查询速度,即使这张标准表已经建立了合适的索引。 这跟索引表的存储结构是分不开的。 因为索引表的数据在存储的时候,所有的行记录都是跟排序过的主键列一起存储在数据库系统中。 故在查询的时候,只需要找到主键,就俄可以记录查询到整条记录的信息。 而标准表在数据查询的时候,需要先找到对应的ROWID列,然后再去查询主键信息,再去查询对应的记录。 所以,索引表减少了数据查询过程中的中间环节,避免了额外的数据块读取操作。 二是索引表中的记录,是按照主键列进行排序存储的。 对于主键列范围内的查询,用户可以获得更快的查询速度。 这主要是因为在表格的ROWID伪列中,直接存储了主键信息。 三是利用溢出存储功能,提高常用列的访问速度。 在后台数据库表中,可能有几十个字段。 但是,前台用户在查询的时候,往往不需要访问所有的字段。 那些用户经常要访问的列,就叫做常用列。 对常用列与不常用列区别明显的,可以通过溢出存储功能,提高常用列的访问速度。 即将表中不经常需要访问的非主键列不存储在B树的叶子节点中,而是存储在一个具有堆组织方式的溢出存储区中。 若索引表比较大,使用溢出存储不但可以减少索引表所占用的存储空间,而且可以提高常用列的查询效率。 当然,这只是针对常用列而言。 若用户查询非常用列时,溢出存储就没有效果了。 三、索引表的使用时机 1、通过关键字查询表的内容。 如果在实际应用中,大部分是通过主键列来查询其他列的信息的时候,就可以考虑把这张表建立为索引表。 如在ERP系统中,有销售订单表、采购订单表等等。 对于这些表单,用户查询的时候,大部分是按照订单单号来进行查询。 此时,数据库管理员在设计的时候,就可以把这些单据的基础表格设置为索引表。 特别是有些系统把单据分为单头档与单身档。 如采购订单单头与采购订单单身。 在前台显示为两个不同的页签,在后台对应两张不同的数据表。 利用单头页签来调用单身的内容。 此时,就是通过一个采购订单单头的ID列来查询单身的内容。 在这种设计的时候,完全可以把订单单身对应的数据库表设置为“索引表”。 从而提高数据查询的速度。 2、若表变化频繁,则不适宜使用索引表。 若表的变更比较频繁的话,则采用索引表不怎么合适。 这主要是因为Oracle数据库在对索引表管理时,开销比较大。 如对于员工考勤系统中,员工信息这个表变更不是很频繁,但是,员工考勤信息表中的数据,则每个小时都可能会发生变化。 对于变换这么频繁的表格,建立索引表就不怎么合适。 此时,我们需要为其建立基本表,然后在基本表上建立索引。 这虽然查询效果没有索引表那么好,但是,却可以大大减少Oracle数据库的开销。 所谓,有得必有失。 数据库管理员有时候还必须在这个得失之间寻求一个平衡。 以期数据库整体性能的最优化。 3、灵活利用溢出存储功能。 普通B树索引条目一般比较小,因为在每个索引条目中仅保存索引列的值与ROWID的值。 但是,因为索引表中的每个索引条目都包有整条地记录,所以,索引表中的索引条目就可能会很大。 为此,用户若在索引表中查询数据,其查询的只是其中一部分字段内容的话,索引表的效果就体现不出来。 相反,若表中的字段比较多的话,则效果会适得其反。 为此,在Oracle数据库中,采用了溢出存储功能来应对索引表的这个缺陷。 如在一张员工基本信息表中,他有员工姓名、员工编号、员工出身年月、身份证号码、住址、户口、民族等信息,长达几十个字段。 但是,在平时的时候,我们基本上只需要查询员工的姓名、编号、身份证号码即可。 而不需要其他的信息。 此时,当员工比较多的时候,就需要把这张表转换成索引表,然后采用溢出管理功能,来提高员工信息常用字段的查询速度。 把用户经常需要用到的员工姓名、编号、身份证号码等字段保存在叶子节点上。 而把其他不常用的字段采取“溢出存储”策略。 四、索引表的建立与使用 在介绍索引表与基本表的差异时,笔者已经说过,两者的差异主要体现在存储结构上。 故对于用户使用是没有多大影响的。 在索引表的建立上,可以参考相关的书籍。 笔者在这里只是要强调,在索引表建立过程中的几个关键点。 一是在索引表中,不能对非主键建立索引,。 这是索引表建立的一个限制条件,数据库管理人员必须无条件的遵守。 二是必须给索引表建立主键。 有些数据库管理员有个习惯,在建立表的时候,一开始不设置主键。 等到表维护的时候,再确定某个字段作为主键。 但是,在索引表建立的时候,一开始就要指定表的主键,否则的话,会有错误产生。 这也是数据库管理人员需要注意的。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 索引的使用规则与性能调优 索引 使用 规则 性能