點(diǎn)擊下載
本文文檔

當(dāng)前位置：首頁 - 科技 - 知識百科 - 正文

SQLServer索引調(diào)優(yōu)實(shí)踐

來源：懂視網(wǎng) 責(zé)編：小采時(shí)間：2020-11-09 15:41:06

SQLServer索引調(diào)優(yōu)實(shí)踐

SQLServer索引調(diào)優(yōu)實(shí)踐:索引的重要性數(shù)據(jù)庫性能優(yōu)化中索引絕對是一個(gè)重量級的因素，可以說，索引使用不當(dāng)，其它優(yōu)化措施將毫無意義。聚簇索引 (Clustered Index) 和非聚簇索引 (Non- Clustered Index) 最通俗的解釋是:聚簇索引的順序就是數(shù)據(jù)的物理存儲順序，而對非聚簇索引的索

推薦度：

點(diǎn)擊下載本文 文檔為doc格式

導(dǎo)讀SQLServer索引調(diào)優(yōu)實(shí)踐:索引的重要性數(shù)據(jù)庫性能優(yōu)化中索引絕對是一個(gè)重量級的因素，可以說，索引使用不當(dāng)，其它優(yōu)化措施將毫無意義。聚簇索引 (Clustered Index) 和非聚簇索引 (Non- Clustered Index) 最通俗的解釋是:聚簇索引的順序就是數(shù)據(jù)的物理存儲順序，而對非聚簇索引的索

索引的重要性數(shù)據(jù)庫性能優(yōu)化中索引絕對是一個(gè)重量級的因素，可以說，索引使用不當(dāng)，其它優(yōu)化措施將毫無意義。聚簇索引 (Clustered Index) 和非聚簇索引 (Non- Clustered Index) 最通俗的解釋是:聚簇索引的順序就是數(shù)據(jù)的物理存儲順序，而對非聚簇索引的索

索引的重要性

數(shù)據(jù)庫性能優(yōu)化中索引絕對是一個(gè)重量級的因素，可以說，索引使用不當(dāng)，其它優(yōu)化措施將毫無意義。

聚簇索引(Clustered Index)和非聚簇索引 (Non- Clustered Index)

最通俗的解釋是:聚簇索引的順序就是數(shù)據(jù)的物理存儲順序，而對非聚簇索引的索引順序與數(shù)據(jù)物理排列順序無關(guān)。舉例來說，你翻到新華字典的漢字“爬”那一頁就是P開頭的部分，這就是物理存儲順序（聚簇索引）；而不用你到目錄，找到漢字“爬”所在的頁碼，然后根據(jù)頁碼找到這個(gè)字（非聚簇索引）。

下表給出了何時(shí)使用聚簇索引與非聚簇索引:

動作	使用聚簇索引	使用非聚簇索引
列經(jīng)常被分組排序	應(yīng)	應(yīng)
返回某范圍內(nèi)的數(shù)據(jù)	應(yīng)	不應(yīng)
一個(gè)或極少不同值	不應(yīng)	不應(yīng)
小數(shù)目的不同值	應(yīng)	不應(yīng)
大數(shù)目的不同值	不應(yīng)	應(yīng)
頻繁更新的列	不應(yīng)	應(yīng)
外鍵列	應(yīng)	應(yīng)
主鍵列	應(yīng)	應(yīng)
頻繁修改索引列	不應(yīng)	應(yīng)

聚簇索引的唯一性

正式聚簇索引的順序就是數(shù)據(jù)的物理存儲順序，所以一個(gè)表最多只能有一個(gè)聚簇索引，因?yàn)槲锢泶鎯χ荒苡幸粋€(gè)順序。正因?yàn)橐粋€(gè)表最多只能有一個(gè)聚簇索引，所以它顯得更為珍貴，一個(gè)表設(shè)置什么為聚簇索引對性能很關(guān)鍵。

初學(xué)者最大的誤區(qū)：把主鍵自動設(shè)為聚簇索引

因?yàn)檫@是SQLServer的默認(rèn)主鍵行為，你設(shè)置了主鍵，它就把主鍵設(shè)為聚簇索引，而一個(gè)表最多只能有一個(gè)聚簇索引，所以很多人就把其他索引設(shè)置為非聚簇索引。這個(gè)是最大的誤區(qū)。甚至有的主鍵又是無意義的自動增量字段，那樣的話Clustered index對效率的幫助，完全被浪費(fèi)了。

剛才說到了，聚簇索引性能最好而且具有唯一性，所以非常珍貴，必須慎重設(shè)置。一般要根據(jù)這個(gè)表最常用的SQL查詢方式來進(jìn)行選擇，某個(gè)字段作為聚簇索引，或組合聚簇索引，這個(gè)要看實(shí)際情況。

