程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> sqlserver 索引的一些總結

sqlserver 索引的一些總結

編輯：MSSQL

sqlserver 索引的一些總結。本站提示廣大學習愛好者：（sqlserver 索引的一些總結）文章只能為提供參考，不一定能成為您想要的結果。以下是sqlserver 索引的一些總結正文

1.1.1 摘要
假如說要對數據庫停止優化，我們重要可以經由過程以下五種辦法，對數據庫體系停止優化。

1. 盤算機硬件調優
2. 運用法式調優
3. 數據庫索引優化
4. SQL語句優化
5. 事務處置調優

在本篇博文中，我們將想年夜家講述數據庫中索引類型和應用場所，本文以SQL Server為例，關於其他技巧平台的同伙也是有參考價值的，只需調換絕對應的代碼就好了！

索引使數據庫引擎履行速度更快，有針對性的數據檢索，而不是簡略地整表掃描（Full table scan）。

為了應用有用的索引，我們必需對索引的組成有所懂得，並且我們曉得在數據表中添加索引必定須要創立和保護索引表，所以我們要全局地權衡添加索引能否能進步數據庫體系的查詢機能。

在物理層面上，數據庫稀有據文件構成，而這些數據文件可以構成文件組，然後存儲在磁盤上。每一個文件包括很多區，每一個區的年夜小為64K由八個物理上持續的頁構成（一個頁8K），我們曉得頁是SQL Server數據庫中的數據存儲的根本單元。為數據庫中的數據文件（.mdf 或 .ndf）分派的磁盤空間可以從邏輯上劃分紅頁（從0到n持續編號）。

頁中存儲的類型有：數據，索引和溢出。

文件和文件組
在SQL Server中，經由過程文件組這個邏輯對象對寄存數據的文件停止治理。

1.1.2 注釋

在物理層面上，數據庫稀有據文件構成，而這些數據文件可以構成文件組，然後存儲在磁盤上。每一個文件包括很多區，每一個區的年夜小為64K由八個物理上持續的頁構成（一個頁8K），我們曉得頁是SQL Server數據庫中的數據存儲的根本單元。為數據庫中的數據文件（.mdf 或 .ndf）分派的磁盤空間可以從邏輯上劃分紅頁（從0到n持續編號）。

頁中存儲的類型有：數據，索引和溢出。

文件和文件組
在SQL Server中，經由過程文件組這個邏輯對象對寄存數據的文件停止治理。

index3

圖1數據庫文件組織

在頂層是我們的數據庫，因為數據庫是由一個或多個文件組構成，而文件組是由一個或多個文件構成的邏輯組，所以我們可以把文件組疏散到分歧的磁盤中，應用戶數據盡量逾越多個裝備，多個I/O 運轉，防止 I/O 競爭，從而平衡I/O負載，戰勝拜訪瓶頸。

區和頁
如圖2所示，文件是由區構成的，而區由八個物理上持續的頁構成，因為區的年夜小為64K，所以每當增長一個區文件就增長64K。

index4

圖2文件構成

頁中保留的數據類型有：表數據、索引數據、溢出數據、分派映照、頁余暇空間、索引分派等，詳細以下圖所示：

頁類型

內容

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 語句以後更改的區的信息。

表1頁中保留的數據類型

在數據頁上，數據行緊接著頁頭（標頭）按次序放置；頁頭包括標識值，如頁碼或對象數據的對象ID；數據行持有現實的數據；最初，頁的末尾是行偏移表，關於頁中的每行，每一個行偏移表都包括一個條目，每一個條目記載對應行的第一個字節與頁頭的間隔，行偏移表中的條目標次序與頁中行的次序相反。

index11

圖3數據頁

索引的根本構造
“索引（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;

index6

圖4索引的葉節點和響應的表數據

如上圖4所示，索引葉節點包括索引值和響應的RID（ROWID），並且葉節點經由過程雙向鏈表有序地銜接起來；同時我們重要到數據表分歧於索引葉節點，表中的數據無序存儲，它們不滿是存儲在統一表塊中，並且塊之間不存在銜接。

總的來講，索引保留著詳細數據的物理地址值。

索引的類型

我們曉得索引的類型有兩種：集合索引和非集合索引。
集合索引：物理存儲依照索引排序。
非集合索引：物理存儲不依照索引排序。

集合索引

集合索引的數據頁是物理有序地存儲，數據頁是集合索引的葉節點，數據頁之間經由過程雙向鏈表的情勢銜接起來，並且現實的數據都存儲在數據頁中。當我們給表添加索引後，表中的數據將依據索引停止排序。
假定我們有一個表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)

index7

圖5集合索引

如上圖5所示，從左往右的第一和第二層是索引頁，第三層是數據頁（葉節點），數據頁之間經由過程雙向鏈表銜接起來，並且數據頁中的數據依據索引排序；假定，我們要查找名字（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查詢是直接依據集合索引獲得記載，查詢速度最快。

index12

圖6查詢籌劃

從下圖查詢成果，我們發明查詢步調只要2步，起首經由過程Clustered Index Seek疾速地找到索引Ifcey，接著查詢索引的葉節點（數據頁）獲得數據。

查詢履行時光：CPU 時光= 0 毫秒，占用時光= 1 毫秒。

index13

圖7查詢成果

如今我們把表中的索引刪除，從新履行查詢籌劃，這時候我們可以發明Logical Operation曾經變成Table Scan，因為表中有100萬行數據，這時候查詢速度就相當遲緩。

index15

圖8查詢籌劃

從下圖查詢成果，我們發明查詢步調釀成3步了，起首經由過程Table Scan查找animal = ‘Ifcey'，在履行查詢的時刻，SQL Server會主動剖析SQL語句，並且它估量我們此次查詢比擬耗時，所以數據庫停止並發操作加速查詢的速度。

