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

索引列使用函数导致索引失效 查询条件上无索引导致索引失效—严重的设计问 题 组合索引前导列未在查询条件中导致索引失效或 者skip index range scan导致性能低下
一切从表结构开始
常见数据库问题总结--rownum使用问题
Rownum使用跟排序同一层
select * from tab where rownum<=10 order by xx desc (这个查询的结果谁能够描述一下？）
SQL> create table userinfo(userid number,username varchar2(50),salary int); • • • • • • • • • • •
•
SQL> insert into userinfo(userid,username,salary) select rownum as userid,'张'||rownum as username,1000+rownum as salary from dba_objects where rownum<=100; SQL> insert into userinfo(userid,username,salary) select rownum+100 as userid,'李'||rownum as username,1500+rownum as salary from dba_objects where rownum<=100; SQL> insert into userinfo(userid,username,salary) select rownum+200 as userid,'李'||rownum as username,1300+rownum as salary from dba_objects where rownum<=100; SQL> commit; (1) 创建索引前写出结果集
查询同一张表中同一个字段的最大最小值使用一条sql语句实现
一切从表结构开始
常见问题分析—索引使用问题
如何避免索引失效
类型隐式转换的规避 总原则：一切从表结构开始
程序定义变量数据类型与表一致 存储过程，函数等参数，变量定义跟表一致（采用取表字段数据类
型的方式可以有效的规避数据类型不一致及将来表数据类型修改时避 免存储过程不匹配）
引用sequence的程序未考虑值域导致的溢出问题
程序最终崩溃—短线程序之重复下发短信
一切从表结构开始
常见数据库问题总结– Oracle基本原百度文库理解问题
多版本一致性理解的问题
读不堵塞写，写不堵塞读的理解问题
• • • • • • • • • • • INSERT INTO MMS_Send_HIS_Info (seq, spsid, …. FailReason) SELECT MMS_Send_HIS_Info_seq.nextval, spsid, …. failreason FROM MMS_UserSend_Info WHERE Sendcount >=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 ;
一切从表结构开始
常见问题分析—绑定变量问题
绑定变量
oracle判断一条sql语句是否相同仅以sql文本是否相同来判断，故文本不一致的sql语句，即需要进行解释生成不 同的执行计划。故会造成文本差异的情况即会导致硬解析。会导致文本不一致的情况如大小写，缩进，未绑定变量 等，其中又以未绑定变量的影响最大，且其影响是跟业务量成正比的，如果一个业务系统完全没绑定变量但又没有 造成严重问题其实也是种悲剧(设置cursor_sharing =similar的情况例外，但此种设置极易导致bug从而引发重大问 题） 。
序列的设置考虑
如非必要请勿使用order，请一定要设置cache
在rac环境下 nocache+order的组合将是数据库杀手
一切从表结构开始
Oracle书籍推荐

Effective Oracle By Design—Oracle高效设计 Expert Oracle Database Architecture--Oracle 9i&10g编程艺术：深入数据
一切从表结构开始
常见数据库问题总结--绑定变量问题
程序未采用预编译语句导致未绑定变量
动态sql语句未实现绑定变量
一切从表结构开始
常见数据库问题总结--索引的使用问题
类型隐式转换导致索引失效
*程序中变量(参数)类型跟表中定义不一致导致隐式转换 *存储过程或者sql语句中参数,变量定义跟表不一致导致隐式转换 *同一意义的字段在各表中类型不一致但需要进行连接操作产生隐式 转换—设计问题(areacode字段在个邮不同表中的数据类型)
解决：书写格式化一致（确定全部采用大写或者小写，相同的缩进，空格统一，统一的换行标准 等）
实现绑定变量
不绑定变量会有什么问题 性能问题 硬解析(代码演示) 索引失效(代码演示) 安全问题(代码演示)
容易sql注入---早期139邮箱未绑定变量被注入案例(admin所有密码被修改，photoshow站点被 攻击）
开发常见数据库问题 总结及分析
一切从表结构开始
大纲
• 开发常见数据库问题总结 • 常见问题分析 • 书籍介绍
数据库开发原则:一切从表结构开始
一切从表结构开始
开发常见数据库问题总结
绑定变量问题 索引使用问题 Rownum使用问题 Sequence使用问题 Oralce基本原理的理解问题
表设计相同意义的字段在不同表中数据类型统一
查询列上使用函数的规避
根据业务等价性改写语句尽量不需要使用函数 对于无法避免一定要使用函数的咨询DBA是否可以创建函数索引
一切从表结构开始
常见问题分析—索引使用问题
如何避免索引失效
索引列参与运算
根据业务等价性改写语句将运算转至条件（变量）
select * from mail_notify_info where modifytime+ 1 / 24 > sysdate =》 select * from mail_notify_info where modifytime> sysdate -1/24 注：即使modifytime列上没有索引也应该这样改写，为什么？
select * from userinfo where rownum<=10 order by salary desc; select * from (select * from userinfo order by salary desc) where rownum<=10;
请按照条件分别写出上面的查询语句的结果集
SQL> create index ix_userinfo$salary on userinfo(salary);
• • • •
（2）创建索引后写出结果集 SQL> alter table userinfo modify salary not null; (3) 修改salary字段约束后写出结果集 ----如果能够毫无疑问的写出正确的结果集则说明对rownum及索引的存储理解超过了大部份人
查询条件未包括索引前导列 根据业务等价性改写sql语句至使用上索引前导列 Where子句中的IS NULL和IS NOT NULL
此种情况需要从源头规避，即从表设计即对于会成为查询条件 或者参与运算的列保证为not null类型，从而规避这种查询需求。
查询同一张表中同一个字段的最大最小值使用一条sql语句实现 分成两条sql语句实现
根据rownum来进行并行处理
一切从表结构开始
常见数据库问题总结-- Sequence的使用问题
多此一举的sequence的使用
赋值给变量再使用，不必要的增加系统负荷
同一个表使用两个sequence
主键或者唯一键冲突，逻辑错误
同一个sequence用于两个表
竞争导致性能低下，seq相关等待事件
检查sql执行计划干扰
一切从表结构开始
常见问题分析—绑定变量问题
如何实现绑定变量 程序采用预编译 动态sql语句采用占位符(数据类型严格一致）
如何知道是否实现了绑定变量
检查共享池(v$sqlarea)中sql语句
一切从表结构开始
常见问题分析—索引使用问题
常见索引失效情况
类型隐式转换 索引列上使用函数 索引列参与运算 查询条件未包括索引前导列(可能失效亦可能skip index range scan) Where子句中的IS NULL和IS NOT NULL 以％打头的LIKE查询
库体系结构
Troubleshooting Oracle Performance—Oracle性能诊断艺术
一切从表结构开始
Q&A 谢谢
一切从表结构开始
一切从表结构开始
常见问题分析—索引使用问题
如何知道索引失效
执行计划是检查索引是否使用的唯一标准--v$sql_plan(OPTION=‘FULL’) 索引监控—定位当前索引的使用情况
索引使用---参见培训文档“索引.ppt”
一切从表结构开始
常见问题分析—rownum使用问题
详见培训文档”从Rowid&Rownum开始.ppt” 一个例子，请大家思考一下结果如何
一切从表结构开始
常见问题深入分析—sequence使用问题
如无必要，请直接调用序列 一个表一个字段请使用一个序列切勿混用 一个序列请仅用于一个表的一个字段 引用序列或者序列设置请结合实际
程序中引用时请注意避免溢出问题 （数据类型定义跟序列定义匹配，无法匹配则要考虑序列对应表字段 是否唯一且永久性保存）