主页 / dba / 问题 / 305649
Accepted
O. Jones
Asked: 2022-01-06 04:20:11 +0800 CST

顺序（自动递增）ID 是否需要唯一索引？

  • 772

如果顺序 AUTO_INCREMENT 列也不是 MySQL 表的主键，它是否必须有自己的 UNIQUE 索引才能确保长期数据完整性？

我正在重新索引一个特定的 MySQL 表以加速某些非常常见的 WHERE 子句。该表来自WordPress CMS。它的发行版外定义，简化，就是这样。

CREATE TABLE wp_postmeta (
    meta_id BIGINT UNSIGNED AUTO_INCREMENT,
    post_id BIGINT UNSIGNED,
    meta_key VARCHAR(255),
    meta_value LONGTEXT,
    PRIMARY KEY (meta_id),
    INDEX post_id (post_id),
    INDEX meta_key (meta_key)
);

MariaDB / MySQL (InnoDB) 现在使用聚集索引，所以我将 PK 更改为：

    PRIMARY KEY (post_id, meta_key, meta_id)

post_id = 42 AND meta_key = 'value'这意味着可以使用聚集索引和 LONGTEXT 获取来满足WHERE 过滤器等。事实证明，它可以解决我的用户的性能问题。

问题是：这个重新索引的表是否还需要在自动递增 ID 列上建立唯一索引才能长期保持正常工作？或者我可以省略该索引以节省一点空间和 UPDATE / INSERT 性能吗？

    UNIQUE INDEX meta_id (meta_id),

（我知道可能有比这个更好的表格设计。但是我目前的任务不允许我更改表格，只能更改索引。）

mysql index-tuning

5 个回答

  1. 原来这是一个愚蠢的问题。DBMS 不允许没有索引的自动递增列。尝试执行此操作时会引发错误 1075 。

    由于问题中未提及其他过滤器模式，我将使用这些索引。

    PRIMARY KEY (post_id, meta_key, meta_id)
UNIQUE INDEX meta_id (meta_id)
INDEX meta_key (meta_key, meta_value(32), post_id)
INDEX meta_value (meta_value(32))

    前缀索引位于 LONGTEXT 列上。如果meta_key我能做到

    INDEX meta_key (meta_key, meta_value(32)) INCLUDE(post_id)

    我会，但是这个 RDBMS 不会在索引中包含 INCLUDE() 东西。

    • 2
  2. Best Answer

    你想改变PRIMARY KEY从

    PRIMARY KEY (meta_id)

    至

    PRIMARY KEY (post_id, meta_key, meta_id)

    这在 InnoDB 引擎中是允许的，但它要求您有一个索引（不一定UNIQUE在(meta_id)或具有更多列并meta_id作为第一列的索引。

    所以，回答最后一个问题：

    或者我可以省略该索引以节省一点空间和 UPDATE / INSERT 性能吗？

    不，您仍然需要 meta_id 上的索引和（是否唯一）额外空间。

    重新提出关于完整性的问题：

    如果顺序 AUTO_INCREMENT 列也不是 MySQL 表的主键，它是否必须有自己的 UNIQUE 索引以确保长期数据完整性？

    这实际上取决于是否需要唯一性(meta_id)。您必须检查是否有任何其他 WordPress 程序和您的应用程序依赖于 (meta_id) 是唯一的。

    如果没有，您不需要将索引设置为UNIQUE.

    不过，作为预防措施，我建议您将其保留为 UNIQUE。您永远不知道几年后，您或下一个开发人员是否决定将 PK 更改回默认值，或者您添加一个与非唯一 meta_id 值中断的插件。或者更改为不同的平台并拥有这些独特的功能可以使迁移更简单。

    回到节省空间的问题上，您可以考虑使用具有 3 列的新 PK，任何其他二级索引（是否唯一）也包含所有这 3 列。

    所以你的索引：

    PRIMARY KEY (post_id, meta_key, meta_id),
UNIQUE INDEX (meta_id),
INDEX post_id (post_id),
INDEX meta_key (meta_key)

    是真的（在引擎盖下）：

    PRIMARY KEY (post_id, meta_key, meta_id),
UNIQUE INDEX (meta_id) INCLUDE (post_id, meta_key),
INDEX post_id (post_id) INCLUDE (meta_key, meta_id),
INDEX meta_key (meta_key) INCLUDE (post_id, meta_id)

    因此，该post_id索引是多余的，您可以删除它而不会造成任何损失。以前使用 , 的任何查询post_id现在都可以使用 PK。

    回复：PK的长度。如果只有一个二级索引，那么拥有时所需的磁盘空间PRIMARY KEY(id), INDEX(foo, bar)几乎与PRIMARY KEY(foo, bar, id), INDEX(id).

    如果有多个二级索引，那么更大的 PK 会导致更多的磁盘空间。

    • 2

  3. 简短的回答：在 InnoDB 中，自动增量列必须是某个索引的最左边的列，要么是主键（聚集）索引，要么是一些二级索引。它不必是唯一键，任何 B 树索引都可以。

    以下无效...

    PRIMARY KEY (post_id, meta_key, meta_id)

    除非另一个索引（又名键）被定义为 meta_id 作为最左边的列。

    KEY (meta_id)

    由于自动增量列本身自然是唯一的，因此将自动增量列与其他列一起创建主键或唯一键是不常见的。

    • 1 
  4. INDEX(meta_id)

    是满足 MySQL 的最低要求。（如其他答案中所述。）

    但是，这允许用户显式地为meta_id. 据推测，WP 从不做这种“愚蠢”的事情。

    UNIQUE(meta_id)

    稍微慢下来INSERTs——因为在检查唯一性之前插入不会返回给客户端。使用 plain INDEX(meta_id)， Insert 将该索引的更新放入“更改缓冲区”，以便稍后存储到该二级索引的 BTree 中。

    UNIQUE和之间的另一个区别INDEX来自SELECTing的特定值meta_id。使用INDEX，查询继续读取行，寻找更多具有相同 的行meta_id。另一方面，UNIQUE可以在第一次出现后停止。

    在担心性能时，这些“差异”都不值得担心。我只是为了完整性而提及它们。

    • 1

  5. 取决于您所说的“长期保持正常工作”是什么意思？唯一索引仅能防止输入重复值，无论该字段是否具有自动增量规范。但这并不能阻止某人手动将唯一的乱序meta_id值插入到字段中。

    • 0

相关问题