＜POSTGRESQL修炼之道：从小工到专家＞之逻辑结构管理（6-11结）

九、索引

1.索引简介

索引记录了表中一列或者多列的值与其物理位置之间的对应关系

建立索引的好处是加快对表中记录的查找或者排序

• 但是建立索引也有代价，增加了数据库的存储空间，在插入和修改数据时要花费较多的时间去更新索引

2.索引的分类

btree

• 应用场景
  b-tree适合所有的数据类型，支持排序，支持大于、小于、等于、大于或等于、小于或等于的搜索。
• 索引与递归查询结合，还能实现快速的稀疏检索。

hash

• 应用场景
  hash索引存储的是被索引字段VALUE的哈希值，只支持等值查询。
• hash索引特别适用于字段VALUE非常长（不适合b-tree索引，因为b-tree一个PAGE至少要存储3个ENTRY，所以不支持特别长的VALUE）的场景，例如很长的字符串，并且用户只需要等值搜索，建议使用hash index。

gist

• GiST是一个通用的索引接口，可以使用GiST实现b-tree, r-tree等索引结构。
• 不同的类型，支持的索引检索也各不一样。例如：
  1、几何类型，支持位置搜索（包含、相交、在上下左右等），按距离排序。
  2、范围类型，支持位置搜索（包含、相交、在左右等）。
  3、IP类型，支持位置搜索（包含、相交、在左右等）。
  4、空间类型（PostGIS），支持位置搜索（包含、相交、在上下左右等），按距离排序。
  5、标量类型，支持按距离排序。

sp-gist

• SP-GiST类似GiST，是一个通用的索引接口，但是SP-GIST使用了空间分区的方法，使得SP-GiST可以更好的支持非平衡数据结构，例如quad-trees, k-d tree, radis tree.
• 应用场景
  1、几何类型，支持位置搜索（包含、相交、在上下左右等），按距离排序。
  2、范围类型，支持位置搜索（包含、相交、在左右等）。
  3、IP类型，支持位置搜索（包含、相交、在左右等）。

gin

• 原理
  gin是倒排索引，存储被索引字段的VALUE或VALUE的元素，以及行号的list或tree。
• 应用场景
  1、当需要搜索多值类型内的VALUE时，适合多值类型，例如数组、全文检索、TOKEN。（根据不同的类型，支持相交、包含、大于、在左边、在右边等搜索）
  2、当用户的数据比较稀疏时，如果要搜索某个VALUE的值，可以适应btree_gin支持普通btree支持的类型。（支持btree的操作符）
  3、当用户需要按任意列进行搜索时，gin支持多列展开单独建立索引域，同时支持内部多域索引的bitmapAnd, bitmapOr合并，快速的返回按任意列搜索请求的数据。

bitmap

• 原理
  bitmap索引是Greenplum的索引接口，类似GIN倒排，只是bitmap的KEY是列的值，VALUE是BIT（每个BIT对应一行），而不是行号list或tree。

• 应用场景
  当某个字段的唯一值个数在100到10万之间(超出这个范围，不建议使用bitmap)时，如果表的记录数特别多，而且变更不频繁（或者是AO表），那么很适合BITMAP索引，bitmap索引可以实现快速的多个或单个VALUE的搜索。因为只需要对行号的BITMAP进行BIT与或运算，得到最终的BITMAP，从最终的BITMAP映射到行进行提取。

• bitmap与btree一样，都支持 等于，大于，小于，大于等于，小于等于的查询。

3.创建索引

CREATE TABLE contacts(
	id int primary key,
	name varchar(40),
	phone varchar(32)[],
	address text
);

1、B-tree索引
create index idx_contacts_name on contacts(name);

2、数组索引，GIN索引
create index idx_contacts_phone on contacts using gin(phone);

想要查询号码13422334455是谁的
SELECT * FROM contacts WHERE phone @> array['13422334455'::varchar(32)];
 @>是数组的操作符，表示包含，GIN索引能在@>上起作用
注：phone在contacts表中是一个数组类型

3、hash索引的更新不会记录到WAL日志中，所以实际上用得少

4、降序索引
create index idx_contacts_name on contacts(name desc);

5、指定存储参数
create index idx_contacts_name on contacts(name) with(fillfactor=50);

6、指定空值排在前面
create index idx_contacts_name on contacts(name desc nulls first);

