SQL性能优化策略之联合索引优化方法

1、案例：一条很简单的SQL语句明明选择了索引扫描，但效率还是很低，SQL语句比较简单，是对单张表进行查询，示例代码如下：SQLsetautottraceSQLSELECTREQUISITION_IDPARAM1,1PARAM2,/*电子标签*/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的数据量为6 933 600条，PRINT_TIME列的不同值为2 226 944个，选择性高达32.1%，PRINT_TIME给定了条件时间范围，目前从执行计划来看，LIS_REQUISITION_INFO表的访问先通过I_PRINT_TIME索引进行范围扫描，符合条件的记录回表之后再过滤，产生了大量的单块读。虽然PRINT_TIME的选择性很高，且符合索引扫描的要求，但因为其给定的条件范围太大，导致该字

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的数据分布存在倾斜，条件中的TAT1_STATE = OR TAT1_STATE IS NULL属于第一种情况，占总数据量的1/3。该字段为固定取值（TAT1_STATE = OR TAT1_STATE IS NULL）。如果 PRINT_TIME和TAT1_STATE组合创建联合索引，那么效果又将如何呢？命令如下：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,/*电子标签*/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创建索引之后，SQL性能有了明显的提升，逻辑读从原来的