MySQL 什麼是索引?

来源:https://www.cnblogs.com/hhhhuanzi/archive/2020/02/14/12308534.html

該文為《 MySQL 實戰 45 講》的學習筆記,感謝查看,如有錯誤,歡迎指正 一、索引簡介 索引就類似書本的目錄,作用就是方便我們更加快速的查找到想要的數據。 索引的實現方式比較多,常見的有 ,`有序數組 搜索樹`。 1.1 哈希表 是將數據以 的形式存儲起來,簡單來說就是將 通過哈希函數換算成數 ...


該文為《 MySQL 實戰 45 講》的學習筆記,感謝查看,如有錯誤,歡迎指正

一、索引簡介

索引就類似書本的目錄,作用就是方便我們更加快速的查找到想要的數據。

索引的實現方式比較多,常見的有哈希表有序數組搜索樹

1.1 哈希表

哈希表是將數據以key-value的形式存儲起來,簡單來說就是將key通過哈希函數換算成數組中的一個確定的位置,將value存到這個位置去。當key比較多時,有可能換算出相同的位置,此時可以通過鏈表來解決。在查詢時先找到位置,再對該位置的多個value進行遍歷。

哈希表適合用於等值查詢,由於是無序的,不適合用來做區間查詢。

1.2 有序數組

有序數組在等值查詢和區間查詢上效率都很高。由於是有序的,可以通過二分法快速得到結果。也支持範圍查詢。但是也有一個缺點,如果要在中間插入一個數據,那麼後面的所有記錄都要向後挪一位,成本太高了。

因此,有序數組只適用於靜態存儲引擎。 例如我們要保存2019年的出生人口信息,就適合用有序數組。

1.3 搜索樹

常見的搜索樹有二叉,也有多叉

二叉樹的特點是:

  • 每個節點的左兒子小於父節點,父節點又小於右兒子。

多叉樹的特點是:

  • 每個節點有多個兒子,兒子之間的大小保證從左到右遞增。

由於索引不止存在記憶體中,還會寫到磁碟上,而讀磁碟越多,查詢效率越慢。要降低讀磁碟的次數的話,就要儘量訪問儘量少的數據塊。

假設數據塊大小是N,樹高為M,最多可以存的數據行數為 N^(M-1)NM-1 次方)。最多訪問磁碟數為 M-1

要使樹高比較小,訪問次數就少,N叉樹的樹高就小於二叉樹。以 InnoDB 的一個整數欄位索引為例,這個 N 差不多是 1200,這棵樹高是 4 的時候,就可以存 1200 的 3 次方個值,這已經 17 億行記錄了。一個 10 億行的表上一個整數欄位的索引,查找一個值最多只需要訪問 3 次磁碟。

資料庫底層存儲的核心就是基於這些數據模型的。每碰到一個新資料庫,我們需要先關註它的數據模型,這樣才能從理論上分析出這個資料庫的適用場景


二、InnoDB 的索引模型
  • 在 InnoDB 中,表都是根據主鍵順序以索引的形式存放的,這種存儲方式的表稱為索引組織表
  • InnoDB 使用了 B+ 樹索引模型,所以數據都是存儲在 B+ 樹中的。

因此,每一個索引在 InnoDB 裡面對應一棵 B+ 樹。

2.1 索引分類

根據欄位約束,分為主鍵索引普通索引;根據欄位內容是否可重覆,分為唯一索引非唯一索引

  • 主鍵索引
    主鍵是一種約束,一個表中只能有一個主鍵;
    主鍵可以是多個列;
    主鍵可以被其它表引用為外鍵使用;
    主鍵索引可以理解為非空欄位+唯一索引
    主鍵索引的葉子節點存的是整行數據。

  • 普通索引(二級索引)
    一個表中可以有多個普通索引;索引可以有多列;
    普通索引的葉子節點內容是主鍵的值;

  • 唯一索引
    欄位內容不能重覆,但是可以為空;
    一個表中可以有多個唯一索引;
    不能做外鍵使用;

  • 非唯一索引
    欄位內容允許重覆;

下麵以表為例,建表語句:

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分別為 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6),兩棵樹的示例示意圖如下:
在這裡插入圖片描述
id欄位為主鍵索引主鍵索引的欄位是不會重覆的,必定是唯一索引
k欄位為普通索引k的值允許重覆,因此是非唯一索引


