SQLSERVER 的複合索引和包含索引到底有啥區別?

来源:https://www.cnblogs.com/huangxincheng/archive/2023/01/06/17029792.html
-Advertisement-
Play Games

一:背景 1. 講故事 在 SQLSERVER 中有非常多的索引,比如:聚集索引,非聚集索引,唯一索引,複合索引,Include索引,交叉索引,連接索引,奇葩索引等等,當索引多了之後很容易傻傻的分不清,比如:複合索引 和 Include索引,但又在真實場景中用的特別多,本篇我們就從底層數據頁層面釐清 ...


一:背景

1. 講故事

在 SQLSERVER 中有非常多的索引,比如:聚集索引,非聚集索引,唯一索引,複合索引,Include索引,交叉索引,連接索引,奇葩索引等等,當索引多了之後很容易傻傻的分不清,比如:複合索引Include索引,但又在真實場景中用的特別多,本篇我們就從底層數據頁層面釐清一下。

二:到底有什麼區別

1. 這些索引解決了什麼問題

說區別之前,一定要知道它們大概解決了什麼問題?這裡我就從 索引覆蓋 角度來展開吧,為了方便講述,先上一個測試 sql:


IF(OBJECT_ID('t') IS NOT NULL) DROP TABLE t;

CREATE TABLE t(a INT IDENTITY, b CHAR(6), c CHAR(10) DEFAULT 'aaaaaaaaaa')

SET NOCOUNT ON
DECLARE @num INT 
SET @num =10000
WHILE (@num <90000)
BEGIN
	INSERT INTO t(b) VALUES ('b'+CAST(@num AS CHAR(5)))
	SET @num=@num+1
END

CREATE CLUSTERED INDEX idx_a ON t(a)
CREATE INDEX idx_b ON t(b)

SELECT * FROM t;

代碼非常簡單,在 t 表中創建三個列,插入 8w 條數據,然後創建兩個索引,接下來做一個查詢獲取 b,c 列。


SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT b,c FROM t WHERE b IN  ('b10000','b20000','b30000','b40000','b50000','b70000','b80000','b90000')
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

輸出如下:


表“t”。掃描計數 8,邏輯讀取次數 30,物理讀取次數 0,頁面伺服器讀取次數 0,預讀讀取次數 0,頁面伺服器預讀讀取次數 0,LOb 邏輯讀取次數 0,LOB 邏輯讀取次數 0,LOB 頁面伺服器讀取次數 0,LOB 預讀讀取次數 0,LOB 頁面伺服器預讀讀取次數 0。

 SQL Server 執行時間:
   CPU 時間 = 0 毫秒,占用時間 = 134 毫秒。

 SQL Server 執行時間:
   CPU 時間 = 0 毫秒,占用時間 = 0 毫秒。

Completion time: 2023-01-06T08:47:45.2364473+08:00

從執行計劃看,這是一個經典的 書簽查找,這種查找返回的行數越多性能越差,在索引優化時一般都會規避掉這種情況,我們也看到了邏輯讀取次數有 30 次,那能不能再小一點呢?

為瞭解決這個問題,乾脆把 c 列也放到索引中去達到索引覆蓋的效果,這就需要用到 複合索引 了,參考sql如下:


CREATE INDEX idx_complex ON t (b,c)

再次查詢輸出如下:


SQL Server 分析和編譯時間: 
   CPU 時間 = 0 毫秒,占用時間 = 0 毫秒。
表“t”。掃描計數 8,邏輯讀取次數 24,物理讀取次數 0,頁面伺服器讀取次數 0,預讀讀取次數 0,頁面伺服器預讀讀取次數 0,LOb 邏輯讀取次數 0,LOB 邏輯讀取次數 0,LOB 頁面伺服器讀取次數 0,LOB 預讀讀取次數 0,LOB 頁面伺服器預讀讀取次數 0。

 SQL Server 執行時間:
   CPU 時間 = 0 毫秒,占用時間 = 96 毫秒。

 SQL Server 執行時間:
   CPU 時間 = 0 毫秒,占用時間 = 0 毫秒。

