如何利用索引提高SQLServer數(shù)據(jù)處理的效率

在良好的數(shù)據(jù)庫設(shè)計基礎(chǔ)上，能有效地使用索引是SQL Server取得高性能的基礎(chǔ)，SQL Server采用基于代價的優(yōu)化模型，它對每一個提交的有關(guān)表的查詢，決定是否使用索引或用哪一個索引。因為查詢執(zhí)行的大部分開銷是磁盤I/O，使用索引提高性能的一個主要目標(biāo)是避免全表掃描，因為全表掃描需要從磁盤上讀表的每一個數(shù)據(jù)頁，如果有索引指向數(shù)據(jù)值，則查詢只需讀幾次磁盤就可以了。

創(chuàng)新互聯(lián)建站專注于企業(yè)全網(wǎng)營銷推廣、網(wǎng)站重做改版、獨(dú)山網(wǎng)站定制設(shè)計、自適應(yīng)品牌網(wǎng)站建設(shè)、html5、購物商城網(wǎng)站建設(shè)、集團(tuán)公司官網(wǎng)建設(shè)、外貿(mào)網(wǎng)站制作、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁設(shè)計等建站業(yè)務(wù)，價格優(yōu)惠性價比高，為獨(dú)山等各大城市提供網(wǎng)站開發(fā)制作服務(wù)。

所以如果建立了合理的索引，優(yōu)化器就能利用索引加速數(shù)據(jù)的查詢過程。但是，索引并不總是提高系統(tǒng)的性能，在增、刪、改操作中索引的存在會增加一定的工作量，因此，在適當(dāng)?shù)牡胤皆黾舆m當(dāng)?shù)乃饕牟缓侠淼牡胤絼h除次優(yōu)的索引，將有助于優(yōu)化那些性能較差的SQL Server應(yīng)用。實(shí)踐表明，合理的索引設(shè)計是建立在對各種查詢的分析和預(yù)測上的，只有正確地使索引與程序結(jié)合起來,才能產(chǎn)生最佳的優(yōu)化方案。本文就SQL Server索引的性能問題進(jìn)行了一些分析和實(shí)踐。

一、聚簇索引(clustered indexes)的使用

聚簇索引是一種對磁盤上實(shí)際數(shù)據(jù)重新組織以按指定的一個或多個列的值排序。由于聚簇索引的索引頁面指針指向數(shù)據(jù)頁面，所以使用聚簇索引查找數(shù)據(jù)幾乎總是比使用非聚簇索引快。每張表只能建一個聚簇索引，并且建聚簇索引需要至少相當(dāng)該表120%的附加空間，以存放該表的副本和索引中間頁。建立聚簇索引的思想是：

1、大多數(shù)表都應(yīng)該有聚簇索引或使用分區(qū)來降低對表尾頁的競爭，在一個高事務(wù)的環(huán)境中，對最后一頁的封鎖嚴(yán)重影響系統(tǒng)的吞吐量。

2、在聚簇索引下，數(shù)據(jù)在物理上按順序排在數(shù)據(jù)頁上，重復(fù)值也排在一起，因而在那些包含范圍檢查(between、、=、、=)或使用group by或order by的查詢時，一旦找到具有范圍中第一個鍵值的行，具有后續(xù)索引值的行保證物理上毗連在一起而不必進(jìn)一步搜索，避免了大范圍掃描，可以大大提高查詢速度。

3、在一個頻繁發(fā)生插入操作的表上建立聚簇索引時，不要建在具有單調(diào)上升值的列(如IDENTITY)上，否則會經(jīng)常引起封鎖沖突。

4、在聚簇索引中不要包含經(jīng)常修改的列，因為碼值修改后，數(shù)據(jù)行必須移動到新的位置。

5、選擇聚簇索引應(yīng)基于where子句和連接操作的類型。

聚簇索引的侯選列是：

1、主鍵列,該列在where子句中使用并且插入是隨機(jī)的。

2、按范圍存取的列，如pri_order 100 and pri_order 200。

3、在group by或order by中使用的列。

4、不經(jīng)常修改的列。

