openGauss每日一练第 17 天 |学习笔记

原创 newdata 2022-12-10

342

今日目标：openGauss DBMS索引的管理

索引管理包括：创建索引、删除索引、查询索引的信息、修改索引的信息。

INDEX

索引是一个指向表中数据的指针。一本书的索引目录是非常相似的。索引可以用来提高数据库查询性能，但是不恰当的使用将导致数据库性能下降。

创建索引时建议仅在匹配如下某条原则时：

经常执行查询的字段。
在连接条件上创建索引，对于存在多字段连接的查询，建议在这些字段上建立组合索引。例如，select * from t1 join t2 on t1.a=t2.a and t1.b=t2.b，可以在t1表上的a，b字段上建立组合索引。
WHERE子句的过滤条件字段上（尤其是范围条件）。
在经常出现在ORDER BY、GROUP BY和DISTINCT后的字段。

查看索引

\di[S+] [PATTERN] \list indexes，查看索引信息

创建索引

单列索引

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

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

删除索引

DROP INDEX index_name;

REINDEX

为表中的数据重建索引。

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

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

Plan Hint调优概述

– Plan Hint支持在SELECT关键字后通过如下形式指定：

/*+ */

– 可以同时指定多个hint，之间使用空格分隔。hint只能hint当前层的计划，对于子查询计划的hint，需要在子查询的select关键字后指定hint。

示例：

select /*+ <plan_hint1> <plan_hint2> / * from t1, (select /+ <plan_hint3> */ * from t2) where 1=1;

PG_INDEXES （系统视图)

PG_INDEXES视图提供对数据库中每个索引的有用信息的访问。

名称	类型	引用	描述
schemaname	name	PG_NAMESPACE.nspname	包含表和索引的模式名称。
tablename	name	PG_CLASS.relname	此索引所服务的表的名称。
indexname	name	PG_CLASS.relname	索引的名称。
tablespace	name	PG_TABLESPACE.nspname	包含索引的表空间名称。
indexdef	text	-	索引定义（一个重建的CREATE INDEX命令）。

课后作业

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

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

omm=# create table test (
omm(# id serial primary key ,
omm(# testnum serial 
omm(# );
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
NOTICE:  CREATE TABLE will create implicit sequence "test_testnum_seq" for serial column "test.testnum"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
omm=# CREATE TABLE

omm=# create index idx_test_testnum on test(testnum);
CREATE INDEX
omm=# \di 
                      List of relations
 Schema |       Name       | Type  | Owner | Table | Storage 
--------+------------------+-------+-------+-------+---------
 public | idx_test_testnum | index | omm   | test  | 
 public | test_pkey        | index | omm   | test  | 
(2 rows)

omm=#

2. 通过hint使用索引

--数据准备，创建表customer，并插入数据
omm=# create table customer(
omm(# ca_address_sk integer not null,
omm(# ca_address_id character(16),
omm(# ca_street_number varchar(10),
omm(# ca_street_name character varying(60),
omm(# ca_street_type character(15),
omm(# ca_suite_number character(10),
omm(# ca_city character varying(60),
omm(# ca_county character varying(100),
omm(# ca_state character(2),
omm(# ca_zip character(10),
omm(# ca_country character varying(20),
omm(# ca_gmt_offset numeric(5,2),
omm(# ca_location_type character(20)
omm(# );
CREATE TABLE
omm=# \dt
                          List of relations
 Schema |   Name   | Type  | Owner |             Storage              
--------+----------+-------+-------+----------------------------------
 public | customer | table | omm   | {orientation=row,compression=no}
 public | test     | table | omm   | {orientation=row,compression=no}
(2 rows)

omm=# 
omm=# insert into customer values
omm-# (1, 'AAAAAAAABAAAAAAA', '18', 'Jackson', 'Parkway', 'Suite 280', 'Fairfield', 'Maricopa County', 'AZ', '86192' ,'United States', -7.00, 'condo'),
omm-# (2, 'AAAAAAAACAAAAAAA', '362', 'Washington 6th', 'RD', 'Suite 80', 'Fairview', 'Taos County', 'NM', '85709', 'United States', -7.00, 'condo'),
omm-# (3, 'AAAAAAAADAAAAAAA', '585', 'Dogwood Washington', 'Circle', 'Suite Q', 'Pleasant Valley', 'York County', 'PA', '12477', 'United States', -5.00, 'single family');
INSERT 0 3
omm=# select * from customer ;
 ca_address_sk |  ca_address_id   | ca_street_number |   ca_street_name   | ca_street_type  | ca_suite_number |     ca_city     |    ca_county   
 | ca_state |   ca_zip   |  ca_country   | ca_gmt_offset |   ca_location_type   
---------------+------------------+------------------+--------------------+-----------------+-----------------+-----------------+----------------
-+----------+------------+---------------+---------------+----------------------
             1 | AAAAAAAABAAAAAAA | 18               | Jackson            | Parkway         | Suite 280       | Fairfield       | Maricopa County
 | AZ       | 86192      | United States |         -7.00 | condo               
             2 | AAAAAAAACAAAAAAA | 362              | Washington 6th     | RD              | Suite 80        | Fairview        | Taos County    
 | NM       | 85709      | United States |         -7.00 | condo               

             3 | AAAAAAAADAAAAAAA | 585              | Dogwood Washington | Circle          | Suite Q         | Pleasant Valley | York County    
 | PA       | 12477      | United States |         -5.00 | single family       
(3 row
 -- 创建索引
omm=# create index customer_idx on customer (ca_address_sk );
CREATE INDEX
omm=# \di                      List of relations
 Schema |       Name       | Type  | Owner |  Table   | Storage 
--------+------------------+-------+-------+----------+---------
 public | customer_idx     | index | omm   | customer | 
 public | idx_test_testnum | index | omm   | test     | 
 public | test_pkey        | index | omm   | test     | 
(3 rows)

omm=# \di customer_idx 
                     List of relations
 Schema |     Name     | Type  | Owner |  Table   | Storage 
--------+--------------+-------+-------+----------+---------
 public | customer_idx | index | omm   | customer | 
(1 row)

omm=# explain select * from customer  where ca_address_sk ;
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on customer  (cost=0.00..1.04 rows=1 width=922)
   Filter: ca_address_sk
(2 rows)

omm=# EXPLAIN SELECT /*+ indexscan(customer customer_idx ) */ 
omm-#  * FROM customer WHERE ca_address_sk<100;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 [Bypass]
 Index Scan using customer_idx on customer  (cost=0.00..8.27 rows=1 width=922)
   Index Cond: (ca_address_sk < 100)
(3 rows)

omm=# explain select * from customer  where ca_address_sk <100;
(2 rows)

omm=#                         QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on customer  (cost=0.00..1.04 rows=1 width=922)
   Filter: (ca_address_sk < 100)

3. rename索引

ALTER INDEX [ IF EXISTS ] index_name RENAME TO new_name;


omm=# \di                             
                       List of relations
 Schema |       Name       | Type  | Owner |  Table   | Storage 
--------+------------------+-------+-------+----------+---------
 public | customer_idx     | index | omm   | customer | 
 public | idx_test_testnum | index | omm   | test     | 
 public | test_pkey        | index | omm   | test     | 
(3 rows)

omm=# 
omm=# alter index idx_test_testnum  rename to idx_test_testnum_new;
ALTER INDEX
omm=# \di 
                         List of relations
 Schema |         Name         | Type  | Owner |  Table   | Storage 
--------+----------------------+-------+-------+----------+---------
 public | customer_idx         | index | omm   | customer | 
 public | idx_test_testnum_new | index | omm   | test     | 
 public | test_pkey            | index | omm   | test     | 
(3 rows)

omm=#

4. 重建索引

ALTER INDEX index_name REBUILD;

reindex index <index_name>

omm=# \di 
                         List of relations
 Schema |         Name         | Type  | Owner |  Table   | Storage 
--------+----------------------+-------+-------+----------+---------
 public | customer_idx         | index | omm   | customer | 
 public | idx_test_testnum_new | index | omm   | test     | 
 public | test_pkey            | index | omm   | test     | 
(3 rows)

omm=# 
omm=# omm=# 
--重建一个单独索引
omm=# alter index idx_test_testnum_new rebuild;
REINDEX
omm=# \di
                         List of relations
 Schema |         Name         | Type  | Owner |  Table   | Storage 
--------+----------------------+-------+-------+----------+---------
 public | customer_idx         | index | omm   | customer | 
 public | idx_test_testnum_new | index | omm   | test     | 
 public | test_pkey            | index | omm   | test     | 
(3 rows)
--重建一个单独索引
omm=# reindex index idx_test_testnum_new ;
REINDEX
omm=#  
--重建表所有索引
omm=# reindex table test ;
REINDEX
omm=# \di
                         List of relations
 Schema |         Name         | Type  | Owner |  Table   | Storage 
--------+----------------------+-------+-------+----------+---------
 public | customer_idx         | index | omm   | customer | 
 public | idx_test_testnum_new | index | omm   | test     | 
 public | test_pkey            | index | omm   | test     | 
(3 rows)

omm=#

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

修改表索引的所属空间，此操作会改变索引的表空间为指定表空间，并且把索引相关的数据文件移动到新的表空间里。

alter index <index_name> set tablespace <tablespace_name>;

-- 查看索引
omm=# \di
                         List of relations
 Schema |         Name         | Type  | Owner |  Table   | Storage 
--------+----------------------+-------+-------+----------+---------
 public | customer_idx         | index | omm   | customer | 
 public | idx_test_testnum_new | index | omm   | test     | 
 public | test_pkey            | index | omm   | test     | 
(3 rows)
-- 查看索引详细信息 包括表空间
omm=# select * from pg_indexes where tablename = 'test';
 schemaname | tablename |      indexname       | tablespace |                                       indexdef                                     
   
------------+-----------+----------------------+------------+------------------------------------------------------------------------------------
---
 public     | test      | test_pkey            |            | CREATE UNIQUE INDEX test_pkey ON test USING btree (id) TABLESPACE pg_default
 public     | test      | idx_test_testnum_new |            | CREATE INDEX idx_test_testnum_new ON test USING btree (testnum) TABLESPACE pg_defau
lt
(2 rows)
-- 创建表空间
omm=# create tablespace myindex_ts relative location 'tablespace/ts_1';
CREATE TABLESPACE
omm=#    
-- 移动索引到其他表空间
omm=# alter index idx_test_testnum_new set tablespace myindex_ts ;
ALTER INDEX
-- 根据表名查看索引信息
omm=# select * from pg_indexes where tablename = 'test';
 schemaname | tablename |      indexname       | tablespace |                                       indexdef                                     
   
------------+-----------+----------------------+------------+------------------------------------------------------------------------------------
---
 public     | test      | test_pkey            |            | CREATE UNIQUE INDEX test_pkey ON test USING btree (id) TABLESPACE pg_default
 public     | test      | idx_test_testnum_new | myindex_ts | CREATE INDEX idx_test_testnum_new ON test USING btree (testnum) TABLESPACE myindex_
ts
(2 rows)
-- 根据索引名查看索引信息
omm=# select * from pg_indexes where indexname='idx_test_testnum_new';
 schemaname | tablename |      indexname       | tablespace |                                       indexdef                                     
   
------------+-----------+----------------------+------------+------------------------------------------------------------------------------------
---
 public     | test      | idx_test_testnum_new | myindex_ts | CREATE INDEX idx_test_testnum_new ON test USING btree (testnum) TABLESPACE myindex_
ts
(1 row)

omm=#

6.删除索引

drop index <index_name>

omm=# \di
                         List of relations
 Schema |         Name         | Type  | Owner |  Table   | Storage 
--------+----------------------+-------+-------+----------+---------
 public | customer_idx         | index | omm   | customer | 
 public | idx_test_testnum_new | index | omm   | test     | 
 public | test_pkey            | index | omm   | test     | 
(3 rows)

omm=# drop index  idx_test_testnum_new ;
DROP INDEX
omm=# \di
                     List of relations
 Schema |     Name     | Type  | Owner |  Table   | Storage 
--------+--------------+-------+-------+----------+---------
 public | customer_idx | index | omm   | customer | 
 public | test_pkey    | index | omm   | test     | 
(2 rows)

omm=#

最后修改时间：2022-12-10 11:53:50

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者