DB2索引

索引结构
表和索引的关系
索引结构
数据页和记录
三、索引规则
LOGO
索引创建规则
有效数据
10万笔记录的烟农表里，要取出编号为001的烟农。 有效数据=1笔，其它的都为无效数据。
索引本质目的
数据库的本质原理是将数据页加载到缓冲池(内存)中进行处理，建 索引的本质目的是减少数据页的读取，也就降低了IO活动。而且即使 缓冲池足够大，能够将整张表缓存进来，但是在缓冲池中定位目标数 据，也会因此耗费更多CPU资源。
索引类型
集群索引(聚簇索引)
对磁盘上数据重新组织以按指定的一个或多个列的值排序的索引。 如字典按字母排序。 一个表上只允许有一个聚簇索引
集群索引的优势
在那些包含范围检查(between、<、<=、>、>=)或使用group by 或orderby的查询时，一旦找到具有范围中第一个键值的行，具有 后续索引值的行保证物理上毗连在一起而不必进一步搜索，避免了大 范围扫描，可以大大提高查询速度。在这期间还能避免调入大量非聚 簇索引页到缓冲池中，减少IO活动，同时也能提高预取的效率。
运行统计 RUNSTATS
RUNSTATS
基本统计信息
表： RUNSTATS ON TABLE 表名 索引： RUNSTATS ON TABLE 表名 FOR INDEXES ALL 表和索引： RUNSTATS ON TABLE 表名 AND INDEXES ALL
增强的统计信息
表： RUNSTATS ON TABLE 表名 WITH DISTRIBUTION 索引： RUNSTATS ON TABLE 表名 FOR DETAILED INDEXES ALL 表和索引： RUNSTATS ON TABLE 表名 WITH DISTRIBUTION AND DETAILED INDEXES ALL 注： 在以上每个命令中，表名必须用模式名全限定。
要复原索引集群、释放空间以及降低叶级别，可以使用 下列其中一种方法：
删除并重新创建索引。 使用 REORG TABLE 命令，并指定允许以脱机方式重组表及其索 引的选项。 使用 REORG INDEXES 命令以联机方式重组索引。在生产环境中， 您可以选择此方法，因为这将允许用户在重建表索引期间对表执行 读写操作。
重组检测reorgchk索引统计indcard索引的数量leaf索引叶子页的总数量eleaf被标记为删除的但还未物理删除移除的索引叶子页lvls索引级数ndel被标记为删除的rid数量keysdistinct出的键值的总和leafrecsize一个叶子页包含的索引记录条目nleafrecsize除去include列叶子页包含的索引记录条目leafpageoverhead在索引叶子页预留内部使用的空间nleafpageoverhead在非索引叶子页预留内部使用的空间重组表reorgtable常用命令如果表上没有索引db2reorgtableschematabnameusetempspace1如果表上有多个索引indexname选择最重要的索引db2reorgtableschematabnameindexschemaidxnameusetempspace1指定数据重排时使用的临时表空间否则reorg工作将会在表所在表空间中原地执行
优化建议
1. 重写来删除由 OR 链接的谓词
----使用 UNION 删除 OR SELECT * FROM TABLE_A A, TABLE_B B WHERE A.COL3 = B.COL3 AND A.COL1 = 2 OR B.COL2 = 'CAT' 变成 SELECT * FROM TABLE_A A, TABLE_B B WHERE A.COL3 = B.COL3 AND A.COL1 = 2 UNION SELECT * FROM TABLE_A A, TABLE_B B WHERE A.COL3 = B.COL3 AND B.COL2 = 'CAT‘ ----使用 IN-list 删除 OR SELECT * FROM TABLE_A WHERE (COL1 = 2 AND COL2 = 'CAT') OR (COL1 = 2 AND COL2 = 'DOG') 变成 SELECT * FROM TABLE_A WHERE COL1 = 2 AND COL2 IN ('CAT', 'DOG')
纯索引
select col2 from 表 where col1=? CREATE INDEX <IDX_NAME> ON <TAB_NAME> (COL1_NAME) INCLUDE(COL2_NAME)
COL2将与键存储到一起，但是不作为索引的一部分，所以不被排 序。纯索引访问是用来减少对数据页的访问，因为所需要的数据已经 显示在索引中了。
包括主键在内，一张表最多可创建15个索引； 每个索引最多可包含8列； 不能在BLOB 数据类型的列上创建索引； DB2默认创建ASC的索引； DB2在主键和UNIQUE字段上自动建立唯一索引； 注：索引也可以到DB2控制中心通过界面创建添加。
索引类型
非唯一索引 唯一索引
创建唯一索引时，该列上的值须是唯一的； 创建了唯一索引的列上的数据允许为空；
有无索引的DML速度对比
有无索引性能对比
注：表1有索引，表2无索引，两张表一样，基础数据量为100万. （单位:根据主键列 表1 表2 0.78 0.78 0.78 0.78 0.62 0.47 0.47 0.46
0
根据唯一索引列 表1 0.63 0.94 0.62 0.16 表2 5.974 5.756 5.85 6.459
五、索引维护
LOGO
索引维护
运行统计 RUNSTATS 重组检测 REORGCHK 重组表 REORG TABLE 重组索引 REORG INDEX 重组规则 自动维护 Automatic maintenance
运行统计 RUNSTATS
RUNSTATS
SQL = 做什么 优化器 = 如何做
重组检测 REORGCHK
下列任何因素都可能指示用户应该重组表
自上次重组表之后，对该表进行了大量的插入、更新和删除活动 对于使用具有高集群率的索引的查询，其性能发生了明显变化 在执行 RUNSTATS 命令以刷新统计信息后，性能没有得到改善 REORGCHK 命令指示需要重组表（注意：在某些情况下， REORGCHK 总是建议重组表，即使在执行了重组后也是如此）。 例如，如果使用 32KB 页大小，并且平均记录长度为 15 字节且每 页最多包含 253 条记录，则每页具有 32700- (15 x 253)=28905 个未使用字节。这意味着大约 88% 的页面是可用 空间。用户应分析 REORGCHK 的建议并针对执行重组所需的成本 平衡利益 db.tb_reorg_req（需要重组）运行状况指示器处于 ATTENTION 状态。此运行状况指示器的集合详细信息描述通过重组可获得好处 的表和索引的列表
因此建索引，通常可以提高查询速度，降低CPU和IO的耗用。
索引创建规则
1. 数量超过6000笔的表考虑建索引
如参数配置表，需要被各单位频繁读，不需要索引
2. 在选择度低的字段上建索引
选择度=目标行数/总行数
如性别列，只有男女两个值，不应建索引。 在选择度高于5 ‰的列上，建索引应慎重。
3. 在联合查询的表的联合字段上建索引
运行统计 RUNSTATS
统计信息
Table 包括表的记录数、PAGE、PCTFREE以及COMPRESS等信息， 相关的系统视图是:sysstat.tables、syscat.tables; Columns 包括COLUMNS的数量、长度、分布特征以及COMPRESS等信息, 相关的系统视图是:sysstat.columns、syscat.columns; Index 包括是否存在索引、索引的组织(叶子页的数量和级别的数量)、索 引键的离散值的数量以及是否群集索引. 相关的系统视图是:sysstat.indexes、syscat. indexes。
四、索引优化
LOGO
索引优化
谓词类型 Col∝con Col between con1 and con2 可索引 Y Y 注 释 ∝代表>,>=,=,<=,<,但是<>是可能不 可索引的。 在匹配系列中必须是最后的。
Col in list
Col is null Col like ‘xyz%’ Col like ‘%xyz’ Col1∝Col2 Col∝Expression Pred1 and Pred2 Pred1 or Pred2 Not Pred1
以有海量藏书的图书馆为例，要快速找到某本书，需要： 1.藏书有序排放；2.有藏书的排放清单； 它应该包含2个基本的信息： 1.索引列的列值 2.该列值的行对应的数据页的逻辑指针。
索引的优缺点
优点 提高数据的查询速度 通过创建UNIQUE INDEX保证数据唯一性
缺点 进行增加、删除和修改的时候，会同时对表和索引进行 操作，需要额外的成本开销。 需要占用额外的物理空间。
优化建议
2. 使用一个 IN-list 来替换一个范围谓词
COL1 BETWEEN '2' AND '4' 重写为 COL1 IN ('2', '3', '4')
3. 谓词条件不在索引列上进行表达式计算
假设 :V1 和 :V2 是主机变量，:V2 包含一个日期值。 (START_DATE - :V1 DAYS) < :V2 重写为 START_DATE < (DATE(:V2) + :V1 DAYS)
创建语法
CREATE [UNIQUE] INDEX <IDX_NAME> ON <TAB_NAME>(col1 ASC/DESC,col2 ASC/DESC) [CLUSTER] --非必须，当创建为集群索引时指定 [ALLOW REVERSE SCAN] --非必须，允许正/逆向扫描

