SQLServer索引调优实践.docx

SQLServer索引调优实践.docx

《SQLServer索引调优实践.docx》由会员分享，可在线阅读，更多相关《SQLServer索引调优实践.docx（13页珍藏版）》请在冰豆网上搜索。

SQLServer索引调优实践

SQＬSerｖｅr索引调优实践　

索引的重要性

数据库性能优化中索引绝对是一个重量级的因素，可以说,索引使用不当,其它优化措施将毫无意义。

聚簇索引（ＣlusｔerｅdIndeｘ）和非聚簇索引　（Non-ＣlｕsteredIndeｘ）

最通俗的解释是：

聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引的索引顺序与数据物理排列顺序无关。

举例来说，你翻到新华字典的汉字“爬”那一页就是P开头的部分，这就是物理存储顺序（聚簇索引）；而不用你到目录，找到汉字“爬”所在的页码,然后根据页码找到这个字（非聚簇索引）。

下表给出了何时使用聚簇索引与非聚簇索引:

动作

使用聚簇索引

使用非聚簇索引

列经常被分组排序

应

应

返回某范围内的数据

应

不应

一个或极少不同值

不应

不应

小数目的不同值

应

不应

大数目的不同值

不应

应

频繁更新的列

不应

应

外键列

应

应

主键列

应

应

频繁修改索引列

不应

应

聚簇索引的唯一性

正式聚簇索引的顺序就是数据的物理存储顺序,所以一个表最多只能有一个聚簇索引，因为物理存储只能有一个顺序。

正因为一个表最多只能有一个聚簇索引，所以它显得更为珍贵，一个表设置什么为聚簇索引对性能很关键。

初学者最大的误区：

把主键自动设为聚簇索引

因为这是ＳQLServｅr的默认主键行为,你设置了主键，它就把主键设为聚簇索引，而一个表最多只能有一个聚簇索引,所以很多人就把其他索引设置为非聚簇索引。

这个是最大的误区。

甚至有的主键又是无意义的自动增量字段,那样的话Ｃlustered　indｅx对效率的帮助，完全被浪费了。

刚才说到了,聚簇索引性能最好而且具有唯一性，所以非常珍贵，必须慎重设置。

一般要根据这个表最常用的ＳQL查询方式来进行选择，某个字段作为聚簇索引,或组合聚簇索引，这个要看实际情况。

事实上，建表的时候,先需要设置主键，然后添加我们想要的聚簇索引,最后设置主键，ＳＱＬServｅｒ就会自动把主键设置为非聚簇索引（会自动根据情况选择）。

如果你已经设置了主键为聚簇索引,必须先删除主键，然后添加我们想要的聚簇索引,最后恢复设置主键即可。

记住我们的最终目的就是在相同结果集情况下,尽可能减少逻辑IＯ。

我们先从一个实际使用的简单例子开始。

一个简单的表：

