深入了解MySQL中的索引（用處、分類(lèi)、匹配方式）

本篇文章帶大家深入了解mysql中的索引，介紹一下索引的優(yōu)點(diǎn)、用處、分類(lèi)、技術(shù)名詞以及匹配方式，希望對(duì)大家有所幫助！

對(duì)于高級(jí)開(kāi)發(fā)，我們經(jīng)常要編寫(xiě)一些復(fù)雜的sql，那么防止寫(xiě)出低效sql，我們有必要了解一些索引的基礎(chǔ)知識(shí)。通過(guò)這些基礎(chǔ)知識(shí)我們可以寫(xiě)出更高效的sql。【相關(guān)推薦：mysql視頻教程】

01 索引的優(yōu)點(diǎn)

• 大大減少服務(wù)器需要掃描的數(shù)據(jù)量，也就是IO量
• 幫助服務(wù)器避免排序和臨時(shí)表（盡量避免文件排序，而是使用索引排序）
• 將隨機(jī)IO變成順序IO

02 索引的用處

• 快速查找匹配where子句中的行
• 如果可以在多個(gè)索引中選擇，mysql通常會(huì)使用找到最少行的索引
• 如果表具有多列索引，則優(yōu)化器可以使用索引的任何最左前綴來(lái)查找行
• 當(dāng)有表連接的時(shí)候，從其他表檢索行數(shù)據(jù)
• 查找特定索引列的min和max的值
• 如果排序或者分組時(shí)可用索引的最左前綴完成的，則對(duì)表進(jìn)行排序和分組
• 在某些情況下，可以優(yōu)化查詢以檢索數(shù)據(jù)值而無(wú)需查找數(shù)據(jù)行

03 索引的分類(lèi)

數(shù)據(jù)庫(kù)默認(rèn)建立的索引是給唯一鍵建立的

• 主鍵索引(唯一且非空)
• 唯一索引(唯一可為空)
• 普通索引(普通字段的索引)
• 全文索引(一般是varchar,char,text類(lèi)型建立的，但很少用)
• 組合索引(多個(gè)字的建立的索引)

04 索引的技術(shù)名詞

1. 回表

name字段是普通索引，從name列的B+樹(shù)找到主鍵，再?gòu)闹麈I的B+樹(shù)找到最終的數(shù)據(jù)，這就是回表。(主鍵索引的葉子節(jié)點(diǎn)保存的是列的所有數(shù)據(jù)，但是普通所有的葉子結(jié)點(diǎn)保存的是對(duì)應(yīng)的主鍵ID）

如圖：一個(gè)use表中name建立的索引結(jié)構(gòu)sql是select * from use where name=’sun’首先會(huì)通過(guò)name這個(gè)非主鍵索引找到sun對(duì)應(yīng)的主鍵Id=2,然后通過(guò)id=2在主鍵索引中找到整個(gè)行數(shù)據(jù)，并返回，這個(gè)就是回表。

2. 覆蓋索引

在非主鍵索引上可以查詢到所需要的字段，不需要回表再次查詢就叫覆蓋索引。

如上圖name索引，sql是 select id,name from user where name =”1″ ，id的值在第一步非主鍵索引就已經(jīng)有了，就不需要根據(jù)ID到主鍵索引中查詢行數(shù)據(jù)了。

3. 最左匹配

組合索引中 先匹配左邊，再繼續(xù)向后匹配；比如user表中有name+age組成的聯(lián)合索引，select * from user where name=”紀(jì)先生” and age = 18 就符合最左匹配，可以用的索引。而select * from user where age = 18就不符合，用不到這個(gè)索引。

擴(kuò)展；

如果是下面兩個(gè)sql怎么建立索引

select?*?from?user?where?name="紀(jì)先生"?and?age?=?18; select?*?from?user?where?age?=?18;

由于最左匹配原則：只需要建立一個(gè)組合索引age+name即可

如果是下面三個(gè)sql呢

select?*?from?user?where?name="紀(jì)先生"?and?age?=?18; select?*?from?user?where?name=?"紀(jì)先生";

建立name+age和age索引，或者建立age+name和name索引,看著兩個(gè)都可以。

其實(shí)name+age和age更好，因?yàn)樗饕彩切枰志没鎯?chǔ)的，占用磁盤(pán)空間，讀取的時(shí)候也是占用內(nèi)存的，name+age和age+name這兩個(gè)占用是一樣的，但是name和age單獨(dú)比較，肯定age占用空間更少，name更長(zhǎng)（索引越大，IO次數(shù)可能更多）

注意！注意！注意！：

在看很多文章的時(shí)候，經(jīng)常看到一些對(duì)于最左匹配錯(cuò)誤的舉例：

