DM8索引的详细介绍

1、索引存储结构
了解索引的存储结构对于正确使用和优化索引有很大帮助。
最常见的索引结构为Btree索引，下图是一个B树索引存储结构图。

B*树相关概念：

• 根节点
• 内节点
• 叶子节点
• 树高度h

聚集索引：

• 叶子节点存储的是数据块

非聚集索引：

• 叶子节点存储的是主键值或聚集索引的值或rowid

从B树中访问每个叶子节点的成本都是h次IO，索引的访问效率只跟B树的高度有关系。

–创建表 插入100万条数据
CREATE TABLE TEST_INDEX(ID INT,AGE INT);
BEGIN
FOR I IN 1…1000000 LOOP
INSERT INTO TEST_INDEX VALUES(MOD(I,9700),TRUNC(RAND * 120));
END LOOP;
COMMIT;
END;
–创建索引
CREATE INDEX IDX_ID_TEST_INDEX ON TEST_INDEX(ID);
–更新索引统计信息
SP_INDEX_STAT_INIT(USER,‘IDX_ID_TEST_INDEX’);
–查看索引相关信息
SELECT B.NAME,A.T_TOTAL, --表总行数
A.BLEVEL+1 HEIGHT, --索引高度
A.N_LEAF_USED_PAGES, --叶子节点数
INDEX_USED_PAGES(B.ID)-
A.N_LEAF_USED_PAGES-1 BRANCH_PAGES –内节点数
FROM SYSSTATS A,SYSOBJECTS B
WHERE A.ID=B.ID
AND B.NAME IN(‘IDX_ID_TEST_INDEX’);

2、创建索引的原则
在什么情况下使用B*树索引？
（1）仅当要通过索引访问表中很少的一部分行（1%~20%）
索引用于访问表中的行（只占一个很小的百分比）
（2）如果要处理表中的多行，而且可以使用索引而不用表
索引用于回答一个查询：索引提供了足够的信息来回答整个查询，不需要去访问表
索引可以作为一个“较瘦”版本的表

原则1：根据索引查询只返回很少一部分行
–创建表 插入10万条数据
CREATE TABLE TEST_INDEX(ID INT,AGE INT);
BEGIN
FOR I IN 1…100000 LOOP
INSERT INTO TEST_INDEX VALUES(MOD(I,9700),TRUNC(RAND * 120));
END LOOP;
COMMIT;
END;
–创建索引
CREATE INDEX IDX_ID_TEST_INDEX ON TEST_INDEX(ID);
CREATE INDEX IDX_AGE_TEST_INDEX ON TEST_INDEX(AGE);
–更新列统计信息
SP_COL_STAT_INIT_EX(USER,‘TEST_INDEX’,‘ID’,100);
SP_COL_STAT_INIT_EX(USER,‘TEST_INDEX’,‘AGE’,100);

–返回少部分行 走索引
EXPLAIN SELECT * FROM TEST_INDEX WHERE ID=100;
1 #NSET2: [0, 11, 16]
2 #PRJT2: [0, 11, 16]; exp_num(3), is_atom(FALSE)
3 #BLKUP2: [0, 11, 16]; IDX_ID_TEST_INDEX(TEST_INDEX)
#SSEK2: [0, 11, 16]; scan_type(ASC), IDX_ID_TEST_INDEX(TEST_INDEX), scan_range[100,100]

–返回大部分行 走全表扫描
EXPLAIN SELECT * FROM TEST_INDEX WHERE AGE>1 ;
1 #NSET2: [11, 98374, 16]
2 #PRJT2: [11, 98374, 16]; exp_num(3), is_atom(FALSE)
3 #SLCT2: [11, 98374, 16]; TEST_INDEX.AGE > 1
4 #CSCN2: [11, 100000, 16]; INDEX33556678(TEST_INDEX)

原则2：索引作为一个较瘦版本的表

–只需要扫描索引 不用扫描表
EXPLAIN SELECT TOP 10 * FROM TEST_INDEX ORDER BY AGE ;
1 #NSET2: [10, 10, 16]
2 #PRJT2: [10, 10, 16]; exp_num(3), is_atom(FALSE)
3 #TOPN2: [10, 10, 16]; top_num(10)
4 #BLKUP2: [10, 100000, 16]; IDX_AGE_TEST_INDEX(TEST_INDEX)
5 #SSCN: [10, 100000, 16]; IDX_AGE_TEST_INDEX(TEST_INDEX)

–只需要扫描索引 不用扫描表
EXPLAIN SELECT COUNT(DISTINCT AGE) FROM TEST_INDEX;
1 #NSET2: [17, 1, 4]
2 #PRJT2: [17, 1, 4]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [17, 1, 4]; grp_num(0), sfun_num(1)
4 #SSCN: [10, 100000, 4]; IDX_AGE_TEST_INDEX(TEST_INDEX)

