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 包收集