重组检测 REORGCHK
表被更新后，索引性能可能会下降
叶子页碎片化。叶子页碎片化之后，必须读取更多的叶子页才能访 存表页，因此 I/O 操作成本会增加。 物理索引页的顺序不再与那些页中键的顺序相匹配，从而产生不良 集群索引叶子页出现不良集群情况后，顺序预取操作的效率将降低， I/O 等待数将增加。 索引发展到层数过多。
Y
Y Y N N N Y N N
仅对一个匹配列
模糊匹配%在后面。 模糊匹配%在前面。 Col1和col2来自同一个表 例如：c1（c1+1）/2 Pred1和Pred2都是可索引的，指相同索引 的列 除了（c1=a or c1=b）外，他可以被认为 是c1 in（a，b） 或者任何的等价形式：Not between,Not in,Not like等等。
集群索引的优势
在插入时做更多的操作
索引类型
非集群索引寻行
集群索引寻行
索引结构
B+树结构
通常B树有三层，即查找一笔记录时，需要三次IO 一次完整的IO时间的经验值是0.0125秒 根结点中至少有两个指针被使用 叶结点中，最后一个指针指向它右边的下一个叶结点存储块，即指 向下一个键值大于它的块。
如主从表
4. 在Where条件中的字段上建索引
如果select的列比较少，可以考虑INCLUDE到索引中。
索引创建规则
5. 不要在经常被修改的字段上建索引 6. 尽量减少索引的创建，要少而精 7. 优先考虑建单索引，必要情况下建复合索引
如果需要多个字段才能有较高的选择度，考虑建复合索引 按使用频率高到低，选择度低到高进行排列建立复合索引
更新1笔
普通索引列 非索引列 删除1笔 查询1笔