5、在連接操作中使用的列。

二、非聚簇索引(nonclustered indexes)的使用

SQL Server缺省情況下建立的索引是非聚簇索引，由于非聚簇索引不重新組織表中的數(shù)據(jù)，而是對每一行存儲索引列值并用一個指針指向數(shù)據(jù)所在的頁面。換句話說非聚簇索引具有在索引結(jié)構(gòu)和數(shù)據(jù)本身之間的一個額外級。一個表如果沒有聚簇索引時,可有250個非聚簇索引。每個非聚簇索引提供訪問數(shù)據(jù)的不同排序順序。在建立非聚簇索引時，要權(quán)衡索引對查詢速度的加快與降低修改速度之間的利弊。另外，還要考慮這些問題：

1、索引需要使用多少空間。

2、合適的列是否穩(wěn)定。

3、索引鍵是如何選擇的，掃描效果是否更佳。

4、是否有許多重復(fù)值。

對更新頻繁的表來說，表上的非聚簇索引比聚簇索引和根本沒有索引需要更多的額外開銷。對移到新頁的每一行而言，指向該數(shù)據(jù)的每個非聚簇索引的頁級行也必須更新，有時可能還需要索引頁的分理。從一個頁面刪除數(shù)據(jù)的進(jìn)程也會有類似的開銷，另外，刪除進(jìn)程還必須把數(shù)據(jù)移到頁面上部，以保證數(shù)據(jù)的連續(xù)性。所以，建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情況：

1、某列常用于集合函數(shù)(如Sum,....)。

2、某列常用于join,order by,group by。

3、查尋出的數(shù)據(jù)不超過表中數(shù)據(jù)量的20%。

三、覆蓋索引(covering indexes)的使用

覆蓋索引是指那些索引項中包含查尋所需要的全部信息的非聚簇索引，這種索引之所以比較快也正是因為索引頁中包含了查尋所必須的數(shù)據(jù),不需去訪問數(shù)據(jù)頁。如果非聚簇索引中包含結(jié)果數(shù)據(jù),那么它的查詢速度將快于聚簇索引。

但是由于覆蓋索引的索引項比較多,要占用比較大的空間。而且update操作會引起索引值改變。所以如果潛在的覆蓋查詢并不常用或不太關(guān)鍵，則覆蓋索引的增加反而會降低性能。

四、索引的選擇技術(shù)

p_detail是住房公積金管理系統(tǒng)中記錄個人明細(xì)的表，有890000行，觀察在不同索引下的查詢運(yùn)行效果，測試在C/S環(huán)境下進(jìn)行，客戶機(jī)是IBM PII350(內(nèi)存64M),服務(wù)器是DEC Alpha1000A(內(nèi)存128M),數(shù)據(jù)庫為SYBASE11.0.3。

1、 select count(*) from p_detail where

op_date’19990101’ and op_date’

19991231’ and pri_surplus1300

2、 select count(*),sum(pri_surplus1) from p_detail

where op_date’19990101’ and

pay_month between‘199908’ and’199912’

不建任何索引查詢1 1分15秒

查詢2 1分7秒

在op_date上建非聚簇索引查詢1 57秒

查詢2 57秒

在op_date上建聚簇索引查詢1 1秒

查詢2 52秒

在pay_month、op_date、pri_surplus1上建索引查詢1 34秒

查詢2 1秒

在op_date、pay_month、pri_surplus1上建索引查詢1 1秒

查詢2 1秒

從以上查詢效果分析，索引的有無，建立方式的不同將會導(dǎo)致不同的查詢效果，選擇什么樣的索引基于用戶對數(shù)據(jù)的查詢條件,這些條件體現(xiàn)于where從句和join表達(dá)式中。一般來說建立索引的思路是：

(1)主鍵時常作為where子句的條件，應(yīng)在表的主鍵列上建立聚簇索引，尤其當(dāng)經(jīng)常用它作為連接的時候。

(2)有大量重復(fù)值且經(jīng)常有范圍查詢和排序、分組發(fā)生的列，或者非常頻繁地被訪問的列，可考慮建立聚簇索引。