2.2 回表操作

分析下麵 2 條 SQL 語句:

  1. select * from T where ID=500。此時用到的是主鍵索引,因此直接從索引中返回了整行記錄,只需要搜索ID這棵 B+ 樹。
  2. select * from T where k=5。此時用到的是普通索引,需要先搜索 k索引樹,得到ID = 500 ,再根據500ID索引樹搜索一次。這種需要返回主鍵索引樹搜索的過程,叫做回表。

以上兩條 SQL 語句返回的結果是一樣的,但是效率卻不一樣,因為第 2 條 SQL 語句有一次回表操作,效率會慢很多,因此,要儘量避免回表操作,多使用主鍵查詢

2.3 頁的分裂與合併

還是以上表為例,如果我們要插入一個數據,ID 值為 700,則只需要在 R5 後面新增加 1 條記錄即可。如果插入的值 ID 為 400,那就需要邏輯上挪動後面的數據,空出位置。

如果恰好 R5 所在的數據頁已經滿了,那麼就需要申請一個新的數據頁,並且將 R5 挪過去,這個情況就叫做頁分裂

數據頁中並不是要利用率達到 100% 才會申請新的數據頁。也不是說只要有數據刪除,那麼後一頁的數據就會順補到前一頁,這樣太浪費性能了。數據頁有一個利用率,假設分裂是80%,合併是 50%。只要利用率達到了 80%,就會申請一個新的數據頁。如果刪除數據比較多,利用率低於 50% 了,就會把後一頁的數據合併過來。

如何避免頁分裂造成的性能消耗?常見做法是在表中,設置一個自增長的 id 主鍵,這個欄位不能和業務相關。自增主鍵的定義:NOT NULL PRIMARY KEY AUTO_INCREMENT

這樣每次插入數據,如果不指定 id 值,就會自增長到最後,因為和業務無關,所以沒必要去指定 id 值。這樣可以避免出現頁分裂。


三、索引的一些特點
3.1 覆蓋索引

還是以上表為例,執行以下 SQL 語句,分析執行過程:

mysql> select * from T where k between 3 and 5;
  1. 在普通索引k上遍歷,得到k=3對應的 ID300
  2. 通過 ID=300 去主鍵索引上取得整行記錄R3
  3. 繼續向後遍歷k,得到k=5對應的 ID500
  4. 通過 ID=500 去主鍵索引上取得整行記錄R5
  5. 繼續向後遍歷k,發現k=6,不滿足between條件,迴圈結束。

可以看到,這個過程讀了k索引樹的 3 條記錄(步驟 1,3,5), 回表了2次(步驟2,4)。

如果我們換成以下 SQL 語句:

mysql> select ID from T where k between 3 and 5;

由於 ID已經在k索引樹上了,因此可以直接返回結果,不用回表。這種索引中已經覆蓋了我們要查詢的數據,叫做覆蓋索引

覆蓋索引可以減少樹的搜索次數(沒有回表過程),顯著提高查詢性能。

3.2 關於掃描行數

MySQL 認為上述操作掃描的行數是 2 行,因為在索引中查數據,是在引擎層的操作。而 Server 層最後只拿到了 2 條記錄,因此 MySQL 認為只掃描了 2 行。

那麼如何看掃描函數呢?有 2 種方法:

  1. 使用explain查看預計掃描行數
mysql> explain select * from t where a between 1000 and 2000;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t     | range | a             | a    | 5       | NULL | 1000 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.01 sec)

mysql>

可以看到使用了索引 key=a,預計掃描行數rows=1000

  1. 將慢日誌記錄時間設置為 0 ,直接在慢日誌中查看掃描行數
# Time: 191228 13:03:16
# [email protected]: federated[federated] @  [60.191.76.22]  Id:   177
# Query_time: 31.211439  Lock_time: 0.000059 Rows_sent: 0  Rows_examined: 95324
SET timestamp=1577509396;
CALL Z10004();

可以看到,掃描行數為Rows_examined: 95324

3.3 最左首碼原則

舉一個例子來理解最左首碼原則,假設有一個聯合索引(name,age)如下:
在這裡插入圖片描述
可以看到,索引順序先按照第一個欄位排序,再按照第二個欄位。

