更多精彩歡迎關(guān)注公眾號 xhJaver 喲
一、 索引數(shù)據(jù)結(jié)構(gòu)
面試的時候肯定會問這一個問題，mysql為什么會選擇b+樹作為索引呢？而不選擇其他索引，例如b樹？hash？
下面說的磁盤IO是指數(shù)據(jù)從硬盤加載到內(nèi)存中的操作
hash索引的話，不支持范圍查詢，因為hash就是一個鍵對應(yīng)一個值的，沒辦法范圍查詢
二叉樹的話，它的特點(diǎn)就是左子樹小于根節(jié)點(diǎn)小于右子樹，如果根節(jié)點(diǎn)取值有問題的話，有可能會退化成鏈表，就是樹不分叉了，樹一直往左或者一直往右，這樣就不能折半查找從而減少IO次數(shù)了，不支持范圍查詢，要是范圍查詢的話，每次都要從根部遍歷，樹也太高了，樹越高，IO操作越頻繁，浪費(fèi)資源
平衡二叉樹的話，它就沒有了二叉樹的這種退化成鏈表的缺點(diǎn)，因為他左右子節(jié)點(diǎn)最多相差1層，可是他也不支持范圍查找這一點(diǎn)和二叉樹的問題一樣
b樹的話，和二叉樹比起來樹是很矮胖，IO操作減少了，是個多叉樹，它每個節(jié)點(diǎn)都存了對應(yīng)的行數(shù)據(jù)，可是如果這一行的數(shù)據(jù)的列不斷的增加，那么這一頁存儲的節(jié)點(diǎn)就會變少，因為所占的空間不斷的變大，樹也會越來越高，增加IO操作次數(shù)，同時是也不支持范圍查找。要是相同大小的空間可以存很多的節(jié)點(diǎn)數(shù)據(jù)的話就更好了，所以就有了下面的b+樹
b+樹 它非葉子節(jié)點(diǎn)只存索引的數(shù)據(jù)，不存整行數(shù)據(jù)，但是葉子節(jié)點(diǎn)是冗余的，冗余了非葉子節(jié)點(diǎn)，葉子節(jié)點(diǎn)還都用雙向鏈表鏈接起來，這樣有助于順序查找，b+樹和b樹比起來，更加矮胖，磁盤IO次數(shù)更少
二、 mysql中索引類型
- 聚簇索引與非聚簇索引
我們可以簡單的理解為 聚簇索引就是主鍵索引，非聚簇索引就是普通索引
本質(zhì)的區(qū)別是
聚簇索引的葉子節(jié)點(diǎn)存儲的是整行數(shù)據(jù)
innodb是通過主鍵來實(shí)現(xiàn)聚簇索引的，如果沒有主鍵的話，那么他就會選擇一個唯一非空的索引來實(shí)現(xiàn)，如果再沒有的話，他就會隱式生成一個主鍵來實(shí)現(xiàn)聚簇索引
非聚簇索引存儲的是索引值和主鍵值
普通索引 一張表中可以有多個普通索引，隨便一個字段都可以建立的索引，我們平常建立的索引大部分都是普通索引
聯(lián)合索引 好幾個字段聯(lián)合起來建立的索引
唯一索引 業(yè)務(wù)中唯一的字段適合建立唯一索引，一個表中可以有多個唯一索引
主鍵索引 和唯一索引一樣，主鍵索引也是唯一的，不同的就是，一個表只能有一個主鍵索引
三、關(guān)于索引的sql
創(chuàng)建主鍵索引
ALTER TABLE test add PRIMARY KEY (id)
創(chuàng)建唯一索引
ALTER TABLE test add UNIQUE idx_id_card(id_card)
創(chuàng)建普通索引
ALTER TABLE test add INDEX idx_name(name)
創(chuàng)建聯(lián)合索引
ALTER TABLE test add INDEX idx_age_name(age,name)
修改索引名稱 ：先刪除再添加
刪除索引 （兩種方式）
ALTER TABLE test DROP INDEX idx_id_cardDROP INDEX idx_id_card on test --刪除主鍵索引DROP PRIMARY key on test ALTER TABLE test DROP PRIMARY key
查看表中索引
SHOW INDEX FROM test
分析索引
EXPLAIN SELECT * from test WHERE name = "xhJaver"
我們先給name字段添加一個索引,索引名字叫做idx_name
ALTER TABLE test add INDEX idx_name(name)
查看test表中的索引
SHOW INDEX FROM test
其中的屬性
table: 表名
Non_unique: 能重復(fù)的話為1，不能重復(fù)的話為0，我們主鍵的那里是0，而name那里是1，因為name可以重復(fù)，而主鍵不能重復(fù)
Key_name: 索引名稱
Seq_in_index:索引中列的順序
Column_name:列名稱
Collation:列以什么方式存儲的，A升序，null無序
Cardinality:數(shù)目越大，則使用該索引的可能性越大
Sub_part:如果列只是部分的編入索引，則被編入索引的字符數(shù)目，如果整列被編入索引，則為null
Packed：關(guān)鍵字是否被壓縮，null表示沒有被壓縮
Null:如果該列含有null,則為yes,如果沒有null,則為no
Index_type:索引數(shù)據(jù)結(jié)構(gòu)
Comment:多種評注
四、回表查詢
select * from test where name = "xhJaver"
假如說我們name字段建立了索引，然后當(dāng)我們運(yùn)行這一句sql語句的時候，因為建立的是普通索引，所以我們的b+樹的葉子節(jié)點(diǎn)存儲的數(shù)據(jù)是id，我們會找到name是xhJaver的這條記錄的id，再根據(jù)這個id,去主鍵索引的那棵b+樹去查詢，查詢到葉子節(jié)點(diǎn)時即查詢出這條記錄，可見這個過程中，我們從一棵樹跑到了另一棵樹繼續(xù)查，這樣就叫做“回表查詢”，那有沒有辦法只查一棵樹就可以查詢出結(jié)果呢？
五、覆蓋索引
辦法當(dāng)然是有的啦，那就是覆蓋索引，我們注意到，剛才這個sql語句時查詢出來了所有元素，假如說我們這樣寫的話
select address from test where name = "xhJaver"
假如說我們建立的索引是（name,address）那么這個時候（name,address）這棵b+樹的葉子節(jié)點(diǎn)存儲的數(shù)據(jù)就包括address了，此時就不需要再根據(jù)name = "xhJaver"的id去第二棵樹查了，這樣就避免了回表查詢
六、最左匹配原則
假如說現(xiàn)在我們寫一個這樣的sql語句
select * from test where name = "xhJaver" and age =23 and address="京東"
并且我們建立的索引是（name,address,age）這樣是會用到（name,address,age）索引的，可是如果要這樣寫的話
select * from test where name = "xhJaver" and age >23 and address="京東"
這樣只會用到（name，age）這兩個索引，從左邊開始匹配，如果要是遇到范圍查詢的話，則不繼續(xù)往右匹配索引
七、explain分析索引語句
我們用explain語句解析一下下面這條sql語句
EXPLAIN SELECT * from test WHERE name = "xhJaver"
它的屬性有
id: 執(zhí)行的順序
- id相同時，順序從上到下執(zhí)行
- id不同時，id大的先執(zhí)行
select_type: 查詢的類型
- primary: 最外層的查詢被標(biāo)記為primary
- simple: 簡單查詢，沒有關(guān)聯(lián)其他表，就一張表
- subquery: 在where或者select中的子查詢
- derived: 衍生虛擬表 例如from(子查詢) t,這個子查詢的結(jié)果就被放在虛擬表t中
table: 關(guān)于哪張表的
partitions: 分區(qū)相關(guān)（還沒搞懂嗚嗚嗚）
type:訪問類型
性能由好至壞依次是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般來說，好的sql查詢至少達(dá)到range級別，最好能達(dá)到ref
system:表中只有一行數(shù)據(jù)
const:常量查詢 通常用于比較主鍵等于一個常量，用索引查詢一次就查到了
eq_ref:唯一性索引，每個索引對應(yīng)一條數(shù)據(jù)，例如主鍵索引
ref:非唯一索引，每個索引有可能對應(yīng)多行數(shù)據(jù)，例如普通索引
range: 范圍查詢，用到了>，<，in,between等查詢
index：全表掃描，但是是遍歷整棵索引樹
all:全表掃描，沒有用到索引
possible_keys：查詢的字段上有索引的話，就會顯示出來，
key : 具體用到的索引，若用到了覆蓋索引，則possible_keys為null,只會顯示在key中
key_len:索引中使用的字節(jié)數(shù)，最大可能長度，并非實(shí)際長度，key_len是根據(jù)表定義計算而得的，不是通過表內(nèi)檢索出的
ref: 表示使用索引的是哪一個字段
rows:大致估算出所需要讀取的行數(shù)
filtered:顯示了通過條件過濾出的行數(shù)的百分比估計值。
Extra:
Using filesort ： mysql無法利用索引完成的排序被稱為文件排序
Using temporary： 使用臨時表存儲了下中間結(jié)果，mysql對查詢結(jié)果排序時是使用了臨時表，常見于order by 和 group by
-
Using index：使用了覆蓋索引，查詢內(nèi)容在索引內(nèi)
- 如果出現(xiàn)了Using where，表示對查詢出來的數(shù)據(jù)進(jìn)行了過濾
- 如果沒有出現(xiàn)Using where，表示對查詢出來的數(shù)據(jù)沒有進(jìn)行過濾
只有Using where 查詢內(nèi)容不在索引內(nèi)，且對查出來的數(shù)據(jù)進(jìn)行了過濾
1. EXPLAIN SELECT (select student.id from student WHERE student.`name`="xhJaver") FROM teacher2. EXPLAIN SELECT * FROM teacher where teacher.id = (select student.id from student WHERE student.`name`="xhJaver")
我們寫幾個sql語句實(shí)際分析下
1.SELECT后面
2.where后面
我們就拿后面這個圖來實(shí)戰(zhàn)分析一下,挑幾個重要的屬性說一下
select_type：
我們最外層的查詢是 from teacher 所以table為teacher的那個表的select_type就是primary
select/where后面的括號中的查詢語句中的表是student，所以table為student的那個表的select_type就是subquery
table: 這條sql查詢用到的表
type: 訪問類型
第一行const : teacher.id =巴拉巴拉巴拉（這個是常數(shù)）主鍵和常數(shù)比較時，這個表最多有一個匹配數(shù)據(jù)，只讀取一次
第二行ref：代表用到了普通索引，就是這個索引name和xhJaver匹配,可能匹配到很多相同的值
possible_key: 代表可能用到的索引，但是不一定會用到
key: 代表用到的索引， 用到了idx_name，PRIMARY索引
ref: 這一列顯示了在key列記錄的索引中，表查找值所用到的列或常量， 常見的有：const，字段名
extra：
- using index: 一般是使用了覆蓋索引，看我們這個sql語句，
select student.id from student WHERE student.`name`="xhJaver"
name字段有索引，查詢的是id，b+樹葉子節(jié)點(diǎn)存的數(shù)據(jù)就是id，所以不需要回表查詢了，用到了覆蓋索引
八、索引失效原因
遇到范圍查詢（>,<,like,beetwon），右邊的索引列會失效
索引字段不能有函數(shù)操作或者不能是表達(dá)式的一部分
索引字段隱式類型轉(zhuǎn)換 索引字段類型是string，我們傳進(jìn)來個int
使用時or,is null ,is not null , !=, <>, like "%xxx" 索引會失效
但是用覆蓋索引就可以解決 like左模糊查詢走不到索引的情況 如果只select索引字段，或者select索引字段和主鍵，也會走索引的。
往期推薦
更多精彩歡迎關(guān)注公眾號 xhJaver 喲
