PostgreSQL自学笔记:9 索引

9 索引

9.1 索引简介

索引是对数据库表中一列或多列值进行排序的一种结构,使用
索引可提高数据库中特定数据的查询速度

9.1.1 索引的含义和特点

索引是一种单独的、存储在磁盘上的数据库结构,他们包含着对
数据表里所有记录的引用指针.索引用于快速找出在某个
或多个列中有一特定值的行,所有PostgreSQL列类型都可
以被索引,对相关列使用索引是提高查询操作时间的最佳
途径

索引是在存储引擎中实现,因此每种存储引擎的索引都不一定完
全相同,并且每种存储引擎也不一定支持所有索引类型.根据
存储引擎定义每个表的最大索引数和最大索引长度.所有存
储引擎支持每个表至少16个索引,总索引长度至少256字节.
大多数存储引擎有更高的限制.

• 索引的优点:

  1. 通过创建唯一索引,可以保证数据库表中每一行数据的唯
     一性
  2. 可以大大加快数据的查询速度,这也是创建索引的主要原因
  3. 在实现数据的参考完整性方面,可以加速表和表之间的连接
  4. 在使用分组和排序子句进行数据查询时.也可以显著减少查
     询中分组和排序的时间.

• 索引的缺点:

  1. 创建索引和维护要耗费时间,并且随着数据量的增加所耗费
     的时间也会增加
  2. 索引需要占磁盘空间,除了数据表占据数据空间之外,每一个
     索引还要占一定的物理空间,如果有大量的索引,索引文件
     可能比数据文件更快达到最大文件尺寸
  3. 当对表中的数据进行增加\删除和修改时,索引也要动态维护,
     这样降低了数据的维护速度

9.1.2 索引的分类

1. B-tree索引
   适合处理那些能够按顺序存储的数据,比喻对一些字段涉及使用
   < <= = >= > 操作符之一进行比较的时候,可以建立一个
   索引

2. Hash索引
   只能处理简单的等于比较.当一个索引的列涉及使用 = 操作符
   进行比较的时候,查询规划器会考虑使用Hash索引

3. GiST索引
   不是单独一种索引类型,而是一种架构,可以在这种架构上实现很
   多不同的索引类型策略,因此,可以使用GiST索引的特定操作
   符类型高度依赖于索引策略(操作符等)

4. GIN索引
   反转索引,可以处理包含多个键的值(比于数组).和GiST类似,GIN
   支持用户定义的索引策略,可以使用GIN索引的特定操作符类型
   根据索引策略的不同而不同

9.1.3 索引的设计原则

1. 索引并非越多越好.如果一个表中有大量的索引,那么不仅会占用大量
   磁盘空间,还会影响insert\delete\update等语句的性能
2. 避免对经常更新的表进行过多的索引,并且索引中的列要尽可能的少.
   对经常用于查询的字段应该创建索引,但要避免添加不必要的字段
3. 数据量小的表最好不要使用索引.数据较少时,查询花费的时间可能比
   遍历索引的时间还要短,索引可能不会产生优化效果
4. 在条件表达式中经常用到的不同值较多的列上建立索引,在不同值少的
   列上不要建立索引.比于学生表'性别'字段上只有'男'与'女'两个不
   同值,建立索引不但不会提高查询效率,反而会严重降低更新速度
5. 当唯一性是某种数据本身的特征时,指定唯一索引.使用唯一索引能够
   确保定义的列的数据完整性,提高查询速度
6. 在频繁进行排序或分组的列上建立索引.如果待排序的列有多个,可以在
   这些列上建立组合索引

9.2 建立索引

create [unique | fulltext | spatial] index 索引名
on 表名 (字段名 [长度],...) [ASC | DESC];

先建表book:

    create table book(
        bookid int not null,
        bookname varchar(255) not null,
        authors varchar(255) not null,
        info varchar(255) null,
        comment varchar(255) null,
        year_publication date not null
    );

1. 创建普通索引
   最基本的索引类型,加快对数据访问速度
   create index bknameidx on book(bookname);
