前段接手了个优化项目大概要求是对公司现有的W多张表进行索引优化完善现有的剔除无效的索引鑒于人手严重不足(当时算两个半人的资源)打消了逐个库手动去改的念头当前的程序结构不允许搞革命的做法只能搞搞改良所以准备搞个自动化工具去处理原型刚开发完开会的时候以拿出来就遭到运维DBA团队强烈抵制具体原因不详最后无限延期这里把思路分享下欢迎拍砖
整个思路是这样的索引都是为查询和更新服务的但是不合适的索引又会对插入和更新带来负面影响面对表上现有的索引想识别那些是有效的不太可能那么根据现有的数据使用情况重建所有的新索引不就解决了嘛根据查询生成全新索引然后和现有对比不吻合的全部删除原来没有的创建虽然说对于正在运行的系统来说风险还是蛮大的但是可以做临界测试嘛
具体解决方案如下
首先在热备的数据库服务器上定期抓取缓存的执行计划(原本想抓取SQL发现有些SQL实在掺不忍睹没有自动化解析的可能性)然后连同该执行的执行次数即表的统计信息一起down到一个备用服务器的数据表中
执行计划积累几次后开始解析由于执行计划是格式良好的XML文件加上微软提供执行计划的XSD文件我们可以反向推出各节点对应的SQL谓词(这个XSD到现在都没找到官方的说明只能反向推出关联)例如建立索引我们比较关心三类谓词分别为SelectJoinWhere 只要拿到这些我们就能建立良好的索引原理很简单Join和Where都是索引键的依据而Select可以斟请添加到Index的Include中
解析的时候也不是针对单个执行计划而是将所有执行计划全分解后进行统计处理好处就是能够知道那些表字段被引用的最多那些是外键列那些数据被反复查询例如可以得出TableA的Col列在一天的业务过程中被Join了W次被WhereW次而Col则被Select了W次仅仅被Where了次这样我们建立索引的基础就是基于表的而不是基于单个查询的最终生成的Index将权衡查询频率和查询的重要性如果某个业务查询特别重要但执行频率不高我们可以提供权重优先建立索引当然创建Index还要参考表的数据分布以决定Index中字段的顺序
好了准备工作完成开始建索引当前拥有的条件表数据分布表字段分别被查询引用次数(SelectJoinWhere)以及这些SQL谓词出现的次数根据这些如何创建索引开始的想法是逐个分析考虑所有可能性然后创建发现这种方式只适合人脑让电脑做得先让电脑的智商增长到以上才有可行性发现逆向思维这里同样大有用处既然不能一下子创建最合适的那我们就根据执行计划得出的组合创建所有的Index组合凡是Join和Where都放到Index的Key里例如
select tA tB tC tJ tk from Table t Join Table t on tA = tj Where tA = param
草创的索引就是
Index(AB)includ(C) 和 Index(j)include(jk)
关于Select如果是小数据类型且Alter的执行计划中该数据修改频率很小的都放到Include里去进去大数据类型和修改比较频繁的就算了这样我们剔除相互覆盖的部分重叠的部分重叠到底保留那一个参考执行频率和查询重要性差异很小的就合并并为一个如
Index (ABC)Include(D)
Index(ABD)Include(C)
直接合并为
Index(AB)Include(CD)
当然如果Alert的特别少也可以合并成Index(ABCD)这个要参考CD字段的修改频率和主键重叠的剔除这样留下的基本上就是我们需要的索引了
对比现有索引进行甄别覆盖的过程就略过简单的拉出来Create Index 进行解析处理就好了发布的时候很简单写个脚本在业务比较少的时候做Drop和Create就完成了项目源代码因为设计到公司的保密问题就不上传了一个注意的地方对于简单查询的SQL执行计划缓存的时候会比较短且一旦缓存不够就会被清理掉要注意这些SQL的执行频率的误差
SqlserverR XSD:schemasmicrosoft/sqlserver///showplan/sql/showplanxmlxsd
总结的节点映射列举如下
查询sql执行计划都包含在节点"StmtSimple"中如果没有这个节点一般就是其它类型的SQL的执行计划
Join关联的节点和自身类型有关一般包含在HashMarger中如何Join同时又是Where条件的话则会出现在SeekKey和Compare节点中因为Join的列都是成对出现这里很容易识别有一个是参数(@开头)或常量(type="Const")则必定是Where条件
Select最终输出字段比较容易找到第一个OutputList节点就是
需要注意的是有因为一般列每个ColumnReference都包含库名表名列信息但是系统表则不会注意剔除
