达梦数据库DCP-索引管理_达梦数据库 强制走索引sql

千里之行始于足下，梦想不付之行动，终究是纸上谈兵

经过一段时间的达梦DCP培训让学习了好多支持点。让我对达梦的理解更加深刻。

今天为大家说一说达梦数据的索引管理

索引概念

/*索引是与表相关的一种结构，它能使对应于表的SQL语句执行的更快，因为索引能快速定位数据库。
DM7索引能提供访问表的数据的更快路径，可以不用重写任何查询二使用索引，其查询结果与不使用索引是一样的，但速度更快。

达梦数据库提供了几种最常见类型的索引，对不同场景有不同的功能。
1、聚集索引：每个普通表有且只有一个聚集索引。注意：在建表的时候要把主键选好，尽量不要在对数据量非常大的表，建立聚集索引。
2、唯一索引：索引数据根据索引建唯一。注意：唯一索引可以保证表上不会有两行数据在键列上具有相同的值
3、函数索引：包含函数/表达式的预先计算的值。
4、位图索引：对低基数的列创建位图索引。 注意：某一列值重复值比较多可以创建位图索引
5、复合索引：在表中用两个或者两个以上的列作为索引。
6、位图连接索引：针对两个或者多个表连接的位图索引，主要用于数据仓库中。
7、全文索引：在表的文本列上创建索引。

索引需要存储空间。建立或删除一个索引，不会影响基本表、数据库应用或其他索引。
一个索引可以对应数据表的一个或者多个字段，每个字段设置索引结果排序方式，默认为安字段值递增排序（ASC），也可以为递增排序（DESC）。
当插入、更改或者删除相关的表的行时，DM7会自动管理索引。如果删除索引，所有的应用仍然继续工作，单访问数据速度会变慢。

索引可以提高数据的查询效率，但也需要注意，索引会降低某些命令的执行效率，如INSERT、UPDATE、DELETE的性能，因为DM不仅需要维护基表数据还要维护索引数据。

*/

**--举例:创建一张T6表并10万行插入数据**

CREATE TABLE TEST.T6 (ID INT,NAME VARCHAR(20) ,age int)storage (on TBS1);

--如未创建，则可以执行如下命令：

SP_CREATE_SYSTEM_PACKAGES (1); --在产生随机数据的过程中需要调用DBMS_RANDOM系统包

BEGIN
FOR I IN 1..100000 LOOP
INSERT INTO TEST.T6 VALUES (I,dbms_random.string('1',TRUNC((dbms_random.VALUE(3,8)))),TRUNC((dbms_random.VALUE(0,100))));
COMMIT;
END LOOP;
END; --循环插入10万条纪律

explain select id,name from test.T6 WHERE ID<=200 ;--查看执行计划
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE INDEX T6_ID_INDEX ON TEST.T6(ID)STORAGE (ON TBS1); --创建T6表的二级索引并放在TBS1表空间中。

explain select id,name from test.T6 WHERE ID<=200 ;--查看执行计划

--CSCN2:聚集索引扫描(全表扫描)
--SLCT2:关系的“选择”（select）运算，用于查询条件的过滤
--PRJT2:关系的“投影”(project)运算，用于选择表达式项的计算
--NSET2:结果集(result set)收集，一般是查询计划的顶层节点

--发现执行计划没有变动。更新统计信息。

DBMS_STATS.GATHER_TABLE_STATS('TEST','T6') ;--更新T6表的统计信息。

explain select id,name from test.T6 WHERE ID<=200 ;--查看执行计划

--发现执行计划已经刚才已经创建的索引了
--SSEK2：二级索引数据定位
--BLKUP2：定位查找
--PRJT2：关系的“投影”运算，用于选择表达式项的计算
--NSET2:结果集(result set)收集，一般是查询计划的顶层节点


--如果索引不生效。可以强制SQL语句走创建的索引。命令如下：

 explain select name from test.t6  INDEX T6_ID_INDEX WHERE T6.ID<=200;
 
 --一般情况下不建议使用。因为达梦数据库优化器会自动的优化数据库索引，会自动根据那个索引快走那个索引。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

创建唯一索引

在创建表的时候，有主键约束或者唯一约束达梦数据库会自动创建索引。

CREATE UNIQUE INDEX  H2_ID_INDEX ON TEST.H2(ID) STORAGE (ON TBS1); 
--给H2这张表创建了一个唯一索引，并放在TSB1表空间。
1
2

