总结不走索引的常见情况

李锴 2019-03-22
78
8 0
摘要:请问SQL什么情况下不走索引,帮忙总结一下

问题描述

请问SQL什么情况下不走索引,帮忙总结一下

专家解答

有索引但没走上索引的可能情况:

1、谓词中出现NULL过滤条件导致没有走索引:

创建索引:

SQL> create index ind_test_objects_object_name on TEST_OBJECTS(Object_Name);
Index created.
SQL> select count(*)  from system.test_objects A  where object_name is null;
  COUNT(*)
----------
         0
Elapsed: 00:00:31.52
Execution Plan
----------------------------------------------------------
Plan hash value: 3799704240
-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    24 | 55569   (1)| 00:11:07 |
|   1 |  SORT AGGREGATE    |              |     1 |    24 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_OBJECTS |     1 |    24 | 55569   (1)| 00:11:07 |

如果是单列索引,则该列上值为空的记录不会被存入索引。如果创建一个常量和该列构成的2列的组合索引,由于常量为定值,恒不为空,所以,即使列上的值为空,但由于不是构成索引的2列均为空值,所以,访记录仍会存入索引。那么,这时再用 object_name is null的条件做检索时,就可以在索引中定位相关记录了。

优化后:

SQL> create index ind_test_objects_object_name2 on SYSTEM.TEST_OBJECTS(Object_Name,0);
Index created.
SQL> select count(*)  from system.test_objects A  where object_name is null;
  COUNT(*)
----------
         0
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 431813695
----------------------------------------------------------------------------------------------
| Id  | Operation         | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                               |     1 |    24 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                               |     1 |    24 |            |          |
|*  2 |   INDEX RANGE SCAN| IND_TEST_OBJECTS_OBJECT_NAME2 |     1 |    24 |    3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_NAME" IS NULL)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          4  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
          1  rows processed

2、谓词中出现函数转换导致没有走索引:

原SQL:

SQL> select count(status) from SYSTEM.TEST_OBJECTS where round(object_id)=20;
COUNT(STATUS)
-------------
          256
Elapsed: 00:00:23.23
Execution Plan
----------------------------------------------------------
Plan hash value: 3799704240
-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    12 | 55648   (1)| 00:11:08 |
|   1 |  SORT AGGREGATE    |              |     1 |    12 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_OBJECTS |   143K|  1681K| 55648   (1)| 00:11:08 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROUND("OBJECT_ID")=20)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     204682  consistent gets
     204676  physical reads
          0  redo size
        532  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

检索条件是“round(object_id)=20”,如果希望能够用上索引,要么,我们用某种方法,不对OBJECT_ID列使用ROUND()函数,而还能确保SQL等价;要么,我们创建一个新的函数索引。以ROUND(OBJECT_ID)的形式创建。

创建索引:

SQL> create index ind_object_id2 on SYSTEM.TEST_OBJECTS(round(Object_id));
SQL> select count(status) from SYSTEM.TEST_OBJECTS where round(object_id)=20;
COUNT(STATUS)
-------------
          256
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 1772782182
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    20 | 55448   (1)| 00:11:06 |
|   1 |  SORT AGGREGATE              |                |     1 |    20 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST_OBJECTS   |   143K|  2803K| 55448   (1)| 00:11:06 |
|*  3 |    INDEX RANGE SCAN          | IND_OBJECT_ID2 | 57410 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access(ROUND("OBJECT_ID")=20)
Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
        135  consistent gets
        132  physical reads
          0  redo size
        532  bytes sent via SQL*Net to client
        520  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不走索引:

SQL> delete from system.test_objects;
SQL> insert into system.test_objects select * from dba_objects where rownum < 2;

收集统计信息:

EXEC DBMS_STATS.gather_table_stats('SYSTEM','TEST_OBJECTS');
SQL> insert into system.test_objects select * from dba_objects;
 
