oracle排序、回表扫描和联合索引

我不是女神ヾ 2022-05-21 00:13 184阅读 0赞

简介：oracle回表扫描也会增加IO消耗，有的时候可以通过一些方法避免。如果对索引的结构还不太理解，可以看一下我以前的博客，oracle树状索引详解。

一.oracle回表扫描

例子：test 表 10w数据，10个字段，主键为id

1.select \* from test where id = 5

回表扫描介绍：执行这个sql的执行计划，可以看到，首先它走了主键的索引，可以看到一个index range scan，并且还有一个table access by index rowid，这个就是回表扫描了。执行流程是这样的，先从id索引块里找到id=5的数据，由于索引里只有id的值，要拿到其他列值，需要通过索引里的rowid去表里检索到这一列的其他字段。

那怎么可以优化呢？根据自己需求来看。

首先确定自己需不需要表里全部的字段，如果不需要全部的，

第一步，可以把sql改为：

例如：select id,name,amount from test where id = 5，这样可以少拿字段，优化了一点点。

第二步，根据需求建立联合索引

当你这几个字段经常要用到查询，可以在这几个字段上建立联合索引，这个索引就有 id，name，amount 三列（通常不建议超过3列，因为索引也是很占空间的，多了得不偿失，而且更新的时候还要更新索引，影响效率）

建好这个索引之后，还是同样的道理，这个查询语句就可以在这个索引块里找到所有的值，就不需要去扫描很多数据块了，建好索引，我们再看一下执行计划，就没有table access by index rowid了，耗费的IO也是变少了（可以看cost和逻辑读有体现）

当不能避免回表扫描的时候怎么办呢？回表扫描肯定也会有快和慢。

第一点：根据索引返回的数据条数越少，回表扫描根据rowid去找数据次数更少，肯定也会更快。

第二点：跟数据块的分布位置也会有关系。

例子：select \* from test where id <10

如果数据分布是有序的，1-9是在一个索引块里面，然后对应数据块也是有序的，这个sql回表扫描数据的时候，只需要扫描一个数据块。如果是无序的，1-9在多个索引块分布，数据也分布在多个数据块，回表扫描数据就需要多扫描很多数据块。

总结：对于回表扫描，能避免最好，不能避免的话，索引尽量优化，返回的数据条数尽量少，回表扫描就会快一点。

表的插入顺序和索引列的顺序基本一致，在回表的时候就会更快。

二.order by 的优化

由于排序是很消耗性能的，要优化 我们可以利用索引是有序的这个特点，比如很多情况下会有create\_time这个字段，有索引的排序可以利用索引排序，就能达到消除排序的目的。

ps：distinct之所以消耗性能也是因为去重之前需要先排序，所以如果distinct的字段有索引的话，也是可以消除distinct带来的排序。

发表评论取消回复

表情：

评论列表（有 0 条评论，184人围观）

还没有评论，来说两句吧...

相关阅读

相关 Oracle查询优化：索引失效与全表扫描案例

在Oracle数据库中，查询性能的优化涉及到很多因素，包括索引使用、数据分布、查询写法等。下面以两个具体的案例来展示索引失效和全表扫描的区别。 1. **索引失效**：

喜欢ヅ旅行/ 2024年12月16日 17:27/ 0 赞/ 13 阅读

相关数据库索引和全表扫描

mysql中建立索引的一些原则 1、先存数据，再建索引有索引的好处是搜索比较快但是在有索引的前提下进行插入、更新操作会很慢 2、不要对规模小的数据表建立索引，

ゞ浴缸里的玫瑰/ 2023年10月18日 15:40/ 0 赞/ 136 阅读

相关 MySQL回表和索引下推

回表覆盖索引索引下推 MySQL回表和索引下推是两个与索引优化相关的概念：回表：当使用非聚簇索引（辅助索引）查询数据时，如果需要获取除索引列以外的其他列的值

左手的ㄟ右手/ 2023年09月27日 21:34/ 0 赞/ 8 阅读

相关 ORACLE表闪回

版本情况 ORCLE版本：10.2.0.1.0 PL/SQL版本：11.0.2.1766 情景还原清空数据时，误操作清空了一个不该清除的表的数据闪回s

傷城~/ 2023年06月27日 05:34/ 0 赞/ 33 阅读

相关索引回表解释

B+ 树索引主要可以分为两种索引，聚集索引和非聚集索引。聚集索引：也就是平常我们说的主键索引，在 B+ 树中叶子节点存的是整行数据。非聚集索引：也叫二级索引，也就是

忘是亡心i/ 2022年09月03日 01:13/ 0 赞/ 5 阅读

相关 oracle排序、回表扫描和联合索引

简介：oracle回表扫描也会增加IO消耗，有的时候可以通过一些方法避免。如果对索引的结构还不太理解，可以看一下我以前的博客，oracle树状索引详解。一.oracle回表

我不是女神ヾ/ 2022年05月21日 00:13/ 0 赞/ 185 阅读

相关 mysql索引与全表扫描

mysql中建立索引的一些原则有索引的好处是搜索比较快，但是在有索引的前提下进行插入、更新操作会很慢，所以 1. 先存数据，再建索引 2. 不要对数据量小的数据表

约定不等于承诺〃/ 2022年03月26日 08:08/ 0 赞/ 326 阅读

相关联合索引

联合索引比单列索引优先字段a的单列索引，（a，b）的联合索引查询时使用联合索引联合索引本质当创建(a,b,c)联合索引时，相当于创建了(a)单列索引

落日映苍穹つ/ 2022年03月15日 03:52/ 0 赞/ 319 阅读

相关 Oracle 不同数据库表联合查询

前言完成不同数据库（包含不同服务器）之间表在同一个SQL语句中的使用。数据库链接在PL/SQL中完成的新建数据库链接 ![这里写图片描述][SouthE

男娘i/ 2021年09月14日 15:14/ 0 赞/ 745 阅读

相关单列索引和联合索引

一、简介利用索引中的附加列，可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。联合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏进行

绝地灬酷狼/ 2021年09月03日 07:04/ 0 赞/ 515 阅读