SQL语句二三事

SQL语句二三事
ORACLE数据库的性能报告。这个报告非常的详细，有I/O读取性能，CPU占用率等参数，不过我们最关心的是‘SQL ordered by Elapsed Time’。
这个报告中有三个值是比较关键：
 Elapsed Time(S): SQL语句执行用总时长，此排序就是按照这个字段进行的。注意该时间不是单个SQL跑的时间，而是监控范围内SQL执行次数的总和时间。单位时间为秒。
 Executions: SQL语句在监控范围内的执行次数总计。
 Elap per Exec(s): 执行一次SQL的平均时间。单位时间为秒。
报告按照Elapsed Time进行排序，排到前面的就是执行最频繁，最占执行时间的SQL。这里的SQL如果能够进行优化，相信会对系统性能有不少的提高。
SQL性能优化有一些基本的guideline提供给大家，方便大家来检查SQL的问题。
1、减少‘SELECT *’的使用 在写SELECT语句时，通过增加where条件来最小化返回结果集，从而减轻I/O开销。并且，在查询时尽量减少通配符‘select *’的使用。
如SELECT * FROM T1语句，要用到几列就选择几列。如：SELECT COL1,COL2 FROM T1；
2、避免使用不兼容的数据类型
数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作 例如float和int、char和varchar、binary和varbinary是不兼容的。不兼容的数据类型会导致无法正确的使用到index.
在工作中碰到过一个例子，读取GOALD中的一个保单:
82
SELECT pol_office_cd, policy_no, eff_dt_seq_no, renewal_no FROM tpolicy WHERE policy_no = ‘EM458000001’ And eff_dt_seq_no = ‘2013010100’
由于程序员对GOALD的字段定义不熟悉，eff_dt_seq_no实际定义为int型，而这个SQL传入的值是char型的，导致建立在policy_no和eff_dt_seq_no上的复合索引失效，降低了整个SQL的查询性能。
修改SQL后正确使用到了INDEX，提高了SQL性能。修改后的SQL如下: SELECT pol_office_cd, policy_no, eff_dt_seq_no, renewal_no FROM tpolicy WHERE policy_no = ‘EM458000001’ And eff_dt_seq_no = 2013010100
3、避免在WHERE子句中对字段进行函数或表达式操作
这是因为对查询字段使用函数或表达式的操作，将导致查询引擎放弃使用索引而进行全表扫描。 例1： SELECT * FROM T1 WHERE F1/2=100 应改为: SELECT * FROM T1 WHERE F1=100*2 例2：SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’ 应改为: SELECT * FROM RECORD WHERE CARD_NO LIKE ‘5378%’
总的原则是对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。
4、避免使用!=或＜＞、IS NULL或IS NOT NULL等操作符
这些操作符会使系统无法使用索引，会全表扫描表里的所有数据，导致查询性能的降低。 例1： SELECT id FROM employee WHERE id != 100 应改为: select d from employee where id < 100 or id > 100
使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。因此建议开发人员在建表时，把需要索引的列设成NOT NULL。
5、尽量避免使用distinct、order by、group by等关键字
在SQL语句中使用distinct, order by, group by等关键字，会让查询器将查询结果放到临时表中，并对临时表中数据进行distinct, order 或者group 操作。如果查询结果较大，会显著降低SQL语句的性能。
如本文开头的例子：
83
select distinct tclmpolicy0_.GS_COVERAGE_TYPE as col_0_0_ from T_CLM_POLICY_CR tclmpolicy0_ where (tclmpolicy0_.GS_COVERAGE_TYPE is not null) and tclmpolicy0_.COVERAGE_CODE=:1
这里的T_CLM_POLICY_CR是理赔CT级别的业务表，系统上线最初的一段时间，里面数据寥寥无几，这段SQL对系统性能没有影响。随着系统中理赔数量的增加，该SQL会越来越慢。并且，这段SQL在系统中执行的次数非常频繁，因此对系统的性能影响非常大。
这次调优将这句SQL改为一次装载后驻留内存中，避免频繁调用，从而提高系统性能。
6、尽量避免使用IN/NOT IN子句
由于IN语句可以用or或者union语句来替代，替代后正确使用索引，提高了SQL性能。 例子：select count(*) from stuff where id_no in('0','1') 应改为： select count(*) from stuff where id_no = ‘0’ or ‘1’
在笔者工作中碰到过一次使用IN语句导致系统down机的灾难性事故。事情是这样的，AIG China的eMarine系统由我们项目组接手维护半年后出现一个比较大的问题。 eMarine系统每周会出现一两次系统异常缓慢，必须应用程序重启才能继续使用。在系统缓慢的时候，检查日志发现数据库出现procedure cache 不足的问题。
“There is not enough procedure cache to run this procedure, trigger, or SQL batch. Retry later, or ask your SA to reconfigure ASE with more procedure cache.”
通常意义上的procedure cache是存放存储过程或触发器的执行计划的，系统上线后存储过程/触发器数量变化不大，因此对procedure cache size的值设定后不大变动。但是，SYBASE的procedure cache还用来存放dynamic SQL的执行分析计划，就是信息中提到的SQL batch.
应用程序中有一条SQL是根据wip_no来动态生成的，例如SQL1： Select policy_no, wip_no, pol_office_no, eff_dt_seq_no, renwal_no From tpolicy Where wip_no in (‘1111’, ‘222’, ‘333’, ‘444’)
这里的in语句中的值是下面这条语句的返回结果,例如SQL2： select wip_no from tpolicyw where 1=1 and trans_type_cd = ‘N’ and timestamp between ‘2013/01/01’ and ‘2013/02/01’
SQL2语句里的查询条件亦是根据用户选择的参数动态生成SQL的。比如，用户传入了trans_type_cd为’N’的值，则where 条件中添加trans_type_cd = ‘N’; 如果用户选择
84
timestamp在’2013/01/01/’和’2013/02/01’的范围，则在where 条件中添加timestamp between ‘2013/01/01’ and ‘2013/02/01’。
如果用户不选择任何过滤条件，则会选择出tpolicyw中的所有wip_no。在故障发生的时候， tpolicyw中的记录数上了10万条。
如果用户在做查询业务的时候不输入参数， SQL2会变成以下写法，查询出超过10万条wip_no： select wip_no from tpolicyw where 1=1
因此，在拼凑第一条SQL的时候，出现了一条巨大无比的动态语句： Select policy_no, wip_no, pol_office_no, eff_dt_seq_no, renwal_no From tpolicy Where wip_no in (10万多个wip_no)
这句SQL提交到数据库运行时，消耗掉了数据库所有的procedure cache size，导致数据库响应变慢，引起这次系统的down机事故。在进行动态SQL生成的时候，IN语句很适合操作批量数据，不过，开发人员在使用IN语句的时候一定要考虑数据可能的记录数，否则就会在系统上线后产生不可知的性能问题。