189 8069 5689

如何避免回表oracle,如何减少回表

回表与覆盖索引,索引下推

通俗的讲就是,如果索引的列在 select 所需获得的列中(因为在 mysql 中索引是根据索引列的值进行排序的,所以索引节点中存在该列中的部分值)或者根据一次索引查询就能获得记录就不需要回表,如果 select 所需获得列中有大量的非索引列,索引就需要到表中找到相应的列的信息,这就叫回表。

成都创新互联拥有一支富有激情的企业网站制作团队,在互联网网站建设行业深耕十载,专业且经验丰富。十载网站优化营销经验,我们已为上1000+中小企业提供了网站制作、成都网站设计解决方案,按需求定制制作,设计满意,售后服务无忧。所有客户皆提供一年免费网站维护!

InnoDB聚集索引的叶子节点存储行记录,因此, InnoDB必须要有,且只有一个聚集索引:

(1)如果表定义了主键,则PK就是聚集索引;

(2)如果表没有定义主键,则第一个非空唯一索引(not NULL unique)列是聚集索引;

(3)否则,InnoDB会创建一个隐藏的row-id作为聚集索引;

先创建一张表,sql 语句如下:

然后,我们再执行下面的 SQL 语句,插入几条测试数据。

假设,现在我们要查询出 id 为 2 的数据。那么执行 select * from xttblog where ID = 2; 这条 SQL 语句就不需要回表。原因是根据主键的查询方式,则只需要搜索 ID 这棵 B+ 树。主键是唯一的,根据这个唯一的索引,MySQL 就能确定搜索的记录。

但当我们使用 k 这个索引来查询 k = 2 的记录时就要用到回表。select * from xttblog where k = 2; 原因是通过 k 这个普通索引查询方式,则需要先搜索 k 索引树,然后得到主键 ID 的值为 1,再到 ID 索引树搜索一次。这个过程虽然用了索引,但实际上底层进行了两次索引查询,这个过程就称为回表。

也就是说,基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。

我这里表里的数据量比较少,如果数据量大的话,你能很明显的看出两次查询所用的时间,很明显使用主键查询效率更高。

更多如下图:

(1)先通过普通索引定位到主键值id=5;

(2)在通过聚集索引定位到行记录;

这就是所谓的回表查询,先定位主键值,再定位行记录,它的性能较扫一遍索引树更低。

使用聚集索引(主键或第一个唯一索引)就不会回表,普通索引就会回表。

只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快。

explain的输出结果Extra字段为Using index时,能够触发索引覆盖。

例子

第一个sql:

select id,name from user where name='shenjian';

Extra:Using index。

第二个sql:

select id,name,sex from user where name='shenjian';

能够命中name索引, 索引叶子节点存储了主键id,没有储存sex,sex字段必须回表查询才能获取到 ,不符合索引覆盖,需要再次通过id值扫描聚集索引获取sex字段,效率会降低。

Extra:Using index condition。

如果把(name)单列索引升级为联合索引(name, sex)就不同了。

可以看到:

select id,name ... where name='shenjian';

select id,name,sex ... where name='shenjian';

单列索升级为联合索引(name, sex)后,索引叶子节点存储了主键id,name,sex ,都能够命中索引覆盖,无需回表。

画外音,Extra:Using index。

场景1:全表count查询优化

原表为:

user(PK id, name, sex);

直接:

select count(name) from user;

不能利用索引覆盖。

添加索引:

alter table user add key(name);

就能够利用索引覆盖提效。

场景2:列查询回表优化

这个例子不再赘述,将单列索引(name)升级为联合索引(name, sex),即可避免回表。

场景3:分页查询

将单列索引(name)升级为联合索引(name, sex),也可以避免回表。

假设有这么个需求,查询表中“名字第一个字是张,性别男,年龄为10岁的所有记录”。那么,查询语句是这么写的:

根据前面说的“最左前缀原则”,该语句在搜索索引树的时候,只能匹配到名字第一个字是‘张’的记录(即记录ID3),接下来是怎么处理的呢?当然就是从ID3开始,逐个回表,到主键索引上找出相应的记录,再比对age和ismale这两个字段的值是否符合。

但是!MySQL 5.6引入了索引下推优化,可以在索引遍历过程中, 对索引中包含的字段先做判断,过滤掉不符合条件的记录,减少回表字数 。

下面图1、图2分别展示这两种情况。

图 1 中,在 (name,age) 索引里面我特意去掉了 age 的值, 这个过程 InnoDB 并不会去看 age 的值 ,只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此,需要回表 4 次。

图 2 跟图 1 的区别是,InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10,对于不等于 10 的记录,直接判断并跳过。在我们的这个例子中,只需要对 ID4、ID5 这两条记录回表取数据判断,就只需要回表 2 次。

如果没有索引下推优化(或称ICP优化),当进行索引查询时, 首先根据索引来查找记录,然后再根据where条件来过滤记录 ;在支持ICP优化后,MySQL会在取出索引的同时, 判断是否可以进行where条件过滤再进行索引查询 ,也就是说提前执行where的部分过滤操作,在某些场景下,可以大大减少回表次数,从而提升整体性能。

