SQL優化（二）（聯合索引的使用）

SQL優化（二）（聯合索引的使用）

問題1

1、從AWR中發現一條SQL效能較差，這條簡單的SQL返回值僅僅一行缺消耗7000+cost覺不能忍受，決定優化！

SQL> select count(*) from user_doudou where aclass=‘10001’ and email='14040928@qq.com';

COUNT(*)

----------

1

Execution Plan

----------------------------------------------------------

Plan hash value: 2038320426

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 40 | 7709 (2)| 00:01:33 |

| 1 | SORT AGGREGATE | | 1 | 40 | | |

|* 2 | TABLE ACCESS FULL| USER_DOUDOU | 260 | 10400 | 7709 (2)| 00:01:33 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("ACLASS"=10001 AND "EMAIL"='14040928@qq.com')

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

238 recursive calls

0 db block gets

34644 consistent gets

33152 physical reads

0 redo size

515 bytes sent via SQL*Net to client

469 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

5 sorts (memory)

0 sorts (disk)

1 rows processed

【由SQL的執行計劃：

<!--[if !supportLists]--&gt1、 <!--[endif]--&gt執行了全表掃描：TABLE ACCESS FULL幾乎佔了所有的COST

<!--[if !supportLists]--&gt2、 <!--[endif]--&gt如果建立索引，應給指定access；最簡單指定access的方法就是把where謂語中的選擇性高的列作為索引列（也可以從filter條件中選取列作為索引，一個原則選擇度越高越好）】

2、建立索引

2-1、檢視資料總行、消耗cost、表基本結構

SQL> select count(*) from user_doudou;

COUNT(*)

----------

2419271

Execution Plan

----------------------------------------------------------

Plan hash value: 2038320426

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 7675 (1)| 00:01:33 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| USER_DOUDOU | 2604K| 7675 (1)| 00:01:33 |

--------------------------------------------------------------------------

SQL> set line 100

SQL> desc user_doudou

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

ACLASS NOT NULL NUMBER

SERIALNO NOT NULL VARCHAR2(16)

PASSWORD VARCHAR2(20)

EMAIL NOT NULL VARCHAR2(50)

STUDENTNO VARCHAR2(20)

STUDENTNAME VARCHAR2(50)

UNIVERSITY VARCHAR2(5)

SENDTYPE CHAR(1)

STATE NOT NULL CHAR(1)

SKILLDATE VARCHAR2(4)

INDATE DATE

IMPORTNO NUMBER

OVERFLAG CHAR(1)

SENDCOUNT NUMBER(2)

STATE_OLD VARCHAR2(2)

EMAILSTATUS VARCHAR2(1)

REFUSED VARCHAR2(1)

INVALID VARCHAR2(1)

LOG_TIME DATE

2-2、建立模擬環境並檢視資料總行及消耗cost，並檢視我們需要調優SQL的cost消耗

SQL> create index idx_doudou_serialno on user_doudou (serialno) online compute statistics;

Index created.

SQL> select count(*) from user_doudou;

COUNT(*)

----------

2419271

Execution Plan

----------------------------------------------------------

Plan hash value: 863670098

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 1471 (3)| 00:00:18 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FAST FULL SCAN| IDX_DOUDOU_SERIALNO | 2604K| 1471 (3)| 00:00:18 |

-------------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

5 recursive calls

0 db block gets

6523 consistent gets

6691 physical reads

0 redo size

518 bytes sent via SQL*Net to client

469 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

2-3、生產庫上的索引在這條SQL沒有被使用，還是全表掃描

SQL> select count(*) from user_doudou where aclass=‘10001’ and email='14040928@qq.com';

COUNT(*)

----------

1

Execution Plan

----------------------------------------------------------

Plan hash value: 2038320426

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 40 | 7709 (2)| 00:01:33 |

| 1 | SORT AGGREGATE | | 1 | 40 | | |

|* 2 | TABLE ACCESS FULL| USER_DOUDOU | 260 | 10400 | 7709 (2)| 00:01:33 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("ACLASS"=10001 AND "EMAIL"='14040928@qq.com')

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

238 recursive calls

0 db block gets

34644 consistent gets

33152 physical reads

0 redo size

515 bytes sent via SQL*Net to client

469 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

