MySQL 中的复合主键性能缺陷 [英] Composite Primary Key performance drawback in MySQL

问题描述

我们有一个包含三个字段的复合主键的表(它在 MySQL 5.1 中).该表每秒有近 200 次插入和 200 次选择，表的大小约为 100 万行，并且还在增加.

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.

我的问题是:复合主键"是否会降低此表上插入和选择的性能?

My question is: does the "Composite Primary Key" decrease the performance of the Inserts and Selects on this table?

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

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)

推荐答案

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

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

SELECT复合PRIMARY KEY的性能取决于很多因素.

SELECT performance of composite PRIMARY KEY depends on many factors.

如果您的表是 InnoDB，那么该表将隐式聚集在 PRIMARY KEY 值上.

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

表格布局是这样的:

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.

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

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

，那么引擎首先需要在索引 ix_mytable_col1_col2 中查找 (col1, col2) 的值，从索引中检索行指针(id 的值)并通过 id 在表本身中再次查找.

, 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.

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

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

在这两种情况下，将创建相同的索引(对于PRIMARY KEY 或UNIQUE KEY)并以相同的方式使用.

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

这篇关于MySQL 中的复合主键性能缺陷的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！