openGauss逻辑结构:索引管理
2023-09-11 14:19:28 时间
学习目标
掌握openGauss DBMS索引的管理:创建索引、删除索引、查询索引的信息、修改索引的信息。
课程学习
索引是一个指向表中数据的指针。一个数据库中的索引与一本书的索引目录是非常相似的。
索引可以用来提高数据库查询性能,但是不恰当的使用将导致数据库性能下降。
1.创建表,在表中创建索引
--登录omm数据库,创建测试表tabc17,为tabc17的testcol列创建一个索引
[omm@ogdb1 ~]$ gsql -p 40000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=# drop table if exists tabc17;
NOTICE: table "tabc17" does not exist, skipping
DROP TABLE
omm=# create table tabc17(id serial primary key,testcol serial);
NOTICE: CREATE TABLE will create implicit sequence "tabc17_id_seq" for serial column "tabc17.id"
NOTICE: CREATE TABLE will create implicit sequence "tabc17_testcol_seq" for serial column "tabc17.testcol"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tabc17_pkey" for table "tabc17"
CREATE TABLE
omm=# create index idx_tabc17_testcol on tabc17(testcol);
CREATE INDEX
omm=#
--查看索引
omm=# \di
List of relations
Schema | Name | Type | Owner | Table | Storage
--------+--------------------+-------+-------+--------+---------
public | idx_tabc17_testcol | index | omm | tabc17 |
public | tabc17_pkey | index | omm | tabc17 |
(2 rows)
2.通过hint使用索引
--测试准备,创建表customer,并插入数据
omm=# 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));
CREATE TABLE
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=# create index customer_idx on customer(ca_address_sk);
CREATE INDEX
--通过hint强制使用索引,查看执行计划
omm=# explain 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=280)
Index Cond: (ca_address_sk < 100)
(3 rows)
omm=#
3.rename索引
omm=# alter index idx_tabc17_testcol rename to idx_tabc17_testcol_new;
ALTER INDEX
4.重建索引
--重建一个单独索引
omm=# alter index idx_tabc17_testcol_new rebuild;
REINDEX
omm=#
omm=# reindex index idx_tabc17_testcol_new ;
REINDEX
omm=#
--重建所有索引
omm=# reindex table tabc17;
REINDEX
5.移动索引到其他表空间
--创建表空间myindex_ts:
omm=# CREATE TABLESPACE myindex_ts RELATIVE LOCATION 'tablespace/myindex_ts1';
CREATE TABLESPACE
--将索引idx_tabc17_tabc17num_new移动到表空间myindex_ts:
omm=# ALTER INDEX idx_tabc17_testcol_new SET TABLESPACE myindex_ts;
ALTER INDEX
--查看索引所在的表空间
omm=# select * from pg_indexes where tablename = 'tabc17';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+------------------------+------------+------------------------------------------------------------------------
-------------------
public | tabc17 | tabc17_pkey | | CREATE UNIQUE INDEX tabc17_pkey ON tabc17 USING btree (id) TABLESPACE p
g_default
public | tabc17 | idx_tabc17_testcol_new | myindex_ts | CREATE INDEX idx_tabc17_testcol_new ON tabc17 USING btree (testcol) TAB
LESPACE myindex_ts
(2 rows)
--或
omm=# select * from pg_indexes where indexname = 'idx_tabc17_testcol_new';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+------------------------+------------+------------------------------------------------------------------------
-------------------
public | tabc17 | idx_tabc17_testcol_new | myindex_ts | CREATE INDEX idx_tabc17_testcol_new ON tabc17 USING btree (testcol) TAB
LESPACE myindex_ts
(1 row)
6.删除索引
--执行下面的命令,删除表tabc17上的索引idx_tabc17_testcol_new:
omm=# drop index idx_tabc17_testcol_new;
DROP INDEX
omm=#
omm=# \di
List of relations
Schema | Name | Type | Owner | Table | Storage
--------+--------------+-------+-------+----------+---------
public | customer_idx | index | omm | customer |
public | tabc17_pkey | index | omm | tabc17 |
(2 rows)
omm=#
相关文章
- SQLSERVER到底能识别多少个逻辑CPU?
- 总感觉自己工作沟通想问题时没有逻辑,这可怎么办?| 极简逻辑指南
- 你需要的不是重构,而是理清业务逻辑
- 《惢客创业日记》2021.01.21(周四)什么是产品逻辑?
- Android Socket通讯 之 表情列表优化、业务逻辑优化
- 逻辑回归及常用模型分类评估方法
- 逻辑回归之案例:癌症分类预测-良/恶性乳腺癌肿瘤预测
- ISA(MIPS,ARM,RISC-V)中的算术运算溢出检测逻辑是怎样的?
- 从流水线角度看MIPS处理器ISA的实现逻辑以及RTOS操作系统仿真
- 物理像素与逻辑像素相关概念
- QT就是别人好心帮你做一些枯燥,并且很重复的代码编写工作,让你更好的把精力投入到你界面的逻辑和功能的实现的功能库(否则写了上万行代码了,才写出个BUG一大堆的毛坯)
- 【Verilog基础】逻辑综合(6)Synopsys TCL语言
- Python逻辑运算符
- python与C混编的执行逻辑