优化SQL Server数据库的经验总结
优化SQLServer数据库的经验总结
下面主要向大家介绍的是正确优化SQLServer数据库的经验总结，其中包括在对其进行优化的实际操作中值得大家注意的地方描述，以及对SQL语句进行优化的最基本原则，以下就是文章的主要内容描述。优化数据库的注意事项：1、关键字段建立索引。
2、使用存储过程，它使SQL变得更加灵活和高效。3、备份数据库和清除垃圾数据。
4、SQL语句语法的优化。(可以用Sybase的SQLExpert，可惜我没找到unexpired的序列号)
5、清理删除日志。
SQL语句优化的基本原则：1、使用索引来更快地遍历表。
缺省情况下建立的索引是非群集索引，但有时它并不是最佳的。在非群集索引下，数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说：
①.有大量重复值、且经常有范围查询(between,>,<，>=,<=)和orderby、groupby发生的列，可考虑建立群集索引
②.经常同时存取多列，且每列都含有重复值可考虑建立组合索引;
③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。2、ISNULL与ISNOTNULL
不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用isnull或isnotnull的语句优化器是不允许使用索引的。3、IN和EXISTS
EXISTS要远比IN的效率高。里面关系到fulltablescan和rangescan。几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。4、在海量查询时尽量少用格式转换。5、当在SQLSERVER201*中
如果存储过程只有一个参数，并且是OUTPUT类型的，必须在调用这个存储过程的时候给这个参数一个初始的值，否则会出现调用错误。6、ORDERBY和GROPUBY
使用ORDERBY和GROUPBY短语，任何一种索引都有助于SELECT的性能提高。注意如果索引列里面有NULL值，Optimizer将无法优化。
7、任何对列的操作都将导致表扫描，它包括SQLServer数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。
8、IN、OR子句常会使用工作表，使索引失效。如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引。
9、SETSHOWPLAN_ALL>10、谨慎使用游标在某些必须使用游标的场合，可考虑将符合条件的数据行转入临时表中，再对临时表定义游标进行操作，这样可使性能得到明显提高。
注释：所谓的优化就是WHERE子句利用了索引，不可优化即发生了表扫描或额外开销。经验显示，SQLServer数据库性能的最大改进得益于逻辑的数据库设计、索引设计和查询设计方面。反过来说，最大的性能问题常常是由其中这些相同方面中的不足引起的。其实SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及SQLServer数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。
扩展阅读：SQL Server 201* 一千万条以上记录分页数据库优化经验总结
SQLServer201*一千万条以上记录分页数据库优化经验总结
对普通开发人员来说经常能接触到上千万条数据优化的机会也不是很多，这里还是要感谢公司提供了这样的一个环境，而且公司让我来做优化工作。当数据库中的记录不超过10万条时，很难分辨出开发人员的水平有多高，当数据库中的记录条数超过1000万条后，还是蛮能考验开发人员的综合技术能力。当然不是每个公司都能请得起专业的DBA，话又说过来专业的DBA也未必能来我们公司长期工作，这就不只是薪资待遇问题了还会涉及到人家的长期发展规划了，当然我也不是专业的DBA，本着能把问题解决好就是好猫的理念。
我们先看图，数据库中的记录数如下：记录数为10581490条同时还需要从另外一个表读取7万多条数据。
页面运行效果如下：这是查看某个单位的数据，每页显示16条、记录数1087292条、分页数为67956页。
遇到的难题如下：
1：当客户用了几年后数据变得很庞大分页速度缓慢得要命几乎到了无法忍受的程度。
2：分页到最后一页时往往速度很慢会有死机现象出现，特别是记录条数很多时死机现象比较多。
那再讲讲，解决问题的方法步骤：
1：首先优化数据库、因为程序也很复杂一时也看不过来也不敢乱改，先从数据库字段类型优化开始入手会好很多。
先把数据库里的datetime都修改为smalldatetime，数据库变小了几百M很有成就感，最起码磁盘的读取压力减少不少吧。由于数据库数据有上千万条，无法用管理工具修改结构，只能用新建查询执行SQL命令才可以。
会有如下超时现象会发生。
那我们只能用执行查询的方式对表结构进行调整了，每次执行一个SQL指令大概需要10分钟时间才能顺利执行好，数据量实在是太大了。
2：接着再优化，数据库索引，原先的索引很乱可以理解为是乱来的所以我全部干掉重新进行了组织。把多余的索引先通通干掉，然后重新建立索引，因为记录数太庞大了，有多余的索引会使数据库变大很庞大，给他先减轻减轻体重。
把主键设置为倒序的、非聚集的，这样的好处是可以把最新的数据排序在最前面。
把主要查询的条件设置为索引，GroupBy的放第一个位置然后设置为聚集索引，这样的好处时查询时会快很多很多，普通所以没这个效率高，数据实在是太庞大了，超过了1000万条数据后，对比一下还是很明显的，都能感觉得到。
完成以上2个步骤后分页速度快了很多最起码没死机现象了，还有一点遗憾是当数据量大时最后一页的分页速度还是有些慢，有些难以忍受的感觉，但是最起码不会死机了。
3：接着重点优化，数据库分页的存储过程，最后一页难以忍受的问题先解决一下。分页是用了SELECTTOPN的反转的方式，我把最后一页到底获取多少条记录准确数字计算出来，适当的修改了一下最后一页慢得死去活来的问题，得到了适当的环节，虽然没能彻底解决也速度明显快了一些，由于写的这个分页程序也有些复杂，我也不敢乱动，就把问题解决好就完事大吉的目的了，不去惹更多的麻烦了。
4：对比一下数据库结构优化后的前后如下图索引优化前索引占用空间2706.109M
索引优化后索引占用空间520.805M
我想就这么一个1000w条记录的表光索引就优化了2200M空间，就单单这个也提高不少性能了。
5：接着重点优化，程序代码部分了，其实代码优化是在索引优化之前的，因为先读懂了代码、读懂了业务逻辑才好优化索引，这边文章写着写着顺序有些颠倒了，大家心里有数就可以了，我还是按照我的思路继续写吧。
在上图的企业编号、企业名称等，在程序里都进行了LIKE处理，当数据库记录超过1000万条时，对字符进行Like操作，那真是会要命的，毕竟那么多数据都进行一次匹配，虽然电脑的运算速度很快，但是上千万条记录，这么被计算过一下，能快到哪里去啊？改进方法：
A:输入企业编号、企业名称修改为模糊查询，能明确定位一个药店的名称。
B:若已经获得企业编号了，不再匹配企业名称，而且企业编号用=来判断，并把企业编号进行索引。
海量数据库分页优化总结：
折腾了接近1周左右，终于把这个1千多万条记录的数据表给优化好了，难题也解决好了虽然不太科学也不专业也缺少理论依据、试验数据、图表对比、性能调试工具等等，但是还好把问题都解决好了，老鼠抓到了就是好猫咪了哈哈。数据库进行了彻底的翻天覆地的优化、程序代码也进行了彻底的翻天覆地的优化后，分页速度飞快了。每页显示16条、记录数1087292条、分页数为67956页，每页分页速度都完全在3秒内，最后一页也不会死机了，也蛮快的足够可以忍受了。
等有空时，再把最后一页分页速度慢的问题再深入解决一下，先不去惹麻烦了稍微休息一下再说。
优化的每个动作需要10分钟左右才会执行好，若做错一次基本上就代表半个小时白忙乎了，还需要删除掉，再重新执行修正过的SQL语句，所以一天下来优化的成果并不会非常明显、需要几天时间才能优化好。
将权限管理、工作流管理做到我能力的极致，一个人只能做好那么很少的几件事情。
友情提示：本文中关于《优化SQL Server数据库的经验总结》给出的范例仅供您参考拓展思维使用，优化SQL Server数据库的经验总结：该篇文章建议您自主创作。
来源：网络整理 免责声明：本文仅限学习分享，如产生版权问题，请联系我们及时删除。
《优化SQL Server数据库的经验总结》
由互联网用户整理提供,转载分享请保留原作者信息,谢谢!
http://m.bsmz.net/gongwen/672274.html
- 上一篇：SQL实训报告
- 下一篇：SQL数据库知识总结