SQL> select  * from system.test_objects a where object_name='abc';
no rows selected
Elapsed: 00:00:04.53
Execution Plan
----------------------------------------------------------
Plan hash value: 3570092908
----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     1 |    73 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_OBJECTS |     1 |    73 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME"='abc')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      25598  consistent gets
      25588  physical reads
          0  redo size
       1343  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

表TEST_OBJECTS有1794048行数据:

SQL> select count(*), count(distinct object_name) from system.TEST_OBJECTS;
  COUNT(*) COUNT(DISTINCTOBJECT_NAME)
---------- --------------------------
   1794048                      68166

object_name字段上也存在索引:

INDEX_NAME                        COLUMN_NAME          COLUMN_POSITION
--------------------------------- -------------------- ---------------
IND_TEST_OBJECTS_OBJECT_NAME      OBJECT_NAME                        1

原因:

由于标的统计信息不准确,导致优化器在选择错误的访问方式。以下是标的统计数据。

SQL> select NUM_ROWS, BLOCKS   from dba_tab_statistics  a where table_name='TEST_OBJECTS';
  NUM_ROWS     BLOCKS
---------- ----------
         1          1

收集表的统计信息,确保统计信息与实际情形相符。

SQL> EXEC DBMS_STATS.gather_table_stats('SYSTEM','TEST_OBJECTS');
SQL> select NUM_ROWS, BLOCKS   from dba_tab_statistics  a where table_name='TEST_OBJECTS';
  NUM_ROWS     BLOCKS
---------- ----------
   1794048      25587
SQL> select  * from system.test_objects a where object_name='abc';
no rows selected
Elapsed: 00:00:00.14
Execution Plan
----------------------------------------------------------
Plan hash value: 260470024
------------------------------------------------------------------------------------------------------------
| Id  | Operation       | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                              |    25 |  2450 |    20   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_OBJECTS       |    25 |  2450 |    20   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| IND_TEST_OBJECTS_OBJECT_NAME |    25 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_NAME"='abc')
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
        790  bytes sent via SQL*Net to client
        531  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
1  rows processed

4、在WEHRE条件中like中关键字两边都有"%"导致无法使用索引

SQL> create index ind_OBJECT_NAME on system.test_objects(object_name);
 
SQL> select object_id, object_name from system.test_objects where object_name like '%TORA%'
12160 rows selected.
Elapsed: 00:00:10.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3570092908
----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |   717K|    19M| 55607   (1)| 00:11:08 |
|*  1 |  TABLE ACCESS FULL| TEST_OBJECTS |   717K|    19M| 55607   (1)| 00:11:08 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME" LIKE '%TORA%' AND "OBJECT_NAME" IS NOT NULL)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     205493  consistent gets
     204676  physical reads
          0  redo size
     518803  bytes sent via SQL*Net to client
       9430  bytes received via SQL*Net from client
        812  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      12160  rows processed

将WHERE条件中的like条件从'%TORA%'改成'TORA%'(前提是去除前面的%后,结果是等价的。)优化后:

SQL> select object_id, object_name from system.test_objects where object_name like 'TORA%';
128 rows selected.
 
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3527391799
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |   200 |  5800 |   141   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_OBJECTS    |   200 |  5800 |   141   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | IND_OBJECT_NAME |   200 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_NAME" LIKE 'TORA%')
       filter("OBJECT_NAME" LIKE 'TORA%')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        141  consistent gets
          3  physical reads
          0  redo size
       2762  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        128  rows processed

5、查询条件值与列类型不一致造成未使用上索引

SQL> create table t1 (id varchar2(10),c1 varchar2(32));
Table created.
 
SQL> insert into t1 select rownum id,lpad('T1',20,'*') c1 from dual connect by rownum<=10000;
10000 rows created.
 
SQL> commit;
Commit complete.

在ID列上创建索引:

SQL> create index ind_t1_id on t1(id);
Index created.

发出以下查询SQL:

SQL> set autot traceonly
SQL> select * from t1 where id=1;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
 
