《开发常见数据库问题总结及分析》由会员分享，可在线阅读，更多相关《开发常见数据库问题总结及分析（18页珍藏版）》请在金锄头文库上搜索。
1、一切从表结构开始开发常见数据库问题开发常见数据库问题 总结及分析总结及分析一切从表结构开始大纲大纲 开发常见数据库问题总结开发常见数据库问题总结 常见问题分析常见问题分析 书籍介绍书籍介绍数据库开发原则数据库开发原则: :一切从表结构开始一切从表结构开始一切从表结构开始开发常见数据库问题总结开发常见数据库问题总结l绑定变量问题 l索引使用问题 lRownum使用问题 lSequence使用问题 lOralce基本原理的理解问题一切从表结构开始常见数据库问题总结常见数据库问题总结- -绑定变量问题l程序未采用预编译语句导致未绑定变量l动态sql语句未实现绑定变量一切从表结构开始常见数据库问题总结
2、常见数据库问题总结- -索引的使用问题l 类型隐式转换导致索引失效*程序中变量(参数)类型跟表中定义不一致导致隐式转换*存储过程或者sql语句中参数,变量定义跟表不一致导致隐式转换*同一意义的字段在各表中类型不一致但需要进行连接操作产生隐式 转换设计问题(areacode字段在个邮不同表中的数据类型) l 索引列使用函数导致索引失效 l 查询条件上无索引导致索引失效严重的设计问 题 l 组合索引前导列未在查询条件中导致索引失效或 者skip index range scan导致性能低下一切从表结构开始常见数据库问题总结常见数据库问题总结- -rownum使用问题l Rownum使用跟排序同一层
3、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 Status=1 ) OR status =-100 ;一切从表结构开始常见问题分析绑定变量问题l绑定变量oracle判断一条sql语句是否相同仅以sql文本是否相同来判断，故文本不一致的sql语句，即需要进行解
4、释生成不 同的执行计划。故会造成文本差异的情况即会导致硬解析。会导致文本不一致的情况如大小写，缩进，未绑定变量 等，其中又以未绑定变量的影响最大，且其影响是跟业务量成正比的，如果一个业务系统完全没绑定变量但又没有 造成严重问题其实也是种悲剧(设置cursor_sharing =similar的情况例外，但此种设置极易导致bug从而引发重大问题 ） 。解决：书写格式化一致（确定全部采用大写或者小写，相同的缩进，空格统一，统一的换行标准 等）实现绑定变量 l不绑定变量会有什么问题性能问题硬解析(代码演示)索引失效(代码演示)安全问题(代码演示)容易sql注入-早期139邮箱未绑定变量被注入案例(a
5、dmin所有密码被修改，photoshow站点被 攻击）检查sql执行计划干扰一切从表结构开始常见问题分析绑定变量问题l 如何实现绑定变量程序采用预编译动态sql语句采用占位符(数据类型严格一致）l 如何知道是否实现了绑定变量检查共享池(v$sqlarea)中sql语句一切从表结构开始常见问题分析索引使用问题l 常见索引失效情况类型隐式转换索引列上使用函数索引列参与运算查询条件未包括索引前导列(可能失效亦可能skip index range scan)WhereWhere子句中的子句中的IS NULLIS NULL和和IS NOT NULLIS NOT NULL以打头的以打头的LIKELIKE
6、查询查询查询同一张表中同一个字段的最大最小值使用一条sql语句实现一切从表结构开始常见问题分析索引使用问题l 如何避免索引失效类型隐式转换的规避总原则：一切从表结构开始程序定义变量数据类型与表一致存储过程，函数等参数，变量定义跟表一致（采用取表字段数据类 型的方式可以有效的规避数据类型不一致及将来表数据类型修改时避 免存储过程不匹配）表设计相同意义的字段在不同表中数据类型统一查询列上使用函数的规避根据业务等价性改写语句尽量不需要使用函数对于无法避免一定要使用函数的咨询DBA是否可以创建函数索引一切从表结构开始常见问题分析索引使用问题l 如何避免索引失效索引列参与运算根据业务等价性改写语句将运算
7、转至条件（变量）select * from mail_notify_info where modifytime+ 1 / 24 sysdate =select * from mail_notify_info where modifytime sysdate -1/24注：即使modifytime列上没有索引也应该这样改写，为什么？查询条件未包括索引前导列根据业务等价性改写sql语句至使用上索引前导列WhereWhere子句中的子句中的IS NULLIS NULL和和IS NOT NULLIS NOT NULL此种情况需要从源头规避，即从表设计即对于会成为查询条件 或者参与运算的列保证为not
8、null类型，从而规避这种查询需求。查询同一张表中同一个字段的最大最小值使用一条查询同一张表中同一个字段的最大最小值使用一条sqlsql语句实现语句实现分成两条分成两条sqlsql语句实现语句实现一切从表结构开始常见问题分析索引使用问题l 如何知道索引失效执行计划是检查索引是否使用的唯一标准- v$sql_plan(OPTION=FULL)索引监控定位当前索引的使用情况l 索引使用-参见培训文档“索引.ppt”一切从表结构开始常见问题分析rownum使用问题l详见培训文档”从RowidSQL insert into userinfo(userid,username,salary) select
9、 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 insert into userinfo(userid,username,salary) select rownum+200
10、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字段约束后写出结果集 -如果能够毫无疑问的写出正确的结果集则说明对rownum及索引的存储理解超过了大部份人一切从表结构开始
11、常见问题深入分析sequence使用问题l 如无必要，请直接调用序列 l 一个表一个字段请使用一个序列切勿混用 l 一个序列请仅用于一个表的一个字段 l 引用序列或者序列设置请结合实际程序中引用时请注意避免溢出问题（数据类型定义跟序列定义匹配，无法匹配则要考虑序列对应表字段 是否唯一且永久性保存） l 序列的设置考虑如非必要请勿使用order，请一定要设置cache在rac环境下 nocache+order的组合将是数据库杀手一切从表结构开始Oracle书籍推荐lEffective Oracle By DesignOracle高效设计 lExpert Oracle Database Architecture-Oracle 9i&10g编程艺术：深入数据 库体系结构 lTroubleshooting Oracle PerformanceOracle性能诊断艺术一切从表结构开始Q & A 谢谢
