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的执行计划:
1、 执行了全表扫描:TABLE ACCESS FULL几乎占了所有的COST
2、 如果建立索引,应给指定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)
1 rows processed
【统计收集索引信息前后是差别的相差了近4倍左右,统计收集索引信息的效果随环境不同而变化,但是绝对是有必要的】
总结:
1、 索引建立:1、选择度高的列上建立索引
2、在where谓语中,选择建立索引的列
3、有类似有index skip scan的条件,建议建立联合索引
2、统计收集索引信息是有必要的,建议使用dbms_stats包收集