時(shí)間：2015-06-28 00:00:00 來(lái)源：IT貓撲網(wǎng) 作者：網(wǎng)管聯(lián)盟 我要評(píng)論(0)
1 SQL Server中的索引
索引是與表或視圖關(guān)聯(lián)的磁盤(pán)上結(jié)構(gòu)，可以加快從表或視圖中檢索行的速度。索引包含由表或視圖中的一列或多列生成的鍵。這些鍵存儲(chǔ)在一個(gè)結(jié)構(gòu)(B 樹(shù))中，使 SQL Server 可以快速有效地查找與鍵值關(guān)聯(lián)的行。
表或視圖可以包含以下類(lèi)型的索引：
聚集索引
聚集索引根據(jù)數(shù)據(jù)行的鍵值在表或視圖中排序和存儲(chǔ)這些數(shù)據(jù)行。索引定義中包含聚集索引列。每個(gè)表只能有一個(gè)聚集索引，因?yàn)閿?shù)據(jù)行本身只能按一個(gè)順序排序。
只有當(dāng)表包含聚集索引時(shí)，表中的數(shù)據(jù)行才按排序順序存儲(chǔ)。如果表具有聚集索引，則該表稱(chēng)為聚集表。如果表沒(méi)有聚集索引，則其數(shù)據(jù)行存儲(chǔ)在一個(gè)稱(chēng)為堆的無(wú)序結(jié)構(gòu)中。
每個(gè)表幾乎都對(duì)列定義聚集索引來(lái)實(shí)現(xiàn)下列功能：
1、可用于經(jīng)常使用的查詢(xún)。
2、提供高度唯一性。
在創(chuàng)建聚集索引之前，應(yīng)先了解數(shù)據(jù)是如何被訪(fǎng)問(wèn)的。考慮對(duì)具有以下特點(diǎn)的查詢(xún)使用聚集索引：
使用運(yùn)算符(如 BETWEEN、>、>=、< 和 <=)返回一系列值。
使用聚集索引找到包含第一個(gè)值的行后，便可以確保包含后續(xù)索引值的行物理相鄰。例如，如果某個(gè)查詢(xún)?cè)谝幌盗胁少?gòu)訂單號(hào)間檢索記 錄，PurchaseOrderNumber 列的聚集索引可快速定位包含起始采購(gòu)訂單號(hào)的行，然后檢索表中所有連續(xù)的行，直到檢索到最后的采購(gòu)訂單號(hào)。
返回大型結(jié)果集。
使用 JOIN 子句;一般情況下，使用該子句的是外鍵列。
使用 ORDER BY 或 GROUP BY 子句。
在 ORDER BY 或 GROUP BY 子句中指定的列的索引，可以使數(shù)據(jù)庫(kù)引擎 不必對(duì)數(shù)據(jù)進(jìn)行排序，因?yàn)檫@些行已經(jīng)排序。這樣可以提高查詢(xún)性能。
聚集索引不適用于具有下列屬性的列：
頻繁更改的列
這將導(dǎo)致整行移動(dòng)，因?yàn)閿?shù)據(jù)庫(kù)引擎 必須按物理順序保留行中的數(shù)據(jù)值。這一點(diǎn)要特別注意，因?yàn)樵诖笕萘渴聞?wù)處理系統(tǒng)中數(shù)據(jù)通常是可變的。
寬鍵
寬鍵是若干列或若干大型列的組合。所有非聚集索引將聚集索引中的鍵值用作查找鍵。為同一表定義的任何非聚集索引都將增大許多，這是因?yàn)榉蔷奂饕?xiàng)包含聚集鍵，同時(shí)也包含為此非聚集索引定義的鍵列?！》蔷奂饕?/p>
非聚集索引具有獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu)。非聚集索引包含非聚集索引鍵值，并且每個(gè)鍵值項(xiàng)都有指向包含該鍵值的數(shù)據(jù)行的指針。
從非聚集索引中的索引行指向數(shù)據(jù)行的指針?lè)Q為行定位器。行定位器的結(jié)構(gòu)取決于數(shù)據(jù)頁(yè)是存儲(chǔ)在堆中還是聚集表中。對(duì)于堆，行定位器是指向行的指針。對(duì)于聚集表，行定位器是聚集索引鍵。
在 SQL Server 2005 中，可以向非聚集索引的葉級(jí)別添加非鍵列以跳過(guò)現(xiàn)有的索引鍵限制(900 字節(jié)和 16 鍵列)，并執(zhí)行完整范圍內(nèi)的索引查詢(xún)。
非聚集索引與聚集索引具有相同的 B 樹(shù)結(jié)構(gòu)，它們之間的顯著差別在于以下兩點(diǎn)：
1、基礎(chǔ)表的數(shù)據(jù)行不按非聚集鍵的順序排序和存儲(chǔ)。
2、非聚集索引的葉層是由索引頁(yè)而不是由數(shù)據(jù)頁(yè)組成。
設(shè)計(jì)非聚集索引時(shí)需要注意數(shù)據(jù)庫(kù)的特征:
更新要求較低但包含大量數(shù)據(jù)的數(shù)據(jù)庫(kù)或表可以從許多非聚集索引中獲益從而改善查詢(xún)性能。
決策支持系統(tǒng)應(yīng)用程序和主要包含只讀數(shù)據(jù)的數(shù)據(jù)庫(kù)可以從許多非聚集索引中獲益。查詢(xún)優(yōu)化器具有更多可供選擇的索引用來(lái)確定最快的訪(fǎng)問(wèn)方法，并且數(shù)據(jù)庫(kù)的低更新特征意味著索引維護(hù)不會(huì)降低性能。
聯(lián)機(jī)事務(wù)處理應(yīng)用程序和包含大量更新表的數(shù)據(jù)庫(kù)應(yīng)避免使用過(guò)多的索引。此外，索引應(yīng)該是窄的，即列越少越好。
一個(gè)表如果建有大量索引會(huì)影響 INSERT、UPDATE 和 DELETE 語(yǔ)句的性能，因?yàn)樗兴饕急仨氹S表中數(shù)據(jù)的更改進(jìn)行相應(yīng)的調(diào)整。
唯一索引
唯一索引確保索引鍵不包含重復(fù)的值，因此，表或視圖中的每一行在某種程度上是唯一的。
聚集索引和非聚集索引都可以是唯一索引。
包含性列索引
一種非聚集索引，它擴(kuò)展后不僅包含鍵列，還包含非鍵列。
索引涵蓋
指查詢(xún)中的SELECT與WHERE子句的所用列同時(shí)也屬于非聚集索引的情況。這樣就可以更快檢索數(shù)據(jù)，因?yàn)樗行畔⒍伎梢灾苯觼?lái)自于索引頁(yè)，從而SQL Server可以避免訪(fǎng)問(wèn)數(shù)據(jù)頁(yè)。加上獨(dú)立的索引文件組，可以用最快速度訪(fǎng)問(wèn)數(shù)據(jù)。
請(qǐng)看如下表示例：
A.創(chuàng)建簡(jiǎn)單非聚集索引 以下示例為 Purchasing.ProductVendor 表的 VendorID 列創(chuàng)建非聚集索引。
USE AdventureWorks;
GO
CREATE INDEX IX_ProductVendor_VendorID
ON Purchasing.ProductVendor (VendorID);
GO
B. 創(chuàng)建簡(jiǎn)單非聚集組合索引
以下示例為 Sales.SalesPerson 表的 SalesQuota 和 SalesYTD 列創(chuàng)建非聚集組合索引。
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
ON Sales.SalesPerson (SalesQuota, SalesYTD);
GO
C. 創(chuàng)建唯一非聚集索引
以下示例為 Production.UnitMeasure 表的 Name 列創(chuàng)建唯一的非聚集索引。該索引將強(qiáng)制插入 Name 列中的數(shù)據(jù)具有唯一性。
USE AdventureWorks;
GO
CREATE UNIQUE INDEX AK_UnitMeasure_Name
ON Production.UnitMeasure(Name);
GO
無(wú)論何時(shí)對(duì)基礎(chǔ)數(shù)據(jù)執(zhí)行插入、更新或刪除操作，SQL Server 2005 數(shù)據(jù)庫(kù)引擎都會(huì)自動(dòng)維護(hù)索引。隨著時(shí)間的推移，這些修改可能會(huì)導(dǎo)致索引中的信息分散在數(shù)據(jù)庫(kù)中(含有碎片)。當(dāng)索引包含的頁(yè)中的邏輯排序(基于鍵值)與數(shù) 據(jù)文件中的物理排序不匹配時(shí)，就存在碎片。碎片非常多的索引可能會(huì)降低查詢(xún)性能，導(dǎo)致應(yīng)用程序響應(yīng)緩慢。這個(gè)時(shí)候，我們需要做得就是重新組織和重新生成索 引。重新生成索引將刪除該索引并創(chuàng)建一個(gè)新索引。此過(guò)程中將刪除碎片，通過(guò)使用指定的或現(xiàn)有的填充因子設(shè)置壓縮頁(yè)來(lái)回收磁盤(pán)空間，并在連續(xù)頁(yè)中對(duì)索引行重 新排序(根據(jù)需要分配新頁(yè))。這樣可以減少獲取所請(qǐng)求數(shù)據(jù)所需的頁(yè)讀取數(shù)，從而提高磁盤(pán)性能。
可以使用下列方法重新生成聚集索引和非聚集索引：
帶 REBUILD 子句的 ALTER INDEX。此語(yǔ)句將替換 DBCC DBREINDEX 語(yǔ)句。
帶 DROP_EXISTING 子句的 CREATE INDEX。
示例如下：
A. 重新生成索引
以下示例將重新生成單個(gè)索引。
USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO
B.重新生成表的所有索引并指定選項(xiàng)
下面的示例指定了 ALL 關(guān)鍵字。這將重新生成與表相關(guān)聯(lián)的所有索引。其中指定了三個(gè)選項(xiàng)。
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO
2 Oracle 中的索引
索引是Oracle使用的加速表中數(shù)據(jù)檢索的數(shù)據(jù)庫(kù)對(duì)象。
下面的情況，可以考慮使用索引：
1) 大表
2) 主鍵(自動(dòng)索引)
3) 單鍵列(自動(dòng)索引)
4) 外鍵列(自動(dòng)索引)
5) 大表上WHERE子句常用的列
6) ORDER BY 或者GROUP BY子句中使用的列。
7) 至少返回表中20%行的查詢(xún)
8) 不包含null值的列。
#p#副標(biāo)題#e#
Oracle中的索引包含有如下幾種類(lèi)型：
B*樹(shù)索引：這是Oracle中最常用的索引，它的構(gòu)造類(lèi)似于二叉樹(shù)，能根據(jù)鍵提供一行或一個(gè)行集的快速訪(fǎng)問(wèn)，通常只需要很少的讀操作就能找到正確的行。B*樹(shù)索引由兩列組成，第一列是ROWID, 它是行的位置;第二列是正被索引列的值。
圖:典型的B*樹(shù)索引布局
這個(gè)樹(shù)底層的塊稱(chēng)為葉子節(jié)點(diǎn)(leaf node) 或(leaf block),其中分別包含各個(gè)索引鍵以及一個(gè)rowid(它是指向所索引的行)。葉子節(jié)點(diǎn)之上的內(nèi)部塊稱(chēng)為分支塊(branch block)，這些節(jié)點(diǎn)用于實(shí)現(xiàn)導(dǎo)航。例如，如果想在索引中找到值20，要從樹(shù)頂開(kāi)始，找到左分支，我們檢查這個(gè)塊，并發(fā)現(xiàn)需要找到范圍"20..25" 的塊，這個(gè)塊將是葉子塊，其中會(huì)指示包含數(shù)20的行。索引的葉子節(jié)點(diǎn)實(shí)際上構(gòu)成了一個(gè)雙向鏈表。一旦發(fā)現(xiàn)要從葉子節(jié)點(diǎn)中的那里開(kāi)始，執(zhí)行值的有序掃描 (index range scan)就會(huì)很容易，我們就不必再在索引結(jié)構(gòu)中導(dǎo)航：而只需根據(jù)葉子節(jié)點(diǎn)向前或向后掃描就可以了。
B*樹(shù)的特點(diǎn)之一是：所有葉子塊都應(yīng)該在樹(shù)的同一層上，這一層稱(chēng)之為索引的高度, 它說(shuō)明所有從索引的根塊到葉子塊的遍歷都會(huì)訪(fǎng)問(wèn)同樣數(shù)目的塊。也就是說(shuō)，對(duì)于形如"SELECT INDEX_column FROM TABLE WHERE INXDEX_column =:X"的索引，要達(dá)到葉子塊來(lái)獲取第一行，不論使用的:X值是什么，都會(huì)執(zhí)行同樣數(shù)目的I/O,由此可見(jiàn)B*樹(shù)的B代表的是balanced，所謂的"Height balanced"。大多數(shù)B*樹(shù)索引的高度都是2或3，即使索引中有數(shù)百萬(wàn)行記錄也是如此，這說(shuō)明，一般而言，在索引中找到一個(gè)鍵只需要2到3次I/O , 這確實(shí)不錯(cuò)。
B*樹(shù)是一個(gè)極佳的通用索引機(jī)制，無(wú)論是大表還是小表都很適用，隨著底層表大小增長(zhǎng)，獲取數(shù)據(jù)的性能僅會(huì)稍有惡化。
比如，我們?yōu)閏ustomers表建立一個(gè)常見(jiàn)的B*樹(shù)索引:
CREATE INDEX IDX_Cus_City on customers(city)
B*樹(shù)索引有以下子類(lèi)型：
復(fù)合
關(guān)鍵詞標(biāo)簽：SQL Server,Oracle數(shù)據(jù)
相關(guān)閱讀
熱門(mén)文章
Oracle中使用alter table來(lái)增加,刪除,修改列的語(yǔ)法
oracle中使用SQL語(yǔ)句修改字段類(lèi)型-oracle修改SQL語(yǔ)句案例
誤刪Oracle數(shù)據(jù)庫(kù)實(shí)例的控制文件
為UNIX服務(wù)器設(shè)置Oracle全文檢索
人氣排行 oracle中使用SQL語(yǔ)句修改字段類(lèi)型-oracle修改SQL語(yǔ)句案例 Oracle中使用alter table來(lái)增加,刪除,修改列的語(yǔ)法 ORACLE SQL 判斷字符串是否為數(shù)字的語(yǔ)句 ORACLE和SQL語(yǔ)法區(qū)別歸納(1) oracle grant 授權(quán)語(yǔ)句 ORACLE修改IP地址后如何能夠使用 如何加速Oracle大批量數(shù)據(jù)處理 Oracle刪除表的幾種方法