SQL性能優(yōu)化策略之聯(lián)合索引優(yōu)化方法
本文轉(zhuǎn)載自微信公眾號(hào)「數(shù)倉(cāng)寶貝庫(kù)」，作者葉樺 等 。轉(zhuǎn)載本文請(qǐng)聯(lián)系數(shù)倉(cāng)寶貝庫(kù)公眾號(hào)。
案例：一條很簡(jiǎn)單的SQL語(yǔ)句明明選擇了索引掃描，但效率還是很低，SQL語(yǔ)句比較簡(jiǎn)單，是對(duì)單張表進(jìn)行查詢(xún)，示例代碼如下：
- SQL> set autot trace
- SQL> SELECT REQUISITION_ID PARAM1, '1' PARAM2, /*電子標(biāo)簽*/ '1' PARAM3
- 2 FROM dbo.LIS_REQUISITION_INFO
- 3 WHERE PRINT_TIME >=
- 4 TO_DATE('2019-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
- 5 AND PRINT_TIME < SYSDATE
- 6 and length(requisition_id) = 12
- 7 AND (TAT1_STATE = '' OR TAT1_STATE IS NULL)
- 8 AND ROWNUM < 800;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1151136383
- ------------------------------------------------------------------------------------------
- | Id | Operation |Name |Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 799 | 18377 | 160K (1)| 00:32:03 |
- |* 1 | COUNT STOPKEY | | | | | |
- |* 2 | FILTER | | | | | |
- |* 3 | TABLE ACCESS BY
- INDEX ROWID |LIS_REQUISITION_INFO| 800 | 18400 | 160K (1)| 00:32:03 |
- |* 4 | INDEX RANGE SCAN |I_PRINT_TIME | | | 3799 (1)| 00:00:46 |
- ------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter(ROWNUM<800)
- 2 - filter(SYSDATE@!>TO_DATE(' 2019-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
- 3 - filter("TAT1_STATE" IS NULL AND LENGTH("REQUISITION_ID")=12)
- 4 - access("PRINT_TIME">=TO_DATE(' 2019-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
- "PRINT_TIME"<SYSDATE@!)
- Statistics
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 1204017 consistent gets
- 161836 physical reads
- 19984 redo size
- 761 bytes sent via SQL*Net to client
- 520 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 3 rows processed
從上述代碼的執(zhí)行計(jì)劃可以看出，Id=4的dbo.LIS_REQUISITION_INFO表選擇的索引是I_PRINT_TIME，PRINT_TIME為時(shí)間字段，邏輯讀高達(dá)1204017，下面我們看下該列的選擇性，命令如下：
- 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) selectivity
- from dba_tab_col_statistics a, dba_tables b
- where a.owner = b.owner
- and a.table_name = b.table_name
- and a.owner = 'DBO'
- and a.table_name = 'LIS_REQUISITION_INFO'
- and a.column_name = 'PRINT_TIME';
- OWNER TABLE_NAME COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY
- ------- --------------------- ----------- -------- ----------- -----------
- DBO LIS_REQUISITION_INFO PRINT_TIME 6933600 2226944 32.1
LIS_REQUISITION_INFO的數(shù)據(jù)量為6 933 600條，PRINT_TIME列的不同值為2 226 944個(gè)，選擇性高達(dá)32.1%，PRINT_TIME給定了條件時(shí)間范圍，目前從執(zhí)行計(jì)劃來(lái)看，
LIS_REQUISITION_INFO表的訪問(wèn)先通過(guò)I_PRINT_TIME索引進(jìn)行范圍掃描，符合條件的記錄回表之后再過(guò)濾，產(chǎn)生了大量的單塊讀。雖然PRINT_TIME的選擇性很高，且符合索引掃描的要求，但因?yàn)槠浣o定的條件范圍太大，導(dǎo)致該字段并不是一個(gè)很好的索引選擇。
除了PRINT_TIME，該SQL還有requisition_id、TAT1_STATE和ROWNUM，下面就來(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) selectivity
- from dba_tab_col_statistics a, dba_tables b
- where a.owner = b.owner
- and a.table_name = b.table_name
- and a.owner = 'DBO'
- and a.table_name = 'LIS_REQUISITION_INFO'
- and a.column_name in ('PRINT_TIME', 'REQUISITION_ID', 'TAT1_STATE');
- OWNER TABLE_NAME COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY
- ------- --------------------- -------------------------- ----------- -----------
- DBO LIS_REQUISITION_INFO TAT1_STATE 6933600 2 0
- DBO LIS_REQUISITION_INFO REQUISITION_ID 6933600 6933600 100
- DBO LIS_REQUISITION_INFO PRINT_TIME 6933600 2226944 32.1
- SQL> select count(*),
- from dbo.LIS_REQUISITION_INFO
- where length(requisition_id) = 12
- COUNT(*)
- -------
- 6968919
- SQL> select TAT1_STATE, count(*)
- from dbo.LIS_REQUISITION_INFO
- group by TAT1_STATE;
- TAT1_STAT COUNT(*)
- ---------- --------
- 1242217
- 1 5355366
- 2 371401
REQUISITION_ID為主鍵的選擇性很高，但幾乎所有的記錄值都符合length (requisition_id) = 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)合索引，那么效果又將如何呢?命令如下：
- SQL> create index dbo.idx_LIS_REQUISITION_INFO_com1 on dbo.LIS_REQUISITION_INFO
- (PRINT_TIME,TAT1_STATE) online;
- SQL> SELECT /*+ index(LIS_REQUISITION_INFO dbo.idx_LIS_REQUISITION_INFO_com1) */
- REQUISITION_ID PARAM1, '1' PARAM2, /*電子標(biāo)簽*/ '1' PARAM3
- FROM dbo.LIS_REQUISITION_INFO
- WHERE PRINT_TIME >=
- TO_DATE('2019-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
- AND PRINT_TIME < SYSDATE
- and length(requisition_id) = 12
- AND (TAT1_STATE = '' OR TAT1_STATE IS NULL)
- AND ROWNUM < 800;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1406522876
- -----------------------------------------------------------------------------------------------------
- | Id | Operation | Name |Starts|E-Rows|A-Rows| A-Time |Buffers|
- -----------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.27| 8146 |
- |* 1 | COUNT STOPKEY | | 1 | | 6 |00:00:00.27| 8146 |
- |* 2 | FILTER | | 1 | | 6 |00:00:00.27| 8146 |
- |* 3 | TABLE ACCESS BY
- INDEX ROWID |LIS_REQUISITION_INFO | 1 | 144 | 6 |00:00:00.27| 8146 |
- |* 4 | INDEX RANGE SCAN |IDX_LIS_REQUISITION_INFO_COM1| 1 |14398 | 8 |00:00:00.27| 8140 |
- -----------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter(ROWNUM<800)
- 2 - filter(SYSDATE@!>TO_DATE(' 2019-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
- 3 - filter(LENGTH("REQUISITION_ID")=12)
- 4 - access("PRINT_TIME">=TO_DATE(' 2019-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TAT1_STATE"
- IS NULL AND "PRINT_TIME"<SYSDATE@!)
- filter("TAT1_STATE" IS NULL)
- Statistics
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 8008 consistent gets
- 8014 physical reads
- 0 redo size
- 471 bytes sent via SQL*Net to client
- 508 bytes received via SQL*Net from client
- 1 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 0 rows processed
創(chuàng)建索引之后，SQL性能有了明顯的提升，邏輯讀從原來(lái)的1204017降到8008，執(zhí)行時(shí)間也從原來(lái)的32分鐘降至27秒。
上述案例介紹了簡(jiǎn)單的復(fù)合索引優(yōu)化，很多情況下，雖然改寫(xiě)SQL能夠更好地解決問(wèn)題，但我們往往很難讓開(kāi)發(fā)商去做出修改，因此索引優(yōu)化變得尤為重要。當(dāng)表上存在多個(gè)過(guò)濾條件時(shí)，字段在表中的選擇性只能作為參考而不能成為最終依據(jù)，在實(shí)際工作中，我們應(yīng)該根據(jù)業(yè)務(wù)特點(diǎn)對(duì)多個(gè)字段進(jìn)行組合分析。在很多情況下，單個(gè)字段的選擇性比較低，多個(gè)字段的選擇性會(huì)成倍增長(zhǎng)。
