SQL语句优化

女爷i 2022-08-01 14:56 311阅读 0赞

前一段时间一直在优化系统，看了一些关于SQL语句优化的东西，在这里分享一下。

1、统一SQL语句的写法

对于以下两句SQL语句，程序员认为是相同的，数据库查询优化器认为是不同的。  
select\*from dual

select\*From dual

其实就是大小写不同，查询分析器就认为是两句不同的SQL语句，必须进行两次解析。生成2个执行计划。所以作为程序员，应该保证相同的查询语句在任何地方都一致，多一个空格都不行！  
2、使用“临时表”暂存中间结果  
简化SQL语句的重要方法就是采用临时表暂存中间结果，但是，临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在tempdb中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。  
3、OLTP系统SQL语句必须采用绑定变量  
select\*from orderheader where changetime >’2010-10-20 00:00:01’  
select\*from orderheader where changetime >’2010-09-22 00:00:01’

以上两句语句，查询优化器认为是不同的SQL语句，需要解析两次。如果采用绑定变量  
select\*from orderheader where changetime >@chgtime  
@chgtime变量可以传入任何值，这样大量的类似查询可以重用该执行计划了，这可以大大降低数据库解析SQL语句的负担。一次解析，多次重用，是提高数据库效率的原则。

4、只在必要的情况下才使用begin tran  
SQL Server中一句SQL语句默认就是一个事务，在该语句执行完成后也是默认commit的。其实，这就是begin tran的一个最小化的形式，好比在每句语句开头隐含了一个begin tran，结束时隐含了一个commit。  
有些情况下，我们需要显式声明begin tran，比如做“插、删、改”操作需要同时修改几个表，要求要么几个表都修改成功，要么都不成功。begin tran 可以起到这样的作用，它可以把若干SQL语句套在一起执行，最后再一起commit。好处是保证了数据的一致性，但任何事情都不是完美无缺的。Begin tran付出的代价是在提交之前，所有SQL语句锁住的资源都不能释放，直到commit掉。  
可见，如果Begin tran套住的SQL语句太多，那数据库的性能就糟糕了。在该大事务提交之前，必然会阻塞别的语句，造成block很多。  
Begin tran使用的原则是，在保证数据一致性的前提下，begin tran 套住的SQL语句越少越好！有些情况下可以采用触发器同步数据，不一定要用begin tran。

5、使用like进行模糊查询时应注意  
有的时候会需要进行一些模糊查询比如  
select\*from contact where username like ‘%yue%’

关键词%yue%，由于yue前面用到了“%”，因此该查询必然走全表扫描，除非必要，否则不要在关键词前加%，

6、  
• 确保TIMED\_STATISTICS在实例级设置为TRUE。  
• 确保MAX\_DUMP\_FILE\_SIZE设置为足够大的值。  
• 确定指向USER\_DUMP\_DEST的位置，并确保有足够大的值。  
• 为正在被讨论的会话开启SQL\_TRACE。  
• 运行应用程序。  
• 确定跟踪文件的位置。  
• 在步骤6所确定的跟踪文件上运行tkprof以产生跟踪输出文件。  
• 研究跟踪输出文件。  
• 优化最昂贵的SQL语句。  
• 反复运行步骤4到步骤9，直到所需的性能目标达到为止。

TIMED_STATISTICS 作用
    1、只统计wait event的等待的次数（TIMED_STATISTICS为false） 
    2、统计wait event的等待的时间和次数（TIMED_STATISTICS为true） 
    
    MAX_DUMP_FILE_SIZE参数：限制trace files及alert file大小

SQL语句的优化是在开发过程中的，那么如果发现系统运行中哪些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两点，尽可能在设计初期避免。 一般不要用如下的字句: “IS NULL”, “<>”, “!=”, “!>”, “!<”, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE ‘%500’”，因为他们不走索引全是表扫描，Select COUNT(\*)的效率教低