5.2　創(chuàng)建索引

MySQL支持多種方法在單個或多個列上創(chuàng)建索引：在創(chuàng)建表的定義語句CREATE TABLE中指定索引列，使用ALTER TABLE語句在存在的表上創(chuàng)建索引，或者使用CREATE INDEX語句在已存在的表上添加索引。本節(jié)將詳細介紹這3種方法。

5.2.1　創(chuàng)建表的時候創(chuàng)建索引

使用CREATE TABLE創(chuàng)建表時，除了可以定義列的數(shù)據(jù)類型外，還可以定義主鍵約束、外鍵約束或者唯一性約束，而不論創(chuàng)建哪種約束，在定義約束的同時相當于在指定列上創(chuàng)建了一個索引。創(chuàng)建表時創(chuàng)建索引的基本語法格式如下：

UNIQUE、FULLTEXT和SPATIAL為可選參數(shù)，分別表示唯一索引、全文索引和空間索引；INDEX與KEY為同義詞，兩者的作用相同，用來指定創(chuàng)建索引；col_name為需要創(chuàng)建索引的字段列，該列必須從數(shù)據(jù)表中定義的多個列中選擇；index_name指定索引的名稱，為可選參數(shù)，如果不指定，那么MySQL默認col_name為索引值；length為可選參數(shù)，表示索引的長度，只有字符串類型的字段才能指定索引長度；ASC或DESC指定升序或者降序的索引值存儲。

1. 創(chuàng)建普通索引

最基本的索引類型沒有唯一性之類的限制，其作用只是加快對數(shù)據(jù)的訪問速度。

【例5.1】在book表中的year_publication字段上建立普通索引，SQL語句如下：

該語句執(zhí)行完畢之后，使用SHOW CREATE TABLE查看表結(jié)構(gòu)：

由結(jié)果可以看到，book1表的year_publication字段上成功建立索引，其索引名稱year_publication為MySQL自動添加的。使用EXPLAIN語句查看索引是否正在使用：

EXPLAIN語句輸出結(jié)果的各個行解釋如下：

（1）select_type行指定所使用的SELECT查詢類型，這里值為SIMPLE，表示簡單的SELECT，不使用UNION或子查詢。其他可能的取值有PRIMARY、UNION、SUBQUERY等。

（2）table行指定數(shù)據(jù)庫讀取的數(shù)據(jù)表的名字，它們按被讀取的先后順序排列。

（3）type行指定本數(shù)據(jù)表與其他數(shù)據(jù)表之間的關(guān)聯(lián)關(guān)系，可能的取值有system、const、eq_ref、ref、range、index和all。

（4）possible_keys行給出了MySQL在搜索數(shù)據(jù)記錄時可選用的各個索引。

（5）key行是MySQL實際選用的索引。

（6）key_len行給出索引按字節(jié)計算的長度，key_len數(shù)值越小，表示越快。

（7）ref行給出了關(guān)聯(lián)關(guān)系中另一個數(shù)據(jù)表里的數(shù)據(jù)列的名字。

（8）rows行是MySQL在執(zhí)行這個查詢時預計會從這個數(shù)據(jù)表里讀出的數(shù)據(jù)行的個數(shù)。

（9）Extra行提供了與關(guān)聯(lián)操作有關(guān)的信息。

可以看到，possible_keys和key的值都為year_publication，查詢時使用了索引。

2. 創(chuàng)建唯一索引

創(chuàng)建唯一索引的主要目的是減少查詢索引列操作的執(zhí)行時間，尤其是對比較龐大的數(shù)據(jù)表。它與前面的普通索引類似，不同的是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。

【例5.2】創(chuàng)建一個表t1，在表中的id字段上使用UNIQUE關(guān)鍵字創(chuàng)建唯一索引。

該語句執(zhí)行完畢之后，使用SHOW CREATE TABLE查看表結(jié)構(gòu)：

由結(jié)果可以看到，id字段上已經(jīng)成功建立了一個名為UniqIdx的唯一索引。

3. 創(chuàng)建單列索引

單列索引是在數(shù)據(jù)表中的某一個字段上創(chuàng)建的索引，一個表中可以創(chuàng)建多個單列索引。前面兩個例子中創(chuàng)建的索引都為單列索引。

【例5.3】創(chuàng)建一個表t2，在表中的name字段上創(chuàng)建單列索引。

表結(jié)構(gòu)如下：

該語句執(zhí)行完畢之后，使用SHOW CREATE TABLE查看表結(jié)構(gòu)：

由結(jié)果可以看到，id字段上已經(jīng)成功建立了一個名為SingleIdx的單列索引，索引長度為20。

4. 創(chuàng)建組合索引

組合索引是在多個字段上創(chuàng)建一個索引。

【例5.4】創(chuàng)建表t3，在表中的id、name和age字段上建立組合索引，SQL語句如下：

該語句執(zhí)行完畢之后，使用SHOW CREATE TABLE查看表結(jié)構(gòu)：

