暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / openGauss每日一练第 9 天 | 普通表索引管理及课后作业

openGauss每日一练第 9 天 | 普通表索引管理及课后作业

原创 JiekeXu All China Database Union 2021-12-12
892

openGauss 每日一练第 9 天学习打卡，巩固 openGauss 数据库普通表索引管理基本操作！

学习目标

学习 openGauss 普通表的索引管理

前面每日一练链接：

openGauss每日一练第1天 | 数据库和表的基本操作（一）
openGauss每日一练第2天 | 数据库和表的基本操作（二）
openGauss每日一练第3天 | 前三课作业实操练习
openGauss每日一练第4天 | 角色管理及课后作业
openGauss每日一练第5天 | 用户管理及课后作业
openGauss每日一练第6天 | 模式管理及课后作业
openGauss每日一练第7天 | 表空间管理及课后作业
openGauss每日一练第8天 | 分区表管理及课后作业

课程学习

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

注意：在启动 gsql 的命令行中加上“-E”参数，就可以把 gsql 中各种以“\”开头的命令执行的实际 SQL 语句打印出来.如果在已运行的 gsql 中显示了某个命令实际执行的 SQL 语句后又想关闭此功能，该怎么办？这时可以使用“\set ECHO_HIDDEN on|off”命令。

连接 openGauss

su - omm
gsql -r

gsql -r -h localhost -U user1 -W JiekeXu12 -d postgres

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

图片.png

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

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

图片.png

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

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

图片.png

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

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

图片.png

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

图片.png

–查看表信息

\d+ tpcds.ship_mode_t1

jiekexu=# \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';

jiekexu=# 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_mo
de_t1_index1 ON tpcds.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_i
ndex4 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_i
ndex2 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_mo
de_t1_index3 ON tpcds.ship_mode_t1 USING btree (sm_ship_mode_sk) TABLESPACE pg_default WHERE (sm_
ship_mode_sk > 10)
(4 rows)

图片.png

2.修改索引定义

–重命名索引

ALTER INDEX tpcds.ds_ship_mode_t1_index1 RENAME TO ds_ship_mode_t1_index5;

图片.png

–设置索引不可用

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;

图片.png

3.重建索引

–重建一个单独索引

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

图片.png

–重建所有索引

重新建立指定表的所有索引，如果表有从属的”TOAST”表，则这个表也会重建索引。如果表上有索引已经被 alter unusable 失效，则这个索引无法被重新创建。

reindex table tpcds.ship_mode_t1;

功能描述:为表中的数据重建索引。

在以下几种情况下需要使用REINDEX重建索引：

索引崩溃，并且不再包含有效的数据。
索引变得“臃肿”，包含大量的空页或接近空页。
为索引更改了存储参数（例如填充因子），并且希望这个更改完全生效。
使用CONCURRENTLY选项创建索引失败，留下了一个“非法”索引。

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;

图片.png

课程作业

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

create table products(id int not null,name char(30),tel int,addr char(40));

create UNIQUE index idx_products_id on products(id);
create index idx_products_addr on products using btree(addr);
create index idx_products_expid on products(id) where id >=100;

图片.png

jiekexu=# create table products(id int not null,name char(30),tel int,addr char(40));
CREATE TABLE
jiekexu=# \d
                          List of relations
 Schema |   Name   | Type  | Owner |             Storage              
--------+----------+-------+-------+----------------------------------
 public | products | table | omm   | {orientation=row,compression=no}
(1 row)

jiekexu=# create UNIQUE index idx_products_id on products(id);
jiekexu=# CREATE INDEX

jiekexu=# \d+ products           
                          Table "public.products"
 Column |     Type      | Modifiers | Storage  | Stats target | Description 
--------+---------------+-----------+----------+--------------+-------------
 id     | integer       | not null  | plain    |              | 
 name   | character(30) |           | extended |              | 
 tel    | integer       |           | plain    |              | 
 addr   | character(40) |           | extended |              | 
Indexes:
    "idx_products_id" UNIQUE, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

