MySQL 中的复合主键性能缺陷
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1460465/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Composite Primary Key performance drawback in MySQL
提问by Ahmad
We have a table with a composite Primary key consisting of three fields (and it is in MySQL 5.1). There are near 200 inserts and 200 selects per second on this table, and the size of the table is around 1 million rows and it is increasing.
我们有一个包含三个字段的复合主键的表（它在 MySQL 5.1 中）。该表每秒有近 200 次插入和 200 次选择，表的大小约为 100 万行，并且还在增加。
My question is: does the "Composite Primary Key" decrease the performance of the Inserts and Selects on this table?
我的问题是：“复合主键”是否会降低此表上插入和选择的性能？
Should I be using a simple Auto-Increasing INT ID field instead of a Composite Primary Key? (I think the answer is very much related to the way MySQL handles the Indexes on multiple columns)
我应该使用简单的自动递增 INT ID 字段而不是复合主键吗？（我认为答案与 MySQL 处理多列索引的方式非常相关）
回答by Quassnoi
INSERTand UPDATEperformance varies little: it will be almost same for (INT)and (INT, INT)keys.
INSERT和UPDATE性能变化不大：(INT)和(INT, INT)键几乎相同。
SELECTperformance of composite PRIMARY KEYdepends on many factors.
SELECT复合材料的性能PRIMARY KEY取决于许多因素。
If your table is InnoDB, then the table is implicitly clustered on the PRIMARY KEYvalue.
如果您的表是InnoDB，则该表隐式地聚集在该PRIMARY KEY值上。
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:
如果您使用自动增量字段作为假 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 idin the table itself.
，那么引擎首先需要(col1, col2)在 index 中查找 的值，从索引ix_mytable_col1_col2中检索行指针（ 的值id）并id在表本身中进行另一次查找。
For MyISAMtables, however, this makes no difference, because MyISAMtables are heap organized and the row pointer is just file offset.
MyISAM然而，对于表，这没有区别，因为MyISAM表是堆组织的，行指针只是文件偏移量。
In both cases, a same index will be created (for PRIMARY KEYor for UNIQUE KEY) and will be used in same way.
在这两种情况下，将创建相同的索引（forPRIMARY KEY或 for UNIQUE KEY）并以相同的方式使用。
回答by MarkR
If it's InnoDB, the composite primary key will be included in each entry in each of the secondary indexes.
如果是 InnoDB，则复合主键将包含在每个二级索引的每个条目中。
This means that
这意味着
- Your secondary indexes will take up as much space as those columns + all the columns in the primary key
- You can use a secondary index as a covering index if all the columns required are contained in the secondary index + pk
- 您的二级索引将占用与这些列+主键中的所有列一样多的空间
- 如果所需的所有列都包含在二级索引 + pk 中，则可以使用二级索引作为覆盖索引
These are of course, a disadvantage and an advantage respectively.
这些当然分别是缺点和优点。
Composite primary keys are not necessarily bad, sometimes they can be really helpful because InnoDB clusters them - which means that (disc-bound) range scans over the PK can be satisfied using far fewer IO operations than would be required on a non-clustered index.
复合主键不一定是坏的，有时它们真的很有帮助，因为 InnoDB 将它们聚集在一起 - 这意味着可以使用比非聚集索引少得多的 IO 操作来满足对 PK 的（磁盘绑定）范围扫描.
Of course if you've got foreign keys in other tables, they're wider as well as they need to include the whole key from your main table.
当然，如果您在其他表中有外键，它们会更宽，并且需要包含主表中的整个键。
But I'd say on balance, generally, no. Having a composite primary key does NOT cause a problem by itself. Having a "big" primary key (e.g. big varchars) may do however, if that outweighs the advantages of clustering and being able to use covering indexes.
但总的来说，我会说不。拥有复合主键本身不会导致问题。然而，拥有一个“大”主键（例如大 varchars）可能会做，如果这超过了集群和能够使用覆盖索引的优势。
回答by chaos
- Having that composite primary key slows down
SELECTs a tiny bit, though the effect is pretty much negligible and not worth worrying about. - Having those columns indexed at allslows down your
INSERTs, and you certainly are doing enoughINSERTs to worry about it. This is much more of a concern if it's a MyISAM table, where anINSERTlocks the table, than if it's an InnoDB table. If, by going with the auto_increment primary key, you would be able to leave those columns unindexed, you would benefit from the change. If you would still need to keep those three columns indexed, though (for example, if you need to enforce uniqueness on the combination of them), it isn't going to do anything for you performance-wise.
- 拥有该复合主键会
SELECT稍微减慢速度，尽管这种影响几乎可以忽略不计，不值得担心。 - 有索引的那些列在所有减慢你的
INSERTS，你肯定是做足够的INSERT担心此而已。如果它是一个 MyISAM 表，在其中INSERT锁定表，这比它是 InnoDB 表更值得关注。如果通过使用 auto_increment 主键，您将能够使这些列未编入索引，那么您将从更改中受益。但是，如果您仍然需要对这三列进行索引（例如，如果您需要对它们的组合强制执行唯一性），那么它不会对您的性能产生任何影响。
