順序（自動遞增）ID 是否需要唯一索引？

如果順序 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),

（我知道可能有比這個更好的表格設計。但是我目前的任務不允許我更改表格，只能更改索引。）

你想改變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 會導致更多的磁碟空間。

引用自：https://dba.stackexchange.com/questions/305649