openGauss每日一练第9天 | 普通表索引

原创 Sean 2021-12-09

267

学习目标

学习openGauss普通表索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息

课程学习实操

#第一次进入等待15秒 #数据库启动中... su - omm gsql -r

复制

1.创建索引

create schema tpcds;
CREATE TABLE tpcds.ship_mode_t1
(
SM_SHIP_MODE_SK INTEGER NOT NULL,
SM_SHIP_MODE_ID CHAR(16) NOT NULL,
SM_TYPE CHAR(30),
SM_CODE CHAR(10),
SM_CARRIER CHAR(20),
SM_CONTRACT CHAR(20)
);

omm=# create schema tpcds;
CREATE SCHEMA
omm=# omm-# (
omm(# CREATE TABLE tpcds.ship_mode_t1
SM_SHIP_MODE_SK INTEGER NOT NULL,
omm(# SM_SHIP_MODE_ID CHAR(16) NOT NULL,
omm(# SM_TYPE CHAR(30),
SM_CODE CHAR(10),
omm(# omm(# SM_CARRIER CHAR(20),
omm(# );SM_CONTRACT CHAR(20)
omm(# 
CREATE TABLE
omm=# 
复制

– SM_SHIP_MODE_SK字段上创建普通的唯一索引

CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK);
omm=# CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK);
CREATE INDEX
omm=# 
复制

– SM_SHIP_MODE_SK字段上创建指定B-tree索引。

CREATE INDEX ds_ship_mode_t1_index4 ON tpcds.ship_mode_t1 USING btree(SM_SHIP_MODE_SK);
omm=# CREATE INDEX ds_ship_mode_t1_index4 ON tpcds.ship_mode_t1 USING btree(SM_SHIP_MODE_SK);
CREATE INDEX
omm=# 
复制

–SM_CODE字段上创建表达式索引

CREATE INDEX ds_ship_mode_t1_index2 ON tpcds.ship_mode_t1(SUBSTR(SM_CODE,1 ,4));
omm=# CREATE INDEX ds_ship_mode_t1_index2 ON tpcds.ship_mode_t1(SUBSTR(SM_CODE,1 ,4));
CREATE INDEX
omm=# 
复制

– SM_SHIP_MODE_SK字段上创建SM_SHIP_MODE_SK大于10的部分索引

CREATE UNIQUE INDEX ds_ship_mode_t1_index3 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK) WHERE SM_SHIP_MODE_SK>10;
omm=# CREATE UNIQUE INDEX ds_ship_mode_t1_index3 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK) WHERE SM_SHIP_MODE_SK>10;
CREATE INDEX
omm=# 
复制

–查看表信息

\d+ tpcds.ship_mode_t1
omm=# \d+ tpcds.ship_mode_t1
                             Table "tpcds.ship_mode_t1"
     Column      |     Type      | Modifiers | Storage  | Stats target | Description 
-----------------+---------------+-----------+----------+--------------+-------------
 sm_ship_mode_sk | integer       | not null  | plain    |              | 
 sm_ship_mode_id | character(16) | not null  | extended |              | 
 sm_type         | character(30) |           | extended |              | 
 sm_code         | character(10) |           | extended |              | 
 sm_carrier      | character(20) |           | extended |              | 
 sm_contract     | character(20) |           | extended |              | 
Indexes:
    "ds_ship_mode_t1_index1" UNIQUE, btree (sm_ship_mode_sk) TABLESPACE pg_default
    "ds_ship_mode_t1_index3" UNIQUE, btree (sm_ship_mode_sk) TABLESPACE pg_default WHERE sm_ship_mode_sk > 10
omm=#     "ds_ship_mode_t1_index2" btree (substr(sm_code::text, 1, 4)) TABLESPACE pg_default
    "ds_ship_mode_t1_index4" btree (sm_ship_mode_sk) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no


omm=# 
复制

–查看系统视图pg_indexes

select * from pg_indexes where tablename = 'ship_mode_t1';
omm=# select * from pg_indexes where tablename = 'ship_mode_t1';
 schemaname |  tablename   |       indexname        | tablespace |                                                            
         indexdef                                                                      
------------+--------------+------------------------+------------+------------------------------------------------------------
---------------------------------------------------------------------------------------
 tpcds      | ship_mode_t1 | ds_ship_mode_t1_index1 |            | CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON tpcds.ship_mo
de_t1 USING btree (sm_ship_mode_sk) TABLESPACE pg_default
 tpcds      | ship_mode_t1 | ds_ship_mode_t1_index4 |            | CREATE INDEX ds_ship_mode_t1_index4 ON tpcds.ship_mode_t1 U
SING btree (sm_ship_mode_sk) TABLESPACE pg_default
 tpcds      | ship_mode_t1 | ds_ship_mode_t1_index2 |            | CREATE INDEX ds_ship_mode_t1_index2 ON tpcds.ship_mode_t1 U
SING btree (substr((sm_code)::text, 1, 4)) TABLESPACE pg_default
 tpcds      | ship_mode_t1 | ds_ship_mode_t1_index3 |            | CREATE UNIQUE INDEX ds_ship_mode_t1_index3 ON tpcds.ship_mo
de_t1 USING btree (sm_ship_mode_sk) TABLESPACE pg_default WHERE (sm_ship_mode_sk > 10)
(4 rows)

omm=# 
复制

2.修改索引定义

–重命名索引

ALTER INDEX tpcds.ds_ship_mode_t1_index1 RENAME TO ds_ship_mode_t1_index5;
omm=# ALTER INDEX tpcds.ds_ship_mode_t1_index1 RENAME TO ds_ship_mode_t1_index5;
ALTER INDEX
omm=# 
复制

–设置索引不可用

ALTER INDEX tpcds.ds_ship_mode_t1_index2 UNUSABLE;
omm=# ALTER INDEX tpcds.ds_ship_mode_t1_index2 UNUSABLE;
ALTER INDEX
omm=# 
复制

–修改索引表空间

CREATE TABLESPACE example0 RELATIVE LOCATION 'tablespace1/tablespace_0';
alter index tpcds.ds_ship_mode_t1_index4 set tablespace example0;
\d+ tpcds.ship_mode_t1;
omm=# CREATE TABLESPACE example0 RELATIVE LOCATION 'tablespace1/tablespace_0';
CREATE TABLESPACE
omm=# alter index tpcds.ds_ship_mode_t1_index4 set tablespace example0;
ALTER INDEXomm=# \d+ tpcds.ship_mode_t1;
                             Table "tpcds.ship_mode_t1"
     Column      |     Type      | Modifiers | Storage  | Stats target | Description 
-----------------+---------------+-----------+----------+--------------+-------------
 sm_ship_mode_sk | integer       | not null  | plain    |              | 
 sm_ship_mode_id | character(16) | not null  | extended |              | 
 sm_type         | character(30) |           | extended |              | 
 sm_code         | character(10) |           | extended |              | 
 sm_carrier      | character(20) |           | extended |              | 
 sm_contract     | character(20) |           | extended |              | 
Indexes:
    "ds_ship_mode_t1_index3" UNIQUE, btree (sm_ship_mode_sk) TABLESPACE pg_default WHERE sm_ship_mode_sk > 10
    "ds_ship_mode_t1_index5" UNIQUE, btree (sm_ship_mode_sk) TABLESPACE pg_default
    "ds_ship_mode_t1_index2" btree (substr(sm_code::text, 1, 4)) TABLESPACE pg_default
    "ds_ship_mode_t1_index4" btree (sm_ship_mode_sk) TABLESPACE example0, tablespace "example0"
Has OIDs: no
Options: orientation=row, compression=no

omm=# 

复制

3.重建索引

–重建一个单独索引

ALTER INDEX tpcds.ds_ship_mode_t1_index2 REBUILD;
REINDEX INDEX tpcds.ds_ship_mode_t1_index4;
omm=# ALTER INDEX tpcds.ds_ship_mode_t1_index2 REBUILD;
REINDEX
omm=# REINDEX INDEX tpcds.ds_ship_mode_t1_index4;
REINDEX
omm=# 
复制

–重建所有索引

reindex table tpcds.ship_mode_t1;
omm=# reindex table tpcds.ship_mode_t1;
REINDEX
omm=# 
复制

4.删除索引

DROP INDEX tpcds.ds_ship_mode_t1_index2;
DROP INDEX tpcds.ds_ship_mode_t1_index3;
DROP INDEX tpcds.ds_ship_mode_t1_index4;
DROP INDEX tpcds.ds_ship_mode_t1_index5;omm=# DROP INDEX tpcds.ds_ship_mode_t1_index2;
DROP INDEX
omm=# DROP INDEX tpcds.ds_ship_mode_t1_index3;
DROP INDEX
omm=# DROP INDEX tpcds.ds_ship_mode_t1_index4;
DROP INDEX
omm=# DROP INDEX tpcds.ds_ship_mode_t1_index5;
DROP INDEX
omm=# 

复制

课程作业

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

omm=# create table products
omm-# (product_id int,
omm(#  product_name varchar(30),
omm(#  prod_desc varchar(100),
omm(#  price    numeric(10,2)
omm(#  );
CREATE TABLE
omm=# create unique index uqx_products_id_index1 on products(product_id);
CREATE INDEX
omm=#  create index idx_products_name_index2 on products using btree(product_name);
CREATE INDEX
omm=# create index fx_products_desc_index3 on products(substr(prod_desc,1,20));
CREATE INDEX
omm=# 
复制

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

omm=# alter index uqx_products_id_index1 unusable;
ALTER INDEX
omm=# 
omm=#  alter index idx_products_name_index2 set tablespace example0;
ALTER INDEX
omm=# 
omm=# alter index fx_products_desc_index3 rename to fx_products_desc_index3_n;
ALTER INDEX
omm=# 

复制

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

omm=# alter index idx_products_name_index2 rebuild;
REINDEX
omm=# reindex index idx_products_name_index2;
REINDEX
omm=#  reindex table products;
REINDEX
omm=# 
复制

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

omm=# \d+ products
                                  Table "public.products"
    Column    |          Type          | Modifiers | Storage  | Stats target | Description 
--------------+------------------------+-----------+----------+--------------+-------------
 product_id   | integer                |           | plain    |              | 
 product_name | character varying(30)  |           | extended |              | 
 prod_desc    | character varying(100) |           | extended |              | 
 price        | numeric(10,2)          |           | main     |              | 
Indexes:
    "uqx_products_id_index1" UNIQUE, btree (product_id) TABLESPACE pg_default
    "fx_products_desc_index3_n" btree (substr(prod_desc::text, 1, 20)) TABLESPACE pg_default
    "idx_products_name_index2" btree (product_name) TABLESPACE example0, tablespace "example0"
Has OIDs: no
Options: orientation=row, compression=no
omm=# select * from pg_indexes where tablename='products';
 schemaname | tablename |         indexname         | tablespace |                                                        inde
xdef                                                         
------------+-----------+---------------------------+------------+------------------------------------------------------------
-------------------------------------------------------------
 public     | products  | uqx_products_id_index1    |            | CREATE UNIQUE INDEX uqx_products_id_index1 ON products USIN
G btree (product_id) TABLESPACE pg_default
 public     | products  | idx_products_name_index2  | example0   | CREATE INDEX idx_products_name_index2 ON products USING btr
ee (product_name) TABLESPACE example0
 public     | products  | fx_products_desc_index3_n |            | CREATE INDEX fx_products_desc_index3_n ON products USING bt
ree (substr((prod_desc)::text, 1, 20)) TABLESPACE pg_default
(3 rows)

复制

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

omm=# drop index uqx_products_id_index1;
DROP INDEX
omm=#  drop index idx_products_name_index2;
DROP INDEX
omm=#  drop index fx_products_desc_index3_n;
DROP INDEX
omm=#  
omm=#  drop table products;
DROP TABLE
omm=#  drop tablespace example0;
DROP TABLESPACE
omm=# 
复制

学习总结

通过本节课的学习，我学会了普通索引的基本操作，包括创建索引（唯一索引，Btree索引，表达式索引等），重命名索引，删除索引，更换索引表空间，查看索引的基本信息等。

opengauss

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者