深入DB2索引

 深入DB2索引

ü        DB2索引简介

ü        DB2索引结构

ü        DB2索引访问机制

ü        DB2索引设计

ü        DB2索引创建原则

ü        DB2索引维护

ü        DB2索引优化

1DB2索引简介

 

   索引优点:

 

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)    群集索引

 

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

群集索引的特点:

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

    以键的顺序扫描整张表;

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

  

2DB2索引结构

 

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

 

典型的B+树结构:

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

 

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

 

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

个指针被实际使用(但如果是根结点,则不管n多大都只要求至少两个指针被使用)。如果j个指针被使用,那该块中将有j1个键,设为K1K2⋯⋯,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”

 

5DB2索引维护

6DB2索引优化

 

谓词类型

可索引

       

Colcon

Y

∝代表>,>=,=,<=,<,但是<>是可能不可索引的。

Col between con1 and con2

Y

在匹配系列中必须是最后的。

Col in list

Y

仅对一个匹配列

Col is null

Y

 

Col like ‘xyz%’

Y

模糊匹配%在后面。

Col like ‘%xyz’

N

模糊匹配%在前面。

Col1Col2

N

Col1col2来自同一个表

ColExpression

N

例如:c1c1+1/2

Pred1 and Pred2

Y

Pred1Pred2都是可索引的,指相同索引的列

Pred1 or Pred2

N

除了(c1=a or c1=b)外,他可以被认为是c1 inab

Not Pred1

N

或者任何的等价形式:Not between,Not in,Not like等等。

 

<think>好的,我现在需要帮助用户了解如何在DB2数据库中创建索引。首先,我得回顾一下用户的问题,他明确询问的是创建索引的步骤,所以我的回答需要围绕这个主题展开。 用户提供的引用资料中有几个相关的引用,比如引用[2]和引用[4]都提到了Create Index语句的基本用法和语法结构。我需要先整理这些信息。引用[2]提到索引用于提高查询速度,有效聚类和分区数据,还能增强视图性能,这点应该在回答中提及,说明为什么需要创建索引。引用[4]给出了具体的语法格式,包括索引名、表名、列名以及排序方式,这部分是核心步骤,必须详细说明。 接下来,我需要考虑用户可能的实际需求。用户可能不仅仅想知道基础语法,还可能需要具体的示例,这样更容易理解。例如,给出一个实际的创建索引SQL语句,并解释每个部分的作用。比如,创建一个唯一索引或复合索引的例子,这样用户可以看到不同的应用场景。 另外,引用[3]提到索引的存储位置,建议将索引与表数据分开存储,虽然用户的问题中没有直接涉及表空间,但可能高级用户会关心这一点。不过,考虑到用户的问题比较基础,可能在回答中简要提到索引存储的最佳实践,但不需要深入,除非用户特别问及。 同时,引用[1]和引用[5]虽然涉及创建数据库对象和存储器组,但和索引创建关系不大,可以暂时忽略,除非有其他相关信息需要关联。 然后,我需要确保回答符合用户给出的格式要求,使用中文,正确应用LaTeX格式的行内公式和独立公式。不过在创建索引的语法示例中,可能不需要数学公式,但如果有涉及到排序顺序(ASC/DESC),可以用行内公式格式,例如$ASC$或$DESC$,但实际检查发现用户示例中并没有在代码块中使用LaTeX,而是用代码格式。所以应该使用代码块来展示SQL语句,而不是LaTeX。 用户还要求生成相关问题,我需要根据创建索引的主题,提出几个相关的问题,比如索引类型、优化方法、维护策略等,这些能帮助用户进一步学习。 另外,需要检查是否有遗漏的要点,比如索引的注意事项,如唯一性约束、性能影响(增删改操作的代价),以及何时创建索引(选择哪些列)。这些内容在引用[2]中提到了唯一索引,可以强调在创建时使用UNIQUE关键字。 最后,确保回答结构清晰,分步骤说明,并包含示例代码,这样用户能一步步跟着操作。同时,引用正确的来源,比如引用[2]和引用[4],在相关的地方添加引用标识。</think>以下是在DB2数据库中创建索引的详细步骤说明: ### 一、创建索引的语法规范 基本语法结构如下: ``` CREATE [UNIQUE] INDEX index_name ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...) [INCLUDE (columnX, columnY)] [其他可选参数]; ``` - $UNIQUE$:声明索引键值的唯一性约束[^2] - $index_name$:需符合DB2命名规范(长度≤128字节) - $ASC|DESC$:指定索引的排序方向(默认$ASC$升序)[^4] ### 二、操作示例 #### 基础索引创建 ```sql CREATE INDEX idx_emp_dept ON employees (department_id); ``` 该语句为employees表的department_id字段创建普通索引 #### 复合索引示例 ```sql CREATE INDEX idx_emp_name ON employees (last_name ASC, first_name DESC); ``` 创建包含两个字段的组合索引,last_name升序,first_name降序 #### 唯一索引示例 ```sql CREATE UNIQUE INDEX uidx_emp_ssn ON employees (social_security_number); ``` 通过$UNIQUE$关键字确保社保号字段唯一性[^2] ### 三、最佳实践建议 1. **索引存储优化**:建议将索引与表数据分开存储到独立表空间(需提前创建表空间)[^3] 2. **选择索引字段**:优先在WHERE/JOIN/ORDER BY涉及的字段上创建索引 3. **索引维护**:定期执行`REORG INDEXES`和`RUNSTATS`命令维护索引效率 4. **避免过度索引**:单个表建议不超过5个索引,过多索引会影响DML操作性能 ### 四、验证索引创建 通过系统目录表查询索引信息: ```sql SELECT indname, colnames, uniquerule FROM syscat.indexes WHERE tabname = 'EMPLOYEES'; ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值