(3)經(jīng)常同時存取多列，且每列都含有重復(fù)值可考慮建立復(fù)合索引來覆蓋一個或一組查詢，并把查詢引用最頻繁的列作為前導(dǎo)列，如果可能盡量使關(guān)鍵查詢形成覆蓋查詢。

(4)如果知道索引鍵的所有值都是唯一的，那么確保把索引定義成唯一索引。

(5)在一個經(jīng)常做插入操作的表上建索引時，使用fillfactor(填充因子)來減少頁分裂，同時提高并發(fā)度降低死鎖的發(fā)生。如果在只讀表上建索引，則可以把fillfactor置為100。

(6)在選擇索引鍵時，設(shè)法選擇那些采用小數(shù)據(jù)類型的列作為鍵以使每個索引頁能夠容納盡可能多的索引鍵和指針，通過這種方式，可使一個查詢必須遍歷的索引頁面降到最小。此外，盡可能地使用整數(shù)為鍵值，因為它能夠提供比任何數(shù)據(jù)類型都快的訪問速度。

五、索引的維護(hù)

上面講到,某些不合適的索引影響到SQL Server的性能,隨著應(yīng)用系統(tǒng)的運(yùn)行,數(shù)據(jù)不斷地發(fā)生變化,當(dāng)數(shù)據(jù)變化達(dá)到某一個程度時將會影響到索引的使用。這時需要用戶自己來維護(hù)索引。索引的維護(hù)包括：

1、重建索引

隨著數(shù)據(jù)行的插入、刪除和數(shù)據(jù)頁的分裂，有些索引頁可能只包含幾頁數(shù)據(jù)，另外應(yīng)用在執(zhí)行大塊I/O的時候，重建非聚簇索引可以降低分片，維護(hù)大塊I/O的效率。重建索引實(shí)際上是重新組織B-樹空間。在下面情況下需要重建索引：

(1)數(shù)據(jù)和使用模式大幅度變化。

(2)排序的順序發(fā)生改變。

(3)要進(jìn)行大量插入操作或已經(jīng)完成。

(4)使用大塊I/O的查詢的磁盤讀次數(shù)比預(yù)料的要多。

(5)由于大量數(shù)據(jù)修改，使得數(shù)據(jù)頁和索引頁沒有充分使用而導(dǎo)致空間的使用超出估算。

(6)dbcc檢查出索引有問題。

當(dāng)重建聚簇索引時，這張表的所有非聚簇索引將被重建。

2、索引統(tǒng)計信息的更新

當(dāng)在一個包含數(shù)據(jù)的表上創(chuàng)建索引的時候，SQL Server會創(chuàng)建分布數(shù)據(jù)頁來存放有關(guān)索引的兩種統(tǒng)計信息：分布表和密度表。優(yōu)化器利用這個頁來判斷該索引對某個特定查詢是否有用。但這個統(tǒng)計信息并不動態(tài)地重新計算。這意味著，當(dāng)表的數(shù)據(jù)改變之后，統(tǒng)計信息有可能是過時的，從而影響優(yōu)化器追求最有工作的目標(biāo)。因此，在下面情況下應(yīng)該運(yùn)行update statistics命令：

(1)數(shù)據(jù)行的插入和刪除修改了數(shù)據(jù)的分布。

(2)對用truncate table刪除數(shù)據(jù)的表上增加數(shù)據(jù)行。

(3)修改索引列的值。

六、結(jié)束語

實(shí)踐表明，不恰當(dāng)?shù)乃饕坏谑聼o補(bǔ)，反而會降低系統(tǒng)的執(zhí)行性能。因為大量的索引在插入、修改和刪除操作時比沒有索引花費(fèi)更多的系統(tǒng)時間。例如下面情況下建立的索引是不恰當(dāng)?shù)模?/p>

1、在查詢中很少或從不引用的列不會受益于索引，因為索引很少或從來不必搜索基于這些列的行。

2、只有兩個或三個值的列，如男性和女性(是或否)，從不會從索引中得到好處。

