什么是索引

• 索引是對(duì) 數(shù)據(jù)庫(kù)中一列或者多列的值進(jìn)行排序的一中結(jié)構(gòu)，使用索引可以快速訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)中表的特定信息。索引的一個(gè)主要的目的就是加快檢索表中數(shù)據(jù)，亦即能協(xié)助信息搜索者盡快的找到符合限制條件的記錄的輔助數(shù)據(jù)結(jié)構(gòu)。
• 簡(jiǎn)單來(lái)說(shuō)索引就是數(shù)據(jù)庫(kù)的目錄。

索引有什么作用

索引的最大作用就是加快數(shù)據(jù)庫(kù)的查詢(xún)速度。

索引為什么會(huì)加快查詢(xún)速度

數(shù)據(jù)庫(kù)在執(zhí)行一條SQL語(yǔ)句的時(shí)候，默認(rèn)的方式是根據(jù)搜索條件進(jìn)行全表掃描，遇到匹配條件的就加入搜索結(jié)果集合。但若是遇到大數(shù)據(jù)量的查詢(xún)時(shí)，直接全表匹配的方式太慢了，這時(shí)候就需要用到索引。我們對(duì)某一字段增加索引，查詢(xún)的時(shí)候就會(huì)先去索引列表中一次定位到特定值得行數(shù)，大大減少遍歷匹配的行數(shù)，所以可以明顯的增加查詢(xún)的速度。

索引的種類(lèi)

• 主鍵索引：數(shù)據(jù)記錄里面不能有null，數(shù)據(jù)內(nèi)容不能重復(fù)，在一張表里面不能有多個(gè)主鍵索引。
• 普通索引：使用字段關(guān)鍵字建立的索引，主要是提高查詢(xún)速度。
• 唯一索引：字段數(shù)據(jù)是唯一的，數(shù)據(jù)內(nèi)容里面能否為null，在一張表里面，是可以添加多個(gè)唯一索引。
• 全文索引：在早起版本中只有myisam引擎支持全文索引，在innodb5.6后也支持全文索引，在MySQL中全文索引不支持中文。我們一般使用sphinx集合coreseek來(lái)實(shí)現(xiàn)中文的全文索引。

索引的創(chuàng)建(索引的例子)

執(zhí)行Create Table語(yǔ)句時(shí)可以創(chuàng)建索引，也可以單獨(dú)用Create index或者 Alter Table來(lái)為表增加索引。

1. ALTER TABLE

ALTER TABLE用來(lái)創(chuàng)建普通索引、unique索引或者primary key索引。

ALTER TABLE table_name ADD INDEX index_name(column_list)  ALTER TABLE table_name ADD UNIQUE(column_list)ALTER TABLE table_name ADD PRIMARY KEY(column_list)

• table_name:是要增加索引的表名。
• column_list:指出對(duì)哪些列進(jìn)行索引，多列時(shí)各列之間用逗號(hào)分隔。
• index_name:可選，缺省是，MySQL將根據(jù)第一個(gè)索引列賦一個(gè)名稱(chēng)。
• ALTER TABLE允許在單個(gè)語(yǔ)句中更改多個(gè)表，因此可以在同時(shí)創(chuàng)建多個(gè)索引。

2. CREATE INDEX

CREATE INDEX可以創(chuàng)建普通索引和UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list)CREATE UNIQUE INDEX index_name ON table_name (column_list)

注： table_name 索引名不可選。

刪除索引

可利用ALTER TABLE或DROP INDEX語(yǔ)句來(lái)刪除索引。類(lèi)似于CREATE INDEX語(yǔ)句，DROP INDEX可以在ALTER TABLE內(nèi)部作為一條語(yǔ)句處理，語(yǔ)法如下。

DROP INDEX index_name ON talbe_nameALTER TABLE table_name DROP INDEX index_nameALTER TABLE table_name DROP PRIMARY KEY

• 前兩條語(yǔ)句是等價(jià)的，刪除掉table_name中的索引index_name。
• 第3條語(yǔ)句只在刪除PRIMARY KEY索引時(shí)使用，因?yàn)橐粋€(gè)表只可能有一個(gè)PRIMARY KEY索引，因此不需要指定索引名。如果沒(méi)有創(chuàng)建PRIMARY KEY索引，但表具有一個(gè)或多個(gè)UNIQUE索引，則MySQL將刪除第一個(gè)UNIQUE索引。
• 如果從表中刪除了某列，則索引會(huì)受到影響。對(duì)于多列組合的索引，如果刪除其中的某列，則該列也會(huì)從索引中刪除。如果刪除組成索引的所有列，則整個(gè)索引將被刪除。

