有索引却走全表扫描的实验分析

最近在查询某范围的数据时，返回结果仅占整个表数据的0.05%，在相应字段上有对应索引，然而这部分数据并没有走索引，
将根据如下实验证明此种情况的原因
一 构建环境
1建立测试表 有两个字段，主键ID和手机号，手机号上有B树索引
drop table TEST_HIS
-- Create table
create table TEST_HIS
(
  id    number,
  phone varchar2(32)
)
;
-- Add comments to the table
comment on table TEST_HIS
  is '测试直方图';
-- Add comments to the columns
comment on column TEST_HIS.phone
  is '手机号，用于测试上面的直方图是否影响走索引';
2 建立主键和索引
-- Create/Recreate indexes
create index IDX_PHONE on TEST_HIS (phone);
-- Create/Recreate primary, unique and foreign key constraints
alter table TEST_HIS
  add constraint PK_ID primary key (ID);
3 构造测试数据，根据如下脚本，可以手工修改循环次数，向表中插入13开头的电话号码11000个，15开头的电话号码10个，
以及18开头的电话号码10个，null的空号码11010个 
declare
 v_phone varchar2(32);
begin
 v_phone :='136818978';
 for i in 1..10000
 loop
  insert into test_his
    (id, phone)
  values
    (SEQ_TEST_HIST.NEXTVAL, v_phone||i);
    --(SEQ_TEST_HIST.NEXTVAL,null);
 end loop;
end;
4 收集统计信息
SELECT t.NUM_ROWS,t.AVG_ROW_LEN,t.BLOCKS,t.TABLE_NAME,T.LAST_ANALYZED,t.EMPTY_BLOCKS FROM USER_TABLES T WHERE TABLE_NAME ='TEST_HIS';

begin
  dbms_stats.gather_table_stats(ownname => 'TEST',tabname => 'TEST_HIS',cascade => TRUE);
end;

SELECT t.NUM_ROWS,t.AVG_ROW_LEN,t.BLOCKS,t.TABLE_NAME,T.LAST_ANALYZED,t.EMPTY_BLOCKS FROM USER_TABLES T WHERE TABLE_NAME ='TEST_HIS';

二 查找13开头和15开头的电话号码的sql执行计划

select  *　from test.test_his H where h.phone like '13%';
执行计划
----------------------------------------------------------
Plan hash value: 2828532586

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |  1754 | 17540 |    14   (8)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_HIS |  1754 | 17540 |    14   (8)| 00:00:01 |
------------------------------------------------------------------------------

