开发常见数据库问题总结及分析

1、一切从表结构开始一切从表结构开始一切从表结构开始l绑定变量问题l索引使用问题lRownum使用问题lSequence使用问题lOralce基本原理的理解问题 一切从表结构开始绑定变量问题l程序未采用预编译语句导致未绑定变量l动态sql语句未实现绑定变量 一切从表结构开始索引的使用问题l 类型隐式转换导致索引失效 *程序中变量(参数)类型跟表中定义不一致导致隐式转换 *存储过程或者sql语句中参数,变量定义跟表不一致导致隐式转换 *同一意义的字段在各表中类型不一致但需要进行连接操作产生隐式转换设计问题(areacode字段在个邮不同表中的数据类型)l 索引列使用函数导致索引失效l 查询条件上无索

2、引导致索引失效严重的设计问题l 组合索引前导列未在查询条件中导致索引失效或者skip index range scan导致性能低下 一切从表结构开始rownum使用问题l Rownum使用跟排序同一层 select * from tab where rownum=5 OR ( sysdate - LastDealTime 10/(24*6*5) AND Status=1 ) OR status =-100 ; DELETE MMS_UserSend_Info WHERE Sendcount =5 OR ( sysdate - LastDealTime 10/(24*6*5) AND Statu

3、s=1 ) OR status =-100 ; 一切从表结构开始常见问题分析绑定变量问题l绑定变量 oracle判断一条sql语句是否相同仅以sql文本是否相同来判断，故文本不一致的sql语句，即需要进行解释生成不同的执行计划。故会造成文本差异的情况即会导致硬解析。会导致文本不一致的情况如大小写，缩进，未绑定变量等，其中又以未绑定变量的影响最大，且其影响是跟业务量成正比的，如果一个业务系统完全没绑定变量但又没有造成严重问题其实也是种悲剧悲剧(设置设置cursor_sharing =similar的情况例外，但此种设置极易导致bug从而引发重大问题） 。 解决：书写格式化一致（确定全部采用大写或

4、者小写，相同的缩进，空格统一，统一的换行标准等） 实现绑定变量l不绑定变量会有什么问题 性能问题性能问题 硬解析(代码演示) 索引失效(代码演示) 安全问题安全问题(代码演示) 容易sql注入-早期139邮箱未绑定变量被注入案例(admin所有密码被修改，photoshow站点被攻击） 检查检查sql执行计划干扰执行计划干扰一切从表结构开始常见问题分析绑定变量问题l 如何实现绑定变量 程序采用预编译 动态sql语句采用占位符(数据类型严格一致）l 如何知道是否实现了绑定变量 检查共享池(v$sqlarea)中sql语句 一切从表结构开始常见问题分析索引使用问题l 常见索引失效情况 类型隐式转换

5、 索引列上使用函数 索引列参与运算 查询条件未包括索引前导列(可能失效亦可能skip index range scan) 查询同一张表中同一个字段的最大最小值使用一条sql语句实现 一切从表结构开始常见问题分析索引使用问题l 如何避免索引失效 类型隐式转换的规避类型隐式转换的规避 总原则：一切从表结构开始 程序定义变量数据类型与表一致程序定义变量数据类型与表一致 存储过程，函数等参数，变量定义跟表一致存储过程，函数等参数，变量定义跟表一致（采用取表字段数据类 型的方式可以有效的规避数据类型不一致及将来表数据类型修改时避免存储过程不匹配） 表设计相同意义的字段在不同表中数据类型统一表设计相同意义

6、的字段在不同表中数据类型统一 查询列上使用函数的规避查询列上使用函数的规避 根据业务等价性改写语句尽量不需要使用函数根据业务等价性改写语句尽量不需要使用函数 对于无法避免一定要使用函数的咨询对于无法避免一定要使用函数的咨询DBA是否可以创建函数索引是否可以创建函数索引 一切从表结构开始常见问题分析索引使用问题l 如何避免索引失效 索引列参与运算索引列参与运算 根据业务等价性改写语句将运算转至条件（变量）根据业务等价性改写语句将运算转至条件（变量） select * from mail_notify_info where modifytime+ 1 / 24 sysdate = select *

7、 from mail_notify_info where modifytime sysdate -1/24 注：即使modifytime列上没有索引也应该这样改写，为什么？ 查询条件未包括索引前导列查询条件未包括索引前导列 根据业务等价性改写根据业务等价性改写sql语句至使用上索引前导列语句至使用上索引前导列 此种情况需要从源头规避，即从表设计即对于会成为查询条件或者参与运算的列保证为not null类型，从而规避这种查询需求。 一切从表结构开始常见问题分析索引使用问题l 如何知道索引失效 执行计划是检查索引是否使用的唯一标准-v$sql_plan(OPTION=FULL) 索引监控定位当前索

8、引的使用情况 l 索引使用-参见培训文档“索引.ppt” 一切从表结构开始常见问题分析rownum使用问题l详见培训文档”从Rowid&Rownum开始.ppt”l一个例子，请大家思考一下结果如何 select * from userinfo where rownum=10 order by salary desc; select * from (select * from userinfo order by salary desc) where rownum create table userinfo(userid number,username varchar2(50),salar

9、y int);SQL insert into userinfo(userid,username,salary) select rownum as userid,张|rownum as username,1000+rownum as salary from dba_objects where rownum insert into userinfo(userid,username,salary) select rownum+100 as userid,李|rownum as username,1500+rownum as salary from dba_objects where rownum i

10、nsert into userinfo(userid,username,salary) select rownum+200 as userid,李|rownum as username,1300+rownum as salary from dba_objects where rownum commit; (1) 创建索引前写出结果集SQL create index ix_userinfo$salary on userinfo(salary);（2）创建索引后写出结果集 SQL alter table userinfo modify salary not null; (3) 修改salary字段

11、约束后写出结果集 -如果能够毫无疑问的写出正确的结果集则说明对如果能够毫无疑问的写出正确的结果集则说明对rownum及索引的存储理解超过了大部份人及索引的存储理解超过了大部份人 一切从表结构开始常见问题深入分析sequence使用问题l 如无必要，请直接调用序列l 一个表一个字段请使用一个序列切勿混用l 一个序列请仅用于一个表的一个字段l 引用序列或者序列设置请结合实际 程序中引用时请注意避免溢出问题 （数据类型定义跟序列定义匹配，无法匹配则要考虑序列对应表字段是否唯一且永久性保存）l 序列的设置考虑 如非必要请勿使用order，请一定要设置cache 在rac环境下 nocache+order的组合将是数据库杀手 一切从表结构开始Oracle书籍推