SQL优化

SQL语句检查规则

sql server数据库

1、检查sql语句执行结果所消耗的服务器io及cpu情况；

• 设置监控参数为打开状态：
  set statistics io on
  set statistics time on
  然后运行sql语句，并检查监控的参数；

2、使用“显示执行计划”检查sql语句对索引的使用；

mysql数据库

• 使用explain + sql语句，检查sql语句的执行效率；

sql语句执行效率规范

1、最终目的：以尽可能少的占用数据库服务器资源为最终目标；

2、单条sql语句执行效率详细目标如下：

• （1）后台应用：
  cpu消耗小于3000毫秒；
  io消耗小于10000次io；
  执行频率：超过5分钟执行1次；
• （2）前台应用：
  A类：
  cpu消耗小于1000毫秒；
  io消耗小于5000次io；
  执行频率：超过30秒钟执行1次；
  B类：
  cpu消耗小于100毫秒；
  io消耗小于1000次io；
  执行频率：30秒钟内执行多次；

SQL效率提升

• 应尽量避免在 where 子句中使用!=或<>或 NOT IN操作符，否则将引擎放弃使用索引而进行全表扫描。
• 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
• 用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤。
• 在 GROUP BY 子句和 ORDER BY 子句中使用索引。GROUP BY 子句和 ORDER BY 子句一般都会进行排序，以对行进行排列和替换，不过如果指定带有索引的列作为这两者的参数列，由于用到了索引，可以实现高速查询，由于索引是有序的，排序本身都会被省略掉
• 减少中间表，在 SQL 中，子查询的结果会产生一张新表，不过如果不加限制大量使用中间表的话，会带来两个问题，一是展示数据需要消耗内存资源，二是原始表中的索引不容易用到，所以尽量减少中间表也可以提升性能。
• 尽可能少的在存储过程中拼接SQL语句再执行，宁可多写IF判断或拆分存储过程
• IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况
• 如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。
• 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。
• 使用CTE：可以简化嵌套SQL，免除创建临时表的麻烦，配合ROW_NUMBER()分页可以在数据量较大的时候提高分页速度(非常明显).还可以让很复杂的语句看起来清晰明了，符合思维习惯，而且支持递归.总之我能想到的都是CTE的优点，除了在数据量小的时候使用CTE分页速度赶不上TOP MAX外(当然在数据量少的时候慢那几毫秒是可以接受的，万一那天表数据量多了呢)
• 如果有复杂的字符串处理(如正则匹配)可以考虑使用CLR函数
• 尽量少用视图，它的效率低。对视图操作比直接对表操作慢
• where条件中尽量减少使用常量比较，建议使用变量
• 大量的排序操作会影响系统性能，所以尽量减少order by和group by排序操作。
• 拆分表：分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。