差异

KEY or INDEX refers to a normal non-unique index. Non-distinct values for the index are allowed, so the index may contain 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. UNIQUE refers 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 may find this undesirable: if you wish to prevent this, you should disallow NULL values in the relevant columns. PRIMARY acts exactly like a UNIQUE index, except that it is always named 'PRIMARY', and there may be only one on a table (and there should always 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 internally store a table's actual records within the PRIMARY KEY's B-tree index. FULLTEXT indexes 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.

相似之处

所有这些索引中都可以有多个列。 除了FULLTEXT，列的顺序是重要的:为了索引在查询中有用，查询必须使用索引中从左边开始的列——它不能只使用索引的第二、第三或第四部分，除非它也使用索引中前面的列来匹配静态值。(FULLTEXT索引要对查询有用，查询必须使用索引的所有列。)

所有这些都是指标。

Primary:必须是唯一的，是一个索引，是(可能的)物理索引，每个表只能有一个。

独特:正如它所说。这个值的元组不能有多行。注意，由于唯一键可以在多个列上，这并不一定意味着索引中的每一列都是唯一的，而是这些列上的每个值组合都是唯一的。

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

全文:一种更专门的索引形式，允许全文搜索。可以把它看作(本质上)为指定列中的每个“单词”创建一个“索引”。

我觉得这已经被很好地覆盖了，除了以下几点:

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 FULLTEXT on CHAR, VARCHAR and TEXT column types FULLTEXT index 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.html and http://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html

差异

KEY or INDEX refers to a normal non-unique index. Non-distinct values for the index are allowed, so the index may contain 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. UNIQUE refers 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 may find this undesirable: if you wish to prevent this, you should disallow NULL values in the relevant columns. PRIMARY acts exactly like a UNIQUE index, except that it is always named 'PRIMARY', and there may be only one on a table (and there should always 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 internally store a table's actual records within the PRIMARY KEY's B-tree index. FULLTEXT indexes 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.

相似之处

所有这些索引中都可以有多个列。 除了FULLTEXT，列的顺序是重要的:为了索引在查询中有用，查询必须使用索引中从左边开始的列——它不能只使用索引的第二、第三或第四部分，除非它也使用索引中前面的列来匹配静态值。(FULLTEXT索引要对查询有用，查询必须使用索引的所有列。)