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)是創建在關係模型基礎上的資料庫,藉助於集合代數等數學概念和方法來處理資料庫中的數據。 現實世界中的各種實體以及實體之間的各種聯繫均用關 ...
一周排行
  • 一、引言 按照專用隊列解釋: MachineName\Private$\QueueName,只針對於本機的程式才可以調用的隊列,有些情況下為了安全起見定義為私有隊列。所以剛開始的時候認為,要想訪問遠程消息隊列,只能使用公共隊列。但是後來發現,公共隊列依賴Domain Controller(域控),在 ...
  • 本文只對api介面,header請求參數進行簡單驗證,起到拋磚引玉使用,需要深入驗證,請自行擴展 項目目錄結構如圖 中間件類 using ApiMiddleware.Common.DataEnityModel; using ApiMiddleware.Common.DbContext; using ...
  • 前言:由於公司占時沒有運維,出於微服務的需要,Apollo只能先裝在windows 阿裡雲上跑起來,由於環境及網路等問題,在安裝過程中遇到很多坑,算是一個個坑填完後,最終實現。 一. java jdk環境 java jdk 1.8下載地址: https://www.oracle.com/java/t ...
  • 前言 nuget 是 .net 的常用包管理器,目前已經內置到 Visual Studio 2012 以後的版本。大多數 .net 包都托管在 nuget.org,包括 .net core 框架基礎包,得益於 .net core 的模塊化設計,很多非核心包都可以進行一定程度的獨立升級。 製作並上傳 ...
  • 簡單的介紹一下集合,通俗來講就是用來保管多個數據的方案。比如說我們是一個公司的倉庫管理,公司有一堆貨物需要管理,有同類的,有不同類的,總而言之就是很多、很亂。我們對照集合的概念對倉庫進行管理的話,那麼 數組就是將一堆貨整整齊齊的碼在倉庫的某個地方,普通列表也是如此;Set就是在倉庫里有這麼一個貨架, ...
  • 中間件分類 ASP.NET Core 中間件的配置方法可以分為以上三種,對應的Helper方法分別是:Run(), Use(), Map()。 Run(),使用Run調用中間件的時候,會直接返回一個響應,所以後續的中間件將不會被執行了。 Use(),它會對請求做一些工作或處理,例如添加一些請求的上下 ...
  • 字元串的常用操作 很好理解 字元串可以用 ' + ' 連接,或者乘一個常數重覆輸出字元串 字元串的索引操作 通過一對中括弧可以找到字元串中的某個字元 可以通過正負數雙向操作噢 用一個中括弧來實現 為什麼沒有-0??去清醒腦子想想 -0 和 0 有差嗎? 還有一個切片操作 就像切菜那樣簡單,同樣是中括 ...
  • title: Java基礎語法(3) 運算符 blog: "CSDN" data: "Java學習路線及視頻" 1.算術運算符 算術運算符的註意問題 如果對負數取模,可以把模數負號忽略不記,如:5% 2=1。 但被模數是負數則不可忽略。此外,取模運算的結果不一定總是整數。 對於除號“/”,它的整數除 ...
  • 下麵是互相轉換的代碼: 有想要瞭解更多關於python知識的請在下方評論或私信小編 ...
  • 引言 構建分散式系統並不容易。然而,人們日常所使用的應用大多基於分散式系統,在短時間內依賴於分散式系統的現狀並不會改變。ApacheZooKeeper旨在減輕構建健壯的分散式系統的任務。ZooKeeper基於 分散式計算的核心概念而設計,主要目的是給開發人員提供一套容易理解和開發的介面,從而簡化分佈 ...
x