在MySQL中使用INDEX和KEY有什么区别?

tcbh2hod  于 2022-12-10  发布在  Mysql
关注(0)|答案(5)|浏览(152)

I know how to use INDEX as in the following code. And I know how to use foreign key and primary key.

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        NULL, 

    PRIMARY KEY ( task_id ), 
    INDEX parent ( parent_id )
)

However I found a code using KEY instead of INDEX as following.

CREATE TABLE orders (
    
    order_id        int unsigned NOT NULL AUTO_INCREMENT,
    -- etc 

    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 ?
The only difference I see is that when I use KEY ... , I need to repeat the word, e.g. KEY order_date ( order_date ) .

q3qa4bjr

q3qa4bjr1#

There's no difference. They are synonyms, though INDEX should be preferred (as INDEX is ISO SQL compliant, while KEY is a MySQL-specific, non-portable, extension).
From the CREATE TABLE manual entry:
KEY is normally a synonym for INDEX . The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.
By "The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition.", it means that these three CREATE TABLE statements below are equivalent and generate identical TABLE objects in the database:

CREATE TABLE orders1 (
    order_id int PRIMARY KEY
);

CREATE TABLE orders2 (
    order_id int KEY
);

CREATE TABLE orders3 (
    order_id int NOT NULL,

    PRIMARY KEY ( order_id )
);

...while these 2 statements below (for orders4 , orders5 ) are equivalent with each other, but not with the 3 statements above, as here KEY and INDEX are synonyms for INDEX , not a PRIMARY KEY :

CREATE TABLE orders4 (
    order_id int NOT NULL,

    KEY ( order_id )
);

CREATE TABLE orders5 (
    order_id int NOT NULL,

    INDEX ( order_id )
);

...as the KEY ( order_id ) and INDEX ( order_id ) members do not define a PRIMARY KEY , they only define a generic INDEX object, which is nothing like a KEY at all (as it does not uniquely identify a row).
As can be seen by running SHOW CREATE TABLE orders1...5 :
| Table | SHOW CREATE TABLE... |
| ------------ | ------------ |
| orders1 | CREATE TABLE orders1 (order_id int NOT NULL,PRIMARY KEY ( order_id )) |
| orders2 | CREATE TABLE orders2 (order_id int NOT NULL,PRIMARY KEY ( order_id )) |
| orders3 | CREATE TABLE orders3 (order_id int NOT NULL,PRIMARY KEY ( order_id )) |
| orders4 | CREATE TABLE orders4 (order_id int NOT NULL,KEY ( order_id )) |
| orders5 | CREATE TABLE orders5 (order_id int NOT NULL,KEY ( order_id )) |

fv2wmkja

fv2wmkja2#

下面是nice description的有关“区别”:
“MySQL还要求对每个键进行索引,这是MySQL特有的实现细节,以提高性能。”

olmpazwi

olmpazwi3#

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.
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

gudnpqoy

gudnpqoy4#

在“创建表”文档中,它被作为INDEX的同义词提及:MySQL 5.5 Reference Manual :: 13 SQL Statement Syntax :: 13.1 Data Definition Statements :: 13.1.17 CREATE TABLE Syntax
@Nos已经引用了该部分并链接了5.1的帮助。
PRIMARY KEY创建主键和索引一样,KEY只创建索引。

waxmsbnn

waxmsbnn5#

A key is a set of columns or expressions on which we build an index.

  1. While an index is a structure that is stored in database, keys are strictly a logical concept.
  2. Index help us in fast accessing a record, whereas keys just identify the records uniquely.
  3. 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

相关问题