7、指定空值排在后面
create index idx_contacts_name on contacts(name DESC NULLS LAST);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

4.并发创建索引

在创建索引时，PG会锁表以防止写入，然后对表做全表扫描，完成索引创建操作，此时其他用户可以读取表，但是插入、更新、删除等操作会一直被阻塞，直到索引创建完毕。
若表更新比较 频繁，且表大，这种方式创建索引是不可以接受的。

• 避免创建索引的长时间阻塞，可以在index关键字后面增加concurrently关键字，可以减少索引的阻塞时间

CREATE TABLE testtab01(id int primary, note text);
插入测试数据
INSERT INTO testtab01 select generate_series(1,5000000),generate_series(1,5000000);

DROP INDEX idx_testtab01_note;
CREATE INDEX CONCURRENTLY idx_testtab01_note on testtab01(note);
开启另一个窗口，其删除操作也不会等待
DELETE FROM testtab01 where id=2;
1
2
3
4
5
6
7
8

5.修改索引

索引改名
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

6.删除索引

DROP INDEX IF EXISTS idx_contacts_name_old;

若有依赖对象依赖这个索引，则使用CASCADE，表示一并删除这些对象删除掉
CREATE TABLE class(
class_no int,
class_name varchar(40)
);

CREATE UNIQUE INDEX index_unique_class_no ON class(class_no);

CREATE TABLE student(
student_no int primary key,
student_name varchar(40),
age int,
class_no REFERENCES class(class_no)
);

DROP INDEX index_unique_class_no CASCADE;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

十、用户及权限管理

十一、事务、并发、锁

1.ACID

原子性atomicity

• 若只执行事务中多个操作的前半部分就会出现错误，那么必须回滚所有的操作，让数据在逻辑上回滚到先前的状态

一致性consistency

• 事务在完成时，必须使所有的数据都保持一致状态
• PG中使用多版本并发控制MVCC来维护数据的一致性。MVCC里对读数据的锁请求和写数据的锁请求不冲突，读不会阻塞写，而写也不会阻塞读。（PG中提供了表和行级锁语句）

隔离性isolation

• 事务查看数据时数据所处的状态，要么是另一并发事务修改他之前的状态，要么是另一个事务修改他之后的状态。
• 事务是不会查看中间状态的数据的。

持久性durability

• 事务完成后，即使今后出现系统故障，数据也将一直保持

2.DDL事务

PG与其他数据库最大的区别是，大多数DDL可以包含在一个事务中，而且是可以回滚的。

• 所以非常适合把PG作为sharding的分布式数据库系统的底层数据库

3.事务的使用

（1）psql的默认下，每执行一条Sql语句，都会自动提交
关闭
\set AUTOCOMMIT off
\echo :AUTOCOMMIT

（2）显示使用BEGIN:启动一个事务，相当于关闭了自动提交
begin:
insert into testtab01 values(1);
select * from testtab01;
rollback;
1
2
3
4
5
6
7
8
9
10

4.SAVEPOINT

大的事务可以分为几个部分，第一个部分成功后，可以建一个保存点，若后面的都失败了，可以回滚到这个保存点

begin;
insert into testtab01 values(1);
savepoint my_savepoint01;

回滚到上一个保存点
rollback to SAVEPOINT my_savepoint01;

1
2
3
4
5
6
7

5.事务的隔离级别

READ UNCOMMITTED读未提交
READ COMMITTED读已提交
REPETABLE READ重复读
SERIALIZABLE串行化

并发事务不一致级别

• 脏读
  一个事务事务读取了另一个未提交事务写入的数据
• 不可重复读
  一个事务重新读取前面读取过的数据时，发现该数据已经被另一个已提交事务修改了
• 幻读
  一个事务开始后，需要根据数据库中现有的数据做一些更新，于是重新执行一个查询，返回一套符合查询条件的行，这时发现这些行因为其他最近提交的事务而发生了改变，导致现有的事务如果再进行下去就可能会出现逻辑错误

PG默认的隔离级别是读已提交

• 当一个事务运行在这个隔离级别，SELECT查询（FOR UPDATE/SHARE子句）只能看到查询开始之前已经提交的数据，而无法看到未提交的数据或在查询执行期间其他事务已提交的数据。
• SELECT查询看到的是在查询开始运行瞬间的一个快照

6.两阶段提交