5 sorts (memory)

0 sorts (disk)

1 rows processed

2-4、根據我們的SQL謂語，及列的資料分佈，決定建立聯合索引

SQL> create index idx_doudou_aclass on user_doudou(aclass,email) online compute statistics;--（建立索引並收集統計資訊）

Index created.

2-5、聯合索引被建立之後，SQL的COST從7000+下降到3，這個結果說明效果還是不錯的

SQL> select count(*) from user_doudou where aclass=‘10001’ and email='14040928@qq.com';

COUNT(*)

----------

1

Execution Plan

----------------------------------------------------------

Plan hash value: 1829380857

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 40 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 40 | | |

|* 2 | INDEX RANGE SCAN| IDX_DOUDOU_ACLASS | 1 | 40 | 3 (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ACLASS"=10001 AND "EMAIL"='14040928@qq.com')

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

9 recursive calls

0 db block gets

88 consistent gets

373 physical reads

0 redo size

515 bytes sent via SQL*Net to client

469 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

問題2

如果我們沒有使用聯合索引，給謂語中的列都建立了單獨索引。效果如何呢

SQL> drop index IDX_DOUDOU_ACLASS;

Index dropped.

SQL> create index idx_dou_class on user_doudou(aclass) online;

Index created.

SQL> create index idx_dou_email on user_doudou(email) online;

Index created.

SQL> select count(*) from user_doudou where aclass=‘10001’ and email='14040928@qq.com';

COUNT(*)

----------

1

Execution Plan

----------------------------------------------------------

Plan hash value: 202351326

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 40 | 356 (0)| 00:00:05 |

| 1 | SORT AGGREGATE | | 1 | 40 | | |

|* 2 | TABLE ACCESS BY INDEX ROWID| USER_DOUDOU | 260 | 10400 | 356 (0)| 00:00:05 |

|* 3 | INDEX RANGE SCAN | IDX_DOU_EMAIL | 382 | | 4 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("ACLASS"=10001)

3 - access("EMAIL"='14040928@qq.com')

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

75 recursive calls

0 db block gets

114 consistent gets

314 physical reads

0 redo size

515 bytes sent via SQL*Net to client

469 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

【單獨給每個列建立索引的效果明顯不如聯合索引。但是這絕不是說聯合索引就比單獨索引效能好，這裡還要考慮到資料分佈和選擇度】

問題3

統計分析索引有必要麼？下面小實驗簡略說明一下

SQL> select * from user_doudou where aclass=‘10001’ and email='14040928@qq.com';

Execution Plan

----------------------------------------------------------

Plan hash value: 3373022615

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 260 | 44460 | 356 (0)| 00:00:05 |

|* 1 | TABLE ACCESS BY INDEX ROWID| USER_DOUDOU | 260 | 44460 | 356 (0)| 00:00:05 |

|* 2 | INDEX RANGE SCAN | IDX_DOU_EMAIL | 382 | | 4 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ACLASS"=10001)

2 - access("EMAIL"='14040928@qq.com')

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

5 consistent gets

0 physical reads

0 redo size

1796 bytes sent via SQL*Net to client

469 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> exec dbms_stats.gather_table_stats(user,'user_doudou',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select * from user_doudou where aclass=‘10001’ and email='14040928@qq.com';

Execution Plan

----------------------------------------------------------

Plan hash value: 3373022615

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 98 | 4 (0)| 00:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| USER_DOUDOU | 1 | 98 | 4 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_DOU_EMAIL | 1 | | 3 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ACLASS"=10001)

2 - access("EMAIL"='14040928@qq.com')

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

5 consistent gets

0 physical reads

0 redo size

1796 bytes sent via SQL*Net to client

469 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

<!--[if !supportLists]--&gt1 <!--[endif]--&gtrows processed

【統計收集索引資訊前後是差別的相差了近4倍左右，統計收集索引資訊的效果隨環境不同而變化，但是絕對是有必要的】

總結：

1、索引建立：1、選擇度高的列上建立索引

2、在where謂語中，選擇建立索引的列

3、有類似有index skip scan的條件，建議建立聯合索引

2、統計收集索引資訊是有必要的，建議使用dbms_stats包收集

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

SQL優化（二）（聯合索引的使用）

相關文章