建站
咨詢
售后
建站咨詢
新聞中心
索引設(shè)計是數(shù)據(jù)庫設(shè)計中比較重要的一個環(huán)節(jié)，對數(shù)據(jù)庫的性能其中至關(guān)重要的作用，但是索引的設(shè)計卻又不是那么容易的事情，性能也不是那么輕易就獲取到的，很多的技術(shù)人員因為不恰當?shù)膭?chuàng)建索引，***使得其效果適得其反，可以說“成也索引，敗也索引”。
創(chuàng)新互聯(lián)公司是一家專業(yè)提供名山企業(yè)網(wǎng)站建設(shè),專注與成都做網(wǎng)站、網(wǎng)站建設(shè)、H5頁面制作、小程序制作等業(yè)務(wù)。10年已為名山眾多企業(yè)、政府機構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)站制作公司優(yōu)惠進行中。
創(chuàng)建、修改和刪除索引是屬于索引維護部分中的內(nèi)容，作為數(shù)據(jù)庫對象，索引同樣也用CREATE, ALTER和 DROP這三個DDL語句進行操作。但不同的是，對于索引來說這幾個語句所能提供的功能要遠遠超過其名字所示，允許你創(chuàng)建、整理、刪除甚至修改索引的metadata。
當你創(chuàng)建或是修改索引時，你可以設(shè)置一些參數(shù)，這些參數(shù)作為索引的一部分存儲在系統(tǒng)表中，你可以通過sys.indexes系統(tǒng)視圖進行查看。當SQL Server查詢或更新數(shù)據(jù)以及維護索引時需要這些數(shù)據(jù)來幫助更好的完整任務(wù)。本篇文章將會講到這些參數(shù)，但不會深入細節(jié)。
索引所在的表越大時，對其索引的DLL語句影響也就越大。這個影響表現(xiàn)在對于服務(wù)器資源的消耗和降低查詢執(zhí)行速度。所以通過學習當執(zhí)行DDL或DML語句時，索引內(nèi)部的執(zhí)行過程，你可以:
1.理解為什么經(jīng)常需要維護索引
2.執(zhí)行維護操作的過程盡量不降低性能
3.減少維護索引過程對于其它查詢的影響
4.減少索引維護的頻率
創(chuàng)建索引：
我們首先創(chuàng)建聚集索引，然后創(chuàng)建非聚集索引。
創(chuàng)建聚集索引的內(nèi)部過程取決于當前表的狀態(tài)以及創(chuàng)建聚集索引過程中指定的參數(shù)。
假如:
表已經(jīng)是聚集索引了:
發(fā)生錯誤，一個表中不能含有兩個聚集索引，因為同一時間使得數(shù)據(jù)按照不同的物理順序排列是不可能的。
表是空的:
SQL Server僅僅更新系統(tǒng)表來讓自己知道這個表是聚集索引結(jié)構(gòu).不需要分配空間。
表中有數(shù)據(jù)，但表上沒有非聚集索引:
SQL Server更新系統(tǒng)表來讓自己知道這個表是聚集索引結(jié)構(gòu).
SQL Server將表中的行按照索引鍵的數(shù)據(jù)進行排序，根據(jù)指定的填充因子將數(shù)據(jù)填充進頁，然后生成索引的非葉子節(jié)點。這個過程幾乎不存在外部碎片。
表中有數(shù)據(jù)，表上存在非聚集索引:
SQL Server釋放由非聚集索引占用的所有空間，但不刪除其metadata。
SQL Server更新系統(tǒng)表來讓自己知道這個表是聚集索引結(jié)構(gòu)。
SQL Server創(chuàng)建聚集索引(過程看上面)。
非聚集索引通過剛才沒有刪除的metadata進行重建,沒有其它選擇。非聚集索引必須完全重建，因為之前非聚集索引的書簽指向的是rowid，但現(xiàn)在書簽需要存儲鍵值。
因此，如果你需要在表上創(chuàng)建多個索引，先建立聚集索引，然后再建立非聚集索引，這樣更加節(jié)省時間。[[204525]]
創(chuàng)建非聚集索引：
表是空的：
SQL Server僅僅更新系統(tǒng)表來讓自己知道這個表上含有非聚集索引.不需要分配空間。
表中存在數(shù)據(jù)：
SQL Server更新系統(tǒng)表來讓自己知道這個表上含有非聚集索引.不需要分配空間。
SQL Server掃描表，或是其它可以包含這個索引的非聚集索引。為表中的每一行創(chuàng)建索引條目，按照索引鍵排序，根據(jù)指定的填充因子將這些條目填充進頁，然后生成索引的葉子節(jié)點。這個步驟幾乎不會產(chǎn)生外部碎片。
修改索引：
ALTER INDEX語句可以被用來做如下四件事:
1.停用索引
2.重建索引
3.整理索引
4.修改索引選項
注意;ALTER INDEX語句不能修改索引中的索引鍵的組合，如果想要實現(xiàn)這點只能通過刪除索引再建立索引，也可以通過CREATE INDEX語句配上DROP_EXISTING選項。
停用索引：
停用索引只需要使用DISABLE關(guān)鍵字，比如:
- ALTER INDEX PK_FragTest_PKCol ON FragTest DISABLE; GO
停用一個索引并不會使得索引的定義信息從索引表中被移除。所有被停用的索引都可以之后執(zhí)行重建或刪除操作。
停用一個非聚集索引可以將非聚集索引所占用的空間釋放出來，因此當索引被停用之后，SQL Server上運行的查詢就會當作這個索引不存在。
對于停用聚集索引來說，則是釋放掉聚集索引非葉子節(jié)點所占用的空間。因為葉子節(jié)點就是表本身，所以不會釋放葉子節(jié)點，但由于沒有非葉子節(jié)點進行索引，所以被停用的聚集索引(也就是表本身)不能再用于查詢或更新。
停用索引涉及到釋放磁盤空間，因此這個過程需要一些IO操作以及寫入日志文件。
存在索引停用的最重要的目的是為了節(jié)省磁盤空間。假如重建索引的時候不停用索引，則SQL Server需要維護兩個版本的索引，新建的索引成功后才會刪除老索引，因此造成磁盤空間的浪費。而重建索引之前首先刪除索引的話，就能剩下磁盤空間了。通常來說，重建一個已經(jīng)刪除的索引需要的空間是重建沒有刪除索引的五分之一。[[204525]]
重建索引：
重建索引不僅可以重建索引，還可以改變選項，比如:
- ALTER INDEX PK_FragTest_PKCol
- ON FragTest
- REBUILD
- WITH ( FILLFACTOR = 75, SORT_IN_TEMPDB = ON, MAXDOP = 3 ) ;
上面重建索引重新指定的選項會更新到系統(tǒng)表中，其它沒有指定的選項保持不變。此外，索引重建之后，外部碎片幾乎為0.所有頁內(nèi)都填充到填充因子所指定的值。如果上面參數(shù)你還指定了填充因子，這個填充因子在重建索引時立刻生效。[[204525]]
整理索引：
整理索引的目的只有一個:消除碎片。整理索引被用于消除外部碎片，并將頁中填滿到填充因子所指定的程度。雖然整理索引所能提供的選項要小于重建索引，但同時整理索引消耗的資源以及對用戶查詢的影響也是小于重建索引的。
整理索引時要記住的四件事:
1.整理索引不會增加索引的大小，也不需要額外的存儲空間，相反，整理索引會減少索引的大小，并釋放不需要的頁所占的空間。
2.索引在整理的過程中可以繼續(xù)使用。
3.整理索引唯一能修改的選項是LOB_COMPACTION,整理索引不能修改填充因子的值。
4.整理索引需要索引允許頁鎖，這是建立索引時的默認值。因為整理索引的過程中，索引依然可用，SQL Server需要在其它查詢使用索引時對索引中的特定頁進行加鎖。而如果ALLOW_PAGE_LOCKS選項設(shè)置成了OFF，則無法整理索引。
因此，常見的整理索引的語句比如:
或是:
SQL Server將整理索引分為兩個階段執(zhí)行。
階段一：主要整理內(nèi)部碎片
這個階段所能做的是非常有限的，因為正如前面提到的，整理索引不能增加額外的頁。因此如果每頁平均的數(shù)據(jù)小于填充因子標識的數(shù)據(jù)，則可以通過整理索引減少索引大小，但平均數(shù)據(jù)如果大于索引因子的填充值的話，則不能通過整理索引增長索引的大小。
階段1按照邏輯順序處理索引。一次處理八個頁。比如從***頁到第八頁，從第二頁到第九頁，從第三頁到第十頁，直到整個索引被檢查完。對于一次八個頁的檢查來說，SQL Server會看這八個頁中的內(nèi)容是否可以在特定填充因子的情況下壓縮到7個頁中，如果可以，則將這八個頁壓縮到七個頁中并釋放第8個頁。
階段二:主要處理外部碎片
階段二主要按照索引的邏輯順序來整理物理順序。SQL Server讀取邏輯上的***頁和物理上的***頁，如果它們不是同一個頁，則交換其內(nèi)容，每次一頁，直到整理完索引的***一頁。這個過程完成后，則索引的外部碎片被降到了***。
整理索引完成后，外部碎片和內(nèi)部碎片都會降到可以接受的程度。
整理索引和重建索引相比起來雖然功能有限，但這個過程不需要額外的磁盤空間，并只需要非常少量的內(nèi)存消耗。最重要的一點是在整理的過程中索引依然可以使用。
所以對于處理索引碎片的選擇包括了：重建，停用和重建，整理索引。在本系列的第15篇中我將會詳細講述關(guān)于索引的***實踐。
修改索引的Metadata?
有一些索引選項可以在不用重建或是整理索引的情況下進行修改。
下面的示例語句顯示了如何修改這些選項:
刪除索引：
刪除索引后，索引所占用的空間被釋放，并且從系統(tǒng)表中刪除索引的metadata。
我們第八篇關(guān)于唯一索引的部分提到過，你不能在有主鍵或唯一約束的情況下刪除對應(yīng)的索引。
值得注意的是，刪除聚集索引并不會刪除其表，僅僅釋放非葉子節(jié)點。但等同于表本身的葉子節(jié)點并不會被刪除，這些葉子頁將會按堆存放，同時所有的非聚集索引也會被自動重建。因此，如果刪除多個索引時，首先要刪除非聚集索引，然后再刪除聚集索引。[[204525]]
選項：
在使用CREATE INDEX語句時可以設(shè)置的選項分為三類:
1.影響索引創(chuàng)建，但并影響索引使用的選項，大多數(shù)選項都屬于這一類。
2.影響索引的使用，但不影響索引的創(chuàng)建的選項。ALLOW_ROW_LOCKS和ALLOW_PAGE_LOCKS 選項都屬于這一類。
3.既影響索引創(chuàng)建又影響索引使用的選項，比如說DATA_COMPRESSION選項。
下面就是這些選項的說明，除非特別注明了，否則都屬于上面提到的***類選項。
FILLFACTOR:
指定頁面的填充因子，僅僅影響葉子節(jié)點，默認值是0，也就是每一頁允許完全填滿。
PAD_INDEX:
指定填充因子是否可以存在于非葉子節(jié)點。
SORT_IN_TEMPDB：
指定創(chuàng)建索引過程的排序操作實在數(shù)據(jù)庫空間操作還是TempDB上操作。
IGNORE_DUP_KEY:
在第8篇關(guān)于唯一索引的文章說已經(jīng)說過了。
STATISTICS_NORECOMPUTE:
在第14篇索引統(tǒng)計中會詳細說明。
DROP_EXISTING:
注意：這個選項僅僅可以在CREATE INDEX中使用。
DROP_EXISTING = ON:
如果創(chuàng)建過程中已經(jīng)存在了同名的索引和索引類型(類型指的是聚集或是非聚集)，則刪除掉舊的索引并重新創(chuàng)建新的索引。
如果已經(jīng)有了同名索引，但類型不同，則會報錯。
如果沒有同名索引，則根據(jù)定義直接創(chuàng)建新的索引。
DROP_EXISTING = OFF:
如果存在同名索引，則報錯。
如果不存在同名索引，則根據(jù)索引定義直接創(chuàng)建新的索引。
ONLINE:
這個選項可以指定當重建索引的時候其它SPID是否可以訪問這個索引。如果創(chuàng)建的是非聚集索引，則SELECT語句都可以訪問底層表。這個選項只能在企業(yè)版，開發(fā)版和評估版中使用。
ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS:
自動SQL Server 2005開始，允許根據(jù)這個選項來控制鎖升級，詳細如表1所示。
這兩個選項都不會影響索引的創(chuàng)建，它們都是創(chuàng)建之后影響索引使用的選項，如果隔離等級允許行版本控制，則這個選項無關(guān)緊要。
整理索引需要ALLOW_ROW_LOCKS 設(shè)置為ON。
兩個都設(shè)置為OFF，或是設(shè)置其中一個為OFF，使得在大量負載的情況下減少鎖升級。指定這個選項對于大量查詢，很少更新的索引非常有用。
這兩個選項需要你對數(shù)據(jù)庫的原理和鎖的原理有比較透徹的了解。
MAXDOP:
指定創(chuàng)建索引的時候可以使用幾個CPU內(nèi)核。
DATA_COMPRESSION:
數(shù)據(jù)壓縮選項。這個選項不僅影響索引的創(chuàng)建，還會影響索引的使用。有關(guān)數(shù)據(jù)壓縮的話題已經(jīng)超出了本篇文章的范圍。
總結(jié)
CREATE INDEX語句允許你創(chuàng)建索引并設(shè)置選項。
ALTER INDEX可以創(chuàng)建，停用，重建，整理和刪除索引。
ALTER INDEX不能為索引添加或刪除列，只有通過CREATE INDEX語句。
整理索引所需的時間和資源更少，并且在整理的過程中允許繼續(xù)使用索引。
停用非聚集索引使得其占用的空間被釋放，并且不能夠在被SQL Server使用。停用聚集索引使得非葉子節(jié)點所占的空間被釋放并且表不能繼續(xù)被訪問。被停用的索引只能執(zhí)行重建或刪除操作。重建一個已經(jīng)存在的索引所需的空間要大于重建被停用的索引。
很多選項只能在重建索引的過程中應(yīng)用。
創(chuàng)建或刪除聚集索引導致其相關(guān)聯(lián)的所有非聚集索引重建。
當一個表需要多個索引時，要先創(chuàng)建聚集索引，再創(chuàng)建非聚集索引。而刪除的過程則相反。
刪除聚集索引并不會導致刪除表，而是使得表中數(shù)據(jù)按堆存放以及相關(guān)的非聚集索引被重建。
分享文章：關(guān)于索引的創(chuàng)建，修改和刪除
本文地址：http://m.fisionsoft.com.cn/article/dhjcdeg.html
