一个SQL性能Bug引发的思考

前两天公司某个项目组发生了生产环境的Oracle数据库崩溃的大Bug。一番分析之后最终定位到海南纽康开发人员编写的一条SQL文。这条SQL文关联了十几张表，其中一张表的记录是千万级别的。十几张表关联在一起产生了笛卡尔乘积效应，把Oracle临时表空间的十几个GB全部占满导致系统崩溃。

关系型数据库的SQL文功能很强大,可以把非常复杂的抽取逻辑写成一条SQL文，从而极大的减少后台代码量。有些不成熟的程序员以写出异常复杂的SQL文为荣，根本不考虑执行性能和易维护性。而且开发环境往往达不到实际生产环境的数据量，性能问题更容易被忽视。那么怎样避免SQL性能问题呢？

我觉得要成为一名合格的SQL开发人员必须掌握以下三个方面的知识和技能：

首先，要了解SQL在Oracle数据库中执行的过程。

1) 数据库得到SQL 执行指令后，创建cursor

2) 解析SQL 语句

a. 语法检查

b. 语义检查

c. 查询Shared Pool 看此SQL 是否曾经解析过

检查规则：字符级完全相同(区分大小写)、所引用对象必须相同、变量不同之处使用绑定变量

如果找到，转e；否则，转d

d. 硬解析(hard parse)：对比各种执行计划，

选择一个COST最小的执行计划——如果没有提示或不采用RULE模式

e. 软解析(soft parse)：取出原来的执行计划作为现在的执行计划

3) 绑定变量(Bind variable)——如果使用了绑定变量，则进行变量绑定

4) 执行：在Buffer Cache 中检查所需数据是否存在，从而决定是否进行物理I/O。数据在Cache 中采用LRU 算法管理，全表扫描时数据放在LRU列表的LRU 端。

5) 返回结果

6) 关闭cursor

其次， 通过[分析->执行->调整]的过程不断完善SQL。

1) 研究、简化业务需求，确定必需访问的表

2) 了解相关表的结构、字段类型及表之间的关系

3) 了解各个表上索引的情况

4) 了解相关字段的值的分布情况，如重复值等

5) 研究查询需要返回的大致数据量范围，比如随着业务的增长，查询效率

可能会有变化

6) 大概估计一下执行计划，估计使用哪些索引以及是否需要新的索引

7) 写出SQL 语句

8) 不断观察执行计划、调整SQL，直到和6)中的预想差不多

9) 格式化SQL 语句，使格式规范，易于阅读和软解析

最后，要规避常见的性能陷阱

1) 简化业务逻辑

2) OLTP 中使用绑定变量（批处理登录等操作中通过变量传参）

3) 书写规范、统一

4) 避免隐式数据类型转换，减少对索引列的数据类型转换

5) 减少“select * ”的使用

6) 用exists 代替in，除非in 子查询里面的记录数相对很少

7) 用not exists 代替not in，除非in 子查询里面的记录数相对很少

8) 减少distinct、trim 的使用

9) 需要返回超过表中30%记录数的时候，使用索引不会有明显的性能改善

10) 用>=( (

11) 避免在索引列上使用函数

12) 索引列尽量少采用like ‘%abc’，而用like ‘abc%’

13) 尽量用Union(如果允许，尽量用Union all)代替OR

14) 在需要使用的索引列上避免使用is null、is not null、

15) 复合索引中，尽量使用前导列(索引第一列)

16) 使用索引的时候，考虑使用ROWID