基础 | 管理索引

本文参考： https://eco.dameng.com

管理索引的准则

索引：提供访问表数据的更快路径

DM8常见类型

类型	描述
聚集索引	每一个普通表有且只有一个聚集索引
唯一索引	索引数据根据索引键唯一
函数索引	包含函数 / 表达式的预先计算的值
位图索引	对低基数的列创建位图索引
位图连接索引	针对两个或多个表连接的位图索引，主要用于数据仓库中
全文索引	在表的文本列上创建的索引

在表中插入数据后创建索引

一般情况下，在插入或装载数据后，为表创建索引会更有效率。因为如果不这么做，插入每行数据时DM8都必须更改和维护每个索引，影响插入效率。

索引正确的表和列

创建索引的准则：

1. 若需要经常检索大表中的少量行，就为查询键创建索引
2. 为改善多表连接的性能，可为连接列创建索引
3. 主键和唯一键自动具有索引，在外键上很多情况下也创建索引
4. 小表不需要索引

选取表中索引列时可考虑以下几点

• 列中的值相对较唯一
• 取值范围大，适合建索引
• CLOB和TEXT只能建立全文索引，BLOB不能建立任何索引

为性能而安排索引列

在CREATE INDEX语句中，列的排序会影响查询的性能。通常，将最常用的列放在最前面。

若查询中有多个字段组合定位，则不应该为每个字段单独创建索引，应该创建一个组合索引。

当两个或多个字段都是等值查询时，组合索引中各个列的前后关系是无关紧要的；若有非等值查询，要想有效利用组合索引，建索引时应把等值字段放在前面，因为查询时只能利用一个非等值字段。

控制表的索引个数

表可以有任意数量的索引，但索引越多，修改表数据的开销越大

插入或修改行时，表上的所有索引都需要"更新"一下
更改一个列时，包含该列的所有索引也需要更改

故需在检索表数据和更新表之间，权衡索引个数

若表主要用于读，可多建索引；若表常更新，则少建索引

创建索引

在employee表的ename列上创建一个名为emp_ename的索引，存放在users表空间中
CREATE INDEX emp_ename ON employee(ename)
STORAGE(
INITIAL 50,
NEXT 50,
ON users
);

创建聚集索引

可在建表后，重新创建新的聚集索引，这样会使表数据按新的聚集索引排序。

CREATE CLUSTER INDEX clu_emp_name ON employee(ename);

新建聚集索引会重建表、表的所有索引（包括二级索引、函数索引），代价巨大，故聚集索引最好在建表时就确定好。

创建聚集索引的约束条件：
略

明确地创建唯一索引

唯一索引可确保索引键上不出现重复值（相当于对表的列添加了唯一约束）

create unique index unique_test20_c1 on test20(c1);

若对表的列定义UNIQUE完整性约束，DM8会自动在该列上定义一个唯一索引。

自动创建与约束相关的唯一索引

DM8通过在唯一键或主键上（自动）创建一个唯一索引，实现UNIQUE KEY或PRIMARY KEY完整性约束。

添加主键时，会自动在主键上创建一个唯一索引
CREATE TABLE tb1(c1 int);
ALTER TABLE tb1 ADD CONSTRAINT pk_tb1_c1 PRIMARY KEY(c1);

创建基于函数的索引

对于函数索引，其索引列上的值是通过函数或表达式得出的，能提高查询列或where条件中需要执行函数或表达式的查询效率

相当于提前把函数或表达式执行了，查询时直接从函数索引中获取即可，无需再次计算

创建测试表
create table tb_emp(
name 	varchar(20),
salary	int
);

创建存储过程，插入100w行数据（随机生成），名字为1-5个字符，工资为1W以内
create or replace procedure pro_insert_emp() as 
begin
	for i in 1..1000000 loop
		insert into tb_emp values(
		DBMS_RANDOM.STRING('1',TRUNC(DBMS_RANDOM.VALUE(1,5))),
		TRUNC(DBMS_RANDOM.VALUE(0,10000))
		);
		commit;
	end loop;
end;
/

调用存储过程
call pro_insert_emp();

检查数据量
select count(*) from tb_emp;

创建普通索引，还是全表扫（CSCN2）
create index idx_emp_sal on tb_emp(salary);
explain select name,salary*3 from tb_emp where salary*3 > 20000; 

1   #NSET2: [126, 50000, 60] 
2     #PRJT2: [126, 50000, 60]; exp_num(3), is_atom(FALSE) 
3       #SLCT2: [126, 50000, 60]; TB_EMP.SALARY*3 > 20000
4         #CSCN2: [126, 1000000, 60]; INDEX33555647(TB_EMP)

创建函数索引，变为二级索引扫描（SSEK2），即先扫描索引，再通过主键、聚集索引、rowid 等信息去扫描表
create index idx_emp_3sal on tb_emp(salary*3);
explain select name,salary*3 from tb_emp where salary*3 > 20000; 