Completion time: 2023-01-06T08:53:56.9688921+08:00

從執行計劃來看,這次沒有走 書簽查找 而是 索引查找,並且邏輯讀也降到了 24 次,這是一個好的優化。

相信有些朋友也知道用 Include索引 也能達到這個效果,接下來試著把複合索引給刪了增加一個 Include索引,代碼如下:


DROP INDEX idx_complex ON dbo.t;
CREATE INDEX idx_include ON  t(b) INCLUDE (c)

再次查詢輸出如下:


表“t”。掃描計數 8,邏輯讀取次數 16,物理讀取次數 0,頁面伺服器讀取次數 0,預讀讀取次數 0,頁面伺服器預讀讀取次數 0,LOb 邏輯讀取次數 0,LOB 邏輯讀取次數 0,LOB 頁面伺服器讀取次數 0,LOB 預讀讀取次數 0,LOB 頁面伺服器預讀讀取次數 0。

 SQL Server 執行時間:
   CPU 時間 = 0 毫秒,占用時間 = 73 毫秒。

 SQL Server 執行時間:
   CPU 時間 = 0 毫秒,占用時間 = 0 毫秒。

Completion time: 2023-01-06T08:58:18.1122561+08:00

從執行計劃來看也是走的 非聚集索引,而且邏輯讀再次降到了 16 次,相比原始的書簽查找已經優化了 50%,這是一個巨大的性能提升不是。

到這裡其實有一個問題,兩種優化走的都是 非聚集索引,從邏輯讀次數看貌似 Include索引 更好一些,為什麼會這樣呢?這就涉及到了底層存儲,接下來一起扒一下。

2. 存儲原理研究

研究它們的不同點,最徹底的方式就是從底層存儲出發,首先我們觀察下 複合索引 的底層存儲是什麼樣的,可以用 DBCC 命令。


DBCC TRACEON(3604)
DBCC IND(MyTestDB,t,-1)

IndexLevel=2 來看這個複合索引構成的B樹已經達到了二層,接下來我們查一下 368 號數據頁內容。


DBCC PAGE(MyTestDB,1,368,2)

輸出如下:


PAGE: (1:368)

Memory Dump @0x000000F555578000