–**

创建函数索引*

*（也可以自定函数）

CREATE TABLE TEST.T7 AS SELECT * FROM DMHR.EMPLOYEE;--创建一张表。

CREATE INDEX T7_UPPER_INDEX ON TEST.T7 (UPPER(EMAIL)); --创建函数索引。

DBMS_STATS.GATHER_TABLE_STATS('TEST','T7') ;--更新T7表的统计信息。

 EXPLAIN SELECT UPPER(T7.EMAIL) FROM TEST.T7; --查看执行计划是否走索引。

--单行函数
--字符函数：initcap、lower、upper、trim、replace 、instr、substr 等
--数据函数： round 、trunc、 mod 等
--日期函数： add_months、mounths_between、netx_day 、last_day 等
--聚合函数： sum、 avg、count、max、min 等
1
2
3
4
5
6
7
8
9
10
11
12
13

–**

复合索引**

复合索引由表中的多个列构成的，注意：先后顺序不同执行效率不一样。

CREATE INDEX ID_NAME_INDEX ON TEST.T7 (EMPLOYEE_ID,EMPLOYEE_NAME) STORAGE (ON TBS1); --创建复合索引

DBMS_STATS.GATHER_TABLE_STATS('TEST','T7') ;--更新T7表的统计信息。

 EXPLAIN select * from test.t7 where T7.EMPLOYEE_NAME='强洁芳';  --查看执行计划。
1
2
3
4
5

–**

位图索引**

CREATE BITMAP INDEX BIT_INDEX ON TEST.T7 (DEPARTMENT_ID);  --创建位图索引

DBMS_STATS.GATHER_TABLE_STATS('TEST','T7') ;--更新T7表的统计信息。

EXPLAIN SELECT * FROM TEST.T7 WHERE T7.DEPARTMENT_ID>=101 ;
--查看执行计划，发现没有走位图索引，走的全表扫描。因为优化器发现这张表走聚集索引比位图索引快
1
2
3
4
5
6

–**

维护索引**

–更新：对数据库做DML操作的时候，会产生碎片。这个时候就需要更新索引。
注意：不支持位图索引更新

ALTER INDEX TEST.ID_NAME_INDEX REBUILD ;--更新上面的复合索引

ALTER INDEX TEST.T7_UPPER_INDEX REBUILD; --更新上面的函数索引。

--注意使用rebuild 可能会锁表。需要在rebuild后面添加 online；

ALTER INDEX TEST.T7_UPPER_INDEX REBUILD ONLINE;
1
2
3
4
5
6
7

-删除索引

DROP INDEX TEST.T7_UPPER_INDEX ;  --删除索引
1

分区索引

分区索引：是表分区上的索引，在该表上的索引就是分区索引，非堆表每个分区一个索引（局部索引）。堆表可以创建全局索引，也可以创建局部索引。
/*
在创建索引的时候指定GLOBAL关键字则建立的索引就是全局索引。那么每个分区表的数据都被索引在同一个B树当中。
目前在达梦里面堆表的水平分区支持全局索引的。在堆表上创建主键 primary key 会自动变成全局索引。
*/

--例：创建一个水平分区堆表，创建全局索引。

CREATE TABLE TEST.T8 (ID INT,NAME VARCHAR(20))
PARTITION BY RANGE (ID)
(PARTITION P1 VALUES LESS THAN (1000),
PARTITION P2 VALUES LESS THAN (2000),
PARTITION P3 VALUES LESS THAN (MAXVALUE)) STORAGE (NOBRANCH); --创建一个水平分区的堆表。

CREATE INDEX T8_ID_INDEX ON TEST.T8(ID) GLOBAL; --创建堆表的全局索引。

--注意：达梦的索引组织表不支持全局索引。达梦数据库仅堆表支持全局索引。
1
2
3
4
5
6
7
8
9
10
11

通过系统视图查看索引名称和ID号

SELECT * FROM sysobjects WHERE NAME = 'T8_ID_INDEX' --查看索引的ID号
1

查看索引信息的函数

INDEX_USED_SPACE  --得到索引占用空间的大小。

INDEX_USED_PAGES  --索引实际使用的页数目。

sp_rebuild_index  --重建按索引的系统函数。

*/


SELECT USER_USED_SPACE('TEST');--查看用户占用的空间

SELECT INDEX_USED_SPACE(33555638);
1
2
3
4
5
6
7
8
9
10
11
12