玩轉資料庫索引

.NET快速开发框架發表於2024-06-25
資料庫索引

1、概述

通常我們要對資料庫進行最佳化，主要可以透過以下五種方法。

  1. 計算機硬體調優
  2. 應用程式調優
  3. 資料庫索引最佳化
  4. SQL語句最佳化
  5. 事務處理調優

資料庫最佳化

本篇文章將向大家介紹資料庫中索引型別和使用場合，本文以SQL Server為例，對於其他技術平臺的朋友也是有參考價值的，原理差不多。

查詢資料時索引使資料庫引擎執行速度更快，有針對性的資料檢索，而不是簡單地整表掃描（Full table scan）。

為了有效的使用索引，我們必須對索引的構成有所瞭解，而且我們知道在資料表中新增索引必然需要建立和維護索引表，所以我們要全域性地衡量新增索引是否能提高資料庫系統的查詢效能。

2、資料庫中的檔案和檔案組

在物理層面上，資料庫由資料檔案組成，而這些資料檔案組成檔案組，然後儲存在磁碟上。每個檔案包含許多區，每個區的大小為64K由八個物理上連續的頁組成（一個頁8K），我們知道頁是SQL Server資料庫中的資料儲存的基本單位。為資料庫中的資料檔案（.mdf 或 .ndf）分配的磁碟空間可以從邏輯上劃分成頁（從0到n連續編號）。

頁中儲存的型別有：資料，索引和溢位。

在SQL Server中，透過檔案組這個邏輯物件對存放資料的檔案進行管理。

檔案和檔案組

在頂層是我們的資料庫，由於資料庫是由一個或多個檔案組組成，而檔案組是由一個或多個檔案組成的邏輯組，所以我們可以把檔案組分散到不同的磁碟中，使使用者資料儘可能跨越多個裝置，多個I/O 運轉，避免 I/O 競爭，從而均衡I/O負載，克服訪問瓶頸。

3、區和頁

如下圖所示，檔案是由區組成的，而區由八個物理上連續的頁組成，由於區的大小為64K，所以每當增加一個區檔案就增加64K。

區和頁

頁中儲存的資料型別有：表資料、索引資料、溢位資料、分配對映、頁空閒空間、索引分配等。

頁型別 內容
Data 當 text in row 設定為 ON 時，包含除 text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 資料之外的所有資料的資料行。
Index 索引條目。
Text/Image 大型物件資料型別：text 、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 資料。資料行超過 8 KB 時為可變長度資料型別列：varchar 、nvarchar、varbinary 和 sql_variant
Global Allocation Map、Shared Global Allocation Map 有關區是否分配的資訊。
Page Free Space 有關頁分配和頁的可用空間的資訊。
Index Allocation Map 有關每個分配單元中表或索引所使用的區的資訊。
Bulk Changed Map 有關每個分配單元中自最後一條 BACKUP LOG 語句之後的大容量操作所修改的區的資訊。
Differential Changed Map 有關每個分配單元中自最後一條 BACKUP DATABASE 語句之後更改的區的資訊。

在資料頁上，資料行緊接著頁頭（標頭）按順序放置；頁頭包含標識值，如頁碼或物件資料的物件ID；資料行持有實際的資料；最後，頁的末尾是行偏移表，對於頁中的每一行，每個行偏移表都包含一個條目，每個條目記錄對應行的第一個位元組與頁頭的距離，行偏移表中的條目的順序與頁中行的順序相反。

資料頁

4、索引的基本結構

“索引（Index）提供查詢的速度”這是對索引的最基本的解釋，接下來我們將透過介紹索引的組成，讓大家對索引有更深入的理解。

索引是資料庫中的一個獨特的結構，由於它儲存資料庫資訊，那麼我們就需要給它分配磁碟空間和維護索引表。建立索引並不會改變表中的資料，它只是建立了一個新的資料結構指向資料表；打個比方，平時我們使用字典查字時，首先我們要知道查詢單詞起始字母，然後翻到目錄頁，接著查詢單詞具體在哪一頁，這時我們目錄就是索引表，而目錄項就是索引了。

