openGauss每日一练第 17天|索引管理

实操作业

理论基础

索引是一个指向表中数据的指针。一个数据库中的索引与一本书的索引目录是非常相似的。

索引可以用来提高数据库查询性能，但是不恰当的使用将导致数据库性能下降。建议仅在匹配如下某条原则时创建索引：

• 经常执行查询的字段。

• 在连接条件上创建索引，对于存在多字段连接的查询，建议在这些字段上建立组合索引。例如，select * from t1 join t2 on t1.a=t2.a and t1.b=t2.b，可以在t1表上的a，b字段上建立组合索引。

• WHERE子句的过滤条件字段上（尤其是范围条件）。

• 在经常出现在ORDER BY、GROUP BY和DISTINCT后的字段。

  语法格式

  • 单列索引

    单列索引是一个只基于表的一个列上创建的索引。

    CREATE INDEX [ [schema_name.]index_name ] ON table_name  (column_name);
    复制

  • 组合索引

    组合索引是基于表的多列上创建的索引。

    CREATE INDEX [ [schema_name.]index_name ] ON table_name  (column1_name,column2_name,...);
    复制

  • 唯一索引

    指定唯一索引的字段不允许重复值插入。

    CREATE  UNIQUE INDEX [ [schema_name.]index_name ] ON table_name  (column_name);
    复制

  • 局部索引

    在表的子集上构建索引，子集由一个条件表达式定义。

    CREATE INDEX [ [schema_name.]index_name ] ON table_name  (expression);
    复制

  • 部分索引

    部分索引是一个只包含表的一部分记录的索引，通常是该表中比其他部分数据更有用的部分。

    CREATE INDEX [ [schema_name.]index_name ] ON table_name  (column_name) 
       [ WHERE predicate ]
    复制

  • 删除索引

    DROP INDEX index_name;
    复制

  参数说明

  • UNIQUE

    创建唯一性索引，每次添加数据时检测表中是否有重复值。如果插入或更新的值会引起重复的记录时，将导致一个错误。

    目前只有B-tree索引支持唯一索引。

  • schema_name

    模式的名称。

    取值范围：已存在模式名。

  • index_name

    要创建的索引名，索引的模式与表相同。

    取值范围：字符串，要符合标识符的命名规范。

  • table_name

    需要为其创建索引的表的名称，可以用模式修饰。

    取值范围：已存在的表名。

  • column_name

    表中需要创建索引的列的名称（字段名）。

    如果索引方式支持多字段索引，可以声明多个字段。全局索引最多可以声明31个字段，其他索引最多可以声明32个字段。

  • expression

    创建一个基于该表的一个或多个字段的表达式索引，通常必须写在圆括弧中。如果表达式有函数调用的形式，圆括弧可以省略。

    表达式索引可用于获取对基本数据的某种变形的快速访问。比如，一个在upper(col)上的函数索引将允许WHERE upper(col) = 'JIM'子句使用索引。

    在创建表达式索引时，如果表达式中包含IS NULL子句，则这种索引是无效的。此时，建议用户尝试创建一个部分索引。

  • WHERE predicate

    创建一个部分索引。部分索引是一个只包含表的一部分记录的索引，通常是该表中比其他部分数据更有用的部分。例如，有一个表，表里包含已记账和未记账的定单，未记账的定单只占表的一小部分而且这部分是最常用的部分，此时就可以通过只在未记账部分创建一个索引来改善性能。另外一个可能的用途是使用带有UNIQUE的WHERE强制一个表的某个子集的唯一性。

    取值范围：predicate表达式只能引用表的字段，它可以使用所有字段，而不仅是被索引的字段。目前，子查询和聚集表达式不能出现在WHERE子句里。

实操目标

掌握openGauss DBMS索引的管理：创建索引、删除索引、查询索引的信息、修改索引的信息。

1.创建表，在表中创建索引

drop table if exists student;
create table student(
stu_id int4 ,
stu_name varchar(20) not null,
stu_age int
);
comment on table student is '学生信息表';
comment on column student.stu_id is '学号';
comment on column student.stu_name is '姓名';
comment on column student.stu_age is '年龄';
gnzyyhis=> \di
No relations found.
--创建单列索引
gnzyyhis=> create  index ON student (stu_id );
CREATE INDEX
--创建唯一索引
gnzyyhis=> create unique index ON student (stu_name );
CREATE INDEX
--查看索引
gnzyyhis=> \d+ student
                               Table "gnzyyhis.student"
  Column  |         Type          | Modifiers | Storage  | Stats target | Description 
