mysql 索引 范围扫描,索引范围扫描

上篇说的是唯一扫描

http://mp.weixin.qq.com/s/nVz94rDPAEEWvCXP8KEy-Q

我们继续在上面的表和唯一索引上做DEBUG

三检查信息

1 对象ID

select object_name,object_id,data_object_id from dba_objects where owner=user and  object_name in ('ZFK','ZFK_PK_OBJID');

OBJECT_NAME  OBJECT_IDDATA_OBJECT_ID

ZFK            119734120052

ZFK_PK_OBJID120051120053

OBJECT_ID       十六进制

119734=>1D3B6

120051=>1D4F3

120052=>1D4F4 =>0x0001d4f4

120053=>1D4F5 =>0x0001d4f5

SQL> show array

arraysize 50

select object_id,object_name,object_type from DBA_MONITER.zfk where object_type='INDEX' and OWNER!='SYS' and object_id between 1 and 9999;

已选择217行。

已用时间:  00: 00: 00.04

执行计划

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

Plan hash value: 1077372689

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

| Id  | Operation    | Name   | Rows  | Bytes | Cost (%CPU)| Time   |

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

|   0 | SELECT STATEMENT    |   |94 |  4230 |   142   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| ZFK   |94 |  4230 |   142   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN    | ZFK_PK_OBJID |  7325 |   |17   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_TYPE"='INDEX' AND "OWNER"<>'SYS')

2 - access("OBJECT_ID">=1 AND "OBJECT_ID"<=9999)

统计信息

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

1  recursive calls

0  db block gets

183  consistent gets

0  physical reads

0  redo size

8138  bytes sent via SQL*Net to client

564  bytes received via SQL*Net from client

6  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

217  rows processed

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> oradebug setmypid;

已处理的语句

SQL> oradebug event 10200 trace name context forever,level 1;

已处理的语句

SQL> select object_id,object_name,object_type from DBA_MONITER.zfk where object_type='INDEX' and OWNER!='SYS' and object_id between 1 and 9999;

已选择217行。

已用时间:  00: 00: 00.05

SQL> oradebug event 10200 trace name context off;

已处理的语句

SQL> oradebug tracefile_name;

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_123822.trc

统计stared 关键字 发现183个 这个就是逻辑读

[oracle@svr3 trace]grep "started" orcl_ora_123822.trc |wc

183    1647   12626

下面是TRC文件主要的信息

*** 2017-03-06 16:53:45.494

Processing Oradebug command 'event 10200 trace name context forever,level 1'

*** 2017-03-06 16:53:45.501

Oradebug command 'event 10200 trace name context forever,level 1' console output:

*** 2017-03-06 16:53:55.041

ktrgtc2(): started for block <0x0004 : 0x0100021b> objd: 0x0001d4f5

读主键根块

ktrgtc2(): completed for block <0x0004 : 0x0100021b> objd: 0x0001d4f5

ktrget2(): started for block  <0x0004 : 0x0100021c> objd: 0x0001d4f5

读主键索引的叶块

ktrget3(): completed for  block <0x0004 : 0x0100021c> objd: 0x0001d4f5

ktrget2(): started for block  <0x000d : 0x0300014b> objd: 0x0001d4f4

读数据块

ktrget3(): completed for  block <0x000d : 0x0300014b> objd: 0x0001d4f4

ktrget2(): started for block  <0x000d : 0x0300014c> objd: 0x0001d4f4

读数据块

ktrget3(): completed for  block <0x000d : 0x0300014c> objd: 0x0001d4f4

ktrget2(): started for block  <0x000d : 0x0300014d> objd: 0x0001d4f4

读数据块

ktrget3(): completed for  block <0x000d : 0x0300014d> objd: 0x0001d4f4

ktrget2(): started for block  <0x000d : 0x0300014e> objd: 0x0001d4f4

读数据块

ktrget3(): completed for  block <0x000d : 0x0300014e> objd: 0x0001d4f4

ktrget2(): started for block  <0x000d : 0x0300014f> objd: 0x0001d4f4

读数据块

ktrget3(): completed for  block <0x000d : 0x0300014f> objd: 0x0001d4f4

ktrget2(): started for block  <0x000d : 0x03000150> objd: 0x0001d4f4

读数据块

ktrget3(): completed for  block <0x000d : 0x03000150> objd: 0x0001d4f4

ktrget2(): started for block  <0x0004 : 0x0100021c> objd: 0x0001d4f5

读主键索引的叶块

ktrget3(): completed for  block <0x0004 : 0x0100021c> objd: 0x0001d4f5

ktrget2(): started for block  <0x000d : 0x03000150> objd: 0x0001d4f4

读数据块

ktrget3(): completed for  block <0x000d : 0x03000150> objd: 0x0001d4f4

ktrget2(): started for block  <0x000d : 0x03000151> objd: 0x0001d4f4

读数据块

ktrget3(): completed for  block <0x000d : 0x03000151> objd: 0x0001d4f4

ktrget2(): started for block  <0x0004 : 0x0100021d> objd: 0x0001d4f5

读主键索引的叶块

ktrget3(): completed for  block <0x0004 : 0x0100021d> objd: 0x0001d4f5

ktrget2(): started for block  <0x000d : 0x03000152> objd: 0x0001d4f4

读数据块

ktrget3(): completed for  block <0x000d : 0x03000152> objd: 0x0001d4f4

ktrget2(): started for block  <0x000d : 0x03000153> objd: 0x0001d4f4

读数据块

ktrget3(): completed for  block <0x000d : 0x03000153> objd: 0x0001d4f4

ktrget2(): started for block  <0x000d : 0x03000154> objd: 0x0001d4f4

读数据块

ktrget3(): completed for  block <0x000d : 0x03000154> objd: 0x0001d4f4

ktrget2(): started for block  <0x000d : 0x03000155> objd: 0x0001d4f4

读数据块

ktrget3(): completed for  block <0x000d : 0x03000155> objd: 0x0001d4f4

ktrget2(): started for block  <0x000d : 0x03000156> objd: 0x0001d4f4

读数据块

ktrget3(): completed for  block <0x000d : 0x03000156> objd: 0x0001d4f4

ktrget2(): started for block  <0x000d : 0x03000155> objd: 0x0001d4f4

读数据块

ktrget3(): completed for  block <0x000d : 0x03000155> objd: 0x0001d4f4

ktrget2(): started for block  <0x000d : 0x03000156> objd: 0x0001d4f4

读数据块

ktrget3(): completed for  block <0x000d : 0x03000156> objd: 0x0001d4f4

ktrget2(): started for block  <0x000d : 0x03000157> objd: 0x0001d4f4

读数据块

ktrget3(): completed for  block <0x000d : 0x03000157> objd: 0x0001d4f4

ktrget2(): started for block  <0x000d : 0x03000159> objd: 0x0001d4f4

读数据块

ktrget3(): completed for  block <0x000d : 0x03000159> objd: 0x0001d4f4

基本上是读个索引块后,就去读表块,接着继续访问索引块.

所以我们基本确定索引范围扫描基本动作是: 索引根节点->叶节点->表块->叶节点->表块....................

所以聚集因子在这里深刻影响着. 所谓聚集因子就是表的数据存放的无序程度和索引的有序程度的对比值. 说明要重复读取相同的数据块的次数问题。会导致性能下降！！

有人说 当array size 影响着逻辑读, 影响回表的次数。我这次实验把

SQL> set array 300  设置成300后 也发现读表是没什么规律的，还有发现读了两次相同的索引叶块。

如果本号文对你有意义可以下我打赏，金额多少无所谓！