SQL SERVER 的SQL语句优化方式小结

1、SQL SERVER 2005的性能工具中有SQL Server Profiler和数据库引擎优化顾问，极好的东东，必须熟练使用。

2、查询SQL语句时打开“显示估计的执行计划”，分析每个步骤的情况

3、初级做法，在CPU占用率高的时候，打开SQL Server Profiler运行，将跑下来的数据存到文件中，然后打开数据库引擎优化顾问调用那个文件进行分析，由SQL SERVER提供索引优化建议。采纳它的INDEX索引优化部分。

4、但上面的做法经常不会跑出你所需要的，在最近的优化过程中CPU占用率极高，但根本提不出我需要的优化建议，特别是有些语句是在存储过程中并且多表联立。这时就需要用中级做法来定位占用CPU高的语句。

5、还是运行SQL Server Profiler，将运行结果保存到某个库的新表中（随便起个名字系统会自己建）。让它运行一段时间，然后可以用
select top 100 * from test where textdata is not null order by duration desc
这个可以选出运行时间长的语句，在ORDER BY 中可以替换成CPU、READS，来选出CPU占用时间长和读数据过多的语句。
定位出问题的语句之后就可以具体分析了。有些语句在执行计划中很明显可以看出问题所在。
常见的有没有建索引或索引建立不合理，会出现table scan或index scan，凡是看到SCAN，就意味着会做全表或全索引扫描，这是带来的必然是读次数过多。我们期望看到的是seek或键查找。

6、怎么看SQL语句执行的计划很有讲究，初学者会过于关注里面显示的开销比例，而实际上这个有时会误导。我在实际优化过程中就被发现，一个index scan的执行项开销只占25%，另一个键查找的开销占50%，而键查找部分根本没有可优化的，SEEK谓词就是ID=XXX这个建立在主键上的查找。而仔细分析可以看到，后者CPU开销0.00015，I/O开销0.0013。而前者呢，CPU开销1.4xxxx,I/O开销也远大于后者。因此，优化重点应该放在前者。

7、如何优化单个部分，一个复杂的SQL语句，SQL SERVER会很聪明地重组WHERE后的语句，试图匹配索引。选中带优化的步骤，选择旁边的‘属性”，再选择其中的“谓词”，将其中部分复制下来，这部分就是分解后的WHERE 语句，然后在查询界面中select * from 表 where 刚才复制下来的“谓词”。这个就是需要优化的部分，既然已经走到这一步了，大部分人应该能手动建立索引了，因为这里的WHERE语句比之前的肯定简单不少。（在我项目中原始SELECT语句的WHERE部分有10个条件组合，涉及6个字段，提取出来要优化的部分就4个条件，涉及到3个字段。新的索引建立后，CPU占用率一下子就降低了，而且新建立的索引涉及的字段属于不常UPDATE的部分，频繁的读写操作不会影响UPDATE的效率）

8、以上就是优化的思路，最后提一些优化过程或是系统设计时中需要注意的问题。
A、尽量避免用select * from xxx where abc like '%xxx'类型的模糊查询，因为%在前面的话是无法利用到索引，必然会引起全量SCAN操作。应该找寻替代方式或用前置条件语句把like查找之前的行数减到最低。
B、尽量避免对大表数据进行select top n * from xxx where xxxx order by newid()的取随机记录的操作。newid()操作会读全量数据后再排序。也会占用大量CPU和读操作。可以考虑用RAND()函数来实现，这方面我还在研究中，对于整表操作比较好弄，比如id>=(select max(id) from table)*rand()。但如果取局部数据的随机记录还需要思量。
C、在SQL Server Profiler记录中会看到Audit Logout会占用大量CPU和读写等操作。查了一些资料称是某个链接在某次连接过程中执行SQL语句产生的总数，不用过于担心。看下来的确似乎这样，很多Audit Logout的CPU和IO消耗量和之前优化的语句基本一致。所以在第5点我提的SQL语句用textdata is not null条件把Audit Logout给隐去。
D、两个不同字段OR语句会导致全表扫描。例如 where m=1 or n=1。如果建立一个索引是m和n，同样会引起scan，解决方法是给m和n分别建立索引。测试12万条数据的表，索引建立错误的情况下IO开销高达 10.xxx，分别建立索引后，全部变成0.003，这个反差是非常巨大的。虽然会引起INSERT操作的性能问题，但毕竟大部分瓶颈在SELECT的读操作上。
E、索引查找（Index Seek)和索引扫描(Index Scan)，我们需要的是前者，而引起后者的原因通常是某个索引里的字段多余要查找的，例如索引建立在A和B两个字段，而我们只要查找A，则会导致 INDEX SCAN。建议针对单独的A建立索引，以形成索引查找。
F、对于小表不建议建立索引，特别是几百的数据量，只有上千上万级别的数据建立索引才有效果。