假設我們要查詢所有名為張三的數據。可以快速定位到ID4,再依次向後遍歷。如果要查詢所有姓張(where name like '張%'),也能用到索引,先定位到ID3,再依次向後遍歷,直到不滿足條件為止。

不只是索引的全部定義,只要滿足最左首碼,就可以利用索引來加速檢索。這個最左首碼可以是聯合索引的最左 N 個欄位,也可以是字元串索引的最左 M 個字元。

在建立聯合索引時,如何確定欄位的前後順序呢?

  • 第一原則,如果通過調整順序,可以少維護一個索引,那麼這個順序往往就是需要優先考慮採用的。
    比如,已經有了一個(a, b)索引,就不必再建立一個 a 索引了。

  • 考慮磁碟空間占用大小。
    比如,(name, age) 索引加上 age 索引,和 (age, name) 索引加上 name 索引。這兩種情況,我們就要考慮占用空間了。選擇占用空間小的。
    由於name 欄位比 age 欄位大,因此我們選擇(name, age) 索引加上 age 索引。

3.4 索引下推

索引下推功能是在 MySQL 5.6 引入的,目的是減少回表次數。

還是以市民表的聯合索引(name, age)為例。如果現在有一個需求:檢索出表中“名字第一個字是張,而且年齡是 10 歲的所有男孩”。那麼,SQL 語句是這麼寫的:

mysql> select * from tuser where name like '張%' and age=10 and ismale=1;
  • 沒有索引下推
    先定位到ID3,然後回表到主鍵索引,找出對應的數據行,判斷是否符合and age=10 and ismale=1。最終要回表 4 次(ID3,ID4,ID5,ID6),返回的結果只有 ID4,ID5。
    在這裡插入圖片描述
  • 索引下推
    在回表之前,會先判斷這個聯合索引上的後續欄位是否滿足條件,不滿足則不進行回表操作。最終只用回表 2 次。
    在這裡插入圖片描述


感謝閱讀,有興趣的小伙伴可以關註我的微信公眾號DevOps探索之旅,大家一起學習進步


您的分享是我們最大的動力!

更多相關文章
  • 學習劉鐵猛老師《C#語言入門詳解》視頻,針對其中重點知識點進行總結。 1、什麼是類型? 類型又稱為數據類型(Data Type),數據類型在數據結構中的定義是一個值的集合以及定義在這個值集上的一組操作。 可以簡單理解為數據在記憶體中存儲的“型號”;小記憶體容納大尺寸數據會丟失精準度,發生錯誤;而大記憶體容 ...
  • 在WPF用戶界面中,繪製2D圖形內容的最簡單方法是使用形狀(shape)——專門用於表示簡單的直線、橢圓、矩形以及多變形的一些類。從技術角度看,形狀就是所謂的繪圖圖元(primitive)。可組合這些基本元素來創建更複雜的圖形。 關於WPF中形狀的重要細節是,它們都繼承自FrameworkEleme ...
  • Blend 修改TreeViewItem樣式 1、用Blend for Visual Studio 2019 新建Wpf項目,拖動一個TreeView控制項到Grid上 2、在繪圖視窗選中TreeViewItem,右鍵編輯模版 編輯副本 3、繪製水平、垂直虛線( "參考博文" ) 在TreeViewI ...
  • ASPNetCore 發佈到IIS 準備工作 1.1. 安裝IIS。(具體操作不再說明) 安裝成功後再瀏覽器輸入localhost得到的頁面如下 1.2. 安裝dotnet-hosting-2.2.2-win.exe安裝成功後在IIS 中可以看到如下兩個程式 這兩個程式對應得NetCore的版本不一 ...
  • 1.Ctrl+s:快速保存代碼 一定要記得隨時隨地用 Ctrl+s 來保存我們的代碼哦!!!不然等到電腦關機或者是使用的Eclipse突然閃退就欲哭無淚了。此時腦海裡就突然出現了嗶嗶嗶的畫面~ 2.Alt+/:自動補全代碼或者提示代碼後半部分 牆裂推薦大家使用啊,真的是超級好用了。 給大家舉一個例子 ...
  • 在看 apue 第 19 章偽終端第 6 節使用 pty 程式時,發現“檢查長時間運行程式的輸出”這一部分內容的實際運行結果,與書上所說有出入。 於是展開一番研究,最終發現是書上講的有問題,現在摘出來讓大家評評理。 先上代碼 pty.c pty_fun.c 這是書上標準的 pty 程式,簡單說起來就 ...
  • 背景介紹 我們在工作中難免會寫一些重覆性的代碼,所以需要我們具備一定的抽象能力,比如把共同的邏輯抽取到抽象類中,也可以通過一些工具類來避免冗餘代碼 今天這篇文章就是把一個調用服務的重試功能抽取出一個工具類,以備復用。這裡為了方便介紹,把調用服務簡化成方法的調用,被調用的 foo 方法如下: ~~~ ...
  • 1 SQL 的哲學 形如 Linux 哲學一切都是文件,在 SQL 領域也有這樣一條至理名言 2 關係資料庫 所謂關係資料庫(Relational database)是創建在關係模型基礎上的資料庫,藉助於集合代數等數學概念和方法來處理資料庫中的數據。 現實世界中的各種實體以及實體之間的各種聯繫均用關 ...