如果索引是name+age的組合索引，sql是select * from user where age = 18 and name=”紀(jì)先生”很多人認(rèn)為這種是不能走索引，實(shí)際上可以的。mysql的優(yōu)化器會(huì)優(yōu)化調(diào)整順序的，調(diào)整成 name=”紀(jì)先生” and age = 18

4. 索引下推

組合索引中盡量利用索引信息，來(lái)盡可能的減少回表的次數(shù)

案例：還是 name+age的組合索引如果沒(méi)有索引下推的查詢是 在組合索引中通過(guò)name查詢所有匹配的數(shù)據(jù)，然后回表根據(jù)ID查詢對(duì)于的數(shù)據(jù)行，之后在篩選出符合age條件的數(shù)據(jù)。索引下推就是組合索引中通過(guò)name查詢匹配再根據(jù)age找到符合的數(shù)據(jù)ID，然后回表根據(jù)ID查詢對(duì)應(yīng)行數(shù)據(jù)，明顯會(huì)減少數(shù)據(jù)的條數(shù)

05 索引匹配方式

mysql官網(wǎng)準(zhǔn)備了一些學(xué)習(xí)測(cè)試的數(shù)據(jù)庫(kù)，可以直接下載通過(guò)source導(dǎo)入到我們自己的數(shù)據(jù)庫(kù)

官網(wǎng)地址：mysql視頻教程

如上圖下載zip, 其中包含了sakila-schema.sql和sakila-data.sql,分別是sakila的庫(kù)，表和數(shù)據(jù)的創(chuàng)建腳本。

mysql>?source?/Users/ajisun/Downloads/sakila-db/sakila-schema.sql; mysql>?source?/Users/ajisun/Downloads/sakila-db/sakila-data.sql;

需要通過(guò)explain來(lái)查看索引的執(zhí)行情況，執(zhí)行計(jì)劃以前有文章詳細(xì)講過(guò)，具體參考mysql視頻教程

1. 全值匹配

指和某個(gè)索引中的所有列進(jìn)行匹配，例如使用數(shù)據(jù)庫(kù)sakila中的staff表

新建一個(gè)三個(gè)字段的聯(lián)合索引：

mysql>?alter?table?staff?add?index?index_n1(first_name,last_name,username);

執(zhí)行sql:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer' and username='Mike'復(fù)制代碼

其中的ref是三個(gè)const， 用到三個(gè)字段，能全匹配一條數(shù)據(jù)

2. 最左前綴匹配

只匹配組合索引中前面幾個(gè)字段

執(zhí)行sql:

mysql>?explain?select?*?from?staff?where?first_name='Mike'?and?last_name='Hillyer';

ref只出現(xiàn)2個(gè)const,比上面全值匹配少一個(gè)，就只匹配了前面兩個(gè)字段

3. 匹配列前綴

可以匹配某一列的的開(kāi)頭部分，像like屬性

執(zhí)行sql:

mysql>?explain?select?*?from?staff?where?first_name?like?'Mi%';

type=range ,是個(gè)范圍查詢，可以匹配一個(gè)字段的一部分，而不需要全值匹配

如果有模糊匹配的字段不要放在索引的最前面，否則有索引也不能使用，如下

4. 匹配一個(gè)范圍值

可以查找某一個(gè)范圍的數(shù)據(jù)

mysql>?explain?select?*?from?staff?where?first_name?>?'Mike';

5. 精確匹配某一列并范圍匹配另一列

可以查詢第一列的全部和另一列的部分

mysql>?explain?select?*?from?staff?where?first_name?=?'Mike'?and?last_name?like?'Hill%';

6. 只訪問(wèn)索引的查詢

查詢的時(shí)候只需要訪問(wèn)索引，不需要訪問(wèn)數(shù)據(jù)行，其實(shí)就是索引覆蓋

mysql>?explain?select?first_name,last_name,username?from?staff?where?first_name='Mike'?and?last_name='Hillyer';

extra=Using index 說(shuō)明是使用了索引覆蓋，不需要再次回表查詢。

其實(shí)一張表中有索引并不總是最好的。總的來(lái)說(shuō)，只有當(dāng)索引幫助存儲(chǔ)引擎快速提高查找到記錄帶來(lái)的好處大于其帶來(lái)的額外工作時(shí)，索引才是有效的。對(duì)應(yīng)很小的表，大部分情況下沒(méi)有索引，全表掃描更高效；對(duì)應(yīng)中大型表，索引時(shí)非常有效的；但是對(duì)于超大的表，索引的建立和使用代價(jià)也就非常高，一般需要單獨(dú)處理特大型的表，例如分區(qū)，分庫(kù)，分表等。

更多編程相關(guān)知識(shí)，請(qǐng)?jiān)L問(wèn)：mysql視頻教程！！