上期我们介绍了openGauss 数据库日志检查和清理和最大连接数设置,接下来我们继续介绍openGauss数据库例行表、索引的维护相关内容。
6.例行表、索引的维护
6.1 实验介绍
6.1.1 关于本实验
为了保证数据库的有效运行,数据库必须在插入/删除操作后,基于客户场景,定期做VACUUM FULL和**YZE,更新统计信息,以便获得更优的性能;
- VACUUM FULL可回收已更新或已删除的数据所占据的磁盘空间,同时将小数据文件合并;
- VACUUM对每个表维护了一个可视化映射来跟踪包含对别的活动事务可见的数组的页。一个普通的索引扫描首先通过可视化映射来获取对应的数组,来检查是否对当前事务可见。若无法获取,再通过堆数组抓取的方式来检查。因此更新表的可视化映射,可加速唯一索引扫描;
- **YZE可收集与数据库中表内容相关的统计信息。统计结果存储在系统表PG_STATISTIC中。查询优化器会使用这些统计数据,生成最有效的执行计划。
- 数据库经过多次删除操作后,索引页面上的索引键将被删除,造成索引膨胀。例行重建索引,可有效的提高查询效率。本实验主要是通过使用VACUUM、VACUUM FULL FULL来收缩表,用**YZE来收集表的统计信息以及对表上的索引进行重建。
6.1.2 实验目的
掌握VACUUM、VACUUM FULL FULL、**YZE基本的使用及如何重建索引;
6.2 场景设置及操作步骤
步骤 1 用root用户登录装有openGauss数据库服务的操作系统然后用 su - omm命令切换至omm用户环境,登录后信息如下。
Welcome to 4.19.90-2003.4.0.0036.oe1.aarch64
System information as of time: Tue Jul 27 16:21:11 CST 2020
System load: 0.01
Processes: 109
Memory used: 6.7%
Swap used: 0.0%
Usage On: 15%
IP address: 192.168.0.96
Users online: 1
[root@ecs-e1b3 ~]# su - omm
Last login: Fri Jul 27 16:22:11 CST 2020 on pts/0
Welcome to 4.19.90-2003.4.0.0036.oe1.aarch64
System information as of time: Tue Jul 27 16:21:11 CST 2020
System load: 0.01
Processes: 111
Memory used: 7.0%
Swap used: 0.0%
Usage On: 15%
IP address: 192.168.0.96
Users online: 1
[omm@ecs-e1b3 ~]$
步骤 2 启动服务器后,然后使用gsql客户端以管理员用户身份连接postgres数据库,假设端口号为26000。
启动数据库服务。
[omm@ecs-e1b3 ~]$ gs_om -t start;
Starting cluster.
=========================================
=========================================
Successfully started.
连接postgres数据库。
[omm@ecs-e1b3 ~]$ gsql -d postgres -p 26000 -r
gsql ((openGauss 1.1.0 build 38a9312a) compiled at 2020-05-27 14:56:08 commit 472 last mr 549 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
postgres=#
步骤 3 创建student表。
postgres=# drop table student;
postgres=# CREATE TABLE student
( std_id INT NOT NULL,
std_name VARCHAR(20) NOT NULL,
std_** VARCHAR(6),
std_birth DATE,
std_in DATE NOT NULL,
std_address VARCHAR(100)
);
CREATE TABLE
步骤 4 表数据插入。
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (1,'张一','男','1993-01-01','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (2,'张二','男','1993-01-02','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (3,'张三','男','1993-01-03','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (4,'张四','男','1993-01-04','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (5,'张五','男','1993-01-05','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (6,'张六','男','1993-01-06','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (7,'张七','男','1993-01-07','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (8,'张八','男','1993-01-08','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (9,'张九','男','1993-01-09','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (10,'李一','男','1993-01-10','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (11,'李二','男','1993-01-11','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (12,'李三','男','1993-01-12','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (13,'李四','男','1993-01-13','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (14,'李五','男','1993-01-14','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (15,'李六','男','1993-01-15','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (16,'李七','男','1993-01-16','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (17,'李八','男','1993-01-17','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (18,'李九','男','1993-01-18','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (19,'王一','男','1993-01-19','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (20,'王二','男','1993-01-20','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (21,'王三','男','1993-01-21','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (22,'王四','男','1993-01-22','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (23,'王五','男','1993-01-23','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (24,'王六','男','1993-01-24','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (25,'王七','男','1993-01-25','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (26,'王八','男','1993-01-26','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (27,'王九','男','1993-01-27','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (28,'钱一','男','1993-01-28','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (29,'钱二','男','1993-01-29','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (30,'钱三','男','1993-01-30','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (31,'钱四','男','1993-02-01','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (32,'钱五','男','1993-02-02','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (33,'钱六','男','1993-02-03','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (34,'钱七','男','1993-02-04','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (35,'钱八','男','1993-02-05','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (36,'钱九','男','1993-02-06','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (37,'吴一','男','1993-02-07','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (38,'吴二','男','1993-02-08','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (39,'吴三','男','1993-02-09','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (40,'吴四','男','1993-02-10','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (41,'吴五','男','1993-02-11','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (42,'吴六','男','1993-02-12','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (43,'吴七','男','1993-02-13','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (44,'吴八','男','1993-02-14','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (45,'吴九','男','1993-02-15','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (46,'柳一','男','1993-02-16','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (47,'柳二','男','1993-02-17','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (48,'柳三','男','1993-02-18','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (49,'柳四','男','1993-02-19','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (50,'柳五','男','1993-02-20','2011-09-01','江苏省南京市雨花台区');
步骤 5 数据查询统计。
postgres=# select count(*) from student;
count
-------
50
(1 row)
postgres=# select * from student order by std_id;
std_id | std_name | std_** | std_birth | std_in | std_address
--------+----------+---------+---------------------+---------------------+----------------------
1 | 张一 | 男 | 1993-01-01 00:00:00 | 2011-09-01 00:00:00 | 江苏省南京市雨花台区
2 | 张二 | 男 | 1993-01-02 00:00:00 | 2011-09-01 00:00:00 | 江苏省南京市雨花台区
3 | 张三 | 男 | 1993-01-03 00:00:00 | 2011-09-01 00:00:00 | 江苏省南京市雨花台区
4 | 张四 | 男 | 1993-01-04 00:00:00 | 2011-09-01 00:00:00 | 江苏省南京市雨花台区
5 | 张五 | 男 | 1993-01-05 00:00:00 | 2011-09-01 00:00:00 | 江苏省南京市雨花台区
……………..
步骤 6 查看表信息。
postgres=# \d student
Table "public.student"
Column | Type | Modifiers
-------------+--------------------------------+-----------
std_id | integer | not null
std_name | character varying(20) | not null
std_** | character varying(6) |
std_birth | timestamp(0) without time zone |
std_in | timestamp(0) without time zone | not null
std_address | character varying(100) |
步骤 7 使用VACUUM命令,进行磁盘空间回收。
postgres=# vacuum student;
VACUUM
步骤 8 删除表中数据。
postgres=# delete from student where std_id>30;
DELETE 20
步骤 9 使用VACUUM FULL命令,进行磁盘空间回收。
postgres=# vacuum full student;
VACUUM
步骤 10 使用**YZE语句更新统计信息。
postgres=# **yze student;
**YZE
步骤 11 使用**YZE VERBOSE语句更新统计信息,并输出表的相关信息。
postgres=# **yze verbose student;
INFO: **yzing "public.student"(dn_6001 pid=37195)
INFO: **YZE INFO : "student": scanned 1 of 1 pages, containing 30 live rows and 20 dead rows; 30 rows in sample, 30 estimated total rows(dn_6001 pid=37195)
**YZE
步骤 12 执行VACUUM **YZE命令进行查询优化。
postgres=# vacuum **yze student;
VACUUM
步骤 13 查看特定表的统计信息
postgres=# select relname,n_tup_ins,n_tup_upd,n_tup_del,last_**yze,vacuum_count from PG_STAT_ALL_TABLES where relname='student';
relname | n_tup_ins | n_tup_upd | n_tup_del | last_**yze | vacuum_count
---------+-----------+-----------+-----------+------------------------------+--------------
student | 50 | 0 | 20 | 2020-07-27 17:07:19.17167+08 | 3
(1 row)
postgres=#
PG_STAT_ALL_TABLES视图将包含当前数据库中每个表的一行统计信息,以上查询结果中各列分别表示:
Relname 表名
n_tup_ins 插入行数
n_tup_upd 更新行数
n_tup_del 删除行数
last_**yze 上次手动分析该表的时间
vacuum_count 这个表被手动清理的次数
步骤 14 索引维护。
说明:
- 如果数据发生大量删除后,索引页面上的索引键将被删除,导致索引页面数量的减少,造成索引膨胀。重建索引可回收浪费的空间。
- 新建的索引中逻辑结构相邻的页面,通常在物理结构中也是相邻的,所以一个新建的索引比更新了多次的索引访问速度要快。
- 重建索引有以下两种方式:
1、使用REINDEX语句重建索引;
2、先删除索引(DROP INDEX),再创建索引(CREATE INDEX)。
先在student表的std_name列上创建一个索引,如下:
postgres=# create index inx_stu01 on student(std_name);
CREATE INDEX
postgres=#
方式1:使用REINDEX语句重建索引,具体如下:
postgres=# reindex table student;
REINDEX
postgres=#
方式2:先删除索引(DROP INDEX),再创建索引(CREATE INDEX),具体如下:
postgres=# drop index inx_stu01;
DROP INDEX
postgres=# create index inx_stu01 on student(std_name);
CREATE INDEX
postgres=#
查看表结构信息,具体如下:
postgres=# \d student;
Table "public.student"
Column | Type | Modifiers
-------------+--------------------------------+-----------
std_id | integer | not null
std_name | character varying(20) | not null
std_** | character varying(6) |
std_birth | timestamp(0) without time zone |
std_in | timestamp(0) without time zone | not null
std_address | character varying(100) |
Indexes:
"inx_stu01" btree (std_name) TABLESPACE pg_default
步骤 15 退出数据库
postgres=#\q
例行表、索引的维护实验结束。
至此,openGauss数据库维护管理指导结束。