一周排行
  • 枚舉是 C 中最有意思的一部分,大部分開發人員只瞭解其中的一小部分,甚至網上絕大多數的教程也只講解了枚舉的一部分。那麼,我將通過這篇文章向大傢具體講解一下枚舉的知識。我將從大家都瞭解的部分開始講解,然後再講解大家所不知道的或者瞭解很少的部分。 零、基礎知識 枚舉是由開發人員聲明的一種 值類型 ,它在 ...
  • 一. elasticsearch on windows 1.下載地址: https://www.elastic.co/cn/downloads/elasticsearch 如果瀏覽器下載文件慢,建議使用迅雷下載,速度很快。下載版本為7.5.2 2. 修改配置文件 下載後解壓,找到config\jvm ...
  • 最近因為” 新冠” 疫情在家辦公,學習了 ASP.NET Core MVC 網站的一些知識,記錄如下。 ...
  • Regex.Replace("<!--(.|[\r\n])*?-->",string.Empty) ...
  • 本筆記摘抄自:https://www.cnblogs.com/PatrickLiu/p/7743118.html,記錄一下學習過程以備後續查用。 一、引言 今天我們要講結構型設計模式的第四個模式--組合模式。當我們談到這個模式的時候,有一個物件和這個模式很像,那就是“俄羅斯套娃”。“俄羅斯套娃”是 ...
  • 一、前言 Entity Framework(後面簡稱EF)作為微軟家的ORM,自然而然從.NET Framework延續到了.NET Core。 二、程式包管理器控制台 為了能夠在控制臺中使用命令行來操作EF,需要先安裝Microsoft.EntityFrameworkCore.Tools。 安裝 ...
  • 項目gitHub地址 點我跳轉 今天給大家帶來一個C#裡面的時間工具類,具體的直接看下麵代碼 1 using System; 2 3 namespace ToolBox.DateTimeTool 4 { 5 public static class DateTimeExtend 6 { 7 /// < ...
  • 《C# 6.0 本質論》 [作者] (美) Mark Michaelis (美) Eric Lippert[譯者] (中) 周靖 龐燕[出版] 人民郵電出版社[版次] 2017年02月 第5版[印次] 2017年02月 第1次 印刷[定價] 108.00元 【前言】 成功學習 C# 的關鍵在於,要盡 ...
  • 本筆記摘抄自:https://www.cnblogs.com/PatrickLiu/p/7772184.html,記錄一下學習過程以備後續查用。 一、引言 今天我們要講結構型設計模式的第五個模式--外觀模式。先從名字上來理解一下外觀模式,當看到“外觀”這個詞時,很容易想到“外表”這個詞語,兩者有著 ...
  • 在 C 中存在一個名叫靜態類型檢查的機制,這個機制可以讓編譯器幫助我們把類型不服的用法找出來,從而使得應用程式在運行期間加少一些類型檢查的操作。但是有時候我們還是需要進行運行期類型檢查,比如我們在設計框架時將方法的參數類型定義為 object ,那麼這時我們就有很大的可能需要將 object 類型的 ...
x