SQL Server中索引的用法詳解

2022-05-19 19:02:29

一、索引的介紹

什麼是索引？

索引是一種磁碟上的資料結構，建立在表或檢視的基礎上。使用索引可以使資料的獲取更快更高校，也會影響其他的一些效能，如插入或更新等。

索引主要分為兩種型別：

1、聚集索引和非聚集索引

字典的目錄就是一個索引，按照拼音查詢想要的字就是聚集索引（物理連續，頁碼與目錄一一對應），偏旁部首就是一個非聚集索引（邏輯連續，頁碼與目錄不連續）。

聚集索引儲存記錄是物理上連續存在的，而非聚集索引是邏輯上的連續，物理儲存並不連續。

聚集索引一個表中只能有一個，而非聚集索引一個表中可以有多個。

2、索引的利弊

使用索引是為了避免全表掃描，因為全表掃描是從磁碟上讀取表的每一個資料頁，如果有索引指向資料值，則只需要讀少次數的磁碟就可以。

帶索引的表在資料庫中佔用更多的空間，同樣增、刪、改資料的命令所需時間會更長。

3、索引的儲存機制

書中的目錄是一個字詞以及所在的頁碼列表，資料庫中的索引是表中的值以及各值儲存位置的列表。

聚集索引是在資料庫中新開闢一個物理空間，用來存放他排列的值，當有新資料插入時，他會重新排列整個物理儲存空間。

非聚集索引只包含原表中的非聚集索引的列和指向實際物理表的一個指標。

資料表的基本結構

當一個新的資料表建立時，系統將在磁碟中分配一段以8k為單位的連續空間。當一個8k用完的時候，資料庫指標會自動分配一個8k的空間，每個8k的空間稱為一個資料頁，並分配從0-7的頁號，每個檔案的第0頁記錄引導資訊叫頁頭，每8個資料頁由64k組成形成擴充套件區。全部資料頁的組合形成堆。

SQL Server規定行不能跨越資料頁，所以每行記錄的最大數量只能是8k，這就是為什麼char和varchar這兩種字元型別容量要限制在8k以內的原因，儲存超過8k的資料應使用text型別，其實text型別的欄位值不能直接錄入和儲存，它是儲存一個指標，指向由若干個8k的資料頁所組成的擴充套件區，真正的資料其實放在這些資料頁中。

二、設定索引的權衡

1、什麼情況下設定索引

定義主鍵的資料列（sql server預設會給主鍵一個聚集索引）。
定義有外來鍵的資料列
對於經常查詢的資料列
對於需要在指定範圍內頻繁查詢的資料列
經常在where子句中出現的資料列
經常出現在關鍵字 order by、group by、distinct後面的欄位。

2、什麼情況下不要設定索引

查詢中很少涉及的列，重複值比較多的列。
text、image、bit資料型別的列
經常存取的列
經常更新操作的表，索引一般不要超過3個、最多不要5個。雖說提高了存取速度，但會影響更新操作。

三、聚集索引

1、使用SSMS建立聚集索引

展開要建立索引的表->右擊索引->選擇新建索引->聚集索引->新建索引點新增->選擇列->選擇升序或降序->輸入名字->確定。

預設情況下，生成主鍵的同時將自動建立一個聚集索引。

2、使用T-SQL建立聚集索引

create clustered index index_name /*聚集索引名*/
on table_name
(
    id desc
)
with(drop_existing=on); /*如果存在則刪除*/

每張表或者檢視只能包含一個聚集索引，因為聚集索引改變了資料儲存與排列方式。

無論是聚集還是非聚集索引，都將資訊儲存在平衡樹或B-樹中，B-樹識別類似資料並將他們組合在一起，正是由於B-樹中的檢索基於鍵值，因此索引可以提升資料存取的速度。B-樹將具有類似鍵的組合起來，所以資料庫引擎只需搜尋少量頁面即可找到目標記錄。

四、非聚集索引

每張表上可以有多個非聚集索引，可以在某個列上建立一個索引，也可以在已經是現有索引組成部分的多列上建立索引。

1、SSMS建立方法同上，T-SQL建立方法如下：

create nonclustered index fei /*聚集索引名*/
on defualt
(
    hits desc
)

2、新增索引選項

fillfactor：用於在建立索引時，每個索引頁的資料佔索引大小的百分比，預設100.當需要頻繁修改表時，建議設定為70-80，不經常更新時建議90。

五、範例

create table ceshi --新建表
(
    id int identity(1,1) primary key,
    name varchar(20),
    code varchar(20),
    [date] datetime
)

--插入10w條測試資料
declare @n int
set @n = 1
while @n <100000
 begin
   insert into ceshi (name,code,[date]) values ('name'+cast(@n as varchar(20)),'code'+cast(@n as varchar(20)),getutcdate())
  set @n=@n+1
end

--檢視資料
set statistics io on --檢視磁碟io
set statistics time on --檢視sql語句分析編譯和執行時間
select * from ceshi

--檢視索引情況
exec sp_helpindex ceshi

select * from ceshi where name = 'name1'

ctrl+l 檢視執行計劃聚集索引掃描開銷100%，考慮優化為索引查詢，在name上建立非聚集索引。

--建立非聚集索引
create index name_index on ceshi
(
    name
)
--再次檢視索引情況 多出來新建的非聚集索引
exec sp_helpindex ceshi

--在執行上面的語句
select * from ceshi where name = 'name1'
--明顯發現速度變快了 ， ctrl+l 發現聚集索引和非聚集索引各佔50%

六、管理索引

--檢視該表中的索引
exec sp_helpindex ceshi 
--改名
exec sp_rename 'ceshi.name_index','new_name' 
--刪除索引
drop index ceshi.new_name
--檢查碎片
dbcc showcontig(ceshi,new_name)
--整理碎片
dbcc indexdefrag(webDB,ceshi,new_name) 
--更新表中所有索引的統計
update statistics ceshi

到此這篇關於SQL Server索引的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。