自流井移动网站建设,怎么进入凡科建设的网站,高端人才做兼职的招聘网站有哪些,在源码之家下载的网站模板可以作为自己的网站吗主要区别及示例
简而言之#xff0c;Postgres 和 MySQL 之间的主要区别实际上归结为主索引和辅助索引的实现方式以及数据的存储和更新方式。
让我们进一步探讨这个问题。
但首先... 基础知识
索引是一种数据结构#xff08;主要是 B 树#xff09;#xff0c;允许通过…主要区别及示例
简而言之Postgres 和 MySQL 之间的主要区别实际上归结为主索引和辅助索引的实现方式以及数据的存储和更新方式。
让我们进一步探讨这个问题。
但首先... 基础知识
索引是一种数据结构主要是 B 树允许通过多层节点进行键的搜索数据库将其实现为页面。树的遍历允许消除不包含结果的页面并缩小包含结果的页面的范围。这一过程一直持续到找到包含键的叶子页面。
叶子节点或页面包含有序键及其值的列表。当找到一个键时可以获取其值并且页面会被缓存在数据库的共享缓冲区中希望未来的查询可能会请求相同页面中的键。 这最后一句是理解数据库工程、管理、编程和建模的基本原则。了解查询是否命中页面中相邻的键将最大程度地减少 I/O 并提高性能。 B 树索引中的键是创建索引所在表的列或多个列而值在 Postgres 和 MySQL 中的实现方式有所不同。让我们探讨一下 Postgres 和 MySQL 中值的含义。
MySQL
在主索引中值是带有所有属性 * 的完整行对象。这就是为什么主索引通常被称为聚簇索引或我更喜欢的术语 索引组织表。这意味着主索引就是表本身。 * 注意对于行存储这是正确的。数据库可能使用不同的存储模型如列存储、图形或文档存储从根本上讲这些也可以作为潜在的值。 如果在主索引中查找一个键你会找到包含该键的页面和它的值该值是该键对应的完整行不需要额外的 I/O 操作来获取其他列。
在二级索引中键是你索引的列或多个列而值是指向实际存储完整行位置的指针。二级索引叶子页面的值通常是主键。
这就是 MySQL 的情况。在 MySQL 中所有的表都必须有一个主索引而所有额外的二级索引都指向主键。如果你在 MySQL 表中不创建主键系统会为你自动创建一个。 Postgres
在 Postgres 中严格来说没有主索引所有的索引都是二级索引它们都指向加载在堆中的数据页中由系统管理的元组标识符tuple ids。堆中的表数据是无序的不像主索引叶子页是有序的。因此如果你插入了 1-100 行并且它们都在同一页中然后后来更新了 1-20 行这 20 行可能会跳转到另一页并且变得无序。而在聚簇主索引中插入操作必须按照键的顺序插入到相应的页中。这就是为什么 Postgres 表通常被称为 nbsp;堆有序表nbsp;而不是 索引组织表 。
需要注意的是在 Postgres 中更新和删除实际上是插入操作。每次更新或删除都会创建一个新的元组标识符tuple id而旧的元组标识符则保留为了多版本并发控制MVCC的原因。我稍后会在本文中探讨这个问题。 事实上仅仅使用元组标识符是不够的。实际上我们需要同时知道元组标识符和页面编号这被称为 c_tid。想一想仅仅知道元组标识符是不够的我们需要知道元组所在的页。这是在 MySQL 中不需要做的事情因为我们实际上是通过查找来找到主键所在的页。而在 Postgres 中我们只需要进行一次 I/O 操作就可以获取到完整的行数据。 查询费用
请参考以下示例中的表格。
#TABLE T; #PRIMARY INDEX ON PK AND SECONDARY INDEX ON C2, NO INDEX ON C1 # C1 and C2 are text # PK is integer | PK | C1 | C2 | |----|----|----| | 1 | x1 | x2 | | 2 | y1 | y2 | | 3 | z1 | z1 |
让我们比较一下 MySQL 和 Postgres 中发生的情况。
SELECT * FROM T WHERE C2 x2;
在 MySQL 中执行这个查询将会产生两次 B 树查找。首先我们需要使用二级索引查找 x2 的主键找到主键值为 1然后再使用主索引进行另一次查找找到完整的行数据因此返回了所有属性因此有 * 号。
在 Postgres 中查找任何二级索引只需要进行一次索引查找然后进行一次常量的单个 I/O 操作以获取包含完整行数据的页。一次 B 树查找要比两次查找好。
为了使这个示例更加有趣假设 C2 不是唯一的并且有多个 x2 的条目那么我们将会找到匹配 x2 的大量 tids或在 MySQL 中的 PK。问题是这些行标识符将位于不同的页面导致随机读取。在 MySQL 中这将导致索引查找根据这些键的数量查询优化器可能会选择索引扫描还是基于 seek 的操作但是两个数据库都会导致许多随机 I/O。
Postgres 尝试通过使用位图索引扫描来最小化随机读取将结果分组为页面而不是元组并以尽可能少的 I/O 操作从堆中获取页面。然后应用额外的过滤来呈现候选行。
让我们看一个不同的查询。
SELECT * FROM T WHERE PK BETWEEN 1 AND 3;
对于对主键索引的范围查询我认为 MySQL 在这方面是更好的选择通过一次查找我们可以找到第一个键并在 B 树链接的叶子页上遍历以找到附近的键当我们遍历时我们找到完整的行数据。
Postgres 在这方面可能会遇到一些困难确实二级索引查找将在叶子页上进行相同的 B 树遍历并找到键但它只会收集 tids 和页码。它的工作并没有结束。Postgres 仍然需要在堆中进行随机读取以获取完整的行数据而这些行数据可能分布在堆中的各个位置而不是紧凑地排列在一起特别是如果这些行数据被更新过。
好的我们来进行一次更新操作。
UPDATE T SET C1 ‘XX1’ WHERE PK 1;
在 MySQL 中更新一个未建立索引的列只会导致更新包含该行的叶子页并将其更新为新值。不需要更新其他任何二级索引因为它们都指向的是未发生变化的主键。
在 Postgres 中更新一个未建立索引的列将生成一个新的元组并可能需要更新所有的二级索引以使用新的元组 ID因为它们只知道旧的元组 ID。这会导致许多写入 I/O 操作。Uber 在 2016 年对此不太满意这也是他们从 Postgres 切换到 MySQL 的主要原因之一。 我在这里说 “可能” 是因为在 Postgres 中有一种优化方法称为 HOT仅堆元组不要与堆组织表混淆它会在二级索引中保留旧的元组 ID并在堆页头上放置一个指向新元组的链接。 数据类型的重要性
在 MySQL 中选择主键数据类型非常重要因为该键将出现在所有的二级索引中。例如如果使用 UUID 作为主键会导致所有二级索引的大小膨胀增加存储和读取 I/O 操作的开销。
在 Postgres 中元组 ID 固定为 4 个字节因此二级索引中不会包含 UUID 值而只包含指向堆的元组 ID。
Undo 日志
所有现代数据库都支持多版本并发控制MVCC。在简单的读已提交隔离级别中如果事务nbsp;tx1nbsp;更新了一行但尚未提交而同时另一个并发事务nbsp;tx2nbsp;想要读取该行它必须读取旧的行而不是更新后的行。大多数数据库包括 MySQL使用 undo 日志来实现此功能。
当事务对一行进行更改时更改会被写入共享缓冲池中的页面因此包含该行的页面始终具有最新的数据。然后事务会在 undo 日志中记录如何撤消对行的最新更改的信息足够构建旧状态的信息这样基于其隔离级别仍需要旧状态的并发事务必须解析 undo 日志并构建旧行。
你可能会想知道将未提交的更改写入页面是否是一个好主意。如果后台进程在事务提交之前将页面刷新到磁盘然后数据库崩溃会发生什么这就是 undo 日志至关重要的地方。在崩溃后会使用 undo 日志在数据库启动时撤消未提交的更改。
不可否认对于长时间运行的事务undo 日志会对其他正在运行的事务产生影响。需要更多的 I/O 操作来构建旧状态并且 undo 日志可能会满导致事务失败的可能性。 在某种情况下我曾经看到一个数据库系统在运行了一个持续 3 小时的未提交长事务后需要一个多小时才能从崩溃中恢复。是的要尽量避免长时间的事务。 Postgres 在这方面处理方式完全不同每次更新、插入和删除都会得到一份具有新的元组 ID 的新行副本并附带有关创建该元组的事务 ID 和删除该元组的事务 ID 的提示。因此Postgres 可以安全地将更改写入数据页面并且并发事务可以根据其事务 ID 读取旧的或新的元组。聪明的设计。
当然没有解决方案是没有问题的。我们实际上已经谈论了在二级索引上创建新元组 ID 的代价。此外如果所有正在运行的事务 ID 都大于删除元组的事务 ID则 Postgres 需要清除不再需要的旧元组。
进程与线程
MySQL 使用线程Postgres 使用进程在这两种选择中都有各自的优缺点。
在数据库系统中我更喜欢线程而不是进程。因为线程更轻量级并共享其父进程的虚拟内存地址。与较小的线程控制块TCB相比进程带来了专用虚拟内存和更大的控制块PCB的开销。
如果我们最终要共享内存并处理互斥锁和信号量为什么不使用线程呢这只是我的个人观点。
总结
你可以选择适合你的数据库系统。真正重要的是将你的使用情况和查询进行分解了解每个数据库的功能看看哪些适合你哪些不适合你。
这里没有对错之分。
