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

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

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

對(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è)就是回表。

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

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視頻教程

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

如上圖下載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ù)制代碼

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

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

2. 最左前綴匹配

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

執(zhí)行sql:

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

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

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

3. 匹配列前綴

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

執(zhí)行sql:

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

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

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

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

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

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

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

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

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

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

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

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

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

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';

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

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視頻教程!!

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點(diǎn)贊10 分享