增加複合索引優化SQL的簡單過程

待優化SQL:
select t2.emperor,t1.* from t1,t2 where t1.countryid=t2.id and t1.CountryId=1010 and t1.DistrictId=1 and playerId<>100

T1表上的索引:
SQL> select index_name,column_name from user_ind_columns where table_name='T1';

INDEX_NAME                     COLUMN_NAME
------------------------------ ----------------------------------------
PK_T1                   PLAYERID
T1_CID_IDX              COUNTRYID
T1_IDX                  DISTRICTID
T1_NO_IDX               ARMYNO

T2表上的索引:
SQL> select index_name,column_name from user_ind_columns where table_name='T2';

INDEX_NAME                     COLUMN_NAME
------------------------------ ----------------------------------------
PK_T2                 ID
INDEX_T2              ID
INDEX_T2              DISTRICTID

這些索引都是開發人員建的。

先檢視一下執行計劃：
SQL> explain plan for select t2.emperor,t1.* from t1,t2 where t1.countryid=t2.id and t1.CountryId=1010 and t1.DistrictId=1 and playerId<>100;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
| Id  | Operation                    |  Name              | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |  6266 |   514K|   211 |
|   1 |  NESTED LOOPS                |                    |  6266 |   514K|   211 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T2                 |     1 |    26 |     1 |
|*  3 |    INDEX RANGE SCAN          | INDEX_T2           |     1 |       |     2 |
|*  4 |   TABLE ACCESS FULL          | T1                 |  7641 |   432K|   210 |
-----------------------------------------------------------------------------------

SQL> set autot traceonly
SQL> select t2.emperor,t1.* from t1,t2 where t1.countryid=t2.id and t1.CountryId=1010 and t1.DistrictId=1 and playerId<>100;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=211 Card=6266 Bytes=526344)
   1    0   NESTED LOOPS (Cost=211 Card=6266 Bytes=526344)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=1 Card=1 Bytes=26)
   3    2       INDEX (RANGE SCAN) OF 'INDEX_T2' (NON-UNIQUE) (Cost=2 Card=1)
   4    1     TABLE ACCESS (FULL) OF 'T1' (Cost=210 Card=7641 Bytes=443178)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2289  consistent gets
          0  physical reads
          0  redo size
     135776  bytes sent via SQL*Net to client
       1735  bytes received via SQL*Net from client
        114  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1687  rows processed

發現T1沒走索引，走了全表掃描（T1,T2都經過了分析），嘗試加INDEX HINT，結果邏輯讀和COST都比不加還要高，CBO計算得沒錯，所以它選擇了FULL TABLE。

之後在表T1中增加複合索引T1_CDID_IDX：
SQL> select index_name,column_name from user_ind_columns where table_name='T1';

INDEX_NAME                     COLUMN_NAME
------------------------------ ----------------------------------------
PK_T1                   PLAYERID
T1_CID_IDX              COUNTRYID
T1_IDX                  DISTRICTID
T1_NO_IDX               ARMYNO
T1_CDID_IDX             DISTRICTID
T1_CDID_IDX             COUNTRYID

SQL> set autot traceonly
SQL> select t2.emperor,t1.* from t1,t2 where t1.countryid=t2.id and t1.CountryId=1010 and t1.DistrictId=1 and playerId<>100;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=32 Card=6266 Bytes=526344)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=31 Card=7641 Bytes=443178)
   2    1     NESTED LOOPS (Cost=32 Card=6266 Bytes=526344)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=1 Card=1 Bytes=26)
   4    3         INDEX (RANGE SCAN) OF 'INDEX_T2' (NON-UNIQUE) (Cost=2 Card=1)
   5    2       INDEX (RANGE SCAN) OF 'T1_CDID_IDX' (NON-UNIQUE) (Cost=1 Card=7641)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1075  consistent gets
          7  physical reads
          0  redo size
     135777  bytes sent via SQL*Net to client
       1735  bytes received via SQL*Net from client
        114  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1687  rows processed

由此看到，增加了複合索引後T1表也走了索引，COST由221降到了32，邏輯讀也降低了一半多，SQL得到了一定程度的優化。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17997/viewspace-256888/，如需轉載，請註明出處，否則將追究法律責任。