SqlServer 索引自动优化工具
程序员文章站
2023-12-01 09:38:16
鉴于人手严重不足（当时算两个半人的资源），打消了逐个库手动去改的念头。当前的程序结构不允许搞革命的做法，只能搞搞改良，所以准备搞个自动化工具去处理。原型刚开发完，开会的时候...
鉴于人手严重不足（当时算两个半人的资源），打消了逐个库手动去改的念头。当前的程序结构不允许搞革命的做法，只能搞搞改良，所以准备搞个自动化工具去处理。原型刚开发完，开会的时候以拿出来就遭到运维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：
总结的节点映射列举如下：
查询sql执行计划都包含在节点“stmtsimple”中，如果没有这个节点一般就是其它类型的sql的执行计划。
join关联的节点和自身类型有关一般包含在hash，marger中，如何join同时又是where条件的话则会出现在seekkey和compare节点中，因为join的列都是成对出现，这里很容易识别，有一个是参数（@开头）或常量（type="const"）则必定是where条件。
select最终输出字段比较容易找到，第一个outputlist节点就是。
需要注意的是有因为一般列每个columnreference都包含库名，表名，列信息，但是系统表则不会。注意剔除。
整个思路是这样的，索引都是为查询和更新服务的，但是不合适的索引又会对插入和更新带来负面影响。面对表上现有的索引想识别那些是有效的不太可能。那么根据现有的数据使用情况重建所有的新索引不就解决了嘛。根据查询生成全新索引，然后和现有对比，不吻合的全部删除，原来没有的创建。虽然说对于正在运行的系统来说风险还是蛮大的。但是可以做临界测试嘛。
具体解决方案如下：
首先在热备的数据库服务器上定期抓取缓存的执行计划（原本想抓取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：
总结的节点映射列举如下：
查询sql执行计划都包含在节点“stmtsimple”中，如果没有这个节点一般就是其它类型的sql的执行计划。
join关联的节点和自身类型有关一般包含在hash，marger中，如何join同时又是where条件的话则会出现在seekkey和compare节点中，因为join的列都是成对出现，这里很容易识别，有一个是参数（@开头）或常量（type="const"）则必定是where条件。
select最终输出字段比较容易找到，第一个outputlist节点就是。
需要注意的是有因为一般列每个columnreference都包含库名，表名，列信息，但是系统表则不会。注意剔除。