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字段而不是Composite Primary Key? (我认为答案与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性能取决于许多因素.

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屋！