SQL语句编写与优化规范

SQL语句编写与优化规范

1用SELECT查询用具体字段代替“*”，且尽可能只查询需要的字段。

2多表查询时，使用表的别名，就可以减少解析的时间并减少那些由列名歧义引起的语法错误

3用EXISTS替代IN，用NOT EXISTS替代NOT IN

4WHERE条件连接顺序，把表关系写在最前

例如Oracle采用自下而上的顺序解析WHERE子句，表之间的条件连接必须写在其他条件之前，把可以过滤掉大量数据的条件写在WHERE子句的最后，按照过滤记录数量的多少

5删除全表时，用TRUNCATE替代DELETE

当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.

当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短。但只有在删除全．

表数据

．．．时才这样使用。

6尽可能多的使用commit

对于执行insert，update，delete语句时尽量多commit，因为系统性能会因commit释放的资源而大大提高。注意事务的处理，因为commit的数据是不允许回滚的。

7优化GROUP BY

为提高GROUP BY的效率，可以将不需要的数据在GROUP BY之前过滤掉，减少由于数据量大而带来的性能损耗。同时避免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序、统计等操作。如果能通过WHERE子句限制记

8ORDER BY字段需建立索引

ORDER BY语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写ORDER BY语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。

9有条件的使用union-all 替代union

这样做效率会提高3到5倍。

10IS NULL 与IS NOT NULL（索引失效）

不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。

任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

11对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。（索引失效）

12避免使用带通配符的LIKE查询（索引失效）

之前我认为只要含有“%”的LIKE查询索引就会失效，经过网络资料查询，说只有“%”

13注意or条件查询，两边条件必须均建立索引才会生效（索引失效）

14避免在索引列上使用函数或计算，如果索引是函数的一部分，则优化器不会使用索引。（索引失效）

15避免使用not、!=、<>（索引失效）

索引只能告诉什么存在于表中，不能告诉我们什么不存在，当数据库遇到not、!=、<>时，索引会失效而去进行全表扫描。用“>=”代替“>”

总结

原则上，应该尽可能的减少与数据库的交互，但不意味着要写一个庞大复杂的SQL来获取所有需要的数据。对于复杂或访问量频繁的功能，可以考虑借助缓存来提升性能。对于表数据量过于庞大而且持续增长，考虑归档历史，分开查询；如果仍然无法提升性能，则可以从增加硬件来改善，如读写分离、数据库集群等方案。

在拼写SQL时，尽可能的将SQL拆解为简单易读的SQL，对于复杂逻辑可以借助程序来协同完成，一方面执行效率不一定低，另一方面也给未来的运维、修改带来便捷。如果设计原因导致关联表略多，考虑视图、拆解、辅助表的方式来简化查询，降低SQL复杂度，减少表关联查询的数量（少于5表），且尽可能少用子查询，视图嵌套不要超过2层。