OracleSQL编写注意事项优化思路.docx

SQL编写规则loulouevaSQL编写相关注意事项在编写SQL期间，以下方面需要多加考虑：1. 杜绝仅仅以实现需求功能为出发点进行SQL编写；2. 索引不应随意添加。索引可以提高查询速度，但当对表进行增删改操作时，需要花费一定资源对索引进行维护。过多索引将会影响数据库整体性能；3. 绑定变量在使用时，尽量用于可选择性较高的WHERE条件字段。如某WHERE条件为时间类型，在对此字段进行范围查询时，用户实际查询范围从一天到一年不等。此种情况使用绑定变量后，可能造成执行计划解析不正确；4. SQL硬解析会消耗较多CPU，为减少Oracle对SQL语句硬解析，在SQL书写方面，需要注意几点：a) SQL语句必须相同，包括大小写、空格、换行等；b) SQL语句涉及对象必须相同，比如表与同义词为不同对象。c) SQL语句必须使用相同名称的绑定变量；例1：SELECT COLUMN1 FROM TAB1 WHERE COLUMN2=:1;select COLUMN1 FROM tab1 where COLUMN2=:1;以上两条SQL大小写不同，会进行两次硬解析。统一大小写，可减少硬解析。例2：SELECT COLUMN1,COLUMN2 FROM TAB1 WHERE COLUMN3=AAA;SELECT COLUMN1,COLUMN2 FROM TAB1 WHERE COLUMN3=AAa;以上两条SQL未使用绑定变量，会进行两次硬解析。可改为绑定变量方式，减少硬解析：SELECT COLUMN1,COLUMN2 FROM TAB1 WHERE COLUMN3=:1;例3：SELECT COLUMN1 FROM TAB1 WHERE COLUMN2=:A;SELECT COLUMN1 FROM TAB1 WHERE COLUMN2=:B;以上两条SQL未使用统一绑定变量名称，进行两次硬解析。可改为统一名称，减少硬解析；5. 尽量避免编写功能过于复杂、语句过于冗长的SQL，这样既增加了阅读、维护的难度，也很可能对SQL执行效率造成影响；对于业务过于复杂的功能，不必考虑只用一次SQL实现，可考虑分步查询、实现；6. 在嵌套查询中，注意避免子查询的冗余、低效写法。如某SQL通过COUNT()函数计算某子查询结果集记录数，但子查询SELECT语句内含有多余的字符串处理函数、聚合函数，或对子查询进行了order by排序等无实际意义操作。为避免干扰优化器的判断，建议清楚对最终结果无影响的SQL语句。具体规则根据讨论的情况，主要SQL编写规则如下：1. 杜绝未查看执行计划，将仅仅实现功能的SQL语句写入系统程序并发布生产；开发人员须结合Oracle执行计划辅助开发；2. 杜绝出现SELECT *语句，须将字段名称一一写明；3. 如WHERE条件中存在对某个表进行了多次子查询，须在逻辑不变的情况下，将多次子查询合并为一次子查询。示例如下：-低效写法SELECT COLUMN1,COLUMN2 FROM TAB1WHERE COLUMN3 IN (SELECT COLUMN3 FROM TAB2)AND COLUMN4 IN (SELECT COLUMN4 FROM TAB2);-替代写法SELECT COLUMN1, COLUMN2 FROM TAB1 WHERE (COLUMN3, COLUMN4) IN (SELECT COLUMN3, COLUMN4 FROM TAB2);4. GROUP BY语句中的HAVING条件，如非聚合函数，应尽量转换为等价WHERE条件；5. ORDER BY尽量以索引字段进行排序；6. 如无业务需求，须用UNION ALL替代UNION；7. 在业务逻辑等价，结果一致的前提下，用EXISTS/NOT EXISTS替代IN/NOT IN；8. 表关联查询时，尽量先进行筛选，再进行关联，从而减少关联后的整体数据量；9. 涉及索引字段的SQL编写规则：a) 对索引字段进行LIKE模糊查询时，首字母为百分号%，即类似%AB13的情况，索引无法使用。非必须时，尽量避免首字母为百分号；b) 组合索引的第一列，如未出现在WHERE条件内，例如索引IX_TEST建立时为CREATE INDEX IX_TEST ON T_TAB1(C1,C2,C3); 但查询WHERE条件只包含对C2和C3的限定，将无法使用组合索引IX_TEST。须考虑调整组合索引列顺序，或增加索引；c) 对索引字段进行NULL相关判断，如COLUMN1 IS NULL或COLUMN1 IS NOT NULL，将无法使用索引。如逻辑允许，可改写为COLUMN1或COLUMN10或其他等价写法；d) 对索引列使用函数、运算，如UPPER(COLUMN1)= :1或COLUMN1|TEST=:1或COLUMN1*100=:1，将无法使用索引。可将处理放在比较值端，即COLUMN1=UPPER(:1)或COLUMN1=:1|TEST或COLUMN1=:1/100或其它等价写法；e) 对索引字段进行限定比较时，比较值须与索引字段类型相同，如COLUMN1为数值列，COLUMN1=123132，将不会使用索引。须按正确类型进行比对，即COLUMN1=123132。f) 在对索引列进行不等值运算时，如COLUMN1:1，须尽可能转化为等价范围比较写法，即(COLUMN1:1)。g) 对索引列的大于比较，如COLUMN11，若可用等价=替代，即COLUMN1=2，须进行转换。小于比较同理。SQL优化流程为了今后更好地开展SQL定期分析、优化工作，现将优化流程进行列举和描述：1. 由数据库管理员定期提供AWR报告，对报告中耗时长、执行次数多、I/O较大的TOP SQL进行分析；2. 在分析SQL前，确认Oracle数据字典信息是否真实有效。需由数据库管理员定期检测；3. 由SQL分析人员针对单条执行效率较低的TOP SQL查看相关信息，如所检索表的记录数量、WHERE字段的DISTINCT数量、SQL编写是否符合上面提到的规则和注意事项。从而判断是否存在SQL编写错误或索引调整可能；4. 如SQL编写、索引等本身并无明显问题，需要与业务人员、开发人员进行业务优化可能性讨论；5. SQL分析人员将技术分析结果和可调整的优化建议形成文档并提交，由相关人员进行讨论，并最终决定优化方案；6. 实施优化方案前后，分别记录SQL相关执行计划信息，以便进行成效对比；总结SQL是影响数据库数据存取性能的主要因素之一。SQL的编写，不仅仅是为了实现某种功能需求。某些时候，编写