MySQL的索引
1、索引的基本介紹
MySQL 官方對索引的定義為：索引（Index）是幫助 MySQL 高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。可以得到索引的本質(zhì)： 索引是數(shù)據(jù)結(jié)構(gòu)。可以簡單理解為排好序的快速查找數(shù)據(jù)結(jié)構(gòu)。
在數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)， 這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實現(xiàn)高級查找算法。這種數(shù)據(jù)結(jié)構(gòu)，就是索引。
下圖就是一種可能的索引方式示例：
左邊是數(shù)據(jù)表，一共有兩列七條記錄，最左邊的是數(shù)據(jù)記錄的物理地址。為了加快 Col2 的查找，可以維護一個 右邊所示的二叉查找樹，每個節(jié)點分別包含索引鍵值和一個指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指 針，這樣就可以運用 二叉查找在一定的復(fù)雜度內(nèi)獲取到相應(yīng)數(shù)據(jù)，從而快速的檢索出符合條件的記錄。
一般來說索引本身也很大，不可能全部存儲在內(nèi)存中，因此索引往往以索引文件的形式存儲的磁盤上。
我們平常所說的索引，如果沒有特別指明，都是指 B 樹（多路搜索樹，并不一定是二叉的）結(jié)構(gòu)組織的索引。其中聚集索引、次要索引、覆蓋索引、復(fù)合索引、前綴索引、唯一索引默認都是使用 B+ 樹索引，統(tǒng)稱索引。當(dāng)然，除了 B+ 樹這種類型的索引之外，還有哈希索引（hash index）等。
索引分單列索引（主鍵索引、唯一索引、普通索引）和組合索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個組合索引包含兩個或兩個以上的列。
創(chuàng)建索引時，你需要確保該索引是應(yīng)用在 SQL 查詢語句的條件中(一般作為 WHERE 子句的條件)。實際上，索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄。
但過多的使用索引將會造成濫用，因為索引也會有它的缺點：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件。建立索引會占用磁盤空間的索引文件。
1.1、索引的好處
MySQL索引的建立對于MySQL的高效運行是很重要的，索引可以大大提高MySQL的檢索速度。
使用索引的好處：
- 建立索引可以大大提高檢索的數(shù)據(jù)，以及減少表的檢索行數(shù)
- 在分組和排序字句進行數(shù)據(jù)檢索，可以減少查詢時間中分組和排序時所消耗的時間（數(shù)據(jù)庫的記錄會重新排序）
- 可以通過建立唯一索引或者主鍵索引，保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性.
- 在表連接的連接條件可以加速表與表直接的相連
1.2、索引的缺點
索引的缺點如下：
- 索引文件會占用物理空間，除了數(shù)據(jù)表需要占用物理空間之外，每一個索引還會占用一定的物理空間。實際上索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄，所以索引列也是要占用空間 的。
- 建立索引會占用磁盤空間的索引文件。一般情況這個問題不太嚴(yán)重，但如果你在一個大表上創(chuàng)建了多種組合索引，索引文件的會膨脹很快。
- 當(dāng)對表的數(shù)據(jù)進行 INSERT、UPDATE、DELETE 的時候，索引也要動態(tài)的維護，這樣就會降低數(shù)據(jù)的維護速度。
1.3、在什么情況下需要建索引
在建立索引的時候應(yīng)該考慮索引應(yīng)該建立在數(shù)據(jù)庫表中的某些列上面，哪一些索引需要建立，哪一些所以是多余的。
適合創(chuàng)建索引的情況：
- 主鍵會自動建立唯一索引，無需我們再手動建。
- 頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引。一般來說，在經(jīng)常需要搜索的列上，可以加快索引的速度。
- 查詢中與其它表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引。在表與表的而連接條件上加上索引，可以加快連接查詢的速度。
- 單鍵/組合索引的選擇問題， 組合索引性價比更高。
- 查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度。在經(jīng)常需要排序(order by)、分組(group by)和distinct 的列上加索引，可以加快排序查詢的時間, (單獨order by 用不了索引，索引考慮加where 或加limit)
- 查詢中統(tǒng)計或者分組字段。
- 在一些where 之后的 < <= > >= BETWEEN IN 以及某個情況下的like 建立字段的索引(B-TREE)
- 使用短索引。如果你的一個字段是Char(32)或者int(32)，在創(chuàng)建索引的時候可以指定前綴長度，比如前10個字符 (前提是多數(shù)值是唯一的..)，那么短索引可以提高查詢速度，并且可以減少磁盤的空間，也可以減少I/0操作。
- 選擇越小的數(shù)據(jù)類型越好,因為通常越小的數(shù)據(jù)類型通常在磁盤,內(nèi)存,cpu,緩存中 占用的空間很少,處理起來更快
不適合創(chuàng)建索引的情況：
- 表記錄太少
- 經(jīng)常增刪改的表或者字段
- Where 條件里用不到的字段不創(chuàng)建索引
- 數(shù)據(jù)重復(fù)且分布平均的表字段不適合建索引。如果某個數(shù)據(jù)列包含許多重復(fù)的內(nèi)容，為它建立索引就沒有太大的實際效果
其他的一些情況說明：
- 不要在列上進行運算，這樣會使得mysql索引失效，也會進行全表掃描。
- like語句的 如果你對nickname字段建立了一個索引.當(dāng)查詢的時候的語句是 nickname lick '%ABC%' 那么這個索引講不會起到作用.而nickname lick 'ABC%' 那么將可以用到索引。
- 索引不會包含NULL列,如果列中包含NULL值都將不會被包含在索引中,復(fù)合索引中如果有一列含有NULL值那么這個組合索引都將失效,一般需要給默認值0或者 ' '字符串
2、創(chuàng)建索引
2.1、單列索引
2.1.1、普通索引
這個是最基本的索引，它沒有任何限制。創(chuàng)建命令如下：
-- 直接創(chuàng)建索引 CREATE INDEX index_name ON table_name (column_name); -- 修改表結(jié)構(gòu)的方式來添加索引 ALTER TABLE table_name ADD INDEX index_name (column_name); -- 示例 CREATE INDEX idx_user_name ON user (name); ALTER TABLE user ADD INDEX idx_user_name (name);
對于CHAR和VARCHAR列，只用一列的一部分就可創(chuàng)建索引。創(chuàng)建索引時，使用 column_name(length) 語法，對列的前 length 個字符編制索引。BLOB和TEXT列也可以編制索引，但是必須給出前綴長度。
-- 只用一列的一部分創(chuàng)建索引 CREATE INDEX index_name ON table_name (column_name(length));
如果是CHAR，VARCHAR類型，length可以小于字段實際長度；如果是BLOB和TEXT類型，必須指定 length。
如果某列的前 n 個字符基本都不同，那么使用該列的前 n 個字符來創(chuàng)建索引也不會比使用列的全名創(chuàng)建的索引速度慢很多。另外，使用列的一部分創(chuàng)建索引可以使索引文件大大減小，從而節(jié)省了大量的磁盤空間，有可能提高INSERT操作的速度。
2.1.2、唯一索引
它與前面的普通索引類似，不同的就是：索引列的值必須唯一，這一點和主鍵索引是一樣的，但唯一索引允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創(chuàng)建方式：
-- 直接創(chuàng)建索引 CREATE UNIQUE INDEX index_name ON table_name (column_name); -- 修改表結(jié)構(gòu)的方式來添加索引 ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_name);
當(dāng)給表創(chuàng)建或設(shè)置主鍵的時候，mysql 會自動添加一個與主鍵對應(yīng)的唯一索引，所不需要對主鍵再做額外的添加索引的操作。
數(shù)據(jù)庫主鍵和索引的區(qū)別與聯(lián)系：
- 主鍵是一定是唯一性索引，但唯一性索引不一定是主鍵。主鍵就是能夠唯一標(biāo)識表中某一行的屬性或者是屬性組，一個表只能有一個主鍵。數(shù)據(jù)庫管理系統(tǒng)對于主鍵自動生成唯一索引，所以主鍵也是一個特殊的索引。
- 唯一索引標(biāo)識索引值唯一，一個表可以有多個唯一索引，但主鍵只能有一個。
- 主鍵列不能為空，但唯一索引列可以為空。
- 一張表只能有一個主鍵，但可以有多個索引。
- 通俗舉例來說：主鍵相當(dāng)于一本書的頁碼，索引相當(dāng)于書的目錄。
2.1.3、主鍵索引
主鍵索引是一種特殊的唯一索引，不允許有空值。一般是在建表的時候指定了主鍵，就會自動創(chuàng)建主鍵索引。CREATE INDEX不能用來創(chuàng)建主鍵索引，而是應(yīng)該使用 ALTER TABLE。
-- 實際上創(chuàng)建主鍵就是創(chuàng)建了主鍵索引 ALTER TABLE table_name ADD PRIMARY KEY (column_name);
2.2、組合索引（復(fù)合索引）
組合索引也就是一個索引包含多個字段。
創(chuàng)建命令如下：
CREATE INDEX index_name ON table_name (column1, column2, column3...); -- 示例 CREATE INDEX idx_user_name_idcard_email ON user (name, idcard, email);
3、刪除索引
DROP INDEX [indexName] ON table_name;
4、查看索引
SHOW INDEX [indexName] FROM table_name;
示例：
show index from tbl_emp;
查詢結(jié)果：
5、覆蓋索引（索引覆蓋）
覆蓋索引，也有人稱之為索引覆蓋，如果一個索引包含了（或覆蓋了）查詢語句中的查詢字段與條件，此時就可以叫做覆蓋索引。
就是select的數(shù)據(jù)列只用從索引中就能夠取得，不必從數(shù)據(jù)表中讀取。也就是查詢的列被所使用的索引覆蓋。索引是高效找到行的一個方法，當(dāng)能通過檢索索引就可以讀取想要的數(shù)據(jù)，那就不需要再到數(shù)據(jù)表中讀取行了。
不是所有類型的索引都可以成為覆蓋索引。覆蓋索引必須要存儲索引的列，而哈希索引、空間索引和全文索引等都不存儲索引列的值，所以 MySQL 只能使用B-Tree索引做覆蓋索引。
當(dāng)發(fā)起一個被索引覆蓋的查詢（也叫作索引覆蓋查詢）時，在EXPLAIN的Extra列可以看到“Using index”的信息。
注意：如果要使用覆蓋索引，一定要注意 select 列表中只取出所需要的列，不能使用 select * 。
6、索引的創(chuàng)建時機
單表：單表時，可針對where和order的字段建立索引，可建復(fù)合索引。如果查詢語句對某個字段有進行范圍的查詢時，如果某個索引包含該字段，那么該索引在該字段后面的其他字段的索引將不會生效。所以如果對某個字段有范圍的查詢的話，可以考慮不為該字段建立索引。
多表：左連接時，右表一定要建立索引。右連接時，左表一定要建立索引。
“永遠用小的結(jié)果集驅(qū)動大的結(jié)果集”，也就是在小的結(jié)果集的表中建立索引。
7、索引使用原則
要想充分利用到索引，可以遵守以下規(guī)則：
- 全值匹配我最愛，最左前綴要遵守
- 帶頭大哥不能死，中間兄弟不能斷
- 索引列上少計算，范圍之后全失效
- Like百分寫最右，覆蓋索引不寫星
- 不等空值還有or，索引失效要少用
- VAR\VARCHAR引號不可丟，SQL高級也不難
7.1、全值匹配我最愛，最左前綴要遵守；帶頭大哥不能死，中間兄弟不能斷
全值匹配的意思查詢的條件或者排序和復(fù)合索引的字段和順序都保持一致。最左前綴原則指的是查詢從索引的最左的那個字段開始并且不跳過索引中的某個字段，但是右邊的字段不一定需要全部保留。比如復(fù)合索引為：name,age,pos，則查詢條件應(yīng)該為where name = xxx 或者 name = xxx and age =xxx 或者 name = xxx and age =xxx and pos= xxx，如果是where age =xxx and pos =xxx，則索引無法生效。
如果查詢字段與索引字段順序的不同或者跳過了中間的某個字段，則可能會導(dǎo)致索引無法充分使用，甚至索引失效！
在創(chuàng)建組合索引的時候，盡量包含查詢條件中更多的字段，并且將最常用到的作為查詢條件的字段放在最左邊。可以通過不斷地調(diào)整 SQL 查詢語句來匹配到合適的索引。
滿足最左前綴和不能跳過索引的某個字段只是針對該字段是屬于索引的，如果該字段不屬于該索引的字段則不影響。比如復(fù)合索引為：name,age,pos，查詢條件可以為：where name = xxx and sex = xxx and age= xxx and pos=xxx ，中間有個 sex 字段不影響，因為該字段不屬于該索引內(nèi)的字段。
7.2、索引列上少計算，范圍之后全失效
不要在索引列上做任何操作（包括計算、函數(shù)、自動或者手動的類型轉(zhuǎn)換），這些都會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描。
范圍之后全失效：指的是如果對某個索引字段使用了范圍的查詢條件（比如：in、like、<>），那么該索引不會對在該字段之后的字段生效。比如復(fù)合索引為：name,age,sex，查詢條件為where name = xxx and age > 20 and sex =xxx，則索引只對name和age字段的查詢生效，sex字段的過濾不會生效，該索引就不會被充分利用。存儲引擎不能使用索引中范圍條件右邊的列。所以建議將可能做范圍查詢的字段放在索引順序的最后面。
索引匹配始于等于號，終于范圍謂詞。
7.3、Like百分寫最右，覆蓋索引不寫星
Like百分寫最右：當(dāng)使用 like 模糊查詢時，如果百分比符號不止是存在于字符串右邊的話，那么 mysql 的索引就會失效，從而變成全表掃描。
上面可以看到，只有當(dāng)百分比符號只存在于字符串右邊時，索引才生效。或者說，只有當(dāng) like 的百分比符號不存在于字符串最左邊時，索引才生效。
如果想要通過 like '%xxx%' 查詢?nèi)阅苁褂盟饕藭r可以使用覆蓋索引。
比如基于 name, age 字段建立索引 idx_user_nameAge ON tbl_user(NAME,age)，如下查詢：
此時查詢 name, age 字段并且查詢條件 name 包含在索引字段內(nèi)，所以能用到覆蓋索引。
覆蓋索引不寫星：盡量使用覆蓋索引，也就是訪問的字段包含在索引的字段內(nèi)，不要使用或者減少使用 select *。
7.4、不等空值還有or，索引失效要少用
mysql 在使用 is not null 和 is null 時，有可能會無法使用索引從而導(dǎo)致全表掃描。
mysql 在使用不等于符號（!= 或者<>）時，有可能會無法使用索引從而導(dǎo)致全表掃描。
少用 OR 來連接查詢條件，OR 關(guān)鍵字連接查詢條件會導(dǎo)致索引失效。如下：
7.5、VAR\VARCHAR引號不可丟，SQL高級也不難
當(dāng)我們根據(jù)字符串條件來查詢時，即使不加引號，mysql 也能把正確結(jié)果給查詢出來，這是因為 mysql 底層會幫你自動進行類型轉(zhuǎn)換，但這樣實際上就相當(dāng)于對索引列進行了計算，這會導(dǎo)致索引直接失效，變成全表查詢。
如下：
但是給數(shù)字類型額外加上引號并不會導(dǎo)致索引失效。
8、索引失效的其他常見情況
8.1、當(dāng)查詢的數(shù)據(jù)量大于全表的20%時，索引失效
當(dāng)查詢的數(shù)據(jù)量超過一定百分比（可能是20%，或者30%）時，索引就會失效。也就是說，當(dāng)我們通過查詢 SQL 最終查詢出來的數(shù)據(jù)量占全表數(shù)據(jù)量的百分比超過 20% 時，mysql 就可能不會使用索引，而是使用全表掃描，因為此時使用索引的開銷反而更大。
當(dāng)然我們也可以通過 SQL 強制使用索引來解決這個問題。
浙公網(wǎng)安備 33010602011771號