什麼是索引
索引是對資料庫表中一列或者多列的值進行排序的一種結構，所引用於快速找出在某個列中有一特定值的行。不使用索引，MySQL必須從第一條記錄開始讀完整個表，直到找出相關的行。表越大，查詢資料所花費的時間越多，如果表中查詢的列有一個索引，MySQL能快速到達一個位置去搜尋資料檔案，而不必檢視所有資料。
索引的含義和特點
索引是一個單獨的、儲存在磁碟上的資料庫結構，它們包含著對資料表裡所有記錄的引用指標。使用索引用於快速找出在某個或多個列中有一特定值的行，所有MySQL列型別都可以被索引，對相關列使用索引是提高查詢操作速度的最佳途徑。
例如，資料庫裡面有20000條記錄，現在要執行這麼一個查詢：SELECT * FROM table where num = 10000。如果沒有索引，必須遍歷整個表，直到num等於10000的這一行被找到為止；如果在num列上建立索引，MySQL不需要任何掃描，直接在索引中找10000，就可以得知值這一行的位置。可見，索引的建立可以提高資料庫的查詢速度。
索引是在儲存引擎中實現的，因此，每種儲存引擎的索引都不一定完全相同，並且每種儲存引擎也不一定支援所有索引型別。所有儲存引擎支援每個表至少16個索引，總索引長度至少為256位元組。大多數儲存引擎有更高的額限制，MySQL中索引的儲存型別有兩種：BTREE和HASH，具體和表的儲存引擎相關；MyISAM和InnoDB儲存引擎只支援BTREE索引，MEMORY/HEAP儲存引擎可以支援HASH和BTREE縮影。
索引的優點主要有：
1、通過建立唯一索引，可以保證資料庫表中每一行資料的唯一性
2、可以大大加快資料的查詢速度，這也是建立索引最主要的原因
3、在實現資料的參考完整性方面，可以加速表和表之間的連線
4、在使用分組和排序子句進行資料查詢時，也可以顯著減少查詢中分組和排序的時間
增加索引也有許多不利的方面，比如：
1、建立索引和維護索引要耗費時間，並且隨著資料量的增加所耗費的時間也會增加
2、索引需要佔用磁碟空間，除了資料表佔資料空間之外，每一個索引還要佔一定的物理空間，如果有大量的索引，索引檔案可能比資料檔案更快達到最大檔案尺寸
3、當對錶中資料進行增加、刪除和修改的時候，索引也要動態地維護，這樣就降低了資料的維護速度
索引的分類
MySQL的索引可以分為以下幾類：
1、普通索引和唯一索引
（1）普通索引是MySQL中的基本索引型別，允許在定義索引的列中插入重複值和空值
（2）唯一索引，索引列的值必須唯一，但允許有空值，主鍵索引是一種特殊的唯一索引，不允許有空值
2、單列索引和組合索引
（1）單列索引即一個索引只包含單個列，一個表可以有多個單列索引
（2）組合索引指在表的多個欄位組合上建立的索引，只有在查詢條件中使用了這些欄位的左邊欄位時，索引才會被使用
3、全文索引
全文索引型別為FULLTEXT，在定義索引的列上支援值的全文查詢，允許在這些索引列中插入重複值和空值。全文索引可以在CHAR、VARCHAR或者TEXT型別的列上建立，MySQL中只有MyISAM儲存引擎支援全文索引
索引的設計原則
索引設計不合理或者缺少索引都會對資料庫和應用程式的效能造成障礙，高效的索引對於獲得良好的效能非常重要，設計索引時，應該考慮一下：
1、索引並非越多越好，一個表中如有大量的索引，不僅佔用磁碟空間，而且會影響INSERT、DELETE、UPDATE等語句的效能，因為當表中的資料更改的同時，索引也會進行調整和更新
2、避免對經常更新的表設計過多的索引，並且索引中的列儘可能要少，而對經常用於查詢的欄位應該建立索引，但要避免新增不必要的欄位
3、資料量小的表最好不要使用索引，由於資料較少，查詢花費的時間可能比遍歷索引時間還要短，索引可能不會產生優化效果
4、在條件表示式中經常用到的不同值較多的列上建立索引，在不同值較少的列上不要建立索引，比如性別欄位只有男和女，就沒必要建立索引。如果建立索引不但不會提高查詢效率，反而會嚴重降低更新速度
5、當唯一性是某種資料本身的特徵時，指定唯一索引。使用唯一索引需能確保定義的列的資料完整性，以提高查詢速度
6、在頻繁排序或分組（即group by或order by操作）的列上建立索引，如果待排序的列有多個，可以在這些列上建立組合索引
建立表的時候建立索引
使用CREATE TABLE建立表的時候，除了可以定義列的資料型別，還可以定義主鍵約束、外來鍵約束或者唯一性約束，而不論建立哪種約束，在定義約束的同時相當於在指定列上建立了一個索引。建立表時建立索引的基本語法如下：
CREATE TABLE table_name[col_name data_type] [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name](col_name[length]) [ASC|DESC]
解釋一下：
1、UNIQUE、FULLTEXT和SPATIAL為可選引數，分別表示唯一索引、全文索引和空間索引
2、INDEX和KEY為同義詞，二者作用相同，用來指定建立索引
3、col_name為需要建立索引的欄位列，該列必須從資料表中該定義的多個列中選擇
4、index_name為指定索引的名稱，為可選引數，如果不指定則MySQL預設col_name為索引值
5、length為可選引數，表示索引的長度，只有字串型別的欄位才能指定索引長度
6、ASC或DESC指定升序或者降序的索引值儲存
下面建立一個普通索引，沒有唯一性之類的限制，其作用只是加快對於資料的訪問速度：
CREATE TABLE book ( bookId INT NOT NULL, bookName VARCHAR(255) NOT NULL, author VARCHAR(255) NOT NULL, info VARCHAR(255) NOT NULL, year_publication YEAR NOT NULL, INDEX(year_publication) )
確認一下索引是否正在使用，可以使用EXPLAIN：
EXPLAIN select * from book where yead_publication = 1990
結果為：
解釋下欄位的意思：
1、select_type行指定所使用的SELECT查詢型別，這裡值為SIMPLE，表示簡單的SELECT，不使用UNION或者子查詢。其他可能的取值有：PRIMARY、UNION、SUBQUERY等
2、table行指定資料庫讀取的資料表的名字，它們按照被讀取的先後順序排列
3、type行指定了本資料表與其他資料表之間的關聯關係，可能的去只有system、const、eq_ref、ref、range、index和All
4、possible_keys行給出了MySQL在搜尋資料記錄時可選用的各個索引
5、key行是MySQL使用的實際索引
6、key_len行給出了索引按位元組計算的長度，key_len數值越小，表示越快
7、ref行給出了關聯關係中另外一個資料表裡的資料列的名字
8、rows行是MySQL在執行這個查詢時預計會從這個資料表裡讀出的資料行的個數
9、extra行提供了與關聯操作有關的資訊
看到，possible_keys和key的值都為year_publication，查詢時使用了索引
2、建立唯一索引
唯一索引和普通索引類似，不過唯一索引索引列的值必須唯一，但允許有空值，如果是組合索引，則列值的組合必須唯一。看一下建立唯一索引的方式：
CREATE TABLE uniquetable ( id INT NOT NULL, name CHAR(30) NOT NULL, UNIQUE INDEX UniqIdx(id) )
這就在表的id欄位上建立了一個名為UniqIdx的唯一索引
3、建立單列索引
單列索引是在資料表中的某一個欄位上建立的索引，一個表中可以建立多個單列索引，前面兩個例子中建立的索引都是單列索引，比如：
CREATE TABLE singletable ( id INT NOT NULL, name CHAR(30) NOT NULL, UNIQUE INDEX SingleIdx(name(20)) )
這就在name欄位上建立了一個名為SingleIdx的單列索引，索引長度為20
4、建立組合索引
組合索引是在多個欄位上建立一個索引，比如：
create table uniontable ( id INT NOT NULL, name CHAR(30) NOT NULL, age INT NOT NULL, info VARCHAR(255), INDEX UnionIdx(id, name, age) )
這就為id、name和age三個欄位成功建立了一個名為UnionIdx的組合索引
5、建立全文索引
全文索引可以對全文進行搜尋，只有MyISAM儲存引擎支援全文索引，並且只為CHAR、VARCHAR和TEXT列，索引總是對整個列進行，不支援區域性索引，比如：
CREATE TABLE fulltexttable ( id INT NOT NULL, name CHAR(30) NOT NULL, age INT NOT NULL, info VARCHAR(255), FULLTEXT INDEX FullTxtIdx(info) )ENGINE=MyISAM
因為預設的儲存引擎為InnoDB，而全文索引只支援MyISAM，所以這裡建立表的時候要手動指定一下引擎。
看到這麼建立，就在info欄位上成功建立了一個名為FullTxtIdx的FULLTEXT全文索引，全文索引非常適合大型資料庫，而對於小的資料集，它的用處可能比較小
在已經存在的表上建立索引
在已經存在的表上建立索引，可以使用ALTER TABLE語句或者CREATE INDEX語句，所以，分別講解一下如何使用ALTER TABLE和CREATE INDEX語句在已知的表欄位上建立索引。
1、使用ALTER TABLE語句建立索引
ALTER TABLE建立索引的基本語法為：
ALTER TABLE table_name ADD [UNIQUE|FUUTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]
與建立表時建立索引的語法不同的是，這裡用了ALTER TABLE和ADD關鍵字，ADD表示向表中新增索引。以book這張表為例，先看一下這張表裡面有哪些索引：
SHOW INDEX FROM book
看下結果：
解釋一下：
1、table表示建立索引的表
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裡面已經有一個索引了，是一個非唯一索引，現在給bookname欄位加上索引，SQL語句如下：
ALTER TABLE book ADD INDEX BoNameIdx(bookname(30));
再給bookId欄位加上唯一索引，名稱為UniqidIdx：
ALTER TABLE book ADD UNIQUE INDEX UniqidIdx(bookId);
再給author欄位加上單列索引：
ALTER TABLE book ADD INDEX BkauthorIdx(author(50));
意思是查詢的時候，只需要檢索前面50個字元。這裡專門提一下，對字串型別的欄位進行索引，如果可能應該指定一個字首長度，例如，一個CHAR(255)的列，如果在前10個或者前30個字元內，多數值是唯一的，則不需要對整個列進行索引，短索引不僅可以提高查詢速度而且可以節省磁碟空間、減少I/O操作
組合索引和全文索引和建立表時建立索引的方式差不多，就不寫了，此時我們SHOW一下INDEX：
2、使用CREATE INDEX語句建立索引
CREATE INDEX語句可以在已經存在的表上新增索引，MySQL中CREATE INDEX被對映到一個ALTER TABLE語句上，基本語法結構為：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(col_name[length],...)[ASC|DESC]
看到和ALTER INDEX語句的語法基本一樣，下面把book表刪除了再建立，所有欄位都沒有索引，用CREATE INDEX語句建立一次索引：
-- 為bookname欄位建立名為BkNameIdx的普通索引 CREATE INDEX BkNameIdx ON book(bookname); -- 為bookid欄位建立名為UniqidIdx的唯一索引 CREATE INDEX UniqidIdx ON book(bookid); -- 為author和info欄位建立名為BkAuAndInfoIdx的組合索引 CREATE INDEX BkAuAndInfoIdx ON book(author(20), info(50)); -- 為year_publication欄位建立名為BkyearIdx的普通索引 CREATE INDEX BkyearIdx ON book(year_publication);
此時我們SHOW一下INDEX，可以看到為5個欄位建立了4個索引：
刪除索引
最後一項工作就是刪除索引了，可以使用ALTER TABLE和DROP INDEX刪除索引。
1、ALTER TABLE
ALTER TABLE的基本語法為：
ALTER TABLE table_name DROP INDEX index_name
比如把book的UniqidIdx給刪除了：
ALTER TABLE book DROP INDEX UniqidIdx;
這樣就刪除了book表中的UniqidIdx這個索引，可以SHOW INDEX from book檢視一下，這裡就不貼圖了
2、DROP INDEX
DROP INDEX的基本語法為：
DROP INDEX index_name ON table_name
比如我把BkAuAndInfoIdx這個組合索引給刪了：
DROP INDEX BkAuAndInfoIdx ON book
這樣就把book表裡面的BkAuAndInfoIdx這個組合索引給刪除了。
注意一個細節，刪除表中的列時，如果要刪除的列為整個索引的組成部分，則該列也會從索引中刪除；如果組成索引的所有列都被刪除，則整個索引將被刪除