7、SQL Server索引、表壓縮

標籤：

索引

什麼是索引？

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

索引主要分為兩種類型：叢集索引和非叢集索引。

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

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

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

索引的利弊

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

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

索引的儲存機制

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

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

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

資料表的基本結構

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

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

什麼情況下設定索引

1、定義主鍵的資料列（sql server預設會給主鍵一個叢集索引）。

2、定義有外鍵的資料列

3、對於經常查詢的資料列

4、對於需要在指定範圍內頻繁查詢的資料列

5、經常在where子句中出現的資料列

6、經常出現在關鍵字 order by、group by、distinct後面的欄位。

什麼情況下不要設定索引

1、查詢中很少涉及的列，重複值比較多的列。

2、text、image、bit資料類型的列

3、經常存取的列

4、經常更新操作的表，索引一般不要超過3個、最多不要5個。雖說提高了訪問速度，但會影響更新操作。

叢集索引

1、使用SSMS建立叢集索引

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

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

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

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

每張表或者視圖只能包含一個叢集索引，因為叢集索引改變了資料存放區與相片順序。無論是聚集還是非叢集索引，都將資訊儲存在平衡樹或B-樹中，B-樹識別類似資料並將他們組合在一起，正是由於B-樹中的檢索基於索引值，因此索引可以提升資料訪問的速度。B-樹將具有類似鍵的組合起來，所以資料庫引擎只需搜尋少量頁面即可找到目標記錄。

非叢集索引

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

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

use webDBgocreate nonclustered index fei /*叢集索引名*/on defualt(    hits desc)

添加索引選項

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

pad_index:用於索引中間級中每個頁上保持開放的空間。不能設定值，他的值繼承自fillfactor。

小例子

use webDBgocreate table ceshi --建立表(    id int identity(1,1) primary key,    name varchar(20),    code varchar(20),    [date] datetime)--插入10w條測試資料declare @n intset @n = 1while @n <100000beginInsert into ceshi (name,code,[date]) values (‘name‘+cast(@n as varchar(20)),‘code‘+cast(@n as varchar(20)),getutcdate())set @n=@n+1end--查看索引情況set statistics io on --查看磁碟ioset statistics time on --查看sql語句分析編譯和執行時間select * from ceshi--查看索引情況exec sp_helpindex ceshiselect * 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的主要效能取決於磁碟I/O效率，SQL Server 2008提供了資料壓縮功能來提高磁碟I/O效率。

表壓縮意味著減小資料的磁碟佔有量，所以壓縮可以用在堆表、叢集索引的表、非叢集索引的表、索引檢視表、分區表上。

可壓縮的資料類型

smallint、int、Bigint、decimal、numeric、real、float、money、smallmoeny、bit、datetime、datetime2、datetimeoffset、char、nchar、binary、rowversion。

SQLServer中有兩種壓縮類型：資料與備份

行壓縮

壓縮會改變資料的實體儲存體方式，但不需要對代碼做任何修改。

行壓縮流程：首先識別表中每一列的資料類型，然後轉換為可變長度，最後將儲存空間的請求總量減少到實際需求量。

如：固定長度的類型int、char、nchar等，在資料頁中以不定長度的方式儲存(儲存真實資料長度)。

1、SSMS行壓縮

表->右鍵->儲存->管理壓縮->對所有分區使用相同壓縮類型->右側 選擇row->立即執行->完成。

在壓縮堆表或叢集索引時並不同時包含非叢集索引，因此需要另外單獨對非叢集索引進行操作。

索引->右鍵->儲存->管理壓縮->對所有分區使用相同壓縮類型->右側 選擇row->立即執行->完成。

2、T-SQL行壓縮

 

--在現有表進行壓縮--聚集alter table ceshi rebuild with(data_compression=row)--非聚集alter index new_name on ceshi rebuild with(data_compression=row) --在建立表時進行壓縮create table yasuo(    id int primary key,    name varchar(50),    mail varchar(50))with (data_compression=row)

 

建立時指定行壓縮方式，這時並未發生改變。只要資料插入表中，該行即被壓縮。

頁壓縮

頁壓縮通過執行額外的一些步驟增強了行壓縮的功能。

頁壓縮步驟：行壓縮、首碼壓縮、字典壓縮。

首先對於每一列將確定一個值，此值可以減少每一列中值的儲存空間。一旦確定該值後，每一列的首碼值的行將被儲存在頁頭中。所有的資訊稱為壓縮資訊，儲存在頁頭之下。標識的值（首碼值）位於沒列中，將由指向壓縮資訊部分中對應值的引用進行替換。

下一步字典壓縮，搜尋整個頁面而非單個列，重複值被移動到頁頭的壓縮資訊部分，取而代之的是指向該值的引用。

在SSMS中頁壓縮步驟與行壓縮步驟一致，只是選擇壓縮方式為Page。T-SQL中將row改成page即可。

需要注意

1、如果保留在記憶體中的資料是壓縮的，一旦被選中，則必須先進行解壓縮。

2、在插入新行時，資料也是行或頁壓縮的。

3、當更新或刪除時，行壓縮對象保留當前的壓縮層級。但是頁壓縮可能需要重新計算，取決於發生變化的資料量。

用哪種壓縮

需要頻繁更新的對象應該使用行壓縮。

只是執行讀取操作的應該使用頁壓縮。

 

7、SQL Server索引、表壓縮