psql-09表:视图和索引

时间：2022-01-12 14:45:31

视图

  • 由查询语句定义的虚拟表;从视图中看到的数据可能来自数据库中的一张或多张表,也可能来自外部;

  • 使用视图的原因一般有:

    * 使复制的查询易于理解和使用;

    * 安全原因;

    * 表一些函数返回的结果映射成视图;

  • 一般数据库提供的视图是只读的;

创建视图

create table users(

  id int,

  user_name varchar(40),

  password varchar(40),

  user_email text,

  user_mark text

);

//创建视图过滤掉password字段;

create [temp] view vw_users as select id, user_name, user_email, user_mark from users;

//然后像查询表一样查询视图

//视图也可以为查询的各列重命名;

create view rename_users(no, name, email, mark)

  as select id, user_name, user_email, user_mark from users;

可更新视图

  • Postgresql9.3开始支持直接在视图上进行insert,delete,update等操作;

删除视图

drop view vn_name;

索引

  • 索引是数据库中一种快速查询数据的方法,索引中记录了表中一列或多列的值与其物理位置之间的对应关系;
  • 建立索引加快了对表中记录的查找或排序;但同时索引也有代价:

    * 增加了数据库的存储空间;

    * 在插入和修改数据时要花费较多的时间，因为索引也要随之更新;

索引分类

  • B-tree: 最常用(默认开启)的索引,适合处理等值查询和范围查询;
<

<=

=

>=

>
  • R-tree: 特別适合于空间资料;
<<

&<

&>

>>

@

~=

&&
  • Hash: 只能处理简单的等值查询;
  • GiST: 不是一种单独的索引类型，而是一种架构，可以在架构上实现很多不同的索引策略;
  • SP-GiST: 空间分区GiST索引,主要是通过一些新的缩索引算法提高GiST索引在某个情况下的性能;
  • GIN: 反转索引，可以处理包含很多个键的值，如数组等;与GiST类似,GIN支持用户定义的索引策略;

创建索引

//创建一个通信信息的表,电话号码为数组类型

create table contacts(

  id int primary key,

  name varchar(40),

  phone varchar(32)[],

  address text

);

//创建B-tree索引

create index idx_contacts_name on contacts (name);

//如果想要按照电话号码查找，可以使用gin索引

create index idx_contacts_phone on contacts using gin(phone);

select * from contacts where phone @> array['xxx'::varchar(32)];    //@>是数组操作符，表示包含;
  • 指定存储参数
create ubdex idx_contacts_name on contacts(name) width(fillfactor=50);
  • 升/降序
create index idx_contacts_name on contacts(name [desc|asc]);

//指定非控制排前面/后面

create index idx_contacts_name on contacts(name [desc|asc] nulls [first|last]);

并发创建索引

  • 通常在创建索引时PostgreSQL会锁定表,直到索引创建完毕,仅可以读取表而不能进行其他操作;
  • PostgreSQL支持不长时间阻塞更新情况下创建索引;此时PostgreSQL 会执行表的两次扫描,因此该方法需要更长的时间创建索引;
create index concurrently idx_contacts_name on contacts(name);   //使用concurrently

  • 并发创建索引的时如果创建过程被强行取消，可能会留下一个无效的索引，这个索引仍然会导致更新变慢;

    * 如果创建的是一个唯一索引，这个无效索引还会导致插入重复值失败;

  • 索引填充因子: 定义该索引每页上的可用空间量,适应以后表数据的扩展并减小了页拆分的可能性,从 1 到 100 之间的某个值，指定索引页保留为空的百分比;

修改索引

  • 索引改名:
alter index idx_contacts_name rename to idx_contacts_name_new;
  • 索引移动表空间
alter index idx_contacts_name set tablespace tb_test;
  • 设置索引填充因子
alter index idx_contacts_name set (fillfactor = 75);
  • 把索引的填充因子设置为默认值
alter index idx_contacts_name reset (fillfactor);
  • 查看索引信息
\d+ idx_contacts_name

删除索引

drop index [if exists] name [cascade | restricr]

相关文章