引入一個(gè)面試問(wèn)題:
查詢一條數(shù)據(jù) 舞虱, 如果where 后面 有主鍵 ,有其他索引债热, mysql 會(huì)使用哪個(gè)去查詢數(shù)據(jù)砾嫉?
為什么選擇用主鍵查詢數(shù)據(jù)?
主鍵查詢完成后 需要回表操作么窒篱?
看完以下以后再回顧焕刮,會(huì)發(fā)現(xiàn)迎刃而解
Mysql 可以為每一張表設(shè)置 存儲(chǔ)引擎 這里我們只說(shuō) InnoDB 存儲(chǔ)引擎.
聚集索引
InnoDB 存儲(chǔ)引擎表 是索引組織表, 即 表中的數(shù)據(jù) 按照主鍵順序存放墙杯。 而聚集索引配并,或者聚簇索引就是按照 表中的主鍵構(gòu)造一顆B +樹(shù) ,(如果米有指定主鍵高镐,那么 Mysql會(huì)自動(dòng)生成一個(gè)6字節(jié)的rowId作為主鍵) 溉旋, 同時(shí) 在它葉子節(jié)點(diǎn)中 存放,主鍵關(guān)聯(lián)行的 數(shù)據(jù)嫉髓, 观腊,也就是說(shuō) 每個(gè)主鍵會(huì)關(guān)聯(lián)所在行的所有記錄數(shù)據(jù)邑闲, 因此也將葉結(jié)點(diǎn)稱為數(shù)據(jù)頁(yè)。 所以 這個(gè)特性決定了 索引組織表中的數(shù)據(jù)梧油,也是索引的一部分苫耸。 同B+樹(shù)一樣, 每個(gè)數(shù)據(jù)頁(yè)都通過(guò)雙向鏈表鏈接
由于實(shí)際情況儡陨,數(shù)據(jù)頁(yè)只能按照一棵 B+樹(shù) 進(jìn)行排序褪子, 因此每張表只能擁有一個(gè) 聚集索引(即 主鍵)。
在多數(shù)情況下骗村, 查詢優(yōu)化器 會(huì)優(yōu)先查詢 聚集索引或者說(shuō)聚簇索引(主鍵)嫌褪, 因?yàn)榭梢酝ㄟ^(guò)該索引 直接在葉子節(jié)點(diǎn)上找到數(shù)據(jù)
此外由于 定義了數(shù)據(jù)的邏輯范圍,(按照主鍵順序排序 胚股,數(shù)據(jù)頁(yè)雙向鏈表) 所以笼痛, 查詢優(yōu)化器可以迅速的發(fā)現(xiàn)一段范圍的數(shù)據(jù)頁(yè)需要掃描
栗子:
查詢 一張注冊(cè)用戶的表, 獲取最后十位注冊(cè)的用戶
?
select * from register order by id DESC, limit 10;
聚集索引的一個(gè)好處就是信轿, 它對(duì)于主鍵的排序和范圍查找速度非郴纬眨快。 而且 葉子節(jié)點(diǎn)關(guān)聯(lián) 了主鍵對(duì)應(yīng)行數(shù)據(jù)财忽,所以不需要回表可以直接定位數(shù)據(jù)倘核, 且 頁(yè)節(jié)點(diǎn)的數(shù)據(jù)(數(shù)據(jù)頁(yè)) 采用雙向鏈表, 可以很方便的去往前 遍歷數(shù)據(jù)
輔助索引
輔助索引也叫 非聚集索引(或者說(shuō)非聚簇索引)即彪, 這類索引 葉子節(jié)點(diǎn)并不包含記錄行數(shù)據(jù)紧唱。
每個(gè)葉子節(jié)點(diǎn)的索引行中包含了一個(gè)書(shū)簽(bookmark). 該書(shū)簽是用來(lái)告訴 InnoDB存儲(chǔ)引擎哪里可以找到該索引對(duì)應(yīng)的數(shù)據(jù)行或者說(shuō) 行數(shù)據(jù)! 由于InnoDB存儲(chǔ)引擎表隶校, 是按照主鍵來(lái)構(gòu)建的漏益, 所以 ,該書(shū)簽內(nèi)其實(shí)包含或者說(shuō)指向了 數(shù)據(jù)行所對(duì)應(yīng)的聚集索引鍵
也就是說(shuō) 輔助索引的 葉結(jié)點(diǎn)保存了 指向?qū)?yīng)數(shù)據(jù)的 聚集索引深胳, 可以通過(guò)該聚集索引 找到對(duì)應(yīng)的數(shù)據(jù)行
輔助索引的存在并不影響數(shù)據(jù)在聚集索引中的組織绰疤,因?yàn)槊繌埍砩峡梢杂卸鄠€(gè)輔助索引。
當(dāng)通過(guò)輔助索引來(lái)尋找數(shù)據(jù)時(shí)舞终,InnoDB 存儲(chǔ)引擎會(huì)遍歷輔助索引并通過(guò)葉級(jí)別的指針獲得指向主鍵索引(聚集索引)的主鍵轻庆,然后再通過(guò)聚集索引找到一個(gè)完整的數(shù)據(jù)行。
例如:
如果在一棵高度為3 的輔助索引樹(shù)中查找數(shù)據(jù)敛劝, 那么需要對(duì)這棵樹(shù)進(jìn)行3次遍歷才能找到指定的聚集索引余爆, 如果聚集索引樹(shù)的高度也是3 , 那么還需要對(duì)該聚集索引樹(shù)進(jìn)行3次的查找夸盟,最終找到一個(gè)完整的行數(shù)據(jù)所在的頁(yè)蛾方,因此一共需要6次 邏輯IO訪問(wèn) 得到一個(gè)最終的數(shù)據(jù)頁(yè)
聚集索引輔助索引關(guān)系:
聯(lián)合索引
: 又叫做組合索引 ,輔助索引的一種, 和普通創(chuàng)建索引的方式一樣桩砰,不同的是 可以同時(shí)添加多列來(lái)作為索引項(xiàng);
從本質(zhì)上來(lái)說(shuō)拓春,聯(lián)合索引也是一課B+樹(shù)
索引中 使用多個(gè)列組成 的索引 如 index(id,name,age)
組合索引 必須遵守最左原則: 即使用組合索引時(shí) 需要滿足 最左原則 否則 索引不生效,即:查詢時(shí)需要滿足 必須存在索引列最左邊的那一列的條件
個(gè)人理解:所謂最左原則五芝, 是因?yàn)?存儲(chǔ)引擎構(gòu)建組合索引時(shí) 是根據(jù)最左邊的那一列索引項(xiàng)進(jìn)行排序的 痘儡,所以使用組合索引,必須滿足 條件中必須存在 最左邊那一列的索引項(xiàng)枢步,這樣 才可以找到對(duì)應(yīng)的索引,繼而 去尋找對(duì)應(yīng)的數(shù)據(jù)
覆蓋索引
: 又叫做 索引覆蓋渐尿,InnoDB中支持覆蓋索引醉途,即 從輔助索引中就可以得到查詢的記錄,而不需要查詢聚集索引中的記錄砖茸。
-
使用覆蓋索引的一個(gè)好處就是隘擎,由于本身不會(huì)包含完整行數(shù)據(jù)(輔助索引 只包含 主鍵的一些信息 比如 key)
所以大小要遠(yuǎn)遠(yuǎn)小于聚集索引,且由于可以從本身直接獲取到想要的數(shù)據(jù)凉夯,無(wú)需回表查詢货葬,可以大大加快查詢速度,減少大量IO 操作
對(duì)于 組合 索引 (id,ke2,ke3)
?
select ke2 from table where id=xxx;
select ke2,key3 from table where id=xxx;
?
等等.. 都可以使用一次輔助聯(lián)合索引 來(lái)完成查詢劲够,這其中就是覆蓋索引震桶,,可以直接獲取數(shù)據(jù)而無(wú)需回索引表查詢
?
select count(*) from table;
InnoDB 存儲(chǔ)引擎是不會(huì)查詢聚集索引來(lái)進(jìn)行統(tǒng)計(jì)征绎, 由于存在輔助索引蹲姐,而輔助索引遠(yuǎn)小于聚集索引,選擇輔助索引可以減少IO操作人柿,所以優(yōu)化器會(huì)選擇 輔助索引來(lái)進(jìn)行統(tǒng)計(jì)
通常情況下: 聯(lián)合索引不滿足 最左原則的話柴墩,優(yōu)化器是不會(huì)選擇使用 該索引的,但是如果是 使用聚合函數(shù)比如 count(*) 進(jìn)行統(tǒng)計(jì)凫岖, 且是可以利用到覆蓋索引的江咳,則優(yōu)化器會(huì)進(jìn)行選擇
比如 這里沒(méi)有根據(jù)最左原則使用組合索引,但是 優(yōu)化器依然進(jìn)行選擇
mysql> desc select count(*) from student where age>10 and age <20;
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | student | NULL | index | id | id | 52 | NULL | 9 | 11.11 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)</pre>
回表
即 : 所謂回表哥放,就是 輔助索引 查找數(shù)據(jù)時(shí)歼指, 獲取到對(duì)應(yīng)的主鍵以后,根據(jù)主鍵掃描另一棵索引樹(shù)(根據(jù)主鍵查詢聚集索引表)來(lái)獲取數(shù)據(jù)
因此婶芭,上面面試題中 东臀,主鍵查詢完成以后不需要回表,因?yàn)?使用了聚集索引犀农,可以直接在葉結(jié)點(diǎn)獲取對(duì)應(yīng)數(shù)據(jù)
共勉惰赋,歡迎指導(dǎo)謝謝~