search 2013 adfgs
作者:Sky.Jian | 可以任意转载, 但转载时务必以超链接形式标明文章原始出处 和 作者信息 及 版权声明
链接:http://isky000.com/database/index_scan_or_full_table_scan | del.icio.us | Twitter it

在大多数时候,大家都会认为Sql语句中走Index Scan比Full Table Scan快,我前面也走进了这样的误区(对Index Scan的理解不够)。这两天重新复习了一下这方面内容,并整理了一下。

当Oracle Optimizer(优化器)没有选择Index Scan而选择了Full Table Scan的时候一般会是由两种情况:
1、表没有Analyze,Optimizer得不到statistics(统计)数据,无法评估而选择了Full Table Scan
2、Optimizer通过得到的statistics数据后评估认为Full Table Scan将比Index Scan更快

对于第一种情况当然很好理解,而且只要运行简单的Analyze命令就好了,然后就是Oracle Optimizer按照他的一系列算法来进行选择了,到这里其实也同样可能会遇到第二种情况,关键还是要看Optimizer的评估结果。

Oracle Optimizer在评估一个Index的Cost(开销)时候,有两个主要的关键指标。
Oracle官方语称为:CF(Clustering Factor)和FF(Filtering Factor)。
用通俗一点的话来理解,CF就是指每读入一个Index Block,对应需要读多少个Data Block。而FF呢就是该Sql最终需要读取的记录集占到了整个Table中记录总条数的百分比。

由于通过Index来读取数据的时候是需要先读取Index Block然后再通过Rowid读取相应的Data Block,每读取一条记录都需要读取一次数据块(这里表述有点问题,已经在下面的comments中解释),这样极有可能出现对同一个Data Block读取多次的情况。使用索引读取数据需要读取的Block数目据说公式大约是这样的(只是据说):
FF ×(CF × Index Blocks)

而对于Full Table Scan,是通过db_file_multiblock_read_count设定的值进行连续读取整个Table的所有(HWM以下)数据块。

所以当需要读取表中数据越多的时候(也即是FF值比较大的时候),Index Scan花销自然也会越大。而CF值主要受到索引中数据的排列方式影响,通常在 索引刚建立时,索引中的记录与表中的记录有良好的对应关系,CF 都很小;在表经过大量的插入、修改后,这种对应关系越来越乱,CF 也越来越大。这时候就需要Rebuild Index。如果出现了一个Sql语句在最初时候走了Index而运行一段时间后突然变成了Full Table Scan的情况的时候,一般都是由于CF值变大的缘故。通过Rebuild Index就可以解决。而FF 则是Oracle 根据 statistics数据所做的估计,所以需要经常Analyze Table来更新表的statistics来让Optimizer做出正确的估计而得出最佳的执行计划。

, , ,

已经有20个回复

  1. sopher Says @ 06-08-1 6:45 pm

    “每读取一条记录都需要读取一次数据块,这样极有可能出现对同一个Data Block读取多次的情况。”

    这个怎么理解啊?不是很明白

  2. sopher Says @ 06-08-1 6:46 pm

    哈哈,发现你blog的一个小bug
    Related Posts只有在有comment的情况下才能看的见:)

  3. 朝阳 Says @ 06-08-2 10:01 am

    Bug已经修复 :)

    上面文中的“每读取一条记录都需要读取一次数据块,这样极有可能出现对同一个Data Block读取多次的情况”是我表述有些问题。可以这样理解:
    在走Index Scan的时候,实际上是按照Index的键值顺序来读取表中数据的,所以当Index的键值顺序和数据存放的物理顺序(也即是说并不是按依键值那样的顺序相邻的记录存放在同一个block上)的时候,就会出现回头重新读取以前已经读取并处理过的Block(目的只是为了取其中某一条或者几条记录)。因为Oracle在根据键值读取记录的时候当后一个键值指向的记录不在当前CR的block上面的时候,就会丢弃当前block,然后重新构建一个CR来取得该键值指向的记录。当继续处理后面的键值的时候又遇到某一个或者几个键值指向了前面已经读取过的block的时候,Oracle不得不重新读取该block来在此构建一个CR(这里就出现了重复读取)。

  4. BitterCoffee Says @ 06-08-2 9:19 pm

    跑过来留个爪印,加油啊~
    ps:谢谢你的Java书

  5. 朝阳 Says @ 06-08-3 10:04 pm

    to BitterCoffee:欢迎常来,不用客气 :)

  6. zergduan Says @ 09-05-18 10:39 am

    最后一段关于CF(Clustering Factor)的结论我不认同。rebuild index不会改变cf的。cf是指对于一个索引键,在table中的分布。这个是由table是否是顺序存储数据有关,如果table中的数据存储方式过于离散,cf就会很大。rebuild索引通过减少空块或合并节点来减少叶子节点,甚至降低高度;实现索引效率提高。但是减少cf的效果微乎其微。

