索引是加速表內容訪問的主要手段，特別對涉及多個表的連接的查詢更是如此。這是數據庫優化中的一個重要內容，我們要了解為什麼需要索引，索引如何工作以及怎樣利用它們來優化查詢。本節中，我們將介紹索引的特點，以及創建和刪除索引的語法。
索引的特點
所有的MySQL列類型能被索引。在相關的列上的使用索引是改進SELECT操作性能的最好方法。
一個表最多可有16個索引。最大索引長度是256個字節，盡管這可以在編譯MySQL時被改變。
對於CHAR和VARCHAR列，你可以索引列的前綴。這更快並且比索引整個列需要較少的磁盤空間。對於BLOB和TEXT列，你必須索引列的前綴，你不能索引列的全部。
MySQL能在多個列上創建索引。一個索引可以由最多15個列組成。（在CHAR和VARCHAR列上，你也可以使用列的前綴作為一個索引的部分）。
雖然隨著 MySQL 的進一步開發創建索引的約束將會越來越少，但現在還是存在一些約束的。下面的表根據索引的特性，給出了 ISAM 表和 MyISAM 表之間的差別：
表2-1 通道信息特征字對照表
索引的特點 ISAM 表 MyISAM 表
NULL 值
BLOB 和 TEXT 列
每個表中的索引數
每個索引中的列數
最大索引行尺寸
不允許
不能索引
16
16
256 字節 允許
只能索引列的前綴
32
16
500 字節
從此表中可以看到，對於 ISAM 表來說，其索引列必須定義為 NOT NULL，並且不能對 BLOB 和 TEXT 列進行索引。MyISAM 表類型去掉了這些限制，而且減緩了其他的一些限制。兩種表類型的索引特性的差異表明，根據所使用的 MySQL 版本的不同，有可能對某些列不能進行索引。例如，如果使用3.23 版以前的版本，則不能對包含 NULL 值的列進行索引。
索引有如下的幾種情況：
INDEX索引：通常意義的索引，某些情況下KEY是它的一個同義詞。索引的列可以包括重復的值。
UNIQUE索引：唯一索引，保證了列不包含重復的值，對於多列唯一索引，它保證值的組合不重復。
PRIMARY KEY索引：也UNIQUE索引非常類似。事實上，PRIMARY KEY索引僅是一個具有PRIMARY名稱的UNIQUE索引。這表示一個表只能包含一個PRIMARY KEY。
用Alter Table語句創建與刪除索引
為了給現有的表增加一個索引，可使用 ALTER TABLE 或 CREATE INDEX 語句。ALTER TABLE 最常用，因為可用它來創建普通索引、UNIQUE 索引或 PRIMARY KEY 索引，如：
ALTER TABLE tbl_name ADD INDEX index_name (column_list)
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)
ALTER TABLE tbl_name ADD PRIMARY KEY index_name (column_list)
其中 tbl_name 是要增加索引的表名，而 column_list 指出對哪些列進行索引。一個(col1,col2,...)形式的列表創造一個多列索引。索引值有給定列的值串聯而成。如果索引由不止一列組成，各列名之間用逗號分隔。索引名 index_name 是可選的，因此可以不寫它，MySQL 將根據第一個索引列賦給它一個名稱。ALTER TABLE 允許在單個語句中指定多個表的更改，因此可以在同時創建多個索引。
同樣，也可以用ALTER TABLE語句產出列的索引：
ALTER TABLE tbl_name DROP INDEX index_name
ALTER TABLE tbl_name DROP PRIMARY KEY
注意上面第一條語句可以用來刪除各種類型的索引，而第三條語句只在刪除 PRIMARY KEY 索引時使用；在此情形中，不需要索引名，因為一個表只可能具有一個這樣的索引。如果沒有明確地創建作為 PRIMARY KEY 的索引，但該表具有一個或多個 UNIQUE 索引，則 MySQL 將刪除這些 UNIQUE 索引中的第一個。
如果從表中刪除了列，則索引可能會受到影響。如果所刪除的列為索引的組成部分，則該列也會從索引中刪除。如果組成索引的所有列都被刪除，則整個索引將被刪除。
例如，對於上面所使用的student為例，你可能想為之創建這樣的索引，以加速表的檢索速度：
MySQL> ALTER TABLE student
-> ADD PRIMARY KEY(id),
-> ADD INDEX mark(english,Chinese,history);
這個例子，既包括PRIMARY索引，也包括多列索引。記住，使用PRIMARY索引的列，必須是一個具有NOT NULL屬性的列，如果你願意產看創建的索引的情況，可以使用SHOW INDEX語句：
MySQL> SHOW INDEX FROM student;
其結果為：
+---------+------------+----------+--------------+-------------+-
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+---------+------------+----------+--------------+-------------+-
| student | 0 | PRIMARY | 1 | id |
| student | 1 | mark | 1 | english |
| student | 1 | mark | 2 | chinese |
| student | 1 | mark | 3 | history |
+---------+------------+----------+--------------+-------------+-
由於列數太多，上表並沒有包括所有的輸出，讀者可以試著自己查看。
再使用ALTER TABLE語句刪除索引，刪除索引需要知道索引的名字，你可以通過SHOW INDEX語句得到：
MySQL> ALTER TABLE student DROP PRIMARY KEY,
-> DROP INDEX mark;
再產看表中的索引，其語句和輸出為：
MySQL> SHOW INDEX FROM student;
Empty set (0.01 sec)
用CREATE\DROP INDEX創建索引
還可以用CREATE INDEX語句來創建索引.CREATE INDEX 是在 MySQL 3.23版中引入的，但如果使用3.23 版以前的版本，可利用 ALTER TABLE 語句創建索引（MySQL 通常在內部將 CREATE INDEX 映射到 ALTER TABLE）。該語句創建索引的語法如下：
CREATE UNIQUE INDEX index_name ON tbl_name (column_list)
CREATE INDEX index_name ON tbl_name (column_list)
tbl_name、index_name 和 column_list 具有與 ALTER TABLE 語句中相同的含義。這裡索引名不可選。很明顯，CREATE INDEX 可對表增加普通索引或 UNIQUE 索引，不能用 CREATE INDEX 語句創建 PRIMARY KEY 索引。
可利用 DROP INDEX語句來刪除索引。類似於 CREATE INDEX 語句，DROP INDEX 通常在內部作為一條 ALTER TABLE 語句處理，並且DROP INDEX是在 MySQL 3.22 中引入的。
刪除索引語句的語法如下：
DROP INDEX index_name ON tbl_name
還是上一節的例子，由於CREATE INDEX不能創建PRIMARY索引，所以這裡我們值創建一個多列索引：
MySQL> CREATE INDEX mark ON student(english,chinese,history);
同樣的檢查student表，可知：
MySQL> SHOW INDEX FROM student;
+---------+------------+----------+--------------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+---------+------------+----------+--------------+-------------+
| student | 1 | mark | 1 | english |
| student | 1 | mark | 2 | chinese |
| student | 1 | mark | 3 | history |
+---------+------------+----------+--------------+-------------+
然後使用下面的語句刪除索引：
MySQL> DROP INDEX mark ON student;
在創建表時指定索引
要想在發布 CREATE TABLE 語句時為新表創建索引，所使用的語法類似於 ALTER TABLE 語句的語法，但是應該在您定義表列的語句部分指定索引創建子句，如下所示：
CREATE TABLE tbl_name
(
…
INDEX index_name (column_list),
KEY index_name (column_list),
UNIQUE index_name (column_list),
PRIMARY KEY index_name (column_list),
…
)
與ALTER TABLE 一樣，索引名對於 INDEX 和 UNIQUE 都是可選的，如果未給出，MySQL 將為其選一個。另外，這裡KEY時INDEX的一個別名，具有相同的意義。
有一種特殊情形：可在列定義之後增加 PRIMARY KEY 創建一個單列的PRIMARY KEY 索引，如下所示：
CREATE TABLE tbl_name
(
i INT NOT NULL PRIMARY KEY
)
該語句等價於以下的語句:
CREATE TABLE tbl_name
(
i INT NOT NULL,
PRIMARY KEY (i)
)
前面所有表創建樣例都對索引列指定了 NOT NULL。如果是 ISAM 表，這是必須的，因為不能對可能包含 NULL 值的列進行索引。如果是 MyISAM 表，索引列可以為 NULL，只要該索引不是 PRIMARY KEY 索引即可。
在CREATE TBALE語句中可以某個串列的前綴進行索引（列值的最左邊 n 個字符）。
如果對某個串列的前綴進行索引，應用 column_list 說明符表示該列的語法為 col_name(n) 而不用col_name。例如，下面第一條語句創建了一個具有兩個 CHAR 列的表和一個由這兩列組成的索引。第二條語句類似，但只對每個列的前綴進行索引：
CREATE TABLE tbl_name
(
name CHAR(30),
address CHAR(60),
INDEX (name,address)
)
CREATE TABLE tbl_name
(
name CHAR(30),
address CHAR(60),
INDEX (name(10),address(20))
)
你可以檢查所創建表的索引：
MySQL> SHOW INDEX FROM tbl_name;
+----------+------------+----------+--------------+-------------+-
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+----------+------------+----------+--------------+-------------+-
| tbl_name | 1 | name | 1 | name |
| tbl_name | 1 | name | 2 | address |
+----------+------------+----------+--------------+-------------+-
在某些情況下，可能會發現必須對列的前綴進行索引。例如，索引行的長度有一個最大上限，因此，如果索引列的長度超過了這個上限，那麼就可能需要利用前綴進行索引。在 MyISAM 表索引中，對 BLOB 或 TEXT 列也需要前綴索引。
對一個列的前綴進行索引限制了以後對該列的更改；不能在不刪除該索引並使用較短前綴的情況下，將該列縮短為一個長度小於索引所用前綴的長度的列。
總結
本節對索引的類型，已經如何創建索引做了介紹，其中涉及三個比較重要的SQL語句——ALTER TABLE、CREATE/DROP INDEX和CREATE TABLE，注意它們的用法。
索引最重要的功能是，通過使用索引加速表的檢索，有關這方面的知識，將在第十章數據庫優化中介紹。
思考題
1、建立一個如下所述的表：
data：FLOAT列，使用隨機函數填充數據
birth：DATETIME列，填充當前時間。
然後，請錄入幾條數據。最後計算data列的平均值、總和、極值，並且按照data列降序排序檢索值。
2、分別使用標准SQL模式和擴展正規表達式模式匹配，匹配上面創建的表，假設你創建表的當前日期為2001-01-01，用模式匹配檢索出birth列包含該日期的值。（實際上，上面的表中記錄都是同一日期錄入的，因此實際將返回全部記錄。）
3、為前幾章使用的數據表創建索引：
student：為id段創建一個PRIMARY索引，為english、chinese和history創建一個多列索引。
pet：為name和owner段創建一個多類索引。
4、刪除為pet表創建的索引。
