达梦数据库系列—6.索引管理

目录

1、管理索引的准则

1.1索引正确的表和列

1.2合适的组合索引

1.3限制每个表的索引的数量

1.4为每个索引指定表空间

2、创建索引

2.1创建聚集索引

2.2创建唯一索引

2.3创建函数索引

2.4创建位图索引

3、SQL 语句中使用索引

4、重建索引

5、删除索引

6、查看索引信息

---

1、管理索引的准则

1.1索引正确的表和列

1.如果需要经常地检索大表中的少量的行，就为查询键创建索引；

2.为了改善多个表的连接的性能，可为连接列创建索引；

3.主键和唯一键自动具有索引；

4.小表不需要索引。

1.2合适的组合索引

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

当两个或多个字段都是等值查询时，组合索引中各个列的前后关系是无关紧要的。但是如果是非等值查询时，要想有效利用组合索引，则应该按等值字段在前，非等值字段在后的原则创建组合索引。

1.3限制每个表的索引的数量

如果一个表主要仅用于读，则索引多就有好处；如果一个表经常被更新，则索引不宜多建。索引越多，修改表数据的开销就越大。

1.4为每个索引指定表空间

将表及其索引放在不同的表空间（在不同磁盘上）产生的性能比放在相同的表空间更好，因为这样做减少了磁盘竞争。

2、创建索引

2.1创建聚集索引

DM8 中表（列存储表和堆表除外）都是使用 B 树索引结构管理的，每一个普通表都有且仅有一个聚集索引，当建表语句未指定聚集索引键时，DM8 的默认聚集索引键是 ROWID。若指定索引键，表中数据都会根据指定索引键排序。

建表后，DM8 也可以用创建新聚集索引的方式来重建表数据，并按新的聚集索引排序。

CREATE CLUSTER INDEX clu_emp_name ON emp(ename);

新建聚集索引会重建这个表以及其所有索引，包括二级索引、函数索引，是一个代价非常大的操作。因此，最好在建表时就确定聚集索引键。

2.2创建唯一索引

唯一索引可以保证表上不会有两行数据在键列上具有相同的值。

CREATE UNIQUE INDEX dept_unique_index ON dept (dname) STORAGE (ON users);

2.3创建函数索引

基于对表中列进行计算后的结果创建索引。如果没有函数索引，那么任何在列上执行了函数的查询都不能使用这个列的索引。

1.函数索引创建时要和使用时保持一致

2.不建议使用自定义函数

3.表达式不支持集函数和不确定函数，不确定函数为每次执行得到的结果不确定，系统中不确定函数包括：RAND、SOUNDEX、CURDATE、CURTIME、CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP、GETDATE、NOW、SYSDATE、CUR_DATABASE、DBID、EXTENT、PAGE、SESSID、UID、USER、VSIZE、SET_TABLE_OPTION、SET_INDEX_OPTION、UNLOCK_LOGIN、CHECK_LOGIN、GET_AUDIT、CFALGORITHMSENCRYPT、SF_MAC_LABEL_TO_CHAR、CFALGORITHMSDECRYPT、BFALGORITHMSENCRYPT、SF_MAC_LABEL_FROM_CHAR、BFALGORITHMSDECRYPT、SF_MAC_LABEL_CMP；

举例说明：

SQL> create table test.t1 as select * from "DMHR"."EMPLOYEE";

操作已执行

已用时间: 20.757(毫秒). 执行号:901.

SQL> EXPLAIN select * from test.t1 where to_char(HIRE_DATE,'yyyy-mm-dd') ='2012-03-27';


1   #NSET2: [1, 21, 285]

2     #PRJT2: [1, 21, 285]; exp_num(12), is_atom(FALSE)

3       #SLCT2: [1, 21, 285]; exp11 = '2012-03-27'

4         #CSCN2: [1, 856, 285]; INDEX33555625(T1); btr_scan(1)


已用时间: 1.687(毫秒). 执行号:0.

SQL> create index idx_func_t1 on TEST.T1(to_char(HIRE_DATE,'yyyy-mm-dd'));

操作已执行

已用时间: 17.666(毫秒). 执行号:901.

SQL> EXPLAIN select * from test.t1 where to_char(HIRE_DATE,'yyyy-mm-dd') ='2012-03-27';


1   #NSET2: [1, 21, 285]

2     #PRJT2: [1, 21, 285]; exp_num(12), is_atom(FALSE)

3       #BLKUP2: [1, 21, 285]; IDX_FUNC_T1(T1)

4         #SSEK2: [1, 21, 285]; scan_type(ASC), IDX_FUNC_T1(T1), scan_range['2012-03-27','2012-03-27']


已用时间: 1.605(毫秒). 执行号:0.

SQL> 

以上可见，建了函数索引idx_func_t1为to_char(HIRE_DATE,'yyyy-mm-dd')后，再次查询，执行计划走索引idx_func_t1，查询效率有提升。

2.4创建位图索引

位图索引主要针对含有大量相同值的列而创建。

CREATE BITMAP INDEX S1 ON PURCHASING.VENDOR (VENDORID);

1.不支持对大字段创建位图索引；

2.不支持对存在 CLUSTER KEY 的表创建位图索引；

3.MPP 环境下不支持位图索引的创建；

4.不支持全局位图索引；

3、SQL 语句中使用索引

如果 SQL 语句想使用某一个索引，则SQL 语句的查询项和 WHERE 子句的过滤项中必须包含该索引的全部索引键。

CREATE TABLE T2 (ID int,NAME varchar(20),DEPARTMENT varchar(20),SALARY INT);