當然，索引比字典目錄更為複雜，因為資料庫必須處理插入，刪除和更新等操作，這些操作將導致索引發生變化。

葉節點

假設我們磁碟上的資料是物理有序的，那麼資料庫在進行插入，刪除和更新操作時，必然會導致資料發生變化，如果我們要儲存資料的連續和有序，那麼我們就需要移動資料的物理位置，這將增大磁碟的I/O，使得整個資料庫執行非常緩慢；使用索引的主要目的是使資料邏輯有序，使資料獨立於物理有序儲存。

為了實現資料邏輯有序，索引使用雙向連結串列的資料結構來保持資料邏輯順序，如果要在兩個節點中插入一個新的節點只需修改節點的前驅和後繼，而且無需修改新節點的物理位置。

雙向連結串列（Doubly linked list）也叫雙連結串列，是連結串列的一種，它的每個資料結點中都有兩個指標，分別指向直接後繼和直接前驅。所以，從雙向連結串列中的任意一個結點開始，都可以很方便地訪問它的前驅結點和後繼結點。

理論上說，從雙向連結串列中刪除一個元素操作的時間複雜度是O(1)，如果希望刪除一個具體有給定關鍵字的元素，那麼最壞的情況下的時間複雜度為O(n)。

在刪除的過程中，我們只需要將要刪除的節點的前節點和後節點相連，然後將要刪除的節點的前節點和後節點置為null即可。

//虛擬碼
node.prev.next=node.next; 
node.next.prev=node.prev; 
node.prev=node.next=null;

索引的葉節點和相應的表資料

如上圖，索引葉節點包含索引值和相應的RID（ROWID），而且葉節點透過雙向連結串列有序地連線起來；同時我們主要到資料表不同於索引葉節點，表中的資料無序儲存，它們不全是儲存在同一表塊中，而且塊之間不存在連線。

總的來說，索引儲存著具體資料的實體地址值。

5、索引的型別

索引的型別主要有兩種：聚集索引和非聚集索引。

聚集索引：物理儲存按照索引排序。

指資料庫錶行中資料的物理順序與鍵值的邏輯（索引）順序相同。一個表只能有一個聚集索引，因為一個表的物理順序只有一種情況，所以，對應的聚集索引只能有一個。如果某索引不是聚集索引，則表中的行物理順序與索引順序不匹配，與非聚集索引相比，聚集索引有著更快的檢索速度。

非聚集索引：物理儲存不按照索引排序。

該索引中索引的邏輯順序與磁碟上行的物理儲存順序不同，一個表中可以擁有多個非聚集索引。除了聚集索引以外的索引都是非聚集索引，只是人們想細分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引類比成現實生活中的東西，那麼非聚集索引就像新華字典的偏旁字典，他結構順序與實際存放順序不一定一致。

5.1、聚集索引

聚集索引的資料頁是物理有序地儲存，資料頁是聚集索引的葉節點，資料頁之間透過雙向連結串列的形式連線起來，而且實際的資料都儲存在資料頁中。當我們給表新增索引後，表中的資料將根據索引進行排序。

假設我們有一個表T_Pet，它包含四個欄位分別是：animal，name，sex和age，而且使用animal作為索引列，具體SQL程式碼如下：

-----------------------------------------------------------
---- Create T_Pet table in tempdb. 
-----------------------------------------------------------
USE tempdb

CREATE TABLE T_Pet
(
    animal    VARCHAR(20),
    [name]    VARCHAR(20),
    sex        CHAR(1),
    age        INT
)

CREATE UNIQUE  CLUSTERED INDEX T_PetonAnimal1_ClterIdx ON T_Pet (animal)

-----------------------------------------------------------
---- Insert data into data table.
-----------------------------------------------------------

DECLARE @i int

SET @i=0
WHILE(@i<1000000)
BEGIN

    INSERT INTO T_Pet (
        animal,
        [name],
        sex,
        age
    )
    SELECT  [dbo].random_string(11) animal,
            [dbo].random_string(11) [name],
            'F'                        sex,
            cast(floor(rand()*5) as int) age    

    SET @i=@i+1

END

