SqlServer 索引自动优化工具

鉴于人手严重不足（当时算两个半人的资源），打消了逐个库手动去改的念头。当前的程序结构不允许搞革命的做法，只能搞搞改良，所以准备搞个自动化工具去处理。原型刚开发完，开会的时候以拿出来就遭到运维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：http://schemas.microsoft.com/sqlserver/2004/07/showplan/sql2008/showplanxml.xsd
　
　总结的节点映射列举如下：

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

　　　　Join关联的节点和自身类型有关一般包含在Hash，Marger中，如何Join同时又是Where条件的话则会出现在SeekKey和Compare节点中，因为Join的列都是成对出现，这里很容易识别，有一个是参数（@开头）或常量（type="Const"）则必定是Where条件。
　　　　
　　　　Select最终输出字段比较容易找到，第一个OutputList节点就是。

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

赞 (0)

相关推荐

  • sql语句优化之SQL Server(详细整理)

    MS SQL Server查询优化方法 查询速度慢的原因很多,常见如下几种 1.没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷) 2.I/O吞吐量小,形成了瓶颈效应. 3.没有创建计算列导致查询不优化. 4.内存不足 5.网络速度慢 6.查询出的数据量过大(可以采用多次查询,其他的方法降低数据量) 7.锁或者死锁(这也是查询慢最常见的问题,是程序设计的缺陷) 8.sp_lock,sp_who,活动的用户查看,原因是读写竞争资源. 9.返回了不必要的行和列 10.查询语句不好,

  • 优化 SQL Server 索引的小技巧

    在本文中,我将说明如何用SQL Server的工具来优化数据库索引的使用,本文还涉及到有关索引的一般性知识. 关于索引的常识 影响到数据库性能的最大因素就是索引.由于该问题的复杂性,我只可能简单的谈谈这个问题,不过关于这方面的问题,目前有好几本不错的书籍可供你参阅.我在这里只讨论两种SQL Server索引,即clustered索引和nonclustered索引.当考察建立什么类型的索引时,你应当考虑数据类型和保存这些数据的column.同样,你也必须考虑数据库可能用到的查询类型以及使用的最为频

  • SQL Server优化50法汇总

    查询速度慢的原因很多,常见如下几种:1.没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷)2.I/O吞吐量小,形成了瓶颈效应.3.没有创建计算列导致查询不优化.4.内存不足5.网络速度慢6.查询出的数据量过大(可以采用多次查询,其他的方法降低数据量)7.锁或者死锁(这也是查询慢最常见的问题,是程序设计的缺陷)8.sp_lock, sp_who, 活动的用户查看,原因是读写竞争资源.9.返回了不必要的行和列 10.查询语句不好,没有优化可以通过如下方法来优化查询 :1.把数据.日

  • SQL Server并行操作优化避免并行操作被抑制而影响SQL的执行效率

    SQL Server并行操作优化避免并行操作被抑制而影响SQL的执行效率

    为什么我也要说SQL Server的并行: 这几天园子里写关于SQL Server并行的文章很多,不管怎么样,都让人对并行操作有了更深刻的认识. 我想说的是:尽管并行操作可能(并不是一定)存在这样或者那样的问题,但是我们不能否认并行,仍然要利用好并行. 但是,实际开发中,某些SQL语句的写法会导致用不到并行,从而影响到SQL的执行效率 所以,本文要表达的是:我们要利用好并行,不要让一些SQL的写法问题"抑制"了并行,让我们享受不了并行带来的快感 关于SQL Server的并行: 所谓的

  • SQL Server中的SQL语句优化与效率问题

    很多人不知道SQL语句在SQL SERVER中是如何执行的,他们担心自己所写的SQL语句会被SQL SERVER误解.比如: select * from table1 where name='zhangsan' and tID > 10000 和执行: select * from table1 where tID > 10000 and name='zhangsan' 一些人不知道以上两条语句的执行效率是否一样,因为如果简单的从语句先后上看,这两个语句的确是不一样,如果tID是一个聚合索引,那

  • 人工智能自动sql优化工具--SQLTuning for SQL Server

    人工智能自动sql优化工具--SQLTuning for SQL Server

    针对这种情况,人工智能自动SQL优化工具应运而生.现在我就向大家介绍这样一款工具:SQLTuning for SQL Server. 1. SQL Tuning 简介 SQL Turning是Quest公司出品的Quest Central软件中的一个工具. QuestCentral(图1)是一款集成化.图形化.跨平台的数据库管理解决方案,可以同时管理Oracle.DB2 和 SQL server 数据库.它包含了如下的多个工具: 数据库管理(DBA)  数据库监控(Monitoring Pack

  • SQL Server游标的使用/关闭/释放/优化小结

    SQL Server游标的使用/关闭/释放/优化小结

    游标是邪恶的! 在关系数据库中,我们对于查询的思考是面向集合的.而游标打破了这一规则,游标使得我们思考方式变为逐行进行.对于类C的开发人员来着,这样的思考方式会更加舒服. 正常面向集合的思维方式是: 而对于游标来说: 这也是为什么游标是邪恶的,它会使开发人员变懒,懒得去想用面向集合的查询方式实现某些功能. 同样的,在性能上,游标会吃更多的内存,减少可用的并发,占用宽带,锁定资源,当然还有更多的代码量-- 从游标对数据库的读取方式来说,不难看出游标为什么占用更多的资源,打个比方: 当你从ATM取钱

  • SqlServer 索引自动优化工具

    鉴于人手严重不足(当时算两个半人的资源),打消了逐个库手动去改的念头.当前的程序结构不允许搞革命的做法,只能搞搞改良,所以准备搞个自动化工具去处理.原型刚开发完,开会的时候以拿出来就遭到运维DBA团队强烈抵制,具体原因不详.最后无限延期.这里把思路分享下.欢迎拍砖. 整个思路是这样的,索引都是为查询和更新服务的,但是不合适的索引又会对插入和更新带来负面影响.面对表上现有的索引想识别那些是有效的不太可能.那么根据现有的数据使用情况重建所有的新索引不就解决了嘛.根据查询生成全新索引,然后和现有对比,

  • java9的JShell小工具和编译器两种自动优化方法

    java9的JShell小工具和编译器两种自动优化方法

    一.按顺序逐步执行的脚本程序: 二.编译器自动优化 1.不超数据类型范围编译器自动添加强转操作: 2.一但发生运算,byte/short/char都会自动提升为Int,当只有常量参与运算时,编译器会先斩后奏,将常量运算结果再进行赋值,因为常量不会变: 以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持我们.

  • Android APK优化工具Zipalign详解

    Android SDK中包含了一个用于优化APK的新工具zipalign.它提高了优化后的Applications与Android系统的交互效率(俗话:"要致富先修路",Android小组重新为Applications与Android系统之间搭建了一条高速公路),从而可以使整个系统的运行速度有了较大的提升.Android小组强烈建议开发者在发布新Apps之前使用zipalign优化工具,而且对于已经发布但不受限于系统版本的Apps,建议用优化后的APK替换现有的版本. 在下面的内容中将

  • 理解MySQL——索引与优化总结

    理解MySQL——索引与优化总结

    写在前面:索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点.考虑如下情况,假设数据库中一个表有10^6条记录,DBMS的页面大小为4K,并存储100条记录.如果没有索引,查询将对整个表进行扫描,最坏的情况下,如果所有数据页都不在内存,需要读取10^4个页面,如果这10^4个页面在磁盘上随机分布,需要进行10^4次I/O,假设磁盘每次I/O时间为10ms(忽略数据传输时间),则总共需要100s(但实际上要好很多很多).如果对之建立B-Tree索引,则只需要进行log100(

  • Android性能优化及性能优化工具

    Android性能优化及性能优化工具

    目录 1.Allaction Tracking (1)追踪 (2)分类我们的内存分配 (3)查看统计图 2.LeakCanary (1)配置 (2)制造一个单例内存泄漏的点 (3)LeakCanary 发出内存泄漏通知 (4)LeakCanary 分析 3.Lint分析工具  性能优化的帮助工具: MAT, Memory Monitor(属于AndroidMonitor中一个模块), HeapTool(查看堆信息), Allaction Tracking, LeakCanary Lint工具 1

  • Java实现的properties文件动态修改并自动保存工具类

    本文实例讲述了Java实现的properties文件动态修改并自动保存工具类.分享给大家供大家参考,具体如下: 一.概述 利用commons-configuration读取配置文件,并实现对配置文件的动态修改和自动保存. Apache Common-Configuration工具可以从 Properties文件,XML文件,JNDI,JDBC数据源,System Properties,Applet parameters,Servlet Parameters等读取相应信息 使用步骤 前提,引入co

  • Android自动测试工具Monkey

    前言: 最近开始研究Android自动化测试方法,对其中的一些工具.方法和框架做了一些简单的整理,其中包括android测试框架.CTS.Monkey.Monkeyrunner.benchmark.其它test tool等等.因接触时间很短,很多地方有不足之处,希望能和大家多多交流. 一.Monkey定义 探索软件测试工具有哪些,本文主要介绍Monkey工具.Monkey测试是Android平台自动化测试的一种手段,通过Monkey程序模拟用户触摸屏幕.滑动.按键等操作来对设备上的程序进行压力测

  • sqlserver索引的原理及索引建立的注意事项小结

    sqlserver索引的原理及索引建立的注意事项小结

    聚集索引,数据实际上是按顺序存储的,数据页就在索引页上.就好像参考手册将所有主题按顺序编排一样.一旦找到了所要搜索的数据,就完成了这次搜索,对于非聚集索引,索引是安全独立于数据本身结构的,在索引中找到了寻找的数据,然后通过指针定位到实际的数据. SQL Server中的索引使用标准的B-树来存储他们的信息,如下图所示,B-树通过查找索引中的一个关键之来提供对于数据的快速访问,B-树以相似的键记录聚合在一起,B不代表二叉(binary),而是代表balanced(平衡的),而B-树的一个核心作用就

  • Mongodb索引的优化

    MongoDB 是一个基于分布式文件存储的数据库.由 C++ 语言编写.旨在为 WEB 应用提供可扩展的高性能数据存储解决方案.MongoDB索引几乎和关系型数据库的索引一样.MongoDB的查询优化器能够使用这种数据结构来快速的对集合(collection)中的文档(collection)进行寻找和排序.准确来说,这些索引是通过B-Tree索引来实现的.在命令行中,可以通过调用ensureIndex()函数来建立索引,该函数指定一个到多个需要索引的字段,下面介绍mongodb索引如何优化 一.

随机推荐