mysql可以靠索引，而我只能靠打工….

4615

mysql教程欄目介紹相關(guān)索引。

mysql可以靠索引，而我只能靠打工….

一、 ?索引數(shù)據(jù)結(jié)構(gòu)

面試的時(shí)候肯定會(huì)問(wèn)這一個(gè)問(wèn)題，mysql為什么會(huì)選擇b+樹(shù)作為索引呢？而不選擇其他索引，例如b樹(shù)？hash？

下面說(shuō)的磁盤(pán)IO是指數(shù)據(jù)從硬盤(pán)加載到內(nèi)存中的操作

  • hash索引的話，不支持范圍查詢，因?yàn)閔ash就是一個(gè)鍵對(duì)應(yīng)一個(gè)值的，沒(méi)辦法范圍查詢

  • 二叉樹(shù)的話，它的特點(diǎn)就是左子樹(shù)小于根節(jié)點(diǎn)小于右子樹(shù)，如果根節(jié)點(diǎn)取值有問(wèn)題的話，有可能會(huì)退化成鏈表，就是樹(shù)不分叉了，樹(shù)一直往左或者一直往右，這樣就不能折半查找從而減少I(mǎi)O次數(shù)了，不支持范圍查詢，要是范圍查詢的話，每次都要從根部遍歷，樹(shù)也太高了，樹(shù)越高，IO操作越頻繁，浪費(fèi)資源

  • 平衡二叉樹(shù)的話，它就沒(méi)有了二叉樹(shù)的這種退化成鏈表的缺點(diǎn)，因?yàn)樗笥易庸?jié)點(diǎn)最多相差1層，可是他也不支持范圍查找這一點(diǎn)和二叉樹(shù)的問(wèn)題一樣

  • b樹(shù)的話，和二叉樹(shù)比起來(lái)樹(shù)是很矮胖，IO操作減少了，是個(gè)多叉樹(shù)，它每個(gè)節(jié)點(diǎn)都存了對(duì)應(yīng)的行數(shù)據(jù)，可是如果這一行的數(shù)據(jù)的列不斷的增加，那么這一頁(yè)存儲(chǔ)的節(jié)點(diǎn)就會(huì)變少，因?yàn)樗嫉目臻g不斷的變大，樹(shù)也會(huì)越來(lái)越高，增加IO操作次數(shù)，同時(shí)是也不支持范圍查找。要是相同大小的空間可以存很多的節(jié)點(diǎn)數(shù)據(jù)的話就更好了，所以就有了下面的b+樹(shù)

  • b+樹(shù) 它非葉子節(jié)點(diǎn)只存索引的數(shù)據(jù)，不存整行數(shù)據(jù)，但是葉子節(jié)點(diǎn)是冗余的，冗余了非葉子節(jié)點(diǎn)，葉子節(jié)點(diǎn)還都用雙向鏈表鏈接起來(lái)，這樣有助于順序查找，b+樹(shù)和b樹(shù)比起來(lái)，更加矮胖，磁盤(pán)IO次數(shù)更少

二、 mysql中索引類型

  • 聚簇索引與非聚簇索引

我們可以簡(jiǎn)單的理解為 聚簇索引就是主鍵索引，非聚簇索引就是普通索引

本質(zhì)的區(qū)別是

聚簇索引的葉子節(jié)點(diǎn)存儲(chǔ)的是整行數(shù)據(jù)

innodb是通過(guò)主鍵來(lái)實(shí)現(xiàn)聚簇索引的，如果沒(méi)有主鍵的話，那么他就會(huì)選擇一個(gè)唯一非空的索引來(lái)實(shí)現(xiàn)，如果再?zèng)]有的話，他就會(huì)隱式生成一個(gè)主鍵來(lái)實(shí)現(xiàn)聚簇索引

非聚簇索引存儲(chǔ)的是索引值和主鍵值

  • 普通索引一張表中可以有多個(gè)普通索引，隨便一個(gè)字段都可以建立的索引，我們平常建立的索引大部分都是普通索引

  • 聯(lián)合索引好幾個(gè)字段聯(lián)合起來(lái)建立的索引

  • 唯一索引業(yè)務(wù)中唯一的字段適合建立唯一索引，一個(gè)表中可以有多個(gè)唯一索引

  • 主鍵索引和唯一索引一樣，主鍵索引也是唯一的，不同的就是，一個(gè)表只能有一個(gè)主鍵索引

三、關(guān)于索引的sql

創(chuàng)建主鍵索引

ALTER?table?test?add??PRIMARY??KEY?(id)復(fù)制代碼

創(chuàng)建唯一索引

ALTER?TABLE?test?add?UNIQUE?idx_id_card(id_card)復(fù)制代碼

創(chuàng)建普通索引

ALTER?TABLE?test?add?INDEX?idx_name(name)復(fù)制代碼

創(chuàng)建聯(lián)合索引

ALTER?TABLE?test?add?INDEX?idx_age_name(age,name)復(fù)制代碼

修改索引名稱 ：先刪除再添加

刪除索引 （兩種方式）

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復(fù)制代碼

查看表中索引

SHOW?INDEX?FROM?test復(fù)制代碼