Oracle 索引的一个小问题

你这个是联合索引,a2索引本身

它是当你有前导列时,条件中只有前导列这个条件时也会用到组合索引,不用两个列作为条件。

比如

(A,B)建了组合索引

当WHERE A='sf'时会用到索引的

而WHERE B='sd'是不会用到组合索引的。

如果联合索引中的多个字段都在where谓词中出现了,则联合索引效率比单列索引高,因为通过多个条件可以从索引中过滤得到更少的记录条数,也就减少了需要回表扫描的次数,甚至可以直接在联合索引中得到所查的所有结果,则不再需要回表。

oracle中数据是怎样前滚和回滚的

保持数据一致性和完整性,是每一款成功商业数据库软件都必须要做到的基本要求。从故障中恢复,保证ACID原则,保证事务完整性,一直是Oracle数据库核心功能组成部分。本篇主要介绍Oracle实例意外终止(断电或者强制关闭)之后,重新启动时发生的恢复过程,也可以称作“前滚和回滚”。

基础知识说明

为了更明确的说明问题,笔者首先介绍一下本文涉及到的一些重要知识。

数据库实例失败

我们经常说的数据库服务器failure是有多层含义的。Oracle数据库是一个由多进程组件共同构成的结构体系。最重要的部分包括监听器、Oracle数据库实例两个部分,当然还包括各类文件,更广义的还有硬件和操作系统OS。不同部分的Failure现象和处理方法都有所不同。本文所阐述的过程是Oracle实例失败后的自动恢复过程。

在实例失败的时候,往往是突然性的终止。此时Oracle数据库可能在进行一系列完成或者未完成的事务。实例失败恢复,就是要将这些状态进行还原,恢复到数据完整性的状态。

写日志(RedoLog)在先机制

Oracle数据库是采用“日志在先”机制的。当我们对数据库数据进行修改时,并不是立即将修改写入到文件中,而是写入到共享内存SGA空间中的BufferCache里。同时,将修改的日志不断的写入到SGA中另一块Log Buffer缓存中。有一个后台进程LGWR不断的将LogBuffer缓存中的日志内容写入到online redo log文件中。

写入LogBuffer缓存和LGWR写入文件的过程是异步进行的。那么LGWR会在哪些情况下将日志缓冲区(全部内容)转储到日志文件呢?如下:--参考OCA认证考试指南(1Z0-052,P40)ü 用户进行直接的commit操作;

ü RedoBuffer数据超过1/3;

ü DBWn启动,将BufferCache中的脏数据写入到文件中;ü 距离上次LGWR写入操作超过三秒(三秒超时,DBWn每三秒钟会对一些缓冲区清理一次,这个时候,刚好符合触发LGWR的第三点);而数据文件写入进程DBWn工作的触发点(此处注意:DBWn会将高速缓冲区的脏缓冲区,即脏数据块写入数据文件,而不是缓冲区里头的全部内容---参考OCA认证考试指南(1Z0-052,P38))。

因为考虑到磁盘I/O会降低性能,DBWn采用的是极懒算法执行写入。如果对于经常变脏的缓冲区,即这边缓冲区处于十分忙碌的状态,那么DBWn不会将缓冲区写入磁盘的。反而一段时间来,任何会话都未曾关注的一些缓冲区,DBWn会将其写入到磁盘。因此DBWn写脏缓冲区比较平缓和低频率。但如果出现检查点的情况例外:DBWn会将所有脏缓冲区全部写入磁盘。---参考OCA认证考试指南(1Z0-052,P38中,P39)。

ü 当BufferCache中没有任何可用缓冲区;ü 脏缓冲区过多;

ü 遇到三秒超时(DBWn每三秒钟会对一些缓冲区清理一次)ü 遇到检查点

综合DBWn和LGWR工作的特点,我们可以得到日志文件的几个特点:

首先,日志文件的写入是很频繁的。LGWR会不断将日志信息从LogBuffer中写入Online Redo Log;其次,在日志文件上,可以有三个类型的事务事件。

1、事务结束,已经被commit,之后打过checkpoint检查点。这种事务记录在LogFile上,但是变化信息已经被DBWn写入进数据文件;2、事务结束,已经被commit,之后没有打入checkpint检查点。这种情况下,LogFile已经写入了日志项目,数据文件可能包括脏数据,也可能没有写入脏数据;3、事务未结束,没有commit。这种时候,数据块DirtyBlock上面是有事务槽信息,表示未结束事务,是不会将数据写入到数据文件中。但是,日志LogBuffer可能将部分未提交的DML操作项目写入到Log File中;检查点Checkpoint

检查点Checkpoint是数据库一致性检查的一个标记。简单的说,就是在这个点上,Oracle保证各个文件(数据、控制、日志等)是一致的。检查点的作用就是在进行实例恢复的时候,告诉SMON进程,这个点之前的内容不需要进行恢复。

