SQL开发及优化

来源于公司资料

分享目标

重点了解

1. 规范、规则

2. SQL执行过程及影响

3. 一些优化手段和方式

抛砖引玉

1. 自己犯过的错

2. 案例与经验教训

3. 我们缺少什么

什么样的SQL是好SQL

1. 尽量简单，模块化

2. 易读、易维护

3. 节省资源

   内存CPU扫描的数据块要少排序

4. 不造成死锁

SQL编写注意事项

目标

别让Oracle做太多，减少资源消耗（主要IO）

设计

• 为优化器提供条件，指令明确

• 创建合适索引、分区等，高效列选择性

编码

• 合理使用索引，避免Full Table Scan，

• 化繁为简，避免复杂SQL，合理拆分，一个SQL搞定未必就好

• 在不影响业务的前提下减小事务的粒度

• 合理使用临时表

• 减少访问次数

细节上的影响

规范与规则

• 同样的SQL只写一次。

• 使用表的别名，降低解析时间。

• SELECT子句中谨慎使用 ‘ * ‘

• 减少访问数据库的次数

• 用Where子句替换HAVING子句

• 尽量多使用COMMIT

• 用>=替代>

• 用Union all代替union

• 尽量用EXISTS替代IN、用NOT EXISTS替代NOT IN

• 用EXISTS替换DISTINCT

• 少用in、Or关键词

• 避免在索引列上进行计算where col1*3>col2

• 在条件上多加引号，避免改变数据类型

• 避免在索引列上使用IS NULL和IS NOT NULL

• 使用复合索引的第一个列进行查询

• 在索引列上慎用LIKE字句where col1 like '%PM

• 在存储过程中慎用打开超过一万行数据的游标

• 优先使用INT而不是Varchar2

• 优先使用Varchar2，而不是CHAR。

• 避免不必要的排序：distinct、order by

为什么要了解SQL执行过程

• 解决或避免SQL可能出现的性能问题

• 开发过程中就以最优的方式编写SQL

• 可能一些先进的设计思想或理念

SQL执行过程

影响改善其中成本最高的过程

• 确认执行计划中消耗成本最高的过程

• 是否可以软解析（绑定变量，也不全是）

• 索引创建或引用是否合理索引创建或引用是否合理

• 统计信息是否准确

• 将常用数据或表缓存在内存中

• 是否使用分区技术（一般是在架构设计初期确定的）绑定变量1.SELECT * FROM EMP WHERE EMPNO = 123;2.SELECT * FROM EMP WHERE EMPNO = :EMP_NO;硬解析SELECT * FROM EMP;SELECT * FROM  emp;

查看执行计划的方式

1. PLSQL使用F5，TOAD CTRL+E

2. sqlplus使用SET AUTOTRACE TRACEONLY;(使用完SET AUTOTRACE off;)

3. 函数SQL>EXPLAIN PLAN FOR SELECT * FROM SCOTT.EMP; --要解析的SQL脚本 SQL>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

分析执行计划

• 由上至下，从右向左

• 由上至下：同级别，上面的优先

• 从右向左：从最右子节点开始执行

分析执行计划

Oracle索引类型

• B-tree B-树索引-又分为正常型B树和反转型B树-Oracle默认索引为B树索引-使用广泛，Update代价低，适用于业务系统-所有关系型数据库几乎都支持

• Bitmap 位图索引-多应用于数据仓库-统计类需求适用-Update代价较高

索引优缺点

• 索引优点:数量大时比较有效，量小时反而是负担加速分组排序

• 索引缺点:增删改时（创建）维护的成本随数据量增加而增加占用物理空间哪些字段适合（规律）Where条件中经常使用，可加速查询的列

• 哪些字段不适合（特别）条件中很少使用重复性非常高，比如性别分布规律差异特别大，比如成功失败大字段如blod、clob等修改几率远大于查询

索引失效

• （索引失效）并不是所有使用索引的效率都很高

• 类型不匹配的(隐式转换)如where id=123456;  => where id='123456';<>、!=is null/is not null索引列使用函数

索引创建原则

• 提高查询语句效率，降低了DML语句速度

• 在全表扫描和索引之间权衡-如果全表扫描可以接受，那么慢一些的索引访问也可以接受

• 在哪些列建立索引-Where字句中引用的列-Join中引用的列-在子表的FK上建立索引，防止对父表操作时锁住子表

• 在哪些列上不要建立索引-经常有DML操作-排它性小

• Select count(1),count(distinct col_name) from table_name

• 不建议表上的索引超过5个

• 能够用组合索引则尽量用组合索引

• 组合索引的靠左原则

• 函数索引很方便，但是要慎用

案例一、游标未关闭，遇到业务小高峰数据库挂了

环境：AIX5L，11.2.0.3 RAC，内存512*2GB（生产）

故障现象：上线后第二天下午，系统资源紧张，登录数据库就困难，看不出某个进程消耗资源很大，但部分功能访问缓慢或直接timeout

分析思路：1、检查分析数据库、OS日志、AWR报告2、确定哪项资源遇到瓶颈事后发现一个简单的查询在一个小时内执行上千万次

结论：cursor有开就得有关，一个小小的、简单的SQL就能摧毁一个系统。

案例二、应用服务器JVM释放机制导致内存泄漏

环境：RHEL5,11.2.0.3 RAC、内存128GB（压力测试）

故障现象：几轮压力测试在混合场景中2节点应用服务器显示内存溢出

分析思路：1、检查分析数据库日志、性能报告和AWR报告2、检查分析应用日志

结果：Java的sleep()方法简单调用，未关闭