openGauss每日一练第9天 | 数据库普通索引的操作

openGauss第9练，普通索引的学习；

主要的相关快键指令：

\d+ 表名:可查看表信息及其索引信息； 也可通过查询pg_indexes 视图查看索引信息；

比较让我深刻的是创建部分索引可以用where:

CREATE UNIQUE INDEX ds_ship_mode_t1_index3 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK) WHERE SM_SHIP_MODE_SK>10;

下列是官方引用的一些索引事项：https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/CREATE-INDEX.html

索引可以用来提高数据库查询性能，但是不恰当的使用将导致数据库性能下降。建议仅在匹配如下某条原则时创建索引：

• 经常执行查询的字段。
• 在连接条件上创建索引，对于存在多字段连接的查询，建议在这些字段上建立组合索引。例如，select * from t1 join t2 on t1.a=t2.a and t1.b=t2.b，可以在t1表上的a，b字段上建立组合索引。
• where子句的过滤条件字段上（尤其是范围条件）。
• 在经常出现在order by、group by和distinct后的字段。

语法格式

• 在表上创建索引。

  复制代码CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [schema_name.]index_name ] ON table_name [ USING method ]
      ({ { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] }[, ...] )
      [ WITH ( {storage_parameter = value} [, ... ] ) ]
      [ TABLESPACE tablespace_name ]
      [ WHERE predicate ];
  

• 在分区表上创建索引。

  复制代码CREATE [ UNIQUE ] INDEX [ [schema_name.]index_name ] ON table_name [ USING method ]
      ( {{ column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] }[, ...] )
      [ LOCAL [ ( { PARTITION index_partition_name [ TABLESPACE index_partition_tablespace ] } [, ...] ) ] | GLOBAL ]
      [ WITH ( { storage_parameter = value } [, ...] ) ]
      [ TABLESPACE tablespace_name ];
  

课程作业

1.创建表products, 分别为表创建一个unique索引1，指定b-tree索引2和表达式索引3

openGauss=# create schema will;
CREATE SCHEMA
openGauss=# create table will.products (id int);
CREATE TABLE
openGauss=# create unique index p_index1 on will.products(id);
CREATE INDEX
openGauss=# create index p_index2 on will.products using btree(id);
CREATE INDEX
openGauss=# create index p_index3 on will.products(substr(id,1,4));
CREATE INDEX
openGauss=# \d+ will.p_index2;
          Index "will.p_index2"
 Column |  Type   | Definition | Storage 
--------+---------+------------+---------
 id     | integer | id         | plain
btree, for table "will.products"

openGauss=# \d+ will.products;
                        Table "will.products"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id     | integer |           | plain   |              | 
Indexes:
    "p_index1" UNIQUE, btree (id) TABLESPACE pg_default
    "p_index2" btree (id) TABLESPACE pg_default
    "p_index3" btree (substr(id::text, 1, 4)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

openGauss=# 

2.设置索引1不可用，修改索引2的表空间，重命名索引3

openGauss=# create tablespace tbs_will relative location 'tbs/tbswill';
CREATE TABLESPACE
openGauss=# alter index will.p_index1 unusable;
ALTER INDEX
openGauss=# alter index will.p_index2 set tablespace tbs_will;
ALTER INDEX                                           ^
openGauss=# alter index will.p_index3 rename to p_index30;
ALTER INDEX
openGauss=# \d+ will.products;
                        Table "will.products"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id     | integer |           | plain   |              | 
Indexes:
    "p_index1" UNIQUE, btree (id) TABLESPACE pg_default UNUSABLE
    "p_index2" btree (id) TABLESPACE tbs_will, tablespace "tbs_will"
    "p_index30" btree (substr(id::text, 1, 4)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

3.重建索引2和products的所有索引

openGauss=# reindex index will.p_index2;
REINDEX
openGauss=# reindex table will.products;
REINDEX
openGauss=# 

4.使用\d+和系统视图pg_indexes查看索引信息

openGauss=# \d+ will.products;
                        Table "will.products"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id     | integer |           | plain   |              | 
Indexes:
    "p_index1" UNIQUE, btree (id) TABLESPACE pg_default UNUSABLE
    "p_index2" btree (id) TABLESPACE tbs_will, tablespace "tbs_will"
    "p_index30" btree (substr(id::text, 1, 4)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

openGauss=# select * from pg_indexes where tablename='products';
 schemaname | tablename | indexname | tablespace |                                               indexdef                                               
------------+-----------+-----------+------------+------------------------------------------------------------------------------------------------------
 will       | products  | p_index1  |            | CREATE UNIQUE INDEX p_index1 ON will.products USING btree (id) TABLESPACE pg_default
 will       | products  | p_index2  | tbs_will   | CREATE INDEX p_index2 ON will.products USING btree (id) TABLESPACE tbs_will
 will       | products  | p_index30 |            | CREATE INDEX p_index30 ON will.products USING btree (substr((id)::text, 1, 4)) TABLESPACE pg_default
(3 rows)

openGauss=# 

5.删除索引、表和表空间

drop index p_index1;
drop index p_index2;
drop index p_index3;
drop table products;
drop tablespace tbs_will;