Postgresql和MYSQL如何选择

这篇文章主要介绍了Postgresql和MYSQL如何选择，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

经常看到有人写关于锁的事情，但常常感觉给人一个感觉，数据库的ACID 是通过锁来控制的，实际上数据库的ACID 控制是复杂的，MVCC 就是一个对资源并发访问时的提高并发访问的有效的方法

在首次定义ACID事务属性时，假定具有可串行性。为了提供严格的可序列化事务结果，使用了2PL(两阶段锁定)机制。在使用2PL时，每次读操作都需要一个共享锁获取，而写操作则需要一个独占锁。

保持数据的完整性，作为一个重要的经验法则，对事务性处理DBs的所有修改都应该在原子事务下进行。而且，每个事务都应该使数据库处于一致的状态，隔离是最难处理的实践????。从理论上讲，非常简单，他隔离保证了所有事务的执行，即使它们同时运行，也“好像”它们是串行执行的。可实践中，它要复杂得多在保持合理性能的同时保持隔离.

多版本并发控制(MVCC)，会创建行的“先前版本”(“快照”)，并将该行的“先前版本”提供给任何可能尝试并发运行的其他事务，而不是在有人开始读取该行时锁定该行。这是有道理的——毕竟，在提交第一个事务之前，不会考虑更改DB的状态。

写到这，会比较枯燥，下面就开始讲点和实际数据库贴边的 MVCC 实现。

就目前掌握的数据库类型，大致解决MVCC的方式有两种

1 新的数据与旧数据分离转移到一个地方，例如undo log，其他人读数据时，从回滚段中把旧的数据读出来，Oracle和MySQL中的innodb引擎是这样做的。

2写新数据时，旧数据不删除，而是把新数据插入，新旧数据在一起。PostgreSQL就是使用的这种实现方法。

那么我们可以对比一下这两种方式的不同

1  Postgresql 中通过行设计和xact 的方式来解决MVCC的问题， 我们可以通过一个表的查询 xmin,xmax,cmin,cmax 来查看相关的原理。

下面的这段代码解释了PG上关于 tuple 设计上的一些原理

typedef struct HeapTupleFields { TransactionId t_xmin;   /* inserting xact ID *

/TransactionId t_xmax;   /* deleting or locking xact ID *

/union{   CommandId t_cid;   /* inserting or deleting command ID, or both *

/   TransactionId t_xvac; /* VACUUM FULL xact ID */}    t_field3; } HeapTupleFields;

• t_xmin 表现的是产生这个行或更高这行的事务ID

• t_xmax 表现的是删除或锁定这个元组的事务ID

• t_cid 包含cmin和cmax两个字段，标识在一个事务里面的这些行的操作顺序，例如插入5行，那这5行的插入顺序是什么，那些tuple 对那些tuple是可见的，这个是一个事务级的可见性的展示。

• t_xvac 存储的是VACUUM FULL 命令的事务ID

当插入一行时，postgres将在该行中存储XID并将其称为xmin。已经提交的并且xmin小于当前事务的XID的每一行对事务都是可见的。这意味着您可以启动一个事务并插入一行，而在该事务提交之前，其他事务不会看到该行。一旦提交并创建了其他事务，它们就能够查看新行，因为它们满足xmin < XID条件——并且创建该行的事务已经完成。

下面我们看看postgresql 表结构，以city表为例

一个表中都有的字段 tableoid,cmax,xmax,cmin,xmin

select attname, attnum, atttypid::regtype, attisdropped::text from pg_attribute where attrelid = 'city'::regclass;

我们举一个例子就能很好的解释MVCC 的具体操作

我们选择一个city 表，然后我们开两个事物，一个更新city_id 1 - 20 另一个事物更新city_id 21 40 

事务1

事务2

不在事务1 和事务2 中看到的

从上面可以总结出

1 每个事务更改操作都会触发 xmin xmax ,改变 

2 每个事务的更改xmin 只会在自己的事务内部看的到，而xmax 就是别的事务正在更改的信息标记