INSERT INTO T_Pet VALUES('Aardark', 'Hello', 'F', 1)
INSERT INTO T_Pet VALUES('Cat', 'Kitty', 'F', 2)
INSERT INTO T_Pet VALUES('Horse', 'Ma', 'F', 1)
INSERT INTO T_Pet VALUES('Turtles', 'SiSi', 'F', 4)
INSERT INTO T_Pet VALUES('Dog', 'Tomma', 'F', 2)
INSERT INTO T_Pet VALUES('Donkey', 'YoYo', 'F', 3)

聚集索引

如上圖所示，從左往右的第一和第二層是索引頁，第三層是資料頁（葉節點），資料頁之間透過雙向連結串列連線起來，而且資料頁中的資料根據索引排序；假設，我們要查詢名字（name）為Xnnbqba的動物Ifcey，這裡我們以animal作為表的索引，所以資料庫首先根據索引查詢，當找到索引值animal = ‘Ifcey時，接著查詢該索引的資料頁（葉節點）獲取具體資料。具體的查詢語句如下：

SET STATISTICS PROFILE ON
SET STATISTICS TIME ON

SELECT animal, [name], sex, age
FROM T_Pet
WHERE animal = 'Ifcey'

SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF

當我們執行完SQL查詢計劃時，把滑鼠指標放到“聚集索引查詢”上，這時會出現如下圖資訊，我們可以檢視到一個重要的資訊Logical Operation——Clustered Index Seek，SQL查詢是直接根據聚集索引獲取記錄，查詢速度最快。

查詢計劃

從下圖查詢結果，我們發現查詢步驟只有2步，首先透過Clustered Index Seek快速地找到索引Ifcey，接著查詢索引的葉節點（資料頁）獲取資料。

查詢執行時間：CPU 時間= 0 毫秒，佔用時間= 1 毫秒。

查詢結果

現在我們把表中的索引刪除，重新執行查詢計劃，這時我們可以發現Logical Operation已經變為Table Scan，由於表中有100萬行資料，這時查詢速度就相當緩慢。

查詢計劃

從下圖查詢結果，我們發現查詢步驟變成3步了，首先透過Table Scan查詢animal = ‘Ifcey’，在執行查詢的時候，SQL Server會自動分析SQL語句，而且它估計我們這次查詢比較耗時，所以資料庫進行併發操作加快查詢的速度。

查詢執行時間：CPU 時間= 329 毫秒，佔用時間= 182 毫秒。

查詢結果

透過上面的有聚集索引和沒有的對比，我們發現了查詢效能的差異，如果使用索引資料庫首先查詢索引，而不是漫無目的的全表遍歷。

5.2、非聚集索引

在沒有聚集索引的情況下，表中的資料頁是透過堆(Heap)形式進行儲存，堆是不含聚集索引的表；SQL Server中的堆儲存是把新的資料行儲存到最後一個頁中。

非聚集索引是物理儲存不按照索引排序，非聚集索引的葉節點（Index leaf pages）包含著指向具體資料行的指標或聚集索引，資料頁之間沒有連線是相對獨立的頁。

假設我們有一個表T_Pet，它包含四個欄位分別是：animal，name，sex和age，而且使用animal作為非索引列，具體SQL程式碼如下：

-----------------------------------------------------------
---- Create T_Pet table in tempdb with NONCLUSTERED INDEX. 
-----------------------------------------------------------
USE tempdb

CREATE TABLE T_Pet
(
    animal    VARCHAR(20),
    [name]    VARCHAR(20),
    sex        CHAR(1),
    age        INT
)

CREATE UNIQUE  NONCLUSTERED INDEX T_PetonAnimal1_NonClterIdx ON T_Pet (animal)

非聚集索引

接著我們要查詢表中animal = ‘Cat’的寵物資訊，具體的SQL程式碼如下：

SET STATISTICS PROFILE ON
SET STATISTICS TIME ON

SELECT animal, [name], sex, age
FROM T_Pet
WHERE animal = 'Cat'

SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF

如下圖所示，我們發現查詢計劃的最右邊有兩個步驟：RID和索引查詢。由於這兩種查詢方式相對於聚集索引查詢要慢（Clustered Index Seek）。

