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

我不是女神ヾ 2022-05-21 00:13 171阅读 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 条评论，171人围观）

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

相关阅读

相关面试官：讲一下MySQL的聚簇/联合/覆盖索引、回表、索引下推

面试官：看你简历上用过MySQL，问你几个简单的问题吧。什么是聚簇索引和非聚簇索引？这个问题难不住我啊。我：举个例子：有这么一张用户表 CREATE TAB

小鱼儿/ 2024年04月06日 09:33/ 0 赞/ 471 阅读

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

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

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

相关 MySQL 回表 & 索引覆盖

索引类型聚簇索引：叶子节点存储的是行记录，每个表必须要有至少一个聚簇索引。使用聚簇索引查询会很快，因为可以直接定位到行记录普通索引：二级索引，除聚簇索引外的索引

Love The Way You Lie/ 2022年09月10日 08:23/ 0 赞/ 159 阅读

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

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

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

相关 mysql联合索引和索引优化的理解

首先介绍联合索引的最左原则，即假设某张表test，有四个字段，id，a，b，c；id是主键，a和b是联合索引，建表和值如下： CREATE TABLE `test`

一时失言乱红尘/ 2022年04月16日 04:16/ 0 赞/ 335 阅读

相关 mysql联合索引

命名规则：表名\_字段名 1、需要加索引的字段，要在where条件中 2、数据量少的字段不需要加索引 3、如果where条件中是OR关系，加索引不起作用 4、符

迷南。/ 2022年04月11日 08:00/ 0 赞/ 347 阅读

相关 mysql索引与全表扫描

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

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

相关联合索引

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

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

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

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

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

相关单列索引和联合索引

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

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