開發(fā)常見數(shù)據(jù)庫(kù)問(wèn)題總結(jié)及分析

1、一切從表結(jié)構(gòu)開始一切從表結(jié)構(gòu)開始一切從表結(jié)構(gòu)開始l綁定變量問(wèn)題l索引使用問(wèn)題lRownum使用問(wèn)題lSequence使用問(wèn)題lOralce基本原理的理解問(wèn)題 一切從表結(jié)構(gòu)開始綁定變量問(wèn)題l程序未采用預(yù)編譯語(yǔ)句導(dǎo)致未綁定變量l動(dòng)態(tài)sql語(yǔ)句未實(shí)現(xiàn)綁定變量 一切從表結(jié)構(gòu)開始索引的使用問(wèn)題l 類型隱式轉(zhuǎn)換導(dǎo)致索引失效 *程序中變量(參數(shù))類型跟表中定義不一致導(dǎo)致隱式轉(zhuǎn)換 *存儲(chǔ)過(guò)程或者sql語(yǔ)句中參數(shù),變量定義跟表不一致導(dǎo)致隱式轉(zhuǎn)換 *同一意義的字段在各表中類型不一致但需要進(jìn)行連接操作產(chǎn)生隱式轉(zhuǎn)換設(shè)計(jì)問(wèn)題(areacode字段在個(gè)郵不同表中的數(shù)據(jù)類型)l 索引列使用函數(shù)導(dǎo)致索引失效l 查詢條件上無(wú)索