查詢計劃

查詢計劃

首先SQL Server查詢索引值，然後根據RID查詢資料行，直到找到符合查詢條件的結果。

查詢執行時間：CPU 時間= 0 毫秒，佔用時間= 1 毫秒

查詢結果

5.3、堆表非聚集索引

由於堆是不含聚集索引的表，所以非聚集索引的葉節點將包含指向具體資料行的指標。

以前面的T_Pet表為例，假設T_Pet使用animal列作為非聚集索引，那麼它的堆表非聚集索引結構如下圖所示：

堆表非聚集索引

透過上圖，我們發現非聚集索引透過雙向連結串列連線，而且葉節點包含指向具體資料行的指標。

如果我們要查詢animal = ‘Dog’的資訊，首先我們遍歷第一層索引，然後資料庫判斷Dog屬於Cat範圍的索引，接著遍歷第二層索引，然後找到Dog索引獲取其中的儲存的指標資訊，根據指標資訊獲取相應資料頁中的資料，接下來我們將透過具體的例子說明。

現在我們建立表employees，然後給該表新增堆表非聚集索引，具體SQL程式碼如下：

USE tempdb

---- Creates a sample table.
CREATE TABLE employees (
    employee_id   NUMERIC       NOT NULL,
    first_name    VARCHAR(1000) NOT NULL,
    last_name     VARCHAR(900)  NOT NULL,
    date_of_birth DATETIME                   ,
    phone_number  VARCHAR(1000) NOT NULL,
    junk          CHAR(1000)             ,
    CONSTRAINT employees_pk PRIMARY KEY NONCLUSTERED (employee_id)
);
GO

現在我們查詢employee_id = 29976的員工資訊。

SELECT * 
FROM employees
WHERE employee_id = 29976

查詢計劃如下圖所示：

查詢計劃

首先，查詢索引值employee_id = ‘29976’的索引，然後根據RID查詢符合條件的資料行；所以說，堆表索引的查詢效率不如聚集表，接下來我們將介紹聚集表的非聚集索引。

5.4、聚集表非聚集索引

當表上存在聚集索引時，任何非聚集索引的葉節點不再是包含指標值，而是包含聚集索引的索引值。

以前面的T_Pet表為例，假設T_Pet使用animal列作為非聚集索引，那麼它的索引表非聚集索引結構如下圖所示：

索引表非聚集索引

透過上圖，我們發現非聚集索引透過雙向連結串列連線，而且葉節點包含索引表的索引值。

如果我們要查詢animal = ‘Dog’的資訊，首先我們遍歷第一層索引，然後資料庫判斷Dog屬於Cat範圍的索引，接著遍歷第二層索引，然後找到Dog索引獲取其中的儲存的索引值，然後根據索引值獲取相應資料頁中的資料。

接下來我們修改之前的employees表，首先我們刪除之前的堆表非聚集索引，然後增加索引表的非聚集索引，具體SQL程式碼如下：

ALTER TABLE employees
    DROP CONSTRAINT employees_pk

ALTER TABLE employees 
    ADD CONSTRAINT employees_pk PRIMARY KEY CLUSTERED (employee_id)
GO 

SELECT * FROM employees
WHERE employee_id=29976

查詢計劃

6、索引的有效性

SQL Server每執行一個查詢，首先要檢查該查詢是否存在執行計劃，如果沒有，則要生成一個執行計劃，那麼什麼是執行計劃呢？簡單來說，它能幫助SQL Server制定一個最優的查詢計劃。

下面我們將透過具體的例子說明SQL Server中索引的使用，首先我們定義一個表testIndex，它包含三個欄位testIndex，bitValue和filler，具體的SQL程式碼如下：

-----------------------------------------------------------
---- Index Usefulness sample
-----------------------------------------------------------

CREATE TABLE testIndex
(
    testIndex int identity(1,1) constraint PKtestIndex primary key,
    bitValue bit,
    filler char(2000) not null default (replicate('A',2000))
)

CREATE INDEX XtestIndex_bitValue on testIndex(bitValue)
GO

INSERT INTO testIndex(bitValue)
    VALUES (0)