000000F555578000:   01020002 00800001 00000000 00001b00 00000000  ....................
000000F555578014:   00000200 3e010000 601f9c00 70010000 01000000  ....>...`...p.......
000000F555578028:   f8000000 e0680000 f5010000 00000000 00000000  .....h..............
000000F55557803C:   00000000 01000000 00000000 00000000 00000000  ....................
000000F555578050:   00000000 00000000 00000000 00000000 16623130  .................b10
000000F555578064:   30303061 61616161 61616161 61010000 00380500  000aaaaaaaaaa....8..
000000F555578078:   00010004 00001662 38333631 36616161 61616161  .......b83616aaaaaaa
000000F55557808C:   61616191 1f010070 05000001 00040000 00006231  aaa....p..........b1

OFFSET TABLE:

Row - Offset                        
1 (0x1) - 126 (0x7e)                
0 (0x0) - 96 (0x60)                 


DBCC 執行完畢。如果 DBCC 輸出了錯誤信息,請與系統管理員聯繫。

根據下麵的 Slot 個數可以知道這個分支節點數據頁只有 2 條記錄,分別為:(b10000,aaaaaaaaaa,0x01) , (b83616,aaaaaaaaaa,0x011f91),這裡說明一下最後的 01 和 0x011f91 是主鍵key,接下來找個葉子節點,比如:1632 號索引頁。


PAGE: (1:1632)


Memory Dump @0x000000F555578000

...
000000F555578050:   00000000 00000000 00000000 00000000 16623135  .................b15
000000F555578064:   32383761 61616161 61616161 61a81400 00040000  287aaaaaaaaaa.......
000000F555578078:   16623135 32383861 61616161 61616161 61a91400  .b15288aaaaaaaaaa...
000000F55557808C:   00040000 16623135 32383961 61616161 61616161  .....b15289aaaaaaaaa
000000F5555780A0:   61aa1400 00040000 16623135 32393061 61616161  a........b15290aaaaa
000000F5555780B4:   61616161 61ab1400 00040000 16623135 32393161  aaaaa........b15291a
000000F5555780C8:   61616161 61616161 61ac1400 00040000 16623135  aaaaaaaaa........b15
000000F5555780DC:   32393261 61616161 61616161 61ad1400 00040000  292aaaaaaaaaa.......
000000F5555780F0:   16623135 32393361 61616161 61616161 61ae1400  .b15293aaaaaaaaaa...
000000F555578104:   00040000 16623135 32393461 61616161 61616161  .....b15294aaaaaaaaa
000000F555578118:   61af1400 00040000 16623135 32393561 61616161  a........b15295aaaaa
000000F55557812C:   61616161 61b01400 00040000 16623135 32393661  aaaaa........b15296a
000000F555578140:   61616161 61616161 61b11400 00040000 16623135  aaaaaaaaa........b15
...

從葉子節點上看,也是 (b,c,key) 的佈局模式,這時候腦子裡就有了一張圖。

用同樣的方式觀察下 Include索引,發現 IndexLevel=1,說明只有一層。

再用 DBCC 觀察下分支節點的佈局。


PAGE: (1:1696)

Memory Dump @0x000000F554F78000

000000F554F78000:   01020001 00820001 00000000 00001100 00000000  ....................
000000F554F78014:   00000601 42010000 1c09d814 a0060000 01000000  ....B....	..........
000000F554F78028:   0f010000 78310000 39010000 00000000 00000000  ....x1..9...........
000000F554F7803C:   f01efa04 00000000 00000000 00000000 00000000  ....................
000000F554F78050:   00000000 00000000 00000000 00000000 16623130  .................b10
000000F554F78064:   30303001 00000088 03000001 00030000 16623130  000..............b10
000000F554F78078:   33313138 010000b0 03000001 00030000 16623130  3118.............b10
000000F554F7808C:   3632326f 020000b1 03000001 00030000 16623130  622o.............b10
000000F554F780A0:   393333a6 030000b2 03000001 00030000 16623131  933..............b11
...

從輸出看並沒有記錄 列c 的值,就是那煩人的 aaaaaaaaaa,然後再抽個葉子節點看看,比如:1218號索引頁。


PAGE: (1:1218)
Memory Dump @0x000000F554F78000

000000F554F78000:   01020000 04020001 c1040000 01001500 c3040000  ....................
000000F554F78014:   01003701 42010000 0a00881d c2040000 01000000  ..7.B...............
000000F554F78028:   0f010000 00310000 03000000 00000000 00000000  .....1..............
000000F554F7803C:   e7351886 00000000 00000000 00000000 00000000  .5..................
000000F554F78050:   00000000 00000000 00000000 00000000 16623833  .................b83
000000F554F78064:   313235a6 1d010061 61616161 61616161 61040000  125....aaaaaaaaaa...
000000F554F78078:   16623833 313236a7 1d010061 61616161 61616161  .b83126....aaaaaaaaa
000000F554F7808C:   61040000 16623833 313237a8 1d010061 61616161  a....b83127....aaaaa
000000F554F780A0:   61616161 61040000 16623833 313238a9 1d010061  aaaaa....b83128....a
000000F554F780B4:   61616161 61616161 61040000 16623833 313239aa  aaaaaaaaa....b83129.
000000F554F780C8:   1d010061 61616161 61616161 61040000 16623833  ...aaaaaaaaaa....b83
000000F554F780DC:   313330ab 1d010061 61616161 61616161 61040000  130....aaaaaaaaaa...
...

在葉子節點中我們終於看到了 aaaaaaaaaa ,其實想一想肯定是有的,不然怎麼做索引覆蓋呢?有了這些信息,腦子中又有了一張圖。

從圖中可以看出,Include索引 的分支節點是不包含 c 列的,這個列只會保存在 葉子節點 中,再結合樹的高度來看就能解釋為什麼 Include索引 的邏輯讀要少於 複合索引

三:總結

總的來說 複合索引Include索引 各有利弊吧,前者會讓索引頁的行數據更大,導致索引頁更多,也就會占用更多的存儲空間,更多的邏輯讀,索引維護開銷也更大,而後者只會將 Include 列 保存在葉子節點,不參與索引計算,相對來說占用的索引頁空間更小。

在查詢方面,複合索引能達到的索引覆蓋場景遠大於單列索引,而且在過濾,排序場景下也能發揮奇效,所以還是根據你的讀寫比例做一個取捨吧。


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

-Advertisement-
Play Games
更多相關文章
  • 什麼是結構化日誌 我們記錄日誌慣常使用 log4j2、NLog 等日誌組件,這些組件提供了輸出到多種終端的能力,但是大部分時候我們選擇將日誌輸出到操作系統的文件系統中,為什麼呢?至少有一部分原因是記錄的每條日誌為字元串格式,且按時間由遠往進順序記錄,打開文件可以直接人肉檢索;如果這些日誌記錄到其它終 ...
  • 本實例使用了工具包SKIT.FlurlHttpClient.Wechat.TenpayV3(github:https://github.com/fudiwei/DotNetCore.SKIT.FlurlHttpClient.Wechat) 示例中的_repositoryWrapper的相關使用是我們 ...
  • cloudflare 賬號註冊 https://www.cloudflare-cn.com/products/tunnel/ 功能變數名稱準備和配置 有兩種方式: 在 cloudflare 自己購買功能變數名稱, 比較貴 在其他平臺的功能變數名稱, 通過配置解析功能變數名稱規則, 這樣可以托管在 cloudflare 以阿裡雲為, ...
  • 背景 https://www.cnblogs.com/liteng0305/p/17018299.html 上次使用樂鑫編譯好的OpenOCD失敗,可能是因為沒有開啟CMSIS-DAP支持,手動開啟編譯試一下 平臺 Ubuntu Linux 5.4.0 官方OpenOCD 直接下載的OpenOCD沒 ...
  • mycode : mycode 思考 突破引導程式方法: 再寫一個程式,並且把這個程式放到存儲介質中; 主引導程式要載入這個新的程式,將控制權轉交給新的程式; 遇到的問題:怎麼在存儲介質中找這個新的程式呢? 那就需要藉助於一個文件系統,有了文件系統,就可以很方便的把寫好的程式放到軟盤裡了,也可以根據 ...
  • 目的 手裡有調試STM32的DAP-LINK,想試試通過JTAG調試ESP32 OpenOCD支持CMSIS-DAP DAP-LINK支持的晶元,我手上這款描述如下,應該JTAG協議的都支持 平臺 windows10 + ESP-IDF ESP-WROOM-32E模組 + 燒錄底座 DAP-LINK ...
  • 目錄 PostgreSQL(01): Ubuntu20.04/22.04 PostgreSQL 安裝配置記錄 PostgreSQL(02): PostgreSQL常用命令 PostgreSQL 常用命令 滿足驗證條件的用戶, 可以用psql命令進入pg的命令行交互模式 用戶管理相關 查看用戶列表 \ ...
  • 摘要:主要介紹華為雲在HBase 2.x內核所做的一些MTTR優化實踐。 本文分享自華為雲社區《華為雲在HBase MTTR上的優化實踐》,作者: 搬磚小能手。 隨著HBase在華為雲的廣泛應用,HBase的數據節點規模也越來越大。最新版本的MRS可支持的單集群HBase數據節點規模可達到1024節 ...
一周排行
    -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版本說明 機器同時安裝了 ...