MySQL中Index與Key的差別

看似有差不多的作用，加了Key的表與建立了Index的表，都可以進行快速的資料查詢。

他們之間的差別在于處于不同的層面上。

Key即鍵值，是 關系模型理論中的一部份，比如有主鍵（Primary Key)，外鍵（Foreign Key）等，用于 資料完整性檢查與 唯一性限制等。

而Index則處于 實作層面，比如可以對表個的任意列建立索引，那麼當建立索引的列 處于SQL語句中的Where條件中時，就可以得到快速的資料定 位，進而快速檢索。至于Unique Index，則隻是屬于Index中的一種而已，建立了Unique Index表示此列資料不可重複，猜想MySQL對Unique Index類型的索引可以做進一步特殊優化吧。

于是乎，在設計表的時候，Key隻是要處于模型層面的，而當需要進行查詢優化，則對相關列建立索引即可。

另外，在MySQL中，對于一個Primary Key的列，MySQL已經自動對其建立了Unique Index，無需重複再在上面建立索引了。

—————————————————————————————————————————————————————————————————————————————

索引被用來快速找出在一個列上用一特定值的行。沒有索引，MySQL不得不首先以第一條記錄開始并然後讀完整個表直到它找出相關的行。表越大，花費時間越多。如果表對于查詢的列有一個索引，MySQL能快速到達一個位置去搜尋到資料檔案的中間，沒有必要考慮所有資料。如果一個表有1000行，這比順序讀取至少快100倍。注意你需要存取幾乎所有1000行，它較快的順序讀取，因為此時我們避免磁盤尋道。 

所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B樹中存儲。字元串是自動地壓縮字首和結尾空間。

索引用于： 

快速找出比對一個WHERE子句的行；

當執行聯結時，從其他表檢索行；

對特定的索引列找出MAX()或MIN()值；

如果排序或分組在一個可用鍵的最左面字首上進行(例如，ORDER BY key_part_1,key_part_2)，排序或分組一個表。如果所有鍵值部分跟随DESC，鍵以倒序被讀取。 

在一些情況中，一個查詢能被優化來檢索值，不用咨詢資料檔案。如果對某些表的所有使用的列是數字型的并且構成某些鍵的最左面字首，為了更快，值可以從索引樹被檢索出來。 

—————————————————————————————————————————————————————————————————————————————

下面是建表的語句:

CREATE TABLE `phpcolor_ad` (

`id` mediumint(8) NOT NULL AUTO_INCREMENT,

`name` varchar(30) NOT NULL,

`type` mediumint(1) NOT NULL,

`code` text,

PRIMARY KEY (`id`),

KEY `type` (`type`)

);

最後一句的KEY `type` (`type`)是什麼意思？

如果隻是key的話，就是普通索引。

         mysql的key和index多少有點令人迷惑，單獨的key和其它關鍵詞結合的key(primary key)實際表示的意義是不同，這實際上考察對資料庫體系結構的了解的。

1 ：key 是資料庫的實體結構，它包含兩層意義和作用，一是限制（偏重于限制和規範資料庫的結構完整性），二是索引（輔助查詢用的）。包括primary key, unique key, foreign key 等。

       primary key 有兩個作用，一是限制作用（constraint），用來規範一個存儲主鍵和唯一性，但同時也在此key上建立了一個主鍵索引；    

                          PRIMARY KEY 限制：唯一辨別資料庫表中的每條記錄；

                                                                 主鍵必須包含唯一的值；

                                                                 主鍵列不能包含 NULL 值；

                                                                 每個表都應該有一個主鍵，并且每個表隻能有一個主鍵。（PRIMARY KEY 擁有自動定義的 UNIQUE 限制）

       unique key 也有兩個作用，一是限制作用（constraint），規範資料的唯一性，但同時也在這個key上建立了一個唯一索引；

                        UNIQUE 限制：唯一辨別資料庫表中的每條記錄。

                                                    UNIQUE 和 PRIMARY KEY 限制均為列或列集合提供了唯一性的保證。

                                                    （每個表可以有多個 UNIQUE 限制，但是每個表隻能有一個 PRIMARY KEY 限制）

       foreign key也有兩個作用，一是限制作用（constraint），規範資料的引用完整性，但同時也在這個key上建立了一個index；

      可見，mysql的key是同時具有constraint和index的意義，這點和其他資料庫表現的可能有差別。（至少在oracle上建立外鍵，不會自動建立index），是以建立key也有如下幾種方式：

