04_深入淺出索引(上)

来源:https://www.cnblogs.com/LuoQi11/archive/2023/06/03/04_early-simple-index-top-27akdi.html
-Advertisement-
Play Games

上周聽到公司同事分享 MySQL 同步數據到 ES 的方案,發現很有意思,感覺有必要將這塊知識點再總結提煉一下,就有了這篇文章。 本文會先講述數據同步的 4 種方案,並給出常用數據遷移工具,乾貨滿滿! 不 BB,上文章目錄: 1. 前言 在實際項目開發中,我們經常將 MySQL 作為業務資料庫,ES ...


04_深入淺出索引(上)

索引的概念

索引的概念:索引是一種數據結構,用於提高資料庫查詢效率。就像一本書的目錄一樣,索引可以幫助資料庫在大量數據中快速找到需要的數據,減少查詢時間和資源消耗。

除了提高查詢效率,索引還可以幫助資料庫實現唯一性約束、主鍵約束和外鍵約束等數據完整性約束。

例如,在一個用戶表中,我們可以使用用戶ID作為主鍵,併在ID列上創建一個唯一索引,以保證每個用戶ID都是唯一的。

常見索引模型

常見索引模型:索引模型是指索引的數據結構和組織方式。常見的索引模型有哈希表、有序數組和搜索樹等。

哈希表:哈希表是一種將鍵映射到值的數據結構,它通過哈希函數將鍵轉換為數組的下標,然後將值存儲在該下標處。

哈希表適用於等值查詢場景,例如在一個存儲用戶信息的表中,我們可以使用用戶ID作為哈希表的鍵,來快速查找某個用戶的信息。

有序數組:有序數組是一種按照元素大小順序排列的數組,它適用於等值查詢和範圍查詢場景。

例如,在一個按照身份證號排序的用戶表中,我們可以使用二分法快速查找某個身份證號對應的用戶信息。但是,有序數組的更新成本較高,適用於靜態存儲引擎

搜索樹:搜索樹是一種按照元素大小順序組織的樹形結構,它適用於等值查詢和範圍查詢場景。

例如,在一個按照用戶ID排序的用戶表中,我們可以使用二叉搜索樹快速查找某個用戶ID對應的用戶信息。但是,搜索樹的查詢效率高,但寫入和更新成本高,不適用於大規模數據存儲。

擴充例子:在一個電商網站的訂單表中,我們可以使用訂單ID作為哈希表的鍵,來快速查找某個訂單的信息。在一個按照訂單時間排序的訂單表中,我們可以使用二分法快速查找某個時間段內的訂單信息。在一個按照商品價格排序的商品表中,我們可以使用B樹來快速查找某個價格區間內的商品信息。

二叉樹雖然是搜索效率最高的,但是實際上大多數的資料庫存儲卻並不使用二叉樹,因為索引不僅存在記憶體中,還要寫入磁碟。

為了讓查詢儘量少地讀磁碟,我們需要讓查詢過程訪問儘量少的數據塊。因此,我們應該使用“N叉”樹來代替二叉樹。在“N叉”樹中,“N”的大小取決於數據塊的大小。

以InnoDB的一個整數欄位索引為例,這個“N”大約是1200。當這棵樹高為4時,就可以存儲1200的3次方個值,這已經達到了17億。考慮到樹根的數據塊總是在記憶體中,一個10億行的表上一個整數欄位的索引,查找一個值最多只需要訪問3次磁碟。實際上,樹的第二層也有很大概率在記憶體中,那麼訪問磁碟的平均次數就更少了。

擴充闡述:在實際的資料庫應用中,磁碟I/O是非常耗時的操作。因此,我們需要儘量減少磁碟I/O的次數,以提高資料庫的查詢效率。為了實現這個目標,資料庫存儲引擎通常會採用B樹、B+樹、R樹等數據結構來實現索引。這些數據結構都是基於“N叉”樹的結構,能夠有效地減少磁碟I/O的次數,提高查詢效率。

例如,在一個電商網站的商品表中,我們可以使用商品價格作為B+樹的鍵,來快速查找某個價格區間內的商品信息。B+樹在葉子節點上保存了所有數據記錄的指針,而非像B樹那樣在每個節點上都保存數據記錄,因此能夠減少磁碟I/O的次數,提高查詢效率。