事實(shí)上，建表的時(shí)候，先需要設(shè)置主鍵，然后添加我們想要的聚簇索引，最后設(shè)置主鍵，SQLServer就會自動把主鍵設(shè)置為非聚簇索引（會自動根據(jù)情況選擇）。如果你已經(jīng)設(shè)置了主鍵為聚簇索引，必須先刪除主鍵，然后添加我們想要的聚簇索引，最后恢復(fù)設(shè)置主鍵即可。

記住我們的最終目的就是在相同結(jié)果集情況下，盡可能減少邏輯IO。

我們先從一個(gè)實(shí)際使用的簡單例子開始。

一個(gè)簡單的表：

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())

來點(diǎn)測試數(shù)據(jù)（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統(tǒng)計(jì)和時(shí)間統(tǒng)計(jì)：

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

顯示實(shí)際的“執(zhí)行計(jì)劃”：

我們最常用的SQL查詢是這樣的：

SELECT * FROM Table1 WHERE Data1 = 2 ORDER BY DTAt DESC;

先在Table1設(shè)主鍵ID，系統(tǒng)自動為該主鍵建立了聚簇索引。

然后執(zhí)行該語句，結(jié)果是：

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]

再次執(zhí)行該語句，結(jié)果是：

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.

可以看到設(shè)立了索引反而沒有任何性能的提升而且消耗的時(shí)間更多了，繼續(xù)調(diào)整。

然后我們刪除所有非聚簇索引，并刪除主鍵，這樣所有索引都刪除了。建立組合索引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]

再次執(zhí)行語句：

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，而且執(zhí)行時(shí)間也只有1ms，達(dá)到了最初優(yōu)化的目的。

組合索引小結(jié)

小結(jié)以上的調(diào)優(yōu)實(shí)踐，要注意聚簇索引的選擇。首先我們要找到我們最多用到的SQL查詢，像本例就是那句類似的組合條件查詢的情況，這種情況最好使用組合聚簇索引，而且最多用到的字段要放在組合聚簇索引的前面，否則的話就索引就不會有好的效果，看下例：

查詢條件落在組合索引的第二個(gè)字段上，引起了index scan，效果很不好，執(zhí)行時(shí)間是：

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.

而如果僅查詢條件是第一個(gè)字段也沒有問題，因?yàn)榻M合索引最左前綴原則，實(shí)踐如下：

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多半是出現(xiàn)在索引列在表達(dá)式中。數(shù)據(jù)庫引擎無法直接確定你要的列的值，所以只能掃描整個(gè)整個(gè)索引進(jìn)行計(jì)算。index seek就要好很多.數(shù)據(jù)庫引擎只需要掃描幾個(gè)分支節(jié)點(diǎn)就可以定位到你要的記錄。回過來,如果聚集索引的葉子節(jié)點(diǎn)就是記錄，那么Clustered Index Scan就基本等同于full table scan。

一些優(yōu)化原則

1、缺省情況下建立的索引是非聚簇索引，但有時(shí)它并不是最佳的。在非群集索引下，數(shù)據(jù)在物理上隨機(jī)存放在數(shù)據(jù)頁上。合理的索引設(shè)計(jì)要建立在對各種查詢的分析和預(yù)測上。一般來說：
a.有大量重復(fù)值、且經(jīng)常有范圍查詢（ > ,< ，> =,< =）和order by、group by發(fā)生的列，可考
慮建立群集索引；
b.經(jīng)常同時(shí)存取多列，且每列都含有重復(fù)值可考慮建立組合索引；
c.組合索引要盡量使關(guān)鍵查詢形成索引覆蓋，其前導(dǎo)列一定是使用最頻繁的列。索引雖有助于提高性能但不是索引越多越好，恰好相反過多的索引會導(dǎo)致系統(tǒng)低效。用戶在表中每加進(jìn)一個(gè)索引，維護(hù)索引集合就要做相應(yīng)的更新工作。
2、ORDER BY和GROPU BY使用ORDER BY和GROUP BY短語，任何一種索引都有助于SELECT的性能提高。

3、多表操作在被實(shí)際執(zhí)行前，查詢優(yōu)化器會根據(jù)連接條件，列出幾組可能的連接方案并從中找出系統(tǒng)開銷最小的最佳方案。連接條件要充份考慮帶有索引的表、行數(shù)多的表；內(nèi)外表的選擇可由公式：外層表中的匹配行數(shù)*內(nèi)層表中每一次查找的次數(shù)確定，乘積最小為最佳方案。
4、任何對列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫函數(shù)、計(jì)算表達(dá)式等等，查詢時(shí)要盡可能將操作移至等號右邊。
5、IN、OR子句常會使用工作表，使索引失效。如果不產(chǎn)生大量重復(fù)值，可以考慮把子句拆開。拆開的子句中應(yīng)該包含索引。

