openGauss每日一练第17天 | 学习心得体会

学习目标

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

学习内容

通过本章学习了解了索引的管理，hint使用索引，重命名索引，索引的移动表空间，重建索引。

课程作业

1.创建表，在表中创建索引

omm=# drop table if exists student;
NOTICE:  table "student" does not exist, skipping
DROP TABLE
omm=# create table student(id int primary key, name varchar(50) not null, age int default 19);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "student_pkey" for table "student"
CREATE TABLE
omm=# alter table student add column class  bigint;
ALTER TABLE
omm=# \d+ student
                               Table "public.student"
 Column |         Type          | Modifiers  | Storage  | Stats target | Description 
--------+-----------------------+------------+----------+--------------+-------------
 id     | integer               | not null   | plain    |              | 
 name   | character varying(50) | not null   | extended |              | 
 age    | integer               | default 19 | plain    |              | 
 class  | bigint                |            | plain    |              | 
Indexes:
    "student_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

omm=# create index idex_stu_class on student(class);
CREATE INDEX
omm=# \di
                      List of relations
 Schema |      Name      | Type  | Owner |  Table   | Storage 
--------+----------------+-------+-------+----------+---------
 public | customer_idx   | index | omm   | customer | 
 public | idex_stu_class | index | omm   | student  | 
 public | student_pkey   | index | omm   | student  | 
 public | test_pkey      | index | omm   | test     | 
(4 rows)

2.通过hint使用索引

omm=#  insert into student (id,name,class) values (10001,'测试0001',10001),(10002,'测试0002',10001),(10003,'测试0003',10001);
INSERT 0 3
omm=# EXPLAIN SELECT /*+ indexscan(student  idex_stu_class) */ * FROM student  WHERE class=1001;
omm=#                                    QUERY PLAN                                    
---------------------------------------------------------------------------------
 [Bypass]
 Index Scan using idex_stu_class on student  (cost=0.00..12.29 rows=2 width=134)
   Index Cond: (class = 1001)
(3 rows)

3.rename索引

omm=# \d student
           Table "public.student"
 Column |         Type          | Modifiers  
--------+-----------------------+------------
 id     | integer               | not null
 name   | character varying(50) | not null
 age    | integer               | default 19
omm=#  class  | bigint                | 
Indexes:
    "student_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
    "idex_stu_class" btree (class) TABLESPACE pg_default


omm=# ALTER INDEX idex_stu_class RENAME TO idex_stu_class_new;
ALTER INDEX
omm=# \d student
           Table "public.student"
 Column |         Type          | Modifiers  
--------+-----------------------+------------
 id     | integer               | not null
 name   | character varying(50) | not null
 age    | integer               | default 19
 class  | bigint                | 
Indexes:
    "student_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
    "idex_stu_class_new" btree (class) TABLESPACE pg_default

4.重建索引

omm=# --重建单独有两种方式：
omm=# ALTER INDEX idex_stu_class_new REBUILD;
REINDEX
omm=# REINDEX INDEX idex_stu_class_new;
REINDEX
omm=# --重建所有索引
omm=# reindex table student;
REINDEX
omm=#

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

omm=# --创建表空间：
omm=# CREATE TABLESPACE tb_myindex RELATIVE LOCATION 'tablespace/tb_myindex';
CREATE TABLESPACE
omm=# 
omm=# --将索引移动到表空间tb_myindex ：
omm=# ALTER INDEX idex_stu_class_new SET TABLESPACE tb_myindex;
ALTER INDEX
omm=# 
omm=# omm=# --查看索引所在的表空间
select * from pg_indexes where tablename = 'student';
 schemaname | tablename |     indexname      | tablespace |                                       indexdef              
                         
------------+-----------+--------------------+------------+-------------------------------------------------------------
 public     | student   | student_pkey       |            | CREATE UNIQUE INDEX student_pkey ON student USING btree (id)
 TABLESPACE pg_default
 public     | student   | idex_stu_class_new | tb_myindex | CREATE INDEX idex_stu_class_new ON student USING btree (clas
s) TABLESPACE tb_myindex
(2 rows)
omm=# select * from pg_indexes where indexname = 'idex_stu_class_new';
 schemaname | tablename |     indexname      | tablespace |                                       indexdef              
                         
------------+-----------+--------------------+------------+-------------------------------------------------------------
 public     | student   | idex_stu_class_new | tb_myindex | CREATE INDEX idex_stu_class_new ON student USING btree (clas
s) TABLESPACE tb_myindex
(1 row)

6.删除索引

omm=# drop index idex_stu_class_new;
DROP INDEX