快捷導(dǎo)航

Asp.Net網(wǎng)站優(yōu)化系列之?dāng)?shù)據(jù)庫(kù)的優(yōu)化措施與索引優(yōu)化方法

更新時(shí)間：2010年06月09日 20:18:20 作者：

索引的作用就類(lèi)似于書(shū)的目錄，書(shū)的目錄會(huì)按照章節(jié)的順序排列，會(huì)指想某一張的位置。這樣如果在一本數(shù)百頁(yè)的書(shū)里面查找某個(gè)章節(jié)位置的時(shí)候，我們就可以只掃描書(shū)的目錄，掃描的范圍縮小了n倍，查詢(xún)的效率自然就提高了。

另外在sql server內(nèi)存夠用的情況下索引會(huì)被放到內(nèi)存中，在內(nèi)存中查找自然又會(huì)提高效率；所以我們必須得合理利用索引。
1）對(duì)什么列建索引
數(shù)據(jù)庫(kù)默認(rèn)情況下會(huì)對(duì)主鍵建聚集索引，除了這個(gè)索引之外還需要在哪些列上建索引呢？這個(gè)問(wèn)題只能具體情況具體分析，要看需要優(yōu)化的sql語(yǔ)句（通常是查詢(xún)次數(shù)多，查詢(xún)相應(yīng)想要高的語(yǔ)句），根據(jù)什么列的條件進(jìn)行查詢(xún)。
例如：在論壇的數(shù)據(jù)庫(kù)中有一張表是帖子回復(fù)表，在論壇的應(yīng)用中用到最多的就是對(duì)指定帖子的某一頁(yè)的回復(fù)進(jìn)行查詢(xún)，查詢(xún)回復(fù)表的條件是主貼的id；這時(shí)候在主貼字段上建索引就勢(shì)在必然。

2）一定要在主鍵上建聚集索引嗎
通常情況下sql server會(huì)自動(dòng)給主鍵加上聚集索引，但也有一些例外的情況我們需要把聚集索引建在其他列上，例如我們用到了表分區(qū)，而分區(qū)的字段不是主鍵，這時(shí)候就需要將聚集索引建在分區(qū)的列上。另外如果查詢(xún)時(shí)根據(jù)主鍵查詢(xún)較少，而根據(jù)其他列的查詢(xún)較頻繁，則也可以考慮將聚集索引建在非主鍵上。單需要注意的是聚集索引的列必須是不易變的列，如果聚集索引變了一會(huì)引起聚集索引內(nèi)的記錄的搬遷，造成頁(yè)page的分離與碎片；二會(huì)引起每一個(gè)非聚集索引被修改，以便于所有相關(guān)的非聚集索引的行的索引鍵的值被糾正。這既浪費(fèi)時(shí)間和空間，也導(dǎo)致需要整理的碎片，增加了不必要的開(kāi)銷(xiāo)(每個(gè)列重組聚集鍵)。

3）復(fù)合索引（索引有兩個(gè)以上的列）要注意列順序
索引在數(shù)據(jù)庫(kù)中是以B樹(shù)的形式存儲(chǔ)的。包含A,B兩個(gè)列的索引會(huì)首先根據(jù)A列建B樹(shù)，A列的葉節(jié)點(diǎn)上才會(huì)開(kāi)始根據(jù)B列建B樹(shù)。所以包含兩個(gè)列的索引就需要根據(jù)查詢(xún)條件所在列來(lái)決定兩個(gè)列在索引中的順序。
可以用下面的sql做實(shí)驗(yàn)：

復(fù)制代碼代碼如下:

 
USE [Test] 
GO 
/****** 對(duì)象: Table [dbo].[testIndexOrder] 腳本日期: 05/27/2010 09:11:26 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[testIndexOrder]( 
[ID] [int] IDENTITY(1,1) NOT NULL, 
[FirstName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL, 
[LastName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL, 
[Desc] [nvarchar](400) COLLATE Chinese_PRC_CI_AS NULL, 
CONSTRAINT [PK_testIndexOrder] PRIMARY KEY CLUSTERED 
( 
[ID] ASC 
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] 
) ON [PRIMARY] 
GO 
/****** 對(duì)象: Index [IX_testIndexOrder] 腳本日期: 05/27/2010 09:11:51 ******/ 
CREATE NONCLUSTERED INDEX [IX_testIndexOrder] ON [dbo].[testIndexOrder] 
( 
[FirstName] ASC, 
[LastName] ASC 
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] 
GO 
declare @i INT; 
DECLARE @random varchar(36); 
set @i = 0; 
while @i < 100000 
begin 
set @random = newid(); 
INSERT INTO [testIndexOrder] 
(FirstName,LastName,[Desc]) 
VALUES( 
substring(@random,1,8),substring(@random,12,8),@random 
); 
set @i = @i + 1 
end 

set statistics time on 
select * from [testIndexOrder] where lastname = '6F-4ECA-' 
select * from [testIndexOrder] where firstname = 'CAABE009' 
set statistics time off 

4）索引的個(gè)數(shù)問(wèn)題
索引提高查詢(xún)效率是以降低更新、插入、刪除的速度為代價(jià)的。每當(dāng)索引列發(fā)生變化時(shí)都需要對(duì)索引數(shù)據(jù)進(jìn)行相應(yīng)的調(diào)整。所以一個(gè)表上不可以建太多的索引，除非你完全不在乎修改數(shù)據(jù)的效率。另外sql server本身會(huì)對(duì)索引的數(shù)量和索引的數(shù)據(jù)長(zhǎng)度有限制，具體請(qǐng)參考
5）在必要時(shí)重建索引
Sql server運(yùn)行一段時(shí)間之后就會(huì)形成一些索引碎片，這時(shí)候就需要重建索引了，有時(shí)候重建索引可以起到意想不到的效果。
查看索引碎片，重建索引，可以通過(guò)sql server管理器來(lái)重建；也可以通過(guò)下面的sql語(yǔ)句來(lái)實(shí)現(xiàn)：

復(fù)制代碼代碼如下:

 
--顯示表testIndexOrder的索引碎片情況 
DBCC SHOWCONTIG(testIndexOrder) 

--重建表的索引 
--第一個(gè)參數(shù)，可以是表名，也可以是表ID。 
--第二個(gè)參數(shù)，如果是''，表示影響該表的所有索引。 
--第三個(gè)參數(shù)，填充因子，即索引頁(yè)的數(shù)據(jù)填充程度。如果是，表示每一個(gè)索引頁(yè)都全部填滿(mǎn)，此時(shí)select效率最高，但以后要插入索引時(shí)，就得移動(dòng)后面的所有頁(yè)，效率很低。如果是，表示使用先前的填充因子值。 
DBCC DBREINDEX(testIndexOrder，''，) 

數(shù)據(jù)庫(kù)優(yōu)化是一門(mén)復(fù)雜的學(xué)問(wèn)，需要不斷的學(xué)習(xí)實(shí)踐，積累經(jīng)驗(yàn)。

您可能感興趣的文章: