概念

索引是一種特殊的文件，包含著對(duì)數(shù)據(jù)表里所有記錄的引用指針。數(shù)據(jù)庫(kù)索引好比是一本書前面的目錄，能加快數(shù)據(jù)庫(kù)的查詢速度。

類型

（1）普通索引
（2）唯一索引
（3）全文索引（FULLTEXT）
（4）單列索引、多列索引
（5）組合索引（最左前綴）

普通索引

1、直接創(chuàng)建索引
語(yǔ)句：CREATE INDEX index_name ON table(column(length))
2、修改表結(jié)構(gòu)的方式添加索引
語(yǔ)句：ALTER TABLE table_name ADD INDEX index_name (column(length))
3、創(chuàng)建表的時(shí)候同時(shí)創(chuàng)建索引
語(yǔ)句：CREATE TABLE table_name (···，INDEX index_name (title(length)))
4、刪除索引
語(yǔ)句：DROP INDEX index_name ON table

唯一索引

索引列的值必須唯一，但允許有空值（注意和主鍵不同）。
1、創(chuàng)建唯一索引
語(yǔ)句：CREATE UNIQUE INDEX indexName ON table(column(length))
2、修改表結(jié)構(gòu)的方式添加唯一索引
語(yǔ)句：ALTER TABLE table_name ADD UNIQUE indexName (column(length))
3、創(chuàng)建表的時(shí)候添加唯一索引
語(yǔ)句：CREATE TABLE table_name (···，UNIQUE indexName (title(length)))

全文索引（FULLTEXT）

全文索引僅可用于MyISAM 表，可以從CHAR、VARCHAR或TEXT列中作為CREATE TABLE語(yǔ)句的一部分被創(chuàng)建，或是隨后使用ALTER TABLE 或CREATE INDEX被添加。對(duì)于大容量的數(shù)據(jù)表，生成全文索引是一個(gè)非常消耗時(shí)間非常消耗硬盤空間的做法。
1、創(chuàng)建全文索引
語(yǔ)句：CREATE FULLTEXT INDEX index_content ON tablename (content)
2、修改表結(jié)構(gòu)添加全文索引
語(yǔ)句：ALTER TABLE table_name ADD FULLTEXT index_content(content)
3、創(chuàng)建表時(shí)添加全文索引
語(yǔ)句：CREATE TABLE table_name (···，F(xiàn)ULLTEXT (content))

單列索引、多列索引

多個(gè)單列索引與單個(gè)多列索引的查詢效果不同，因?yàn)閳?zhí)行查詢時(shí)，MySQL只能使用一個(gè)索引，會(huì)從多個(gè)索引中選擇一個(gè)限制最為嚴(yán)格的索引。

組合索引（最左前綴）

平時(shí)用的SQL查詢語(yǔ)句一般都有比較多的限制條件，所以為了進(jìn)一步榨取MySQL的效率，就要建立一個(gè)組合索引：
語(yǔ)句：ALTER TABLE table_name ADD INDEX index_name (column1(length),column2(length))。
建立這樣的組合索引，其實(shí)是相當(dāng)于分別建立了下面兩組組合索引：
–(column1,column2)和–column1。并不是只要包含這兩列的查詢都會(huì)用到該組合索引；
例如：
使用到上面的索引
SELECT * FROM table_name WHERE column1=··· AND column2=···;
SELECT * FROM table_name WHERE column1=···;
不使用上面的索引
SELECT * FROM table_name WHERE column2=···;
SELECT * FROM table_name WHERE column1=··· OR column2=···;
！注：column1就是最左前綴，只有限制條件從column1開始篩選的索引才有效。

索引優(yōu)化

1、索引不會(huì)包含有NULL值的列
只要列中包含有NULL值都將不會(huì)被包含在索引中，組合索引中只要有一列含有NULL值，那么這一列對(duì)于此組合索引就是無(wú)效的。所以我們?cè)跀?shù)據(jù)庫(kù)設(shè)計(jì)時(shí)不要讓字段的默認(rèn)值為NULL。
2、使用短索引
對(duì)串列進(jìn)行索引，如果可能應(yīng)該指定一個(gè)前綴長(zhǎng)度。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作。
3、索引列排序
MySQL查詢只使用一個(gè)索引，因此如果where子句中已經(jīng)使用了索引的話，那么order by中的列是不會(huì)使用索引的。
4、like語(yǔ)句操作
like “%aaa%”不會(huì)使用索引而like “aaa%”可以使用索引。
5、不要在列上進(jìn)行運(yùn)算
這將導(dǎo)致索引失效而進(jìn)行全表掃描
注：MySQL只對(duì)以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些時(shí)候的like(不以通配符%或_開頭的情形)。

EXPLAIN

