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
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 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]-->1、 <!--[endif]-->執行了全表掃描：TABLE ACCESS FULL幾乎佔了所有的COST
<!--[if !supportLists]-->2、 <!--[endif]-->如果建立索引，應給指定access；最簡單指定access的方法就是把where謂語中的選擇性高的列作為索引列（也可以從filter條件中選取列作為索引，一個原則選擇度越高越好）】
2、建立索引
2-1、檢視資料總行、消耗cost、表基本結構
SQL> select count(*) from user_doudou;
COUNT(*)
----------
2419271
Execution Plan
----------------------------------------------------------
Plan hash value: 2038320426
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 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
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 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
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 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
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 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
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 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
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 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]-->1 <!--[endif]-->rows processed
【統計收集索引資訊前後是差別的相差了近4倍左右，統計收集索引資訊的效果隨環境不同而變化，但是絕對是有必要的】
總結：
1、 索引建立：1、選擇度高的列上建立索引
2、在where謂語中，選擇建立索引的列
3、有類似有index skip scan的條件，建議建立聯合索引
2、統計收集索引資訊是有必要的，建議使用dbms_stats包收集
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-751301/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL優化案例-正確的使用索引（二）SQL優化索引
- sql優化之多列索引的使用SQL優化索引
- SQL優化案例一則--複合索引沒有被使用SQL優化索引
- 增加複合索引優化SQL的簡單過程索引優化SQL
- SQL優化-索引SQL優化索引
- 【Mysql】InnoDB 中的聚簇索引、二級索引、聯合索引MySql索引
- MySQL 調優之如何正確使用聯合索引MySql索引
- SQL Server索引優化系列之二：索引效能考慮 (轉)SQLServer索引優化
- Oracle優化案例-正確的使用索引（二）Oracle優化索引
- SUM優化（複合索引）優化索引
- SQL最佳化案例-正確的使用索引（二）SQL索引
- 聊聊索引和SQL優化索引SQL優化
- SQL優化--函式索引SQL優化函式索引
- 又一個複合索引的SQL調優索引SQL
- 查詢中讓優化器使用複合索引優化索引
- 一個複合索引的優化案例索引優化
- SQL優化之利用索引排序SQL優化索引排序
- sql優化案例一:使用了表示式不會使用索引SQL優化索引
- SQLServer索引優化（3）：對於建組合索引的要求SQLServer索引優化
- mysql索引的使用和優化MySql索引優化
- 使用Elasticsearch的動態索引和索引優化Elasticsearch索引優化
- OGG複製程式延遲高，優化方法二(存在索引),SQL選擇不好的索引優化索引SQL
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- MySQL SQL 優化之覆蓋索引MySql優化索引
- 複合索引與函式索引優化一例索引函式優化
- [zt] 基於索引的SQL語句優化索引SQL優化
- 【oracle 效能優化】組合索引查詢。Oracle優化索引
- 使用索引優化StopKey索引優化TopK
- mysql優化 | 儲存引擎，建表，索引，sql的優化建議MySql優化儲存引擎索引
- SQLT 最佳化SQL 用複合索引代替單列索引的案例SQL索引
- SQL優化中索引列使用函式之靈異事件SQL優化索引函式事件
- SQL最佳化案例一則--複合索引沒有被使用SQL索引
- ORACLE 9i資料庫優化案例(2) --- 單列索引變複合索引的優化Oracle資料庫優化索引
- 資料庫效能優化-索引與sql相關優化資料庫優化索引SQL
- SQL優化引出的問題（二）SQL優化
- SQL優化案例-分割槽索引之無字首索引（六）SQL優化索引
- SQL優化之統計資訊和索引SQL優化索引
- SQL優化--強制走索引失效的情況SQL優化索引