mysql可以靠索引，而我只能靠打工，加油，打工人！
點(diǎn)擊上方?好好學(xué)java?，選擇?星標(biāo)?公眾號
重磅資訊、干貨，第一時(shí)間送達(dá)
重磅資訊、干貨，第一時(shí)間送達(dá)
今日推薦：硬剛一周，3W字總結(jié)，一年的經(jīng)驗(yàn)告訴你如何準(zhǔn)備校招！
個人原創(chuàng)100W+訪問量博客：點(diǎn)擊前往，查看更多
個人原創(chuàng)100W+訪問量博客：點(diǎn)擊前往，查看更多
一、 ?索引數(shù)據(jù)結(jié)構(gòu)
面試的時(shí)候肯定會問這一個問題，mysql為什么會選擇b+樹作為索引呢？而不選擇其他索引，例如b樹？hash？
下面說的磁盤IO是指數(shù)據(jù)從硬盤加載到內(nèi)存中的操作
hash索引的話，不支持范圍查詢，因?yàn)閔ash就是一個鍵對應(yīng)一個值的，沒辦法范圍查詢
二叉樹的話，它的特點(diǎn)就是左子樹小于根節(jié)點(diǎn)小于右子樹（指的是節(jié)點(diǎn)的值），如果根節(jié)點(diǎn)取值有問題的話，有可能會退化成鏈表，就是樹不分叉了，樹一直往左或者一直往右，這樣就不能折半查找從而減少IO次數(shù)了，同時(shí)也不支持范圍查詢，要是范圍查詢的話，每次都要從根部遍歷，樹也太高了，樹越高，IO操作越頻繁，浪費(fèi)資源
平衡二叉樹的話，它就沒有了二叉樹的這種退化成鏈表的缺點(diǎn)，因?yàn)樗笥易庸?jié)點(diǎn)最多相差1層，可是他也不支持范圍查找這一點(diǎn)和二叉樹的問題一樣
b樹的話，和二叉樹比起來樹是很矮胖，IO操作減少了，是個多叉樹，它每個節(jié)點(diǎn)都存了對應(yīng)的行數(shù)據(jù)，可是如果這一行的數(shù)據(jù)的列不斷的增加，那么這一頁存儲的節(jié)點(diǎn)就會變少，因?yàn)樗嫉目臻g不斷的變大，樹也會越來越高，增加IO操作次數(shù)，同時(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_card
DROP?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，因?yàn)閚ame可以重復(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語句的時(shí)候，因?yàn)榻⒌氖瞧胀ㄋ饕晕覀兊腷+樹的葉子節(jié)點(diǎn)存儲的數(shù)據(jù)是id，我們會找到name是xhJaver的這條記錄的id，再根據(jù)這個id,去主鍵索引的那棵b+樹去查詢，查詢到葉子節(jié)點(diǎn)時(shí)即查詢出這條記錄，可見這個過程中，我們從一棵樹跑到了另一棵樹繼續(xù)查，這樣就叫做“回表查詢”，那有沒有辦法只查一棵樹就可以查詢出結(jié)果呢？
五、覆蓋索引
辦法當(dāng)然是有的啦，那就是覆蓋索引，我們注意到，剛才這個sql語句時(shí)查詢出來了所有元素，假如說我們這樣寫的話
select?address?from?test?where??name?=?"xhJaver"
假如說我們建立的索引是（name,address）那么這個時(shí)候（name,address）這棵b+樹的葉子節(jié)點(diǎn)存儲的數(shù)據(jù)就包括address了，此時(shí)就不需要再根據(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相同時(shí)，順序從上到下執(zhí)行 id不同時(shí)，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ù)表定義計(jì)算而得的，不是通過表內(nèi)檢索出的
ref: 表示使用索引的是哪一個字段
rows:大致估算出所需要讀取的行數(shù)
filtered:顯示了通過條件過濾出的行數(shù)的百分比估計(jì)值。
Extra:
Using filesort ：? mysql無法利用索引完成的排序被稱為文件排序
Using temporary：? 使用臨時(shí)表存儲了下中間結(jié)果，mysql對查詢結(jié)果排序時(shí)是使用了臨時(shí)表，常見于order by 和 group by
1.?EXPLAIN?SELECT?(select?student.id?from?student?WHERE?student.`name`="xhJaver")?FROM?teacher
2.?EXPLAIN?SELECT?*?FROM?teacher?where?teacher.id?=?(select?student.id?from?student?WHERE?student.`name`="xhJaver")?
我們寫幾個sql語句實(shí)際分析下
1.sql1? SELECT后面有子查詢
2.sql2? 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í)，這個表最多有一個匹配數(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），右邊的索引列會失效(使用聯(lián)合索引時(shí))
索引字段不能有函數(shù)操作或者不能是表達(dá)式的一部分
索引字段隱式類型轉(zhuǎn)換 索引字段類型是string，我們傳進(jìn)來個int
使用時(shí)or,is null ,is not null , !=, <>, like "%xxx" 索引會失效
但是用覆蓋索引就可以解決 like左模糊查詢走不到索引的情況 如果只select索引字段，或者select索引字段和主鍵，也會走索引的。
推薦文章
原創(chuàng)電子書
歷時(shí)整整一年總結(jié)的?Java 面試 + Java 后端技術(shù)學(xué)習(xí)指南，這是本人這幾年及校招的總結(jié)，各種高頻面試題已經(jīng)全部進(jìn)行總結(jié)，按照章節(jié)復(fù)習(xí)即可，已經(jīng)拿到了大廠offer。
原創(chuàng)思維導(dǎo)圖
掃碼或者微信搜?程序員的技術(shù)圈子?回復(fù)?面試?領(lǐng)取原創(chuàng)電子書和思維導(dǎo)圖。
