常见的基于B-Tree索引的扫描
以下展示了一个索引跳跃扫描的示例。
【示例3】
emp表中,对JOB和ename列创建复合索引,顺序为(JOB,ename),查询时where过滤条件使用了后面的ename列,如下所示:
--创建复合索引
create index ind_EMP_JENAME ON scott.emp(JOB,ename)
--执行查询,条件中未使用复合索引中的前导列
select empno,ename
from scott.emp
where ename='ALLEN'
执行计划如下:
索引跳跃扫描的性能不如扫描前导列高,其一般在前导列键值比较少(前导列的选择性较低,或者复合索引中CBO计算后发现前几个列的选择性均较低)的情况下,会有较好的性能。
对于索引跳跃扫描的调优,可采取以下方式将调整为索引范围扫描(INDEX RANGE INDEX)。
-
为where条件列单独创建索引。
-
调整复合索引列中的顺序,使得where条件中的列作为复合索引中的前导列。
-
调整复合索引,尽量将选择性较高的列作为前导列。
关于表扫描方式的调优总结
-
1. Oracle 11g中,在对一个大表进行全表扫描时,将表直接读入PGA,绕过SGA中的buffer cache,此时的等待事件为“direct path read”。
这种方式将导致统计信息不准确,因此一般禁用此方式,即:
alter system set “_serial_direct_read”=false;
-
2. 尽量避免在Oracle中定义clob类型的列,对该字段执行全表扫描时将导致性能严重下降(clob类型的列单独存放在一个段中)。
可以考虑将clob类型的列拆分为多个varchar2(4000)的列,或将这类的值存放到nosql数据库中,例如mongodb。
-
3. 如果表中有部分块已经缓存在buffer cache中,执行全表扫描时,扫描到已经被缓存的块所在的区时,将引起I/O中断。
如果一个表不同的区有大量块缓存在buffer cache中,此时,全表扫描的性能将严重下降(例如,较多的“db file scattered read”等待)。
-
4. 如果表中正在执行较大的事务,此时对该表执行全表扫描,会从undo日志读取部分数据,这种读取方式只能以单块读的方式进行,使得全表扫描的效率非常低下(出现较多的db file sequential read等待)。
建议使用批量游标方式处理较大的事务。
-
5. 在表数据较多的极端情况下,对于没有指定过滤条件(无where条件)的全表扫描,检查查询涉及的字段数(select的字段),如果涉及的字段数不多,可以将这些字段创建成一个复合索引,将全表扫描(TABLE ACCESS FULL)调优为索引快速全扫描(INDEX FAST FULL SCAN)。
-
6.在表数据较多的情况下,如果指定了过滤条件(有where条件),出现了全表扫描操作,可按按以下步骤调整:
(1)使用“select count(*) from <table>”查看表中的记录总数;
(2)使用“select count(*) from <table> where <col>...”查看该过滤条件下,返回的记录数;
(3)如果返回的记录数在表总记录数的5%以内,可以考虑在相应的过滤列上建立索引,如果有多个过滤条件列,需要创建包含这些列的复合索引,且将选择性高的列作为前缀列,同时where条件中的过滤列也应与创建的复合索引一致或至少包含前缀列;
如果索引已经创建,但仍执行全表扫描,需要对相关表的统计信息进行检查,特别是直方图信息,必要时重新收集统计信息。
(4)如果返回的记录数超过表总记录数的5%,如果select获取的字段不多,可以将这些字段和where条中的字段创建复合索引,通过相关索引访问的方式调优全表扫描。
创建原则为:where条件中的过滤字段作为前缀列,表连接涉及的字段放于中部,select中的字段位于最后部分。
如果select中的字段较多,则只能执行全表扫描操作。
-
7.如果表中包含大量空或接近与空的块,将影响全表扫描的性能,因为要读取块才可以确定其中是否包含数据。
一个导致表产生大量分布稀疏块的常见场景是当表删除多于插入时。
虽然表中可以删除大量的数据,但执行全表扫描所读取的块并未减少,因为HWM线未降低。
此时,需要降低HWM。
-
8. 对于较大的表,如果只能执行全表扫描操作,此时应考虑将较大的表进行分区,查询时采取分区剪裁的方式。
-
9.过滤条件(where)中对于单列索引列的IS NULL操作,将无法使用索引,此时将使用全表扫描,如果要调优为索引扫描,可将该单列索引与0(或其他整数值)组合创建复合索引,即:(col,0)。
此时,将采用索引复合扫描(INDEX RANGE SCAN)的方式。
-
10.在条件允许的情况下,通过索引覆盖可以进一步消除索引扫描后的回表操作。
最后: 下方这份完整的软件测试视频教程已经整理上传完成,需要的朋友们可以自行领取【保证100%免费】
软件测试面试文档
我们学习必然是为了找到高薪的工作,下面这些面试题是来自阿里、腾讯、字节等一线互联网大厂最新的面试资料,并且有字节大佬给出了权威的解答,刷完这一套面试资料相信大家都能找到满意的工作。