openGauss每日一练第 17 天 | 索引管理

学习目标
掌握openGauss DBMS索引的管理：创建索引、删除索引、查询索引的信息、修改索引的信息。

课程学习
索引是一个指向表中数据的指针。一个数据库中的索引与一本书的索引目录是非常相似的。
索引可以用来提高数据库查询性能，但是不恰当的使用将导致数据库性能下降。

课程作业
1.创建表，在表中创建索引

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# create table t1(id int primary key,num serial);
NOTICE:  CREATE TABLE will create implicit sequence "t1_num_seq" for serial column "t1.num"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
omm=# create index idx_t1 on t1(num);
CREATE INDEX
omm=# \di
                 List of relations
 Schema |  Name   | Type  | Owner | Table | Storage 
--------+---------+-------+-------+-------+---------
 public | idx_t1  | index | omm   | t1    | 
 public | t1_pkey | index | omm   | t1    | 
(2 rows)

2.通过hint使用索引

omm=# CREATE TABLE t2
omm-# (
omm(# ca_address_sk integer NOT NULL ,
omm(# ca_address_id character(16),
omm(# ca_street_number character(10) ,
omm(# omm(# ca_street_name character varying(60) ,
ca_street_type character(15) ,
omm(# ca_suite_number character(10) ,
omm(# ca_city character varying(60) ,
omm(# ca_county character varying(30) ,
omm(# ca_state character(2) ,
omm(# ca_zip character(10) ,
omm(# ca_country character varying(20) ,
omm(# ca_gmt_offset numeric(5,2) ,
omm(# ca_location_type character(20)
);omm(# 
omm=# CREATE TABLE

omm=# insert into t2 values
omm-# (1, 'AAAAAAAABAAAAAAA', '18', 'Jackson', 'Parkway', 'Suite 280', 'Fairfield', 'Maricopa County', 'AZ', '86192' ,'United States', -7.00, 'condo'),
omm-# (2, 'AAAAAAAACAAAAAAA', '362', 'Washington 6th', 'RD', 'Suite 80', 'Fairview', 'Taos County', 'NM', '85709', 'United States', -7.00, 'condo'),
omm-# (3, 'AAAAAAAADAAAAAAA', '585', 'Dogwood Washington', 'Circle', 'Suite Q', 'Pleasant Valley', 'York County', 'PA', '12477', 'United States', -5.00, 'single family');
INSERT 0 3
omm=# 
omm=# create index idx_t2 on  t2(ca_address_sk);
CREATE INDEX
omm=# EXPLAIN SELECT /*+ indexscan(t2 idx_t2 ) */  * FROM t2 WHERE ca_address_sk<100;
                            QUERY PLAN                             
-------------------------------------------------------------------
 [Bypass]
 Index Scan using idx_t2 on t2  (cost=0.00..8.27 rows=1 width=788)
   Index Cond: (ca_address_sk < 100)
(3 rows)

3.rename索引

omm=# \di
                 List of relations
 Schema |  Name  | Type  | Owner | Table | Storage 
--------+--------+-------+-------+-------+---------
 public | idx_t2 | index | omm   | t2    | 
(1 row)

omm=# alter index idx_t2 rename to idx_t2_new;
ALTER INDEX
omm=# \di
                   List of relations
 Schema |    Name    | Type  | Owner | Table | Storage 
--------+------------+-------+-------+-------+---------
 public | idx_t2_new | index | omm   | t2    | 
(1 row)

4.重建索引

omm=# alter index idx_t2_new rebuild;
REINDEX
omm=# reindex index idx_t2_new;
REINDEX
omm=# reindex table t2;
REINDEX

5.移动索引到其他表空间

omm=# create tablespace test relative location 'tablespace/test';
omm=# CREATE TABLESPACE

omm=# alter index idx_t2_new set tablespace test;
ALTER INDEX
omm=# select * from pg_indexes where indexname='idx_t2_new';
 schemaname | tablename | indexname  | tablespace |                                 indexdef
                                  
------------+-----------+------------+------------+-----------------------------------------
----------------------------------
 public     | t2        | idx_t2_new | test       | CREATE INDEX idx_t2_new ON t2 USING btre
e (ca_address_sk) TABLESPACE test
(1 row)

6.删除索引

omm=# drop index idx_t2_new;
DROP INDEX