CREATＥTABＬＥ[ｄbｏ］.[Ｔａbｌｅ１]（

 [ID][inｔ]IDENTITY（1,1）　ＮOTNULL，

 　[Data1］［ｉnt]NOTNＵLLDEFAULT（（０））,

 　[Daｔa2][int]NOTＮＵＬLＤEFAULT（（０））,

 [Ｄａta3］　[ｉｎｔ］ＮOＴＮULＬDEＦＡULT　（（0））,

 ［Ｎame1][nvａｒcｈar］（50）　NOTNULLDEＦAＵLT（''）,

 ［Ｎame2][nvarchar]（50）NＯＴ　NULＬDEFAＵLＴ（＇'）,

 　[Ｎame3][nvarｃhaｒ]（50）DEFAULT　（''），

 [ＤTAt]［datetｉme]　NOT　ＮＵLLDEＦAULＴ　（ｇetｄate（））

来点测试数据（1０w条）:

decｌａｒe@i　inｔ

set@ｉ　＝1

wｈｉｌｅ＠i<1０00０0　

beｇiｎ

inseｒtintｏTaｂｌｅ１　（[Data1］,［Ｄａｔa２],[Data3］,[Name１],[Nａme2],[Name3]）

valuｅｓ（@ｉ，2*＠i,3*@i,ＣＡＳT（@iＡS　ＮＶARCＨＡR（50））,　CAST（2＊@i　ASＮVARCHAＲ（５0））,ＣAST（3＊＠ｉASＮVＡRＣHAＲ（５０）））

set@i　=　@i+1

enｄ

ｕpdate　tablｅ1setｄtat＝DatｅAdd（s，dａｔａ1,dtat）

打开查询分析器的IO统计和时间统计：

SEＴＳTＡＴISTICSIＯON;

SETSＴAＴＩSTICSTIＭＥON;

显示实际的“执行计划”：

我们最常用的SQL查询是这样的：

SELECＴ＊FROＭTａblｅ1WHEREDａta1=　2ＯRDER　ＢYDTAt　DEＳＣ;

先在Tａble1设主键ID，系统自动为该主键建立了聚簇索引。

然后执行该语句,结果是:

Taｂle'Table1＇．Scancount　１,　logｉcalｒeａds91１,physiｃal　reads0,reaｄ－aheadreadｓ0，　loblogｉcalreａds　0,lobｐｈysiｃaｌ　rｅads0,lobread-aｈeａdｒeａdｓ0.

ＳＱLＳervｅr　ExecuｔionTimes:

CＰUtｉｍe=１６ｍs,elapsedtime=7　ｍs.

然后我们在Data1和DTaｔ字段分别建立非聚簇索引:

CREＡTENＯNCLUSＴEREDINDEX[N_Datａ1]ON　[dbo]．[Tａｂle１]　

（

[Data１］　ＡSC

）WＩTH（SＯＲT_IN_TEＭPＤB=OFＦ，　DROP＿EXISTIＮG=OＦF,IGNＯＲＥ_DUP_KEY＝OFF,ONLINE=ＯFＦ）ＯN[PＲIＭＡRY]

CREATENONCLUSＴＥREＤＩNDEX[N＿DＴat］ＯN[ｄbo].［Ｔable１］　

（

［ＤTＡt]ＡSC

）WITH（ＳORT_IN_TEMPDB=OFF,DRＯＰ＿ＥXISTING　=ＯFF,IGNORE_DＵP＿KEY=OFＦ,　ONLＩNE　＝　OＦF）ON[PRIMAＲY］

再次执行该语句,结果是：

Taｂle　'Tabｌe1'．Sｃanｃoｕnｔ1，loｇｉｃalreads5,ｐｈysical　reａｄs0,　rｅaｄ-ａheａｄreads0,loblogicalrｅａｄｓ0，　loｂphysiｃaｌreａds０,　lｏbread-ahｅadrｅａｄs0.

ＳQL　SｅrｖeｒＥxecｕtiｏnＴimes:

ＣＰＵtimｅ＝０ms，　elapｓeｄｔime=　39ms.

可以看到设立了索引反而没有任何性能的提升而且消耗的时间更多了，继续调整。

然后我们删除所有非聚簇索引,并删除主键,这样所有索引都删除了。

建立组合索引Datａ1和DTAt,最后加上主键：

ＣRＥAＴECLUSＴEREDINDEX[Ｃ_Daｔa1_DTａt]　ON[dbo].[Table1］

（

[Dａta1]　ASＣ,

[DTＡt]AＳC

）ＷIＴＨ（SOＲT_IN_TEMPDＢ　=ＯＦF,DROP_EXISＴＩNG=OFF,IＧＮORE_DUＰ_ＫEY＝OFF,ONLINE　=OFF）　ON　[PRIMARY］

再次执行语句:

Tablｅ＇Tabｌe1'.　Scan　count　1,logｉcaｌrｅadｓ3,physｉcalreads0,read-aｈeadreaｄs0,lobｌｏgiｃalreadｓ0,　loｂｐｈysicａl　reaｄs0,lob　rｅad-ahｅaｄrｅaｄs０．

SQLSeｒｖerExｅcｕtiｏｎTimｅｓ:

CPU　ｔime=０ｍｓ,elapｓedtime　=　1ｍｓ.

可以看到只有聚簇索引sｅek了，消除了indeｘ　scaｎ和ｎestｅｄ　ｌoop，而且执行时间也只有1ms，达到了最初优化的目的。

组合索引小结

小结以上的调优实践,要注意聚簇索引的选择。

首先我们要找到我们最多用到的SQL查询，像本例就是那句类似的组合条件查询的情况,这种情况最好使用组合聚簇索引,而且最多用到的字段要放在组合聚簇索引的前面，否则的话就索引就不会有好的效果，看下例：

查询条件落在组合索引的第二个字段上,引起了index　scan，效果很不好，执行时间是：

Ｔable　'Taｂlｅ1'.Scaｎcoｕnｔ1，logiｃalreadｓ238,physical　reads　０，read－ahｅaｄｒeads　０,lobｌogｉcalreaｄs0,ｌobphysiｃａlreａds0，lobrｅａd-aheadｒeａds　０.

SQLＳerverEｘecutionＴｉmes:

CPＵtiｍｅ　＝16　mｓ，elａpseｄtｉmｅ　=22　mｓ.

而如果仅查询条件是第一个字段也没有问题，因为组合索引最左前缀原则,实践如下:

Table'Tablｅ1'．Scａn　ｃount1,ｌogｉcａlreａdｓ　３,pｈｙsicaｌｒｅａds　０,reａd-ａｈeadｒeaｄｓ0,loblogiｃalreａds0,　ｌobphysicａｌreａds0,loｂread-aheaｄreads0.

ＳQLServｅrExeｃuｔiｏnＴｉmes:

ＣPＵ　ｔiｍe＝0　ｍs，ｅlａpseｄtｉme＝1ｍs.

从中可以看出，最多用到的字段要放在组合聚簇索引的前面。

Indeｘ　seek为什么比Iｎdｅxscan好？

索引扫描也就是遍历B树,而seek是B树查找直接定位。

Iｎdexｓcａn多半是出现在索引列在表达式中。

数据库引擎无法直接确定你要的列的值,所以只能扫描整个整个索引进行计算。

indｅxseek就要好很多.数据库引擎只需要扫描几个分支节点就可以定位到你要的记录。

回过来,如果聚集索引的叶子节点就是记录,那么ClusteredIｎdexScan就基本等同于ｆulｌtａblｅscan。

一些优化原则

∙1、缺省情况下建立的索引是非聚簇索引，但有时它并不是最佳的。

在非群集索引下，数据在物理上随机存放在数据页上。

合理的索引设计要建立在对各种查询的分析和预测上。

一般来说：

ﻫａ.有大量重复值、且经常有范围查询（>,<，>=,<=）和orderby、grｏupby发生的列，可考ﻫ虑建立群集索引;　ﻫb.经常同时存取多列,且每列都含有重复值可考虑建立组合索引;ﻫc.组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。

索引虽有助于提高性能但不是索引越多越好,恰好相反过多的索引会导致系统低效。

用户在表中每加进一个索引,维护索引集合就要做相应的更新工作。

2、ORＤEＲ　BY和GＲOＰＵＢY使用ORDＥR　BY和GＲＯＵＰ　BY短语,任何一种索引都有助于SＥLECＴ的性能提高。

3、多表操作在被实际执行前，查询优化器会根据连接条件,列出几组可能的连接方案并从中找出系统开销最小的最佳方案。

连接条件要充份考虑带有索引的表、行数多的表；内外表的选择可由公式:

外层表中的匹配行数*内层表中每一次查找的次数确定，乘积最小为最佳方案。

4、任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

ﻫ5、IＮ、OR子句常会使用工作表，使索引失效。

如果不产生大量重复值,可以考虑把子句拆开。

拆开的子句中应该包含索引。

Sql的优化原则2:

1、只要能满足你的需求，应尽可能使用更小的数据类型：

例如使用MEＤIUMＩNT代替ＩNＴ　

2、尽量把所有的列设置为ＮOT　NＵLL,如果你要保存NULL，手动去设置它,而不是把它设为默认值。

ﻫ3、尽量少用VＡRCＨＡR、TＥXＴ、ＢLOB类型ﻫ4、如果你的数据只有你所知的少量的几个。

最好使用ＥＮＵM类型 

有关Jｏｉn的一些原则

ＳQLＳeｒvｅｒ有三种类型的JOＩN操作：

∙Ｎesｔedloｏpsjｏins

∙Merge　joins

∙Hashjｏinｓ

∙ 　

如果Join的输入很小,例如小于10行，然后其他的Ｊoiｎ输入很大并且索引在其列上,则Nesｔｅdｌｏoｐsjｏins是最快的。

（原因参考Undｅｒstandｉｎg　NesｔedLoops　Ｊoins）

如果两个Join输入都不小，但在索引列上排序（例如是在扫描排序的索引后获得的scannｉng　ｓｏrtｅdｉndexｅs）,则Merｇｅｊｏｉnｓ是最快的。

（原因参考Underｓtaｎdｉｎg　MerｇｅＪｏinｓ）

Ｈａsｈ　joins可以有效的处理大量的、没有排序的、没有索引的输入。

尤其对复杂查询的中间结果处理很有效。

（更多参考UnｄerstａnｄinｇHａshJoins）

如何分析SQL语句

微软MSDN给出了答案:

ｈｔtp：

／/msdn．microsｏ

找出数据库中性能最差的SQL

优化哪个表？

从何入手？

首先需要定位性能瓶颈,找到运行最慢的SＱL。

可以采用如下步骤：

１.运行dbcc 　freeProcＣache 清除缓存

２．运行你的程序,或者你的SＱＬ或存储过程，操作数据库

3．完了以后运行以下SQL找到运行最慢的SQL：

SELECT ＤB_IＤ（DB.dbｉd）'数据库名'

     ,OＢJECT_ID（db.obｊｅctｉd）'对象'　ﻫ     　,QS.ｃreａtｉon_time'编译计划的时间'　

     ,QS．lａst_eｘecution_tｉｍe　'上次执行计划的时间'　ﻫ     　,QS．exｅcｕｔion_cｏｕnt　＇执行的次数'

     ,　ＱＳ.tｏtal_elapseｄ_timｅ/１0０0　'占用的总时间（秒）'ﻫ     ，　QＳ.total_physical＿reａds　'物理读取总次数'　ﻫ     ,QＳ.tｏｔａl_worker_tiｍｅ/1000'CＰU时间总量（秒）'ﻫ     　,QS.totaｌ＿logicaｌ_wｒites＇逻辑写入总次数＇

     ，ＱS.ｔoｔal_lｏgical＿reaｄsN'逻辑读取总次数＇ﻫ     　,ＱS.total_elapsed_tｉmｅ/10０0N'总花费时间（秒）'ﻫ     ,SUBＳTRIＮＧ（ＳT．text,（QS.stateｍｅnt_stａｒｔ_offset　/2）+1,

                 　（（CＡＳEstateｍent＿ｅnd_ｏffseｔﻫ                       WＨEN　－1THEＮ　DＡTＡLＥＮGTＨ（ｓt.text）ﻫ                       ELSEQS.ｓtateｍenｔ_end_ｏfｆｓet

                     END-ＱＳ.sｔａtｅmeｎｔ_starｔ_offseｔ）/　２）　＋1）AS'执行语句'　ﻫFROM   sｙｓ.ｄｍ＿eｘｅｃ_qｕery＿ｓtａtsAＳQSCＲOSS　APＰLＹ　ﻫ       　sys.dｍ_eｘec_sql_text（QS.sql_hａｎdle）ASＳＴＩNNEＲ　JOＩNﻫ       （ＳELECT   *

         ＦROＭ     sys.ｄm_exｅｃ＿caｃhｅd_pｌaｎs　cpCＲＯＳSAPＰLＹ　

                   　ｓys.ｄｍ_ｅxeｃ_query_ｐlan（cｐ.plａｎ＿handｌe）

       　）　DBﻫ           ＯNQS.ｐlan_handｌe=DＢ.plan＿handleﻫwheｒe  SＵＢSＴRＩNG（st.texｔ,（qs.staｔｅment_start_offset/2　）+1,

                 （（　ＣAＳＥsｔatｅｍeｎt_ｅnｄ_offｓｅt

                       WHEN-1THＥNDＡTALEＮGTH（st．text）ﻫ                       ＥLSE　ｑs.stａtｅmｅnｔ＿enｄ_offset

                     ＥNＤ-qs.ｓｔatemｅｎt_sｔarｔ＿ofｆset）　/　2　）+1）notｌｉkｅ'％fｅｔcｈ％＇

                     ORDＥRBY　QＳ．ｔotal＿elapsed＿time　/1000DESC

使用ＳＱLSｅrｖeｒＰrｏｆileｒ找出数据库中性能最差的SQＬ

首先打开ＳQＬＳｅrvｅrProfｉｌer:

然后点击工具栏“NewTraｃe”，使用默认的模板,点击RＵＮ。

也许会有报错：

＂onlyTｒueTypefｏntsarｅsｕpported.TｈｅｒｅｉdｎotaTｒueＴypefont＂。

不用怕,点击Ｔoｏls菜单->Options,重新选择一个字体例如Vendanａ即可。

（这个是微软的一个ｂuｇ）

运行起来以后,SQＬServer　Pｒofilｅr会监控数据库的活动，所以最好在你需要监控的数据库上多做些操作。

等觉得差不多了，点击停止。

然后保存tｒａce结果到文件或者ｔａｂlｅ。

这里保存到Taｂｌｅ：

在菜单“Ｆｉle”-“Ｓaveas”-“Tracetable”，例如输入一个masｔｅr数据库的新的ｔaｂlｅ名:

profｉｌeＴraｃe，保存即可。

找到最耗时的SQL：

usｅmasｔer

sｅlecｔ*ｆroｍprofiletraｃeorderbyduｒationdesc；

找到了性能瓶颈，接下来就可以有针对性的一个个进行调优了。

对使用ＳQLSerｖｅrProfilｅr的更多信息可以参考：

ｈttｐ:

//wwｗ.codｅpｒojecｔ.coｍ/ＫB／dataｂaｓe/ＤiagｎｏsePｒoblemsSQＬSｅrver.ａspx

使用ＳQＬＳeｒｖｅr　ＤatａbａseEngiｎeTuｎingAdviｓor数据库引擎优化顾问

使用上述的SQLServerPrｏｆiler得到了tｒace还有一个好处就是可以用到这个优化顾问。

用它可以偷点懒,得到SQLSeｒver给您的优化顾问，例如这个表需要加个索引什么的…

首先打开数据库引擎优化顾问:

然后打开刚才prｏfiler的结果（我们存到了masteｒ数据库的ｐｒofileTraｃe表）：

点击“stａｒtanａｌyｓis”，运行完成后查看优化建议（图中最后是建议建立的索引,性能提升72%）

这个方法可以偷点懒,得到ＳQＬServer给您的优化顾问。