select  *　from test.test_his H where h.phone like '15%';
执行计划
----------------------------------------------------------
Plan hash value: 2828532586

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |  2227 | 22270 |    14   (8)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_HIS |  2227 | 22270 |    14   (8)| 00:00:01 |
------------------------------------------------------------------------------
根据执行计划我们看到查询15开头的数据返回的rows是2227，这和实际数据相差较大，于是打开10053查看相关信息
三 查看10053
alter system flush buffer_cache;
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
explain plan for select *　from test.test_his where phone like '15%';
alter session set events  '10053 trace name context off';
观察10053中针对表的访问路径信息
SINGLE TABLE ACCESS PATH
  Column (#2): PHONE(VARCHAR2)
    AvgLen: 6.00 NDV: 10867 Nulls: 11107 Density: 9.2022e-005
  Table: TEST_HIS  Alias: TEST_HIS    
    Card: Original: 22235  Rounded: 2227  Computed: 2226.62  Non Adjusted: 2226.62
  Access Path: TableScan
    Cost:  14.09  Resp: 14.09  Degree: 0
      Cost_io: 13.00  Cost_cpu: 6359522
      Resp_io: 13.00  Resp_cpu: 6359522
kkofmx: index filter:"TEST_HIS"."PHONE" LIKE '15%'
  Access Path: index (RangeScan)
    Index: IDX_PHONE
    resc_io: 604.00  resc_cpu: 5125340
    ix_sel: 0.20203  ix_sel_with_filters: 0.20203
    Cost: 604.88  Resp: 604.88  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 14.09  Degree: 1  Resp: 14.09  Card: 2226.62  Bytes: 0
TEST_HIS中 NDV: 高达10867个，然而sql中要查询的是以15开头的电话号码，15开头，13开头，18开头的数据只有三种
因此统计信息和实际sql要查询的语义也不相符，因此虽然phone上有索引，但是sql的查询条件是查询某号码开头的数据时，已有索引
就不能准确描述15开头的数据分布情况，因此即便15开头的数据只有10个，优化器也会按照普通索引上的统计信息NDV的值计算
返回rows，而这个返回rows远超过实际数据的分布情况，因此建立直方图收集正确的分布信息
四 建立直方图
analyze table test.test_his compute statistics for columns phone;

alter system flush buffer_cache;
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
explain plan for select *　from test.test_his where phone like '15%';
alter session set events  '10053 trace name context off';

SINGLE TABLE ACCESS PATH
  Column (#2): PHONE(VARCHAR2)
    AvgLen: 5.00 NDV: 10021 Nulls: 11010 Density: 1.0720e-004
    Histogram: HtBal  #Bkts: 75  UncompBkts: 75  EndPtVals: 76
  Table: TEST_HIS  Alias: TEST_HIS    
    Card: Original: 22235  Rounded: 30  Computed: 29.93  Non Adjusted: 29.93
  Access Path: TableScan
    Cost:  14.09  Resp: 14.09  Degree: 0
      Cost_io: 13.00  Cost_cpu: 6359522
      Resp_io: 13.00  Resp_cpu: 6359522
kkofmx: index filter:"TEST_HIS"."PHONE" LIKE '15%'
  Access Path: index (RangeScan)
    Index: IDX_PHONE
    resc_io: 10.00  resc_cpu: 82314
    ix_sel: 0.002716  ix_sel_with_filters: 0.002716
    Cost: 10.01  Resp: 10.01  Degree: 1
******** Begin index join costing ********
  ****** trying bitmap/domain indexes ******
  Access Path: index (IndexOnly)
    Index: IDX_PHONE
    resc_io: 2.00  resc_cpu: 20243
    ix_sel: 0.002716  ix_sel_with_filters: 0.002716
    Cost: 2.00  Resp: 2.00  Degree: 0
    SORT resource      Sort statistics
      Sort width:         477 Area size:      417792 Max Area size:    83676160
      Degree:               1
      Blocks to Sort:       1 Row size:           21 Total Rows:             30
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 5826332
      Total Temp space used: 0
  Access Path: index (FullScan)
    Index: PK_ID
    resc_io: 43.00  resc_cpu: 4712422
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 43.81  Resp: 43.81  Degree: 0
******** Cost index join ********
Index join: Considering index join to index IDX_PHONE
Index join: Joining index PK_ID
Ix HA Join
  Outer table:
    resc: 2.00  card 29.93  bytes: 15  deg: 1  resp: 2.00
  Inner table:
    resc: 54.76  card: 22235.00  bytes: 15  deg: 1  resp: 54.76
    using dmeth: 2  #groups: 1
    Cost per ptn: 0.88  #ptns: 1
    hash_area: 0 (max=0)   Hash join: Resc: 57.65  Resp: 57.65  [multiMatchCost=0.00]
******** Index join cost ********
Cost: 57.65 
******** End index join costing ********
  Best:: AccessPath: IndexRange  Index: IDX_PHONE
         Cost: 10.01  Degree: 1  Resp: 10.01  Card: 29.93  Bytes: 0

查看对应执行计划
---------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
ime     |

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

|   0 | SELECT STATEMENT            |           |    30 |   300 |    10   (0)| 0
0:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HIS  |    30 |   300 |    10   (0)| 0
0:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_PHONE |    30 |       |     2   (0)| 0
0:00:01 |

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

 

时间： 2024-09-30 01:15:00

有索引却走全表扫描的实验分析的相关文章

一条全表扫描sql语句的分析

今天在对生产系统做监控的时候,发现一个process的cpu消耗很高,抓取了对应的session和执行的sql语句. 发现是一个简单的update语句,这样一条如果CPU消耗较大,很可能是由于全表扫描的. UPDATE COMM_ACTIVITY SET COMM_ACTIVITY.EXTRACT_STATUS = NVL(:1 , EXTRACT_STATUS), COMM_ACTIVITY.SOURCE_TYPE = NVL(:2 , SOURCE_TYPE), OPERATOR_ID =

SQL SERVER中关于OR会导致索引扫描或全表扫描的浅析

在SQL SERVER的查询语句中使用OR是否会导致不走索引查找(Index Seek)或索引失效(堆表走全表扫描 (Table Scan).聚集索引表走聚集索引扫描(Clustered Index Scan))呢?是否所有情况都是如此?又该如何优化呢? 下面我们通过一些简单的例子来分析理解这些现象.下面的实验环境为SQL SERVER 2008,如果在不同版本有所区别,欢迎指正.   堆表单索引 首先我们构建我们测试需要实验环境,具体情况如下所示: DROP TABLE TEST    CRE

@dbsnake-用合适的函数索引来避免看似无法避免的全表扫描

昨天听了@dbsnake的SQL方法论,感觉比第一次要更有感觉,希望对实际工作能有帮助. 昨天讲到一处利用reverse函数建立索引,避免全表扫描的case,颇有感触,拿出来试一下. SQL> create table rev (id number, name varchar2(5)); Table created. SQL> select * from rev;         ID NAME ---------- -----          1 abc          2 bc    

Oracle 全表扫描及其执行计划(full table scan)

    全表扫描是Oracle访问数据库表是较为常见的访问方式之一.很多朋友一看到SQL语句执行计划中的全表扫描,就要考虑对其进行修理一番.全表扫描的存在,的确存在可能优化的余地.但事实上很多时候全表扫描也并非是最低效的,完全要看不同的情形与场合,任一方式都是有利有弊的,也就是具体情况要具体分析.本文描述了什么是全表扫描以及何时发生全表扫描,何时全表扫描才低效.  本文涉及到的相关链接:     高水位线和全表扫描      启用 AUTOTRACE 功能     Oracle 测试常用表BIG

使用索引快速全扫描(Index FFS)避免全表扫描的若干场景

使用索引快速全扫描(Index FFS)避免全表扫描(FTS) (文档 ID 70135.1) 什么使用使用Index FFS比FTS好? Oracle 8的Concept手册中介绍: 1. 索引必须包含所有查询中参考到的列. 2. Index FFS只能通过CBO(Index hint强制使用CBO)获得. 3. Index FFS使用hint:/*+ INDEX_FFS() */. Index FFS是在7.3中引入的.在Oracle 7中,它要求初始化参数V733_PLANS_ENABLE

MongoDB Primary 为何持续出现 oplog 全表扫描？

线上某 MongoDB 复制集实例(包含 Primary.Secondary.Hidden 3个节点 ),Primary 节点突然 IOPS 很高,调查后发现,其中 Hidden 处于 RECOVERING 状态,同时 Priamry 上持续有一全表扫描 oplog 的操作,正是这个 oplog 的 COLLSCAN 导致IO很高. 2017-10-23T17:48:01.845+0800 I COMMAND [conn8766752] query local.oplog.rs query: {

高水位线和全表扫描

   高水位线好比水库中储水的水位线,用于描述数据库中段的扩展方式.高水位线对全表扫描方式有着至关重要的影响.当使用delete 操作 表记录时,高水位线并不会下降,随之导致的是全表扫描的实际开销并没有任何减少.本文给出高水位线的描述,如何降低高水位线,以及高水 位线对全表扫描的影响.   一.何谓高水位线    如前所述,类似于水库中储水的水位线.只不过在数据库中用于描述段的扩展方式.     可以将数据段或索引段等想象为一个从左到右依次排开的一系列块.当这些块中未填充任何数据时,高水位线位于

SQL中WHERE变量IS NULL条件导致全表扫描问题的解决方法_MsSql

复制代码 代码如下: SET @SQL = 'SELECT * FROM Comment with(nolock) WHERE 1=1    And (@ProjectIds Is Null or ProjectId = @ProjectIds)    And (@Scores is null or Score =@Scores)' 印象中记得,以前在做Oracle开发时,这种写法是会导致全表扫描的,用不上索引,不知道Sql Server里是否也是一样呢,于是做一个简单的测试1.建立测试用的表结

大幅提升MySQL中InnoDB的全表扫描速度的方法_Mysql

 在 InnoDB中更加快速的全表扫描 一般来讲,大多数应用查询的时候都会用索引,查找很少的几行数据(主键查找或百行内的查询),但有时候我们需要全表查询.典型的全表扫描就是逻辑备份  (mysqldump) 和 online schema changes( 注:在线上对大表 schema 的操作,也是 facebook 的一个开源项目) (SELECT ... INTO OUTFILE).  在 Facebook我们用 mysqldump 来备份数据库. 正如你所知MySql提供两种备份方式,提