1   #NSET2: [50, 50000, 60] 
2     #PRJT2: [50, 50000, 60]; exp_num(3), is_atom(FALSE) 
3       #BLKUP2: [50, 50000, 60]; IDX_EMP_3SAL(TB_EMP)
4         #SSEK2: [50, 50000, 60]; scan_type(ASC), IDX_EMP_3SAL(TB_EMP), scan_range(20000,max]

有些情况下，不能创建函数索引，详情请查看
https://eco.dameng.com/docs/zh-cn/pm/manage-index.html

创建位图索引

位图索引主要针对含有大量相同值的列而创建（即列的基数低，例如性别、年级，就那几样）

位图索引广泛用于数据仓库，对低基数（不同的值很少）的列创建位图索引，能够有效提高基于该列的查询效率。且执行查询语句的where子句中带有AND和OR谓词时，效率更加明显。

为表tb_emp增加性别列，对比位图索引和普通索引的差异

创建测试表，增加了性别列（即低基数的列）
create table tb_emp(
name 	varchar(20),
salary	int,
sex		smallint
);

随机生成100w条记录的存储过程，用数字0和1代表性别
create or replace procedure pro_insert_emp() as 
begin
	for i in 1..1000000 loop
		insert into tb_emp values(
		DBMS_RANDOM.STRING('1',TRUNC(DBMS_RANDOM.VALUE(1,5))),
		TRUNC(DBMS_RANDOM.VALUE(0,10000)),
		TRUNC(DBMS_RANDOM.VALUE(0,2))
		);
		commit;
	end loop;
end;
/

调用存储过程
call pro_insert_emp();

查询性别为1的用户，对比普通索引和位图索引的区别

explain select name,salary from tb_emp where sex=1;
1   #NSET2: [127, 25000, 62] 
2     #PRJT2: [127, 25000, 62]; exp_num(3), is_atom(FALSE) 
3       #SLCT2: [127, 25000, 62]; TB_EMP.SEX = 1
4         #CSCN2: [127, 1000000, 62]; INDEX33555657(TB_EMP)

在性别列上创建普通索引idx_emp_sex_normal
create index idx_emp_sex_normal on tb_emp(sex);
explain select name,salary from tb_emp where sex=1;
1   #NSET2: [25, 25000, 62] 
2     #PRJT2: [25, 25000, 62]; exp_num(3), is_atom(FALSE) 
3       #BLKUP2: [25, 25000, 62]; IDX_EMP_SEX_NORMAL(TB_EMP)
4         #SSEK2: [25, 25000, 62]; scan_type(ASC), IDX_EMP_SEX_NORMAL(TB_EMP), scan_range[1,1]

在性别列上创建位图索引idx_emp_sex_bitmap
drop index idx_emp_sex_normal;
create bitmap index idx_emp_sex_bitmap on tb_emp(sex);
explain select name,salary from tb_emp where sex=1;
1   #NSET2: [1, 25000, 62] 
2     #PRJT2: [1, 25000, 62]; exp_num(3), is_atom(FALSE) 
3       #BLKUP2: [1, 25000, 62]; INDEX33555657(TB_EMP)
4         #BMCVT: [1, 25000, 62]
5           #BMSEK: [1, 25000, 62]; scan_type(ASC), INDEX33555660(BM$_33555659), scan_range[(1,null2),(1,max))

由三元组[127, 25000, 62]、[25, 25000, 62]、[1, 25000, 62]可以看出，位图索引的代价最低（为1）

创建位图索引也是有约束的，具体内容请查阅
https://eco.dameng.com/docs/zh-cn/pm/manage-index.html

重建索引

表的增删改操作，会使表和索引产生碎片（即数据不紧凑，页数变多），从而影响访问速度。

可以使用重建索引来对索引的数据进行重组，使数据更加紧凑，并释放不需要的空间，从而提高访问效率和空间效率。

创建存储过程，向tb_emp表插入n行数据
create or replace procedure pro_insert_emp(loop_times in int) as 
begin
	for i in 1..loop_times loop
		insert into tb_emp values(
		DBMS_RANDOM.STRING('1',TRUNC(DBMS_RANDOM.VALUE(1,5))),
		TRUNC(DBMS_RANDOM.VALUE(0,10000)),
		TRUNC(DBMS_RANDOM.VALUE(0,2))
		);
		commit;
	end loop;
end;
/

删除测试表
drop table tb_emp;

重建测试表
create table tb_emp(
name 	varchar(20),
salary	int,
sex		smallint

插入10w行记录
pro_insert_emp(100000);

创建索引
create bitmap index idx_emp_sex_bitmap on tb_emp(sex);
);

查看表和索引的空间使用情况

查看第一次插入10w条记录的大小
SQL> SELECT TABLE_USED_SPACE('SYSDBA', 'TB_EMP');

