《openGauss每日一练第9天|索引操作--普通表》

一、学习目标

学习openGauss普通表的索引

二、索引（INDEX）

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

三、openGauss基础操作

• openGauss连接

su - omm
gsql -r

• 查看openGauss表空间信息

// 显示表信息（包含索引）
\d
// 显示表更多信息
\d+
// 显示指定表更多信息
\d+ table_name

四、openGauss普通表的索引操作

• 创建索引

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 index_name ON table_name(field_name);
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 index_name ON table_name USING btree(field_name);
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;

• 修改索引

// 重命名索引
ALTER INDEX tpcds.ds_ship_mode_t1_index1 RENAME TO ds_ship_mode_t1_index5;

// 设置索引不可用
ALTER INDEX tpcds.ds_ship_mode_t1_index2 UNUSABLE;

// 修改索引表空间
alter index tpcds.ds_ship_mode_t1_index4 set tablespace example0;

• 重建索引

// 重建一个单独索引
ALTER INDEX tpcds.ds_ship_mode_t1_index2 REBUILD;
REINDEX INDEX tpcds.ds_ship_mode_t1_index4;

// 重建所有索引
reindex table tpcds.ship_mode_t1;

• 删除索引
  DROP INDEX index_name

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;

五、课后作业

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

// 创建表products
CREATE TABLE products
(
num int,
name char(30),
type char(30)
);

// 创建一个unique索引1
CREATE UNIQUE INDEX index_1 ON products(type);

// 创建指定b-tree索引2
CREATE INDEX index_2 ON products USING btree(type);

// 创建表达式索引3
CREATE INDEX index_3 ON products(SUBSTR(name,1,3));

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

// 设置索引1不可用
ALTER INDEX index_1 UNUSABLE;

// 创建表空间
CREATE TABLESPACE test RELATIVE LOCATION 'tablespace/tablespace_0';

// 修改索引2的表空间
ALTER INDEX index_2 set tablespace test;

// 重命名索引3
ALTER INDEX index_3 RENAME TO index_3_1;

• 重建索引2和products的所有索引

// 重建索引2
ALTER INDEX index_2 REBUILD;

// 重建products的所有索引
reindex table products;

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

\d+
select * from pg_indexes where tablename = 'products';

• 删除索引、表和表空间

DROP INDEX index_1;
DROP INDEX index_2;
DROP INDEX index_3_1;
DROP table if exists products;
drop tablespace test;