MySQL 中 INDEX、PRIMARY、UNIQUE、FULLTEXT 之间的区别？

Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?

提问by thomasrutter

What are the differences between PRIMARY, UNIQUE, INDEX and FULLTEXT when creating MySQL tables?

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

How would I use them?

我将如何使用它们？

回答by thomasrutter

Differences

差异

• KEYor INDEXrefers to a normal non-unique index. Non-distinct values for the index are allowed, so the index maycontain rows with identical values in all columns of the index. These indexes don't enforce any restraints on your data so they are used only for access - for quickly reaching certain ranges of records without scanning all records.

• UNIQUErefers to an index where all rows of the index must be unique. That is, the same row may not have identical non-NULL values for all columns in this index as another row. As well as being used to quickly reach certain record ranges, UNIQUE indexes can be used to enforce restraints on data, because the database system does not allow the distinct values rule to be broken when inserting or updating data.

  Your database system may allow a UNIQUE index to be applied to columns which allow NULL values, in which case two rows are allowed to be identical if they both contain a NULL value (the rationale here is that NULL is considered not equal to itself). Depending on your application, however, you mayfind this undesirable: if you wish to prevent this, you should disallow NULL values in the relevant columns.

• PRIMARYacts exactly like a UNIQUE index, except that it is always named 'PRIMARY', and there may be only one on a table (and there shouldalways be one; though some database systems don't enforce this). A PRIMARY index is intended as a primary means to uniquely identify any row in the table, so unlike UNIQUE it should not be used on any columns which allow NULL values. Your PRIMARY index should be on the smallest number of columns that are sufficient to uniquely identify a row. Often, this is just one column containing a unique auto-incremented number, but if there is anything else that can uniquely identify a row, such as "countrycode" in a list of countries, you can use that instead.

  Some database systems (such as MySQL's InnoDB) will store a table's records on disk in the order in which they appear in the PRIMARY index.

• FULLTEXTindexes are different from all of the above, and their behaviour differs significantly between database systems. FULLTEXT indexes are only useful for full text searches done with the MATCH() / AGAINST() clause, unlike the above three - which are typically implemented internally using b-trees (allowing for selecting, sorting or ranges starting from left most column) or hash tables (allowing for selection starting from left most column).

  Where the other index types are general-purpose, a FULLTEXT index is specialised, in that it serves a narrow purpose: it's only used for a "full text search" feature.

• KEY或INDEX是指正常的非唯一索引。允许索引的非不同值，因此索引可能包含在索引的所有列中具有相同值的行。这些索引不会对您的数据施加任何限制，因此它们仅用于访问 - 用于在不扫描所有记录的情况下快速到达特定范围的记录。

• UNIQUE是指索引的所有行都必须唯一的索引。也就是说，对于该索引中的所有列，同一行可能不具有与另一行相同的非 NULL 值。除了用于快速到达某些记录范围外，UNIQUE 索引还可用于强制限制数据，因为数据库系统不允许在插入或更新数据时破坏不同值规则。

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

• PRIMARY 的行为与 UNIQUE 索引完全一样，只是它始终命名为“PRIMARY”，并且一张表上可能只有一个（并且应该始终有一个；尽管某些数据库系统不强制执行此操作）。PRIMARY 索引旨在作为唯一标识表中任何行的主要方法，因此与 UNIQUE 不同，它不应用于任何允许 NULL 值的列。您的 PRIMARY 索引应该位于足以唯一标识行的最少列数上。通常，这只是一个包含唯一自动递增数字的列，但如果还有其他任何东西可以唯一标识一行，例如国家/地区列表中的“国家/地区代码”，您可以使用它。

  一些数据库系统（例如 MySQL 的 InnoDB）将按照它们在 PRIMARY 索引中出现的顺序将表的记录存储在磁盘上。

• FULLTEXT索引与上述所有索引都不同，它们的行为在数据库系统之间存在显着差异。FULLTEXT 索引仅对使用 MATCH() / AGAINST() 子句完成的全文搜索有用，与上述三个不同 - 通常使用 b 树在内部实现（允许从最左边的列开始选择、排序或范围）或哈希表（允许从最左边的列开始选择）。

  其他索引类型是通用的，而 FULLTEXT 索引是专门的，因为它的用途很窄：它仅用于“全文搜索”功能。

Similarities

相似之处

• All of these indexes may have more than one column in them.

• With the exception of FULLTEXT, the column order is significant: for the index to be useful in a query, the query must use columns from the index starting from the left - it can't use just the second, third or fourth part of an index, unless it is also using the previous columns in the index to match static values. (For a FULLTEXT index to be useful to a query, the query must use allcolumns of the index.)

• 所有这些索引中可能有不止一列。

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

回答by tpdi

All of these are kinds of indices.

所有这些都是指数的种类。

primary:must be unique, is an index, is (likely) the physical index, can be only one per table.

primary：必须是唯一的，是一个索引，是（可能的）物理索引，每个表只能有一个。

unique:as it says. You can't have more than one row with a tuple of this value. Note that since a unique key can be over more than one column, this doesn't necessarily mean that each individual column in the index is unique, but that each combination of values across these columns is unique.

独特：正如它所说。您不能有超过一行包含此值的元组。请注意，由于唯一键可以超过一列，这并不一定意味着索引中的每一列都是唯一的，但这些列中的每个值组合都是唯一的。

index:if it's not primary or unique, it doesn't constrain values inserted into the table, but it does allow them to be looked up more efficiently.

索引：如果它不是主要的或唯一的，它不会限制插入到表中的值，但它确实允许更有效地查找它们。

fulltext:a more specialized form of indexing that allows full text search. Think of it as (essentially) creating an "index" for each "word" in the specified column.

fulltext：一种更专业的索引形式，允许全文搜索。将其视为（本质上）为指定列中的每个“单词”创建一个“索引”。

回答by Sebas

I feel like this has been well covered, maybe except for the following:

我觉得这已经被很好地涵盖了，也许除了以下几点：

• Simple KEY/ INDEX(or otherwise called SECONDARY INDEX) do increase performance if selectivity is sufficient. On this matter, the usual recommendation is that if the amount of records in the result set on which an index is applied exceeds 20% of the total amount of records of the parent table, then the index will be ineffective. In practice each architecture will differ but, the idea is still correct.

• Secondary Indexes (and that is very specific to mysql) should not be seen as completely separate and different objects from the primary key. In fact, both should be used jointly and, once this information known, provide an additional tool to the mysql DBA: in Mysql, indexes embed the primary key. It leads to significant performance improvements, specifically when cleverly building implicit covering indexes such as described there

• If you feel like your data should be UNIQUE, use a unique index. You may think it's optional (for instance, working it out at application level) and that a normal index will do, but it actually represents a guarantee for Mysql that each row is unique, which incidentally provides a performance benefit.

• You can only use FULLTEXT(or otherwise called SEARCH INDEX) with Innodb (In MySQL 5.6.4 and up) and Myisam Engines

• You can only use FULLTEXTon CHAR, VARCHARand TEXTcolumn types
• FULLTEXTindex involves a LOT more than just creating an index. There's a bunch of system tables created, a completely separate caching system and some specific rules and optimizations applied. See http://dev.mysql.com/doc/refman/5.7/en/fulltext-restrictions.htmland http://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html

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

• 二级索引（这是 mysql 特有的）不应该被视为与主键完全分离和不同的对象。事实上，两者应该联合使用，一旦知道这些信息，就为 mysql DBA 提供一个额外的工具：在 Mysql 中，索引嵌入了主键。它带来了显着的性能改进，特别是在巧妙地构建隐式覆盖索引时，例如那里描述的

• 如果您觉得您的数据应该是UNIQUE，请使用唯一索引。您可能认为它是可选的（例如，在应用程序级别解决它）并且普通索引可以做到，但它实际上代表了 Mysql 每一行都是唯一的保证，这顺便提供了性能优势。

• 您只能使用FULLTEXT（或以其他方式调用SEARCH INDEX）与 Innodb（在 MySQL 5.6.4 及更高版本中）和 Myisam 引擎

• 您只能使用FULLTEXT上CHAR，VARCHAR和TEXT列类型
• 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