查詢履行時光：CPU 時光= 329 毫秒，占用時光= 182 毫秒。

index14

圖9查詢成果

經由過程下面的有集合索引和沒有的比較，我們發明了查詢機能的差別，假如應用索引數據庫起首查找索引，而不是漫無目標的全表遍歷。

非集合索引

在沒有集合索引的情形下，表中的數據頁是經由過程堆(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)

index8

圖10非集合索引

接著我們要查詢表中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）。

index17

index16

圖11查詢籌劃

起首SQL Server查找索引值，然後依據RID查找數據行，直到找到相符查詢前提的成果。

查詢履行時光：CPU 時光= 0 毫秒，占用時光= 1 毫秒

index18

圖12查詢成果

堆表非集合索引

因為堆是不含集合索引的表，所以非集合索引的葉節點將包括指向詳細數據行的指針。

之前面的T_Pet表為例，假定T_Pet應用animal列作為非集合索引，那末它的堆表非集合索引構造以下圖所示：

index9

圖13堆表非集合索引

經由過程上圖，我們發明非集合索引經由過程雙向鏈表銜接，並且葉節點包括指向詳細數據行的指針。

假如我們要查找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

查詢籌劃以下圖所示：

index19

圖14查詢籌劃

起首，查找索引值employee_id = ‘29976'的索引，然後依據RID查找相符前提的數據行；所以說，堆表索引的查詢效力不如集合表，接上去我們將引見集合表的非集合索引。

集合表非集合索引

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

之前面的T_Pet表為例，假定T_Pet應用animal列作為非集合索引，那末它的索引表非集合索引構造以下圖所示：

index10

圖15索引表非集合索引

經由過程上圖，我們發明非集合索引經由過程雙向鏈表銜接，並且葉節點包括索引表的索引值。

假如我們要查找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

index20

圖16查詢籌劃

索引的有用性
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

index21

圖17查詢籌劃

如今我們查詢bitValue = 1的數據行。

SELECT *FROM testIndexWHERE bitValue = 1
index22

圖18查詢籌劃

如今我們留意到對統一個表分歧數據查詢，竟然履行判然不同的查詢籌劃，這畢竟是甚麼緣由招致的呢？

我們可以經由過程應用DBCC SHOW_STATISTICS檢查到表中索引的具體應用情形，詳細SQL代碼以下：

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

index23

圖19直方圖

經由過程下面的直方圖，我們曉得SQL Server估量bitValue = 0數據行行有約19989行，而bitValue = 1估量約21；SQL Server優化器依據數據量預算值，采用分歧的履行籌劃，從而達到最優的查詢機能，因為bitValue = 0數據量年夜，SQL Server只能供給掃描集合索引獲得響應數據行，而bitValue = 1現實數據行只要10行，SQL Server起首經由過程鍵查找bitValue = 1的數據行，然後嵌套輪回聯接到集合索引取得余下數據行。

總結完全實例代碼：

-- =============================================
-- Author: JKhuang
-- Create date: 04/20/2012
-- Description: Create sample for Clustered and
-- Nonclustered index.
-- =============================================

-----------------------------------------------------------
---- 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)
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)

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

-----------------------------------------------------------
---- Create employees table in tempdb.
-----------------------------------------------------------
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) ,
--PK constraint defaults to clustered
CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);
GO

-----------------------------------------------------------
---- Insert data into data table.
-----------------------------------------------------------
CREATE VIEW rand_helper AS SELECT RND=RAND();
GO
---- Generates random string function.
CREATE FUNCTION random_string (@maxlen int) RETURNS VARCHAR(255)
AS BEGIN
DECLARE @rv VARCHAR(255)
DECLARE @loop int
DECLARE @len int
SET @len = (SELECT CAST(rnd * (@maxlen-3) AS INT) +3
FROM rand_helper)
SET @rv = ''
SET @loop = 0
WHILE @loop < @len BEGIN
SET @rv = @rv
+ CHAR(CAST((SELECT rnd
FROM rand_helper) * 26 AS INT )+97)
IF @loop = 0 BEGIN
SET @rv = UPPER(@rv)
END
SET @loop = @loop +1;
END
RETURN @rv
END
GO
---- Generates random date function.
CREATE FUNCTION random_date (@mindaysago int, @maxdaysago int)
RETURNS VARCHAR(255)
AS BEGIN
DECLARE @rv datetime
SET @rv = (SELECT GetDate()
- rnd * (@maxdaysago-@mindaysago)
- @mindaysago
FROM rand_helper)
RETURN @rv
END
GO
---- Generates random int function.
CREATE FUNCTION random_int (@min int, @max int) RETURNS INT
AS BEGIN
DECLARE @rv INT
SET @rv = (SELECT rnd * (@max) + @min
FROM rand_helper)
RETURN @rv
END
GO
---- Inserts data into employees table.
WITH generator (n) as
(
select 1
union all
select n + 1 from generator
where N < 30000
)
INSERT INTO employees (employee_id
, first_name, last_name
, date_of_birth, phone_number, junk)
select n employee_id
, [dbo].random_string(11) first_name
, [dbo].random_string(11) last_name
, [dbo].random_date(20*365, 60*365) dob
, 'N/A' phone
, 'junk' junk
from generator
OPTION (MAXRECURSION 30000)
-----------------------------------------------------------
---- 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
SELECT filler
FROM testIndex
WHERE bitValue = 1
UPDATE STATISTICS dbo.testIndex
DBCC SHOW_STATISTICS('dbo.testIndex', 'XtestIndex_bitValue')
WITH HISTOGRAM