openGauss开源数据库实战十三

任务十三 openGauss逻辑结构:索引管理

预备知识:索引

索引是一种辅助的数据结构,能够加速基于索引键的搜索的速度。索引中的索引记录,按照索引键进行排序。
在关系数据库中,在表的某些列上(这些列的属性值就是索引键值)建有索引,在有些情况可以加速在这些列上的查找速度。对该表进行查找时,DBMS如果发现使用索引可以加快查找速度,就会在执行计划中使用该索引。
当对该表进行DML操作(增删改表中的行)时,DBMS会自动更新该表上的所有索引。这是索引的副作用。
索引类似于一本很厚的书的目录。假设这本书正文有1000页,目录有20页。如果不使用书的目录,直接在书的正文中查找内容,那么我们运气最好时是在书的第1页找到了所需的内容;当然如果我们运气很差,则可能必须翻遍正文的1000页,才能找到所需的内容。平均下来,我们需要翻阅(1+1000)/2(约等于500)页。如果使用书的目录,那么我们运气最好时是在目录的第1页找到内容所在的页码,然后直接到该页码找详细内容,也就是说,我们只需要访问两页,就可以找到所需要的内容;运气最差的情况是翻完20页目录才找到内容所在的页码,然后再转到该页码找需要的内容,也就是说,一共翻阅了21页才找到我们所需要的内容。平均下来,我们需要翻阅(2+21)/2(约等于12)页。所以,在查找单页内容的情况下,使用目录比不使用目录平均来看能提高40倍的速度。
如果表中有大量的行,且我们只想查找单条记录或者少量的记录(要查找的记录数小于总记录数的2%),使用索引比不使用索引可能要快几千倍甚至是几万倍。

任务目标

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

实施步骤

一、准备工作

使用Linux用户omm打开一个Linux终端窗口,执行如下的命令,创建表空间ustb_ts、数据库ustbdb、用户temp,并授予用户temp SYSADMIN权限:

gsql -d postgres -p 26000 -r

CREATE TABLESPACE ustb_ts RELATIVE LOCATION 'tablespace/ustb_ts1';
CREATE DATABASE ustbdb WITH TABLESPACE = ustb_ts;
CREATE USER temp IDENTIFIED BY 'temp@ustb2020';
ALTER USER temp  SYSADMIN;
\q

二、索引性能测试

使用索引,在某些场景下能大大提高查询效率。
使用Linux用户omm,另外打开一个Linux终端窗口,以数据库用户temp的身份,连接到刚刚创建的数据库ustbdb,创建一个测试表test:

gsql -d ustbdb -h 192.168.100.91 -U temp -p 26000 -W temp@ustb2020 -r

drop table if exists test;
create table test(id serial primary key,testnum serial);

运行下面的命令,直接为表test插入1000万行,可能会报错，常见原因有：

1. 内存不足：一次性插入大量数据会占用大量内存。
2. 事务日志过大：单个事务包含太多操作，导致事务日志过大。
3. 超时：操作时间过长，导致数据库连接超时。

insert into test(testnum) values(generate_series(1,10000000));

采用变通的办法,执行下面的命令,每次只为表test添加100万行,重复执行10次,为表test插入1000万行:

insert into test(testnum) values(generate_series(1,1000000));
insert into test(testnum) values(generate_series(1,1000000));
insert into test(testnum) values(generate_series(1,1000000));
insert into test(testnum) values(generate_series(1,1000000));
insert into test(testnum) values(generate_series(1,1000000));
insert into test(testnum) values(generate_series(1,1000000));
insert into test(testnum) values(generate_series(1,1000000));
insert into test(testnum) values(generate_series(1,1000000));
insert into test(testnum) values(generate_series(1,1000000));
insert into test(testnum) values(generate_series(1,1000000));

执行下面的命令和SQL语句,开始进行SQL查询语句测试(因为日前还没有为该SQL查询语句WHERE子句上的testnum列创建索引,因此需要全表扫描才能完成查询):

gsql -d ustbdb -h 192.168.100.91 -U temp -p 26000 -W temp@ustb2020 -r