總結:為了提高資料庫的查詢效率,我們需要選擇合適的索引模型,並採用相應的數據結構來實現索引。在選擇數據結構時,需要考慮具體的查詢場景和存儲引擎特點。常見的索引模型有哈希表、有序數組和搜索樹等,而常用的數據結構有B樹、B+樹、R樹等。通過選擇合適的索引模型和數據結構,可以有效地提高資料庫的查詢效率,降低磁碟I/O的次數,從而提升資料庫的整體性能。

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

B 樹和 B+ 樹

B樹和B+樹都是多路搜索樹,是一種常用的數據結構,在資料庫、文件系統等領域廣泛應用。它們不是二叉樹,而是多叉樹。

B樹和B+樹的主要區別在於它們的索引結構和葉子節點的存儲方式不同。B樹的每個節點都包含鍵值和指向子節點的指針,而B+樹的非葉子節點只包含鍵值和指向子節點的指針,而所有的數據都存儲在葉子節點中。

B樹的搜索過程比較複雜,因為需要在非葉子節點和葉子節點之間不斷切換,而B+樹的搜索過程更加簡單,因為只需要在葉子節點中進行搜索。此外,B+樹的葉子節點是通過鏈表相連的,可以方便地進行範圍查詢和遍歷。

因此,B+樹通常比B樹更適合在資料庫中使用,因為它能夠更快地進行範圍查詢和遍歷。但是,在某些場景下,B樹也可能比B+樹更適合使用,例如需要快速插入和刪除數據的場景。

InnoDB 的索引模型

在InnoDB中,表都是根據主鍵順序以索引的形式存放的,這種存儲方式的表稱為索引組織表。

InnoDB 使用的是 B+ 樹索引模型,所以數據都是存儲在 B+ 樹中的,每一個索引在 InnoDB 裡面對應一棵 B+ 樹。

索引類型分為主鍵索引和非主鍵索引。

  • 主鍵索引的葉子節點存的是整行數據。在 InnoDB 里,主鍵索引也被稱為聚簇索引(clustered index)。
  • 非主鍵索引的葉子節點內容是主鍵的值。在 InnoDB 里,非主鍵索引也被稱為二級索引(secondary index)。

舉個例子來說,假設我們有一個學生表,其中主鍵為學生ID。如果我們要查詢學號為1001的學生的所有信息,如果使用主鍵索引,則只需要搜索ID這棵B+樹,而如果使用非主鍵索引,則需要先搜索學號這棵B+樹得到ID的值為1001,再到ID索引樹搜索一次,這個過程稱為回表。

因此,使用主鍵索引查詢可以減少一次搜索,提高查詢效率。

在應用中我們應該儘量使用主鍵查詢,以減少查詢時間和提高性能。

但是,在實際使用中,我們也需要根據具體情況來選擇使用哪種索引類型。例如,如果我們需要查詢學生的所有信息,而不僅僅是學號,那麼使用主鍵索引就無法滿足我們的需求,這時候就需要使用非主鍵索引。

InnoDB的索引模型是資料庫中非常重要的一個概念,不同的索引類型在查詢效率和使用場景上都有著不同的優缺點。我們需要根據具體需求來選擇使用哪種索引類型,以提高資料庫的性能和效率。

索引維護

索引維護是資料庫中非常重要的一部分,它確保了數據的快速查詢和排序。

在B+樹中,為了維護索引有序性,在插入新值的時候需要做必要的維護。

這個過程中,當插入的數據頁已經滿了,就需要進行頁分裂操作。這個過程會申請一個新的數據頁,並將部分數據挪動過去,影響了性能和數據頁的利用率。

不過,有分裂就有合併。當相鄰兩個頁由於刪除了數據,利用率很低之後,會將數據頁做合併,這個過程可以認為是分裂過程的逆過程。

另外,對於自增主鍵的使用場景,我們需要分析哪些場景下應該使用自增主鍵,而哪些場景下不應該。

自增主鍵的插入數據模式,是遞增插入的場景,每次插入一條新記錄,都是追加操作,都不涉及到挪動其他記錄,也不會觸發葉子節點的分裂。

而有業務邏輯的欄位做主鍵,則往往不容易保證有序插入,這樣寫數據成本相對較高。

