psql-09表:视图和索引
视图
-
由查询语句定义的虚拟表;从视图中看到的数据可能来自数据库中的一张或多张表,也可能来自外部;
-
使用视图的原因一般有:
* 使复制的查询易于理解和使用;
* 安全原因;
* 表一些函数返回的结果映射成视图; -
一般数据库提供的视图是只读的;
创建视图
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]
