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

這篇文章給大家分享的是有關mysql可以靠索引，而我只能靠打工....的內容。小編覺得挺實用的，因此分享給大家做個參考。一起跟隨小編過來看看吧。

一、  索引數據結構

面試的時候肯定會問這一個問題，mysql為什么會選擇b+樹作為索引呢？而不選擇其他索引，例如b樹？hash？

下面說的磁盤IO是指數據從硬盤加載到內存中的操作

• hash索引的話，不支持范圍查詢，因為hash就是一個鍵對應一個值的，沒辦法范圍查詢

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

• 平衡二叉樹的話，它就沒有了二叉樹的這種退化成鏈表的缺點，因為他左右子節點最多相差1層，可是他也不支持范圍查找這一點和二叉樹的問題一樣

• b樹的話，和二叉樹比起來樹是很矮胖，IO操作減少了，是個多叉樹，它每個節點都存了對應的行數據，可是如果這一行的數據的列不斷的增加，那么這一頁存儲的節點就會變少，因為所占的空間不斷的變大，樹也會越來越高，增加IO操作次數，同時是也不支持范圍查找。要是相同大小的空間可以存很多的節點數據的話就更好了，所以就有了下面的b+樹

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

二、 mysql中索引類型

• 聚簇索引與非聚簇索引

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

本質的區別是

聚簇索引的葉子節點存儲的是整行數據

innodb是通過主鍵來實現聚簇索引的，如果沒有主鍵的話，那么他就會選擇一個唯一非空的索引來實現，如果再沒有的話，他就會隱式生成一個主鍵來實現聚簇索引

非聚簇索引存儲的是索引值和主鍵值

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

• 聯合索引好幾個字段聯合起來建立的索引

• 唯一索引業務中唯一的字段適合建立唯一索引，一個表中可以有多個唯一索引

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

三、關于索引的sql

創建主鍵索引

ALTER TABLE test add  PRIMARY  KEY (id)復制代碼

創建唯一索引

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

創建普通索引

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

創建聯合索引

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: 能重復的話為1，不能重復的話為0，我們主鍵的那里是0，而name那里是1，因為name可以重復，而主鍵不能重復

• Key_name: 索引名稱

• Seq_in_index:索引中列的順序

• Column_name:列名稱

• Collation:列以什么方式存儲的，A升序，null無序

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

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

• Packed：關鍵字是否被壓縮，null表示沒有被壓縮

• Null:如果該列含有null,則為yes,如果沒有null,則為no

• Index_type:索引數據結構

• Comment:多種評注

四、回表查詢

select * from test where  name = "xhJaver"復制代碼

假如說我們name字段建立了索引，然后當我們運行這一句sql語句的時候，因為建立的是普通索引，所以我們的b+樹的葉子節點存儲的數據是id，我們會找到name是xhJaver的這條記錄的id，再根據這個id,去主鍵索引的那棵b+樹去查詢，查詢到葉子節點時即查詢出這條記錄，可見這個過程中，我們從一棵樹跑到了另一棵樹繼續查，這樣就叫做“回表查詢”，那有沒有辦法只查一棵樹就可以查詢出結果呢？

五、覆蓋索引

辦法當然是有的啦，那就是覆蓋索引，我們注意到，剛才這個sql語句時查詢出來了所有元素，假如說我們這樣寫的話

select address from test where  name = "xhJaver"復制代碼

假如說我們建立的索引是（name,address）那么這個時候（name,address）這棵b+樹的葉子節點存儲的數據就包括address了，此時就不需要再根據name = "xhJaver"的id去第二棵樹查了，這樣就避免了回表查詢

六、最左匹配原則

假如說現在我們寫一個這樣的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）這兩個索引，從左邊開始匹配，如果要是遇到范圍查詢的話，則不繼續往右匹配索引

七、explain分析索引語句

我們用explain語句解析一下下面這條sql語句

EXPLAIN SELECT * from test WHERE name = "xhJaver"復制代碼

它的屬性有

id: 執行的順序

• id相同時，順序從上到下執行
• id不同時，id大的先執行

select_type:  查詢的類型

• primary: 最外層的查詢被標記為primary
• simple:  簡單查詢，沒有關聯其他表，就一張表
• subquery: 在where或者select中的子查詢
• derived: 衍生虛擬表  例如from(子查詢) t,這個子查詢的結果就被放在虛擬表t中

table:  關于哪張表的

partitions:  分區相關（還沒搞懂嗚嗚嗚）

type:訪問類型

性能由好至壞依次是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般來說，好的sql查詢至少達到range級別，最好能達到ref

• system:表中只有一行數據

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

• eq_ref:唯一性索引，每個索引對應一條數據，例如主鍵索引

• ref:非唯一索引，每個索引有可能對應多行數據，例如普通索引

• range:  范圍查詢，用到了>，<，in,between等查詢

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

• all:全表掃描，沒有用到索引

possible_keys：查詢的字段上有索引的話，就會顯示出來，

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

key_len:索引中使用的字節數，最大可能長度，并非實際長度，key_len是根據表定義計算而得的，不是通過表內檢索出的

ref: 表示使用索引的是哪一個字段

rows:大致估算出所需要讀取的行數

filtered:顯示了通過條件過濾出的行數的百分比估計值。

Extra:

• Using filesort ：  mysql無法利用索引完成的排序被稱為文件排序

• Using temporary：  使用臨時表存儲了下中間結果，mysql對查詢結果排序時是使用了臨時表，常見于order by 和 group by

• Using index：使用了覆蓋索引，查詢內容在索引內

  1. 如果出現了Using where，表示對查詢出來的數據進行了過濾
  2. 如果沒有出現Using where，表示對查詢出來的數據沒有進行過濾

• 只有Using where 查詢內容不在索引內，且對查出來的數據進行了過濾

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語句實際分析下 1.SELECT后面2.where后面

我們就拿后面這個圖來實戰分析一下,挑幾個重要的屬性說一下

select_type：

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

• select/where后面的括號中的查詢語句中的表是student，所以table為student的那個表的select_type就是subquery

table: 這條sql查詢用到的表

type: 訪問類型

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

• 第二行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+樹葉子節點存的數據就是id，所以不需要回表查詢了，用到了覆蓋索引

八、索引失效原因

1. 遇到范圍查詢（>,<,like,beetwon），右邊的索引列會失效

2. 索引字段不能有函數操作或者不能是表達式的一部分

3. 索引字段隱式類型轉換 索引字段類型是string，我們傳進來個int

4. 使用時or,is null ,is not null , !=, <>, like "%xxx" 索引會失效

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

感謝各位的閱讀！關于mysql可以靠索引，而我只能靠打工....就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！