分析索引

EXPLAIN?select?*?from?test?WHERE?name?=?"xhJaver"復(fù)制代碼

我們先給name字段添加一個(gè)索引,索引名字叫做idx_name

ALTER?TABLE?test?add?INDEX?idx_name(name)復(fù)制代碼

查看test表中的索引

SHOW?INDEX?FROM?test復(fù)制代碼
mysql可以靠索引，而我只能靠打工….

其中的屬性

  • table: 表名

  • Non_unique: 能重復(fù)的話為1，不能重復(fù)的話為0，我們主鍵的那里是0，而name那里是1，因?yàn)閚ame可以重復(fù)，而主鍵不能重復(fù)

  • Key_name: 索引名稱

  • Seq_in_index:索引中列的順序

  • Column_name:列名稱

  • Collation:列以什么方式存儲(chǔ)的，A升序，NULL無(wú)序

  • Cardinality:數(shù)目越大，則使用該索引的可能性越大

  • Sub_part:如果列只是部分的編入索引，則被編入索引的字符數(shù)目，如果整列被編入索引，則為null

  • Packed：關(guān)鍵字是否被壓縮，null表示沒(méi)有被壓縮

  • Null:如果該列含有null,則為yes,如果沒(méi)有null,則為no

  • Index_type:索引數(shù)據(jù)結(jié)構(gòu)

  • Comment:多種評(píng)注

四、回表查詢 

select?*?from?test?where??name?=?"xhJaver"復(fù)制代碼

假如說(shuō)我們name字段建立了索引，然后當(dāng)我們運(yùn)行這一句sql語(yǔ)句的時(shí)候，因?yàn)榻⒌氖瞧胀ㄋ饕晕覀兊腷+樹(shù)的葉子節(jié)點(diǎn)存儲(chǔ)的數(shù)據(jù)是id，我們會(huì)找到name是xhJaver的這條記錄的id，再根據(jù)這個(gè)id,去主鍵索引的那棵b+樹(shù)去查詢，查詢到葉子節(jié)點(diǎn)時(shí)即查詢出這條記錄，可見(jiàn)這個(gè)過(guò)程中，我們從一棵樹(shù)跑到了另一棵樹(shù)繼續(xù)查，這樣就叫做“回表查詢”，那有沒(méi)有辦法只查一棵樹(shù)就可以查詢出結(jié)果呢？

五、覆蓋索引

辦法當(dāng)然是有的啦，那就是覆蓋索引，我們注意到，剛才這個(gè)sql語(yǔ)句時(shí)查詢出來(lái)了所有元素，假如說(shuō)我們這樣寫(xiě)的話

select?address?from?test?where??name?=?"xhJaver"復(fù)制代碼

假如說(shuō)我們建立的索引是（name,address）那么這個(gè)時(shí)候（name,address）這棵b+樹(shù)的葉子節(jié)點(diǎn)存儲(chǔ)的數(shù)據(jù)就包括address了，此時(shí)就不需要再根據(jù)name = “xhJaver”的id去第二棵樹(shù)查了，這樣就避免了回表查詢

六、最左匹配原則

假如說(shuō)現(xiàn)在我們寫(xiě)一個(gè)這樣的sql語(yǔ)句

select?*??from?test?where??name?=?"xhJaver"?and?age?=23??and?address="京東"復(fù)制代碼

并且我們建立的索引是（name,address,age）這樣是會(huì)用到（name,address,age）索引的，可是如果要這樣寫(xiě)的話

select?*??from?test?where??name?=?"xhJaver"?and?age?>23??and?address="京東"復(fù)制代碼

這樣只會(huì)用到（name，age）這兩個(gè)索引，從左邊開(kāi)始匹配，如果要是遇到范圍查詢的話，則不繼續(xù)往右匹配索引

七、explain分析索引語(yǔ)句

我們用explain語(yǔ)句解析一下下面這條sql語(yǔ)句

EXPLAIN?SELECT?*?from?test?WHERE?name?=?"xhJaver"復(fù)制代碼
mysql可以靠索引，而我只能靠打工….

它的屬性有

id: 執(zhí)行的順序

  • id相同時(shí)，順序從上到下執(zhí)行
  • id不同時(shí)，id大的先執(zhí)行

select_type: ?查詢的類型

  • primary: 最外層的查詢被標(biāo)記為primary
  • simple: ?簡(jiǎn)單查詢，沒(méi)有關(guān)聯(lián)其他表，就一張表
  • subquery: 在where或者select中的子查詢
  • derived: 衍生虛擬表 ?例如from(子查詢) t,這個(gè)子查詢的結(jié)果就被放在虛擬表t中

table: ?關(guān)于哪張表的

partitions: ?分區(qū)相關(guān)（還沒(méi)搞懂嗚嗚嗚）

type:訪問(wèn)類型

