欢迎光临
我们一直在努力

索引扫描可能不如全表扫描的场景的理解__纯粹数据量而言,不涉及CLUSTERING_FACTOR

多块读的场景
Full Table Scan –全表扫描
Index Fast Full Scans  –索引快速全扫描

单块读的场景
Rowid Scans –直接通过Rowid获取
Index Unique Scans –索引唯一扫描
Index Range Scans –索引局部扫描
Index Skip Scans –索引跳跃扫描
Index Full Scans –索引全扫描

全表扫描(Full Table Scans FTS)
为实现全表扫描,Oracle 读取表中所有行,并检查每一行是否满足语句的WHERE 限制条件,全表扫描时一次I/O 能读取多个数据库块(db_file_multiblock_read_count参数设定),而不是只读取一个数据块,即多块读,这极大的减少了I/O 总次数,提高了系统的吞吐量。

索引扫描(Index Scan或index lookup)
我们先通过index 查找到数据对应的rowid 值(对于非唯一索引可能返回多个rowid 值),然后根据rowid 直接从表中得到具体的数据,这种查找方式称为索引扫描或索引查找(indexlookup)。
一个rowid 唯一的表示一行数据,该行对应的数据块是通过一次I/0 得到的,在此情况下该次i/o 只会读取一个数据库块,即单块读。

在索引中,除了存储每个索引的值外,索引还存储具有此值的行对应的ROWID 值。
索引扫描可以由2 步组成:
1 扫描索引得到对应的rowid 值。
2 通过找到的rowid 从表中读出具体的数据。
每步都是单独的一次I/O,但是对于索引,由于经常使用,绝大多数都已经CACHE 到内存中,所以第1 步的I/O 经常是逻辑I/O,即数据可以从内存中得到。但是对于第2 步来说,如果表比较大,则其数据不可能全在内存中,所以其I/O 很有可能是物理I/O,这是一个机械操作,相对逻辑I/O 来说,是极其费时间的。

为什么多块读比单块读快,即为什么有时全表扫描比索引扫描还快
因为逻辑读物理读单位是次块(一次读取相同或不同块数情况下,看读取了多少次,当然逻辑读没有IO,只有物理读有IO),数据总块数一样的情况下,多块读的话,读取次数就少,逻辑读或物理读就少了,而全表扫描就是多块读。
个人理解:一个IO就是一个IO,不管多块,还是单块,都是一个次IO。
就好比你花1块钱买了1颗糖,有人1块能买10颗糖,消耗的成本其实都是1块钱。
再比如一秒内要读完10个块,单块读的话1次读一个块,需要10次,多块读的话假如1次读10个块,需要1次。虽然两者产生的IO吞吐量都是一样的,但是前者的IOPS是10,后者的IOPS是1,而一次IO的开启和结束是要消耗操作系统很多资源的。

案例1
假定多块读,一次读取5个数据库块,一张大表10000个数据库块,100个索引块,如果要取出的数据大于总量的20%,使用索引扫描,因为两步的每一步都是单独的一次I/O,且每一次I/O都是单块读只能读取一个数据库块,所以要扫描的次数=索引块的数量+全部数据块的20%的数量=100+10000*20%=2100次,如果都是物理读那么其中IO次数就是2100;使用全表扫描,一次就读取5个数据块,所以要扫描的次数=全部数据库/5=10000/5=2000,如果都是物理读那么IO次数就是2000。

案例2
假设一张表含有10万行数据——–100000行
我们要读取其中20%(2万)行数据—-20000行
这张表一共有10000个数据块——–10000块(一个块10行,每行800字节)
通过索引读取20000行数据 = 约20000个table access by rowid = 需要处理20000个块来执行这个查询,但是,整个表只有10000个块,所以:如果按照索引读取全部的数据的20%相当于将整张表平均读取了2次。

当然也不能说索引读取行数大于整表的块数,那都是全表扫描了,还要考虑读取的块是逻辑读还是物理读。
如果都是逻辑读,肯定是
索引扫描次数大于全表扫描次数
如果都是物理读,不CLUSTERING_FACTOR极端的情况下,肯定是索引扫描小于全表扫描
比如上面案例2索引扫描虽然要处理20000个块,但是这20000个块,肯定不是都是物理读,其中物理读IO正常情况下大概也就2000个块(占整表块的20%),全表扫描的话如果都是物理读那么IO是10000个块。
当然如果这20%的数据分布极端散列,分布在了表的所有块上, 也就是10000个块上,如果索引扫描和全表扫描都是物理读,那么索引扫描的IO=100+10000,全表扫描的IO=10000

所以如果较大表进行索引扫描,取出的数据如果大于总量的5%—10%,使用索引扫描可能效果还不如全表扫描

赞(0)
【声明】:本博客不参与任何交易,也非中介,仅记录个人感兴趣的主机测评结果和优惠活动,内容均不作直接、间接、法定、约定的保证。访问本博客请务必遵守有关互联网的相关法律、规定与规则。一旦您访问本博客,即表示您已经知晓并接受了此声明通告。