PostgreSQL基础（八）：表的基本操作（二）
原创PostgreSQL基础（八）：表的基本操作（二）
原创
表的基本操作（二）
一、视图
跟MySQL的没啥区别，把一些复杂的操作封装起来，还可以隐藏一些敏感数据。
视图对于用户来说，就是一张真实的表，可以直接基于视图查询一张或者多张表的信息。
视图对于开发来说，就是一条SQL语句。
在PGSQL中，简单（单表）的视图是允许写操作的。
但是强烈不推荐对视图进行写操作，虽然PGSQL默认允许（简单的视图）。
写入的时候，其实修改的是表本身
-- 构建一个简单视图
create view vw_score as
(select id,math_score from score);
select * from vw_score;
update vw_score set math_score = 99 where id = 2;多表视图
-- 复杂视图(两张表关联)
create view vw_student_score as
(select stu.id as id ,stu.name as name ,score.math_score from student stu,score score where stu.id = score.student_id);
select * from vw_student_score;
update vw_student_score set math_score =999 where id = 2;
二、索引
1、索引的基本概念
先了解概念和使用
索引是数据库中快速查询数据的方法。
索引能提升查询效率的同时，也会带来一些问题
- 增加了存储空间
- 写操作时，花费的时间比较多
索引可以提升效率，甚至还可以给字段做一些约束
2、索引的分类
B-Tree索引：最常用的索引。
Hash索引：跟MySQL类似，做等值判断，范围不可以。
GIN索引：针对字段的多个值的类型，比如数组类型。
3、创建索引看效果
准备大量测试数据，方便查看索引效果。
-- 测试索引效果
create table tb_index(
id bigserial primary key,
name varchar(64),
phone varchar(64)[]
);
-- 添加300W条数据测试效果
do $$
declare
i int := 0;
begin
while i < 3000000 loop
i = i + 1;
insert into
tb_index
(name,phone)
values
(md5(random()::text || current_timestamp::text)::uuid,array[random()::varchar(64),random()::varchar(64)]);
end loop;
end;
$$ language plpgsql;在没有索引的情况下，先基于name做等值查询，看时间，同时看执行计划。
-- c0064192-1836-b019-c649-b368c2be31ca
select * from tb_index where id = 2222222;
select * from tb_index where name = 'c0064192-1836-b019-c649-b368c2be31ca';
explain select * from tb_index where name = 'c0064192-1836-b019-c649-b368c2be31ca';
-- Seq Scan 这个代表全表扫描
-- 时间大致0.3秒左右在有索引的情况下，再基于name做等值查询，看时间，同时看执行计划。
-- name字段构建索引（默认就是b-tree）
create index index_tb_index_name on tb_index(name);
-- 测试效果
select * from tb_index where name = 'c0064192-1836-b019-c649-b368c2be31ca';
explain select * from tb_index where name = 'c0064192-1836-b019-c649-b368c2be31ca';
-- Index Scan 使用索引
-- 0.1s左右测试GIN索引效果
在没有索引的情况下，基于phone字段做包含查询
-- phone：{0.6925242730781953,0.8569644964711074}
select * from tb_index where phone @> array['0.6925242730781953'::varchar(64)];
explain select * from tb_index where phone @> array['0.6925242730781953'::varchar(64)];
-- Seq Scan 全表扫描
-- 0.5s左右给phone字段构建GIN索引，在查询。
-- 给phone字符串数组类型字段构建一个GIN索引
create index index_tb_index_phone_gin on tb_index using gin(phone);
-- 查询
select * from tb_index where phone @> array['0.6925242730781953'::varchar(64)];
explain select * from tb_index where phone @> array['0.6925242730781953'::varchar(64)];
-- Bitmap Index 位图扫描
-- 0.1s以内完成三、物化视图
前面说过普通视图，本质就是一个SQL语句，普通的视图并不会本地磁盘存储任何物理。
每次查询视图都是执行这个SQL。效率有点问题。
物化视图从名字上就可以看出来，必然是要持久化一份数据的。使用套路和视图基本一致。这样一来查询物化视图，就相当于查询一张单独的表。相比之前的普通视图，物化视图就不需要每次都查询复杂SQL，每次查询的都是真实的物理存储地址中的一份数据（表）。
物化视图因为会持久化到本地，完全脱离原来的表结构。
而且物化视图是可以单独设置索引等信息来提升物化视图的查询效率。
But，有好处就有坏处，更新时间不太好把控。 如果更新频繁，对数据库压力也不小。 如果更新不频繁，会造成数据存在延迟问题，实时性就不好了。
如果要更新物化视图，可以采用触发器的形式，当原表中的数据被写后，可以通过触发器执行同步物化视图的操作。或者就基于定时任务去完成物化视图的数据同步。
看一下语法：
-- 构建物化视图
create materialized view mv_test as (select id,name,price from test);
-- 操作物化视图和操作表的方式没啥区别。
select * from mv_test;
-- 操作原表时，对物化视图没任何影响
insert into test values (4,'月饼',50,10);
-- 物化视图的添加操作(不允许写物化视图)，会报错
insert into mv_test values (5,'大阅兵',66);物化视图如何从原表中进行同步操作。
PostgreSQL中，对物化视图的同步，提供了两种方式，一种是全量更新，另一种是增量更新。
全量更新语法，没什么限制，直接执行，全量更新。
-- 查询原来物化视图的数据
select * from mv_test;
-- 全量更新物化视图
refresh materialized view mv_test;
-- 再次查询物化视图的数据
select * from mv_test;增量更新，增量更新需要一个唯一标识，来判断哪些是增量，同时也会有行数据的版本号约束。
-- 查询原来物化视图的数据
select * from mv_test;
-- 增量更新物化视图，因为物化视图没有唯一索引，无法判断出哪些是增量数据
refresh materialized view concurrently mv_test;
-- 给物化视图添加唯一索引。
create unique index index_mv_test on mv_test(id);
-- 增量更新物化视图
refresh materialized view concurrently mv_test;
-- 再次查询物化视图的数据
select * from mv_test;
-- 增量更新时，即便是修改数据，物化视图的同步，也会根据一个xmin和xmax的字段做正常的数据同步
update test set name = '汤圆' where id = 5;
insert into test values (5,'猪头肉',99,40);
select * from test;原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
