转载声明：本文为DBA 社群原创文章，转载必须连同本订阅号二维码全文转载，并注明作者名字及来源：DBA 社群（dbaplus）。 在数据库日常优化中，索引是经常使用的手段之一。本文由DB2数据库专家刘自传就围绕索引从DB2索引的扫描方式、DB2在索引列上应用谓词、在DB2中如何设计索引、在DB2中如何做索引的深度清理展开一些讨论、学习，更进一步探索索引的奥妙。 专家简介
拥有10年IT从业经验，DB2数据库专家；具有多年的SQL开发、C语言开发、DB2运维及诊断优化经验；获IBMDB2 V10.1 for LUW的初、中、高级的DBA认证；擅长DB2的SQL优化、故障诊断及性能调优；db2china.net社区专家，并组织在线DB2数据库性能优化技术交流活动；具有多年医疗、金融行业、移动运营商（广东、西藏）运维经验。 1 DB2索引的扫描方式 在DB2，索引的扫描方式有多种形式，从DB2执行计划中可以看到有以下的形式： 1、IXSCANFETCH (先索引扫描，再根据RID读数据行) 2、INDEXONLY（索引扫描，只索引读即可满足整个SQL） 3、LIST PREFETCH（先索引扫描，再把RID排序，最后根据排序后RID读数据行） 4、IndexANDing（也叫IXAND，对两或多个索引分别进行扫描，这些索引一般是单列索引，再把符合条件RID进行AND运算，最后根据AND运算后的RID读取数据行） 5、IndexORing（也叫IXOR，对两或多个索引分别进行扫描，这些索引一般是单列索引，再把符合条件RID进行OR运算，最后根据OR运算后的RID读取数据行） 如下图的执行计划树中，分别有LIST PREFETCH、IXAND、IXOR扫描方式： 2 DB2索引管理器在对索引进行扫描时，在索引列上应用谓词时涉及到索引列的start key/stop key，有以下3种情况： （以上效率比较，是在这3种方式之间的比较，并不是与表扫描方式比较） 结合上图，其中A类型是属于Range-delimiting谓词；B、C类型是属于Index-SARgable谓词。 在分析执行计划时，可以识别出索引扫描时使用的是哪种谓词扫描方式，如下图可以看出是使用A类型：Range-delimiting谓词方式： 3 （一）、索引设计大原则： 1.为最重要、最频繁的查询/业务优先建索引； 2.为表的主、外键建立索引（注意主、外键的数据类型及长度最好一致，避免数据类型的转换）; 3.索引的总数量、每个索引的字段数量要适中，一定不能太多，否则会增加IO、内存、CPU的额外开销，增加UDI、编译及准备、实用工具、备份及恢复的工作负载。（如：OLTP建议在5个以内，OLAP在10个以内）; 4.可结合SQL执行计划、经验、db2advis等来分析：SQL是否使用了已有索引、是否需要创建新索引、是否基于现有索引重设计索引; 5.根据不同的情况选择不同的索引类型：普通索引、唯一索引、集聚索引、包含索引；单键索引、组合索引；MDC索引等; 6.若业务有特殊性，可考虑让索引支持双向扫描; 7.根据表的UID工作负载，选择适当pctfree/minpctused，避免索引页的浪费、不合理的索引页合并; 8.必要时可考虑创建完全索引扫描、包含索引来避免数据页读； 9.若评估启用索引压缩能有效节省索引大小，则启用索引压缩（注意不建议在CPU密集型环境中启用）； 10.把索引表空间与数据表空间、大对象表空间分开放置在不同的物理磁盘，避免IO争用； 11.对于分区表，可根据实际情况，选择建立全局索引、分区索引，建议使用分区索引； 12.若时间、工作负载允许，可考虑在建索引时收集统计信息、采样收集统计信息，加快收集统计信息速度； 13.避免在小表上建议索引； 14.避免在大对象、长列（LB/LF）列上建立索引； 15.避免创建不使用的索引（建议考虑定期的索引清理，后面会提到索引的深度清理）； 16.特殊情况下，考虑建多个相关单列索引，让DB2优化器使用index-Anding/index-Oring索引操作。 （二）、避免创建冗余索引 冗余索引是指一个索引字段是另一个索引字段的前导部分，如存在两个索引，分别是：（ A B C）、（ A B），则索引（ A B）是索引（ A B C）的冗余索引。相比之下，DB2优化器一般不会优先考虑冗余索引。 知道了什么是冗余索引，那么我们就可以有效地避免冗余索引、识别冗余索引。 要识别冗余索引，通过查询系统视图：syscat.indexes.COLNAMES/SYSCAT.INDEXCOLUSE，检查系统中是否已经存在了冗余索引，根据索引的使用情况考虑做索引的清理，以节省额外的磁盘、IO、内存、CPU的开销及相关UDI的维护成本。 （三）、复合索引的设计细节建议： 在对复合索引设计有一个three-star原则，就是选择哪些字段、安排字段的顺序： 1-start: where谓词列表放最前面，当然where谓词列表中有多个字段时，索引字段也有先后原则：等于谓词及范围定界谓词的放在前面、过滤因子较小(能过滤较多的记录)的放在较前；注意部分操作谓词（如 |
|
声明：文章版权归原作者所有 部分文章转自互联网 如有侵权请联系
[邮箱地址] 删除
|
注册
登录