2. 创建唯一索引
   减少索引列操作的执行时间,尤其是对比较庞大的数据表.索引列的值
   必须唯一,但允许空值.如果是组合索引,那么列值的组合必须唯一
   create unique index uniqididx on book(bookid);
3. 创建单列索引
   单列索引是在数据表中的某个字段上创建的索引,一个表中可以创建多
   个单列索引.前面两个例子中创建的索引都是单列索引
   create index bkcmtidx on book(comment);
4. 创建组合索引
   组合索引是在多个字段上创建一个索引
   create index bkauandinfoidx on book(authors,info);

9.3 重命名索引

alter index 旧索引名 rename to 新索引名;

alter index bkauandinfoidx rename to publicbk;

• cookie:
  mysql中索引重命名
  alter table 表名 rename index 旧索引名 to 新索引名;
alter table book rename index bkauandinfoidx to publicbk;

9.4 删除索引

drop index 索引名;

drop index publicbk;

• cookie:
  mysql中删除索引
  alter table 表名 drop index 索引名;

10 视图

10.1 视图概述

10.1.1 视图的含义

视图是一个虚拟表,是从数据库中一个或多个表中导出来的表,还可以
从已经存在的视图的基础上定义

先创建表student和stu_info并插入数据

    create table student(
        s_id int,
        name varchar(40)
    );

    insert into student 
    values(1,'孙悟空'),(2,'猪悟能'),(3,'沙悟净');

    create table stu_info(
        s_id int,
        glass varchar(40),
        addr varchar(90)
    );

    insert into stu_info
    values(1,'五班','花果山'),(2,'六班','高老庄'),
    (3,'七班','流沙河')

10.1.2 视图的作用

1. 简单化
2. 安全性
   • 通过视图,用户只能查询和修改他们所能见到的数据,数据库中的其他
     数据既看不见也取不到.数据库授权命令可以使每个用户对数据库
     的检索限制到特定的数据库对象上
3. 逻辑数据独立性

10.2 创建视图

10.2.1 创建视图的语法形式

create [or replace] [algorithm = {undefined | merge | temptable}]
view 视图名 [(字段名)]
as  查询语句
[with [cascaded | local] check option]

1. create表示创建新的视图,replace表示替换已经创建的视图
2. algorithm表示视图选择的算法
   1. undefined表示自动选择算法
   2. merge表示将使用视图语句与视图定义合并起来,时视图定义的某一部分
      取代语句对应部分
   3. temptable将视图的结果存入临时表,然后用临时表来执行语句
3. with后面的参数表示视图在更新时保证视图的权限范围之内

10.2.2 在单表上创建视图

在数据表t上创建名为view_t的视图:

    create table t(quantity int,price int);
    insert into t values(3,50);

    create view view_t 
    as select quantity,price,quantity * price from t;

`select * from view_t;`

10.2.3 在多表上创建视图

create view stu_glass(id,name,glass)
as select student.s_id,student.name,stu_info.glass
from student,stu_info where student.s_id = stu_info.s_id;

select * from stu_glass;

10.3 查看视图

10.3.2 使用SQL语句在views表中查看视图详细信息

查看数据库中所有视图的详细信息
select * from information_schema.views;

10.4 删除视图

10.4.2 使用SQL语句删除视图

drop view [if exists] 视图名[,视图名...] [restrict | cascade]

删除视图必须拥有drop权限
drop view if exists view_t;

10.6 常见问题及解答

PostgreSQL中视图和表的区别:

1. 视图是已经编译好的SQL语句,是基于SQL语句结果集的可视化表;
   而表不是
2. 视图没有实际的物理记录,而表基本有
3. 表是内容,视图是窗口
4. 表只用物理空间,而视图不占用物理空间,只是逻辑概念的存在.
   表可以及时被修改,但视图只能用创建的语句来修改
5. 视图是查看数据表的一种方法,可以查询数据表中某些字段构成的
   数据,只是一些SQL语句的集合.从安全角度说,视图可以防止用户
   接触数据表,不让其知道表结构
6. 表属于全局模式中的表,是实表;视图属于局部模式的表,是虚表
7. 视图的建立和删除只影响视图本身,不影响对应的表

PostgreSQL中视图和表的联系:

