openGauss每日一练第17天

学习索引管理

学习对数据库索引的管理如创建索引、删除索引、查询索引的信息、修改索引的信息等，完成课后作业。

创建表test

在omm用户下通过gsql -r连接数据库后创建表并设置列级约束，使用元命令\d <tablename>查看列约束信息

omm=# create table test(id bigint primary key,name char(100) not null,age int default 20);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
omm=# \d test
         Table "testsm.test"
 Column |      Type      | Modifiers  
--------+----------------+------------
 id     | bigint         | not null
 name   | character(100) | not null
 age    | integer        | default 20
Indexes:
    "test_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

在test表中为name字段创建索引

omm=# create index idx_test_name on test(name);
CREATE INDEX
omm=# \di
                     List of relations
 Schema |     Name      | Type  | Owner |  Table  | Storage 
--------+---------------+-------+-------+---------+---------
 testsm | idx_test_name | index | omm   | test    | 
 testsm | test001_pkey  | index | omm   | test001 | 
 testsm | test002_pkey  | index | omm   | test002 | 
 testsm | test_pkey     | index | omm   | test    | 
(4 rows)

通过元命令\di可以看到，除了test的主键索引外，还增加了名为idx_test_name的索引

通过hint使用索引

首先执行以下语句，向test表插入10条数据。

INSERT INTO test(id, name, age) VALUES(1, 'John', 25);
INSERT INTO test(id, name, age) VALUES(2, 'Jane', 30);
INSERT INTO test(id, name, age) VALUES(3, 'Tom', 35);
INSERT INTO test(id, name, age) VALUES(4, 'Sara', 40);
INSERT INTO test(id, name, age) VALUES(5, 'Lily', 45);
INSERT INTO test(id, name, age) VALUES(6, 'Alex', 50);
INSERT INTO test(id, name, age) VALUES(7, 'Leo', 55);
INSERT INTO test(id, name, age) VALUES(8, 'Linda', 60);
INSERT INTO test(id, name, age) VALUES(9, 'Chris', 65);
INSERT INTO test(id, name, age) VALUES(10, 'Mia', 70);

omm=# EXPLAIN SELECT /*+ indexscan(test idx_test_name ) */ 
omm-#  * FROM test WHERE name like 'Lily';
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Index Scan using idx_test_name on test  (cost=0.00..8.27 rows=1 width=416)
   Index Cond: (name = 'Lily'::bpchar)
   Filter: (name ~~ 'Lily'::text)
(3 rows)

重建索引

单独重建name的索引

omm=# alter index idx_test_name rebuild;
REINDEX

或者可以使用REINDEX INDEX <索引名>

重建所有

omm=# reindex table test;
REINDEX

移动索引到其他表空间

创建表空间myindex_ts，并将索引idx_test_name移动到表空间myindex_ts，并查看是否修改成功。

omm=# CREATE TABLESPACE myindex_ts RELATIVE LOCATION 'tablespace/myindex_ts1';
CREATE TABLESPACE
omm=# ALTER INDEX idx_test_name SET TABLESPACE myindex_ts;
ALTER INDEX
omm=# \x
Expanded display is on.
omm=# select * from pg_indexes where tablename = 'test';
-[ RECORD 1 ]----------------------------------------------------------------------------
schemaname | testsm
tablename  | test
indexname  | test_pkey
tablespace | 
indexdef   | CREATE UNIQUE INDEX test_pkey ON test USING btree (id) TABLESPACE pg_default
-[ RECORD 2 ]----------------------------------------------------------------------------
schemaname | testsm
tablename  | test
indexname  | idx_test_name
tablespace | myindex_ts
indexdef   | CREATE INDEX idx_test_name ON test USING btree (name) TABLESPACE myindex_ts

删除索引idx_test_name

omm=#  drop index idx_test_name;
DROP INDEX

总结

数据库索引是一种特殊的数据结构，它可以用来提高数据库查询性能，但是不恰当的使用将导致数据库性能下降，所以对数据库索引的管理就显得很重要。