数据库优化是很深的学问，在数据库设计时就应该注意，特别是最后提到的A、B两点，尽可能在设计初期避免。

赞 (0)

相关推荐

  • SQLServer 优化SQL语句 in 和not in的替代方案

    但是用IN的SQL性能总是比较低的,从SQL执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别: SQL试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询.由此可见用IN的SQL至少多了一个转换的过程.一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了. 推荐在业务密集的SQL当中尽量不采用IN操作符 NOT IN 此操作是强列推荐不使用的,因为它不能应用表的索引.推荐用NOT EXIS

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

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

  • 浅谈MySQL中优化sql语句查询常用的30种方法

    1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描. 3.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from

  • SQL语句优化方法30例(推荐)

    1. /*+ALL_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. 例如: SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; 2. /*+FIRST_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化. 例如: SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BS

  • 如何优化SQL语句的心得浅谈

    (1)选择最有效率的表名顺序(只在基于规则的优化器中有效):Oracle的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.(2)WHERE子句中的连接顺序:Oracle采用自下而上的顺序解析WHERE子句,根据

  • MySQL SQL语句优化的10条建议

    1.将经常要用到的字段(比如经常要用这些字段来排序,或者用来做搜索),则最好将这些字段设为索引.2.字段的种类尽可能用int 或者tinyint类型.另外字段尽可能用NOT NULL.3.当然无可避免某些字段会用到text ,varchar等字符类型,最好将text字段的单独出另外一个表出来(用主键关联好)4.字段的类型,以及长度,是一个很考究开发者优化功力的一个方面.如果表数据有一定的量了,不妨用PROCEDURE ANALYSE()命令来取得字段的优化建议!(在phpmyadmin里可以在查

  • oracle下一条SQL语句的优化过程(比较详细)

    原来的语句是这样的: select sum(sl0000) from xstfxps2 where dhao00 in ( select dhao00 from xstfxps1 where trunc(ywrq00)=trunc(sysdate) and khdm00='500000003913'); 已用时间: 00: 02: 49.04 Execution Plan ---------------------------------------------------------- 0 S

  • SQL语句性能优化（续）

    SQL语句性能优化（续）

    上篇介绍了一下自己在项目中遇到的一种使用sql语句的优化方式(性能优化--SQL语句),但是说的不够完整.在对比的过程中,没有将max函数考虑在内,经人提醒之后赶紧做了一个测试,测试过程中又学到了不少的东西. 上次用的是select count(*) 和select * 的执行效率问题,因为我的需求是获取数据的一个总数来自动给出新的id,然后网友给出可以使用max的方式给出新id.其实这也是一种不错的思路(当时我们也用过该函数,只不过因为系统数据本身问题,不适合用该函数),然后我就对max函数的

  • Sql Server 索引使用情况及优化的相关Sql语句分享

    复制代码 代码如下: --Begin Index(索引) 分析优化的相关 Sql -- 返回当前数据库所有碎片率大于25%的索引 -- 运行本语句会扫描很多数据页面 -- 避免在系统负载比较高时运行 -- 避免在系统负载比较高时运行 declare @dbid int select @dbid = db_id() SELECT o.name as tablename,s.* FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL,

  • 数据库SQL语句优化总结(收藏)

    网上关于SQL优化的教程很多,但是比较杂乱.近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充. 这篇文章我花费了大量的时间查找资料.修改.排版,希望大家阅读之后,感觉好的话推荐给更多的人,让更多的人看到.纠正以及补充. 1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id f

  • 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语句（全）

    高性能的SQL语句会在软件运行中起到非常重要的作用,下面小编把最近整理的SQL语句优化资料分享给大家. 第一: 选择最有效率的表名顺序(只在基于规则的seo/' target='_blank'>优化器中有效): ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.如果有3个以上的表连接查询, 那就需要选择交叉表(intersecti

  • 常用SQL语句优化技巧总结【经典】

    本文实例总结了常用SQL语句优化技巧.分享给大家供大家参考,具体如下: 除了建立索引之外,保持良好的SQL语句编写习惯将会降低SQL性能问题发生. ①通过变量的方式来设置参数 好: stringsql = "select * from people p where p.id = ? "; 坏: stringsql = "select * from people p where p.id = "+id; 数据库的SQL文解析和执行计划会保存在缓存中,但是SQL文只要有

随机推荐