由結(jié)果可以看到，id、name和age字段上已經(jīng)成功建立了一個名為MultiIdx的組合索引。

組合索引可起幾個索引的作用，但是使用時并不是隨便查詢哪個字段都可以使用索引，而是遵從“最左前綴”：利用索引中最左邊的列集來匹配行，這樣的列集稱為最左前綴。例如，這里由id、name和age三個字段構(gòu)成的索引，索引行中按id/name/age的順序存放，索引可以搜索的字段組合為：（id, name, age）、（id, name）或者id。如果列不構(gòu)成索引最左面的前綴，MySQL就不能使用局部索引，如（age）或者（name,age）組合不能使用索引查詢。

在t3表中，查詢id和name字段，使用EXPLAIN語句查看索引的使用情況：

可以看到，查詢id和name字段時，使用了名稱為MultiIdx的索引，如果查詢（name,age）組合或者單獨查詢name和age字段，結(jié)果如下：

此時，possible_keys和key值為NULL，并沒有使用在t3表中創(chuàng)建的索引進行查詢。

5. 創(chuàng)建全文索引

FULLTEXT全文索引可以用于全文搜索。只有MyISAM存儲引擎支持FULLTEXT索引，并且只為CHAR、VARCHAR和TEXT列創(chuàng)建索引。索引總是對整個列進行，不支持局部（前綴）索引。

【例5.5】創(chuàng)建表t4，在表中的info字段上建立全文索引，SQL語句如下：

語句執(zhí)行完畢之后，使用SHOW CREATE TABLE查看表結(jié)構(gòu)：

由結(jié)果可以看到，info字段上已經(jīng)成功建立了一個名為FullTxtIdx的FULLTEXT索引。全文索引非常適合大型數(shù)據(jù)集，對于小的數(shù)據(jù)集，它的用處比較小。

6. 創(chuàng)建空間索引

空間索引必須在MyISAM類型的表中創(chuàng)建，且空間類型的字段必須為非空。

【例5.6】創(chuàng)建表t5，在空間類型為GEOMETRY的字段上創(chuàng)建空間索引，SQL語句如下：

該語句執(zhí)行完畢之后，使用SHOW CREATE TABLE查看表結(jié)構(gòu)：

可以看到，t5表的g字段上創(chuàng)建了名稱為spatIdx的空間索引。注意創(chuàng)建時指定空間類型字段值的非空約束，并且表的存儲引擎為MyISAM。

5.2.2　在已經(jīng)存在的表上創(chuàng)建索引

在已經(jīng)存在的表中創(chuàng)建索引可以使用ALTER TABLE語句或者CREATE INDEX語句，本節(jié)將介紹如何使用ALTER TABLE和CREATE INDEX語句在已知的表字段上創(chuàng)建索引。

1. 使用ALTER TABLE語句創(chuàng)建索引

ALTER TABLE語句創(chuàng)建索引的基本語法如下：

與創(chuàng)建表時創(chuàng)建索引的語法不同的是，在這里使用了ALTER TABLE和ADD關(guān)鍵字，ADD表示向表中添加索引。

【例5.7】在book表中的bookname字段上建立名為BkNameIdx的普通索引。

添加索引之前，使用SHOW INDEX語句查看指定表中創(chuàng)建的索引：

其中各個主要參數(shù)的含義為：

（1）Table表示創(chuàng)建索引的表。

（2）Non_unique表示索引非唯一，1代表非唯一索引，0代表唯一索引。

（3）Key_name表示索引的名稱。

（4）Seq_in_index表示該字段在索引中的位置，單列索引該值為1，組合索引為每個字段在索引定義中的順序。

（5）Column_name表示定義索引的列字段。

（6）Sub_part表示索引的長度。

（7）Null表示該字段是否能為空值。

（8）Index_type表示索引類型。

可以看到，book表中已經(jīng)存在了一個索引，即前面已經(jīng)定義的名稱為year_publication的索引，該索引為非唯一索引。

下面使用ALTER TABLE語句在bookname字段上添加索引，SQL語句如下：

     ALTER TABLE book ADD INDEX BkNameIdx( bookname(30) );

使用SHOW INDEX語句查看表中的索引：

可以看到，現(xiàn)在表中已經(jīng)有了兩個索引，另一個為通過ALTER TABLE語句添加的名稱為BkNameIdx的索引，該索引為非唯一索引，長度為30。

【例5.8】在book表的bookId字段上建立名稱為UniqidIdx的唯一索引，SQL語句如下：

     ALTER TABLE book ADD UNIQUE INDEX UniqidIdx ( bookId );

使用SHOW INDEX語句查看表中的索引：

可以看到Non_unique的屬性值為0，表示名稱為UniqidIdx的索引為唯一索引，創(chuàng)建唯一索引成功。

【例5.9】在book表的comment字段上建立單列索引，SQL語句如下：

     ALTER TABLE book ADD INDEX BkcmtIdx ( comment(50) );

使用SHOW INDEX語句查看表中的索引：

