MySQL 中 INDEX、PRIMARY、UNIQUE、FULLTEXT 的区别是什么？

Question

在创建MySQL表时，PRIMARY、UNIQUE、INDEX和FULLTEXT有什么区别？

我应该如何使用它们？

- Sam

---

对于任何想了解“SPATIAL”的人：https://dev59.com/-nE95IYBdhLWcg3wkeqq - Leo

有关Python中主索引和辅助索引的比较，请参阅此帖子https://stackoverflow.com/questions/59918440/secondary-index-in-python。 - Athanassios

Answer 1

差异

• KEY或INDEX指的是普通的非唯一索引。索引中允许存在非唯一值，因此索引中的所有列的值都可以相同。这些索引不对数据施加任何约束，因此它们仅用于访问 - 快速访问某个范围的记录，而无需扫描所有记录。

• UNIQUE指的是索引中的所有行必须是唯一的。也就是说，一行不能与索引中的另一行具有相同的非NULL值。除了用于快速访问某个记录范围外，UNIQUE索引还可以用于对数据施加约束，因为在插入或更新数据时，数据库系统不允许违反唯一值规则。

  您的数据库系统可能允许将UNIQUE索引应用于允许NULL值的列，如果两行都包含NULL值，则允许它们相同（这里的理由是NULL被认为不等于自身）。然而，根据您的应用程序，您可能会发现这是不可取的：如果您希望防止这种情况发生，应禁止相关列中的NULL值。

• PRIMARY与UNIQUE索引的行为完全相同，只是它的名称始终为“PRIMARY”，并且一个表中只能有一个PRIMARY索引（并且应该始终有一个；尽管某些数据库系统不强制执行此规定）。PRIMARY索引用作唯一标识表中任何行的主要手段，因此与UNIQUE不同，它不应用于允许NULL值的任何列。您的PRIMARY索引应该是足以唯一标识一行的最少列数。通常，这只是一个包含唯一自增数字的列，但如果还有其他可以唯一标识一行的内容，例如在国家列表中的“countrycode”，则可以使用该内容。

  某些数据库系统（例如MySQL的InnoDB）将实际记录存储在PRIMARY KEY的B树索引中。

• FULLTEXT索引与上述所有索引不同，并且其行为在数据库系统之间有很大差异。FULLTEXT索引仅对使用MATCH() / AGAINST()子句进行的全文搜索有用，而上述三种索引通常使用B树（允许从最左列选择、排序或范围）或哈希表（允许从最左列开始选择）进行内部实现。

  其他索引类型是通用的，而FULLTEXT索引是专用的，因为它只用于“全文搜索”功能。

相似之处

• 所有这些索引都可以包含多个列。

• 除了FULLTEXT索引外，列的顺序很重要：为了使索引在查询中有用，查询必须从索引的左侧开始使用列 - 不能仅使用索引的第二、第三或第四部分，除非它还使用索引中的前面列来匹配静态值。（对于FULLTEXT索引在查询中有用，查询必须使用索引的所有列。）

- thomasrutter

---

这是否意味着如果您在查询中不使用MATCH() / AGAINST()，那么FULLTEXT索引基本上是无用的，只是浪费空间？ - user1397417

是的，这个功能只能用于MySQL上的MyISAM数据库，不能用于InnoDB。其他数据库服务器可能有类似的功能，但工作方式可能不同。 - thomasrutter

它不应该用于允许NULL值的任何列。这应该是“不能使用”。主键必须为NOT NULL。如果没有定义其他主键，MySQL将在show columns中报告非空唯一键是主键。 - Gordon Linoff

这里的理由是NULL被认为与自身不相等。哈哈，我不会忘记这个。 - mercury

这就是为什么我们的查询语句中有IS NULL和IS NOT NULL。 - Arheisel

MySQL从5.6版本开始支持InnoDB引擎的FULLTEXT功能。 - Marek Skiba

Answer 2

这些都是索引的种类。

主键索引: 必须是唯一的，是一个索引，通常是物理索引，每个表只能有一个。

唯一索引: 如其名称所示。您不能具有具有此值元组的多个行。请注意，由于唯一键可以跨越多个列，这并不一定意味着索引中的每个单独列都是唯一的，而是指跨这些列的每个值组合都是唯一的。

普通索引: 如果它既不是主键索引也不是唯一索引，则不限制插入到表中的值，但可以更有效地查找它们。

全文索引：一种更专业的索引形式，允许进行全文搜索。可以将其视为在指定列的每个“单词”上创建一个“索引”。

- tpdi

---

在MySQL（以及许多其他数据库中），主键可以是复合的，即多个键组成。它们只是特殊的索引。唯一性不算是索引，而是一个约束条件（需要索引来在合理的时间内强制执行，因此会创建一个索引）。 - MBCook

Answer 3

我认为这个问题已经被很好地解决了，除了以下几点：

• 如果选择性足够高，简单的KEY / INDEX（或称为SECONDARY INDEX）确实可以提高性能。在这一点上，通常的建议是，如果应用索引的结果集中记录的数量超过父表总记录数的20％以上，则该索引将无效。在实践中，每种架构都会有所不同，但是这个想法仍然是正确的。

• 次要索引（这非常特定于mysql）不应视为与主键完全独立和不同的对象。实际上，两者应该共同使用，并且一旦知道了这些信息，就为mysql DBA提供了一个额外的工具：在Mysql中，索引嵌入了主键。它导致显着的性能改进，特别是当巧妙地构建隐式覆盖索引时，例如描述在那里。

• 如果您觉得数据应该是UNIQUE，请使用唯一索引。您可能认为这是可选的（例如，在应用程序级别工作），并且正常索引会起到作用，但实际上它代表了mysql中每一行都是唯一的保证，这恰好提供了性能优势。

• 只有在Innodb（在MySQL 5.6.4及以上版本）和Myisam Engines上才能使用FULLTEXT（或称为SEARCH INDEX）

• 只有在CHAR，VARCHAR和TEXT列类型上才能使用FULLTEXT

FULLTEXT索引不仅仅是创建一个索引，它还涉及到大量的系统表创建、完全独立的缓存系统以及一些特定的规则和优化。详见http://dev.mysql.com/doc/refman/5.7/en/fulltext-restrictions.html和http://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html

- Sebas