CREATE CLUSTER INDEX S21 on T2(ID); //聚集索引

CREATE INDEX S22 on T2(NAME,DEPARTMENT);

CREATE INDEX S23 on T2(NAME);

CREATE INDEX S24 on T2(DEPARTMENT);

例 1 使用聚集索引 S21

EXPLAIN SELECT SALARY FROM T2 WHERE ID=10;

例 2 使用非聚集索引 S22

EXPLAIN SELECT DEPARTMENT FROM T2 WHERE NAME LIKE'%Zhang' AND DEPARTMENT='A部门';

EXPLAIN SELECT DEPARTMENT FROM T2 WHERE NAME='Zhang San';

举例说明：

SQL> CREATE TABLE test.T2 (ID int,NAME varchar(20),DEPARTMENT varchar(20),SALARY INT);

操作已执行

已用时间: 7.072(毫秒). 执行号:902.

SQL> insert into test.t2 select employee_id,employee_name,identity_card,salary from test.t1;

影响行数 856


已用时间: 2.495(毫秒). 执行号:903.

SQL> commit;

操作已执行

已用时间: 1.654(毫秒). 执行号:904.

SQL> set schema test;

操作已执行

已用时间: 0.302(毫秒). 执行号:0.

SQL> CREATE CLUSTER INDEX S21 on T2(ID);

操作已执行

已用时间: 62.835(毫秒). 执行号:906.

SQL> CREATE INDEX S22 on T2(NAME,DEPARTMENT);

操作已执行

已用时间: 12.905(毫秒). 执行号:907

SQL> CREATE INDEX S23 on T2(NAME);

操作已执行

已用时间: 12.786(毫秒). 执行号:908.

SQL> CREATE INDEX S24 on T2(DEPARTMENT);

操作已执行

已用时间: 13.661(毫秒). 执行号:909.

SQL> EXPLAIN SELECT SALARY FROM T2 WHERE ID=1001;


1   #NSET2: [1, 21, 8]

2     #PRJT2: [1, 21, 8]; exp_num(1), is_atom(FALSE)

3       #CSEK2: [1, 21, 8]; scan_type(ASC), S21(T2), scan_range[1001,1001]


已用时间: 1.061(毫秒). 执行号:0.

SQL> EXPLAIN SELECT DEPARTMENT FROM T2 WHERE NAME LIKE'马%' AND DEPARTMENT='340102196202303000';


1   #NSET2: [1, 1, 96]

2     #PRJT2: [1, 1, 96]; exp_num(1), is_atom(FALSE)

3       #SLCT2: [1, 1, 96]; T2.DEPARTMENT = '340102196202303000'

4         #SSEK2: [1, 1, 96]; scan_type(ASC), S22(T2), scan_range[('马',min),('驭',min))


已用时间: 0.697(毫秒). 执行号:0.

SQL> EXPLAIN SELECT DEPARTMENT FROM T2 WHERE NAME LIKE'马%' ;


1   #NSET2: [1, 32, 96]

2     #PRJT2: [1, 32, 96]; exp_num(1), is_atom(FALSE)

3       #SSEK2: [1, 32, 96]; scan_type(ASC), S22(T2), scan_range[('马',min),('驭',min))


已用时间: 0.736(毫秒). 执行号:0.

SQL> EXPLAIN SELECT * FROM T2 WHERE NAME LIKE '马%';


1   #NSET2: [1, 32, 116]

2     #PRJT2: [1, 32, 116]; exp_num(5), is_atom(FALSE)

3       #BLKUP2: [1, 32, 116]; S23(T2)

4         #SSEK2: [1, 32, 116]; scan_type(ASC), S23(T2), scan_range['马','驭')


已用时间: 0.621(毫秒). 执行号:0.

4、重建索引

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

SP_REBUILD_INDEX(SCHEMA_NAME varchar(256), INDEX_ID int);

生产环境建议使用 online 方式重建，不影响表的 DML 操作:

alter index HRTEST.IX_EMP01_EMPLOYEENAME rebuild ONLINE;

5、删除索引

DROP INDEX IF EXISTS emp_ename;

不能直接删除与已启用的 UNIQUE KEY 键或 PRIMARY KEY 键约束相关的索引。要删除一个与约束相关的索引，必须停用或删除该约束本身。

ALTER TABLE emp DROP CONSTRAINT pk_emp_name;

6、查看索引信息

查看索引的定义  

SELECT INDEXDEF(INDEX_ID int, PREFLAG int);

INDEX_ID 为索引 ID，PREFLAG 表示返回信息中是否增加模式名前缀，0或1。

SQL> SELECT INDEXDEF(33555645, 1);


行号     INDEXDEF(33555645,1)                                                                                  

---------- ------------------------------------------------------------------------------------------------------

1          CREATE OR REPLACE CLUSTER  INDEX "TEST"."S21" ON "TEST"."T2"("ID" ASC) STORAGE(ON "TBS", CLUSTERBTR) ;


已用时间: 1.777(毫秒). 执行号:913.

数据字典：

select * from dba_indexes t where t.OWNER ='HRTEST';

select * from dba_ind_columns t where t.index_OWNER ='HRTEST';

--索引的有效性和可见性（只有有效且可见的索引，执行计划才会选择走该索引）

select t.INDEX_NAME, t.INDEX_TYPE, t.TABLE_NAME, t.TABLE_OWNER, t.status,

t.VISIBILITY from user_indexes t; 

达梦技术社区：https://eco.dameng.com/