MySQL索引的分類(lèi)、何時(shí)使用、何時(shí)不使用、何時(shí)失效？

1、分類(lèi)

MySQL索引分為普通索引、唯一索引、主鍵索引、組合索引、全文索引。索引不會(huì)包含有null值的列，索引項(xiàng)可以為null（唯一索引、組合索引等），但是只要列中有null值就不會(huì)被包含在索引中。

（1）普通索引： create index index_name on table(column)；

或者創(chuàng)建表時(shí)指定，create table(..., index index_name column);

（2）唯一索引： 類(lèi)似普通索引，索引列的值必須唯一（可以為空，這點(diǎn)和主鍵索引不同）

create unique index index_name on table(column)；或者創(chuàng)建表時(shí)指定unique index_name column

（3）主鍵索引： 特殊的唯一索引，不允許為空，只能有一個(gè)，一般是在建表時(shí)指定primary key(column)

（4）組合索引： 在多個(gè)字段上創(chuàng)建索引，遵循最左前綴原則。alter table t add index index_name(a,b,c);

（5）全文索引： 主要用來(lái)查找文本中的關(guān)鍵字，不是直接與索引中的值相比較，像是一個(gè)搜索引擎，配合match against使用，現(xiàn)在只有char，varchar，text上可以創(chuàng)建全文索引。

在數(shù)據(jù)量較大時(shí)，先將數(shù)據(jù)放在一張沒(méi)有全文索引的表里，然后再利用create index創(chuàng)建全文索引，比先生成全文索引再插入數(shù)據(jù)快很多。

2、何時(shí)使用索引

MySQL每次查詢(xún)只使用一個(gè)索引。與其說(shuō)是“數(shù)據(jù)庫(kù)查詢(xún)只能用到一個(gè)索引”，倒不如說(shuō)，和全表掃描比起來(lái)，去分析兩個(gè)索引B+樹(shù)更加耗費(fèi)時(shí)間。所以where A=a and B=b這種查詢(xún)使用（A，B）的組合索引最佳，B+樹(shù)根據(jù)（A，B）來(lái)排序。

1. 主鍵，unique字段；
2. 和其他表做連接的字段需要加索引；
3. 在where里使用＞，≥，＝，＜，≤，is null和between等字段；
4. 使用不以通配符開(kāi)始的like，where A like 'China%'；
5. 聚集函數(shù)MIN()，MAX()中的字段；
6. order by和group by字段；

3、何時(shí)不使用索引

1. 表記錄太少；
2. 數(shù)據(jù)重復(fù)且分布平均的字段（只有很少數(shù)據(jù)值的列）；
3. 經(jīng)常插入、刪除、修改的表要減少索引；
4. text，image等類(lèi)型不應(yīng)該建立索引，這些列的數(shù)據(jù)量大（假如text前10個(gè)字符唯一，也可以對(duì)text前10個(gè)字符建立索引）；
5. MySQL能估計(jì)出全表掃描比使用索引更快時(shí)，不使用索引；

4、索引何時(shí)失效

1. 組合索引未使用最左前綴，例如組合索引（A，B），where B=b不會(huì)使用索引；
2. like未使用最左前綴，where A like '%China'；
3. 搜索一個(gè)索引而在另一個(gè)索引上做order by，where A=a order by B，只使用A上的索引，因?yàn)椴樵?xún)只使用一個(gè)索引 ；
4. or會(huì)使索引失效。如果查詢(xún)字段相同，也可以使用索引。例如where A=a1 or A=a2（生效），where A=a or B=b（失效）
5. 如果列類(lèi)型是字符串，要使用引號(hào)。例如where A='China'，否則索引失效（會(huì)進(jìn)行類(lèi)型轉(zhuǎn)換）；
6. 在索引列上的操作，函數(shù)（upper()等）、or、！=(<>)、not in等；

5、explain語(yǔ)句

type字段為All，未使用索引；為ref，使用索引

• ALL： 全表掃描
• index： 索引全掃描
• range： 索引范圍掃描，常用語(yǔ)<,<=,>=,between等操作
• ref： 使用非唯一索引掃描或唯一索引前綴掃描，返回單條記錄，常出現(xiàn)在關(guān)聯(lián)查詢(xún)中
• eq_ref： 類(lèi)似ref，區(qū)別在于使用的是唯一索引，使用主鍵的關(guān)聯(lián)查詢(xún)
• const/system： 單條記錄，系統(tǒng)會(huì)把匹配行中的其他列作為常數(shù)處理，如主鍵或唯一索引查詢(xún)
• null： MySQL不訪問(wèn)任何表或索引，直接返回結(jié)果

還有key字段表示用到的索引，沒(méi)有用到為null