深入DB2索引

 深入DB2索引

ü        DB2索引简介

ü        DB2索引结构

ü        DB2索引访问机制

ü        DB2索引设计

ü        DB2索引创建原则

ü        DB2索引维护

ü        DB2索引优化

1、DB2索引简介

 

   索引优点：

 

（1） 创建索引可提高查询速度。

（2） 创建索引保证数据唯一性。

 

   索引类型：

 

在介绍索引类型前介绍一下关于稠密度的概念.

稠密度定义：在数据分布均匀的情况下，稠密度=数据分布的可能数/数据总条数。例如：表1中有索引1在列1上，其中列1的数据分布有10中，分别是1-10，数据接近均匀分布，总数据量为1000，则该索引的稠密度=100/1000=10%，稠密度最高为1。稠密度越小，索引的选择性越大，查询性能越好。

 

（1）    非唯一索引

 

可以说大部分的索引的非唯一索引，这和数据的分布有关系，一般的数据都具有可重复性特性，所以他们不能被定义为唯一索引。非唯一索引可以使用命令：

CREATE INDEX <IDX_NAME> ON <TAB_NAME> (<COLNAME>)来定义。

（2）    唯一索引

 

唯一索引用来保证数据的唯一性，唯一索引一般性能要高于非唯一索引，这与索引的稠密度有关。唯一索引的稠密度永远等于数据总条数的倒数。

（3）    纯索引

 

纯索引的概念是相对与一般索引。如下方式表中有俩个字段，其中字段1是唯一主键，字段2为数据，实际的查询中经常是select * from 表 where col1=?

这样的查询条件可以使用纯索引来避免表查询，具体创建命令为

CREATE UNIQUE INDEX <IDX_NAME> ON <TAB_NAME> (COL1_NAME) INCLUDE(COL2_NAME)。上述的语句的意思就是在col1上创建唯一索引，选择包含col2的数据，这些附加的数据将与键存储到一起，但是不作为索引的一部分，所以不被排序。纯索引访问是用来减少对数据页的访问，因为所需要的数据已经显示在索引中了。

（4）    群集索引

 

群集索引允许对数据页采用更线性的访问模式，允许更有效的预取，并且避免排序。群集索引是要求数据在插入时，做更多的操作，将相临的数据条目放入相同的页，使得查询速度更快，因为每次访问索引页要将所有的索引条目都访问完毕才移到下一页，保证了缓存池中任何一个时刻都只有一个索引页存在。

群集索引的特点：

    提高查询速度，数据页以键的顺序排列；

    以键的顺序扫描整张表；

    插入和更新需要做更多的事情，不建议经常插入和更新的表上做群集索引；

  

2、DB2索引结构

 

在DB2中，索引的数据结构是一颗B+树。B树把它的存储块组织成一棵树。这棵树是平衡的，即从树根到树叶的所有路径都一样长。通常B树有三层：根、中间层和叶，但也可以是任意多层。

 

典型的B+树结构：

根结点中至少有两个指针被使用。所有指针指向位于B树下一层的存储块；

 

叶结点中，最后一个指针指向它右边的下一个叶结点存储块，即指向下一个键值大于它的块。在叶块的其他n个指针当中，至少有个指针被使用且指向数据记录；未使用的指针可看作空指针且不指向任何地方。如果第i个指数被使用，则指向具有第i个键值的记录；

 

 在内层结点中，所有的n+ 1个指针都可以用来指向B树中下一层的块。其中至少

个指针被实际使用（但如果是根结点，则不管n多大都只要求至少两个指针被使用）。如果j个指针被使用，那该块中将有j－1个键，设为K1，K2⋯⋯，Kj - 1。第一个指针指向B树的一部分，一些键值小于K1的记录可在这一部分找到。第二个指针指向B树的另一部分，所有键值大小等于K1且小于K2的记录可在这一部分中。依此类推。最后，第j个指针指向B树的又一部分，一些键值大于等于Kj - 1的记录可以在这一部分中找到。注意：某些键值远小于K1或远大于Kj - 1的记录可能根本无法通过该块到达，但可通过同一层的其他块到达。

 

假若我们以常规的画树方式来画B树，任一给定结点的子结点按从左（第一个子结点）到右（最后一个子结点）的顺序排列。那么，我们在任何一个层次上从左到右来看B树的结点，结点的键值将按非减的顺序出现。

DB2中索引结构

 

标准表的表和索引管理

记录表示和数据页

 

 

在DB2中可使用命令db2dart <dbname> /di /tsi <tabspacesid> /oi <tableid> /ps <N>p /np 1 /v y来查看索引的物理结构以增加对索引的理解。

例如：主库中

3、 DB2索引访问机制

 

快速索引式访问

 

