《SQLServer索引调优实践.doc》由会员分享，可在线阅读，更多相关《SQLServer索引调优实践.doc（12页珍藏版）》请在三一文库上搜索。
1、SQL Ser v e索引调优实践索引的重要性数据库性能优化中索引绝对是一个重虽级的因索，可以说，索引使用不当,其它优化描施将亳无意义。聚簇索弓|(Clus t ere d Inde x )和非聚簇索引(Non- CI ustered Inde x )最通俗的解释是：聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的索引顺序与数据物理 排列顺序无关。举例來说，你翻到新华字典的汉字爬那一页就是P开头的部分，这就是物理存储顺 序(聚簇索引：而不用你到口录，找到汉字爬所在的页码，然后根据页码找到这个字(非聚簇索引)。卜表给出了何时使用聚簇索引与非聚簇索引：动作使用聚簇索引使用非聚簇索引列经常被分
2、组排序应应返回某范围内的数据应不应一个或极少不同值不应不应小数目的不同值应不应大数目的不同值不应应频繁更新的列不应应外键列应应主键列应应频繁修改索引列不应应聚簇索引的唯一性正式聚簇索引的顺序就是数据的物理存储顺序，所以一个表最多只能有一个聚簇索引，因为物理存储 只能有一个顺序。正因为一个表最多只能有一个聚簇索引，所以它显得更为珍贵，一个表设且什么为 聚簇索引对性能很关键。初学者鼓大的误区：把主键自动设为聚簇索引因为这是S QLServ e r的默认丄键行为，你设键，它就把丄键设为聚簇索引，而一个表最多只 能有一个聚簇索引，所以很多人就把其他索引设置为非聚簇索引。这个是最大的误区。甚至有的主键
3、又是无总义的口动增虽字段，那样的话Clustered index对效率的帮助，完全被浪费了。刚才说到了，聚簇索引性能最好而且具有唯一性，所以非常珍贵，必须慎重设迓。一般要根据这个表 报常用的SQL查询方式來进行选择，某个字段作为聚簇索引，或组合聚簇索引，这个耍看实际情况。事实上，建表的时候,先需要设且上键，然后添加我们想要的聚簇索引，最后设豆上键，SQLServe就会门动把上键设且为非聚簇索引（会I动根据惜况选择）。如果你己经设置了上键为聚簇索引, 必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设星主键即町。记住我们的最终口的就是在相同结果集情况卜，尽可能减少逻辑10。我们先从一个实际使
4、用的简单例子开始。一个简单的表：CREATE TABL E dbo .T ab 1 e 1 (ID in t IDENTITY(1,1) NOT NULL,Datal i nt NOT NULL DEFAULT ( 0 ) )zDa t a2 int NOT NULL D EFAULT ( 0 ),D a ta3 int NOT NULL DEFAULT (0),Namel nva rchar (50) NOT NULL DEFAULT (), Name2 nvarchar(50) NOT NULL DEFAULT (z ), Name3 nvar c ha r (50) DEFAULT ()
5、,DTAt datet i me NOT NULL DEFAULT (getdate() dec I a r e i in tColum n NameData TypejintDatalintData 2intData 3intNameln varchar(50)Name 2nvarchar(50)Name 3nvarchar(50)DTAtdatetime来点测试数据（10W条）：Alloys- Nullsset i = 1wh i 1 e i , =, =)和order by. group by 发生的列，町考“虑建立群集索引；亠b.经常同时存取多列，且每列都含有重复值可考虑建立 组合索引
6、；亠c.组合索引要尽虽:使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。索 引虽有助于提高性能但不是索引越多越好，恰好相反过多的索引会导致系统低效。用戸在表中每 加进一个索引，维护索引集合就要做相应的更新工作。2、ORDER BY 和 GROPU BY 使用 0 RD E R BY 和 GROUP BY 短语，任何一种索 引都有助于SELECT的性能提高。3、多表操作在被实际执行前，査询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统 开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表；内外表的选infill公式:外 层表中的匹配行数*内层表中每一次査找的次数确定，
7、乘积最小为最佳方案。4、任何对列的操作都将导致表扌描,它包括数据库函数、计算表达式等等，査询时耍尽可能将操作移 至等号右边。弘、IN、OR子句常会使用工作表，使索引失效。如果不产生大虽重复值，町以考虑 把子句拆开。拆开的子句中应该包含索引。Sql的优化原则2：1、只要能满足你的需求，应尽可能使用更小的数据类型：例如使用MEDIUM I NT代替INT2、尽虽把所有的列设呂为NOT NULL,如果你要保存NULL,手动去设迓它，而不是把它设为默认 值。3a、尽虽少用VARCHAR. TEXT、BLOB类型、如果你的数据只有你所知的少虽的 几个。最好使用ENUM类型有关J o i n的一些原则SQ
8、L Serve r有三种类型的JO I N操作：N es t ed Io o ps j o insMerge joinsHash j o in s如果Join的输入很小，例如小于10行,然后其他的Join输入很大并且索引在其列上，则Nes t ed1 o o p s j o ins 是最快的a （原因参考 Und estand i n q Nes t ed Loops J oins）如果两个Join输入都不小，但在索引列上排序（例如是在扌描排序的索引后获得的scann i ng s o rt e d in dexes） z 则 Merg e joins 是最快的。（原因参考 Understan
9、d i nq Meg e Joins）Hash joins可以有效的处理大虽的、没有排序的、没有索引的输入。尤其对复杂査询的中间结果 处理很有效o （更多参考Un d est ending H n sh Joins）如何分析SQL语句微软 MSDN 给出了答案:h t tp： /msdn. microso s 1 91227.a sP2WHEN -1THEN DATALE NGTH( s t.text) aELSE QS statemen t _end_of fs etEND QS.st ate men t _star t _offse t )/2 ) + 1) AS 执行语句a FROM s
10、ys d m_e x e c_q u ery_ s t a ts AS QS CROSS APPLYasysdm_e x ec_sql_text(QS.sql_h a n die) AS S T I NNE R JO I Na( SELECT *F ROM sys. d m_ex e c _ca c h e d_p Ians cp CR 0 SS APPLYs ys. d m_ e xe c _quey_ p lan(c p pl a n _hand 1 e)DBON QS. p Ian _ha nd 1 e = DB.plan_ha ndle whe r e SUBSTR I NG (st.
11、tex t, ( qs.sta t e ment_stat_offset / 2 ) + 1,(CASE S t at e me nt_en d_offs e tWHEN -1 THEN DATALENGTH(St. texELSE qs.st a t e m e n t _en d offsetEND qs. s t atem e nt_s t ar t _of f set) / 2 ) + 1) not like %fe t ch %ORDER BY QS t otal_elapsed_time / 1000 DESC使用S QLServer Pro filer找出数据库中性能最差的SQL
12、首先打开SQL S erver Prof i 1 er:皎 Microsoft SQL Server Management StudioFile Edit View Query ProjectTools Window Community HelpJ. New Q“ry山忸秘阻口： SQL Sender Profiler3?魅 masterDatabase Engine Tuning AdviserCheese Tcclbcx Items.Object ExplorerConnect 电 二也 /Ext ernalTools.Custcinize.Cpticns.然后点击工具栏New Tra c
13、使用默认的模板，点击RUN。也许会有报错：H onlyT r ueType f o nts are s u pported .There i d notaT rue Type font。不用怕，点击Tools菜单-Optionsz重新选择一个字体例如Vendano即叭（这个是微 软的一个b Ug）运行起來以后SQLServer P r ofil e r会监控数据库的活动，所以最好在你需要监控的数据库上多 做些操作。等觉得差不多了，点击停止。然后保存ace结果到文件或者t a bleo这里保存到Tab 1 e： （菜单i leSave as-Trace table,例如输入一个mast e数据库
14、的新的table名：prof i 1 eTa c e,保存即叭找到最耗时的SQL：us e mas t ers e lec t * f rom profiletra c e order by du r ation desc；找到了性能瓶颈，接卜來就可以有针对性的一个个进行调优了。对使用s QLSer v e r Profil e r的更多信息M以参考：h tt p :ww w .cod e pojec t com/ K B / data b a s e/ D iaq n o sePoblemsSQ I, S eve a spx使用 SQL Server D at a b a se Engine
15、 Tuning Advi s or 数据库引擎优化顾问使用上述的SQLServer Pro filer得到了 trace还有一个好处就是町以用到这个优化顾问。用它可以偷点懒，得到SQLSe r ver给您的优化顾问，例如这个表需要加个索引什么的.首先打开数据库引擎优化顾问：咲 Microsoft SQL Server Management StudioFile Edit View Qu&ry ProjectTeels Window Community Help.jNivQMry 愠密的匚SQL Ser.zer Profiler，电2 陋 masterDatabas亡 Engine *uning
16、 AdviserObject ExplorerChoose Tool box Items.Connects 恕总External Tools.Customize.Options.然后打开刚才profiler的结果（我们存到了 maste数据库的p rofileTrace表）：General芯也簸3 54 34 pF打开刚才保存p rofiler果的表Select dauboses and bles toID NorreQjMMMi qi口 111 rwter (J rrdel iB e 总 (j Rwttgwj 珈oo-49V|27yV0 3 btnrg togg 師皿h thiw ect a catibaH toEnor* Tirwg Advwxcona forthef you wktoad mckxMrw w Tiww?-SQLMah&M 0皿轴 Erx* 心E 册sy Ml Mo 少勺uuus Z mV刊 theWw om upecabaMtf orcablet to tune点*vsta rtana 1 y s is.运行完成后査看优化建议（图中最后是建议建立的索引，性能提升72%）Estudedinpoveaert: 72XPattoRecoMeftiAnImhRecoMenidlEmg jC0Jfe*i*这个方法可以偷点懒，得到SQL Server给您的优化顾问。