另外，鑒于索引加快了查詢速度，但減慢了數(shù)據(jù)更新速度的特點(diǎn)?？赏ㄟ^在一個段上建表，而在另一個段上建其非聚簇索引，而這兩段分別在單獨(dú)的物理設(shè)備上來改善操作性能。

SqlServer是如何管理，分配存儲空間的呢

Sql Server 區(qū)管理(GAM,SGAM)

大家都知道Sql Server 中數(shù)據(jù)文件存儲的最小單位是頁面(Page),但實(shí)際SQLSERVE并不是以頁面為單位給數(shù)據(jù)分配空間的，Sql Server默認(rèn)的存儲分配單位是盤區(qū)(Extend)。這樣做的主要原因是為了避免頻繁的讀寫IO，提升性能。在表或其它對象分配存儲空間，不是直接分配一個8K的頁面，而是以一個盤區(qū)(Extend)為存儲分配單位，一個盤區(qū)為8個頁面(Size = 8＊8K＝64K)。

這樣，對區(qū)得操作就會非常頻繁，也要求Sql Server有自己的一套系統(tǒng)管理著數(shù)量眾多的區(qū)。其中最突出的出一個問題，那就是在存儲那些只有少量數(shù)據(jù)，不足8K的對象，如果也是分配給一個盤區(qū)，就會存在存儲空間上的浪費(fèi)，降低了空間分配效率。

為解決上述問題，SQLSERVER提供了一種解決方案，定義了兩種盤區(qū)類型,統(tǒng)一盤區(qū)和混合盤區(qū)。

全局分配映射表 (GAM)?：統(tǒng)一盤區(qū)，GAM 頁記錄已分配的區(qū)。每個 GAM 包含 64,000 個區(qū)，相當(dāng)于近 4 GB 的數(shù)據(jù)。GAM 用一個位來表示所涵蓋區(qū)間內(nèi)的每個區(qū)的狀態(tài)。如果位為 1，則區(qū)可用；如果位為 0，則區(qū)已分配。?

共享全局分配映射表 (SGAM)?：由多個對象共同擁有該盤區(qū)，SGAM 頁記錄當(dāng)前用作混合區(qū)且至少有一個未使用的頁的區(qū)。每個 SGAM 包含 64,000 個區(qū)，相當(dāng)于近 4 GB 的數(shù)據(jù)。SGAM 用一個位來表示所涵蓋區(qū)間內(nèi)的每個區(qū)的狀態(tài)。如果位為 1，則區(qū)正用作混合區(qū)且有可用頁。如果位為 0，則區(qū)未用作混合區(qū)，或者雖然用作混合區(qū)但其所有頁均在使用中。?

在實(shí)際為對象分配存儲盤區(qū)時，為了提高空間利用率，默認(rèn)的情況下，如果一個對象一開始大小小于8個頁面，就盡量放在混合盤區(qū)中，如果該對象大小增加到8個頁面后，SQLSERVER會為這個對象重新分配一個統(tǒng)一盤區(qū)。

據(jù)區(qū)當(dāng)前的使用情況，GAM 和 SGAM 中每個區(qū)具有以下位模式：

這將簡化區(qū)管理算法。若要分配統(tǒng)一區(qū)，數(shù)據(jù)庫引擎將在 GAM 中搜索為 1 的位，并將其設(shè)置為 0。若要查找具有可用頁的混合區(qū)，數(shù)據(jù)庫引擎將在 SGAM 中搜索為 1 的位。若要分配混合區(qū)，數(shù)據(jù)庫引擎將在 GAM 中搜索為 1 的位，將其設(shè)置為 0，然后將 SGAM 中對應(yīng)的位設(shè)置為 1。若要釋放區(qū)，數(shù)據(jù)庫引擎確保將 GAM 位設(shè)置為 1，將 SGAM 位設(shè)置為 0。實(shí)際上，數(shù)據(jù)庫引擎內(nèi)部使用的算法比本主題中介紹的更為復(fù)雜，因為數(shù)據(jù)庫引擎在數(shù)據(jù)庫中均勻分布數(shù)據(jù)。但是，由于無需管理區(qū)分配信息鏈，因此即使是實(shí)際算法也會被簡化。

