创建索引在实战中非常重要, 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息.

0. 连接openGauss

su - omm
gsql -r

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

CREATE TABLE products
( product_id integer,
product_name char(30),
category char(20)
) ;

CREATE UNIQUE INDEX prod_id_index_1 ON products(product_id);
CREATE INDEX prod_id_index_2 ON products USING btree(product_name);
CREATE INDEX prod_id_index_3 ON products(SUBSTR(category,1,4));
select * from pg_indexes where tablename = 'products';

– 回显

 schemaname | tablename |    indexname    | tablespace | indexdef
------------+-----------+-----------------+------------+------------------------------------
 public     | products  | prod_id_index_1 |            | CREATE UNIQUE INDEX prod_id_index_1 ON products USING btree (product_id) TABLESPACE pg_default
 public     | products  | prod_id_index_2 |            | CREATE INDEX prod_id_index_2 ON products USING btree (product_name) TABLESPACE pg_default
 public     | products  | prod_id_index_3 |            | CREATE INDEX prod_id_index_3 ON products USING btree (substr((category)::text, 1, 4)) TABLESPACE pg_default
(3 rows)

我们可以看到, 默认的schema是public, 默认的TABLESPACE是pg_default.

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

ALTER INDEX prod_id_index_1 UNUSABLE;
CREATE TABLESPACE tspc1 RELATIVE LOCATION 'tablespace/tspc1';
ALTER INDEX prod_id_index_2 SET TABLESPACE tspc1;
ALTER INDEX prod_id_index_3 RENAME TO prod_id_index_30;

select * from pg_indexes where tablename = 'products';

– 回显

 schemaname | tablename |    indexname     | tablespace | indexdef
------------+-----------+------------------+------------+-----------------------------------
 public     | products  | prod_id_index_1  |            | CREATE UNIQUE INDEX prod_id_index_ 1 ON products USING btree (product_id) TABLESPACE pg_default
 public     | products  | prod_id_index_2  | tspc1      | CREATE INDEX prod_id_index_2 ON products USING btree (product_name) TABLESPACE tspc1
 public     | products  | prod_id_index_30 |            | CREATE INDEX prod_id_index_30 ON products USING btree (substr((category)::text, 1, 4)) TABLESPACE pg_default
(3 rows)

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

ALTER INDEX prod_id_index_2 REBUILD;
REINDEX TABLE products;

如何查看rebuild 这个过程是否有变化呢? 这是个问题.

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

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

– 回显

                             Table "public.products"
    Column    |     Type      | Modifiers | Storage  | Stats target | Description
--------------+---------------+-----------+----------+--------------+-------------
 product_id   | integer       |           | plain    |              |
 product_name | character(30) |           | extended |              |
 category     | character(20) |           | extended |              |
Indexes:
    "prod_id_index_1" UNIQUE, btree (product_id) TABLESPACE pg_default
    "prod_id_index_2" btree (product_name) TABLESPACE tspc1, tablespace "tspc1"
    "prod_id_index_30" btree (substr(category::text, 1, 4)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

 schemaname | tablename |    indexname     | tablespace | indexdef
------------+-----------+------------------+------------+-----------------------------------
 public     | products  | prod_id_index_1  |            | CREATE UNIQUE INDEX prod_id_index_1 ON products USING btree (product_id) TABLESPACE pg_default
 public     | products  | prod_id_index_2  | tspc1      | CREATE INDEX prod_id_index_2 ON products USING btree (product_name) TABLESPACE tspc1
 public     | products  | prod_id_index_30 |            | CREATE INDEX prod_id_index_30 ON products USING btree (substr((category)::text, 1, 4)) TABLESPACE pg_default
(3 rows)

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

DROP INDEX prod_id_index_1;
DROP INDEX prod_id_index_2;
DROP INDEX prod_id_index_30;
DROP TABLE products;
DROP TABLESPACE tspc1;

\d+
\db

– 回显

No relations found.
      List of tablespaces
    Name    | Owner | Location
 pg_global  | omm   |
(2 rows)

清理干净啦!(完)