• 视图是在基本表之上建立的表,结构和内容都来自基本表,依据基本表
  而存在.一个视图既可以对应一个基本表,也可以对应多个基本表.
  视图是基本表的抽象和在逻辑意义上建立的新关系

11 触发器

11.1 什么是触发器和触发器函数

一个触发器是一种声明,告诉数据库应该在执行特定操作的时候执行特定的函数.
触发器的执行不需要call语句来调用,也不需要手工启动,只要当一个预定以
的时间发生的时候,就会被PostgreSQL自动调用
触发器函数,是指一个没有参数并且返回trigger类型的函数,在创建触发器之前,
首先需要创建触发器函数

create function fun_name() returns trigger as $fun_name$
    begin
        函数执行代码
    end;
$ fun_name $ language plpgsql;

• 注: fun_name为为触发器函数名称

11.2 创建触发器

create trigger 触发器名 before | after 触发事件
on 表名 for each row execute procedure 触发器函数;

创建一个触发器,使得每次有新数据插入时,其中的时间字段uptime自动变更为
当前时间

1. 创建一个用于测试的数据表

    create table timedb(
        uid int,
        gid int,
        uptime timestamp with time zone
    );

2. 创建一个自定义触发函数

    create function func_timedb () returns trigger as $func_timedb$
        begin
            if (tg_op = 'update') then
                if new.uptime = old.uptime then
                    return null;
                end if;
            end if;
            update timedb set uptime = now() where uid = new.uid and gid = new.gid;
            return null;
        end;
    $func_timedb$ language plpgsql;

3. 创建触发器

    create trigger timedb_update after insert on timedb
        for each row execute procedure func_timedb ();

4. 测试:

    insert into timedb values(1,3);
    select * from timedb;

11.5 删除触发器

drop trigger 触发器名 on 表名;

12 事务处理与并发控制

12.1.3 事务块管理的常用语句

事务的属性:
原子性 一致性 隔离性 持久性

begin;
SQL 语句1;
...
commit;

start transaction 开始一个新的事务块
begin 表示初始化一个事务块.在begin命令后的语句都将在一个事务里面
执行,知道出现commit或rollback.此命令和start transaction等价
commit 表示提交事务
rollback 事务失败时执行回滚操作
set transaction 设置当前事务的特性,对后面的事务没有影响

12.2 PostgreSQL的并非控制

1. 脏读
   当一个事务读取的记录是另一个事务的一部分,如果另一个事务未完成,
   就会产生脏读
2. 幻读
   当一个数据执行insert或delete操作,而该数据行恰好属于某个事务正
   在读取的范围时,就会发生幻读现象
3. 不可重复性读取
   如果一个事务不止一次读取相同的记录,但两次读取中间有另一个事务
   刚好修改了数据,则两次读取的数据将出现差异,此时就发生了非
   重复读取

12.3 锁机制

12.3.1 锁类型

1. SpinLock 自旋锁
2. LWLock 轻量级锁
3. RegularLock 常规锁

13 PostgreSQL用户管理

13.1 组角色管理

创建角色:
create role 角色名;

create role post1;
查看系统中的角色
select rolname from pg_roles;
修改角色名称
alter role 组角色名称 rename to 新角色名;
删除角色
drop role 组角色名称;

13.2 角色的各种权限

1. login 登录

2. superuser 超级用户

3. createdb 创建数据库

4. createrole 创建角色
   create role post2 createdb;

5. 口令
   create role name 口令认证方式 具体口令;

   create role post3 password '123456';

13.3 账户管理

创建用户
create user 用户名;
删除用户
drop user 用户名;
修改密码
alter user 用户名 口令认证方式 新密码;

13.4 组角色和用户权限管理

对组角色授权
alter role 角色名 权限1,权限2...;
对用户授权
alter user 角色名 权限1,权限2...;
收回组角色权限
alter user 角色名 no权限1,no权限2...;

`alter user post nocreatedb;`

收回用户权限
alter role 角色名 no权限1,no权限2...;

13.5 数据库权限管理

修改数据库的拥有者
alter database 数据库名 owner to 拥有者名称;
增加用户的数据表权限
grant 权限 on 数据表 to 用户名称;