管理Sql Server可用空間

首先摘錄段 MSDN 的一段官方解釋：

頁可用空間 (PFS) 頁記錄每頁的分配狀態(tài)，是否已分配單個頁以及每頁的可用空間量。PFS 對每頁都有一個字節(jié)，記錄該頁是否已分配。如果已分配，則記錄該頁是為空、已滿 1% 到 50%、已滿 51% 到 80%、已滿 81% 到 95% 還是已滿 96% 到 100%。

將區(qū)分配給對象后，數(shù)據(jù)庫引擎將使用 PFS 頁來記錄區(qū)中的哪些頁已分配或哪些頁可用。數(shù)據(jù)庫引擎必須分配新頁時，將使用此信息。保留的頁中的可用空間量僅用于堆和 Text/Image 頁。數(shù)據(jù)庫引擎必須找到一個具有可用空間的頁來保存新插入的行時，使用此信息。索引不要求跟蹤頁的可用空間，因為插入新行的點(diǎn)是由索引鍵值設(shè)置的。

在數(shù)據(jù)文件中，PFS 頁是文件頭頁之后的第一頁（頁碼為 1）。接著是 GAM 頁（頁碼為 2），然后是 SGAM 頁（頁碼為 3）。第一個 PFS 頁之后是一個大小大約為 8,000 頁的 PFS 頁。在第 2 頁的第一個 GAM 頁之后還有另一個 GAM 頁（包含 64,000 個區(qū)），在第 3 頁的第一個 SGAM 頁之后也有另一個 SGAM 頁（包含 64,000 個區(qū)）。下圖顯示了數(shù)據(jù)庫引擎用來分配和管理區(qū)的頁順序。

看過之后，讓人一頭霧水，真是不知所云，真佩服這些 MSDN 是如何翻譯的，看來中文 MSDN 太不靠譜，最后沒辦法，只能google了

其實(shí)上面說的意思就是：Sql Server 管理可用空間的方法是，查找每個每個頁面是否使用，以及使用情況情況。這時就需要一個頁面來記錄各個頁面的使用情況了，這就是 PFS 頁。

PFS(Page Free Space)，也叫頁面自由空間，該頁面用來跟蹤一個文件中每一個特定的頁面的利用率情況。一個文件中第二個頁面(頁碼1)就是PFS頁面，該頁面的每個字節(jié)都記錄了相應(yīng)頁面的分配情況、頁面類型、是否IAM頁、是否包含刪除記錄、以及空間利用率信息;PFS能夠管理和跟蹤8088個頁面的使用情況，即接近64M的空間，以后每8088個頁面將再出現(xiàn)一次。

讓我們首先了解一下PFS的頁面管理字節(jié)的構(gòu)造，管理單位為字節(jié)，每字節(jié)管理一個頁面。

第0個bit為保留字節(jié)，始終為0

第1個bit表示該頁面是否已分配，我們知道GAM頁用來管理區(qū)是否已分配，但一個區(qū)包含8個頁面，所以用該bit用來準(zhǔn)確定位該區(qū)的某個頁面是否已分配出去了。

第2個bit表示該頁面是否混合分區(qū)的一個頁面。

第3個bit表示該頁面是否是一個IAM(索引分配映射)頁面。

第4個bit表示該頁面中是否包含幻影或已刪除記錄，這有助于SQL Server定期清理幻影或已刪除記錄。

第5~7個頁面表示該頁面的空間使用率情況。

關(guān)于sqlserver2000的分布顯示

sqlserver2000中可以用top關(guān)鍵字,如顯示第11到20條記錄:

select top 20 * from table where id not in (select top 10 id from table)

本文名稱：sqlserver分布,sqlserver分布式重播客戶端
網(wǎng)站網(wǎng)址：http://aaarwkj.com/article16/dssgpgg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供建站公司、網(wǎng)站營銷、定制網(wǎng)站、全網(wǎng)營銷推廣、微信小程序、ChatGPT

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)