组合索引列的顺序：
最优先把等值匹配的列放最前面，范围匹配的放后面
其次把过滤性好的列放前面，过滤性差的放后面
查询时组合索引只能利用一个非等值字段
–创建测试表
create table tab(c1 int,c2 char(1),c3 char(1),c4 int);
–构造测试数据
insert into tab
select level c1,chr(mod(level,27)+65) c2,chr(mod(level,27)+65) c3,level c4
from dual
connect by level<=10000;

–待优化语句如下：
SELECT * FROM TAB WHERE C1 BETWEEN 10 AND 20 AND C2 =‘A’ AND C3=‘B’;

–创建索引
CREATE INDEX IDX_C1_C2_C3_TAB ON TAB(C1,C2,C3);
CREATE INDEX IDX_C2_C3_C1_TAB ON TAB(C2,C3,C1);

–查看执行计划
EXPLAIN SELECT * FROM TAB WHERE C1 BETWEEN 10 AND 20 AND C2 =‘A’ AND C3=‘B’;
1 #NSET2: [0, 1, 112]
2 #PRJT2: [0, 1, 112]; exp_num(5), is_atom(FALSE)
3 #BLKUP2: [0, 1, 112]; IDX_C2_C3_C1_TAB(TAB)
#SSEK2: [0, 1, 112]; scan_type(ASC), IDX_C2_C3_C1_TAB(TAB),scan_range[(A,B,10),(A,B,20)]

–查看执行计划 可以看出这个索引只能利用C1列
EXPLAIN SELECT * FROM TAB INDEX IDX_C1_C2_C3_TAB TT WHERE C1 BETWEEN 10 AND 20 AND C2 =‘A’ AND C3=‘B’;

1 #NSET2: [0, 1, 112]
2 #PRJT2: [0, 1, 112]; exp_num(5), is_atom(FALSE)
3 #SLCT2: [0, 1, 112]; (TT.C2 = A AND TT.C3 = B)
4 #BLKUP2: [0, 25, 112]; IDX_C1_C2_C3_TAB(TT)
#SSEK2: [0, 25, 112]; scan_type(ASC), IDX_C1_C2_C3_TAB(TAB as TT), scan_range[(10,min,min),(20,max,max))

3、关于不走索引的各种场景
案例1：条件列不是索引的首列
–创建表
CREATE TABLE TAB1(C1 INT,C2 CHAR(1),C3 CHAR(1),C4 INT);
–构造测试数据
INSERT INTO TAB1
SELECT LEVEL C1,CHR(MOD(LEVEL,27)+65) C2,CHR(MOD(LEVEL,27)+65) C3,LEVEL C4
FROM DUAL
CONNECT BY LEVEL<=10000;
COMMIT;
CREATE INDEX IDX_C1_C2 ON TAB1(C1,C2);
EXPLAIN SELECT * FROM TAB1 WHERE C2=‘A’;

1 #NSET2: [1, 250, 112]
2 #PRJT2: [1, 250, 112]; exp_num(5), is_atom(FALSE)
3 #SLCT2: [1, 250, 112]; TAB1.C2 = A
4 #CSCN2: [1, 10000, 112]; INDEX33556684(TAB1)

案例2：条件列上有函数或计算
–正常情况
EXPLAIN SELECT * FROM TAB1 WHERE C1 =123;
1 #NSET2: [0, 1, 112]
2 #PRJT2: [0, 1, 112]; exp_num(5), is_atom(FALSE)
3 #BLKUP2: [0, 1, 112]; IDX_C1_C2_C3_TAB1(TAB1)
#SSEK2: [0, 1, 112]; scan_type(ASC), IDX_C1_C2_C3_TAB1(TAB1), scan_range[(123,min,min),(123,max,max))

–条件列上有函数
EXPLAIN SELECT * FROM TAB1 WHERE abs(C1) =123;
1 #NSET2: [137, 25000, 112]
2 #PRJT2: [137, 25000, 112]; exp_num(5), is_atom(FALSE)
3 #SLCT2: [137, 25000, 112]; exp11 = var1
#CSCN2: [137, 1000000, 112]; INDEX33556691(TAB1)

–条件列上有计算EXPLAIN SELECT * FROM TAB1 WHERE C1-1 =123;
1 #NSET2: [137, 25000, 112]
2 #PRJT2: [137, 25000, 112]; exp_num(5), is_atom(FALSE)
3 #SLCT2: [137, 25000, 112]; TAB1.C1-1 = 123
#CSCN2: [137, 1000000, 112]; INDEX33556691(TAB1)

EXPLAIN SELECT * FROM TAB1 WHERE C1 =123+1
1 #NSET2: [0, 1, 112]
2 #PRJT2: [0, 1, 112]; exp_num(5), is_atom(FALSE)
3 #BLKUP2: [0, 1, 112]; IDX_C1_C2_C3_TAB1(TAB1)
4 #SSEK2: [0, 1, 112]; scan_type(ASC), IDX_C1_C2_C3_TAB1(TAB1), scan_range[(123+1,min,min),(123+1,max,max))