jiekexu=# create index idx_products_addr on products using btree(addr);
CREATE INDEX
jiekexu=# \d+ products
                          Table "public.products"
 Column |     Type      | Modifiers | Storage  | Stats target | Description 
--------+---------------+-----------+----------+--------------+-------------
 id     | integer       | not null  | plain    |              | 
 name   | character(30) |           | extended |              | 
 tel    | integer       |           | plain    |              | 
 addr   | character(40) |           | extended |              | 
Indexes:
    "idx_products_id" UNIQUE, btree (id) TABLESPACE pg_default
    "idx_products_addr" btree (addr) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

jiekexu=# create index idx_products_expid on products(id) where id >=100; 
CREATE INDEX
jiekexu=# \d+ products
                          Table "public.products"
 Column |     Type      | Modifiers | Storage  | Stats target | Description 
--------+---------------+-----------+----------+--------------+-------------
 id     | integer       | not null  | plain    |              | 
 name   | character(30) |           | extended |              | 
 tel    | integer       |           | plain    |              | 
 addr   | character(40) |           | extended |              | 
Indexes:
    "idx_products_id" UNIQUE, btree (id) TABLESPACE pg_default
    "idx_products_addr" btree (addr) TABLESPACE pg_default
    "idx_products_expid" btree (id) TABLESPACE pg_default WHERE id >= 100
Has OIDs: no
Options: orientation=row, compression=no

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

alter index idx_products_id unusable;

create tablespace products_tbs relative location 'tablespace1/products_tab';
alter index idx_products_addr set tablespace products_tbs;
\d+ products

alter index idx_products_expid rename to idx_products_whereid;

图片.png

图片.png

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

alter index idx_products_addr rebuild;
reindex table products;

图片.png

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

jiekexu=# \d+ products
                          Table "public.products"
 Column |     Type      | Modifiers | Storage  | Stats target | Description 
--------+---------------+-----------+----------+--------------+-------------
 id     | integer       | not null  | plain    |              | 
 name   | character(30) |           | extended |              | 
 tel    | integer       |           | plain    |              | 
 addr   | character(40) |           | extended |              | 
Indexes:
    "idx_products_id" UNIQUE, btree (id) TABLESPACE pg_default
    "idx_products_addr" btree (addr) TABLESPACE products_tbs, tablespace "products_tbs"
    "idx_products_whereid" btree (id) TABLESPACE pg_default WHERE id >= 100
Has OIDs: no
Options: orientation=row, compression=no

jiekexu=# select schemaname,tablename,indexname,tablespace from pg_indexes where tablename='products';
 schemaname | tablename |      indexname       |  tablespace  
------------+-----------+----------------------+--------------
 public     | products  | idx_products_id      | 
 public     | products  | idx_products_addr    | products_tbs
 public     | products  | idx_products_whereid | 
(3 rows)

图片.png

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

drop index idx_products_id;
drop index idx_products_addr;
drop index idx_products_whereid;
drop table products;
drop tablespace products_tbs;

图片.png

jiekexu=# drop index idx_products_id;
DROP INDEX
jiekexu=# drop index idx_products_addr;
DROP INDEX
jiekexu=# drop index idx_products_whereid;
DROP INDEX
jiekexu=# drop table products;
DROP TABLE
jiekexu=# drop tablespace products_tbs;
DROP TABLESPACE
jiekexu=# \d
No relations found.

jiekexu=# \db
              List of tablespaces
    Name    | Owner |         Location         
------------+-------+--------------------------
 example0   | omm   | tablespace1/tablespace_0
 pg_default | omm   | 
 pg_global  | omm   | 
(3 rows)

欧耶，第九课普通表索引管理实操及课后作业练习题完成啦！

墨力计划opengauss
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

JiekeXu_openGauss 学习笔记（共39篇）
JiekeXu 的 openGauss 学习笔记，国产数据库之路，学习华为 openGauss，记录 openGauss 路上的点点滴滴。openGauss 每日一练，openGauss 训练营。

评论