性能由好至壞依次是 system?>?const?>?eq_ref?>?ref?> fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >?range?>?index?>?ALL一般來(lái)說(shuō)，好的sql查詢至少達(dá)到range級(jí)別，最好能達(dá)到ref

  • system:表中只有一行數(shù)據(jù)

  • const:常量查詢 通常用于比較主鍵等于一個(gè)常量，用索引查詢一次就查到了

  • eq_ref:唯一性索引，每個(gè)索引對(duì)應(yīng)一條數(shù)據(jù)，例如主鍵索引

  • ref:非唯一索引，每個(gè)索引有可能對(duì)應(yīng)多行數(shù)據(jù)，例如普通索引

  • range: ?范圍查詢，用到了>，

  • index：全表掃描，但是是遍歷整棵索引樹(shù)

  • all:全表掃描，沒(méi)有用到索引

possible_keys：查詢的字段上有索引的話，就會(huì)顯示出來(lái)，

key : 具體用到的索引，若用到了覆蓋索引，則possible_keys為null,只會(huì)顯示在key中

key_len:索引中使用的字節(jié)數(shù)，最大可能長(zhǎng)度，并非實(shí)際長(zhǎng)度，key_len是根據(jù)表定義計(jì)算而得的，不是通過(guò)表內(nèi)檢索出的

ref: 表示使用索引的是哪一個(gè)字段

rows:大致估算出所需要讀取的行數(shù)

filtered:顯示了通過(guò)條件過(guò)濾出的行數(shù)的百分比估計(jì)值。

Extra:

  • using filesort?：? mysql無(wú)法利用索引完成的排序被稱為文件排序

  • Using temporary：? 使用臨時(shí)表存儲(chǔ)了下中間結(jié)果，mysql對(duì)查詢結(jié)果排序時(shí)是使用了臨時(shí)表，常見(jiàn)于order by 和 group by

  • Using index：使用了覆蓋索引，查詢內(nèi)容在索引內(nèi)

    1. 如果出現(xiàn)了Using where，表示對(duì)查詢出來(lái)的數(shù)據(jù)進(jìn)行了過(guò)濾
    2. 如果沒(méi)有出現(xiàn)Using where，表示對(duì)查詢出來(lái)的數(shù)據(jù)沒(méi)有進(jìn)行過(guò)濾

  • 只有Using where 查詢內(nèi)容不在索引內(nèi)，且對(duì)查出來(lái)的數(shù)據(jù)進(jìn)行了過(guò)濾

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")? 復(fù)制代碼

我們寫(xiě)幾個(gè)sql語(yǔ)句實(shí)際分析下 1.SELECT后面mysql可以靠索引，而我只能靠打工….2.where后面mysql可以靠索引，而我只能靠打工….

我們就拿后面這個(gè)圖來(lái)實(shí)戰(zhàn)分析一下,挑幾個(gè)重要的屬性說(shuō)一下

select_type：

  • 我們最外層的查詢是 from teacher 所以table為teacher的那個(gè)表的select_type就是primary

  • select/where后面的括號(hào)中的查詢語(yǔ)句中的表是student，所以table為student的那個(gè)表的select_type就是subquery

table: 這條sql查詢用到的表

type: 訪問(wèn)類型

  • 第一行const : ?teacher.id =巴拉巴拉巴拉（這個(gè)是常數(shù)）主鍵和常數(shù)比較時(shí)，這個(gè)表最多有一個(gè)匹配數(shù)據(jù)，只讀取一次

  • 第二行ref：代表用到了普通索引，就是這個(gè)索引name和xhJaver匹配,可能匹配到很多相同的值

possible_key: 代表可能用到的索引，但是不一定會(huì)用到

key: 代表用到的索引， 用到了idx_name，PRIMARY索引

ref: 這一列顯示了在key列記錄的索引中，表查找值所用到的列或常量， 常見(jiàn)的有：const，字段名

extra：

  • using index: 一般是使用了覆蓋索引，看我們這個(gè)sql語(yǔ)句，
select?student.id?from?student?WHERE?student.`name`="xhJaver"復(fù)制代碼

name字段有索引，查詢的是id，b+樹(shù)葉子節(jié)點(diǎn)存的數(shù)據(jù)就是id，所以不需要回表查詢了，用到了覆蓋索引

八、索引失效原因

  1. 遇到范圍查詢（>,

  2. 索引字段不能有函數(shù)操作或者不能是表達(dá)式的一部分

  3. 索引字段隱式類型轉(zhuǎn)換 索引字段類型是String，我們傳進(jìn)來(lái)個(gè)int

  4. 使用時(shí)or,is null ,is not null , !=, , like “%xxx” 索引會(huì)失效

但是用覆蓋索引就可以解決 like左模糊查詢走不到索引的情況 如果只select索引字段，或者select索引字段和主鍵，也會(huì)走索引的。

更多相關(guān)免費(fèi)學(xué)習(xí)推薦：mysql教程(視頻)

以上就是

? 版權(quán)聲明
THE END
數(shù)據(jù)庫(kù)
# mysql# sql# select# word# 數(shù)據(jù)結(jié)構(gòu)# table# NULL# String# int# 類型轉(zhuǎn)換# auto# 常量# overflow# position# display# background# li# ul# using# const# padding# border# margin# break# tr# 隱式類型轉(zhuǎn)換# td# !important# th
喜歡就支持一下吧
點(diǎn)贊15 分享