openGauss每日一练第9天 | 普通表索引的基本操作

普通表索引

索引是对数据库表中的一列或多列的值进行排序的一种结构，索引可以提高数据的访问速度，但同时也增加了插入、更新和删除操作的处理时间。

所以是否要为表增加索引，索引建立在哪些字段上，是创建索引前必须要考虑的问题。
····在经常需要搜索查询的列上创建索引，可以加快搜索的速度。
····在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构。
····在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。
····在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
····在经常使用WHERE子句的列上创建索引，加快条件的判断速度。
····为经常出现在关键字ORDER BY、GROUP BY、DISTINCT后面的字段建立索引。

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

----查看系统视图 pg_indexes

select schemaname,tablename,indexname,tablespace from pg_indexes where tablename = 'ship_mode_t1';

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;

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 lvzz;
create table lvzz.products
(
product_id     integer,
product_code   integer, 
product_name   char(20),
category       char(30)
);
create unique index products_index1 on lvzz.products(product_id);
create index products_index2 on lvzz.products using btree(product_code);
create index products_index3 on lvzz.products(substr(product_name,1 ,4));

\d+ lvzz.products;
select schemaname,tablename,indexname,tablespace from pg_indexes where tablename = 'products';

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

alter index lvzz.products_index1 unusable;

create tablespace example0 relative location 'tablespace1/tablespace_0';
alter index lvzz.products_index2 set tablespace example0;

alter index lvzz.products_index3 rename to products_index4;

select schemaname,tablename,indexname,tablespace from pg_indexes where tablename = 'products';

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

reindex index lvzz.products_index2;
reindex table lvzz.products;

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

\d+ lvzz.products;
select schemaname,tablename,indexname,tablespace from pg_indexes where tablename = 'products';

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

drop index lvzz.products_index1;
drop index lvzz.products_index2;
drop index lvzz.products_index4;
drop table lvzz.products;
drop tablespace example0;