本篇文章帶大家深入了解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視頻教程!!