主页 / dba / 问题 / 216125
Accepted
Jon of All Trades
Asked: 2018-08-29 13:59:01 +0800 CST

可以在唯一索引而不是主键上创建 MySQL 集群吗？

  • 772

MySQL 文档指出：

当您在表上定义 PRIMARY KEY 时，InnoDB 将其用作聚集索引。

但这不是唯一的可能性，您可以改为使用唯一索引进行聚类：

如果你没有为你的表定义 PRIMARY KEY，MySQL 会定位第一个唯一索引，其中所有键列都不是 NULL，InnoDB 将它用作聚集索引。

似乎可以这样说，如果我首先在表上创建唯一索引，它将被标记为聚集；然后我可以创建一个主键，它将是非聚集的：

CREATE TABLE Tmp_CUQTest
(
ID1 INT NOT NULL,  -- Desired clustering field
ID2 INT NOT NULL   -- Desired PK field
);

CREATE UNIQUE INDEX CUC_Tmp ON Tmp_CUQTest (ID1);
ALTER TABLE Tmp_CUQTest ADD CONSTRAINT PRIMARY KEY PK_Tmp (ID2);

但是，当我检查结果表时，我的希望破灭了：

Key      Type               Uni  Columns
PRIMARY  BTREE (clustered)  YES  ID2
ID1      BTREE              YES  ID1

DBA.SE 上的另一个问题意味着创建索引作为CREATE TABLE脚本的一部分，而不是之后，作为单独的CREATE INDEX语句，可能会有所作为。但是，我得到相同的结果。

有没有办法强制 MySQL 在我选择的唯一索引上进行集群，而不是主键，或者这个“后备选项”是否只适用于没有主键的表？

mysql mysql-5.7

2 个回答

  1. Best Answer

    PRIMARY KEY

    • InnoDB在每张PRIMARY KEY表上都有一个。
    • PRIMARY KEY总是聚集的；不能有任何其他“聚集”索引。
    • 该 PK 要么是明确声明的 PK（这是​​首选），要么是第一个UNIQUE密钥（具有某些限制），要么是隐藏的序列号（与 `AUTO_INCREMENT; 6 字节不完全相同）。
    • PK是 UNIQUE和是和索引。
    • PK（和其他索引）可能包含多个列（“复合”索引。
    • 该表结构为 BTree（实际上是 B+Tree），所有列都存在，并且 BTree 根据 PK 排序。

    辅助键

    • 所有其他索引都称为“二级索引”。（“键”==“索引”）
    • 结构为B+Tree，按key排序
    • PRIMARY KEY此 BTree 的“行”包含（即“聚集索引”）的列的副本。这就是辅助键可以到达行的方式。
    • 请注意，通过辅助键查找行需要向下钻取两个 BTree。（除非它是“覆盖”索引）。
    • AUNIQUE KEY是两件事：辅助键（除非提升为 PK）和“唯一性约束”。

    其他注意事项

    • 与其他一些供应商不同，InnoDB 没有“ROWNUM”或“行定位器”的概念。PK 用作行定位器。
    • MySQL 在 InnoDB 上没有“堆”或“位”索引。
    • InnoDB 只有 BTree、SPATIAL 和 FULLTEXT；我不是在这个答案中讨论后两者。
    • CREATE TABLE对于大多数用途，您是在语句中创建索引还是CREATE INDEX稍后再创建索引都没有关系。定义同上FOREIGN KEYs。
    • 结构上，PK+data是B+Tree；每个二级索引都是一个 B+Tree。这些 B+树的外观、感觉、气味等都相同。唯一的区别是您在“行”中找到的内容。

    AUTO_INCREMENT

    • 有很多警告。它不一定是连续的数字；出现差距的方式有很多种。只相信他们会是“独一无二的”。
    • AUTO_INCREMENT不一定要申报PRIMARY KEY，甚至UNIQUE。

    一招。可以执行以下操作。是你钓鱼的目的吗？？ 

    CREATE TABLE ... (
  id INT AUTO_INCREMENT,
  foo ...,
  ...
  PRIMARY KEY (foo, id),  -- to get clustering on `foo` and uniqueness (via `id`)
  INDEX(id)               -- sufficient to keep AUTO_INCREMENT happy.
) ENGINE=InnoDB;
    • 2

  2. 主键是唯一键和索引。因此，将 PK 创建为您想要集群的唯一索引。

    创建之后添加索引并作为创建表的一部分产生相同的结果。

    • 1

相关问题