SQL Server索引自动优化工具

  次阅读 作者:智能小宝 来源:互联网 2016-03-01 10:21 我要评论(0)

前段接手了个优化项目,大概要求是对公司现有的1W多张表进行索引优化,完善现有的,剔除无效的索引。鉴于人手严重不足(当时算两个半人的资源),打消了逐个库手动去改的念头。当前的程序结构不允许搞革命的做法,只能搞搞改良,所以准备搞个自动化工具去处理。原型刚开发完,开会的时候以拿出来就遭到运维DBA团队强烈抵制,具体原因不详。最后无限延期。这里把思路分享下。欢迎拍砖。

整个思路是这样的,索引都是为查询和更新服务的,但是不合适的索引又会对插入和更新带来负面影响。面对表上现有的索引想识别那些是有效的不太可能。那么根据现有的数据使用情况重建所有的新索引不就解决了嘛。根据查询生成全新索引,然后和现有对比,不吻合的全部删除,原来没有的创建。虽然说对于正在运行的系统来说风险还是蛮大的。但是可以做临界测试嘛。

具体解决方案如下:

首先在热备的数据库服务器上定期抓取缓存的执行计划(原本想抓取SQL发现有些SQL实在掺不忍睹,没有自动化解析的可能性),然后连同该执行的执行次数即表的统计信息一起down到一个备用服务器的数据表中。

执行计划积累几次后,开始解析。由于执行计划是格式良好的XML文件,加上微软提供执行计划的XSD文件。我们可以反向推出各节点对应的SQL谓词(这个XSD到现在都没找到官方的说明,只能反向推出关联)。例如建立索引我们比较关心三类谓词,分别为:Select,Join,Where. 只要拿到这些我们就能建立良好的索引。原理很简单,Join和Where都是索引键的依据,而Select可以斟请添加到Index的Include中。

解析的时候也不是针对单个执行计划,而是将所有执行计划全分解后进行统计处理。好处就是能够知道那些表字段被引用的最多,那些是外键列。那些数据被反复查询。例如可以得出TableA的Col1列在一天的业务过程中被Join了10W次,被Where2W次。而Col2则被Select了10W次,仅仅被Where了100次。这样我们建立索引的基础就是基于表的而不是基于单个查询的。最终生成的Index将权衡查询频率和查询的重要性,如果某个业务查询特别重要,但执行频率不高我们可以提供权重,优先建立索引。当然创建Index还要参考表的数据分布以决定Index中字段的顺序。

好了,准备工作完成,开始建索引。当前拥有的条件,表数据分布,表字段分别被查询引用次数(Select,Join,Where),以及这些SQL谓词出现的次数。根据这些如何创建索引开始的想法是逐个分析,考虑所有可能性然后创建。发现这种方式只适合人脑,让电脑做得先让电脑的智商增长到120以上才有可行性。发现逆向思维这里同样大有用处,既然不能一下子创建最合适的,那我们就根据执行计划得出的组合创建所有的Index组合。凡是Join和Where都放到Index的Key里。例如:

select t1.A, t1.B, t1.C, t2.J, t2.k from Table1 t1 Join Table1 t2 on t1.A = t2.j Where t1.A = 'param'

草创的索引就是:

Index(A,B)includ(C) 和 Index(j)include(j,k)

关于Select如果是小数据类型且Alter的执行计划中该数据修改频率很小的都放到Include里去进去。大数据类型和修改比较频繁的就算了。这样我们剔除相互覆盖的。部分重叠的,部分重叠到底保留那一个参考执行频率和查询重要性。差异很小的就合并并为一个,如:

1、Index (A,B,C)Include(D)

2、Index(A,B,D)Include(C)

直接合并为:

Index(A,B)Include(C,D)

当然如果Alert的特别少也可以合并成Index(A,B,C,D)这个要参考C,D字段的修改频率。和主键重叠的剔除。这样留下的基本上就是我们需要的索引了。

对比现有索引进行甄别覆盖的过程就略过。简单的拉出来Create Index 进行解析处理就好了。发布的时候很简单。写个脚本在业务比较少的时候做Drop和Create就完成了。项目源代码因为设计到公司的保密问题就不上传了。一个注意的地方对于简单查询的SQL执行计划缓存的时候会比较短且一旦缓存不够就会被清理掉。要注意这些SQL的执行频率的误差。

SqlserverR2 XSD:schemas.microsoft./sqlserver/2004/07/showplan/sql2008/showplanxml.xsd

总结的节点映射列举如下:

查询sql执行计划都包含在节点"StmtSimple"中,如果没有这个节点一般就是其它类型的SQL的执行计划。

Join关联的节点和自身类型有关一般包含在Hash,Marger中,如何Join同时又是Where条件的话则会出现在SeekKey和Compare节点中,因为Join的列都是成对出现,这里很容易识别,有一个是参数(@开头)或常量(type="Const")则必定是Where条件。

Select最终输出字段比较容易找到,第一个OutputList节点就是。

需要注意的是有因为一般列每个ColumnReference都包含库名,表名,列信息,但是系统表则不会。注意剔除。

本站文章信息来源于网络以及网友投稿,本站只负责对文章进行整理、排版、编辑,是出于传递更多信息之目的,并不意味着赞同其观点或证实其内容的真实性。如果您有什么意见或建议,请联系QQ28-1688-302!

人工智能实验室
相关文章相关文章
  • 未来两年人工智能要怎么走?看这篇就够了

    未来两年人工智能要怎么走?看这篇就够了

  • 韩春雨称已能重复实验结果 近期将有消息公布

    韩春雨称已能重复实验结果 近期将有消息公布

  • 无人驾驶汽车如何改变城市生活?听听他们怎么说

    无人驾驶汽车如何改变城市生活?听听他们怎么说

  • 英国研发“杀生”机器人 通过生命体获取能量

    英国研发“杀生”机器人 通过生命体获取能量

网友点评网友点评
阅读推荐阅读推荐

据国外媒体报道,在过去两年内,聊天机器人(chatbot)、人工智能以及机器学习的研发和采用取得了巨大进展。许多初创公司正利用人工智能和...

霍金 视觉中国 图 英国著名物理学家霍金(Stephen Hawking)再次就人工智能(AI)发声,他认为:对于人类来说,强大AI的出现可能是最美妙的...

文|郑娟娟 今年,人工智能(AI) 60岁了。在AI60岁的时候,笔者想要介绍一下AI100,一个刚刚2岁的研究项目,但它的预设寿命是100年,甚至更长...

AlphaGo与李世石的人机大战,为大众迅速普及了人工智能的概念。 但对谷歌而言,除了下围棋,现在的人工智能进展到哪一步了?未来,人工智能...