问MySQL & PostgreSQL中的聚类索引性能考虑

EN

MySQL

虽然MySQL文档字面上说的是Typically, the clustered index is synonymous with the primary key，但它们并不是一回事。请记住，创建(_碎屑_指数))的方式是主键的索引页和表的行数据在相同的页面中共存。拥有广泛的主键值(如UUID )将使BTREE页面更宽。它甚至可能导致数据页被分割。由于默认的诺姆b_页面_大小在MySQL中是16 is (这是一个修正了MySQL 5.5和back中的编译- in值)，所以您必须期望数据页有更少的行，并且在每16 is页上为主键导航留出更少的空间。

我以前曾讨论过PRIMARY KEY的含义:参见我的文章InnoDB主密钥效率

PostgreSQL

A 来自Peter的StackOverflow帖子说The maximum length for a value in a B-tree index, which includes primary keys, is one third of the size of a buffer page, by default floor(8192/3) = 2730 bytes.

根据PostgreSQL维基

通过将默认块大小增加到32k，最大表大小、行大小和最大列数可以翻两番。使用表分区还可以增加表的最大大小。

因此，假设您使用32K块而不是默认的8K块。你可以容纳4倍以上的信息，但仍然有某种限制。

幸运的是，UUID只有16个字节。我不认为它会带来令人震惊的缺点。

分析

InnoDB对聚集索引的使用( order可能很不灵活)将受益于较小的键，并且由于不必管理聚集索引中分配密钥的足够空间，因此可以快速写入。

虽然PostgreSQL的存储引擎不像MySQL的InnoDB那样受到限制或绑定，但较小的键无疑必须处理得更快，占用的空间更少。这将提高PostgreSQL、MySQL或任何其他关系型数据库的读写性能。

为了演示结构更改是如何产生影响的，让我们使用MySQL其他存储引擎MyISAM (它是非事务性的，没有聚集索引)。有一次，我拿了一个MyISAM表，并将它的行格式从动态更改为固定长度，性能提高了20%，而没有触及任何其他内容。为了获得更好的阅读性能，我把数据做大了。写入性能也有所提高，因为触发任何空间管理的机制较少(请参阅我的post 在固定大小的字段上使用CHAR和VARCHAR的性能影响是什么？)。

只要读一遍的MySQL文档Optimizing Data Size，你就会得到这样的短语

较小的表通常需要较少的主内存，而它们的内容在查询执行期间正在积极处理。表数据的任何空间减少也会导致较小的索引，这些索引可以更快地处理。尽可能使用最有效(最小)的数据类型。MySQL有许多特殊类型，可以节省磁盘空间和内存。例如，如果可能的话，使用较小的整数类型来获得较小的表。MEDIUMINT通常是一个比INT更好的选择，因为MEDIUMINT列使用的空间比INT少25%。

为了进一步讨论更小的数据类型，我提到了MySQL的选择。程序分析()；。当您运行SELECT * FROM tablename PROCEDURE ANALYSE();时，输出是对您的数据、最小值、最大值、avg值、值的STD的分析，以及(这里是每个列的推荐数据类型)的分析。

如果应用ALTER TABLE命令应用推荐的数据类型，则表最终必须变小。

即使是PostgreSQL也必须从较小的数据类型中获益。怎么做到的？

请回顾一下，PostgreSQL有一个名为吐司(外部属性存储技术)的机制，我之前已经讨论过了(请参阅我的post MySQL blob处理修订版:建议必须在有大列的情况下处理行数据)。显然，这种机制永远不会被触发，因为所有的行都是小的，许多行可以很好地适应PostgreSQL的8K块。

结论

由于您的问题似乎更多地集中在PostgreSQL上，让我这样回答您的问题：

如果我选择UUID作为PK，会产生什么影响？数据库写性能杀手是否也存在于PostgreSQL中，就像在MySQL中一样？

PostgreSQL将以较小的列值处理更快的写入。UUID是16个字节。使用8字节整数作为主键将比UUID更快地编写和处理。一个4字节的整数甚至比这个还要快.从这一切中吸取教训？如果没有必要的话，不要用更广泛的PRIMARY KEY值来减缓自己的速度。

如果UUID不是顺序变量，那么按照创建它们的顺序插入UUID将导致索引中的一些随机叶节点对插入的每一行进行删除。一旦索引足够大，这将降低旋转硬盘上的写入性能，因为它无法合并写入，以便有效地写入磁盘。

如果您正在逃离MySQL，并希望PostgreSQL更好，那么首先考虑一下您拥有的UUID类型。如果它们是“顺序变体”(类型1)，而且如果您按时间进行了一些聚类，那么MySQL (或任何数据库)都可以利用它。下面是对以下内容的讨论：http://mysql.rjweb.org/doc.php/uuid

虽然MySQL没有UUID的数据类型，但是很容易将CHAR(36)转换为/从二进制(16)。上面的链接也显示了这一点。