多台数据库之间的原子性，需要通过两阶段来实现，两阶段提交时实现分布式事务的关键。

set max_prepared_transaction=10;

pg_ctl stop -D $PGDATA
pg_ctl start -D $PGDATA
psql postgres

vim postgresql.conf修改max_prepared_transaction=10

create table testtab01(id int primary key);

启动一个事务
begin;
insert into testtab01 values(1);

事务第一阶段提交：准备事务提交
osdba_global_trans_0001是全局事务的ID，PG数据库一旦成功执行这条命令，就会把事务持久化
PREPARE TRANSACTION 'osdba_global_trans_0001'

事务第二阶段提交：真正事务提交
COMMIT PREPARED 'osdba_global_trans_0001'


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

7.锁机制

当要查询、插入、更新、删除表中的数据时，首先要获得表上的锁，然后再获得行上的锁

表级锁

• 只有SHARE（读锁）和EXCLUSIVE（写锁）两种锁
  （1）SHARE（读锁），S
  可为多个事务加上此锁，只要任意一个人不释放这个读锁，则其他人就不能修改这个表
  （2）EXCLUSIVE（写锁），X
  别的进程既不能写也不能读；
  PG后期实现了多版本功能：若修改了某行数据，实际上并没有改原来的那行数据，而是另外复制一个新行，修改都在新行上，事务都不提交，别是是看不到这条数据的。修改过程中，读数据的人仍然可以读取到旧的数据，所以增加了ACCESS SHARE锁，表明即使正在修改数据的情况下也允许读数据，ACCESS EXCLUSION锁表明即使有多版本的功能，也不允许访问数据。（ACCESS表面是与多版本读相关）
• 表级锁加锁的对象是表，使得加锁范围太大，并发度低，所以提出了行级锁

行级锁与表级锁冲突

• Mysql中使用意向锁来解决这个问题，即：将在表的部分行上加共享锁或排他锁。
• PG类似，使用ROW SHARE（对应Mysql的意向共享锁IS）和ROW EXCLUSIVE（对应Mysql的意向排它锁IX）
• 由于意向锁之间不会产生冲突，且意向排他锁互相之间也不会产生冲突，所以需要更严格的锁，这就产生了：SHARE UPDATE EXCLUSIVE和SHARE ROW EXCLUSIVE

行级锁

• 只有2个：共享锁和排他锁
• PG中不称之为读锁的原因是，由于有多版本的实现，所以实际读取行数据时，并不会再行上执行任何锁

8.死锁及防范

• PG能自动检测到死锁，然后会退出其中一个事务，从而允许其他事务完成
  

9.表级锁命令LOCK TABLE

PG中，事务自己的锁是从不冲突的，所以一个事务可以持有SHARE锁，再请求一个ROW EXCLUSIVE锁，也不会阻塞自己

显式在表上加锁
LOCK TABLE
1
2

10.行级锁命令

由SELECT命令添加

11.锁的查看

通过查询系统视图pg_locks来查找

psql postgres
select pg_backend_pid();

锁定一张表
begin;
lock table testtab01;

查看数据库中锁的情况
select locktype,
		relation::regclass as rel,
		virtualxid as vxid,
		transactionid as xid,
		virtualtransaction as vsid2,
		pid,
		mode,
		granted
from pg_locks
where pid = 8127;
想要查看被锁住的进程，只要查看视图pg_locks中granted字段的值为False的进程就就可以了；

行锁
加上行锁，是先在表上加一个表级意向锁，在相应主键上加意向锁；
行锁是会在数据行上加上自己的xid，另一个进程读到这一行时，若发现有行锁，会把行上另一个事务的xid
读取出来，然后再找个xid上加上Share锁，由于之间持有行锁的进程已经在此xid上加了Exclusive的锁，
所以后面要更新这行的进程会被阻塞。
begin;
select * from testtab01 where id=1 for update;


判断哪一行被阻塞了？
在sql上加上page和tuple字段，这两个字段组合就是系统字段ctid
select locktype,
		relation::regclass as rel,
		page||','tuple as ctid
		virtualxid as vxid,
		transactionid as xid,
		virtualtransaction as vsid2,
		pid,
		mode,
		granted
from pg_locks
where pid = (8416,8112);

SELECT * FROM testtab01 WHERE ctid='(0,1)';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

virtualxid 与virtualtransaction 关系