MVCC多版本并发控制

与锁机制配合实现事务隔离，天生避免幻读。

通过更新数据版本方式实现Repeatable Read级别事务隔离，因为两个事务操作的版本不同，所以不需要加锁，实现了读写隔离，增加并发行。只有写与写需要借助锁机制，保持数据一致性。

MVCC实现

xmin——插入或修改tuple时记录当前事务id

xmax——tuple在别的事务更新或删除时记录当时事务id

cmin/cmax ——标识在同一个事务中多个语句命令的序列值，从0开始，用于同一个事务中实现版本可见性判断

隐藏字段的变化过程

幻读和不可重复读的区别

幻读主要针对insert操作，而不可重复读针对update和delete操作。

如果使用锁机制来实现可重复读隔离级别，执行SQL第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免，是锁机制实现事务隔离场景下的问题。

事务

默认隔离级别：read committed

read uncommitted 在事务中，可以读取其他还没有已经commit的事务的修改内容（在PostgreSQL中设置无效，等同于read committed）

PostgreSQL read uncommitted

read committed 在事务中，可以读取其他已经commit的事务的修改内容，每次读写都会重新拿快照

PostgreSQL默认使用read committed

写操作需要锁，只有上一个对该条目进行修改的事务结束，下一个事务才能继续

PostgreSQL read committed写操作加锁

repeatable read 在事务中，不可以读取其他已经commit的事务的修改内容，使用事务第一次读写时得到的快照

mysql默认使用repeatable read

Repeatable Read级别下PostgreSQL修改了一个记录，另一个事务无法再次修改，即并发事务对同一条目只能一个事务commit成功

PostgreSQL repeatable read左侧释放锁后，右边也无法update操作

Mysql的repeatable read级别下没有这样限制

Mysql repeatable read左侧commit后释放锁，右侧update成功

serializable 在两个并发事务中，其中一个commit后，另一个不可以修改同表数据

PostgreSQL serializable左侧事务update全表，右侧事务不允许提交

mysql serializable级别右侧希望左侧释放表锁插入数据，右侧希望左侧释放插入那行的锁，所以死锁，切断了左侧事务

索引

PostgreSQL默认使用B-tree索引，索引类型： B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。

B-trees have a few important traits:

B-trees are balanced, that is, each leaf page is separated from the root by the same number of internal pages. Therefore, search for any value takes the same time.每个叶子节点到根结点距离都相同

B-trees are multi-branched, that is, each page (usually 8 KB) contains a lot of (hundreds) TIDs(tupleId). As a result, the depth of B-trees is pretty small, actually up to 4–5 for very large tables.每个非叶子节点都有多个子节点，B-Tree最多四到五层

Data in the index is sorted in nondecreasing order (both between pages and inside each page), and same-level pages are connected to one another by a bidirectional list. Therefore, we can get an ordered data set just by a list walk one or the other direction without returning to the root each time.节点默认按照升序排列，手动设置降序create index on aircrafts(range desc)，每节点都有和右兄弟节点的双向指针，不必每次遍历回到根结点

在一个大表上创建一个索引会耗费很长的时间。默认情况下，PostgreSQL允许在索引创建时并行地进行读（SELECT命令），但写（INSERT、UPDATE和DELETE）则会被阻塞直到索引创建完成。在生产环境中这通常是不可接受的。

一个索引被创建后，对于索引数据的每个操作，无论是插入，删除还是更新表行，也需要在同一事务中更新该表的索引。这增加了数据操作的负担。

不需要手工在UNIQUE列上创建索引，PostgreSQL会自动创建

可以使用Btree索引的操作符

<

<=

=

>=

>

BETWEEN

IN

IS NULL

IS NOT NULL

模糊查询常量在开头也可以使用Btree索引

LIKE 'foo%'

~ '^foo'

但 LIKE '%bar' 不会。

在数据量少时使用索引并不一定比扫表性能好，PostgreSQL会自动优化

复合索引最多可以指定32个列，复合索引中的第一个索引字段(最左边)被包含其中时，才可以获得最高效率。而且只有使用AND连接是才能使用索引，使用OR连接不能直接使用索引。

索引是a和b的联合索引，使用索引的第一个条件，才能发挥索引更好的性能

组合多个索引或者同一个索引使用多次，系统扫描每一个所需的索引并在内存中准备一个Bitmap用于指示表中符合索引条件的行的位置。然后这些Bitmap会被根据查询的需要AND和OR起来。最后，实际的表行将被访问并返回。

table_a_index_a_b是a和b的复合索引，因为用了or所以也算组合多个索引查询

a与b各自有索引

Sequence Scan、Index Scan、Bitmap Heap Scan的理解

Sequence Scan全表扫描，从表的第一行开始顺序扫描，一直扫描到最后满足查询条件的记录。

Index Scan索引扫描，其原理是通过索引key去寻找tuple的ctid，然后回表获取对应的数据。

Bitmap Heap Scan位图扫描，当只处理几个值时，索引扫描就可以正常工作。但是，随着检索到的行数的增加，它更有可能多次返回同一PAGE。因此，优化器将切换到位图扫描。

是对于每个查询条件，系统扫描每一个所需的索引并在内存中准备一个Bitmap用于指示表中符合索引条件的行的位置。然后根据查询的需要“与”和“或”起来，然后再进行Recheck。

* 原始索引中的任何排序都会被丢失

* PAGE 是PostgreSQL的存储单元

NULL

null值与任何值做布尔运算，结果都是false

null列索引注意一定要一致，否则索引用不到

数据库版本

PostgreSQL version 9.6

Mysql version 5.7

PostgreSQL 学习