mysql 5.0索引 - 唯一与非唯一 [英] mysql 5.0 indexes - Unique vs Non Unique

问题描述

在性能方面，mysql唯一索引和非唯一索引之间有什么区别？
让我们说我想在2列的组合上创建索引，并且组合是唯一的，但我创建了一个非唯一索引。这对mysql的性能或内存有什么重大影响吗？
同样的问题，主键和唯一索引之间是否有区别。

What is the difference between mysql unique and non-unique index in terms of performance? 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? Same question, is there is difference between primary key and unique index.

推荐答案

UNIQUE和PRIMARY KEY是约束，而不是索引。尽管大多数数据库都使用索引来实现这些约束。除了索引之外，约束的额外开销是微不足道的，尤其是当你计算跟踪和纠正无意重复的成本时（不是）它们发生时。

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.

如果您具有较高的选择性，则索引通常会更有效。这是不同值的数量与总行数的比率。

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.

例如，在社会安全号码列中，您可能有100万行，其中包含100万个不同的值。所以选择性是1000000/1000000 = 1.0（尽管有罕见的历史例外，SSN是唯一的）。

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万行的两个不同的值。 2/1000000 =选择性非常低。

But another column in that table, "gender" may only have two distinct values over 1 million rows. 2/1000000 = very low selectivity.

具有UNIQUE或PRIMARY KEY约束的索引保证选择性为1.0，因此它始终与index可以是。

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.

您询问主键和唯一约束之间的区别。主要是，每个表只能有一个主键约束（即使该约束的定义包括多个列），而您可以有多个唯一约束。具有唯一约束的列可能允许NULL，而主键约束中的列不允许NULL。否则，主键和唯一键的实现和使用非常相似。

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.

您在评论中询问是否使用MyISAM或InnoDB。在MySQL中，他们使用术语存储引擎。这两个存储引擎之间存在着许多微妙的差异，但主要的是：

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:

• InnoDB支持事务，因此您可以选择滚动退回或提交更改。 MyISAM实际上总是自动提交。


• InnoDB强制执行外键约束。 MyISAM不会强制执行甚至存储外键约束。

如果这些功能是您应用程序中需要的功能，那么您应该使用InnoDB。

If these features are things you need in your application, then you should use InnoDB.

要回复你的评论，就不那么简单了。在很多情况下，InnoDB实际上比MyISAM更快，因此它取决于您的应用程序的选择，更新，并发查询，索引，缓冲区配置等的组合。

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.

请参阅 http：// www .mysqlperformanceblog.com / 2007/01/08 / innodb-vs-myisam-vs-falcon-benchmarks-part-1 / ，用于对存储引擎进行非常全面的性能比较。 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.

与大多数与性能相关的问题一样，回答它的唯一方法是em>适用于您的应用程序是使用您的应用程序和代表性数据样本测试这两种配置，并测量结果。

As with most performance-related questions, the only way to answer it for your application is to test both configurations using your application and a representative sample of data, and measure the results.

这篇关于mysql 5.0索引 - 唯一与非唯一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！