一、索引的介紹

什么是索引？

索引是一種磁盤上的數(shù)據(jù)結(jié)構(gòu)，建立在表或視圖的基礎(chǔ)上。使用索引可以使數(shù)據(jù)的獲取更快更高校，也會(huì)影響其他的一些性能，如插入或更新等。

索引主要分為兩種類型：

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

字典的目錄就是一個(gè)索引，按照拼音查詢想要的字就是聚集索引（物理連續(xù)，頁碼與目錄一一對(duì)應(yīng)），偏旁部首就是一個(gè)非聚集索引（邏輯連續(xù)，頁碼與目錄不連續(xù)）。

聚集索引存儲(chǔ)記錄是物理上連續(xù)存在的，而非聚集索引是邏輯上的連續(xù)，物理存儲(chǔ)并不連續(xù)。

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

2、索引的利弊

使用索引是為了避免全表掃描，因?yàn)槿頀呙枋菑拇疟P上讀取表的每一個(gè)數(shù)據(jù)頁，如果有索引指向數(shù)據(jù)值，則只需要讀少次數(shù)的磁盤就可以。

帶索引的表在數(shù)據(jù)庫中占用更多的空間，同樣增、刪、改數(shù)據(jù)的命令所需時(shí)間會(huì)更長。

3、索引的存儲(chǔ)機(jī)制

書中的目錄是一個(gè)字詞以及所在的頁碼列表，數(shù)據(jù)庫中的索引是表中的值以及各值存儲(chǔ)位置的列表。

聚集索引是在數(shù)據(jù)庫中新開辟一個(gè)物理空間，用來存放他排列的值，當(dāng)有新數(shù)據(jù)插入時(shí)，他會(huì)重新排列整個(gè)物理存儲(chǔ)空間。

非聚集索引只包含原表中的非聚集索引的列和指向?qū)嶋H物理表的一個(gè)指針。

數(shù)據(jù)表的基本結(jié)構(gòu)

當(dāng)一個(gè)新的數(shù)據(jù)表創(chuàng)建時(shí)，系統(tǒng)將在磁盤中分配一段以8k為單位的連續(xù)空間。當(dāng)一個(gè)8k用完的時(shí)候，數(shù)據(jù)庫指針會(huì)自動(dòng)分配一個(gè)8k的空間，每個(gè)8k的空間稱為一個(gè)數(shù)據(jù)頁，并分配從0-7的頁號(hào)，每個(gè)文件的第0頁記錄引導(dǎo)信息叫頁頭，每8個(gè)數(shù)據(jù)頁由64k組成形成擴(kuò)展區(qū)。全部數(shù)據(jù)頁的組合形成堆。

SQL Server規(guī)定行不能跨越數(shù)據(jù)頁，所以每行記錄的最大數(shù)量只能是8k，這就是為什么char和varchar這兩種字符類型容量要限制在8k以內(nèi)的原因，存儲(chǔ)超過8k的數(shù)據(jù)應(yīng)使用text類型，其實(shí)text類型的字段值不能直接錄入和保存，它是存儲(chǔ)一個(gè)指針，指向由若干個(gè)8k的數(shù)據(jù)頁所組成的擴(kuò)展區(qū)，真正的數(shù)據(jù)其實(shí)放在這些數(shù)據(jù)頁中。

二、設(shè)置索引的權(quán)衡

1、什么情況下設(shè)置索引

• 定義主鍵的數(shù)據(jù)列（sql server默認(rèn)會(huì)給主鍵一個(gè)聚集索引）。
• 定義有外鍵的數(shù)據(jù)列
• 對(duì)于經(jīng)常查詢的數(shù)據(jù)列
• 對(duì)于需要在指定范圍內(nèi)頻繁查詢的數(shù)據(jù)列
• 經(jīng)常在where子句中出現(xiàn)的數(shù)據(jù)列
• 經(jīng)常出現(xiàn)在關(guān)鍵字 order by、group by、distinct后面的字段。

2、什么情況下不要設(shè)置索引

• 查詢中很少涉及的列，重復(fù)值比較多的列。
• text、image、bit數(shù)據(jù)類型的列
• 經(jīng)常存取的列
• 經(jīng)常更新操作的表，索引一般不要超過3個(gè)、最多不要5個(gè)。雖說提高了訪問速度，但會(huì)影響更新操作。

三、聚集索引

1、使用SSMS創(chuàng)建聚集索引

展開要?jiǎng)?chuàng)建索引的表->右擊索引->選擇新建索引->聚集索引->新建索引點(diǎn)添加->選擇列->選擇升序或降序->輸入名字->確定。

默認(rèn)情況下，生成主鍵的同時(shí)將自動(dòng)創(chuàng)建一個(gè)聚集索引。

2、使用T-SQL創(chuàng)建聚集索引

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

每張表或者視圖只能包含一個(gè)聚集索引，因?yàn)榫奂饕淖兞藬?shù)據(jù)存儲(chǔ)與排列方式。

無論是聚集還是非聚集索引，都將信息存儲(chǔ)在平衡樹或B-樹中，B-樹識(shí)別類似數(shù)據(jù)并將他們組合在一起，正是由于B-樹中的檢索基于鍵值，因此索引可以提升數(shù)據(jù)訪問的速度。B-樹將具有類似鍵的組合起來，所以數(shù)據(jù)庫引擎只需搜索少量頁面即可找到目標(biāo)記錄。

四、非聚集索引

每張表上可以有多個(gè)非聚集索引，可以在某個(gè)列上創(chuàng)建一個(gè)索引，也可以在已經(jīng)是現(xiàn)有索引組成部分的多列上創(chuàng)建索引。

1、SSMS創(chuàng)建方法同上，T-SQL創(chuàng)建方法如下：

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

2、添加索引選項(xiàng)

fillfactor：用于在創(chuàng)建索引時(shí)，每個(gè)索引頁的數(shù)據(jù)占索引大小的百分比，默認(rèn)100.當(dāng)需要頻繁修改表時(shí)，建議設(shè)置為70-80，不經(jīng)常更新時(shí)建議90。

五、示例

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

--插入10w條測(cè)試數(shù)據(jù)
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

--查看數(shù)據(jù)
set statistics io on --查看磁盤io
set statistics time on --查看sql語句分析編譯和執(zhí)行時(shí)間
select * from ceshi

--查看索引情況
exec sp_helpindex ceshi

select * from ceshi where name = 'name1'

ctrl+l 查看執(zhí)行計(jì)劃 聚集索引掃描開銷100%，考慮優(yōu)化為索引查找，在name上建立非聚集索引。

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

--在運(yùn)行上面的語句
select * from ceshi where name = 'name1'
--明顯發(fā)現(xiàn)速度變快了 ， ctrl+l 發(fā)現(xiàn)聚集索引和非聚集索引各占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) 
--更新表中所有索引的統(tǒng)計(jì)
update statistics ceshi 

到此這篇關(guān)于SQL Server索引的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

最新評(píng)論