索引的重要性 數據庫性能優化中索引絕對是一個重量級的因素，可以說，索引使用不當，其它優化措施將毫無意義。 聚簇索引 (Clustered Index) 和非聚簇索引 (Non- Clustered Index) 最通俗的解釋是:聚簇索引的順序就是數據的物理存儲順序，而對非聚簇索引的索
索引的重要性
數據庫性能優化中索引絕對是一個重量級的因素，可以說，索引使用不當，其它優化措施將毫無意義。
聚簇索引(Clustered Index)和非聚簇索引 (Non- Clustered Index)
最通俗的解釋是:聚簇索引的順序就是數據的物理存儲順序，而對非聚簇索引的索引順序與數據物理排列順序無關。舉例來說，你翻到新華字典的漢字“爬”那一頁就是P開頭的部分，這就是物理存儲順序（聚簇索引）；而不用你到目錄，找到漢字“爬”所在的頁碼，然后根據頁碼找到這個字（非聚簇索引）。
下表給出了何時使用聚簇索引與非聚簇索引:
|
動作 |
使用聚簇索引 |
使用非聚簇索引 |
|
列經常被分組排序 |
應 |
應 |
|
返回某范圍內的數據 |
應 |
不應 |
|
一個或極少不同值 |
不應 |
不應 |
|
小數目的不同值 |
應 |
不應 |
|
大數目的不同值 |
不應 |
應 |
|
頻繁更新的列 |
不應 |
應 |
|
外鍵列 |
應 |
應 |
|
主鍵列 |
應 |
應 |
|
頻繁修改索引列 |
不應 |
應 |
聚簇索引的唯一性
正式聚簇索引的順序就是數據的物理存儲順序，所以一個表最多只能有一個聚簇索引，因為物理存儲只能有一個順序。正因為一個表最多只能有一個聚簇索引，所以它顯得更為珍貴，一個表設置什么為聚簇索引對性能很關鍵。
初學者最大的誤區：把主鍵自動設為聚簇索引
因為這是SQLServer的默認主鍵行為，你設置了主鍵，它就把主鍵設為聚簇索引，而一個表最多只能有一個聚簇索引，所以很多人就把其他索引設置為非聚簇索引。這個是最大的誤區。甚至有的主鍵又是無意義的自動增量字段，那樣的話Clustered index對效率的幫助，完全被浪費了。
剛才說到了，聚簇索引性能最好而且具有唯一性，所以非常珍貴，必須慎重設置。一般要根據這個表最常用的SQL查詢方式來進行選擇，某個字段作為聚簇索引，或組合聚簇索引，這個要看實際情況。
事實上，建表的時候，先需要設置主鍵，然后添加我們想要的聚簇索引，最后設置主鍵，SQLServer就會自動把主鍵設置為非聚簇索引（會自動根據情況選擇）。如果你已經設置了主鍵為聚簇索引，必須先刪除主鍵，然后添加我們想要的聚簇索引，最后恢復設置主鍵即可。
記住我們的最終目的就是在相同結果集情況下，盡可能減少邏輯IO。
我們先從一個實際使用的簡單例子開始。
一個簡單的表：
|
CREATE TABLE [dbo].[Table1]( [ID] [int] IDENTITY(1,1) NOT NULL, [Data1] [int] NOT NULL DEFAULT ((0)), [Data2] [int] NOT NULL DEFAULT ((0)), [Data3] [int] NOT NULL DEFAULT ((0)), [Name1] [nvarchar](50) NOT NULL DEFAULT (''), [Name2] [nvarchar](50) NOT NULL DEFAULT (''), [Name3] [nvarchar](50) DEFAULT (''), [DTAt] [datetime] NOT NULL DEFAULT (getdate()) |
來點測試數據（10w條）：
|
declare @i int set @i = 1 while @i < 100000 begin insert into Table1 ([Data1] ,[Data2] ,[Data3] ,[Name1],[Name2] ,[Name3]) values(@i, 2* @i,3*@i, CAST(@i AS NVARCHAR(50)), CAST(2*@i AS NVARCHAR(50)), CAST(3*@i AS NVARCHAR(50))) set @i = @i + 1 end update table1 set dtat= DateAdd (s, data1, dtat) |
打開查詢分析器的IO統計和時間統計：
|
SET STATISTICS IO ON; SET STATISTICS TIME ON; |
顯示實際的“執行計劃”：
我們最常用的SQL查詢是這樣的：
|
SELECT * FROM Table1 WHERE Data1 = 2 ORDER BY DTAt DESC; |
先在Table1設主鍵ID，系統自動為該主鍵建立了聚簇索引。
然后執行該語句，結果是：
|
Table 'Table1'. Scan count 1, logical reads 911, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 7 ms. |
然后我們在Data1和DTat字段分別建立非聚簇索引：
|
CREATE NONCLUSTERED INDEX [N_Data1] ON [dbo].[Table1] ( [Data1] ASC )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] CREATE NONCLUSTERED INDEX [N_DTat] ON [dbo].[Table1] ( [DTAt] ASC )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] |
再次執行該語句，結果是：
|
Table 'Table1'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 39 ms. |
可以看到設立了索引反而沒有任何性能的提升而且消耗的時間更多了，繼續調整。
然后我們刪除所有非聚簇索引，并刪除主鍵，這樣所有索引都刪除了。建立組合索引Data1和DTAt，最后加上主鍵：
|
CREATE CLUSTERED INDEX [C_Data1_DTat] ON [dbo].[Table1] ( [Data1] ASC, [DTAt] ASC )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] |
再次執行語句：
|
Table 'Table1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. |
可以看到只有聚簇索引seek了，消除了index scan和nested loop，而且執行時間也只有1ms，達到了最初優化的目的。
組合索引小結
小結以上的調優實踐，要注意聚簇索引的選擇。首先我們要找到我們最多用到的SQL查詢，像本例就是那句類似的組合條件查詢的情況，這種情況最好使用組合聚簇索引，而且最多用到的字段要放在組合聚簇索引的前面，否則的話就索引就不會有好的效果，看下例：
查詢條件落在組合索引的第二個字段上，引起了index scan，效果很不好，執行時間是：
|
Table 'Table1'. Scan count 1, logical reads 238, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 22 ms. |
而如果僅查詢條件是第一個字段也沒有問題，因為組合索引最左前綴原則，實踐如下：
|
Table 'Table1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. |
從中可以看出，最多用到的字段要放在組合聚簇索引的前面。
Index seek 為什么比 Index scan好？
索引掃描也就是遍歷B樹，而seek是B樹查找直接定位。
Index scan多半是出現在索引列在表達式中。數據庫引擎無法直接確定你要的列的值，所以只能掃描整個整個索引進行計算。index seek就要好很多.數據庫引擎只需要掃描幾個分支節點就可以定位到你要的記錄。回過來,如果聚集索引的葉子節點就是記錄，那么Clustered Index Scan就基本等同于full table scan。
一些優化原則
3、多表操作在被實際執行前，查詢優化器會根據連接條件，列出幾組可能的連接方案并從中找出系統開銷最小的最佳方案。連接條件要充份考慮帶有索引的表、行數多的表；內外表的選擇可由公式：外層表中的匹配行數*內層表中每一次查找的次數確定，乘積最小為最佳方案。
4、任何對列的操作都將導致表掃描，它包括數據庫函數、計算表達式等等，查詢時要盡可能將操作移至等號右邊。
5、IN、OR子句常會使用工作表，使索引失效。如果不產生大量重復值，可以考慮把子句拆開。拆開的子句中應該包含索引。
Sql的優化原則2：
1、只要能滿足你的需求，應盡可能使用更小的數據類型：例如使用MEDIUMINT代替INT
2、盡量把所有的列設置為NOT NULL，如果你要保存NULL，手動去設置它，而不是把它設為默認值。
3、盡量少用VARCHAR、TEXT、BLOB類型
4、如果你的數據只有你所知的少量的幾個。最好使用ENUM類型
有關Join的一些原則
SQL Server 有三種類型的JOIN操作：
如果Join的輸入很小，例如小于10行，然后其他的Join輸入很大并且索引在其列上，則Nested loops joins是最快的。（原因參考Understanding Nested Loops Joins）
如果兩個Join輸入都不小，但在索引列上排序（例如是在掃描排序的索引后獲得的 scanning sorted indexes），則Merge joins是最快的。（原因參考Understanding Merge Joins）
Hash joins可以有效的處理大量的、沒有排序的、沒有索引的輸入。尤其對復雜查詢的中間結果處理很有效。（更多參考Understanding Hash Joins）
如何分析SQL語句
微軟MSDN給出了答案：http://msdn.microsoft.com/en-us/library/ms191227.aspx找出數據庫中性能最差的SQL
優化哪個表？從何入手？首先需要定位性能瓶頸，找到運行最慢的SQL。可以采用如下步驟：
1. 運行 dbcc freeProcCache 清除緩存
2. 運行你的程序，或者你的SQL或存儲過程，操作數據庫
3. 完了以后運行以下SQL找到運行最慢的SQL：
SELECT DB_ID(DB.dbid) '數據庫名'
, OBJECT_ID(db.objectid) '對象'
, QS.creation_time '編譯計劃的時間'
, QS.last_execution_time '上次執行計劃的時間'
, QS.execution_count '執行的次數'
, QS.total_elapsed_time / 1000 '占用的總時間（秒）'
, QS.total_physical_reads '物理讀取總次數'
, QS.total_worker_time / 1000 'CPU 時間總量（秒）'
, QS.total_logical_writes '邏輯寫入總次數'
, QS.total_logical_reads N'邏輯讀取總次數'
, QS.total_elapsed_time / 1000 N'總花費時間（秒）'
, SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset
END - QS.statement_start_offset ) / 2 ) + 1) AS '執行語句'
FROM sys.dm_exec_query_stats AS QS CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) AS ST INNER JOIN
( SELECT *
FROM sys.dm_exec_cached_plans cp CROSS APPLY
sys.dm_exec_query_plan(cp.plan_handle)
) DB
ON QS.plan_handle = DB.plan_handle
where SUBSTRING(st.text, ( qs.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2 ) + 1) not like '%fetch%'
ORDER BY QS.total_elapsed_time / 1000 DESC
使用SQLServer Profiler找出數據庫中性能最差的SQL
首先打開SQLServer Profiler:
然后點擊工具欄“New Trace”，使用默認的模板，點擊RUN。
也許會有報錯："only TrueType fonts are supported. There id not a TrueType font"。不用怕，點擊Tools菜單->Options，重新選擇一個字體例如Vendana 即可。（這個是微軟的一個bug）
運行起來以后，SQLServer Profiler會監控數據庫的活動，所以最好在你需要監控的數據庫上多做些操作。等覺得差不多了，點擊停止。然后保存trace結果到文件或者table。
這里保存到Table：在菜單“File”-“Save as ”-“Trace table”，例如輸入一個master數據庫的新的table名：profileTrace，保存即可。
找到最耗時的SQL：
|
use master select * from profiletrace order by duration desc; |
找到了性能瓶頸，接下來就可以有針對性的一個個進行調優了。
對使用SQLServer Profiler的更多信息可以參考：
http://www.codeproject.com/KB/database/DiagnoseProblemsSQLServer.aspx
使用SQLServer Database Engine Tuning Advisor數據庫引擎優化顧問
使用上述的SQLServer Profiler得到了trace還有一個好處就是可以用到這個優化顧問。用它可以偷點懶，得到SQLServer給您的優化顧問，例如這個表需要加個索引什么的…
首先打開數據庫引擎優化顧問：
然后打開剛才profiler的結果（我們存到了master數據庫的profileTrace表）：
點擊“start analysis”，運行完成后查看優化建議（圖中最后是建議建立的索引，性能提升72%）
這個方法可以偷點懶，得到SQLServer給您的優化顧問。
繼續閱讀：
或許您對以下文章有興趣：
聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
Copyright ? 2019-2025 m.alabama37th.com 版權所有
違法及侵權請聯系：TEL:177 7030 7066 E-MAIL:11247931@qq.com 本站由北京市萬商天勤律師事務所王興未律師提供法律服務
