学习目标
掌握openGauss DBMS索引的管理：创建索引、删除索引、查询索引的信息、修改索引的信息。
课程学习
索引是一个指向表中数据的指针。一个数据库中的索引与一本书的索引目录是非常相似的。
索引可以用来提高数据库查询性能，但是不恰当的使用将导致数据库性能下降。
课程作业
1.创建表，在表中创建索引
/*
--为表test的testnum列创建一个索引
drop table if exists test;
create table test(id serial primary key,testnum serial);
create index idx_test_testnum on test(testnum);
*/
omm=# --为表test的testnum列创建一个索引
omm-# drop table if exists test;
WARNING: Session unused timeout.
FATAL: terminating connection due to administrator command
could not send data to server: Broken pipe
The connection to the server was lost. Attempting reset: Succeeded.
omm=# create table test(id serial primary key,testnum serial);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq1" for serial column "test.id"
NOTICE: CREATE TABLE will create implicit sequence "test_testnum_seq1" for serial column "test.testnum"
ERROR: relation "test" already exists in schema "public"
DETAIL: creating new table with existing name in the same schema
omm=#
omm=# create index idx_test_testnum on test(testnum);
CREATE INDEX
WHERE c.relkind IN ('i','I','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'db4ai'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND c.relname not like 'matviewmap\_%'
AND c.relname not like 'mlog\_%'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
omm=# --查看索引
omm=# \di
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'I' THEN 'global partition index' WHEN 'S' THEN 'sequence' WHEN 'L' THEN 'large sequence' WHEN 'f' THEN 'foreign table' WHEN 'm' THEN 'materialized view' WHEN 'e' THEN 'stream' WHEN 'o' THEN 'contview' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
c2.relname as "Table",
c.reloptions as "Storage"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
List of relations
Schema | Name | Type | Owner | Table | Storage
--------+------------------+-------+-------+-------+---------
public | idx_test_testnum | index | omm | test |
public | test_pkey | index | omm | test |
(2 rows)
--可见有两个索引， 都是关于test表，其中一个primary key主键，另一个就是我们创建的索引
2.通过hint使用索引
/*
--测试准备，创建表customer，并插入数据
CREATE TABLE customer
(
ca_address_sk integer NOT NULL ,
ca_address_id character(16),
ca_street_number character(10) ,
ca_street_name character varying(60) ,
ca_street_type character(15) ,
ca_suite_number character(10) ,
ca_city character varying(60) ,
ca_county character varying(30) ,
ca_state character(2) ,
ca_zip character(10) ,
ca_country character varying(20) ,
ca_gmt_offset numeric(5,2) ,
ca_location_type character(20)
);
insert into customer values
(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');
--创建索引
create index customer_idx on customer(ca_address_sk);
--通过hint强制使用索引，查看执行计划
EXPLAIN SELECT /*+ indexscan(customer customer_idx ) */
* FROM customer WHERE ca_address_sk<100;
*/
omm=# --创建表customer，并插入数据
omm=# CREATE TABLE customer
omm-# (
omm(# ca_address_sk integer NOT NULL ,
omm(# ca_address_id character(16),
omm(# ca_street_number character(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(30) ,
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(# );
insert into customer values
omm-# CREATE TABLE
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=#
omm=# --创建索引
omm=#
omm=# create index customer_idx on customer(ca_address_sk);
CREATE INDEX
omm=#
omm=# --通过hint强制使用索引，查看执行计划
omm=#
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=788)
Index Cond: (ca_address_sk < 100)
(3 rows)
--对比不使用索引的执行计划
omm=# EXPLAIN analyze SELECT * FROM customer WHERE ca_address_sk<100;
QUERY PLAN
-------------------------------------------------------------------------------
---------------------
Seq Scan on customer (cost=0.00..1.04 rows=1 width=788) (actual time=0.013..0
.015 rows=3 loops=1)
Filter: (ca_address_sk < 100)
Total runtime: 0.081 ms
(3 rows)
omm=# EXPLAIN analyze SELECT /*+ indexscan(customer customer_idx ) */ * 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=788)
(actual time=0.106..0.109 rows=3 loops=1)
Index Cond: (ca_address_sk < 100)
Total runtime: 0.173 ms
(4 rows)
--并没有说由索引就更快啊，哈哈，看情况。
3.rename索引
omm=# ALTER INDEX idx_test_testnum RENAME TO idx_test_testnum_new;
ALTER INDEX
4.重建索引
omm=# omm=# --重建一个单独索引
ALTER INDEX idx_test_testnum_new REBUILD;
REINDEX
omm=#
omm=# REINDEX INDEX idx_test_testnum_new;
REINDEX
omm=# --重建所有索引
omm=# reindex table test;
REINDEX
5.移动索引到其他表空间
/*
--创建表空间myindex_ts：
CREATE TABLESPACE myindex_ts RELATIVE LOCATION 'tablespace/myindex_ts1';
--将索引idx_test_testnum_new移动到表空间myindex_ts：
ALTER INDEX idx_test_testnum_new SET TABLESPACE myindex_ts;
--查看索引所在的表空间
select * from pg_indexes where tablename = 'test';
--或
select * from pg_indexes where indexname = 'idx_test_testnum_new';
*/
omm=# CREATE TABLESPACE 表空间myindex_ts：
omm=# E TABLESPACE myindex_ts RELATIVE LOCATION 'tablespace/myindex_ts1';
omm=# --将索引idx_test_testnum_new移动到表空间myindex_ts：
omm=# ALTER INDEX idx_test_testnum_new SET TABLESPACE myindex_ts;
ALTER INDEX
omm=#
omm=# --查看索引所在的表空间
omm=# select * from pg_indexes where tablename = 'test';
schemaname | tablename | indexname | tablespace |
indexdef
------------+-----------+----------------------+------------+------------------
---------------------------------------------------------------------
public | test | test_pkey | | CREATE UNIQUE IND
EX 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=# --或
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)
6.删除索引
--删除表test上的索引idx_test_testnum_new和索引表空间
omm=# drop TABLESPACE myindex_ts ;
ERROR: tablespace "myindex_ts" is not empty
omm=# drop index idx_test_testnum_new;
DROP INDEX
omm=# drop TABLESPACE myindex_ts ;
DROP TABLESPACE
omm=#
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
