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

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

2、的數(shù)據(jù)類型)l 索引列使用函數(shù)導(dǎo)致索引失效l 查詢條件上無索引導(dǎo)致索引失效嚴(yán)重的設(shè)計問題l 組合索引前導(dǎo)列未在查詢條件中導(dǎo)致索引失效或者skip index range scan導(dǎo)致性能低下 精品資料一切從表結(jié)構(gòu)(jigu)開始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 - Last

3、DealTime 10/(24*6*5) AND Status=1 ) OR status =-100 ; 精品資料一切(yqi)從表結(jié)構(gòu)開始常見問題分析綁定變量(binling)問題l綁定變量 oracle判斷一條sql語句是否相同僅以sql文本是否相同來判斷，故文本不一致的sql語句，即需要進(jìn)行解釋生成不同的執(zhí)行計劃。故會造成文本差異的情況即會導(dǎo)致硬解析。會導(dǎo)致文本不一致的情況如大小寫，縮進(jìn)，未綁定變量等，其中又以未綁定變量的影響最大，且其影響是跟業(yè)務(wù)量成正比的，如果一個業(yè)務(wù)系統(tǒng)完全沒綁定變量但又沒有造成嚴(yán)重問題其實(shí)也是種悲劇悲劇(設(shè)置設(shè)置cursor_sharing =similar的情

4、況例外，但此種設(shè)置極易導(dǎo)致bug從而引發(fā)重大問題） 。 解決：書寫格式化一致（確定全部采用大寫或者小寫，相同的縮進(jìn)，空格統(tǒng)一，統(tǒng)一的換行標(biāo)準(zhǔn)等） 實(shí)現(xiàn)綁定變量l不綁定變量會有什么問題 性能問題性能問題 硬解析(代碼演示) 索引失效(代碼演示) 安全問題安全問題(代碼演示) 容易sql注入-早期139郵箱未綁定變量被注入案例(admin所有密碼被修改，photoshow站點(diǎn)被攻擊） 檢查檢查sql執(zhí)行計劃干擾執(zhí)行計劃干擾精品資料一切(yqi)從表結(jié)構(gòu)開始常見問題分析(fnx)綁定變量問題l 如何實(shí)現(xiàn)綁定變量 程序采用預(yù)編譯 動態(tài)sql語句采用占位符(數(shù)據(jù)類型嚴(yán)格一致）l 如何知道是否實(shí)現(xiàn)了綁定變

5、量 檢查共享池(v$sqlarea)中sql語句 精品資料一切(yqi)從表結(jié)構(gòu)開始常見問題分析索引使用(shyng)問題l 常見索引失效情況 類型隱式轉(zhuǎn)換 索引列上使用函數(shù) 索引列參與運(yùn)算 查詢條件未包括索引前導(dǎo)列(可能失效亦可能skip index range scan) 查詢同一張表中同一個字段的最大最小值使用一條sql語句實(shí)現(xiàn) 精品資料一切(yqi)從表結(jié)構(gòu)開始常見問題分析索引(suyn)使用問題l 如何避免索引失效 類型隱式轉(zhuǎn)換的規(guī)避類型隱式轉(zhuǎn)換的規(guī)避 總原則：一切從表結(jié)構(gòu)開始 程序定義變量數(shù)據(jù)類型與表一致程序定義變量數(shù)據(jù)類型與表一致 存儲過程，函數(shù)等參數(shù)，變量定義跟表一致存儲過程，

6、函數(shù)等參數(shù)，變量定義跟表一致（采用取表字段數(shù)據(jù)類 型的方式可以有效的規(guī)避數(shù)據(jù)類型不一致及將來表數(shù)據(jù)類型修改時避免存儲過程不匹配） 表設(shè)計相同意義的字段在不同表中數(shù)據(jù)類型統(tǒng)一表設(shè)計相同意義的字段在不同表中數(shù)據(jù)類型統(tǒng)一 查詢列上使用函數(shù)的規(guī)避查詢列上使用函數(shù)的規(guī)避 根據(jù)業(yè)務(wù)等價性改寫語句盡量不需要使用函數(shù)根據(jù)業(yè)務(wù)等價性改寫語句盡量不需要使用函數(shù) 對于無法避免一定要使用函數(shù)的咨詢對于無法避免一定要使用函數(shù)的咨詢DBA是否可以創(chuàng)建函數(shù)索引是否可以創(chuàng)建函數(shù)索引 精品資料一切(yqi)從表結(jié)構(gòu)開始常見問題分析索引使用(shyng)問題l 如何避免索引失效 索引列參與運(yùn)算索引列參與運(yùn)算 根據(jù)業(yè)務(wù)等價性改寫語

7、句將運(yùn)算轉(zhuǎn)至條件（變量）根據(jù)業(yè)務(wù)等價性改寫語句將運(yùn)算轉(zhuǎn)至條件（變量） select * from mail_notify_info where modifytime+ 1 / 24 sysdate = select * from mail_notify_info where modifytime sysdate -1/24 注：即使modifytime列上沒有索引也應(yīng)該這樣改寫，為什么？ 查詢條件未包括索引前導(dǎo)列查詢條件未包括索引前導(dǎo)列 根據(jù)業(yè)務(wù)等價性改寫根據(jù)業(yè)務(wù)等價性改寫sql語句至使用上索引前導(dǎo)列語句至使用上索引前導(dǎo)列 此種情況需要從源頭規(guī)避，即從表設(shè)計即對于會成為查詢條件或者參與運(yùn)算的列

8、保證為not null類型，從而規(guī)避這種查詢需求。 精品資料一切從表結(jié)構(gòu)(jigu)開始常見問題分析索引使用(shyng)問題l 如何知道索引失效 執(zhí)行計劃是檢查索引是否使用的唯一標(biāo)準(zhǔn)-v$sql_plan(OPTION=FULL) 索引監(jiān)控定位當(dāng)前索引的使用情況 l 索引使用-參見培訓(xùn)文檔“索引.ppt” 精品資料一切從表結(jié)構(gòu)(jigu)開始常見問題分析(fnx)rownum使用問題l詳見培訓(xùn)文檔”從Rowid&Rownum開始.ppt”l一個例子，請大家思考一下結(jié)果如何 select * from userinfo where rownum=10 order by salary d

9、esc; select * from (select * from userinfo order by salary desc) where rownum 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 insert

10、 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 as userid,李|rownum as username,1300+rownum as salary from dba_objects where rownum commit; (1) 創(chuàng)建

11、索引前寫出結(jié)果集SQL create index ix_userinfo$salary on userinfo(salary);（2）創(chuàng)建索引后寫出結(jié)果集 SQL alter table userinfo modify salary not null; (3) 修改salary字段約束后寫出結(jié)果集 -如果能夠毫無疑問的寫出正確的結(jié)果集則說明對如果能夠毫無疑問的寫出正確的結(jié)果集則說明對rownum及索引的存儲理解超過了大部份人及索引的存儲理解超過了大部份人 精品資料一切從表結(jié)構(gòu)(jigu)開始常見問題深入分析sequence使用(shyng)問題l 如無必要，請直接調(diào)用序列l(wèi) 一個表一個字段請使用一個序列切勿混用l 一個序列請僅用于一個表的一個字段l 引用序列或者序列設(shè)置請結(jié)合實(shí)際 程序中引用時請注意避免溢出問題 （數(shù)據(jù)類型定義跟序列定義匹配，無法匹配則要考慮序列對應(yīng)表字段是否唯一且永久性保存）l 序列的設(shè)置考慮 如非必要請勿使用order，請一定要設(shè)置cache 在rac環(huán)境下 nocache+order的組合將是數(shù)據(jù)庫殺手 精品資料一切從表結(jié)構(gòu)(jigu)開始Oracle書籍(shj)推薦lEffective O