Sql的優(yōu)化原則2：
1、只要能滿足你的需求，應(yīng)盡可能使用更小的數(shù)據(jù)類型：例如使用MEDIUMINT代替INT
2、盡量把所有的列設(shè)置為NOT NULL，如果你要保存NULL，手動去設(shè)置它，而不是把它設(shè)為默認(rèn)值。
3、盡量少用VARCHAR、TEXT、BLOB類型
4、如果你的數(shù)據(jù)只有你所知的少量的幾個(gè)。最好使用ENUM類型

有關(guān)Join的一些原則

SQL Server 有三種類型的JOIN操作：

Nested loops joins

Merge joins

Hash joins

如果Join的輸入很小，例如小于10行，然后其他的Join輸入很大并且索引在其列上，則Nested loops joins是最快的。（原因參考Understanding Nested Loops Joins）

如果兩個(gè)Join輸入都不小，但在索引列上排序（例如是在掃描排序的索引后獲得的 scanning sorted indexes），則Merge joins是最快的。（原因參考Understanding Merge Joins）

Hash joins可以有效的處理大量的、沒有排序的、沒有索引的輸入。尤其對復(fù)雜查詢的中間結(jié)果處理很有效。（更多參考Understanding Hash Joins）

如何分析SQL語句

微軟MSDN給出了答案：http://msdn.microsoft.com/en-us/library/ms191227.aspx

找出數(shù)據(jù)庫中性能最差的SQL

優(yōu)化哪個(gè)表？從何入手？首先需要定位性能瓶頸，找到運(yùn)行最慢的SQL?？梢圆捎萌缦虏襟E：

1. 運(yùn)行 dbcc freeProcCache 清除緩存

2. 運(yùn)行你的程序，或者你的SQL或存儲過程，操作數(shù)據(jù)庫

3. 完了以后運(yùn)行以下SQL找到運(yùn)行最慢的SQL：

SELECT DB_ID(DB.dbid) '數(shù)據(jù)庫名'
, OBJECT_ID(db.objectid) '對象'
, QS.creation_time '編譯計(jì)劃的時(shí)間'
, QS.last_execution_time '上次執(zhí)行計(jì)劃的時(shí)間'
, QS.execution_count '執(zhí)行的次數(shù)'
, QS.total_elapsed_time / 1000 '占用的總時(shí)間（秒）'
, QS.total_physical_reads '物理讀取總次數(shù)'
, QS.total_worker_time / 1000 'CPU 時(shí)間總量（秒）'
, QS.total_logical_writes '邏輯寫入總次數(shù)'
, QS.total_logical_reads N'邏輯讀取總次數(shù)'
, QS.total_elapsed_time / 1000 N'總花費(fèi)時(shí)間（秒）'
, 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 '執(zhí)行語句'
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找出數(shù)據(jù)庫中性能最差的SQL

首先打開SQLServer Profiler:

然后點(diǎn)擊工具欄“New Trace”，使用默認(rèn)的模板，點(diǎn)擊RUN。

也許會有報(bào)錯(cuò)："only TrueType fonts are supported. There id not a TrueType font"。不用怕，點(diǎn)擊Tools菜單->Options，重新選擇一個(gè)字體例如Vendana 即可。（這個(gè)是微軟的一個(gè)bug）

運(yùn)行起來以后，SQLServer Profiler會監(jiān)控?cái)?shù)據(jù)庫的活動，所以最好在你需要監(jiān)控的數(shù)據(jù)庫上多做些操作。等覺得差不多了，點(diǎn)擊停止。然后保存trace結(jié)果到文件或者table。

這里保存到Table：在菜單“File”-“Save as ”-“Trace table”，例如輸入一個(gè)master數(shù)據(jù)庫的新的table名：profileTrace，保存即可。

找到最耗時(shí)的SQL：

use master

select * from profiletrace order by duration desc;

找到了性能瓶頸，接下來就可以有針對性的一個(gè)個(gè)進(jìn)行調(diào)優(yōu)了。

對使用SQLServer Profiler的更多信息可以參考：

http://www.codeproject.com/KB/database/DiagnoseProblemsSQLServer.aspx

使用SQLServer Database Engine Tuning Advisor數(shù)據(jù)庫引擎優(yōu)化顧問

使用上述的SQLServer Profiler得到了trace還有一個(gè)好處就是可以用到這個(gè)優(yōu)化顧問。用它可以偷點(diǎn)懶，得到SQLServer給您的優(yōu)化顧問，例如這個(gè)表需要加個(gè)索引什么的…

首先打開數(shù)據(jù)庫引擎優(yōu)化顧問：