-- 打开gsql的执行时间开关
\timing on
select * from test where testnum=8234567;
select * from test where testnum=8234567;
select * from test where testnum=8234567;
\q

第一次执行查询时,需要将表test的数据块从硬盘读人内存,然后在内存中进行查询,因此耗时较长(6372.483 ms);之后的查询都是在内存中读取表test的数据,因此查询执行时间要少一些(645.933 ms和661.210 ms)。可以看出,在没有索引的情况下,这个查询的执行时间稳定在650ms左右。
执行下面的命令和SOL语句,为表test的testnum列创建一个索引:

gsql -d ustbdb -h 192.168.100.91 -U temp -p 26000 -W temp@ustb2020 -r

create index idx_test_testnum on test(testnum);
\q

执行下面的命令和SQL语句,开始进行SQL查询语句测试(SQL查询语句的执行将会使用索引):

gsql -d ustbdb -h 192.168.100.91 -U temp -p 26000 -W temp@ustb2020 -r

-- 打开gsql的执行时间开关
\timing on   
select * from test where testnum=8234567;
select * from test where testnum=8234567;
select * from test where testnum=8234567;
\q

创建索引后,第一次执行查询时,需要将索引的数据块从硬盘读人内存,然后在内存中进行索引查询,因此耗时较长(7.259 ms),但是比没有索引时(6372.483 ms)要少得多;之后的查询都是在内存中读取索引的数据,因此查询执行时间要更少一些(0.289 ms和0.278 ms)。可以看出,在有索引的情况下,这个查询的执行时间稳定在0.28ms左右,相比没有索引时的查询执行时间(650ms),性能有了巨大的提升。

三、显示索引的信息

执行下面的gsql元命令,查看索引的信息:

gsql -d ustbdb -h 192.168.100.91 -U temp -p 26000 -W temp@ustb2020 -r

\di

执行下面的gsql元命令,查看索引 idx_test_testnum 的详细信息:

\d+ idx_test_testnum

四、删除索引

执行下面的命令,删除表test上的索引idx_test_testnum:

drop index idx_test_testnum;

删除索引后,再次执行前面的查询:

-- 打开gsql的执行时间开关
\timing on   
select * from test where testnum=8234567;

对比删除索引前的查询执行时间(7.259 ms左右)和删除索引后的查询执行时间(637.839 ms),我们发现,有索引的话,查询效率提高了很多(大概有100倍)。
为了继续下面的实验,执行下面的命令重新创建索引:

create index idx_test_testnum on test(testnum);

五、修改索引

1.修改索引的名字

执行下面的命令,可以修改索引的名字:

ALTER INDEX idx_test_testnum RENAME TO idx_test_testnum_renamebytemp;
\q

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

打开另外一个Linux终端窗口,使用Linux用户omm,执行下面的命令创建表空间myindex_ts:

gsql -d postgres -p 26000 -r

CREATE TABLESPACE myindex_ts RELATIVE LOCATION 'tablespace/myindex_ts1';
\q

继续执行下面的命令和SQL语句,将索引idx_test_testnum_renamebytemp移动到表空间myindex_ts:

gsql -d ustbdb -h 192.168.100.91 -U temp -p 26000 -W temp@ustb2020 -r

ALTER INDEX idx_test_testnum_renamebytemp SET TABLESPACE myindex_ts;

3.修改索引的填充因子

执行下面的命令和SQL语句,设置索引的填充因子为60:

\d+ idx_test_testnum_renamebytemp
alter index idx_test_testnum_renamebytemp SET(fillfactor=60);
\d+ idx_test_testnum_renamebytemp

执行下面的命令和SQL语句,将索引的填充因子恢复为默认的值:

alter index idx_test_testnum_renamebytemp RESET(fillfactor);
\d+ idx_test_testnum_renamebytemp
\q

六、清理工作

在继续后面的任务之前,关闭所有的Linux终端窗口(或者退出所有的openGaussgsql会话),打开一个Linux终端窗口,执行下面的命令和SQL语句,进行清理:

gsql -d postgres -p 26000 -r

drop database ustbdb;
drop user temp;
drop tablespace myindex_ts;
drop tablespace ustb_ts;
\q