----------+-----------------------+-----------+----------+--------------+-------------
 stu_id   | integer               |           | plain    |              | 学号
 stu_name | character varying(20) | not null  | extended |              | 姓名
 stu_age  | integer               |           | plain    |              | 年龄
Indexes:
    "student_stu_name_idx" UNIQUE, btree (stu_name) TABLESPACE gnzyyhis_tbs
    "student_stu_id_idx" btree (stu_id) TABLESPACE gnzyyhis_tbs
Has OIDs: no
Options: orientation=row, compression=no
--或
\di+
--注意：当创建索引语句忽略索引名称时，系统惠自动创建索引名称格式为：tablename_columnname_idx
复制

2.通过hint使用索引

gnzyyhis=> select * from student  where stu_id >100015;
 stu_id | stu_name | stu_age 
--------+----------+---------
 100016 | 张三16   |      35
 100017 | 张三17   |      36
 100018 | 张三18   |      37
 100019 | 张三19   |      38
 100020 | 张三20   |      39
 100021 | 张三21   |      40
 100022 | 张三22   |      41
(7 rows)

--通过hint强制使用索引，查看执行计划
gnzyyhis=> explain select /*+ indexsac(student student_stu_id_idx) */ * from student where stu_id >100005;
WARNING:  LINE 1: syntax error at 'indexsac'
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Bitmap Heap Scan on student  (cost=7.65..23.14 rows=439 width=32)
   Recheck Cond: (stu_id > 100005)
   ->  Bitmap Index Scan on student_stu_id_idx  (cost=0.00..7.54 rows=439 width=0)
         Index Cond: (stu_id > 100005)
(4 rows)
复制

3.rename索引

gnzyyhis=> \d student
           Table "gnzyyhis.student"
  Column  |         Type          | Modifiers 
----------+-----------------------+-----------
 stu_id   | integer               | 
 stu_name | character varying(20) | not null
 stu_age  | integer               | 
Indexes:
    "student_stu_name_idx" UNIQUE, btree (stu_name) TABLESPACE gnzyyhis_tbs
    "student_stu_id_idx" btree (stu_id) TABLESPACE gnzyyhis_tbs
--重命名索引
gnzyyhis=> alter index student_stu_id_idx rename to student_id_idx;
ALTER INDEX
--再次查看索引
gnzyyhis=> \di
                          List of relations
  Schema  |         Name         | Type  | Owner |  Table  | Storage 
----------+----------------------+-------+-------+---------+---------
 gnzyyhis | student_id_idx       | index | gnzyy | student | 
 gnzyyhis | student_stu_name_idx | index | gnzyy | student | 
(2 rows)
复制

4.重建索引

需要重建索引的最常见场景

索引碎片在不断增加

索引不断增加，删除的空间没有重复使用

索引 clustering factor （群集因子）不同步

--重建一个单独索引
gnzyyhis=> alter index  student_id_idx REBUILD;
REINDEX
--重建所有索引
gnzyyhis=> reindex table student ;
REINDEX
复制

5.移动索引到其他表空间

--查看索引
gnzyyhis=> select * from pg_indexes a  where a.tablename='student';
 schemaname | tablename |      indexname       | tablespace |                                              indexdef                        ------------+-----------+----------------------+------------+----------------------------------------------------------------------------
 gnzyyhis   | student   | student_id_idx       |            | CREATE INDEX student_id_idx ON student USING btree (stu_id) TABLESPACE gnzyyhis_tbs
 gnzyyhis   | student   | student_stu_name_idx |            | CREATE UNIQUE INDEX student_stu_name_idx ON student USING btree (stu_name) TABLESPACE gnzyyhis_tbs
(2 rows)
--移动指定索引到指定表空间
gnzyyhis=> alter index student_id_idx set tablespace pg_default;
ALTER INDEX
--再次查看
gnzyyhis=> select * from pg_indexes a  where a.tablename='student';
 schemaname | tablename |      indexname       | tablespace |                                              indexdef                       
------------+-----------+----------------------+------------+----------------------------------------------------------------------------
 gnzyyhis   | student   | student_id_idx       | pg_default | CREATE INDEX student_id_idx ON student USING btree (stu_id) TABLESPACE pg_default
 gnzyyhis   | student   | student_stu_name_idx |            | CREATE UNIQUE INDEX student_stu_name_idx ON student USING btree (stu_name) TABLESPACE gnzyyhis_tbs
(2 rows)
复制

6.删除索引

gnzyyhis=> drop  index student_id_idx ;
DROP INDEX
复制