当前位置：文江博客话题详情

MySQL 中的复合主键性能缺陷

发布于 2024-08-05 08:30:13 字数 196 浏览 2 评论 0原文

我们有一个表，其复合主键由三个字段组成（在 MySQL 5.1 中）。该表每秒有近 200 次插入和 200 次选择，表的大小约为 100 万行，并且还在不断增加。

我的问题是：“复合主键”是否会降低该表上的插入和选择的性能？

我应该使用简单的自动递增 INT ID 字段而不是复合主键吗？（我认为答案与MySQL处理多列索引的方式有很大关系）

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

傲影 2024-08-12 08:30:13

INSERT 和 UPDATE 性能变化不大：(INT) 和 (INT, INT) 性能几乎相同键。

复合PRIMARY KEY 的SELECT 性能取决于许多因素。

如果您的表是 InnoDB，则该表将隐式聚集在 PRIMARY KEY 值上。

这意味着如果两个值都包含键，则搜索这两个值将会更快：不需要额外的键查找。

假设您的查询是这样的：

SELECT  *
FROM    mytable
WHERE   col1 = @value1
        AND col2 = @value2

并且表布局是这样的：

CREATE TABLE mytable (
        col1 INT NOT NULL,
        col2 INT NOT NULL,
        data VARCHAR(200) NOT NULL,
        PRIMARY KEY pk_mytable (col1, col2)
) ENGINE=InnoDB

，引擎只需要在表本身中查找确切的键值。

如果您使用自动增量字段作为假 id:

CREATE TABLE mytable (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        col1 INT NOT NULL,
        col2 INT NOT NULL,
        data VARCHAR(200) NOT NULL,
        UNIQUE KEY ix_mytable_col1_col2 (col1, col2)
) ENGINE=InnoDB

，那么引擎首先需要在索引 ix_mytable_col1_col2 中查找 (col1, col2) 的值，检索索引中的行指针（id 的值），并在表本身中通过 id 进行另一次查找。

然而，对于 MyISAM 表来说，这没有什么区别，因为 MyISAM 表是堆组织的，行指针只是文件偏移量。

在这两种情况下，都会创建相同的索引（对于 PRIMARY KEY 或 UNIQUE KEY），并以相同的方式使用。

INSERT and UPDATE performance varies little: it will be almost same for (INT) and (INT, INT) keys.

SELECT performance of composite PRIMARY KEY depends on many factors.

If your table is InnoDB, then the table is implicitly clustered on the PRIMARY KEY value.

That means that searches for both values will be faster if the both values comprise the key: no extra key lookup will be required.

Assuming your query is something like this:

SELECT  *
FROM    mytable
WHERE   col1 = @value1
        AND col2 = @value2

and the table layout is this:

CREATE TABLE mytable (
        col1 INT NOT NULL,
        col2 INT NOT NULL,
        data VARCHAR(200) NOT NULL,
        PRIMARY KEY pk_mytable (col1, col2)
) ENGINE=InnoDB

, the engine will just need to lookup the exact key value in the table itself.

If you use an autoincrement field as a fake id:

CREATE TABLE mytable (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        col1 INT NOT NULL,
        col2 INT NOT NULL,
        data VARCHAR(200) NOT NULL,
        UNIQUE KEY ix_mytable_col1_col2 (col1, col2)
) ENGINE=InnoDB

, then the engine will need, first, to lookup the values of (col1, col2) in the index ix_mytable_col1_col2, retrieve the row pointer from the index (the value of id) and make another lookup by id in the table itself.

For MyISAM tables, however, this makes no difference, because MyISAM tables are heap organized and the row pointer is just file offset.

In both cases, a same index will be created (for PRIMARY KEY or for UNIQUE KEY) and will be used in same way.

回复收藏 0 原文

凉风有信 2024-08-12 08:30:13

如果是InnoDB，复合主键将包含在每个二级索引的每个条目中。

这意味着

您的二级索引将占用与这些列+主键中的所有列一样多的空间
您可以使用二级索引作为覆盖索引，如果所需的所有列都包含在二级索引+pk中

这些当然是，分别是缺点和优点。

复合主键不一定是坏事，有时它们确实很有帮助，因为 InnoDB 将它们聚集在一起 - 这意味着可以使用比非聚集索引所需少得多的 IO 操作来满足 PK 上的（磁盘绑定）范围扫描。

当然，如果您在其他表中有外键，它们会更宽，并且需要包含主表中的整个键。

但总的来说，我想说的是，不会。拥有复合主键本身不会导致问题。然而，拥有“大”主键（例如大varchar）可能会起作用，如果这超过了集群和能够使用覆盖索引的优势。

回复收藏 0 原文

画▽骨i 2024-08-12 08:30:13

使用复合主键会稍微减慢SELECT的速度，尽管这种影响几乎可以忽略不计，不值得担心。
对这些列进行索引会减慢您的INSERT速度，而且您肯定已经做了足够多的INSERT来担心它。如果它是 MyISAM 表（其中 INSERT 锁定该表），那么与 InnoDB 表相比，这一点更值得关注。如果通过使用 auto_increment 主键，您可以使这些列保持未索引状态，那么您将从更改中受益。但是，如果您仍然需要对这三列进行索引（例如，如果您需要对它们的组合强制执行唯一性），那么它不会对您的性能产生任何影响。