前滚和回滚介绍

“前滚和回滚”是Oracle数据库实例发生意外崩溃,重新启动的时候,由SMON进行的自动恢复过程。下面通过模拟实例和讲解介绍这个过程。

失败前场景说明

日志中记录过程如下:

1、事务A进行之后,结束commit。之后系统进行了一次checkpointA;2、Checkpoint之后,进行事务B,结束commit;3、进行事务C,C事务量较大,其中进行了一定量的RedoLog文件写入。之后系统断电;--按照LGWR的工作机制,C事务量比较大,所以应用程序将在几分之一秒内的时间里生成足以填充1/3秒的重做内容,因此这会触发LGWR将日志缓冲区的内容转储到日志文件,但始终得不到针对C事务的提交记录,这是需要回滚的。

4、还有种可能,事务B和D,事务D所用的缓冲区处于高速缓冲区不活跃的位置,而且事务B已提交,但其所用的缓冲区处于高速缓冲区活跃的位置。因此DBWn会将D事务缓冲区数据写入数据文件,而没将B事务的数据写入。此种情况需要回滚D事务,保留B事务。---参考OCP认证考试指南全册(P358下半部分内容).

1、系统启动过程,进入实例恢复阶段

当实例意外中断的时候,各类型文件,包括控制文件、数据文件和日志文件上,会存在不一致的问题。这种不一致主要体现在SCN值的差异上。

实例在启动的时候,经过三阶段(nomount、mount和open)。在open之前,会进行这种不一致现象的检查,如果出现不一致,要启动SMON进程的恢复流程。

SMON是Oracle实例的一个后台进程,主要负责进行系统监控恢复。进行恢复的依据主要是RedoLog记录。

2、前滚进程

SMON首先找到最后SCN记录的Redo LogFile。寻找最后一个打入的Checkpoint。

顺序找到CheckPointA之后,表示A之前的所有事务都是完全写入到数据文件中,不存在不一致性问题。恢复过程从CheckpointA开始,Oracle开始依据重做日志Redo Log的系列条目,进行推进。

首先遇到了事务B信息,由于事务B已经commit,所以事务B所有相关的Redo Log条目已经全都写入到Redo LogFile中。所以,按照日志继续条目推进,完全可以重演replay,并且应用apply事务B的全部过程。

这样,事务B全部实现,最终将通过DBWn完全写入到数据文件中。所以,实例失败之前提交commit的事务B,完全恢复。

进入事务C的范畴,由于一部分事务C的RedoLog条目已经进入Redo LogFile中(根据LGWR和DBWn的工作机制,事务C有可能将部分数据块写入日志文件和数据文件,但这时候C事务始终没提交,这是比较严重的讹误,所以需要回滚),所以在进行前滚的时候,一定会replay到这部分的内容。不过,这部分内容中不可能出现commit的标记。所以,前滚的结果一定是遇到实例突然中断的那个时点。此时replay的结果是,事务C没有提交。这样结束了前滚过程,进入回滚阶段。

3、回滚过程(与普通的回滚一样(当事务执行失败后自动回滚或者命令:ROLLBACK.)---参考OCP认证考试指南全册)对事务C(针对DML的update,当然其他同理),要进行回滚过程,释放所有相关资源。在前滚中,利用日志填充了的撤销块和表数据块的值,然后在回滚的时候,会将撤销块的值复制回表数据块中(因为此事务没提交记录),以此来进行SGA中BufferCache数据块恢复。

4、说说恢复过程的损耗

很多时候,由于我们事务规模较大,当出现实例崩溃的时候,重启所需要的时间很多。有一种经验说法是,事务有多长,前滚和回滚所消耗的时间有多长×2。而且,如果不能完成SMON恢复过程,数据库是不能算作正常的Open的。

SMON的恢复过程是Oracle强制进行的一个过程,即使恢复中发生断电或者其他中断失败事件。Oracle在下一次启动的时候,还是会继续这个过程,只有耐心等待。

通过检查一些内部视图(X$视图),可以观察到恢复进程和速度,但是丝毫不能影响到最终恢复的过程。

这个过程虽然可以保证数据一致性,但是也带来了系统不能启动,影响生产环境的问题。我们可以通过两个方式进行缓解:

首先,我们在设计开发系统时,要保证事务规模的可控性,不要让事务规模在技术层面上过大。避免一旦发生崩溃,大规模强制回滚的发生;其次,一旦出现了这个强制回滚,要注意对生产环境的影响。可以采用备库standby进行顶替,让主库安静的慢慢恢复;

2020-07-12:什么是索引回表,如何避免?

通过索引查数据其实是分两步:

1、通过索引字段查到数据的主键ID

2、通过主键ID查询全部数据

其中第二步就是索引回表,要避免索引回表,就要保证查询的数据只包含主键和索引字段本身,这样第二步就可以省了


网站标题:如何避免回表oracle,如何减少回表
链接URL:http://cdxtjz.com/article/dsdhdos.html

其他资讯