--------------------------------------------------------------------------
| Id  | Operation     | Name | Rows  | Bytes | Cost (%CPU)| Time      |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |   |     1 |    25 |    13   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1     |     1 |    25 |    13   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_NUMBER("ID")=1)
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
 
Statistics
----------------------------------------------------------
       5  recursive calls
       0  db block gets
      94  consistent gets
       0  physical reads
       0  redo size
     604  bytes sent via SQL*Net to client
     523  bytes received via SQL*Net from client
       2  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
       1  rows processed

将条件值改为字符1,而不是数值1,就可以避免隐式转换的发生,从而使用上索引。优化后:

SQL> select * from t1 where id='1';
Execution Plan
----------------------------------------------------------
Plan hash value: 2892477391
 
-----------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows    | Bytes | Cost (%CPU)| Time  |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    26 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    26 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN        | IND_T1_ID |     1 |     |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ID"='1')
 
 
Statistics
----------------------------------------------------------
       1  recursive calls
       0  db block gets
       4  consistent gets
       1  physical reads
       0  redo size
     608  bytes sent via SQL*Net to client
     523  bytes received via SQL*Net from client
       2  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
       1  rows processed

6、查询条件列进行数学运算造成未使用上索引

创建表T1,其中ID列上创建索引

SQL> create table t1 as select rownum id,lpad(rownum,10,'*') c1 from dual connect by rownum<=10000;
 
Table created.
 
SQL>  create index ind_t1_object_id on t1(id);
 
Index created.

发出以下查询SQL:

SQL> select * from t1 where id+1=10;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
 
--------------------------------------------------------------------------
| Id  | Operation     | Name | Rows  | Bytes | Cost (%CPU)| Time      |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |   |     1 |    35 |    10   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1     |     1 |    35 |    10   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("ID"+1=10)
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
 
Statistics
----------------------------------------------------------
       0  recursive calls
       0  db block gets
      32  consistent gets
       0  physical reads
       0  redo size
     595  bytes sent via SQL*Net to client
     523  bytes received via SQL*Net from client
       2  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
       1  rows processed

将id+1=10做等价数学转换为id=10-1,从而避免在查询条件列上进行数学运算。优化后:

SQL> select * from t1 where id=10-1;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3177125663
 
----------------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    35 |     2  (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1           |     1 |    35 |     2  (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN        | IND_T1_OBJECT_ID |     1 |       |     1     (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ID"=9)
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
Statistics
----------------------------------------------------------
       0  recursive calls
       0  db block gets
       4  consistent gets
       0  physical reads
       0  redo size
     599  bytes sent via SQL*Net to client
     523  bytes received via SQL*Net from client
       2  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
       1  rows processed

7、索引列在 IN 或者多个 OR 语句中;

8、是否使用的是不可见索引;

SQL> create table test as select * from SH.sales;
Table created.
SQL> create index INDEX_CUST_ID on test(CUST_ID);
Index created.
SQL> select PROD_ID from test where CUST_ID=8888;
192 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4137905133
 
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |   192 |  4992 |   201   (0)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST          |   192 |  4992 |   201   (0)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN          | INDEX_CUST_ID |   192 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CUST_ID"=8888)
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        158  consistent gets
          0  physical reads
          0  redo size
       4016  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
         14  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        192  rows processed
修改索引为不可见索引:
SQL> alter index INDEX_CUST_ID invisible;
Index altered.
SQL> select PROD_ID from test where CUST_ID=8888;
192 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    49 |  1274 |  1206   (1)| 00:00:15 |
|*  1 |  TABLE ACCESS FULL| TEST |    49 |  1274 |  1206   (1)| 00:00:15 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("CUST_ID"=8888)
 
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
         18  recursive calls
          0  db block gets
       4519  consistent gets
       4428  physical reads
          0  redo size
       4016  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
         14  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
        192  rows processed
「喜欢文章,快来给作者赞赏墨值吧」

评论

0
8
Oracle
订阅
欢迎订阅Oracle频道,订阅之后可以获取最新资讯和更新通知。
墨值排行
今日本周综合
近期活动
全部
相关课程
全部