此外,從存儲空間的角度來看,如果用身份證號等字元串類型的欄位做主鍵,那麼每個非主鍵索引的葉子節點上都是主鍵的值,占用的空間較大。因此,自增主鍵往往是更合理的選擇。

但是,對於只有一個索引且必須是唯一索引的場景,可以直接將這個索引設置為業務欄位做主鍵,避免每次查詢需要搜索兩棵樹。

假設你在設計一個訂單系統,其中包含訂單的ID、用戶ID、商品ID、數量、價格等信息。如果你需要在該系統中快速查找某個訂單的信息,可以在訂單ID欄位上建立一個唯一索引,這樣就可以快速查找到該訂單的信息。但是,如果你需要根據用戶ID或商品ID等信息進行查詢,那麼就需要在這些欄位上建立索引,以保證查詢的速度和效率。

在這種情況下,如果使用自增主鍵作為主鍵,可以保證數據的有序插入和查詢,提高了查詢效率。每次插入一條新記錄,都是追加操作,都不涉及到挪動其他記錄,也不會觸發葉子節點的分裂。此外,自增主鍵的數據類型通常為整型,占用的存儲空間相對較小,可以節省存儲空間。

但是,如果你的業務場景需要根據用戶ID或商品ID等欄位進行頻繁的查詢和排序,那麼就應該考慮將這些欄位作為主鍵。在這種情況下,使用自增主鍵可能會導致數據的插入順序與查詢順序不一致,降低查詢效率。

因此,在設計主鍵時,需要根據具體業務場景進行選擇。


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

-Advertisement-
Play Games
更多相關文章
  • 1. 擴展方法 擴展方法使你能夠向現有類型“添加”方法,而無需創建新的派生類型、重新編譯或以其他方式修改原始類型。 擴展方法是一種靜態方法,但可以像擴展類型上的實例方法一樣進行調用。 對於用 C#、F# 和 Visual Basic 編寫的客戶端代碼,調用擴展方法與調用在類型中定義的方法沒有明顯區別 ...
  • 最近ET做熱更重載dll的時候,返回登陸會重新檢測新的dll,首次登錄之前已經Assembly.Load()過一次dll,第二次返回登陸再次load dll到記憶體中,Invoke執行方法的時候,異常了,有些方法執行了,有些未執行,於是查資料,看到些老資料說Assembly.Load重覆載入同名dll ...
  • # 1. 與 .NET Core 緩存的關係和差異 ABP 框架中的緩存系統核心包是 [Volo.Abp.Caching](https://www.nuget.org/packages/Volo.Abp.Caching) ,而對於分散式緩存的支持,abp 官方提供了基於 Redis 的方案,需要安裝 ...
  • 人臉識別技術在現代社會中扮演著越來越重要的角色,比如人臉識別門禁、人臉識別支付、甚至人臉識別網站登錄等。 最近有群友問.NET有沒有人臉識別的組件,小編查閱相關資料介紹下麵幾種.NET人臉識別組件供大家參考。 **1、Microsoft Azure Face API** 簡介:Microsoft A ...
  • ## 01|修改Win用戶名 打開運行,輸入cmd,回車; 輸入control userpasswords2,回車; 點擊屬性,修改用戶名,點擊確定; 打開運行,輸入regedit,回車; 定位到HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\Cur ...
  • 最近在研究ebpf的應用,網上對較低版本的內核和centos操作系統的相關資料較少,這裡記錄一個自己環境配置&編譯運行一個ebpf的helloworld程式的過程。 > 環境是centos7.9,虛擬機安裝記憶體需要分配高一些,後續編譯llvm很吃性能 # 基礎依賴安裝 ## 升級內核版本 ebpf需 ...
  • 目錄 一、grep查找文件內容 二、sort排序 三、uniq統計壓縮重覆 四、tr替換壓縮 五、cut截斷 六.sqlit拆分 七.paste合併 八.eval 一、grep(匹配文件內容) grep [選項]… 查找條件 目標文件 -m 匹配次數 -v 除什麼以外 -i 忽略大小寫 -n 顯示匹 ...
  • nginx.conf中有關訪客日誌定義如下 #log_format #創建日誌格式,名為main log_format main '$remote_addr - $remote_user [$time_local] "$request" ' '$status $body_bytes_sent "$h ...
一周排行
    -Advertisement-
    Play Games
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...