这样MVCC 的初步功能就可以进行下去了，所以postgresql 没有页锁，只有表锁和行锁。

这样做的优点就是事务的回滚非常迅速,但需要经常性的 vacuum

反观MYSQL 的MVCC 采用的是undo log的方式，这和ORACLE 的方式雷同，MVCC 的功能实现并不是在每行中实现的，innodb存储引擎对undo的管理采用段的方式,rollback segment称为回滚段，每个回滚段中有1024个undo log segment。(MYSQL 8 已经有改变)

使所有回滚段(rsegs)驻留在所选的UNDO表空间中不活动。Inactive意味着这些回滚段不会分配给新的事务。清除系统将继续释放不再需要的回滚段。这将分配给回滚段的页面标记为空闲，并减少回滚的逻辑大小。

通过上面的一个UNDO 表空间的大概的流程，可以提出几个问题

1 回滚段是有数量限制的，回滚段的数量限制就是这个数据库系统的同一个时间可以执行事务的数量的限制，每个回滚段维护一个页头，每个页面会划分1024slot 每个slot 会对应一个事务，所以MYSQL 5.7（8.0重新设计了UNDOLOG）另外即使是只读的事务，只要有对临时表的写入，也是分配回滚段的。

例如MYSQL的事务在prepare 阶段，insert undo 和 update undo的状态为prepare，调用trx_undo_set_state_at_prepare，对对应的undo log slot头页面(trx_undo_t::hdr_page_no)，将页面段头的TRX_UNDO_STATE设置为TRX_UNDO_PREPARED，同时修改其他对应字段。

在commit 阶段，Undo状态为TRX_UNDO_CACHED，则加入到回滚段的insert_undo_cached链表上，或者将该undo所占的segment及其所占用的回滚段的slot全部释放掉，修改当前回滚段的大小，并释放undo对象所占的内存，如果是Update_undo操作，则insert_undo不放到History list上。最后事务提交后将回滚段的计数器减一。

其实就是将事务ID 和 回滚段的指针连接起来，同时MYSQL的行中也有两个字段来记录，针对MYSQL 表每一行 都有 6个字节的 db_trx_id , 7个字节的 db_roll_ptr ,undo log对于update或者delete操作，每一行都保存了一个事务Id，修改事务Id为当前Session的事务id,生成数据行事务之前的版本，将当前行的回滚指针指向事务之前的版本。对于insert操作，将当前行的回滚指针指为空，因为insert没有事务操作之前的版本。

数据库如果在执行事务的过程中想要回滚，必然要考虑并发和回滚，这就造成随着并发和回滚的需求，导致占用更多的磁盘空间，而在事务提交后就需要清理掉这些无用的东西，POSTGRESQL 叫 VACUUM ，MYSQL 叫 Purge ，在InnoDB中，更新后的行的最新版本只保留在表中。旧版本的行在回滚段，而删除后的行版本则保留在原处，并标记为以后的清理。因此，须从表本身清理标记任何已删除的行，并从回滚段中清除任何更新后的旧版本的行。查找被删除的记录所需的所有信息。

所以从设计结构上来说postgresql 的结构设计要简单，MYSQL ORACLE 的结构设计要复杂，并且POSTGRESQL 也没有redo等结构，所以针对POSTGRESQL 最大的问题就是VACUUM ， 而MYSQL  INNODB ，则会面对redo ,undo ，purge 等方面的I/O 压力。

纯个人认为，postgresql 在不考虑vacuum 的情况下，性能上的瓶颈要小于MYSQL 方面的复杂结构上产生的影响（可以在非频繁工作期间进行一些其他的回收方式）。postgresql 在使用中要给出的磁盘空间要有余量，mysql  在这方面上要好一些。

所以单纯说那个 better ，没有什么意义，有意义的是你掌握了多少他们的特性  knowledge

感谢你能够认真阅读完这篇文章，希望小编分享的“Postgresql和MYSQL如何选择”这篇文章对大家有帮助，同时也希望大家多多支持亿速云，关注亿速云行业资讯频道，更多相关知识等着你来学习!