程式SQL优化规范

PCEBG系統資訊處程式SQL優化經驗匯總SFC課程式開發組:周順明AGENDA一、程式SQL優化目的二、SQL语句优化規範與實例三、SQL优化工具四、SQL調整的目標程式SQL優化目的目的•規範、統一SFCS的SQL語法.•提升SQL語句的執行速度與程式的執行效率.•降低數據庫負載,避免SQL引起DB負載異常,影響產線生產.•透過實作,培養新人SQL優化概念,使新人程式開發技能更加全面、系統.SQL语句优化規範與實例ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,所以由小至大去Table例如:表TAB116,384條記錄表TAB21條記錄選擇TAB2作為基礎表(最好的方法)selectcount(*)fromtab1,tab2執行時間0.96秒選擇TAB2作為基礎表(不佳的方法)selectcount(*)fromtab2,tab1執行時間26.09秒1.選擇最有效率的表名順序1.選擇最有效率的表名順序•實例煙台DMDIIIWEB有“Yieldratechart-byoneday”(查詢一天內各個工站生產數量及良率並Show出良率圖表)一支報表,Sql如下:(低效)10分38秒SELECTr.group_name,SUM(r.pass_qty)+SUM(r.fail_qty)total_qty,SUM(r.pass_qty)pass_qty,SUM(r.fail_qty)fail_qty,TO_CHAR(100*SUM(r.pass_qty)/DECODE((SUM(r.pass_qty)+SUM(r.fail_qty)),0,1(SUM(r.pass_qty)+SUM(r.fail_qty))),'990.99')yield_rate,SUM(r.repass_qty)repass_qty,SUM(r.refail_qty)refail_qty,g.group_codeFROMsfism4.r_mo_base_tkk,sfism4.r_station_rec_tr,(SELECTgroup_code,group_nameFROMsfis1.c_group_config_t)gWHEREr.work_id=TO_CHAR(TO_DATE('2006/01/19','yyyy-mm-dd'),'yyyymmdd')||'00'ANDr.work_id=TO_CHAR(TO_DATE('2006/01/19','yyyy-mm-dd'),'yyyymmdd')||'25'ANDr.group_name'PTHINSPECT'ANDr.line_name='PTH0703'ANDkk.cust_no='FOXCONN'ANDr.mo_number=kk.mo_numberANDkk.mo_type='NORMAL'ANDr.group_name=g.group_nameANDr.group_nameIN('SMTINSPECT','INIT','TOUCHUP','ICT','FFT','FVI','OQM','SI','IFT')GROUPBYr.group_name,g.group_codeORDERBYg.group_code優化後SQL語句如下:(高效)1秒SELECTr.group_name,SUM(r.pass_qty)+SUM(r.fail_qty)total_qty,SUM(r.pass_qty)pass_qty,SUM(r.fail_qty)fail_qty,TO_CHAR(100*SUM(r.pass_qty)/DECODE((SUM(r.pass_qty)+SUM(r.fail_qty)),0,1(SUM(r.pass_qty)+SUM(r.fail_qty))),'990.99')yield_rate,SUM(r.repass_qty)repass_qty,SUM(r.refail_qty)refail_qty,g.group_codeFROMsfism4.r_station_rec_tr,sfism4.r_mo_base_tkk,(SELECTgroup_code,group_nameFROMsfis1.c_group_config_t)gWHEREr.work_id=TO_CHAR(TO_DATE('2006/01/19','yyyy-mm-dd'),'yyyymmdd')||'00'ANDr.work_id=TO_CHAR(TO_DATE('2006/01/19','yyyy-mm-dd'),'yyyymmdd')||'25'ANDr.group_name'PTHINSPECT'ANDr.line_name='PTH0703'ANDkk.cust_no='FOXCONN'ANDr.mo_number=kk.mo_numberANDkk.mo_type='NORMAL'ANDr.group_name=g.group_nameANDr.group_nameIN('SMTINSPECT','INIT','TOUCHUP','ICT','FFT','FVI','OQM','SI','IFT')GROUPBYr.group_name,g.group_codeORDERBYg.group_codeSQL语句优化規範與實例ORACLE採用自下而上的順序解析WHERE子句例如:(低效,執行時間156.3秒)SELECT…FROMEMPEWHERESAL50000ANDJOB=‘MANAGER’AND25(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO);(高效,執行時間10.6秒)SELECT…FROMEMPEWHERE25(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO)ANDSAL50000ANDJOB=‘MANAGER’;2.WHERE子句中的連接順序SQL语句优化規範與實例SQL语句优化規範與實例3.盡量避免使用IN操作符•IN操作符介紹用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的，从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。•推荐方案在业务密集的SQL当中尽量不采用IN操作符,用EXITS替代IN。SQL语句优化規範與實例•例如3.盡量避免使用IN操作符煙台DMDIII維修程式每次消號會使用下面SQL語句SELECT*FROMsfis1.c_route_control_tWHEREGROUP_NAME=:GNANDSTATE_FLAG=0ANDROUTE_CODEin(SELECTSPECIAL_ROUTEFROMSFISM4.R_WIP_TRACKING_TWHERESERIAL_NUMBER=:SN);優化後SQL語句如下(高效)SELECT*FROMsfis1.c_route_control_tCWHEREC.GROUP_NAME=:GNANDC.state_flag=0ANDEXISTS(SELECTSPECIAL_ROUTEFROMSFISM4.R_WIP_TRACKING_TDWHERED.SPECIAL_ROUTE=C.ROUTE_CODEANDSERIAL_NUMBER=:SN);SQL语句优化規範與實例4.盡量避免使用NOTIN操作符•NOTIN操作符介紹此操作是強列推薦不使用的，因為它不能應用表的索引,當對數據量巨大的表操作的時候,我們可以想像不用索引進行全表遍歷的後果是多么可怕!•推荐方案用NOTEXISTS或（外连接+判断为空）方案代替。SQL语句优化規範與實例•例如4.盡量避免使用NOTIN操作符SELECT…FROMEMPWHEREDEPT_NONOTIN(SELECTDEPT_NOFROMDEPTWHEREDEPT_CAT=’A’);(高效)SELECT….FROMEMPEWHERENOTEXISTS(SELECT‘X’FROMDEPTDWHERED.DEPT_NO=E.DEPT_NOANDDEPT_CAT=‘A’);SQL语句优化規範與實例5.盡量避免使用操作符(不等於)•操作符介紹不等于操作符是永远不会用到索引的，因此对它的处理只会产生不願意看到的全表扫描•推荐方案用其它相同功能的操作运算代替，如a0改为a0ora0a’’改为a’’。SQL语句优化規範與實例6.避免在索引列上使用ISNULL與ISNOTNULL操作符•ISNULL與ISNOTNULL操作符介紹不能用null作索引，任何包含null值的列都将不会被包含在索引中。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用isnull或isnotnull的语句优化器都是不允许使用索引的。•推薦方案用其它相同功能的操作運算代替，如aisnotnull改為a0或a’’等。不允許字段為空，而用一個缺省值代替空值。SQL语句优化規範與實例•例如煙台DMDIII維修程式每次消號會使用下面SQL語句SELECT*fromSFISM4.R_REPAIR_TWHEREREPAIR_TIMEISNULLANDSERIAL_NUMBER=:SNorderbyTEST_TIMEdesc優化後SQL語句如下(高效)SELECT*fromSFISM4.R_REPAIR_TWHEREREPAIR_TIMEto_date('190001010000','YYYYMMDDHH24miss')andSERIAL_NUMBER=:SNorderbyTEST_TIMEdesc6.避免在索引列上使用ISNULL與ISNOTNULL操作符SQL语句优化規範與實例7.慎用LIKE操作符•LIKE操作符介紹LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE‘%5400%’这种查询不会引用索引，而LIKE‘X5400%’则会引用范围索引。•推荐方案用其它相同功能的操作运算代替，如：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号YY_BHLIKE‘%5400%’这个条件会产生全表扫描，如果改成YY_BHLIKE’X5400%’ORYY_BHLIKE’B5400%’则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。低效SELECTTAB_NAMEFROMTABLESWHERETAB_NAME=(SELECTTAB_NAMEFROMTAB_COLUMNSWHEREVERSION=604)ANDDB_VER=(SELECTDB_VERFROMTAB_COLUMNSWHEREVERSION=604)高效SELECTTAB_NAMEFROMTABLESWHERE(TAB_NAME,DB_VER)=(SELECTTAB_NAME,DB_VER)FROMTAB_COLUMNSWHEREVERSION=604)8.盡量減少對表的查詢•例如SQL语句优化規範與實例SQL语句优化規範與實例使用綁定變量DB在進行SQL解析的時候,只需要執行一次,可以很大的提升程式的執行效率。9.使用綁定變量•介紹•例如SELECT*FROMempWHEREempno:e1;SQL语句优化規範與實例10.避免在索引列上使用計算•介紹WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描。•推薦方案調整WHERE子句,用其它相同功能的操作運算代替。