SQLServer索引调优实践
SQＬSerｖｅr索引调优实践
聚簇索引的唯一性
正式聚簇索引的顺序就是数据的物理存储顺序,所以一个表最多只能有一个聚簇索引，因为物理存储只能有一个顺序。正因为一个表最多只能有一个聚簇索引，所以它显得更为珍贵，一个表设置什么为聚簇索引对性能很关键。
初学者最大的误区：把主键自动设为聚簇索引
因为这是ＳQLServｅr的默认主键行为,你设置了主键，它就把主键设为聚簇索引，而一个表最多只能有一个聚簇索引,所以很多人就把其他索引设置为非聚簇索引。这个是最大的误区。甚至有的主键又是无意义的自动增量字段,那样的话Ｃlustered indｅx对效率的帮助，完全被浪费了。
刚才说到了,聚簇索引性能最好而且具有唯一性，所以非常珍贵，必须慎重设置。一般要根据这个表最常用的ＳQL查询方式来进行选择，某个字段作为聚簇索引,或组合聚簇索引，这个要看实际情况。
事实上，建表的时候,先需要设置主键，然后添加我们想要的聚簇索引,最后设置主键，ＳＱＬServ ｅｒ就会自动把主键设置为非聚簇索引(会自动根据情况选择）。如果你已经设置了主键为聚簇索引,必须先删除主键，然后添加我们想要的聚簇索引,最后恢复设置主键即可。
记住我们的最终目的就是在相同结果集情况下,尽可能减少逻辑IＯ。
我们先从一个实际使用的简单例子开始。
一个简单的表：
CREATＥTABＬＥ[ｄbｏ］.[Ｔａbｌｅ１](
[ID] [inｔ] IDENTITY(1,1)ＮOT NULL，
[Data1］［ｉnt] NOT NＵLL DEFAULT ((０）),
[Daｔa2] [int] NOT ＮＵＬL ＤEFAULT ((０)),
[Ｄａta3］[ｉｎｔ］ＮOＴＮULＬDEＦＡULT((0)),
［Ｎame1] [nvａｒcｈar］（50)NOT NULL DEＦAＵLT (''),
［Ｎame2] [nvarchar](50) NＯＴNULＬDEFAＵLＴ(＇'),
[Ｎame3] [nvarｃhaｒ](50）DEFAULT（'')，
[ＤTAt] ［datetｉme]NOTＮＵLL DEＦAULＴ(ｇetｄate())
来点测试数据（1０w条）:
decｌａｒe @i inｔ
set @ｉ＝1
wｈｉｌｅ＠i < 1０00０0
beｇiｎ
inseｒt intｏ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ｅ1 set ｄtat＝DatｅAdd (s，dａｔａ1, dtat）
打开查询分析器的IO统计和时间统计：
SEＴＳTＡＴISTICS IＯON;
SET SＴAＴＩSTICS TIＭＥON;
显示实际的“执行计划”：
我们最常用的SQL查询是这样的：
SELECＴ＊FROＭTａblｅ1 WHERE Dａta1 = 2 ＯRDERＢY DTAt DEＳＣ;
先在Tａble1设主键ID，系统自动为该主键建立了聚簇索引。
然后执行该语句,结果是:
Taｂle 'Table1＇．Scan count１,logｉcal ｒeａds 91１, physiｃal reads 0, reaｄ－ahead readｓ0，lob logｉcal reａds0, lob ｐｈysiｃaｌrｅads 0, lob read-aｈeａd ｒeａdｓ0.
ＳＱL Ｓervｅr Execuｔion Times:
CＰU tｉｍe = １６ｍs, elapsed time = 7ｍs.
然后我们在Data1和DTaｔ字段分别建立非聚簇索引:
CREＡTE NＯNCLUSＴERED INDEX [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]
CREATE NONCLUSＴＥ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ｇｉｃal reads 5, ｐｈysical reａｄs 0,rｅaｄ-ａheａｄreads 0, lob logical rｅａｄｓ0，loｂphysiｃaｌre ａds ０,lｏb read-ahｅad rｅａｄs 0.
ＳQL SｅrｖeｒＥxecｕtiｏn Ｔimes:
ＣＰＵtimｅ＝０ms，elapｓeｄｔime =39 ms.
可以看到设立了索引反而没有任何性能的提升而且消耗的时间更多了，继续调整。
然后我们删除所有非聚簇索引,并删除主键,这样所有索引都删除了。建立组合索引Datａ1和DTAt,最后加上主键：
ＣRＥAＴE CLUSＴERED INDEX [Ｃ_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 count1, logｉcaｌrｅadｓ3, physｉcal reads 0, read-aｈead reaｄs 0, lob ｌｏgiｃal readｓ0,loｂｐｈysicａl reaｄs 0, lob rｅad-ahｅaｄrｅaｄs ０．
SQL Seｒｖer ExｅcｕtiｏｎTimｅｓ:
CPUｔime = ０ｍｓ, elapｓed time= 1 ｍｓ.
可以看到只有聚簇索引sｅek了，消除了indeｘscaｎ和ｎestｅｄｌoop，而且执行时间也只有1ms，达到了最初优化的目的。
组合索引小结
小结以上的调优实践,要注意聚簇索引的选择。首先我们要找到我们最多用到的SQL查询，像本例就是那句类似的组合条件查询的情况,这种情况最好使用组合聚簇索引,而且最多用到的字段要放在组合聚簇索引的前面，否则的话就索引就不会有好的效果，看下例：
查询条件落在组合索引的第二个字段上,引起了index scan，效果很不好，执行时间是：
Ｔable'Taｂlｅ1'. Scaｎcoｕnｔ1，logiｃal readｓ238, physical reads ０，read－ahｅaｄｒeads０, lob ｌogｉcal reaｄs 0, ｌob physiｃａl reａds 0，lob rｅａd-ahead ｒeａds０.
SQL Ｓerver Eｘecution Ｔｉmes:
CPＵtiｍｅ＝16mｓ，elａpseｄtｉmｅ= 22mｓ.
而如果仅查询条件是第一个字段也没有问题，因为组合索引最左前缀原则,实践如下:
Table 'Tablｅ1'．Scａnｃount 1, ｌogｉcａl reａdｓ３, pｈｙsicaｌｒｅａds０, reａd-ａｈead ｒeaｄｓ0, lob logiｃal reａds 0,ｌob physicａｌreａds 0, loｂread-aheaｄreads 0.
ＳQL Servｅr Exeｃuｔiｏn Ｔｉmes:
ＣPＵｔiｍe ＝0ｍs，ｅlａpseｄtｉme ＝ 1 ｍs.
从中可以看出，最多用到的字段要放在组合聚簇索引的前面。
Indeｘseek 为什么比Iｎdｅx scan好？
索引扫描也就是遍历B树,而seek是B树查找直接定位。
Iｎdex ｓcａn多半是出现在索引列在表达式中。数据库引擎无法直接确定你要的列的值,所以只能扫描整个整个索引进行计算。indｅx seek就要好很多.数据库引擎只需要扫描几个分支节点就可以定位到你要的记录。回过来,如果聚集索引的叶子节点就是记录,那么Clustered Iｎdex Scan就基本等同于ｆulｌtａblｅscan。
一些优化原则
?1、缺省情况下建立的索引是非聚簇索引，但有时它并不是最佳的。在非群集索引下，数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说：?ａ.有大量重复值、且经常有范围查询( > ,< ，> =,< =）和order by、grｏup by 发生的列，可考?虑建立群集索引;? 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，手动去设置它,而不是把它设为默认
4、如果你的数据只有你所知的少量的值。?
3、尽量少用VＡRCＨＡR、TＥXＴ、ＢLOB类型?
几个。最好使用ＥＮＵM类型
有关Jｏｉn的一些原则
ＳQL Ｓeｒvｅｒ有三种类型的JOＩN操作：
?Ｎesｔed loｏps jｏins
?Merge joins
?Hash jｏinｓ
?
如果Join的输入很小,例如小于10行，然后其他的Ｊoiｎ输入很大并且索引在其列上,则Nesｔｅd ｌｏoｐs jｏins是最快的。（原因参考Undｅｒstandｉｎg Nesｔed LoopsＪoins）
如果两个Join输入都不小，但在索引列上排序(例如是在扫描排序的索引后获得的scannｉngｓｏrtｅd ｉndexｅs）,则Merｇｅｊｏｉnｓ是最快的。（原因参考Underｓtaｎdｉｎg MerｇｅＪｏinｓ）
Ｈａsｈjoins可以有效的处理大量的、没有排序的、没有索引的输入。尤其对复杂查询的中间结果处理很有效。(更多参考UnｄerstａnｄinｇHａsh Joins)
如何分析SQL语句
微软MSDN给出了答案:ｈｔtp：／/msdn．microsｏhttps://www.360docs.net/doc/cf15685083.html,/en-us/library/mｓ１91227.aｓpx
找出数据库中性能最差的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ｄs N'逻辑读取总次数＇?, ＱS.total_elapsed_tｉm ｅ/ 10０0 N'总花费时间(秒)' ?, SUBＳTRIＮＧ（ＳT．text, ( QS.stateｍｅnt_stａｒｔ_offset/ 2 ) + 1,
（（CＡＳE stateｍent＿ｅnd_ｏffseｔ?WＨEN－1 THEＮDＡTＡLＥＮGTＨ(ｓt.text) ?ELSE QS.ｓtateｍenｔ_end_ｏfｆｓet
END - ＱＳ.sｔａtｅmeｎｔ_starｔ_offseｔ) /２)＋1) AS '执行语句'?FROM sｙｓ.ｄｍ＿eｘｅｃ_qｕery＿ｓtａts AＳQS CＲ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 cp CＲＯＳS APＰLＹ
ｓys.ｄｍ_ｅxeｃ_query_ｐlan(cｐ.plａｎ＿handｌe)
)DB ?ＯN QS.ｐ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 -1 THＥN DＡ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ＥR BY QＳ．ｔotal＿elapsed＿time/ 1000 DESC
使用ＳＱLSｅrｖeｒＰrｏｆileｒ找出数据库中性能最差的SQＬ
首先打开ＳQＬＳｅrvｅr Profｉｌer:
然后点击工具栏“New Traｃe”，使用默认的模板,点击RＵＮ。
也许会有报错：＂only TｒueType fｏnts arｅsｕpported. Tｈｅｒｅｉd ｎot a TｒueＴype font＂。不用怕,点击Ｔoｏls菜单->Options,重新选择一个字体例如Vendanａ即可。（这个是微软的一个ｂuｇ)
运行起来以后,SQＬServer Pｒofilｅr会监控数据库的活动，所以最好在你需要监控的数据库上多做些操作。等觉得差不多了，点击停止。然后保存tｒａce结果到文件或者ｔａｂlｅ。
这里保存到Taｂｌｅ：在菜单“Ｆｉle”-“Ｓave as ”-“Trace table”，例如输入一个masｔｅr数据库的新的ｔaｂlｅ名:profｉｌeＴraｃe，保存即可。
找到最耗时的SQL：
usｅmasｔer
sｅlecｔ* ｆroｍprofiletraｃe order by duｒation desc；
找到了性能瓶颈，接下来就可以有针对性的一个个进行调优了。
对使用ＳQLSerｖｅr Profilｅr的更多信息可以参考：
ｈttｐ://wwｗ.codｅpｒojecｔ.coｍ/ＫB／dataｂaｓe/ＤiagｎｏsePｒoblemsSQＬSｅrver.ａspx
使用ＳQＬＳeｒｖｅrＤatａbａse Engiｎe Tuｎing Adviｓor数据库引擎优化顾问
使用上述的SQLServer Prｏｆiler得到了tｒace还有一个好处就是可以用到这个优化顾问。用它可以偷点懒,得到SQLSeｒver给您的优化顾问，例如这个表需要加个索引什么的…
首先打开数据库引擎优化顾问:
然后打开刚才prｏfiler的结果（我们存到了masteｒ数据库的ｐｒofileTraｃe表）：
点击“stａｒt anａｌyｓis”，运行完成后查看优化建议(图中最后是建议建立的索引,性能提升72%)
这个方法可以偷点懒,得到ＳQＬServer给您的优化顾问。