一般来将DB2最快的数据访问方式就是使用索引。索引是为了快速找着数据块的数据结构。

 

在DB2使用索引来查询数据前，必须满足以下要求：

 

至少有一个SQL谓词必须是可索引的。

其中一列必须作为可用索引中的列而存在。

 

 

4、 DB2索引创建原则

 

DB2索引实现是一个B+树，通过索引可以实现快速查询，避免全表扫描以此来减少IO操作。

 

    索引是对表数据的一种抽象，通过抽取有限数据，对数据的分布进行计算，以此来完成对数据的快速检索。

索引创建语句”CREATE INDEX <INDEX_NAME> ON <TABLE_NAME> (<COLNAME1,COLNAME2…>)”。

X`

    创建索引需要注意的地方：

l        索引应该用来提高查询速度，但是会对更新和删除操作带来负面影响，因为要同步更新索引。所以索引应该创建到更新、删除相对比读取少的表上。

 

l            索引需要独立的空间进行存储和管理。索引是需要磁盘空间来存储。所以避免重复创建冗余索引。如下:“CREATE TABLE TEST_IDX (COL1 INT NOT NULL, COL2 INT NOT NULL, COL3 IN NOT NULL)”“CREATE INDEX TEST_IDX_IDX1 ON TEST_IDX (COL1, COL2, COL3)”已经有索引TEST_IDX_IDX1在三个列上，在创建”CREATE INDEX TEST_IDX_IDX2 ON TEST_IDX (COL1, COL2)”，这样的索引一般没有什么作用。

   

l            索引用来避免表扫描。通过索引对大量数据抽取有限部分，形成一个相对少量的有序数据结构，通过对有序数据结构的查找可以快速想要查找的数据。所以索引适合建立在数据量比较大的表上，而且该表上的查询经常是根据条件查询部分数据。比如一些系统基础表，如SYSTEM表，这些表数据量小，而且经常是查询全部数据，所以这些表上建立索引对性能的影响不是很大，完全可以避免，以免对管理造成影响。

 

l            创建索引的目的还有一个就是保证数据唯一性，可以利用”CREATE UNIQUE INDEX <INDEX_NAME> ON <TABLENAME> (<COLNAME>)”，来完成。

 

l            主键会隐式创建索引，所以请不要在主键上创建索引浪费空间。

 

l            尽量减少索引的创建。DB2路径访问优化器会根据表中所提供的索引来完成尽可能多的访问路径的成本估计。创建过多的索引意味着DB2优化器生成更多的访问路径，完成更多的访问计划成本估算，这会增加SQL语句编译时间。

 

l            创建唯一索引可以避免排序。因为索引是有序数据结构，在进行扫描时，DB2会默认按照顺序输出结果，而不是按照插入先后。通过创建唯一索引可以避免排序，提高查询性能。

 

l            具有大量重复数据的列上不要创建索引。在大量重复的列上创建索引没有任何意义。如下数据结构：表中字段col1有大量重复数据，其中的数据分布是按照90%的Y，和10%的N来分布。这样的列上创建索引没有任何意义。在查询条件为col1=‘Y’时，该表的索引扫描和表扫描没有特大差异。根据实践经验，列上的数据分布应该均匀，并且抽密度不能大于5 ‰。

 

    索引扫描原理图：

创建如下表：

   

“CREATE TABLE TEST1 (NO INT NOT NULL, NAME CHAR(5))”

“CREATE INDEX TEST1_IDX_1 ON TEST1 (NO)”

“INSERT INTO TEST1 SELECT ROW_NUMBER() OVER(), CHR(INT(RAND()*75+48))||CHR(INT(RAND()*75+48))||CHR(INT(RAND()*75+48)) FROM SYSCAT.COLUMNS T1 JOIN SYSCAT.COLUMNS T2 ON T1.COLNAME!=T2.COLNAME FETCH FIRST 100 ROWS ONLY“

 “SELECT * FROM TEST1 WHERE NO = 50”

 

5、DB2索引维护

6、DB2索引优化

 

谓词类型	可索引	注        释
Col∝con	Y	∝代表>,>=,=,<=,<,但是<>是可能不可索引的。
Col between con1 and con2	Y	在匹配系列中必须是最后的。
Col in list	Y	仅对一个匹配列
Col is null	Y
Col like ‘xyz%’	Y	模糊匹配%在后面。
Col like ‘%xyz’	N	模糊匹配%在前面。
Col1∝Col2	N	Col1和col2来自同一个表
Col∝Expression	N	例如：c1（c1+1）/2
Pred1 and Pred2	Y	Pred1和Pred2都是可索引的，指相同索引的列
Pred1 or Pred2	N	除了（c1=a or c1=b）外，他可以被认为是c1 in（a，b）
Not Pred1	N	或者任何的等价形式：Not between,Not in,Not like等等。