连续(自动递增) id是否需要唯一的索引？

Question

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

我正在重新编制特定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)

这意味着可以使用聚集索引和LONGTEXT获取满足像post_id = 42 AND meta_key = 'value'这样的过滤器。它已经被证明可以解决我的用户的性能问题。

问题是:这个被重新索引的表是否也需要在自动递增ID列上一个唯一的索引才能长期保持正确工作？或者我可以省略该索引以节省一点空间并更新/插入性能？

    UNIQUE INDEX meta_id (meta_id),

)我知道可能有比这个好得多的桌子设计。但我现在的任务不允许我更改表，只更改索引。)

Answer 1

简单回答:在InnoDB中，自动增量列必须是某个索引的最左边列，或者是主键(聚集)索引，或者是一些次要索引。它不必是唯一的键，任何B树索引都可以。

以下内容无效..。

PRIMARY KEY (post_id, meta_key, meta_id)

除非用meta_id作为最左边的列来定义另一个索引(又名键)。

KEY (meta_id)

Answer 2

INDEX(meta_id) 

是满足MySQL所需的最小值。(如其他答覆所述)。

但是，这允许用户显式地为meta_id添加一个重复的值。据推测，WP从来不会做这种“愚蠢”的事情。

UNIQUE(meta_id)

稍微降低INSERTs的速度--因为在检查唯一性之前，插入不会返回到客户端。对于普通的INDEX(meta_id)，插入将该索引的更新放入"change缓冲区“，以便稍后存储到该辅助索引的BTree中。

UNIQUE和INDEX之间的另一个区别是当SELECTing为meta_id指定一个值时。使用INDEX，查询将继续读取行，查找具有相同meta_id的更多行。另一方面，UNIQUE可以在第一次发生之后停止。

在担心业绩时，这两个“差异”都不值得担心。我只是为了完整地提到它们。

Answer 3

取决于你所说的“长期保持正确工作”是什么意思？唯一索引只会完成其防止输入重复值的工作，而不管字段是否有自动增量规范。但是，这并不能阻止某些人手动插入一个在meta_id字段中唯一的非序列值。