SQL性能優(yōu)化策略之索引優(yōu)化方法
點(diǎn)擊上方“IT共享之家”，進(jìn)行關(guān)注
回復(fù)“資料”可獲贈(zèng)Python學(xué)習(xí)福利
導(dǎo)讀：SQL優(yōu)化是優(yōu)化工作中經(jīng)常會(huì)涉及的問(wèn)題，由于早期的開(kāi)發(fā)人員往往只關(guān)注于SQL功能的實(shí)現(xiàn)，而忽略了性能。特別是復(fù)雜的SQL，上線之后很少修改，一旦出現(xiàn)問(wèn)題，即使是當(dāng)初的開(kāi)發(fā)人員自己也很難理清其中的業(yè)務(wù)邏輯，需要花費(fèi)大量的時(shí)間去理解代碼之間的關(guān)系，最終可能還是感覺(jué)無(wú)從下手。因此開(kāi)發(fā)人員前期應(yīng)做好代碼注釋，避免編寫過(guò)于復(fù)雜的SQL語(yǔ)句。本文為大家介紹一些生產(chǎn)環(huán)境中真實(shí)的常用索引優(yōu)化方法。
遇到問(wèn)題SQL時(shí)，大家可以根據(jù)各自的習(xí)慣使用不同的工具（PL/SQL、TOAD等）對(duì)SQL進(jìn)行格式化，我們需要重點(diǎn)關(guān)注的是FROM后面的表，以及包含WHERE語(yǔ)句的條件，然后通過(guò)awrsqrpt或dbms_xplan獲取SQL的詳細(xì)執(zhí)行計(jì)劃和資源消耗信息，業(yè)務(wù)案例中的SQL語(yǔ)句如下：
SQL> select sum(cggzl) cggzl, sum(qbgzl) qbgzlfrom (select casewhen zlxm_mc like '%2ê3?3￡1??ì2é%' thengzlelse0end cggzl,casewhen zlxm_mc like '%?3±í?÷1ù%' thengzlelse0end qbgzlfrom dictmanage.dict_zl_pro b,his.pat_inpat_order_info c,pat_inpat_order_cost dwhere d.sfxm_id = b.zlxm_idand c.yzjl_id = d.dyzy_yzjl_idand zlxm_mc like '%2???%'and c.yz_zxrq >= to_date(sysdate)and c.yz_zxrq < to_date(sysdate + 1)and d.fy_status in ('1', '2')and sfxm_je > 0and c.yz_zfrq is nulland c.zylsh = :in_zylsh)
SQL的詳細(xì)執(zhí)行計(jì)劃如圖1所示。
圖1 SQL執(zhí)行計(jì)劃
AWR報(bào)告中的資源消耗信息如圖2所示。
圖2 AWR報(bào)告中的資源消耗信息
上述代碼所示的業(yè)務(wù)SQL語(yǔ)句通過(guò)三張表進(jìn)行關(guān)聯(lián)，最終返回的行數(shù)為個(gè)位數(shù)，從執(zhí)行計(jì)劃中我們可以看出，Id=0，CBO計(jì)算總的COST為123K，其中絕大部分的COST是由Id=10的表pat_inpat_order_cost全表掃描所產(chǎn)生的。此時(shí)，我們需要重點(diǎn)關(guān)注 pat_inpat_order_cost與其他兩張表格的關(guān)聯(lián)情況，where條件中，pat_inpat_order_cost的sfxm_id和dyzy_yzjl_id除了與其他兩張表的字段相關(guān)聯(lián)之外，只有fy_status一個(gè)過(guò)濾條件，下面我們就來(lái)看下該列的選擇性，代碼如下：
SQL> select /*+ NO_MERGE LEADING(a b) */b.owner,b.table_name,a.column_name,b.num_rows,a.num_distinct Cardinality,ROUND(A.num_distinct * 100 / B.num_rows, 1) selectivityfrom dba_tab_col_statistics a, dba_tables bwhere a.owner = b.ownerand a.table_name = b.table_nameand a.owner = upper('his')and a.table_name = upper('pat_inpat_order_cost')and a.column_name = upper('fy_status');
pat_inpat_order_cost表的字段信息如圖3所示。
圖3 pat_inpat_order_cost表的字段信息
SQL> select count(*), FY_STATUSfrom his.pat_inpat_order_cost cgroup by FY_STATUS;
fy_status字段列的選擇性如圖4所示。
圖4 fy_status字段列的選擇性
由圖4可知，fy_status的選擇性并不好，而且存在嚴(yán)重傾斜，語(yǔ)句中的固定寫法d.fy_status in ('1', '2')幾乎包含了所有記錄，因此其并不是一個(gè)很好的過(guò)濾條件。where條件中的大部分過(guò)濾條件均來(lái)自于C表pat_inpat_order_info，而且C表與D表pat_inpat_order_cost的sfxm_id字段相關(guān)聯(lián)。
整個(gè)SQL語(yǔ)句最終返回的行數(shù)為個(gè)位數(shù)，C表通過(guò)YZ_ZXRQ_IDX索引范圍掃描再回表進(jìn)行過(guò)濾，獲取綁定變量值，之后再進(jìn)一步確認(rèn)C表返回的行數(shù)，代碼如下：
SQL> select sql_Id, name, datatype_string, last_captured, value_stringfrom v$sql_bind_capturewhere sql_id = '18rwad2bgcxfa';
SQL綁定變量值獲取情況如圖5所示。
圖5 SQL綁定變量值獲取情況
SQL> select count(*)from his.pat_inpat_order_info cwhere c.yz_zxrq >= to_date(sysdate)and c.yz_zxrq < to_date(sysdate + 1)and c.yz_zfrq is nulland c.zylsh = 72706;
帶入綁定變量我們可以發(fā)現(xiàn)，這個(gè)查詢返回的行數(shù)都保持在個(gè)位數(shù)，如果C表和D表采用嵌套連接的方式，C表能作為驅(qū)動(dòng)表與D表pat_inpat_order_cost相關(guān)聯(lián)，被驅(qū)動(dòng)表只需要在關(guān)聯(lián)列上創(chuàng)建索引，即可大幅提升整個(gè)查詢的效率，做法其實(shí)很簡(jiǎn)單，只需要在sfxm_id字段上創(chuàng)建索引即可，命令如下：
SQL> create index IDX_SFXM_ID on PAT_INPAT_ORDER_COST (SFXM_ID);Plan hash value: 408580053------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 12 (100)| || 1 | SORT AGGREGATE | | 1 | 68 | | ||* 2 | FILTER | | | | | || 3 | NESTED LOOPS | | 1 | 68 | 12 (0)| 00:00:01 || 4 | NESTED LOOPS | | 1 | 68 | 12 (0)| 00:00:01 || 5 | NESTED LOOPS | | 1 | 39 | 11 (0)| 00:00:01 ||* 6 | TABLE ACCESS BY GLOBAL INDEX ROWID| PAT_INPAT_ORDER_INFO | 1 | 21 | 5 (0)| 00:00:01 ||* 7 | INDEX RANGE SCAN | YZ_ZXRQ_IDX | 4 | | 3 (0)| 00:00:01 ||* 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| PAT_INPAT_ORDER_COST | 6 | 108 | 6 (0)| 00:00:01 ||* 9 | INDEX RANGE SCAN | IDX_DYZY_YZJL_ID | 6 | | 2 (0)| 00:00:01 ||* 10 | INDEX UNIQUE SCAN | DICT_ZL_PRO_PK | 1 | | 0 (0)| ||* 11 | TABLE ACCESS BY INDEX ROWID | DICT_ZL_PRO | 1 | 29 | 1 (0)| 00:00:01 |------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - filter(TO_DATE(TO_CHAR(SYSDATE@!+1))>TO_DATE(TO_CHAR(SYSDATE@!)))6 - filter(("C"."ZYLSH"=TO_NUMBER(:IN_ZYLSH) AND "C"."YZ_ZFRQ" IS NULL))7 - access("C"."YZ_ZXRQ">=TO_DATE(TO_CHAR(SYSDATE@!)) AND "C"."YZ_ZXRQ"<TO_DATE(TO_CHAR(SYSDATE@!+1)))8 - filter(("SFXM_JE">0 AND INTERNAL_FUNCTION("D"."FY_STATUS")))9 - access("C"."YZJL_ID"="D"."DYZY_YZJL_ID")10 - access("D"."SFXM_ID"="B"."ZLXM_ID")11 - filter("ZLXM_MC" LIKE '%部位%')
創(chuàng)建索引之后，整個(gè)執(zhí)行計(jì)劃按照我們?cè)O(shè)想的方式進(jìn)行，SQL執(zhí)行時(shí)間也從原來(lái)的24分鐘縮短到1秒，速度提升了上千倍。
上述案例介紹了一種最簡(jiǎn)單的SQL優(yōu)化方式，在大多數(shù)情況下，我們很難讓開(kāi)發(fā)商修改應(yīng)用，因此索引的優(yōu)化在SQL優(yōu)化工作中顯得尤為重要。
本文摘編于《DBA攻堅(jiān)指南：左手Oracle，右手MySQL》，經(jīng)出版方授權(quán)發(fā)布。
------------------- End -------------------
往期精彩文章推薦：