主页 / dba / 问题 / 63652
Accepted
BenMorel
Asked: 2014-04-23 07:31:19 +0800 CST

InnoDB 主键效率

  • 772

我已经阅读了许多关于取决于存储引擎的主键效率的文章，我感到很困惑。

storeId给定一个具有两个字段和的简单多对多表，zoneId以下哪种设计对 InnoDB 最有效，为什么？

  • 使用这两个字段作为复合主键：
CREATE TABLE store_zone(
    storeId INT(10) UNSIGNED NOT NULL,
    zoneId  INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY(storeId, zoneId)
);
  • 使用特定的自增主键：
CREATE TABLE store_zone(
    id      INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    storeId INT(10) UNSIGNED NOT NULL,
    zoneId  INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY(storeId, zoneId)
);

笔记：

  • 无论如何，我需要 ( storeId, ) 对上的唯一键zoneId
  • store我有和表的外键，zone为了便于阅读，这里没有显示zoneId，所以在这两种情况下，还有一个额外的必需索引
mysql innodb

1 个回答

  1. Best Answer

    分析

    既然我们在谈论 InnoDB，让我们把gen_clust_index. 它是一个特殊的索引，可让 PRIMARY KEY 和关联的行数据可从相同的 InnoDB 页面访问。

    根据gen_clust_index 上的 MySQL 文档

    • 如果您在表上定义 PRIMARY KEY，InnoDB 会将其用作聚集索引。

    • 如果你没有为你的表定义 PRIMARY KEY，MySQL 选择第一个只有 NOT NULL 列的唯一索引作为主键，InnoDB 使用它作为聚集索引。

    • 如果表没有 PRIMARY KEY 或合适的 UNIQUE 索引，InnoDB 在内部生成一个隐藏的聚集索引，该索引包含行 ID 值的合成列。这些行按 InnoDB 分配给此类表中的行的 ID 排序。行 ID 是一个 6 字节的字段，随着新行的插入而单调增加。因此，按行 ID 排序的行在物理上是按插入顺序排列的。

    既然如此，你会真正感受到以下几个方面的效率或不足：

    磁盘空间

    将单个 auto_increment 列作为PRIMARY KEY保持 PRIMARY KEY 的整体大小小于具有两列的大小。为什么？PRIMARY KEY如果是两个 INT 而不是一个，则 BTREE 页面将是两倍大。如果您使用外键约束和二级索引，这将变得更加痛苦，因为它们随后也必须膨胀。

    在这种情况下，您将选择第二个模式以更好地使用索引。

    插入性能

    将行数据插入具有两个 UNIQUE 索引的 InnoDB 表需要两倍的 BTREE 管理和唯一检查。

    • 一个用于 gen_clust_index（由 auto_increment 决定）
    • 一个用于另一个 UNIQUE 索引（由 指示(storeId, zoneId)）

    插入数百万行时，每个额外的 UNIQUE 索引的额外微秒会增加 CPU 时间。

    在这种情况下，您将选择第一个模式以获得更快的 INSERT。

    结论

    磁盘空间和插入性能问题几乎迫使您做出选择。显然，您选择与哪一个住在一起。如果您使用表示 的外键约束(storeId, zoneId)，那么您需要使用 auto_increment 作为外部表中的引用的第二个模式。

    • 5

相关问题