（1）在字段級以key方式建立， 如 create table t (id int not null primary key);

（2）在表級以constraint方式建立，如create table t(id int, CONSTRAINT pk_t_id PRIMARY key (id));

（3）在表級以key方式建立，如create table t(id int, primary key (id));

       其它key建立類似，但不管那種方式，既建立了constraint，又建立了index，隻不過index使用的就是這個constraint或key。

2： index是資料庫的實體結構，它隻是輔助查詢的，它建立時會在另外的表空間（mysql中的innodb表空間）以一個類似目錄的結構存儲。索引要分類的話，分為字首索引、全文本索引等；

        是以，索引隻是索引，它不會去限制索引的字段的行為（那是key要做的事情）。如，create table t(id int,index inx_tx_id (id));

3 總結，最後的釋疑：

（1）我們說索引分類，分為主鍵索引（必須指定為“PRIMARY KEY”，沒有PRIMARY Index）、唯一索引（unique index，一般寫成unique key）、普通索引(index，隻有這一種才是純粹的index)等，也是基于是不是把index看作了key。

           比如 create table t(id int, unique indexinx_tx_id (id));--index當作了key使用

（2）最重要的也就是，不管如何描述，需要了解index是純粹的index（普通的key，或者普通索引index），還是被當作key（如：unique index、unique key和primary key），若當作key時則會有兩種意義或起兩種作用。

—————————————————————————————————————————————————————————————————————————————

MySQL Key值（PRI, UNI, MUL）的含義：

PRI主鍵限制；

UNI唯一限制；

MUL可以重複。

注：若是普通的key或者普通的index（實際上，普通的key與普通的index同義）。

當我們在desc 表名; 的時候,有一個Key值，表示該列是否含有索引

假設表結構如下所示

mysql> desc aa;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| id    | int(11) | YES  |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

| xx    | int(11) | YES  | PRI | NULL    |       |

+-------+---------+------+-----+---------+-------+

| yy    | int(11) | YES  | UNI | NULL    |       |

+-------+---------+------+-----+---------+-------+

| zz    | int(11) | YES  | MUL | NULL    |       |

+-------+---------+------+-----+---------+-------+

1 row in set (0.00 sec)

我們看到Key那一欄，可能會有4種值，即'啥也沒有','PRI','UNI','MUL'

1. 如果Key是空的, 那麼該列值的可以重複，表示該列沒有索引, 或者是一個非唯一的複合索引的非前導列

2. 如果Key是PRI,  那麼該列是主鍵的組成部分

3. 如果Key是UNI,  那麼該列是一個唯一值索引的第一列(前導列)，且不能含有空值(NULL)

4. 如果Key是MUL,  那麼該列的值可以重複, 該列是一個非唯一索引的前導列(第一列)或者是一個唯一性索引的組成部分但是可以含有空值NULL

注：

1、如果對于一個列的定義，同時滿足上述4種情況的多種，比如一個列既是PRI，又是UNI（如果是PRI，則一定是UNI）

那麼"desc 表名"; 的時候，顯示的Key值按照優先級來顯示 PRI->UNI->MUL

那麼此時，顯示PRI。

2、如果某列不能含有空值，同時該表沒有主鍵，則一個唯一性索引列可以顯示為PRI，

3、如果多列構成了一個唯一性複合索引，那麼一個唯一性索引列可以顯示為MUL。（因為雖然索引的多列組合是唯一的，比如ID+NAME是唯一的，但是每一個單獨的列依然可以有重複的值，因為隻要ID+NAME是唯一的即可）