GO 20000 --runs current batch 20000 times.

INSERT INTO testIndex(bitValue)
    VALUES (1)
GO 10 --puts 10 rows into table with value 1

接著我們查詢表中bitValue = 0的資料行，而且表中bitValue = 0的資料有2000行。

SELECT *
FROM   testIndex
WHERE  bitValue = 0

查詢計劃

現在我們查詢bitValue = 1的資料行。

SELECT *
FROM   testIndex
WHERE  bitValue = 1

查詢計劃

現在我們注意到對同一個表不同資料查詢，居然執行截然不同的查詢計劃，這究竟是什麼原因導致的呢？

我們可以透過使用DBCC SHOW_STATISTICS檢視到表中索引的詳細使用情況，具體SQL程式碼如下：

UPDATE STATISTICS dbo.testIndex
DBCC SHOW_STATISTICS('dbo.testIndex', 'XtestIndex_bitValue')
WITH HISTOGRAM

直方圖

透過上面的直方圖，我們知道SQL Server估計bitValue = 0資料行行有約19989行，而bitValue = 1估計約21；SQL Server最佳化器根據資料量估算值，採取不同的執行計劃，從而到達最優的查詢效能，由於bitValue = 0資料量大，SQL Server只能提供掃描聚集索引獲取相應資料行，而bitValue = 1實際資料行只有10行，SQL Server首先透過鍵查詢bitValue = 1的資料行，然後巢狀迴圈聯接到聚集索引獲得餘下資料行。

7、索引的優缺點

優點
第一，透過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性。
第二，可以大大加快 資料的檢索速度，這也是建立索引的最主要的原因。
第三，可以加速表和表之間的連線，特別是在實現資料的參考完整性方面特別有意義。
第四，在使用分組和排序 子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間。
第五，透過使用索引，可以在查詢的過程中，使用最佳化隱藏器，提高系統的效能。

缺點
第一，建立索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加。
第二，索引需要佔物理空間，除了資料表佔資料空間之外，每一個索引還要佔一定的物理空間，如果要建立聚簇索引，那麼需要的空間就會更大。
第三，當對錶中的資料進行增加、刪除和修改的時候，索引也要動態的維護，這樣就降低了資料的維護速度。

8、索引建立原則

1.定義主鍵的資料列一定要建立索引。

2.定義有外來鍵的資料列一定要建立索引。

3.對於經常查詢的資料列最好建立索引。

4.對於需要在指定範圍內的快速或頻繁查詢的資料列;

5.經常用在WHERE子句中的資料列。

6.經常出現在關鍵字order by、group by、distinct後面的欄位。如果建立的是複合索引，索引的欄位順序要和這些關鍵字後面的欄位順序一致，否則索引不會被使用。

7.對於那些查詢中很少涉及的列，重複值比較多的列不要建立索引。

8.對於定義為text、image和bit的資料型別的列不要建立索引。

9.對於經常存取的列不要建立索引

10.限制表上的索引數目。對一個存在大量更新操作的表，所建索引的數目一般不要超過3個，最多不要超過5個。索引雖說提高了訪問速度，但太多索引會影響資料的更新操作。

11.對複合索引，按照欄位在查詢條件中出現的頻度建立索引。在複合索引中，記錄首先按照第一個欄位排序。對於在第一個欄位上取值相同的記錄，系統再按照第二個欄位的取值排序，以此類推。因此只有複合索引的第一個欄位出現在查詢條件中，該索引才可能被使用,因此將應用頻度高的欄位，放置在複合索引的前面，會使系統最大可能地使用此索引，發揮索引的作用。

9、參考文章

索引 - SQL Server | Microsoft Learn

聚集與非聚集索引 - SQL Server | Microsoft Learn

《ORACLE PL/SQL程式設計詳解》全原創（共八篇）--系列文章導航

8 種主流資料遷移工具技術選型

SQLServer中的CTE(Common Table Expression)通用表表示式使用詳解

[推薦推薦]ORACLE SQL：經典查詢練手系列文章收尾(目錄篇)

國思RDIF低程式碼快速開發平臺（支援vue2、vue3）

相關文章