[[413295]]

本文轉(zhuǎn)載自微信公眾號「數(shù)倉寶貝庫」，作者葉樺 等 。轉(zhuǎn)載本文請聯(lián)系數(shù)倉寶貝庫公眾號。

案例：一條很簡單的SQL語句明明選擇了索引掃描，但效率還是很低，SQL語句比較簡單，是對單張表進行查詢，示例代碼如下：

SQL> set autot trace 
 
SQL> SELECT REQUISITION_ID PARAM1, '1' PARAM2, /*電子標簽*/ '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í)行計劃可以看出，Id=4的dbo.LIS_REQUISITION_INFO表選擇的索引是I_PRINT_TIME，PRINT_TIME為時間字段，邏輯讀高達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個，選擇性高達32.1%，PRINT_TIME給定了條件時間范圍，目前從執(zhí)行計劃來看，

LIS_REQUISITION_INFO表的訪問先通過I_PRINT_TIME索引進行范圍掃描，符合條件的記錄回表之后再過濾，產(chǎn)生了大量的單塊讀。雖然PRINT_TIME的選擇性很高，且符合索引掃描的要求，但因為其給定的條件范圍太大，導致該字段并不是一個很好的索引選擇。

除了PRINT_TIME，該SQL還有requisition_id、TAT1_STATE和ROWNUM，下面就來看下它們的選擇性，命令如下：

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, /*電子標簽*/ '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性能有了明顯的提升，邏輯讀從原來的1204017降到8008，執(zhí)行時間也從原來的32分鐘降至27秒。

上述案例介紹了簡單的復合索引優(yōu)化，很多情況下，雖然改寫SQL能夠更好地解決問題，但我們往往很難讓開發(fā)商去做出修改，因此索引優(yōu)化變得尤為重要。當表上存在多個過濾條件時，字段在表中的選擇性只能作為參考而不能成為最終依據(jù)，在實際工作中，我們應該根據(jù)業(yè)務特點對多個字段進行組合分析。在很多情況下，單個字段的選擇性比較低，多個字段的選擇性會成倍增長。