openGauss每日一练第9天|学习数据库普通表索引

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

课程学习

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)
);

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

CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK);

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

CREATE INDEX ds_ship_mode_t1_index4 ON tpcds.ship_mode_t1 USING btree(SM_SHIP_MODE_SK);

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

CREATE INDEX ds_ship_mode_t1_index2 ON tpcds.ship_mode_t1(SUBSTR(SM_CODE,1 ,4));

• 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;

• 查看表信息

\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
    "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

• 查看系统视图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 tpc
ds.ship_mode_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 USING 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 USING 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 tpc
ds.ship_mode_t1 USING btree (sm_ship_mode_sk) TABLESPACE pg_default WHERE (sm_ship_mode_sk > 10)
(4 rows)

2.修改索引定义

• 重命名索引

ALTER INDEX tpcds.ds_ship_mode_t1_index1 RENAME TO ds_ship_mode_t1_index5;

• 设置索引不可用

ALTER INDEX tpcds.ds_ship_mode_t1_index2 UNUSABLE;

• 修改索引表空间

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=# \d+ tpcds.ship_mode_t1 ;
-----------------+---------------+-----------+----------+--------------+-------------
                             Table "tpcds.ship_mode_t1"
     Column      |     Type      | Modifiers | Storage  | Stats target | Description 
 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

\d+: extra argument ";" ignored
 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 |              | 

3.重建索引

• 重建一个单独索引

ALTER INDEX tpcds.ds_ship_mode_t1_index2 REBUILD;
REINDEX INDEX tpcds.ds_ship_mode_t1_index4;

• 重建所有索引

reindex table tpcds.ship_mode_t1;

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;

课程作业

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

create schema tpcds;
create table tpcds.products (
    id int not null,
    name char(30) not null,
    price int not null
);

create unique index ds_products_index1 on tpcds.products(id);
create index ds_products_index2 on tpcds.products using btree(id);
create index ds_products_index3 on tpcds.products(substr(name, 1, 4));

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

alter index tpcds.ds_products_index1 unusable;
create tablespace example1 relative location 'tablespace1/tablespace_1';
alter index tpcds.ds_products_index2 set tablespace example1;
alter index tpcds.ds_products_index3 rename to ds_products_index3_1;

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

alter index tpcds.ds_products_index2 rebuild;
reindex table tpcds.products;

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

omm=# \d+ tpcds.products;
                           Table "tpcds.products"
 Column |     Type      | Modifiers | Storage  | Stats target | Description 
--------+---------------+-----------+----------+--------------+-------------
 id     | integer       | not null  | plain    |              | 
 name   | character(30) | not null  | extended |              | 
 price  | integer       | not null  | plain    |              | 
Indexes:
    "ds_products_index1" UNIQUE, btree (id) TABLESPACE pg_default
    "ds_products_index2" btree (id) TABLESPACE example1, tablespace "example1"
    "ds_products_index3_1" btree (substr(name::text, 1, 4)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

select * from pg_indexes where tablename = 'products';
omm=# select * from pg_indexes where tablename = 'products';
 schemaname | tablename |       indexname       | tablespace |                                                      
indexdef                                                       
------------+-----------+-----------------------+------------+------------------------------------------------------
---------------------------------------------------------------
 tpcds      | products  | ds_products_index1    |            | CREATE UNIQUE INDEX ds_products_index1 ON tpcds.produ
cts USING btree (id) TABLESPACE pg_default
 tpcds      | products  | ds_products_index2    | example1   | CREATE INDEX ds_products_index2 ON tpcds.products USI
NG btree (id) TABLESPACE example1
 tpcds      | products  | ds_products_index3_1 |            | CREATE INDEX ds_products_index3_1 ON tpcds.products 
USING btree (substr((name)::text, 1, 4)) TABLESPACE pg_default
(3 rows)

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

drop index tpcds.products_index1;
drop index tpcds.products_index2;
drop index tpcds.products_index3_1;
drop tablespace example1;
drop table tpcds.products;