SQL Server中索引的用法詳解

Posted on by

一、索引的介紹

什麼是索引？

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

索引主要分為兩種類型：

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索引的文章就介紹到這瞭。希望對大傢的學習有所幫助，也希望大傢多多支持WalkonNet。

推薦閱讀：