LINEID     TABLE_USED_SPACE('SYSDBA','TB_EMP')
---------- -----------------------------------
1          192

SQL> SELECT TABLE_USED_PAGES('SYSDBA', 'TB_EMP');

LINEID     TABLE_USED_PAGES('SYSDBA','TB_EMP')
---------- -----------------------------------
1          105

SQL> select id,name from sysobjects where name='IDX_EMP_SEX_BITMAP';

LINEID     ID          NAME              
---------- ----------- ------------------
1          33555662    IDX_EMP_SEX_BITMAP

SQL> select index_used_space(33555662);

LINEID     INDEX_USED_SPACE(33555662)
---------- --------------------------
1          96

SQL> select index_used_pages(33555662);

LINEID     INDEX_USED_PAGES(33555662)
---------- --------------------------
1          4

模拟大量删除、插入操作，看看对索引空间有什么影响（重复操作几次）
delete tb_emp where rownum<=10000;
commit;
pro_insert_emp(10000);

检查下，大量删除、插入操作执行完后，数据量仍为10W行
select count(*) from tb_emp;

注意，此处索引使用的页数多了1页（由4页变成了5页），为什么呢？
索引存储的数据量不变（10w行），每行存储的值也不变（不是0就是1），按理说使用的页应该也不变（仍为4页）
这多出来的页数，应该就是大量插入、删除、修改操作所产生的磁盘碎片
SQL> select index_used_pages(33555662);

LINEID     INDEX_USED_PAGES(33555662)
---------- --------------------------
1          5

页数多了，会影响索引扫描的效率，故需要通过重建索引，消除磁盘碎片，减少索引使用的页数

重建索引IDX_EMP_SEX_BITMAP，页数由5变成了4
SQL> SP_REBUILD_INDEX('SYSDBA', 33555662);
SQL> select index_used_pages(33555662);

LINEID     INDEX_USED_PAGES(33555662)
---------- --------------------------
1          4

删除索引

一般情况下，这么删除索引

DROP INDEX IF EXISTS IDX_EMP_SEX_BITMAP;

然而，不能直接删除与已启用的UNIQUE KEY键或PRIMARY KEY 键约束相关的索引。要删除一个与约束相关的索引，必须停用或删除该约束本身。如下面的语句删除主键约束pk_emp_name，同时删除其对应的索引。

要删除与UNIQUE KEY或PRIMARY KEY相关的索引，需停用或删除约束本身
例如，如下语句删除主键约束pk_emp_name，系统会自动删除对应的索引

新建表emp，主键为pk_emp_name
create table emp(
name varchar(20),
constraint pk_emp_name primary key (name)
);

系统自动在主键列创建了索引INDEX33555665
SQL> SELECT TABLE_OWNER schema_name,TABLE_NAME ,COLUMN_NAME ,INDEX_NAME  from DBA_IND_COLUMNS WHERE TABLE_NAME='EMP';

LINEID     SCHEMA_NAME TABLE_NAME COLUMN_NAME INDEX_NAME   
---------- ----------- ---------- ----------- -------------
1          SYSDBA      EMP        NAME        INDEX33555665

直接删除与约束相关的索引会报错
SQL> drop index if exists INDEX33555665;
drop index if exists INDEX33555665;
[-5540]:Error in line: 1
No drop index privilege of table [EMP].
used time: 1.137(ms). Execute id is 0.

删除索引对应的约束，系统会自动删除索引
SQL> alter table "SYSDBA"."EMP" drop constraint "PK_EMP_NAME";
SQL> SELECT TABLE_OWNER schema_name,TABLE_NAME ,COLUMN_NAME ,INDEX_NAME  from DBA_IND_COLUMNS WHERE TABLE_NAME='EMP';
no rows

删除表就自动删除了所有与其相关的索引

SQL> create table tb22(c1 int);
SQL> create index idx_tb22_c1 on tb22(c1);
SQL> select id,name from sysobjects where name='IDX_TB22_C1';

LINEID     ID          NAME       
---------- ----------- -----------
1          33555668    IDX_TB22_C1
SQL> drop table tb22;
SQL> select id,name from sysobjects where name='IDX_TB22_C1';
no rows

查看索引信息

查看索引ID

SQL> select id,name from sysobjects where name='IDX_TB22_C1';

LINEID     ID          NAME       
---------- ----------- -----------
1          33555670    IDX_TB22_C1

查看索引定义，第一个参数为索引ID，第二个参数可以是0或1（显示详细内容，选1）

SQL> select INDEXDEF(33555670, 1);

LINEID     INDEXDEF(33555670,1)                                                                     
---------- -----------------------------------------------------------------------------------------
1          CREATE  INDEX "IDX_TB22_C1" ON "SYSDBA"."TB22"("C1" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;

达梦云适配中心：
https://eco.dameng.com