循序渐进db2笔记索引讲解.docx
- 文档编号:25199515
- 上传时间:2023-06-06
- 格式:DOCX
- 页数:15
- 大小:24.54KB
循序渐进db2笔记索引讲解.docx
《循序渐进db2笔记索引讲解.docx》由会员分享,可在线阅读,更多相关《循序渐进db2笔记索引讲解.docx(15页珍藏版)》请在冰豆网上搜索。
循序渐进db2笔记索引讲解
索引类型
唯一索引
它确保表中没有两个数据行具有完全相同的键值来帮助维护数据完整性。
尝试为已经包含数据的表创建唯一索引时,将检查组成该索引的列中的值是否唯一,如果包含具有重复键值的行,索引创建将失败。
为表定义了唯一索引之后,每当在索引中添加或更改键时就会强制唯一性(包括插入、更新、装入、导入和设置完整性以命名一部分)。
引:
唯一索引用来保证数据的唯一性,唯一索引一般性能要高于非唯一索引,只允许列表中出现一个键值,允许显示单个NULL。
例句:
CREATEUNIQUEINDEX
非唯一性索引
维护频繁使用的数据值的排序顺序,这仅仅用于提高查询性能,不用于对关联的表强制执行约束。
引:
数据库管理器使用唯一索引和NOTNULL约束的组合来实现主键约束和唯一键约束。
可以说大部分的索引是非唯一索引,这和数据的分布有关系,一般的数据都具有可重复性特性,所以他们不能被定义为唯一索引。
非唯一索引可以使用命令:
CREATEINDEX
集群索引
数据页中行的顺序对应于索引中行的顺序。
这就使得给定表中只能存在一个集群索引。
在某些关系数据库管理系统中,集群索引的叶子节点对应于实际数据,而不是对应于指定位于其他地方的数据的指针。
集群索引作用:
如果对表进行了重组,那么会按照索引键的顺序将行插入数据页中。
集群索引改善了以键的顺序扫描整张表的性能。
引:
群集索引允许对数据页采用更线性的访问模式,允许更有效的预取,并且避免排序。
群集索引是要求数据在插入时,做更多的操作,将相临的数据条目放入相同的页,使得查询速度更快,因为每次访问索引页要将所有的索引条目都访问完毕才移到下一页,保证了缓存池中任何一个时刻都只有一个索引页存在。
群集索引的特点:
提高查询速度,数据页以键的顺序排列;
以键的顺序扫描整张表;
插入和更新需要做更多的事情,不建议经常插入和更新的表上做群集索引
非集群索引
两者都只包含索引结构中的键和记录标识。
记录标识始终指向数据页中的行。
集群与非集群的区别
数据库管理器尝试按照相应的键在索引页中出现的顺序来将数据保存在数据页中,将尝试把具有相似键的行插入同一页中。
双向索引
双向索引允许按正反两个方向进行扫描。
CREATEINDEX语句的ALLOWREVERSESCANS子句同时启用正反向索引扫描,反之DISALLOWREVERSESCANS。
双向索引优点:
1.便于使用MIN和MAX函数
2.访存先前的键
3.不需要数据库管理器创建临时表来进行反向扫描
4.消除冗余反向顺序索引
MDC块索引
当我们创建MDC时,数据库会自动生成块(block)索引,MDC索引相关的块索引是排列到一起的,可以显著的提高性能。
DB2数据库管理器使用B+树结构进行索引存储。
索引的缺点
1.创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
2.索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间。
3.当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。
索引访问机制
1.大多数情况下,DB2优化器喜欢使用索引。
2.如果没有WHERE子句,全扫描是最佳的。
3.对于非常小的表甚至较大的表,使用索引有可能不会优化性能,读取索引页再读取数据页,组织索引需要额外的I/O以实现查询。
4.表扫描也有其优点。
DB2会限定要扫描的页面;还可以调用顺序预取以在请求某些页面之前就读取这些页面。
索引扫描方式
匹配索引扫描MatchingIndexScan
索引:
INDEX(CITY,CNTRY,ADD_DATE)
查询:
SELECT ADD_DATE
FROMCUSTOMER
WHERE CITY = ’BEIJING’ AND CNTRY = ‘CHINA';
由于此查询在新建索引的第一列上存在谓词条件,DB2能够根据这个谓词条件从索引树的根节点开始遍历,经过中间节点最后定位到某一个叶子节点,然后从此叶子节点开始往后进行在叶子节点上的索引扫描,直到找到所有满足条件的记录。
非匹配索引扫描Non-matchingIndexScan
索引:
INDEX(CITY,CNTRY,ADD_DATE)
查询:
SELECT ADD_DATE
FROMCUSTOMER
WHERE CITY = ’BEIJING’ AND CNTRY = ‘CHINA';
将add_date放在索引的第一个位置,而查询并不存在add_date上的谓词条件,那么这个索引扫描将会从第一个索引叶子节点开始,它无法从根节点开始并经过中间节点直接定位到某一个叶子节点,这种扫描的范围扩大到了整个索引,我们称之为非匹配索引扫描。
使用索引访问数据,必须满足以下条件
1.至少有一个SQL谓词必须是可索引的。
某些谓词因其特性而不能被索引。
2.其中一列(在任何可索引的谓词中)必须作为可用索引中的列而存在。
3.直接索引查找(也是最简单的),要求WHERE子句中必须为每个列提供值,例如以下语句:
SELECTFIRSTNME,LASTNAME
FROMEMPLOYEE
WHEREDEPNO=5ANDTYPE='X'ANDEMPCODE=10;
注意:
以下三个实例是数据库中包含DEPTNO、TYPE、EMPCODE列的索引。
4.匹配索引扫描与直接索引查找完全一样,有时称为绝对定位。
要使用匹配索引,必须指定索引关键字中的高次序列例如以下语句中的字段“DEPNO”。
这向DB2提供了遍历索引结构的起始点,从根页开始遍历,直到相应的叶子页。
SELECTFIRSTNME,LASTNAME
FROMEMPLOYEE
WHEREDEPNO=5ANDTYPE='X';
5.非匹配索引扫描,有时称为相对定位。
如果将以上实例改为如下情况,会用到非匹配索引方式,从索引中的第一个叶子页开始遍历,应用可用的谓词,顺序扫描后续的叶子页。
不使用根页和任何中间叶子页。
SELECTFIRSTNME,LASTNAME
FROMEMPLOYEE
WHERETYPE='X'ANDEMPCODE=10;
6.完全索引访问(indexaccessonly)是一种特殊类型的索引扫描,如果所需要的全部数据都位于索引中,那么DB2完全可以避免读取数据页。
例如:
SELECTDEPTNO,TYPEFROMEMPOYEEWHEREEMPLOYEE=10;
7.多索引访问:
即针对一个存取路径,使用多个索引。
例如,查询EMPLOYEE表,其中只有两个索引:
关于EMPNO列的IX1和关于DEPTNO列的IX2。
然后,要求这条查询显示在某个特定部门工作的员工:
SELECTLASTNAME,FIRSTNME,MIDINIT
FROMEMPLOYEE
WHEREEMPNOIN('000100','000110','000120')ANDDEPTNO=5;
根据谓词是用AND连接还是用OR连接,可将多索引分为两类:
IndexANDING和IndexORING。
前者先使用两个索引取到索引扫描的结果,然后对两个扫描结果取交集;后者则对取得的结果并操作。
创建集群索引
CREATEINDEXINDEX1ONEMPLOYEE(LASTNAME)CLUSTER
为了让语句更有效,可以通过ALTERTABLE语句相关的PCTFREE参数来使用群集索引,以便于将新数据插入到正确的页上,从而维护该集群的次序。
通常情况下,表上的INSERT操作越多,为维护群集所需要的PCTFREE值就越大。
因为这个索引确定数据在物理页上放置的次序,所以对任何特定的表只能定义一个。
另一方面,如果这些新行的索引关键字值总是新的大关键字值,那么表的群集属性将尝试把它们放到表的末尾,其他页上有空闲空间对保持群集没有什么作用。
这种情况下,将表设置为追加方式可能优于使用群集集群,该表来拥有一个大的PCTFREE值。
可以执行如下命令来将表设置为追加方式:
ALTERTABLEAPPENDON。
以上讨论也适用于增大行大小的UPDATE引起的新的“溢出(overflow)”行。
创建双向索引
CREATEINDEXinameONtname(cnameDESC)ALLOWREVERSESCANS
在这种情况下,基于给定列(cname)中的递减值(DESC)形成索引(iname)。
可以按照递减次序扫描,也可按照降序(反向)扫描,由优化器控制这些索引的使用。
索引页合并与分裂
CREATEINDEX语句的MINPCTUSED子句指定在索引叶页上最小已用空间的阀值。
使用这个子句,就可以对这个索引启用联机索引重组。
以下考虑事项来确定是否执行联机重组:
当从这个索引的一个索引叶子页(leaf)中删除一个关键字(key)后,如果该页上已用空间的百分比小于所指定的阀值,那么就检查相邻的索引叶页来确定是否可以将两个叶页上的关键字合并到单个索引页中。
例如,下列SQL语句创建启用联机索引重组的索引:
CREATEINDEXLASTNONEMPLOYEE(LASTNAME)MINPCTUSED20
当从这个索引删除一个关键字时,如果这个索引页上的其余关键字占用索引页上20%或更小的空间,那么就可以尝试将这个索引页的关键字与相邻索引页的关键字合并,来删除这个索引页。
如果组合的关键字可以全部位于一页上,那么就执行这个合并,并删除其中一个索引页。
CREATEINDEX语句的PCTFREE子句指定,创建索引时每个索引页中要留作空闲空间的百分比。
在索引页上保留更多的空闲空间将导致更少的页分割,这将减少为重新获得顺序索引页面而重组表的需要,从而增加预存取,而预存取是一个可以提高性能的重要部件。
此外,如果总是存在大关键字值,那么就要考虑降低CREATEINDEX语句的PCTFREE。
对于只读表上的索引,使PCTFREE为0;
对于其他索引,使PCTFREE为10,以提供可用的空间,从而加快插入操作的速度。
对于有群集索引的表而言,这个值应该更大一些,以确保集群索引不会被分成太多的碎片。
如果存在大量的插入操作,那么使用15到35之间的值或许更合适一些。
完全索引访问
CREATEINDEX语句的INCLUDE子句指定在创建索引时,可以选择包含附加的列数据,这些附加的列数据将与键存储在一起,但实际上它们不是键自身的一部分,所以不被排序。
在索引中包含附加列的主要原因是为了提高某些查询的性能。
DB2将不需要访问数据页,因为索引页早已经提供了数据值。
只可以为包含的列定义唯一索引。
但在强制执行索引的唯一性时不考虑被包含的列。
例:
CREATEUIIQUEINDEXIEMPNOONEMP(EMPNO)INCLUDE(EMPNAME)
为什么不干脆在索引中包括所有的数据呢?
首先,这需要数据库中更多的物理空间,因为本质上数据是在索引中复制的。
其次,只要更新了数据的值,数据的所有副本都需要更新,在发生许多次更新的数据中,这是一项很大的开销。
创建索引示例
在最频繁处理的查询和事务的WHERE子句中所使用的那些列上创建关系索引。
以下WHERE子句
WHEREWORKDEPT='A01'ORWORKDEPT='E21'
通常会从WORKDEPT上的索引获益,除非WORKDEPT列包含许多重复值。
在按查询所需要的顺序对行排序的一列或多列上创建关系索引时,不仅在ORDERBY子句中,而且其他功能,如DISTINCT和GROUPBY子句也都需要排序。
以下示例使用DISTINCT子句:
SELECTDISTINCTWORKDEPTFROMEMPLLOYEE
数据库管理器可使用WORKDEPT上定义为升序或降序的索引来消除重复值。
此同一个索引也可用于GROUPBY子句中,已将其分组,如下所示:
SELECTWORKDEPT,AVERAGE(SALARY)
FROMEMPLOYEEGROUPBYWORKDEPT
使用复合键创建索引,该键命名语句中引用的每个列。
当用此方式指定索引时,可以从完全索引检索关系数据,这比访问表更有效。
例如,考虑以下SQL语句:
SELECTLASTNAMEFROMEMPLOYEEWHEREWORKDEPTIN('A00','D11','D21')
如果为EMPLYEE表的WORKDEPT和LASTNAME列定义了关系索引,那么通过扫描索引而不是扫描整个表可能会更有效的处理该语句。
注意,因为该谓词基于WORKDEPT,因此此列应是该关系索引的第一列。
使用INCLUDE列创建关系索引可改善表上索引的使用。
使用上述示例,可将唯一关系索引定义为:
CREATEUNIQUEINDEXXONEMPLOYEE(WORKDEPT)INCLUDE(LASTNAME)
指定LASTNAME为INCLUDE列而不是索引键的一部分,意味着LASTNAME只存储在索引叶子页上。
1.根据查询所使用的列创建索引
对于一个特定的查询,可以为某一个表所有出现在查询中的列建立一个复合索引,包括出现在SELECT语句和条件子句中的列,一般情况下,要根据谓词的选择度来排列索引中各列的位置(索引中列的顺序是非常重要的,将会影响到查询是匹配索引扫描还是非匹配),选择度大的谓词所使用的列放在索引的前面,把那些只存在于SELECT语句中的列放在索引的最后。
例如下面的查询:
SELECTADD_DATEFORMTEMP.CUSTOMERWHERECITY='WASHINGTON'ANDCNTRY_CODE='USA'
可以建立(city,cntry_code,add_date)索引。
由于该索引包含了所有用到的列,所以此查询将不会访问数据页面,直接使用索引页面。
2.根据条件语句中的谓词的选择度创建索引
因为建立索引需要占用数据库的存储空间,所以需要在空间和时间性能之间进行权衡。
很多时候,只考虑那些在条件子句中有条件判断的列上建立的索引也会同样有效,同时节约了空间。
SELECTCOUNT(*)FROMTEMP.CUSTOMERWHERECITY='WASHINGTON'ANDCNTRY_CODE='USA';
SELECTCOUNT(*)FROMTEMP.CUSTOMERWHERECITY='WASHINGTON';
SELECTCOUNT(*)FROMTEMP.CUSTOMERWHERECNTRY_CODE='USA';
RESULTS:
1404
1407
128700
选择度越大,过滤掉的记录越多,返回的结果集也就越少。
从上面的结果可以看到,第二个查询的选择度几乎和整个条件语句相同。
因此,可以直接建立单列索引(CITY),其性能与索引(CITY,CNTRY_CODE,ADD_DATE)相差不多。
3.避免在建有索引的列上使用函数
函数的的单调性不确定,函数的返回值和输入值可能不会一一对应,那么可能存在索引中位置差异很大的多个列值可以满足带有函数的谓词条件,DB2优化器将无法进行MatchingIndexScan,更坏的情况是可能导致直接表扫描。
比较:
SELECTADD_DATEFROMTEMP.CUSTOMERWHERECITY='WASHINGTON'ANDCNTRY_CODE='USA';
SELECTADD_DATEFROMTEMP.CUSTOMERWHEREUPPER(CITY)='WASHINGTON'ANDCNTRY_CODE='USA';
4.在那些需要被排序的列上创建索引
这里不仅仅指ORDERBY子句,还包括DISTINCTUNIONGROUPUP子句,它们都会产生排序操作。
由于索引本身是有序的,在其创建过程中已经进行了排序处理,因此在应用这些语句的列上创建索引会降低排序操作的代价。
这种情况一般针对没有条件的查询。
如果存在条件语句,DB2优化器会首先选择出满足条件的记录,然后才对中间结果集进行排序。
可以创建单列索引,如果需要复合索引,则需要把被排序的列放在复合索引的第一列。
例如:
SELECTDISTINCTADD_DATEFROMTEMP.CUSTOMER
比较
无索引
temp.cust_i4(add_date)
temp.cust_i5(add_date,cust_name)
temp.cust_i6(cust_name,add_date)
5.合理使用include关键词创建索引
对于类似下面查询:
SELECTCUST_NAMEFROMTEMP.CUSTOMER
WHERECUST_NUMBETWEEN'0007000000'AND'0007200000'
CREATEUNIQUEINDEXTEMP.CUST_I7ONTEMP.CUSTOMER(CUST_NUM)INCLUDE(CUST_NAME)
使用带有include列的唯一索引会带来优于复合索引的性能,因为唯一索引能够避免一些不必要的操作,如排序。
6.创建索引的排序属性
SELECTMAX(ADD_DATE)FROMTEMP.EMPLOYEE
CREATEINDEXTEMP.EMPLOYEE_I1ONTEMP.EMPLOYEE(ADD_DATE)
因为创建索引时候并没有制定排序属性,默认为ASC升序排列,DB2将会扫描整个索引树的叶子节点并取得所有值后,取得最大值。
我们可以设置排序属性来提高查询性能。
CREATEINDEXTEMP.EMPLOYEE_I1ONTEMP.EMPLOYEE(ADD_DATEDESC)
对于降序排列的索引,DB2不需要扫描整个索引数的叶子节点,因为第一个节点便是最大的。
我们同样可以使用ALLOWREVERSESCANS来指定索引为双向扫描,具有和DESC近似的查询性能,可以被认为是ASC和DESC的组合,只是在以后数据更新的时候维护成本会相对高一些。
如果无法改变索引的排序属性,但是我们具有额外的信息,如该公司每个月都会有新员工入职,那个这个查询就可以改写成:
SELECTMAX(ADD_DATE)FROMTEMP.EMPLOYEEWHEREADD_DATE>CURRENTTIMESTAMP-1MONTH
这样一来,通过一个查询范围也会有效的提高查询性能。
建立索引策略
DB2在用户指定数据表主键时,自动生成以主键为关键字的聚簇索引。
建立其他索引时有以下策略:
(1)避免在小表上建立索引:
因为索引的维护需要一定的代价,在表上进行增删改操作时,索引需要重组,这就增加了数据库的负担,如果对索引的插入或者更新损失的时间大于在查询中节省下来的时间,那么使用索引就是一个不明智的决定。
(2)在经常进行链接的列上建立索引,并且字段类型保持一致多表链接查询是数据库中最复杂、最耗时的操作之一。
改进多表链接查询性能对系统性能的改进起很大的作用。
在链接属性上存在索引时,DB2中采用索引嵌套循环链接,否则DB2使用哈希链接。
如果表A与表B以A.a-m---B.b为条件进行链接,则索引嵌套循环链接的时间复杂度为(IRl*logISI),而时间复杂度为(IRI+ISI)。
(3)在频繁进行groupby/orderby的列上建立索引
(4)建立索引字段的列的长度尽量小,避免在Blob/Clob类型上建立索引。
(5)在SQL语句中频繁进行比较运算的列上建立索引。
(6)避免在选择性太低的字段上建立索引,选择性太低指的是索引中同一索引值的对应记录太多,在这种列上建立索引意义不大。
DB2最优化处理器不会使用该列作为执行计划的一部分。
(7)建立组合索引需要注意索引列顺序如果在A,B两列上顺序建立组合索引以后,那么在where子句中带有下列搜索条件会使用此索引:
条件为A;条件为A。
B。
如果条件仅为B则不会使用此索引。
(8)可以适当采用DB2的MDC表格。
在MDC表格中,索引在定义表格结构时就确定,以后无法更改。
但是,查询语句中只要条件属于索引列,查询进行索引扫描;缺点就是在数据块没有占满的情况下会引起空间浪费。
在普通表格中,组合索引是表格建立以后才建立的,可以自由的添加删除,查询语句设置条件时必须严格按照组合索引的顺序进行组织,否则查询将导致表扫描。
索引总结
创建的索引应该取决于数据和存取该数据的查询。
以下准则可帮助你如何创建可用于各种目的的索引:
1.要避免某些排序,只要有可能,就使用CREATEUNIQUEINDEX语句定义主键和唯一键。
2.要改善数据检索,将INCLUDE列添加至唯一索引。
合适的列为:
---被频繁存取,因此可从完全索引访问(indexaccessonly)受益的列
---不需要用来限制索引扫描的范围的列
---不影响索引键的排序或唯一性的列
3.要有效存取小表,使用索引来优化对含有较多数据页的表的频繁查询,数据页数记录在SYSCAT.TABLES目录试图的NNPAGES列中。
你应该:
---根据连接表时要使用的任何一列来创建索引
---根据将用于定期索引特定值的任何列来创建索引
4.要有效的搜索,对键使用升序还是降序取决于将最常使用的次序。
尽管当在CREATEINDEX语句中指定了ALLOWREVERSESCANS参数时可以按逆向方向搜索值。
但是,执行指
定索引次序的扫描比执行逆向扫描稍微更快一些。
5.要节省索引维护成本和空间
---避免创建的索引是这些列上其他索引键的部分键。
---不在所有列上任意创建索引。
不必要的索引不仅占用空间,而且会导致大量准备时间。
当使用具有动态编程连接枚举的优化级别时,对于复杂的查询这特别重要。
---使用下列一般规则来确定将为表定义的索引的典型数目。
此数目根据数据库的主要使用来确定:
对于OLTP环境,创建2个或3个索引
对于只读环境,可以创建5个以上索引
对于混合查询和在线事务处理环境,可以创建2到5个索引
6.要改进对父表执行的删除和更新操作的性能,在外键上创建索引。
7.对于快速排序操作,在频繁用于排序数据的列上创建索引。
8.要改进多列索引的连接性能,如果第一个键列有多项选择,则使用最常见的“=”(等值连接)谓词指定的那一列,或使用如第一个键那样具有最多不同值的那些列。
9.要帮助新插入的行根据索引进行群集并避免页分割,定义一个集群索引。
集群索引应显著减少重组表的需要。
当定义表时使用PCTREE关键字来指定页上应该留下多少可用空间,才能允许将插入行适当的放在页上。
也可以指定LOAD命令的pagefreespaceMODIFIEDBY子句。
10.要启用联机索引整理碎片,创建索引时使用MINPCTUSED选项。
该选项指定索引叶子页中最小使用空间量的阀值并启用联机索引碎片整理。
如果这些删除实际上是从索引页除去键,则可以在键删除期间以性能损失为代
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 循序渐进 db2 笔记 索引 讲解