2、引導(dǎo)致索引失效嚴(yán)重的設(shè)計(jì)問(wèn)題l 組合索引前導(dǎo)列未在查詢條件中導(dǎo)致索引失效或者skip index range scan導(dǎo)致性能低下 一切從表結(jié)構(gòu)開始rownum使用問(wèn)題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 ; 一切從表結(jié)構(gòu)開始常見問(wèn)題分析綁定變量問(wèn)題l綁定變量 oracle判斷一條sql語(yǔ)句是否相同僅以sql文本是否相同來(lái)判斷，故文本不一致的sql語(yǔ)句，即需要進(jìn)行解釋生成不同的執(zhí)行計(jì)劃。故會(huì)造成文本差異的情況即會(huì)導(dǎo)致硬解析。會(huì)導(dǎo)致文本不一致的情況如大小寫，縮進(jìn)，未綁定變量等，其中又以未綁定變量的影響最大，且其影響是跟業(yè)務(wù)量成正比的，如果一個(gè)業(yè)務(wù)系統(tǒng)完全沒(méi)綁定變量但又沒(méi)有造成嚴(yán)重問(wèn)題其實(shí)也是種悲劇悲劇(設(shè)置設(shè)置cursor_sharing =similar的情況例外，但此種設(shè)置極易導(dǎo)致bug從而引發(fā)重大問(wèn)題） 。 解決：書寫格式化一致（確定全部采用大寫或

4、者小寫，相同的縮進(jìn)，空格統(tǒng)一，統(tǒng)一的換行標(biāo)準(zhǔn)等） 實(shí)現(xiàn)綁定變量l不綁定變量會(huì)有什么問(wèn)題 性能問(wèn)題性能問(wèn)題 硬解析(代碼演示) 索引失效(代碼演示) 安全問(wèn)題安全問(wèn)題(代碼演示) 容易sql注入-早期139郵箱未綁定變量被注入案例(admin所有密碼被修改，photoshow站點(diǎn)被攻擊） 檢查檢查sql執(zhí)行計(jì)劃干擾執(zhí)行計(jì)劃干擾一切從表結(jié)構(gòu)開始常見問(wèn)題分析綁定變量問(wèn)題l 如何實(shí)現(xiàn)綁定變量 程序采用預(yù)編譯 動(dòng)態(tài)sql語(yǔ)句采用占位符(數(shù)據(jù)類型嚴(yán)格一致）l 如何知道是否實(shí)現(xiàn)了綁定變量 檢查共享池(v$sqlarea)中sql語(yǔ)句 一切從表結(jié)構(gòu)開始常見問(wèn)題分析索引使用問(wèn)題l 常見索引失效情況 類型隱式轉(zhuǎn)換

5、 索引列上使用函數(shù) 索引列參與運(yùn)算 查詢條件未包括索引前導(dǎo)列(可能失效亦可能skip index range scan) 查詢同一張表中同一個(gè)字段的最大最小值使用一條sql語(yǔ)句實(shí)現(xiàn) 一切從表結(jié)構(gòu)開始常見問(wèn)題分析索引使用問(wèn)題l 如何避免索引失效 類型隱式轉(zhuǎn)換的規(guī)避類型隱式轉(zhuǎn)換的規(guī)避 總原則：一切從表結(jié)構(gòu)開始 程序定義變量數(shù)據(jù)類型與表一致程序定義變量數(shù)據(jù)類型與表一致 存儲(chǔ)過(guò)程，函數(shù)等參數(shù)，變量定義跟表一致存儲(chǔ)過(guò)程，函數(shù)等參數(shù)，變量定義跟表一致（采用取表字段數(shù)據(jù)類 型的方式可以有效的規(guī)避數(shù)據(jù)類型不一致及將來(lái)表數(shù)據(jù)類型修改時(shí)避免存儲(chǔ)過(guò)程不匹配） 表設(shè)計(jì)相同意義的字段在不同表中數(shù)據(jù)類型統(tǒng)一表設(shè)計(jì)相同意義

6、的字段在不同表中數(shù)據(jù)類型統(tǒng)一 查詢列上使用函數(shù)的規(guī)避查詢列上使用函數(shù)的規(guī)避 根據(jù)業(yè)務(wù)等價(jià)性改寫語(yǔ)句盡量不需要使用函數(shù)根據(jù)業(yè)務(wù)等價(jià)性改寫語(yǔ)句盡量不需要使用函數(shù) 對(duì)于無(wú)法避免一定要使用函數(shù)的咨詢對(duì)于無(wú)法避免一定要使用函數(shù)的咨詢DBA是否可以創(chuàng)建函數(shù)索引是否可以創(chuàng)建函數(shù)索引 一切從表結(jié)構(gòu)開始常見問(wèn)題分析索引使用問(wèn)題l 如何避免索引失效 索引列參與運(yùn)算索引列參與運(yùn)算 根據(jù)業(yè)務(wù)等價(jià)性改寫語(yǔ)句將運(yùn)算轉(zhuǎn)至條件（變量）根據(jù)業(yè)務(wù)等價(jià)性改寫語(yǔ)句將運(yùn)算轉(zhuǎn)至條件（變量） select * from mail_notify_info where modifytime+ 1 / 24 sysdate = select *

7、 from mail_notify_info where modifytime sysdate -1/24 注：即使modifytime列上沒(méi)有索引也應(yīng)該這樣改寫，為什么？ 查詢條件未包括索引前導(dǎo)列查詢條件未包括索引前導(dǎo)列 根據(jù)業(yè)務(wù)等價(jià)性改寫根據(jù)業(yè)務(wù)等價(jià)性改寫sql語(yǔ)句至使用上索引前導(dǎo)列語(yǔ)句至使用上索引前導(dǎo)列 此種情況需要從源頭規(guī)避，即從表設(shè)計(jì)即對(duì)于會(huì)成為查詢條件或者參與運(yùn)算的列保證為not null類型，從而規(guī)避這種查詢需求。 一切從表結(jié)構(gòu)開始常見問(wèn)題分析索引使用問(wèn)題l 如何知道索引失效 執(zhí)行計(jì)劃是檢查索引是否使用的唯一標(biāo)準(zhǔn)-v$sql_plan(OPTION=FULL) 索引監(jiān)控定位當(dāng)前索

8、引的使用情況 l 索引使用-參見培訓(xùn)文檔“索引.ppt” 一切從表結(jié)構(gòu)開始常見問(wèn)題分析rownum使用問(wèn)題l詳見培訓(xùn)文檔”從Rowid&Rownum開始.ppt”l一個(gè)例子，請(qǐng)大家思考一下結(jié)果如何 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) 創(chuàng)建索引前寫出結(jié)果集SQL create index ix_userinfo$salary on userinfo(salary);（2）創(chuàng)建索引后寫出結(jié)果集 SQL alter table userinfo modify salary not null; (3) 修改salary字段

11、約束后寫出結(jié)果集 -如果能夠毫無(wú)疑問(wèn)的寫出正確的結(jié)果集則說(shuō)明對(duì)如果能夠毫無(wú)疑問(wèn)的寫出正確的結(jié)果集則說(shuō)明對(duì)rownum及索引的存儲(chǔ)理解超過(guò)了大部份人及索引的存儲(chǔ)理解超過(guò)了大部份人 一切從表結(jié)構(gòu)開始常見問(wèn)題深入分析sequence使用問(wèn)題l 如無(wú)必要，請(qǐng)直接調(diào)用序列l(wèi) 一個(gè)表一個(gè)字段請(qǐng)使用一個(gè)序列切勿混用l 一個(gè)序列請(qǐng)僅用于一個(gè)表的一個(gè)字段l 引用序列或者序列設(shè)置請(qǐng)結(jié)合實(shí)際 程序中引用時(shí)請(qǐng)注意避免溢出問(wèn)題 （數(shù)據(jù)類型定義跟序列定義匹配，無(wú)法匹配則要考慮序列對(duì)應(yīng)表字段是否唯一且永久性保存）l 序列的設(shè)置考慮 如非必要請(qǐng)勿使用order，請(qǐng)一定要設(shè)置cache 在rac環(huán)境下 nocache+order的組合將是數(shù)據(jù)庫(kù)殺手 一切從表結(jié)構(gòu)開始Oracle書籍推