概述

本文档记录openGauss 3.0.0数据库每日一练第17天课程作业，掌握openGauss DBMS索引的管理：创建索引、删除索引、查询索引的信息、修改索引的信息。

课程练习

创建表，在表中创建索引

omm@local:/opt/huawei/tmp [postgres]=#CREATE TABLESPACE day17 RELATIVE LOCATION 'tablespace/day17';
CREATE TABLESPACE
omm@local:/opt/huawei/tmp [postgres]=#CREATE DATABASE musicdb17 WITH TABLESPACE = day17;
CREATE DATABASE
omm@local:/opt/huawei/tmp [postgres]=#CREATE USER user17 IDENTIFIED BY 'zs@123456';
CREATE ROLE
omm@local:/opt/huawei/tmp [postgres]=#ALTER USER user17  SYSADMIN;
ALTER ROLE
omm@local:/opt/huawei/tmp [postgres]=#\c musicdb17 user17
Password for user user17: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb17" as user "user17".
user17@local:/opt/huawei/tmp [musicdb17]=>create table tab_day17(id serial primary key,testnum serial);
NOTICE:  CREATE TABLE will create implicit sequence "tab_day17_id_seq" for serial column "tab_day17.id"
NOTICE:  CREATE TABLE will create implicit sequence "tab_day17_testnum_seq" for serial column "tab_day17.testnum"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tab_day17_pkey" for table "tab_day17"
CREATE TABLE
user17@local:/opt/huawei/tmp [musicdb17]=>create index idx_test_testnum on tab_day17(testnum);
CREATE INDEX
user17@local:/opt/huawei/tmp [musicdb17]=>\di
                        List of relations
 Schema |       Name       | Type  | Owner  |   Table   | Storage 
--------+------------------+-------+--------+-----------+---------
 public | idx_test_testnum | index | user17 | tab_day17 | 
 public | tab_day17_pkey   | index | user17 | tab_day17 | 
(2 rows)

通过hint使用索引