1、作用：用于分析索引查詢結(jié)果
2、用法：EXPLAIN sql語(yǔ)句
3、執(zhí)行結(jié)果各字段的含義：
（1）id：select查詢的序列號(hào)，包含一組數(shù)字，表示查詢中執(zhí)行select子句或操作表的順序。
三種情況：
id相同——執(zhí)行順序由上至下；
id不同——如果是子查詢，id的序號(hào)會(huì)遞增，id值越大優(yōu)先級(jí)越高，越先被執(zhí)行；
id相同、不同同時(shí)存在——id相同的是一組，組內(nèi)從上往下執(zhí)行，組外id值越大越先執(zhí)行。
（2）select_type
SIMPLE——簡(jiǎn)單查詢，查詢中不包含子查詢或者UNION
PRIMARY——查詢中若包含任何復(fù)雜的子部分，最外層查詢則被標(biāo)記為PRIMARY
SUBQUERY——在SELECT或WHERE中包含了子查詢
DERIVED——在FROM中包含的子查詢被標(biāo)記為DERIVED（衍生），MySQL會(huì)遞歸執(zhí)行這些子查詢，把結(jié)果放在臨時(shí)表中
UNION——若第二個(gè)SELECT出現(xiàn)在UNION之后，則被標(biāo)記為UNION：若UNION包含在FROM子句的子查詢中，外層SELECT將被標(biāo)記為：DERIVED
UNION RESULT——從UNION表獲取結(jié)果的SELECT
（3）table：當(dāng)前執(zhí)行的表
（4）type：連接操作的類型
system——表只有一行記錄（等于系統(tǒng)表），這是const類型的特例，平時(shí)不會(huì)出現(xiàn)，可以忽略不計(jì)
const——表示通過(guò)索引一次就找到了，const用于比較primary key 或者unique索引。因?yàn)橹黄ヅ湟恍袛?shù)據(jù)，所以很快。如將主鍵置于where列表中，MySQL就能將該查詢轉(zhuǎn)換為一個(gè)常量。
eq_ref——唯一性索引掃描，對(duì)于每個(gè)索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描
ref——非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行，本質(zhì)上也是一種索引訪問(wèn)，它返回所有匹配某個(gè)單獨(dú)值的行，然而，它可能會(huì)找到多個(gè)符合條件的行，所以它應(yīng)該屬于查找和掃描的混合體。
range——只檢索給定范圍的行，使用一個(gè)索引來(lái)選擇行，key列顯示使用了哪個(gè)索引，一般就是在你的where語(yǔ)句中出現(xiàn)between、< 、>、in等的查詢，這種范圍掃描索引比全表掃描要好，因?yàn)樗恍枰_始于索引的某一點(diǎn)，而結(jié)束于另一點(diǎn)，不用掃描全部索引。
index——Full Index Scan，Index與All區(qū)別為index類型只遍歷索引樹。這通常比ALL快，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小。（也就是說(shuō)雖然all和Index都是讀全表，但index是從索引中讀取的，而all是從硬盤讀取的）
all——Full Table Scan 將遍歷全表以找到匹配的行。
！類型從好到查：system > const > eq_ref > ref > range > index > all
（5）possible_keys：顯示可能應(yīng)用在這張表中的索引，一個(gè)或多個(gè)。查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實(shí)際使用。
（6）key：實(shí)際使用的索引，如果為NULL，則沒(méi)有使用索引。查詢中若使用了覆蓋索引（select 后要查詢的字段剛好和創(chuàng)建的索引字段完全相同），則該索引僅出現(xiàn)在key列表中。
（7）key_len：表示索引中使用的字節(jié)數(shù)，可通過(guò)該列計(jì)算查詢中使用的索引的長(zhǎng)度，在不損失精確性的情況下，長(zhǎng)度越短越好。key_len顯示的值為索引字段的最大可能長(zhǎng)度，并非實(shí)際使用長(zhǎng)度，即key_len是根據(jù)表定義計(jì)算而得，不是通過(guò)表內(nèi)檢索出的。
（8）ref：顯示的是列的名字（或單詞“const”），MySQL將根據(jù)這些列來(lái)選擇行。哪些列或常量被用于查找索引列上的值。
（9）rows：根據(jù)表統(tǒng)計(jì)信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數(shù)，也就是說(shuō)，用的越少越好。
（10）Extra：包含不適合在其他列中顯示但十分重要的額外信息。
Using filesort——說(shuō)明mysql會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取。MySQL中無(wú)法利用索引完成的排序操作稱為“文件排序”；
Using temporary——使用了用臨時(shí)表保存中間結(jié)果，MySQL在對(duì)查詢結(jié)果排序時(shí)使用臨時(shí)表。常見于排序order by和分組查詢group by；
Using index——表示相應(yīng)的select操作中使用了覆蓋索引（Covering Index），避免訪問(wèn)了表的數(shù)據(jù)行，效率不錯(cuò)；
！注：如果同時(shí)出現(xiàn)using where，表明索引被用來(lái)執(zhí)行索引鍵值的查找；如果沒(méi)有同時(shí)出現(xiàn)using where，表明索引用來(lái)讀取數(shù)據(jù)而非執(zhí)行查找動(dòng)作。
Using where——表明使用了where過(guò)濾；
Using join buffer——表明使用了連接緩存，比如說(shuō)在查詢的時(shí)候，多表join的次數(shù)非常多，那么將配置文件中的緩沖區(qū)的join buffer調(diào)大一些；
impossible where——where子句的值總是false，不能用來(lái)獲取任何元組；
select tables optimized away——在沒(méi)有GROUP BY子句的情況下，基于索引優(yōu)化MIN/MAX操作或者對(duì)于MyISAM存儲(chǔ)引擎優(yōu)化COUNT(*)操作，不必等到執(zhí)行階段再進(jìn)行計(jì)算，查詢執(zhí)行計(jì)劃生成的階段即完成優(yōu)化；
distinct——優(yōu)化distinct操作，在找到第一匹配的元組后即停止找同樣值的動(dòng)作。