MySQL 5.0 索引 - 唯一与非唯一
MySQL 唯一索引和非唯一索引在性能上有什么区别?
What is the difference between MySQL unique and non-unique index in terms of performance?
假设我想在 2 列的组合上创建索引，并且该组合是唯一的，但我创建了一个非唯一索引.这对性能或 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?
同问，主键和唯一索引有区别吗?
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 的选择性，因此它始终与索引一样有效.
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.
与大多数与性能相关的问题一样，为您的应用程序回答这个问题的唯一方法是使用您的应用程序和具有代表性的数据样本测试这两种配置，并测量结果.
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.
相关文章