在 MySQL 中使用 INDEX 和 KEY 有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1401572/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What's the difference between using INDEX vs KEY in MySQL?
提问by shin
I know how to use INDEX as in the following code. And I know how to use foreign keyand primary key.
我知道如何在以下代码中使用 INDEX。而且我知道如何使用外键和主键。
CREATE TABLE tasks (
task_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
parent_id INT UNSIGNED NOT NULL DEFAULT 0,
task VARCHAR(100) NOT NULL,
date_added TIMESTAMP NOT NULL,
date_completed TIMESTAMP,
PRIMARY KEY (task_id),
INDEX parent (parent_id),
....
However I found a code using KEY instead of INDEX as following.
但是我找到了一个使用 KEY 而不是 INDEX 的代码，如下所示。
...
KEY order_date (order_date)
...
I could not find any explanation on the official MySQL page. Could anyone tell me what is the differences between KEY and INDEX?
我在 MySQL 官方页面上找不到任何解释。谁能告诉我 KEY 和 INDEX 之间有什么区别？
The only difference I see is that when I use KEY ..., I need to repeat the word, e.g.KEY order_date (order_date).
我看到的唯一区别是，当我使用 时KEY ...，我需要重复这个词，例如KEY order_date (order_date)。
回答by nos
There's no difference. They are synonyms.
没有区别。它们是同义词。
From the CREATE TABLEmanual entry:
KEYis normally a synonym forINDEX. The key attributePRIMARY KEYcan also be specified as justKEYwhen given in a column definition. This was implemented for compatibility with other database systems.
KEY通常是 的同义词INDEX。key 属性PRIMARY KEY也可以KEY在列定义中指定时指定。这是为了与其他数据库系统兼容而实施的。
回答by Stefan
Here is a nice descriptionabout the "difference":
这是关于“差异”的一个很好的描述：
"MySQL requires every Key also be indexed, that's an implementation detail specific to MySQL to improve performance."
“MySQL 要求每个 Key 也都被索引，这是 MySQL 特定的实现细节，以提高性能。”
回答by fineliner
It is mentioned as a synonym for INDEXin the 'create table' docs:
MySQL 5.5 Reference Manual :: 13 SQL Statement Syntax :: 13.1 Data Definition Statements :: 13.1.17 CREATE TABLE Syntax
它INDEX在“创建表”文档中作为同义词提到：
MySQL 5.5 参考手册 :: 13 SQL 语句语法 :: 13.1 数据定义语句 :: 13.1.17 CREATE TABLE 语法
Nos already cited the section and linked the help for 5.1.
Nos 已经引用了该部分并链接了 5.1 的帮助。
Like PRIMARY KEYcreates a primary key and an index for you,
KEYcreates an index only.
就像PRIMARY KEY为你创建一个主键和一个索引一样，
KEY只创建一个索引。
回答by JayD
Keys are special fields that play very specific roles within a table, and the type of key determines its purpose within the table.
键是在表中扮演非常特定角色的特殊字段，键的类型决定了它在表中的用途。
An index is a structure that RDBMS(database management system) provides to improve data processing. An index has nothing to do with a logical database structure.
索引是RDBMS（数据库管理系统）为改进数据处理而提供的结构。索引与逻辑数据库结构无关。
SO...
所以...
Keys are logical structures you use to identify records within a table and indexes are physical structures you use to optimize data processing.
键是用于标识表中记录的逻辑结构，索引是用于优化数据处理的物理结构。
Source: Database Design for Mere Mortals
资料来源：普通人的数据库设计
Author: Michael Hernandez
作者：迈克尔·埃尔南德斯
回答by Atulya Anand
A key is a set of columns or expressions on which we build an index.
键是我们在其上构建索引的一组列或表达式。
While an index is a structure that is stored in database, keys are strictly a logical concept.
Index help us in fast accessing a record, whereas keys just identify the records uniquely.
Every table will necessarily have a key, but having an index is not mandatory.
虽然索引是存储在数据库中的结构，但键是严格的逻辑概念。
索引帮助我们快速访问记录，而键只是唯一地标识记录。
每个表都必须有一个键，但有一个索引不是强制性的。
Check on https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT721
检查https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT721