user17@local:/opt/huawei/tmp [musicdb17]=>CREATE TABLE tab_day171
musicdb17-> (
musicdb17(> ca_address_sk integer NOT NULL ,
musicdb17(> ca_address_id character(16),
musicdb17(> ca_street_number character(10) ,
musicdb17(> ca_street_name character varying(60) ,
musicdb17(> ca_street_type character(15) ,
musicdb17(> ca_suite_number character(10) ,
musicdb17(> ca_city character varying(60) ,
musicdb17(> ca_county character varying(30) ,
musicdb17(> ca_state character(2) ,
musicdb17(> ca_zip character(10) ,
musicdb17(> ca_country character varying(20) ,
musicdb17(> ca_gmt_offset numeric(5,2) ,
musicdb17(> ca_location_type character(20)
musicdb17(> );
CREATE TABLE
user17@local:/opt/huawei/tmp [musicdb17]=>insert into tab_day171 values
musicdb17-> (1, 'AAAAAAAABAAAAAAA', '18', 'Jackson', 'Parkway', 'Suite 280', 'Fairfield', 'Maricopa County', 'AZ', '86192' ,'United States', -7.00, 'condo'),
musicdb17-> (2, 'AAAAAAAACAAAAAAA', '362', 'Washington 6th', 'RD', 'Suite 80', 'Fairview', 'Taos County', 'NM', '85709', 'United States', -7.00, 'condo'),
musicdb17-> (3, 'AAAAAAAADAAAAAAA', '585', 'Dogwood Washington', 'Circle', 'Suite Q', 'Pleasant Valley', 'York County', 'PA', '12477', 'United States', -5.00, 'single family');
INSERT 0 3
user17@local:/opt/huawei/tmp [musicdb17]=>create index customer_idx on  tab_day171(ca_address_sk);
CREATE INDEX

--未使用hint                               ^
user17@local:/opt/huawei/tmp [musicdb17]=>EXPLAIN SELECT  * FROM tab_day171 WHERE ca_address_sk<100;
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on tab_day171  (cost=0.00..1.04 rows=1 width=280)
   Filter: (ca_address_sk < 100)
(2 rows)

--使用hint
user17@local:/opt/huawei/tmp [musicdb17]=>EXPLAIN SELECT /*+ indexscan(tab_day171 customer_idx ) */  * FROM tab_day171 WHERE ca_address_sk<100;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 [Bypass]
 Index Scan using customer_idx on tab_day171  (cost=0.00..8.27 rows=1 width=280)
   Index Cond: (ca_address_sk < 100)
(3 rows

rename索引

user17@local:/opt/huawei/tmp [musicdb17]=>\di
                         List of relations
 Schema |       Name       | Type  | Owner  |   Table    | Storage 
--------+------------------+-------+--------+------------+---------
 public | customer_idx     | index | user17 | tab_day171 |   <=======
 public | idx_test_testnum | index | user17 | tab_day17  | 
 public | tab_day17_pkey   | index | user17 | tab_day17  | 
(3 rows)

user17@local:/opt/huawei/tmp [musicdb17]=>ALTER INDEX customer_idx RENAME TO customer_idx_new;
ALTER INDEX
user17@local:/opt/huawei/tmp [musicdb17]=>\di
                         List of relations
 Schema |       Name       | Type  | Owner  |   Table    | Storage 
--------+------------------+-------+--------+------------+---------
 public | customer_idx_new | index | user17 | tab_day171 |   <=======
 public | idx_test_testnum | index | user17 | tab_day17  | 
 public | tab_day17_pkey   | index | user17 | tab_day17  | 
(3 rows)

重建索引

--重建一个单独索引
user17@local:/opt/huawei/tmp [musicdb17]=>ALTER INDEX  customer_idx_new REBUILD;
REINDEX
user17@local:/opt/huawei/tmp [musicdb17]=>\di
                         List of relations
 Schema |       Name       | Type  | Owner  |   Table    | Storage 
--------+------------------+-------+--------+------------+---------
 public | customer_idx_new | index | user17 | tab_day171 | 
 public | idx_test_testnum | index | user17 | tab_day17  | 
 public | tab_day17_pkey   | index | user17 | tab_day17  | 
(3 rows)

user17@local:/opt/huawei/tmp [musicdb17]=>REINDEX INDEX  customer_idx_new;
REINDEX
--重建所有索引
user17@local:/opt/huawei/tmp [musicdb17]=>reindex table tab_day17;
REINDEX

移动索引到其他表空间

--创建索引表空间
user17@local:/opt/huawei/tmp [musicdb17]=> CREATE TABLESPACE myindexday17_ts RELATIVE LOCATION 'tablespace/myindexday17_ts1';
CREATE TABLESPACE
user17@local:/opt/huawei/tmp [musicdb17]=> select * from pg_indexes where tablename = 'tab_day171';
-[ RECORD 1 ]----------------------------------------------------------------------------------------
schemaname | public
tablename  | tab_day171
indexname  | customer_idx_new
tablespace | 
indexdef   | CREATE INDEX customer_idx_new ON tab_day171 USING btree (ca_address_sk) TABLESPACE day17  <============

--移动索引至新表空间
user17@local:/opt/huawei/tmp [musicdb17]=>  ALTER INDEX customer_idx_new SET TABLESPACE myindexday17_ts;
ALTER INDEX
user17@local:/opt/huawei/tmp [musicdb17]=> select * from pg_indexes where tablename = 'tab_day171';
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------
schemaname | public
tablename  | tab_day171
indexname  | customer_idx_new
tablespace | myindexday17_ts
indexdef   | CREATE INDEX customer_idx_new ON tab_day171 USING btree (ca_address_sk) TABLESPACE myindexday17_ts  <============

user17@local:/opt/huawei/tmp [musicdb17]=> select * from pg_indexes where indexname = 'customer_idx_new';
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------
schemaname | public
tablename  | tab_day171
indexname  | customer_idx_new
tablespace | myindexday17_ts
indexdef   | CREATE INDEX customer_idx_new ON tab_day171 USING btree (ca_address_sk) TABLESPACE myindexday17_ts

删除索引

user17@local:/opt/huawei/tmp [musicdb17]=>\di
List of relations
-[ RECORD 1 ]-------------
Schema  | public
Name    | customer_idx_new    <=============
Type    | index
Owner   | user17
Table   | tab_day171
Storage | 
-[ RECORD 2 ]-------------
Schema  | public
Name    | idx_test_testnum
Type    | index
Owner   | user17
Table   | tab_day17
Storage | 
-[ RECORD 3 ]-------------
Schema  | public
Name    | tab_day17_pkey
Type    | index
Owner   | user17
Table   | tab_day17
Storage | 

user17@local:/opt/huawei/tmp [musicdb17]=>\x
Expanded display is off.
user17@local:/opt/huawei/tmp [musicdb17]=>drop index customer_idx_new;
DROP INDEX
user17@local:/opt/huawei/tmp [musicdb17]=>\di
                        List of relations
 Schema |       Name       | Type  | Owner  |   Table   | Storage 
--------+------------------+-------+--------+-----------+---------
 public | idx_test_testnum | index | user17 | tab_day17 | 
 public | tab_day17_pkey   | index | user17 | tab_day17 | 
(2 rows)