插入1万笔仅相差1秒，差别不大。 如果根据主键列查询，无索引表在更新/删除/查询时比有索引表略快。 如果根据索引列查询，有索引表在更新/删除/查询时比无索引表快很多。 此次对比未考虑对资源的额外需求。
二、索引类型
LOGO
索引类型
创建语法 索引类型 索引结构
8. 聚簇索引的候选列
1、主键列,该列在where子句中使用并且插入是随机的。 2、按范围存取的列，如pri_order > 100 and pri_order < 200。 3、在group by或order by中使用的列。 4、不经常修改的列。
索引匹配规则
1. 复合索引匹配按照从左到右顺序； 2. 建立的复合索引，在按该索引列中任一列进行 单独查询时，也可以匹配。
优化器使用来自数据库的目录表来获取关于数据库、数据库中的数 据量以及其他特征的信息，并使用此信息来选择访问数据的最佳方式。 当一张表经过大量更新（插入、删除、修改）后，或者表重组后， 应该使用 RUNSTATS 命令来收集关于表和索引的当前统计信息。 一般来说，大约表里面的数据量的10%-20%发生了变化，就应该 作一次RUNSTATS 。
DB2 索引
质量管理中心
LOGO
目录
一、索引简介 二、索引类型 三、索引规则 四、索引优化
五、索引维护
Company Logo
一、索引简介
LOGO
一、索引简介
索引的定义 索引的优缺点 有无索引的DML速度对比
索引的定义
索引是一个单独的、物理的数据库结构，它是某个表中 一列或若干列值的集合和相应的指向表中物理标识这些值 的数据页的逻辑指针的清单（序列）。