Trackbacks & Pingbacks

  • Oraus.net | physical reads and Logical reads

    [...] 看了朝阳写的索引扫描还是全表扫描(Index Scan Or Full Table Scan)?以后,请教了朝阳几个问题。 [...]

  • Sky.Jian 朝阳的天空 » DB Bocks gets,Consistent gets And Physical reads

    [...] 通过前面关于索引扫描还是全表扫描(Index Scan Or Full Table Scan)?的讨论又发现了自己基础概念不清晰的一个大问题: DB block gets,Consistent gets,Physical reads与Logical reads各自具体表示的是什么以及之间的的关系到底是怎样。 [...]

  • » MySQL 数据库性能优化之索引优化 - Sky.Jian – i Sky000 - 简朝阳

    [...] 如果我们还有这样一个需求(虽然基本不可能):“帮我将图书馆中所有的计算机图书借来”。朋友如果通过索引来找,每次都到索引柜找到计算机书籍所在的区域,然后从书架上搬下一格(假设只能以一格为单位从书架上取下,类比数据库中以block/page为单位读取),取出第一本,然后再从索引柜找到计算机图书所在区域,再搬下一格,取出一本… 如此往复直至取完所有的书。如果他不通过索引来找又会怎样呢?他需要从地一个书架一直往后找,当找到计算机的书,搬下一格,取出所有计算机的书,再往后,直至所有书架全部看一遍。在这个过程中,如果计算机类书籍较多,通过索引来取所花费的时间很可能要大于直接遍历,因为不断往复的索引翻阅所消耗的时间会非常长。(延伸阅读:这里有一篇以前写的关于Oracle的文章,索引扫描还是全表扫描(Index Scan Or Full Table Scan)) 所以,当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候,使用索引并不一定优于全表扫描。 [...]

  • MySQL 数据库性能优化之索引优化 | vicenteforever

    [...] 如果我们还有这样一个需求(虽然基本不可能):“帮我将图书馆中所有的计算机图书借来”。朋友如果通过索引来找,每次都到索引柜找到计算机书籍所在的区域,然后从书架上搬下一格(假设只能以一格为单位从书架上取下,类比数据库中以block/page为单位读取),取出第一本,然后再从索引柜找到计算机图书所在区域,再搬下一格,取出一本… 如此往复直至取完所有的书。如果他不通过索引来找又会怎样呢?他需要从地一个书架一直往后找,当找到计算机的书,搬下一格,取出所有计算机的书,再往后,直至所有书架全部看一遍。在这个过程中,如果计算机类书籍较多,通过索引来取所花费的时间很可能要大于直接遍历,因为不断往复的索引翻阅所消耗的时间会非常长。(延伸阅读:这里有一篇以前写的关于Oracle的文章,索引扫描还是全表扫描(Index Scan Or Full Table Scan)) 所以,当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候,使用索引并不一定优于全表扫描。 [...]

  • MySQL 数据库性能优化之索引优化[转] | vicenteforever

    [...] 如果我们还有这样一个需求(虽然基本不可能):“帮我将图书馆中所有的计算机图书借来”。朋友如果通过索引来找,每次都到索引柜找到计算机书籍所在的区域,然后从书架上搬下一格(假设只能以一格为单位从书架上取下,类比数据库中以block/page为单位读取),取出第一本,然后再从索引柜找到计算机图书所在区域,再搬下一格,取出一本… 如此往复直至取完所有的书。如果他不通过索引来找又会怎样呢?他需要从地一个书架一直往后找,当找到计算机的书,搬下一格,取出所有计算机的书,再往后,直至所有书架全部看一遍。在这个过程中,如果计算机类书籍较多,通过索引来取所花费的时间很可能要大于直接遍历,因为不断往复的索引翻阅所消耗的时间会非常长。(延伸阅读:这里有一篇以前写的关于Oracle的文章,索引扫描还是全表扫描(Index Scan Or Full Table Scan)) 所以,当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候,使用索引并不一定优于全表扫描。 [...]

  • MySQL 数据库性能优化之索引优化 | phper之家

    [...] 如果我们还有这样一个需求(虽然基本不可能):“帮我将图书馆中所有的计算机图书借来”。朋友如果通过索引来找,每次都到索引柜找到计算机书籍所在的区域,然后从书架上搬下一格(假设只能以一格为单位从书架上取下,类比数据库中以block/page为单位读取),取出第一本,然后再从索引柜找到计算机图书所在区域,再搬下一格,取出一本… 如此往复直至取完所有的书。如果他不通过索引来找又会怎样呢?他需要从地一个书架一直往后找,当找到计算机的书,搬下一格,取出所有计算机的书,再往后,直至所有书架全部看一遍。在这个过程中,如果计算机类书籍较多,通过索引来取所花费的时间很可能要大于直接遍历,因为不断往复的索引翻阅所消耗的时间会非常长。(延伸阅读:这里有一篇以前写的关于Oracle的文章,索引扫描还是全表扫描(Index Scan Or Full Table Scan))所以,当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候,使用索引并不一定优于全表扫描。 [...]

  • MySQL 数据库性能优化之索引优化 - 北极狐的博客-专注于互联网-电子商务-SNS-系统架构-服务器集群-DBA

    [...] 如果我们还有这样一个需求(虽然基本不可能):“帮我将图书馆中所有的计算机图书借来”。朋友如果通过索引来找,每次都到索引柜找到计算机书籍所在的区域,然后从书架上搬下一格(假设只能以一格为单位从书架上取下,类比数据库中以block/page为单位读取),取出第一本,然后再从索引柜找到计算机图书所在区域,再搬下一格,取出一本… 如此往复直至取完所有的书。如果他不通过索引来找又会怎样呢?他需要从地一个书架一直往后找,当找到计算机的书,搬下一格,取出所有计算机的书,再往后,直至所有书架全部看一遍。在这个过程中,如果计算机类书籍较多,通过索引来取所花费的时间很可能要大于直接遍历,因为不断往复的索引翻阅所消耗的时间会非常长。(延伸阅读:这里有一篇以前写的关于Oracle的文章,索引扫描还是全表扫描(Index Scan Or Full Table Scan)) 所以,当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候,使用索引并不一定优于全表扫描。 [...]

  • MySQL数据库性能优化之索引优化【转】 | 早睡晚起

    [...] 如果我们还有这样一个需求(虽然基本不可能):“帮我将图书馆中所有的计算机图书借来”。朋友如果通过索引来找,每次都到索引柜找到计算机书籍所在的区域,然后从书架上搬下一格(假设只能以一格为单位从书架上取下,类比数据库中以block/page为单位读取),取出第一本,然后再从索引柜找到计算机图书所在区域,再搬下一格,取出一本… 如此往复直至取完所有的书。如果他不通过索引来找又会怎样呢?他需要从地一个书架一直往后找,当找到计算机的书,搬下一格,取出所有计算机的书,再往后,直至所有书架全部看一遍。在这个过程中,如果计算机类书籍较多,通过索引来取所花费的时间很可能要大于直接遍历,因为不断往复的索引翻阅所消耗的时间会非常长。(延伸阅读:这里有一篇以前写的关于Oracle的文章,索引扫描还是全表扫描(Index Scan Or Full Table Scan)) 所以,当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候,使用索引并不一定优于全表扫描。 [...]

  • 北极狐的博客 » MySQL 数据库性能优化之索引优化

    [...] 如果我们还有这样一个需求(虽然基本不可能):“帮我将图书馆中所有的计算机图书借来”。朋友如果通过索引来找,每次都到索引柜找到计算机书籍所在的区域,然后从书架上搬下一格(假设只能以一格为单位从书架上取下,类比数据库中以block/page为单位读取),取出第一本,然后再从索引柜找到计算机图书所在区域,再搬下一格,取出一本… 如此往复直至取完所有的书。如果他不通过索引来找又会怎样呢?他需要从地一个书架一直往后找,当找到计算机的书,搬下一格,取出所有计算机的书,再往后,直至所有书架全部看一遍。在这个过程中,如果计算机类书籍较多,通过索引来取所花费的时间很可能要大于直接遍历,因为不断往复的索引翻阅所消耗的时间会非常长。(延伸阅读:这里有一篇以前写的关于Oracle的文章,索引扫描还是全表扫描(Index Scan Or Full Table Scan)) 所以,当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候,使用索引并不一定优于全表扫描。 [...]

  • PHP-Boss 北极狐的博客 » MySQL数据库性能优化(三)索引优化

    [...] 如果我们还有这样一个需求(虽然基本不可能):“帮我将图书馆中所有的计算机图书借来”。朋友如果通过索引来找,每次都到索引柜找到计算机书籍所在的区域,然后从书架上搬下一格(假设只能以一格为单位从书架上取下,类比数据库中以block/page为单位读取),取出第一本,然后再从索引柜找到计算机图书所在区域,再搬下一格,取出一本… 如此往复直至取完所有的书。如果他不通过索引来找又会怎样呢?他需要从地一个书架一直往后找,当找到计算机的书,搬下一格,取出所有计算机的书,再往后,直至所有书架全部看一遍。在这个过程中,如果计算机类书籍较多,通过索引来取所花费的时间很可能要大于直接遍历,因为不断往复的索引翻阅所消耗的时间会非常长。(延伸阅读:这里有一篇以前写的关于Oracle的文章,索引扫描还是全表扫描(Index Scan Or Full Table Scan)) 所以,当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候,使用索引并不一定优于全表扫描。 [...]

  • MySQL 数据库性能优化之索引优化 « 良宏工作室

    [...] 如果我们还有这样一个需求(虽然基本不可能):“帮我将图书馆中所有的计算机图书借来”。朋友如果通过索引来找,每次都到索引柜找到计算机书籍所在的区域,然后从书架上搬下一格(假设只能以一格为单位从书架上取下,类比数据库中以block/page为单位读取),取出第一本,然后再从索引柜找到计算机图书所在区域,再搬下一格,取出一本… 如此往复直至取完所有的书。如果他不通过索引来找又会怎样呢?他需要从地一个书架一直往后找,当找到计算机的书,搬下一格,取出所有计算机的书,再往后,直至所有书架全部看一遍。在这个过程中,如果计算机类书籍较多,通过索引来取所花费的时间很可能要大于直接遍历,因为不断往复的索引翻阅所消耗的时间会非常长。(延伸阅读:这里有一篇以前写的关于Oracle的文章,索引扫描还是全表扫描(Index Scan Or Full Table Scan)) 所以,当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候,使用索引并不一定优于全表扫描。 [...]

  • MySQL数据库性能优化-索引优化 | 嘿,高波

    […] 如果我们还有这样一个需求(虽然基本不可能):“帮我将图书馆中所有的计算机图书借来”。朋友如果通过索引来找,每次都到索引柜找到计算机书籍所在的区域,然后从书架上搬下一格(假设只能以一格为单位从书架上取下,类比数据库中以block/page为单位读取),取出第一本,然后再从索引柜找到计算机图书所在区域,再搬下一格,取出一本… 如此往复直至取完所有的书。如果他不通过索引来找又会怎样呢?他需要从地一个书架一直往后找,当找到计算机的书,搬下一格,取出所有计算机的书,再往后,直至所有书架全部看一遍。在这个过程中,如果计算机类书籍较多,通过索引来取所花费的时间很可能要大于直接遍历,因为不断往复的索引翻阅所消耗的时间会非常长。(延伸阅读:这里有一篇以前写的关于Oracle的文章,索引扫描还是全表扫描(Index Scan Or Full Table Scan)) 所以,当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候,使用索引并不一定优于全表扫描。 […]

  • MySQL学习笔记(23)———–索引优化 - 数据库 - 阿里欧歌

    […] 如果我们还有这样一个需求(虽然基本不可能):“帮我将图书馆中所有的计算机图书借来”。朋友如果通过索引来找,每次都到索引柜找到计算机书籍所在的区域,然后从书架上搬下一格(假设只能以一格为单位从书架上取下,类比数据库中以block/page为单位读取),取出第一本,然后再从索引柜找到计算机图书所在区域,再搬下一格,取出一本… 如此往复直至取完所有的书。如果他不通过索引来找又会怎样呢?他需要从地一个书架一直往后找,当找到计算机的书,搬下一格,取出所有计算机的书,再往后,直至所有书架全部看一遍。在这个过程中,如果计算机类书籍较多,通过索引来取所花费的时间很可能要大于直接遍历,因为不断往复的索引翻阅所消耗的时间会非常长。(延伸阅读:这里有一篇以前写的关于Oracle的文章,索引扫描还是全表扫描(Index Scan Or Full Table Scan)) 所以,当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候,使用索引并不一定优于全表扫描。 […]

  • MySQL 数据库性能优化之索引优化 | 大话运维

    […] 如果我们还有这样一个需求(虽然基本不可能):“帮我将图书馆中所有的计算机图书借来”。朋友如果通过索引来找,每次都到索引柜找到计算机书籍所在的区域,然后从书架上搬下一格(假设只能以一格为单位从书架上取下,类比数据库中以block/page为单位读取),取出第一本,然后再从索引柜找到计算机图书所在区域,再搬下一格,取出一本… 如此往复直至取完所有的书。如果他不通过索引来找又会怎样呢?他需要从地一个书架一直往后找,当找到计算机的书,搬下一格,取出所有计算机的书,再往后,直至所有书架全部看一遍。在这个过程中,如果计算机类书籍较多,通过索引来取所花费的时间很可能要大于直接遍历,因为不断往复的索引翻阅所消耗的时间会非常长。(延伸阅读:这里有一篇以前写的关于Oracle的文章,索引扫描还是全表扫描(Index Scan Or Full Table Scan)) 所以,当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候,使用索引并不一定优于全表扫描。 […]

看完了要说点啥么?