可以看到，語句執(zhí)行之后，在book表的comment字段上建立了名稱為BkcmgIdx的索引，長度為50，在查詢時，只需要檢索前50個字符。

【例5.10】在book表的authors和info字段上建立組合索引，SQL語句如下：

     ALTER TABLE book ADD INDEX BkAuAndInfoIdx ( authors(30),info(50) );

使用SHOW INDEX語句查看表中的索引：

可以看到名稱為BkAuAndInfoIdx的索引由兩個字段組成，authors字段長度為30，在組合索引中的序號為1，該字段不允許空值（NULL）；info字段長度為50，在組合索引中的序號為2，該字段可以為空值（NULL）。

【例5.11】創(chuàng)建表t6，在t6表上使用ALTER TABLE語句創(chuàng)建全文索引，SQL語句如下：

首先創(chuàng)建表t6，語句如下：

注意修改ENGINE參數(shù)為MyISAM，MySQL默認引擎InnoDB不支持全文索引。

使用ALTER TABLE語句在info字段上創(chuàng)建全文索引：

     ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx ( info );

使用SHOW INDEX語句查看索引：

可以看到，t6表中已經(jīng)創(chuàng)建了名稱為infoFTIdx的索引，該索引在info字段上創(chuàng)建，類型為FULLTEXT，允許空值。

【例5.12】創(chuàng)建表t7，在t7的空間數(shù)據(jù)類型字段g上創(chuàng)建名稱為spatIdx的空間索引，SQL語句如下：

     CREATE TABLE t7 ( g GEOMETRY NOT NULL )ENGINE=MyISAM;

使用ALTER TABLE在表t7的g字段建立空間索引：

     ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g);

使用SHOW INDEX語句查看索引：

可以看到，t7表的g字段上創(chuàng)建了名稱為spatIdx的空間索引。

2. 使用CREATE INDEX創(chuàng)建索引

CREATE INDEX語句可以在已經(jīng)存在的表上添加索引，在MySQL中，CREATE INDEX被映射到一個ALTER TABLE語句上，基本語法結(jié)構(gòu)為：

可以看到CREATE INDEX語句和ALTER INDEX語句的語法基本一樣，只是關(guān)鍵字不同。

在這里，使用相同的表book，假設該表中沒有任何索引值，創(chuàng)建book表的語句如下：

【例5.13】在book表中的bookname字段上建立名為BkNameIdx的普通索引，SQL語句如下：

     CREATE INDEX BkNameIdx ON book(bookname);

語句執(zhí)行完畢之后，將在book表中創(chuàng)建名稱為BkNameIdx的普通索引。讀者可以使用SHOW INDEX或者SHOW CREATE TABLE語句查看book表中的索引，其索引內(nèi)容與前面介紹的相同。

【例5.14】在book表的bookId字段上建立名稱為UniqidIdx的唯一索引，SQL語句如下：

     CREATE UNIQUE INDEX UniqidIdx  ON book ( bookId );

語句執(zhí)行完畢之后，將在book表中創(chuàng)建名稱為UniqidIdx的唯一索引。

【例5.15】在book表的comment字段上建立單列索引，SQL語句如下：

     CREATE INDEX BkcmtIdx ON book(comment(50) );

語句執(zhí)行完畢之后，將在book表的comment字段上建立一個名為BkcmtIdx的單列索引，長度為50。

【例5.16】在book表的authors和info字段上建立組合索引，SQL語句如下：

     CREATE INDEX BkAuAndInfoIdx ON book ( authors(20),info(50) );

語句執(zhí)行完畢之后，將在book表的authors和info字段上建立一個名為BkAuAndInfoIdx的組合索引，authors的索引序號為1，長度為20，info的索引序號為2，長度為50。

【例5.17】刪除表t6，重新建立表t6，在t6表中使用CREATE INDEX語句，在CHAR類型的info字段上創(chuàng)建全文索引，SQL語句如下：

首先刪除表t6，并重新建立該表，分別輸入下面的語句：

使用CREATE INDEX在t6表的info字段上創(chuàng)建名稱為infoFTIdx的全文索引：

     CREATE FULLTEXT INDEX infoFTIdx ON t6(info);

語句執(zhí)行完畢之后，將在t6表中創(chuàng)建名稱為infoFTIdx的索引，該索引在info字段上創(chuàng)建，類型為FULLTEXT，允許空值。

【例5.18】刪除表t7，重新創(chuàng)建表t7，在t7表中使用CREATE INDEX語句在空間數(shù)據(jù)類型字段g上創(chuàng)建名稱為spatIdx的空間索引，SQL語句如下：

首先刪除表t7，并重新建立該表，分別輸入下面的語句：

使用CREATE INDEX語句在表t7的g字段上建立空間索引：

     CREATE SPATIAL INDEX spatIdx ON t7 (g);

語句執(zhí)行完畢之后，將在t7表中創(chuàng)建名稱為spatIdx的空間索引，該索引在g字段上創(chuàng)建。