MySQL 5.0 索引 - 唯一与非唯一
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/388982/
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
MySQL 5.0 indexes - Unique vs Non Unique
提问by Sanjay
What is the difference between MySQL unique and non-unique index in terms of performance?
MySQL唯一索引和非唯一索引在性能方面有什么区别？
Let us say I want to make an index on a combo of 2 columns, and the combination is unique, but I create a non-unique index. Will that have any significant effect on the performance or the memory MySQL uses?
假设我想在 2 列的组合上创建索引，并且组合是唯一的，但我创建了一个非唯一索引。这会对性能或 MySQL 使用的内存产生重大影响吗？
Same question, is there is difference between primarykey and uniqueindex?
同样的问题，主键和唯一索引有区别吗？
回答by Bill Karwin
UNIQUE and PRIMARY KEY are constraints, not indexes. Though most databases implement these constraints by using an index. The additional overhead of the constraint in addition to the index is insignificant, especially when you count the cost of tracking down and correcting unintentional duplicates when (not if) they occur.
UNIQUE 和 PRIMARY KEY 是约束，而不是索引。尽管大多数数据库通过使用索引来实现这些约束。除了索引之外，约束的额外开销是微不足道的，尤其是当您计算在（不是如果）发生时（不是如果）无意中跟踪和纠正重复项的成本时。
Indexes are usually more effective if there you have a high selectivity. This is the ratio of number of distinct values to the total number of rows.
如果有高选择性，索引通常会更有效。这是不同值的数量与总行数的比率。
For example, in a column for Social Security Number, you may have 1 million rows with 1 million distinct values. So the selectivity is 1000000/1000000 = 1.0 (although there are rare historical exceptions, SSN's are intended to be unique).
例如，在社会安全号码的列中，您可能有 100 万行，其中包含 100 万个不同的值。所以选择性是 1000000/1000000 = 1.0（虽然有罕见的历史例外，SSN 是唯一的）。
But another column in that table, "gender" may only have two distinct values over 1 million rows. 2/1000000 = very low selectivity.
但是该表中的另一列“性别”可能只有超过 100 万行的两个不同值。2/1000000 = 非常低的选择性。
An index with a UNIQUE or PRIMARY KEY constraint is guaranteed to have a selectivity of 1.0, so it will always be as effective as an index can be.
具有 UNIQUE 或 PRIMARY KEY 约束的索引保证具有 1.0 的选择性，因此它始终与索引一样有效。
You asked about the difference between a primary key and a unique constraint. Chiefly, it's that you can have only one primary key constraint per table (even if that constraint's definition includes multiple columns), whereas you can have multiple unique constraints. A column with a unique constraint may permit NULLs, whereas columns in primary key constraints must not permit NULLs. Otherwise, primary key and unique are very similar in their implementation and their use.
您询问了主键和唯一约束之间的区别。主要是每个表只能有一个主键约束（即使该约束的定义包含多个列），而您可以有多个唯一约束。具有唯一约束的列可以允许 NULL，而主键约束中的列不得允许 NULL。否则，主键和唯一键的实现和使用非常相似。
You asked in a comment about whether to use MyISAM or InnoDB. In MySQL, they use the term storage engine. There are bunch of subtle differences between these two storage engines, but the chief ones are:
您在评论中询问是否使用 MyISAM 或 InnoDB。在 MySQL 中，他们使用术语存储引擎。这两个存储引擎之间有很多细微的区别，但主要的区别是：
- InnoDB supports transactions, so you can choose to roll back or commit changes. MyISAM is effectively always autocommit.
- InnoDB enforces foreign key constraints. MyISAM doesn't enforce or even store foreign key constraints.
- InnoDB 支持事务，因此您可以选择回滚或提交更改。MyISAM 实际上总是自动提交。
- InnoDB 强制执行外键约束。MyISAM 不强制执行甚至不存储外键约束。
If these features are things you need in your application, then you should use InnoDB.
如果您的应用程序需要这些功能，那么您应该使用 InnoDB。
To respond to your comment, it's not that simple. InnoDB is actually faster than MyISAM in quite a few cases, so it depends on what your application's mix of selects, updates, concurrent queries, indexes, buffer configuration, etc.
回复你的评论，没那么简单。在很多情况下，InnoDB 实际上比 MyISAM 更快，因此这取决于您的应用程序的选择、更新、并发查询、索引、缓冲区配置等的组合。
See http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/for a very thorough performance comparison of the storage engines. InnoDB wins over MyISAM frequently enough that it's clearly not possible to say one is faster than the other.
有关存储引擎的非常彻底的性能比较，请参见http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/。InnoDB 经常胜过 MyISAM，显然不可能说一个比另一个更快。
As with most performance-related questions, the only way to answer it for your applicationis to test both configurations using your application and a representative sample of data, and measure the results.
与大多数与性能相关的问题一样，为您的应用程序回答它的唯一方法是使用您的应用程序和代表性数据样本测试这两种配置，并测量结果。
回答by MarkR
On a non-unique index that just happens to be unique and a unique index? I'm not sure, but I'd guess not a lot. The optimiser should examine the cardinality of the index and use that (it will always be the number of rows, for a unique index).
在恰好是唯一的非唯一索引和唯一索引上？我不确定，但我猜不会很多。优化器应该检查索引的基数并使用它（对于唯一索引，它始终是行数）。
As far as a primary key is concerned, probably quite a lot, but it depends which engine you use.
就主键而言，可能相当多，但这取决于您使用的引擎。
The InnoDB engine (which is used by many people) always clusters rows on the primary key. This means that the PK is essentially combined with the actual row data. If you're doing a lot of lookups by PK (or indeed, range scans etc), this is a Good Thing, because it means that it won't need to fetch as many blocks from the disc.
InnoDB 引擎（被很多人使用）总是在主键上聚集行。这意味着 PK 本质上是与实际行数据相结合的。如果您通过 PK（或者实际上，范围扫描等）进行大量查找，这是一件好事，因为这意味着它不需要从光盘中获取尽可能多的块。
A non-PK unique index will never be clustered in InnoDB.
非 PK 唯一索引永远不会在 InnoDB 中聚集。
On the other hand, some other engines (MyISAM in particular) don't cluster the PK, so the primary key is just like a normal unique index.
另一方面，一些其他引擎（尤其是 MyISAM）不会对 PK 进行集群，因此主键就像普通的唯一索引一样。
