MySQL中KEY、PRIMARY KEY、UNIQUE KEY、INDEX 的區(qū)別

對于題目中提出的問題，可以拆分來一步步解決。在 MySQL 中 KEY 和 INDEX 是同義。那這個(gè)問題就可以簡化為 PRIMARY KEY，UNIQUE KEY 和 INDEX 的區(qū)別。而這三者也正好是索引的劃分，主鍵索引，唯一索引和普通索引（INDEX）。

使用 INDEX 來加速從數(shù)據(jù)庫中讀取數(shù)據(jù)。INDEX 通常加在那些 JOIN， WHERE，和 ORDER BY 子句的列上。

創(chuàng)建索引時(shí)，需要確保該索引是應(yīng)用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)。 實(shí)際上，索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄。

索引也有它的缺點(diǎn)：雖然索引提高了查詢速度，卻會降低更新表的速度，如對表進(jìn)行INSERT、UPDATE和DELETE。因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件。

MySQL 中 KEY 與 INDEX 區(qū)別

KEY 通常是 INDEX 同義詞。如果關(guān)鍵字屬性 PRIMARY KEY 在列定義中已給定，則 PRIMARY KEY 也可以只指定為KEY。這么做的目的是與其它數(shù)據(jù)庫系統(tǒng)兼容。 PRIMARY KEY 是一個(gè)唯一 KEY，此時(shí)，所有的關(guān)鍵字列必須定義為NOT NULL。如果這些列沒有被明確地定義為NOT NULL，MySQL應(yīng)隱含地定義這些列。

KEY 即鍵值，是關(guān)系模型理論中的一部份，比如有主鍵（PRIMARY KEY)，外鍵（Foreign KEY）等，用于數(shù)據(jù)完整性檢否與唯一性約束等。而 INDEX 則處于實(shí)現(xiàn)層面，比如可以對表個(gè)的任意列建立索引，那么當(dāng)建立索引的列處于SQL語句中的Where條件中時(shí)，就可以得到快速的數(shù)據(jù)定位，從而快速檢索。至于UNIQUE INDEX，則只是屬于INDEX中的一種而已，建立了UNIQUE INDEX表示此列數(shù)據(jù)不可重復(fù)，猜想MySQL對UNIQUE INDEX類型的索引可以做進(jìn)一步特殊優(yōu)化吧。

于是，在設(shè)計(jì)表的時(shí)候，KEY只是要處于模型層面的，而當(dāng)需要進(jìn)行查詢優(yōu)化，則對相關(guān)列建立索引即可。

KEY

KEY 是數(shù)據(jù)庫的物理結(jié)構(gòu)，包含兩層含義，一是約束，偏重于約束和規(guī)范數(shù)據(jù)庫的結(jié)構(gòu)完整性，二是索引，輔助查詢。
•primary key 有兩個(gè)作用，一是約束作用（constraint），用來規(guī)范一個(gè)存儲主鍵和唯一性，但同時(shí)也在此key上建立了一個(gè)index；
•unique key 也有兩個(gè)作用，一是約束作用（constraint），規(guī)范數(shù)據(jù)的唯一性，但同時(shí)也在這個(gè)key上建立了一個(gè)index；
•foreign key也有兩個(gè)作用，一是約束作用（constraint），規(guī)范數(shù)據(jù)的引用完整性，但同時(shí)也在這個(gè)key上建立了一個(gè)index；

可見，key是同時(shí)具有constraint和index的意義.

INDEX

INDEX 也是數(shù)據(jù)庫的物理結(jié)構(gòu)，但他只有輔助查詢作用，它會在創(chuàng)建時(shí)占用另外的空間。索引分為前綴索引、全文索引等。索引只是索引，不會去約束索引字段的行為。

PRIMARY KEY 和 UNIQUE KEY 的區(qū)別

PRIMARY KEYs（主鍵） 和 UNIQUE KEYs（唯一鍵約束） 是類似的， PRIMARY KEY通常是一列，也有可能多列，通常由他來決定一行數(shù)據(jù)(row)。 一張表只能有一個(gè) PRIMARY KEY，但可以有很多 UNIQUE KEY。 當(dāng)給一列設(shè)置為 UNIQUE KEY 之后，不能有兩行在該列上有相同的數(shù)據(jù)。 PRIMARY KEY 不允許有 NULL值，但是 UNIQUE KEY 可以。

修改表 `ALTER TABLE table_name ADD PRIMARY KEY(column_name, …)

總結(jié)，相同點(diǎn)：
•PRIMARY KEY 和 UNIQUE KEY 都是用來保證列上數(shù)據(jù)的為原型
•都可以在一列或者多列上加

差異點(diǎn)：
•同一張表 PRIMARY KEY 只能有一個(gè)， UNIQUE KEY可以有多個(gè)
•
PRIMARY KEY 不能有空值， UNIQUE KEY 可以有。如果 PRIMARY KEY 的1個(gè)或多個(gè)列為NULL，在增加PRIMARY KEY時(shí)，列自動(dòng)更改為 NOT NULL 。而UNIQUE KEY 對列沒有要求是通過參考索引實(shí)施的，如果插入的值均為NULL，則根據(jù)索引的原理，全NULL值不被記錄在索引上，所以插入全NULL值時(shí)，可以有重復(fù)的，而其他的則不能插入重復(fù)值。

alter table t add constraint uk_t_1 UNIQUE (a,b); insert into t (a ,b ) values (null,1); # 不能重復(fù) insert into t (a ,b ) values (null,null);#可以重復(fù)

在MySQL中，對于一個(gè)PRIMARY KEY的列，MySQL已經(jīng)自動(dòng)對其建立了UNIQUE INDEX，無需重復(fù)再在上面建立索引了。

網(wǎng)上關(guān)于 PRIMARY KEY 和 UNIQUE INDEX 的一段解釋：

Note that “PRIMARY” is called PRIMARY KEY not INDEX.
KEY is something on the logical level, describes your table and database design (i.e. enforces referential integrity …)
INDEX is something on the physical level, helps improve access time for table operations.
Behind every PK there is (usually) UNIQUE INDEX created (automatically).

操作索引

建立索引會占用磁盤空間的索引文件。
CREATE INDEX IndexName ON mytable(username(length));

如果是 CHAR，VARCHAR 類型，length 可以小于字段實(shí)際長度；如果是 BLOB 和 TEXT類型，必須指定 length。

在創(chuàng)建表時(shí)創(chuàng)建索引：

CREATE TABLE mytable( 
 ID INT NOT NULL,  
 username VARCHAR(15) NOT NULL,
 INDEX [INDEXName] (username(length)) 
);

刪除索引

DROP INDEX [INDEXName] ON mytable;