MySQL唯一索引和非唯一索引在性能方面有何区别？
假设我想在两列的组合上创建一个索引，且这个组合是唯一的，但我创建了一个非唯一的索引。这会对MySQL使用的性能或内存产生重大影响吗？
同样的问题，主键和唯一索引之间是否有区别？
2个回答
128
UNIQUE和PRIMARY KEY是约束(constraints)，而不是索引(index)。尽管大多数数据库通过使用索引来实现这些约束。除了索引之外，附加的约束开销微不足道，特别是当您考虑跟踪和纠正无意中重复时（不是如果）它们发生的成本时。
如果您具有高选择性(selectivity)，索引通常更有效。这是不同值的数量与总行数的比率。
例如，在社会保障号码列中，您可能有100万条记录，并且有100万个不同的值。因此，选择性为1000000/1000000 = 1.0(虽然有罕见的历史例外，但社会保障号码(SSN)应该是唯一的)。
但在该表中的另一列“性别”可能仅具有两个不同的值，超过100万行。2/1000000=非常低的选择性。
具有唯一或主键约束的索引保证具有选择性1.0，因此它将始终像索引一样有效。
您在评论中询问主键和唯一约束之间的区别。主要是，每个表只能有一个主键约束(即使该约束的定义包括多个列)，而您可以有多个唯一约束。带有唯一约束的列可能允许空值，而带有主键约束的列必须不允许空值。否则，主键和唯一在其实现和使用方面非常相似。
您在评论中询问是否要使用MyISAM还是InnoDB。在MySQL中，它们使用术语存储引擎(storage engine)。这两个存储引擎之间存在许多微妙的差异，但主要差异包括:
- InnoDB支持事务，因此您可以选择回滚或提交更改。MyISAM实际上始终自动提交。 - InnoDB强制执行外键约束。MyISAM不执行或甚至不存储外键约束。
如果您的应用程序需要这些功能，则应使用InnoDB。为了回应您的评论，其实情况并不简单。在很多情况下，InnoDB比MyISAM更快，因此这取决于您的应用程序中关于selects，updates，concurrent queries，indexes，buffer configuration等方面的综合考虑。
请参考http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/，其中详细介绍了存储引擎的性能比较。InnoDB的胜率频繁地超过了MyISAM，因此无法明确地说哪个更快。
与大多数与性能有关的问题一样，唯一回答“对于您的应用程序”是测试两种配置，并使用代表性数据样本来测量结果。
如果您具有高选择性(selectivity)，索引通常更有效。这是不同值的数量与总行数的比率。
例如，在社会保障号码列中，您可能有100万条记录，并且有100万个不同的值。因此，选择性为1000000/1000000 = 1.0(虽然有罕见的历史例外，但社会保障号码(SSN)应该是唯一的)。
但在该表中的另一列“性别”可能仅具有两个不同的值，超过100万行。2/1000000=非常低的选择性。
具有唯一或主键约束的索引保证具有选择性1.0，因此它将始终像索引一样有效。
您在评论中询问主键和唯一约束之间的区别。主要是，每个表只能有一个主键约束(即使该约束的定义包括多个列)，而您可以有多个唯一约束。带有唯一约束的列可能允许空值，而带有主键约束的列必须不允许空值。否则，主键和唯一在其实现和使用方面非常相似。
您在评论中询问是否要使用MyISAM还是InnoDB。在MySQL中，它们使用术语存储引擎(storage engine)。这两个存储引擎之间存在许多微妙的差异，但主要差异包括:
- InnoDB支持事务，因此您可以选择回滚或提交更改。MyISAM实际上始终自动提交。 - InnoDB强制执行外键约束。MyISAM不执行或甚至不存储外键约束。
如果您的应用程序需要这些功能，则应使用InnoDB。为了回应您的评论，其实情况并不简单。在很多情况下，InnoDB比MyISAM更快，因此这取决于您的应用程序中关于selects，updates，concurrent queries，indexes，buffer configuration等方面的综合考虑。
请参考http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/，其中详细介绍了存储引擎的性能比较。InnoDB的胜率频繁地超过了MyISAM，因此无法明确地说哪个更快。
与大多数与性能有关的问题一样，唯一回答“对于您的应用程序”是测试两种配置，并使用代表性数据样本来测量结果。
- Bill Karwin
3
1只要您的查询为索引中的每个字段提供了值，并且没有任何提供的值为空，并且键中的任何字段都不可为空（大致如此），那么“始终会是...”。 - dkretz
这实际上意味着，如果我不需要事务/外键约束，最好使用MyISAM，并且我们可以从中获得更好的性能。 - Sanjay
哈哈，我总是知道在滚动到第一段之前就能看出这将会是Bill Karwin的回答 :) 总是清晰而有见地，谢谢 +1 - Darragh Enright
2
在非唯一索引发生唯一性并且唯一索引之间的区别上，优化器应该会检查索引的基数并使用它（对于唯一索引来说，基数总是行数）。 关于主键，这取决于你使用哪个引擎。使用广泛的InnoDB引擎始终将行按照主键聚集。 这意味着PK实际上与实际的行数据结合在一起。如果您通过PK进行了大量查找（或者进行范围扫描等），那么这是一个好事情，因为它意味着它不需要从磁盘中获取太多块。在InnoDB中，非PK唯一索引永远不会聚集。另一方面，其他一些引擎（特别是MyISAM）不会聚集PK，因此主键就像普通的唯一索引一样。
- MarkR
1
这就引出了第二个问题，使用哪个索引 - 我个人更喜欢MyISAM，因为听说它在选择查询性能方面更好，并且在恢复mysqldump时速度更快。有什么理由我应该使用InnoDB吗？ - Sanjay
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接