SQL性能優(yōu)化策略之聯(lián)合索引優(yōu)化方法

1、案例：一條很簡單的SQL語句明明選擇了索引掃描，但效率還是很低，SQL語句比較簡單，是對單張表進(jìn)行查詢，示例代碼如下：SQLsetautottraceSQLSELECTREQUISITION_IDPARAM1,1PARAM2,/*電子標(biāo)簽*/1PARAM32FROMdbo.LIS_REQUISITION_INFO3WHEREPRINT_TIME=4TO_DATE(2019-01-0100:00:00,YYYY-MM-DDHH24:MI:SS)5ANDPRINT_TIMESYSDATE6andlength(requisition_id)=127AND(TAT1_STATE=ORTAT1_STAT

2、EISNULL)8ANDROWNUM800;ExecutionPlan-Planhashvalue:1151136383-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|799|18377|160K(1)|00:32:03|*1|COUNTSTOPKEY|*2|FILTER|*3|TABLEACCESSBYINDEXROWID|LIS_REQUISITION_INFO|800|18400|160K(1)|00:32:03|*4|INDEXRANGESCAN|I_PRINT_TIME|3799(1)|00:00:

3、46|-PredicateInformation(identifiedbyoperationid):-1-filter(ROWNUMTO_DATE(2019-01-0100:00:00,syyyy-mm-ddhh24:mi:ss)3-filter(TAT1_STATEISNULLANDLENGTH(REQUISITION_ID)=12)4-access(PRINT_TIME=TO_DATE(2019-01-0100:00:00,syyyy-mm-ddhh24:mi:ss)ANDPRINT_TIMEselect/*+NO_MERGELEADING(ab)*/b.owner,b.table_nam

4、e,a.column_name,b.num_rows,a.num_distinctCardinality,ROUND(A.num_distinct*100/B.num_rows,1)selectivityfromdba_tab_col_statisticsa,dba_tablesbwherea.owner=b.owneranda.table_name=b.table_nameanda.owner=DBOanda.table_name=LIS_REQUISITION_INFOanda.column_name=PRINT_TIME;OWNERTABLE_NAMECOLUMN_NAMENUM_ROW

5、SCARDINALITYSELECTIVITY-DBOLIS_REQUISITION_INFOPRINT_TIME6933600222694432.1LIS_REQUISITION_INFO的數(shù)據(jù)量為6 933 600條，PRINT_TIME列的不同值為2 226 944個，選擇性高達(dá)32.1%，PRINT_TIME給定了條件時間范圍，目前從執(zhí)行計劃來看，LIS_REQUISITION_INFO表的訪問先通過I_PRINT_TIME索引進(jìn)行范圍掃描，符合條件的記錄回表之后再過濾，產(chǎn)生了大量的單塊讀。雖然PRINT_TIME的選擇性很高，且符合索引掃描的要求，但因為其給定的條件范圍太大，導(dǎo)致該字

6、段并不是一個很好的索引選擇。除了PRINT_TIME，該SQL還有requisition_id、TAT1_STATE和ROWNUM，下面就來看下它們的選擇性，命令如下：SQLselect/*+NO_MERGELEADING(ab)*/b.owner,b.table_name,a.column_name,b.num_rows,a.num_distinctCardinality,ROUND(A.num_distinct*100/B.num_rows,1)selectivityfromdba_tab_col_statisticsa,dba_tablesbwherea.owner=b.ownerand

7、a.table_name=b.table_nameanda.owner=DBOanda.table_name=LIS_REQUISITION_INFOanda.column_namein(PRINT_TIME,REQUISITION_ID,TAT1_STATE);OWNERTABLE_NAMECOLUMN_NAMENUM_ROWSCARDINALITYSELECTIVITY-DBOLIS_REQUISITION_INFOTAT1_STATE693360020DBOLIS_REQUISITION_INFOREQUISITION_ID69336006933600100DBOLIS_REQUISIT

8、ION_INFOPRINT_TIME6933600222694432.1SQLselectcount(*),fromdbo.LIS_REQUISITION_INFOwherelength(requisition_id)=12COUNT(*)-6968919SQLselectTAT1_STATE,count(*)fromdbo.LIS_REQUISITION_INFOgroupbyTAT1_STATE;TAT1_STATCOUNT(*)-1242217153553662371401REQUISITION_ID為主鍵的選擇性很高，但幾乎所有的記錄值都符合length (requisition_id

9、) = 12，TAT1_STATE的數(shù)據(jù)分布存在傾斜，條件中的TAT1_STATE = OR TAT1_STATE IS NULL屬于第一種情況，占總數(shù)據(jù)量的1/3。該字段為固定取值（TAT1_STATE = OR TAT1_STATE IS NULL）。如果 PRINT_TIME和TAT1_STATE組合創(chuàng)建聯(lián)合索引，那么效果又將如何呢？命令如下：SQLcreateindexdbo.idx_LIS_REQUISITION_INFO_com1ondbo.LIS_REQUISITION_INFO(PRINT_TIME,TAT1_STATE)online;SQLSELECT/*+index(LIS

10、_REQUISITION_INFOdbo.idx_LIS_REQUISITION_INFO_com1)*/REQUISITION_IDPARAM1,1PARAM2,/*電子標(biāo)簽*/1PARAM3FROMdbo.LIS_REQUISITION_INFOWHEREPRINT_TIME=TO_DATE(2019-01-0100:00:00,YYYY-MM-DDHH24:MI:SS)ANDPRINT_TIMESYSDATEandlength(requisition_id)=12AND(TAT1_STATE=ORTAT1_STATEISNULL)ANDROWNUM800;ExecutionPlan-Pl

11、anhashvalue:1406522876-|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|-|0|SELECTSTATEMENT|1|6|00:00:00.27|8146|*1|COUNTSTOPKEY|1|6|00:00:00.27|8146|*2|FILTER|1|6|00:00:00.27|8146|*3|TABLEACCESSBYINDEXROWID|LIS_REQUISITION_INFO|1|144|6|00:00:00.27|8146|*4|INDEXRANGESCAN|IDX_LIS_REQUISITION_IN

12、FO_COM1|1|14398|8|00:00:00.27|8140|-PredicateInformation(identifiedbyoperationid):-1-filter(ROWNUMTO_DATE(2019-01-0100:00:00,syyyy-mm-ddhh24:mi:ss)3-filter(LENGTH(REQUISITION_ID)=12)4-access(PRINT_TIME=TO_DATE(2019-01-0100:00:00,syyyy-mm-ddhh24:mi:ss)ANDTAT1_STATEISNULLANDPRINT_TIMESYSDATE!)filter(TAT1_STATEISNULL)Statistics-1recursivecalls0dbblockgets8008consistentgets8014physicalreads0redosize471bytessentviaSQL*Nettoclient508bytesreceivedviaSQL*Netfromclient1SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)0rowsprocessed創(chuàng)建索引之后，SQL性能有了明顯的提升，邏輯讀從原來的