案例3：存在隐式类型转换
–对条件列C1做了隐式的类型转换，将int类型转换为char类型
EXPLAIN SELECT * FROM TAB1 WHERE C1=‘1234567890’
1 #NSET2: [137, 25000, 112]
2 #PRJT2: [137, 25000, 112]; exp_num(5), is_atom(FALSE)
3 #SLCT2: [137, 25000, 112]; exp_cast(TAB1.C1) = var1
#CSCN2: [137, 1000000, 112]; INDEX33556691(TAB1)

–后面的常量小于10位，优化器对常量做了类型转换，这时可以走索引
EXPLAIN SELECT * FROM TAB1 WHERE C1=‘123456789’
1 #NSET2: [0, 1, 112]
2 #PRJT2: [0, 1, 112]; exp_num(5), is_atom(FALSE)
3 #BLKUP2: [0, 1, 112]; IDX_C1_C2_C3_TAB1(TAB1)
#SSEK2: [0, 1, 112]; scan_type(ASC), IDX_C1_C2_C3_TAB1(TAB1), scan_range[(exp_cast(123456789),min,min),(exp_cast(123456789),max,max))

–写SQL的时候数据类型最好匹配，不要让优化器来做这种隐式的类型转换
EXPLAIN SELECT * FROM TAB1 WHERE C1=1234567890
1 #NSET2: [0, 1, 112]
2 #PRJT2: [0, 1, 112]; exp_num(5), is_atom(FALSE)
3 #BLKUP2: [0, 1, 112]; IDX_C1_C2_C3_TAB1(TAB1)
4 #SSEK2: [0, 1, 112]; scan_type(ASC), IDX_C1_C2_C3_TAB1(TAB1), scan_range[(1234567890,min,min),(1234567890,max,max))

案例4：如果走索引会更慢
–创建测试表
CREATE TABLE TX(ID INT, NAME VARCHAR(100));
–插入数据
BEGIN
FOR X IN 1 … 100000 LOOP
INSERT INTO TX VALUES(X, ‘HELLO’);
END LOOP;
COMMIT;
END;
–创建索引 更新统计信息
CREATE INDEX TXL01 ON TX(ID);
SP_INDEX_STAT_INIT(USER,‘TXL01’);

–返回记录较多 不走索引
EXPLAIN SELECT * FROM TX WHERE ID <50000;
1 #NSET2: [12, 49998, 60]
2 #PRJT2: [12, 49998, 60]; exp_num(3), is_atom(FALSE)
3 #SLCT2: [12, 49998, 60]; TX.ID < 50000
#CSCN2: [12, 100000, 60]; INDEX33556697(TX)
–返回记录较少 走索引
EXPLAIN SELECT * FROM TX WHERE ID <500;
1 #NSET2: [8, 498, 60]
2 #PRJT2: [8, 498, 60]; exp_num(3), is_atom(FALSE)
3 #BLKUP2: [8, 498, 60]; TXL01(TX)
4 #SSEK2: [8, 498, 60]; scan_type(ASC), TXL01(TX), scan_range(null2,500)

案例5：没有更新统计信息
–创建测试表
CREATE TABLE TY(ID INT, NAME VARCHAR(100));
–插入数据
BEGIN
FOR X IN 1 … 100000 LOOP
INSERT INTO TY VALUES(X, ‘HELLO’);
END LOOP;
COMMIT;
END;
–创建索引
CREATE INDEX TYL01 ON TY(ID);

–未更新统计信息
EXPLAIN SELECT * FROM TY WHERE ID <500;
1 #NSET2: [12, 5000, 60]
2 #PRJT2: [12, 5000, 60]; exp_num(3), is_atom(FALSE)
3 #SLCT2: [12, 5000, 60]; TY.ID < 500
#CSCN2: [12, 100000, 60]; INDEX33556699(TY)

–更新统计信息
SP_INDEX_STAT_INIT(USER,‘TYL01’);
EXPLAIN SELECT * FROM TY WHERE ID <500;
1 #NSET2: [8, 498, 60]
2 #PRJT2: [8, 498, 60]; exp_num(3), is_atom(FALSE)
3 #BLKUP2: [8, 498, 60]; TYL01(TY)
4 #SSEK2: [8, 498, 60]; scan_type(ASC), TYL01(TY), scan_range(null2,500)

索引能提高查询性能，也能拖慢DML的效率。
下面在单机做了一个测试，在一个千万行的表上分别添加0~10个索引，记录更新一万行记录的时间，如下表：

上面具体的数字没有意义，因为不同的硬件环境和参数，数据会不一样。但是通过比较能发现，索引越多，表上DML操作的速度越慢。所以使用索引一定要注意质量，可有可无的索引必须要删掉。另外，大批量更新数据时，允许的话可以先删除索引，更新完毕后再重建，这样效率会高一些。