查看索引

show index from tblname;show keys from tblname;

查詢(xún)結(jié)果各字段解釋

MySQL中的索引

MyISAM索引

MyISAM引擎使用B+Tree 作為索引結(jié)構(gòu)，葉節(jié)點(diǎn)的data域存放的是數(shù)據(jù)記錄的地址。

• MyISAM的索引文件僅僅保存數(shù)據(jù)記錄的地址。
• MyISAM中，主索引和輔助索引在結(jié)構(gòu)上沒(méi)有任何區(qū)別，只是主索引的key要求是唯一的。而輔助索引的key值可以是重復(fù)的。
  如果在Col2上建立一個(gè)輔助索引，則此索引的結(jié)構(gòu)如下。
  
  

如圖，此索引同樣是一顆B+ 樹(shù)，data域保存數(shù)據(jù)記錄的地址,因此，MyISAM 中索引檢索的算法為首先按照B+樹(shù)搜素算法搜素索引，如果指定的Key存在，則取出其data域中的值，然后以data域中的值為地址，讀取相應(yīng)數(shù)據(jù)記錄。

MyISAM的索引方式也叫做”非聚集“ 的，之所以這么稱(chēng)呼是為了與INNODB的聚集索引區(qū)分。

InnoDB索引實(shí)現(xiàn)

InnoDB的索引也是使用B+Tree作為索引結(jié)構(gòu)，但是具體的實(shí)現(xiàn)方式與MyISAM截然不同。
區(qū)別：

1. InnoDB的數(shù)據(jù)文件本身就是索引文件。從上文知道，MyISAM索引文件和數(shù)據(jù)文件是分離的，索引文件僅保存數(shù)據(jù)記錄的地址。而在InnoDB 中，表數(shù)據(jù)文件本身就是按B+樹(shù)組織的一個(gè)索引結(jié)構(gòu)，這棵樹(shù)的葉節(jié)點(diǎn)data域保存了完整的數(shù)據(jù)記錄。這個(gè)索引的key是數(shù)據(jù)表的主鍵，因此InnoDB表數(shù)據(jù)文件本身就是主索引。
   
   

可以看到葉節(jié)點(diǎn)包含了完整的數(shù)據(jù)記錄。這種索引叫做聚集索引。因?yàn)镮nnoDB的數(shù)據(jù)文件本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒(méi)有），如果沒(méi)有顯式指定，則MySQL系統(tǒng)會(huì)自動(dòng)選擇一個(gè)可以唯一標(biāo)識(shí)數(shù)據(jù)記錄的列作為主鍵，如果不存在這種列，則MySQL自動(dòng)為InnoDB表生成一個(gè)隱含字段作為主鍵，這個(gè)字段長(zhǎng)度為6個(gè)字節(jié)，類(lèi)型為長(zhǎng)整形。

2. InnoDB的輔助索引data域存儲(chǔ)相應(yīng)記錄主鍵的值而不是地址。換句話(huà)說(shuō)，InnoDB的所有輔助索引都引用主鍵作為data域。
   如圖在Col3上的一個(gè)輔助索引：
   
   

聚集索引這種實(shí)現(xiàn)方式使得按主鍵的搜索十分高效，但是輔助索引搜索需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄。

為什么說(shuō)不建議使用過(guò)長(zhǎng)的字段作為主鍵？

答：因?yàn)樗休o助索引都引用主索引，過(guò)長(zhǎng)的主索引會(huì)令輔助索引變得過(guò)大。

用非單調(diào)的字段作為主鍵在InnoDB中不是個(gè)好主意

InnoDB數(shù)據(jù)文件本身是一顆B+Tree，非單調(diào)的主鍵會(huì)造成在插入新記錄時(shí)數(shù)據(jù)文件為了維持B+Tree的特性而頻繁的分裂調(diào)整，十分低效，而使用自增字段作為主鍵則是一個(gè)很好的選擇。