一、概述
为了保证数据库的有效运行，数据库必须在插入/删除操作后，基于客户场景，定期做VACUUM FULL和ANALYZE，更新统计信息，以便获得更优的性能；
VACUUM FULL可回收已更新或已删除的数据所占据的磁盘空间，同时将小数据文件合并；
VACUUM对每个表维护了一个可视化映射来跟踪包含对别的活动事务可见的数组的页。一个普通的索引扫描首先通过可视化映射来获取对应的数组，来检查是否对当前事务可见。若无法获取，再通过堆数组抓取的方式来检查。因此更新表的可视化映射，可加速唯一索引扫描；
ANALYZE可收集与数据库中表内容相关的统计信息。统计结果存储在系统表PG_STATISTIC中。查询优化器会使用这些统计数据，生成最有效的执行计划。
数据库经过多次删除操作后，索引页面上的索引键将被删除，造成索引膨胀。例行重建索引，可有效的提高查询效率。
本实验主要是通过使用VACUUM、VACUUM FULL FULL来收缩表，用ANALYZE来收集表的统计信息以及对表上的索引进行重建。
二、试验
1、登录
gsql -d postgres -p 26000 -r
2、创建数据库并插入数据
drop table student;
CREATE TABLE student
( std_id INT NOT NULL,
std_name VARCHAR(20) NOT NULL,
std_sex VARCHAR(6),
std_birth DATE,
std_in DATE NOT NULL,
std_address VARCHAR(100)
);
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (1,'张一','男','1993-01-01','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (2,'张二','男','1993-01-02','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (3,'张三','男','1993-01-03','2011-09-01','江苏省南京市雨花台区');
3、数据查询统计。
postgres=# select count(*) from student;
count
-------
50
(1 row)
postgres=# select * from student order by std_id;
std_id | std_name | std_sex | 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、查看表信息
postgres=# \d student
Table "public.student"
Column | Type | Modifiers
-------------+--------------------------------+-----------
std_id | integer | not null
std_name | character varying(20) | not null
std_sex | character varying(6) |
std_birth | timestamp(0) without time zone |
std_in | timestamp(0) without time zone | not null
std_address | character varying(100) |
5、使用VACUUM命令，进行磁盘空间回收
postgres=# vacuum student;
VACUUM
6、删除表中数据
delete from student where std_id=3;
7、使用VACUUM FULL命令，进行磁盘空间回收
postgres=# vacuum student;
VACUUM
8、使用ANALYZE语句更新统计信息
postgres=# analyze student;
ANALYZE
9、使用ANALYZE VERBOSE语句更新统计信息，并输出表的相关信息
postgres=# analyze verbose student;
INFO: analyzing "public.student"(dn_6001 pid=37195)
INFO: ANALYZE 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)
ANALYZE
10、执行VACUUM ANALYZE命令进行查询优化
vacuum analyze student;
11、查看特定表的统计信息
postgres=# select relname,n_tup_ins,n_tup_upd,n_tup_del,last_analyze,vacuum_count from PG_STAT_ALL_TABLES where relname='student';
relname | n_tup_ins | n_tup_upd | n_tup_del | last_analyze | 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_analyze 上次手动分析该表的时间
vacuum_count 这个表被手动清理的次数
12、索引维护
如果数据发生大量删除后，索引页面上的索引键将被删除，导致索引页面数量的减少，造成索引膨胀。重建索引可回收浪费的空间。
新建的索引中逻辑结构相邻的页面，通常在物理结构中也是相邻的，所以一个新建的索引比更新了多次的索引访问速度要快。
重建索引有以下两种方式：
1、使用REINDEX语句重建索引；
2、先删除索引（DROP INDEX），再创建索引（CREATE INDEX）。
先在student表的std_name列上创建一个索引，如下：
create index inx_stu01 on student(std_name);
方式1：使用REINDEX语句重建索引，具体如下：
reindex table student;
方式2：先删除索引（DROP INDEX），再创建索引（CREATE INDEX），具体如下：
drop index inx_stu01;
create index inx_stu01 on student(std_name);
查看表结构信息，具体如下：
postgres=# \d student;
Table "public.student"
Column | Type | Modifiers
-------------+--------------------------------+-----------
std_id | integer | not null
std_name | character varying(20) | not null
std_sex | 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
13、退出数据库
postgres=#\q
