Asp.Net網站最佳化系列之資料庫的最佳化措施與索引最佳化方法

另外在sql server記憶體夠用的情況下索引會被放到記憶體中，在記憶體中尋找自然又會提高效率；所以我們必須得合理利用索引。
1）對什麼列建索引
資料庫預設情況下會對主鍵建叢集索引，除了這個索引之外還需要在哪些列上建索引呢？這個問題只能具體情況具體分析，要看需要最佳化的sql語句（通常是查詢次數多，查詢相應想要高的語句），根據什麼列的條件進行查詢。
例如：在論壇的資料庫中有一張表是文章回複表，在論壇的應用中用到最多的就是對指定文章的某一頁的回複進行查詢，查詢回複表的條件是主貼的id；這時候在主貼欄位上建索引就勢在必然。

2）一定要在主鍵上建叢集索引嗎
通常情況下sql server會自動給主鍵加上叢集索引，但也有一些例外的情況我們需要把叢集索引建在其他列上，例如我們用到了表分區，而分區的欄位不是主鍵，這時候就需要將叢集索引建在分區的列上。另外如果查詢時根據主鍵查詢較少，而根據其他列的查詢較頻繁，則也可以考慮將叢集索引建在非主鍵上。單需要注意的是叢集索引的列必須是不易變的列，如果叢集索引變了一會引起叢集索引內的記錄的搬遷，造成頁page的分離與片段；二會引起每一個非聚 集索引被修改，以便於所有相關的非叢集索引的行的索引鍵的值被糾正。這既浪費時間和空間，也導致需要整理的片段，增加了不必要的開銷(每個列重組聚集鍵)。

3）複合索引（索引有兩個以上的列）要注意列順序
索引在資料庫中是以B樹的形式儲存的。包含A,B兩個列的索引會首先根據A列建B樹，A列的分葉節點上才會開始根據B列建B樹。所以包含兩個列的索引就需要根據查詢條件所在列來決定兩個列在索引中的順序。
可以用下面的sql做實驗： 複製代碼 代碼如下:USE [Test]
GO
/****** 對象: 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
/****** 對象: 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）索引的個數問題
索引提高查詢效率是以降低更新、插入、刪除的速度為代價的。每當索引列發生變化時都需要對索引資料進行相應的調整。所以一個表上不可以建太多的索引，除非你完全不在乎修改資料的效率。另外sql server本身會對索引的數量和索引的資料長度有限制，具體請參考
5）在必要時重建索引
Sql server運行一段時間之後就會形成一些索引片段，這時候就需要重建索引了，有時候重建索引可以起到意想不到的效果。
查看索引片段，重建索引，可以通過sql server管理器來重建；也可以通過下面的sql語句來實現： 複製代碼 代碼如下:--顯示表testIndexOrder的索引片段情況
DBCC SHOWCONTIG(testIndexOrder)

--重建表的索引
--第一個參數，可以是表名，也可以是表ID。
--第二個參數，如果是''，表示影響該表的所有索引。
--第三個參數，填滿因數，即索引頁的資料填充程度。如果是，表示每一個索引頁都全部填滿，此時select效率最高，但以後要插入索引時，就得移動後面的所有頁，效率很低。如果是，表示使用先前的填滿因數值。
DBCC DBREINDEX(testIndexOrder，''，)

資料庫最佳化是一門複雜的學問，需要不斷的學習實踐，積累經驗。