中国体彩网唯一官网
首頁 > 數據庫 > mysql教程 > 正文

MySql索引那些事

轉載 2019-04-04 16:46:50 0 986
贊助會員專享特權
今天我們來聊一聊MySql索引的那些事,在這篇文章中,我會主要聊聊InnoDB下索引的數據結構,索引如何起作用的,如何更好的利用索引提高效率。

169cf0d7b4f4d8e5.jpg

一、什么是索引

數據庫索引,是數據庫管理系統中一個排序的數據結構,以協助快速查詢、更新數據庫表中數據。就像我們以前用的新華字典的目錄一樣,能幫助我們快速查詢到某一個字。

二、索引的分類

分類角度索引名稱
數據結構B+樹,Hash索引,R-Tree等
存儲層面

聚簇索引,非聚簇索引

邏輯層面主鍵索引,普通索引,復合索引,唯一索引,空間索引等

三、索引實例分析(以InnoDB為例)

3.1 InnoDB下索引的結構

InnoDB下,表都是根據主鍵順序以索引的形式存放的,這種數據存儲方式也被稱為聚簇索引,“聚簇”就是表示數據行和相鄰的鍵值緊湊的存儲在一起,也就是數據行實際上是存儲在索引的葉子頁中。我們創建一張表來實際說明下InnoDB下的索引結構,建表語句如下:

create table person(id int primary key, 
age int not nullindex (age)engine=InnoDB;

然后我們插入五條數據分別為(1,15),(2,17),(6,20),(10,18),(19,21),索引的樹結構如下:

1554366940949179.jpg

上圖中展示了兩部分內容,第一個圖為聚簇索引(主鍵索引)的內容,可以看到,數據按照Id的大小排序,對應的索引會包含該索引的整行數據。

第二個圖展示了用age做索引的索引結構圖,也就是非聚簇索引(非主鍵索引),可以看到索引以年齡排序,但是和主鍵索引不同的是,年齡索引對應的卻是Id,所以我們可以知道非主鍵索引記錄的內容就是主鍵索引的值。

這里可能有同學會有疑問,如果我建表的時候沒有指定主鍵的話,索引結構又是如何的呢?其實在InnoDB中,如果沒有定義主鍵,那么他會選擇一個唯一的非空索引代替。如果沒有這樣的索引,那么他會隱式的定義一個主鍵來作為聚簇索引。所以無論你是否設置主鍵,InnoDB還是會幫你滿足以上圖的形式來索引數據。接下來我們分析下索引查詢的流程。

3.2 索引查詢分析

假設我們執行一條查詢語句 select * from person where ID = 6,因為直接使用的是主鍵ID查詢,所以就會用主鍵索引,由于主鍵索引直接關聯了整行所有數據,所以,引擎只要執行一次就能查詢出結果。

如果執行的sql語句是非主鍵索引

select * from person where age = 18

從普通索引查出主鍵索引,然后查詢出數據的過程叫做回表。由于回表需要多執行一次查詢,這也是為什么主鍵索引要比普通索引要快的原因,所以,我們要盡量使用主鍵查詢。上述語句會走age的普通索引,索引先根據age搜索等于18的索引記錄,找到ID=10的記錄,然后再到主鍵索引搜索一次,然后拿出需要查詢的數據。

3.3 覆蓋索引

我們通常創建索引的依據都是根據查詢的where條件,但是這只是我們通常的做法,我們根據上面的分析可以知道,如果要想查詢效率高,第一,使用主鍵索引,第二,避免回表,也就是盡可能的在索引中就能獲取想要的數據。如果一個索引包含了需要查詢的字段,那么我們就叫做“覆蓋索引”。

那么如何建立一個覆蓋索引呢?答案是通過聯合索引來實現,通過聯合索引的字段來覆蓋要查詢的字段,從而達到索引覆蓋的效果。

我們把上面的建表語句改造下,來分析下如何實現覆蓋索引。

 CREATE TABLE `person` (  `id` int(11) NOT NULL,  `age` int(11) DEFAULT NULL,  `name` varchar(20) DEFAULT NULL,  `sex` varchar(1) DEFAULT NULL,

上面我創建了一個name和age的聯合索引,索引結構圖表示如下:

1554367112856391.jpg

我們根據圖可以知道,聯合索引是和創建索引字段順序有關的,上面這個例子就是先以name排序,然后name相同再以age為標準排序。那么我們建表后該如何達到覆蓋索引的效果呢?相信有些同學已經知道了怎么寫sql可以達到覆蓋索引效果,sql如下:

select name,age from person where name = "Barry"

因為我們需要查詢的字段name和age,都在索引中可以直接查詢到了,所以不需要查找到主鍵ID,然后再回表了。

看到這里,肯定有同學會說,既然這樣的話,我把所有需要查詢的字段組合都建上聯合索引不就行了嗎?答案是:不行。因為索引也是需要消耗空間的,而且維護索引也是需要成本的,這一點我會在后面的優缺點中提到。那么有沒有別的方式可以盡可能的實現不回表的效果呢?這里我們就要引入MySql的最左前綴原則了。

什么叫最左前綴原則呢?就是在索引的匹配中,可以以索引的最左N個字段,也可以是字符串索引的最左N個字符。比如在上圖中,要查詢以A開頭的名字,查詢語句就是

select name from person where name like 'A%'

這個時候就可以滿足最左前綴規則來使用索引查詢了,這里就會依賴索引查詢到第一個首字母是A的名字,然后向后遍歷,直到不滿足條件為止。

那么最左N個字段是什么意思呢?意思就是索引(name,age),可以直接利用 name來當做單獨索引使用,可以只使用聯合索引的部分字段,但是必須是順序一致,比如索引(a,b,c),如果要想使用最左前綴規則,可以使用索引a,ab。

我們也可以利用該規則來少維護一個或多個索引,比如我們需要 a,ab,abc的查詢,那就只需要(a,b,c)聯合索引就滿足要求了。

3.4 索引下推

在MySql 5.6版本中引入了一個新特性,叫做“索引條件推送(index condition pushdown)”,這也稱為索引下推。那么索引下推是這個什么東東呢?其實從“索引條件推送”這個名字就可以表明,這個特性是可以在索引中的字段進行條件判斷,然后過濾不滿足條件的記錄,減少回表的次數。

比如以上圖中的數據為準,sql如下:

select * from person where name like 'A%' and age =19;

那么如果沒有索引下推的情況下,首先會根據索引查詢出名字以A開頭的所有記錄,然后查詢出ID,然后回表去查詢對應的ID記錄,最后再判斷age=19,返回滿足條件的語句。因為滿足A開頭的記錄有2條,所以這種情況下,會回表2次。

在索引下推情況下,InnoDB會在索引內部直接判斷age=19是否滿足條件,過濾掉不滿足條件的記錄,所以只返回了一條,也就是只需要回表一次。從而提高了性能。

3.5 索引的優點與缺點

說了這么多關于索引的內容,我們來談談索引的優缺點。

優點:

減少服務器需要掃描的數據量索引可以幫助服務器避免排序和臨時表索引可以將隨機IO變為順序IO

缺點

索引會占用額外的存儲空間索引的維護需要一定的成本,插入數據后需要保證原來的索引有序,所以也會影響一定的數據庫性能。

五、總結

這篇博文我主要說了,索引的定義,索引的分類,索引按照不同的角度可以分為常見的哪幾種。然后我重點說了在InnoDB下索引的索引的數據結構。 主鍵索引和非主鍵索引的區別就是查詢主鍵索引可以直接返回數據,非主鍵索引需要先查詢出主鍵ID,然后再查詢出數據,這個過程就叫做回表。我們可以通過覆蓋索引減少回表的次數,從而達到提高性能的效果。在mysql5.6以后,InnoDB可以支持索引下推,在使用聯合索引的時候,如果可以在索引判斷條件,那么就在索引中過濾不滿足條件的行,從而減少回表次數。

六、參考

《高性能MySql》第3版

《MySql45講》專欄


【推薦課程:MySQL視頻教程

以上就是MySql索引那些事的詳細內容,更多請關注php中文網其它相關文章!

php中文網最新課程二維碼
  • 相關標簽:mysql 數據庫
  • 本文轉載于:開源中國,如有侵犯,請聯系刪除
  • 相關文章


  • MySQL中EXPLAIN解釋命令的介紹(附示例)
  • mysql索引原理是什么?
  • MySQL執行過程以及查詢緩存的詳細介紹
  • MySQL Workbench如何導出查詢結果?(圖文)
  • Mysql、SqlServer、Oracle三大數據庫的區別
  • MySQL中十進制轉八進制的實現方法
  • MySql索引那些事
  • 網友評論

    文明上網理性發言,請遵守 新聞評論服務協議

    我要評論
    獨孤九賤(5)_ThinkPHP5視頻教程

    獨孤九賤(5)_ThinkPHP5視頻教程

    ThinkPHP是國內最流行的中文PHP開發框架,也是您Web項目的最佳選擇。《php.cn獨孤九賤(5)-ThinkPHP5視頻教程》課程以ThinkPHP5最新版本為例,從最基本的框架常識開始,將...

    獨孤九賤(4)_PHP視頻教程

    獨孤九賤(4)_PHP視頻教程

    江湖傳言:PHP是世界上最好的編程語言。真的是這樣嗎?這個梗究竟是從哪來的?學會本課程,你就會明白了。 PHP中文網出品的PHP入門系統教學視頻,完全從初學者的角度出發,絕不玩虛的,一切以實用、有用...

    獨孤九賤(1)_HTML5視頻教程

    獨孤九賤(1)_HTML5視頻教程

    《php.cn原創html5視頻教程》課程特色:php中文網原創幽默段子系列課程,以惡搞,段子為主題風格的php視頻教程!輕松的教學風格,簡短的教學模式,讓同學們在不知不覺中,學會了HTML知識。 ...

    ThinkPHP5實戰之[教學管理系統]

    ThinkPHP5實戰之[教學管理系統]

    本套教程,以一個真實的學校教學管理系統為案例,手把手教會您如何在一張白紙上,從零開始,一步一步的用ThinkPHP5框架快速開發出一個商業項目。

    PHP入門視頻教程之一周學會PHP

    PHP入門視頻教程之一周學會PHP

    所有計算機語言的學習都要從基礎開始,《PHP入門視頻教程之一周學會PHP》不僅是PHP的基礎部分更主要的是PHP語言的核心技術,是學習PHP必須掌握的內容,任何PHP項目的實現都離不開這部分的內容,通...

    作者信息

    little bottle

    認證0級講師

    最近文章
    ps圖片怎么保存ai格式 513
    psd格式用什么軟件打開 454
    eclipse ide是什么 875

    相關視頻教程

  • MySQL開發技巧(一)視頻教程 MySQL開發技巧(一)視頻教程
  • 數據庫mysql視頻教程 數據庫mysql視頻教程
  • MySql參考手冊 MySql參考手冊
  • MySQL權威開發指南(教程) MySQL權威開發指南(教程)
  • MySQL數據庫技術小知識每日分享 MySQL數據庫技術小知識每日分享
  • MySQL高級進階視頻教程 MySQL高級進階視頻教程
  • 相關視頻章節

    中国体彩网唯一官网