高效的SQL（index range scan优化排序）

最新推荐文章于 2025-07-06 15:56:41 发布
最新推荐文章于 2025-07-06 15:56:41 发布
阅读量3.7k 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： 高效SQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/dbadoudou/article/details/8252355
本文详细介绍了SQL中使用索引范围扫描的优化技术，包括实验表结构、执行计划、使用条件、注意事项及性能优势。通过实例展示了如何正确利用索引范围扫描提升查询效率。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

高效的SQL（index range scan优化排序）

 

一、     实验表

表的结构、内容、索引(Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.)

doudou@TEST> desc range_scan

 Name                                                  Null?    Type

 ----------------------------------------------------- -------- ------------------------------------

 ID                                                             NUMBER

 NAME                                                           VARCHAR2(20)

 SUM                                                            VARCHAR2(20)

doudou@TEST> select * from range_scan;

 

        ID NAME                                     SUM

---------- ---------------------------------------- ----------------------------------------

         2 doudou+2                                 3

         3 doudou+3                                 3

         4 doudou+4                                 3

         5 doudou+5                                 4

         6 doudou+6                                 4

         7 doudou+7                                 4

         8 doudou+8                                 5

         9 doudou+9                                 5

        10 doudou+10                                6

        11 doudou+11                                6

        12 doudou+12                                6

 

11 rows selected.

doudou@TEST>  select index_name,column_name,table_name fromuser_ind_columns where table_name='RANGE_SCAN';

 

INDEX_NAME                     COLUMN_NAME                    TABLE_NAME

------------------------------ ------------------------------ ------------------------------

INDEX_RANGE_SCAN_SUM           SUM                            RANGE_SCAN

二、     开启执行计划，执行SQL

The optimizer uses a range scan when it finds one or more leading columns of an index specified in conditions（优化器使用一个范围扫描当找到一个或多个列的索引指定主要在条件。）

1、例如：sum=’5’； sum>'5'；sum<'5'and (sum> '4' and sum<'6')

doudou@TEST> select * from range_scan where sum='5';

 

        ID NAME                                     SUM

---------- ---------------------------------------- ----------------------------------------

         8 doudou+8                                 5

         9 doudou+9                                 5

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3049432069

 

----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                      |     2 |    74 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| RANGE_SCAN           |     2 |    74 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | INDEX_RANGE_SCAN_SUM |     2 |       |     1   (0)| 00:00:01 |

doudou@TEST> select * from range_scan where sum>'5';

 

        ID NAME                                     SUM

---------- ---------------------------------------- ----------------------------------------

        10 doudou+10                                6

        11 doudou+11                                6

        12 doudou+12                                6

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3049432069

 

----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                      |     3 |   111 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| RANGE_SCAN           |     3 |   111 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | INDEX_RANGE_SCAN_SUM |     3 |       |     1   (0)| 00:00:01 |

doudou@TEST> select * from range_scan where sum<'5';

 

        ID NAME                                     SUM

---------- ---------------------------------------- ----------------------------------------

         2 doudou+2                                 3

         3 doudou+3                                 3

         4 doudou+4                                 3

         5 doudou+5                                 4

         6 doudou+6                                 4

         7 doudou+7                                 4

 

6 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3049432069

 

----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                      |     6 |   222 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| RANGE_SCAN           |     6 |   222 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | INDEX_RANGE_SCAN_SUM |     6 |       |     1   (0)| 00:00:01 |

 

doudou@TEST> select * from range_scan where sum >'4'  and  sum<'6';

 

        ID NAME                                     SUM

---------- ---------------------------------------- ----------------------------------------

         8 doudou+8                                 5

         9 doudou+9                                 5

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3049432069

 

----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                      |     2 |    74 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| RANGE_SCAN           |     2 |    74 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | INDEX_RANGE_SCAN_SUM |     2 |       |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------

 

2、index range scan使用通配符需注意 ‘%5’像这样，通配符在前的不走范围索引；’5%’通配符在最后的正常走范围索引

doudou@TEST> select * from range_scan where sum like '%5';

 

        ID NAME                                     SUM

---------- ---------------------------------------- ----------------------------------------

         8 doudou+8                                 5

         9 doudou+9                                 5

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1933872984

 

--------------------------------------------------------------------------------

| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |            |     2 |    74 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| RANGE_SCAN |     2 |    74 |     3   (0)| 00:00:01 |

【使用通配符不恰当，导致不走索引（低效）】

doudou@TEST> select * from range_scan where sum like '5%';

 

        ID NAME                                     SUM

---------- ---------------------------------------- ----------------------------------------

         8 doudou+8                                 5

         9 doudou+9                                 5

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3049432069

 

----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                      |     2 |    74 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| RANGE_SCAN           |     2 |    74 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | INDEX_RANGE_SCAN_SUM |     2 |       |     1   (0)| 00:00:01 |

【正确的使用通配符’5%’，（高效）】

 

3、当在索引列分组或排序的时候使用范围扫描,order by 索引列可以避免排序

doudou@TEST> select * from range_scan where sum='5' order by sum;

 

        ID NAME                                     SUM

---------- ---------------------------------------- ----------------------------------------

         8 doudou+8                                 5

         9 doudou+9                                 5

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3049432069

 

----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                      |     2 |    74 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| RANGE_SCAN           |     2 |    74 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | INDEX_RANGE_SCAN_SUM |     2 |       |     1   (0)| 00:00:01 |

【索引列进行了order by，索引没有进行更多的sort order by(高效)】

doudou@TEST>  select * from range_scan where sum='5' order by id ;

 

        ID NAME                                     SUM

---------- ---------------------------------------- ----------------------------------------

         8 doudou+8                                 5

         9 doudou+9                                 5

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 823340891

 

----------------------------------------------------------------------------------------------------

-

 

| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time

|

 

----------------------------------------------------------------------------------------------------

-

 

|   0 | SELECT STATEMENT             |                      |     2 |    74 |     3  (34)| 00:00:01

|

 

|   1 |  SORT ORDER BY               |                      |     2 |    74 |     3  (34)| 00:00:01

|

 

|   2 |   TABLE ACCESS BY INDEX ROWID| RANGE_SCAN           |     2 |    74 |     2   (0)| 00:00:01

|

 

|*  3 |    INDEX RANGE SCAN          | INDEX_RANGE_SCAN_SUM |     2 |       |     1   (0)| 00:00:01

|

 

----------------------------------------------------------------------------------------------------

【order by id，id不是索引列，这里又进行了排序，浪费了cost（低效）】

 

三、     总结

1、index range scan 使用条件：(Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.)

2、index range scan使用通配符需注意： ‘%name’不恰当使用不走索引，’name%’正确使用走索引

3、index range scan优点：group by 或order by  使用索引列这样就避免了再次sort，提高了性能

 

附表：

 

1、实验数据库环境

sys@TEST> show parameter optimizer_mode

 

NAME                                 TYPE                   VALUE

------------------------------------ ---------------------- ------------------------------

optimizer_mode                       string                 ALL_ROWS

sys@TEST> select * from v$version;

 

BANNER

----------------------------------------------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

PL/SQL Release 10.2.0.4.0 - Production

CORE    10.2.0.4.0      Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 – Production

 

2、官档

13.5.3.3.1 When the Optimizer Uses Index Range Scans

The optimizer uses a range scan when it finds one or more leading columns of an index specified in conditions, such as the following:

  • col1 = :b1
  • col1 < :b1
  • col1 > :b1
  • AND combination of the preceding conditions for leading columns in the index
  • col1 like 'ASD%' wild-card searches should not be in a leading position otherwise the condition col1 like '%ASD' does not result in a range scan.

Range scans can use unique or non-unique indexes. Range scans avoid sorting when index columns constitute theORDERBY/GROUPBY clause.

 

警惕 Oracle 索引优化时陷阱之无效的索引范围扫描(INDEX RANGE SCAN)导致的全表扫描
Defonds 的专栏
07-06 2980
生产环境慢查询统计中，发现表 STATUS 的 MILESTONE 字段条件查询时进行了全表扫描。表 STATUS 的 MILESTONE 字段定义如下： 针对上述问题创建索引： create index STATUS_MILESTONE on STATUS("MILESTONE") tablespace DFS_INDEX2; 分析执行计划发现问题语句依然走的是全表扫描，新建的索引 STATUS_MILESTONE 没有用到。第一反应是索引是不是没有创建成功？ 查看索引库发现该索引确实存在并且生效：
对通过index range scans访问的表的优化
cuishiping6190的博客
06-10 412
表authors有1706123行,39677个blocks,259个empty blocks,在au_fname有索引INDEX_AUTHORS.对表进行分析.Analyze Table AUTHORSCompute Stat...
SQL优化：index range scan真的不会多块读吗？
最新发布
07-06 747
SQL优化案例分享：索引范围扫描与多块读问题 本文分析了西安某客户的一个SQL优化案例，重点关注了索引范围扫描(index range scan)是否使用多块读的问题。案例涉及一个UPDATE语句，执行计划显示存在嵌套循环、哈希连接等多种操作。其中发现了一个有趣现象：索引范围扫描和回表操作都使用了多块读方式，这与常规理解有所不同。执行计划中包含了全局索引的ROWID访问和分区表的操作，最终优化后性能得到提升。该案例来自真实生产环境，对理解Oracle数据库执行机制具有参考价值。
SQL优化 INDEX RANGE SCAN (MIN/MAX)
kiwixing的博客
10-16 877
当需要根据某一列的记录，获取另外一列的最大值最小值时，可以创建索引进行INDEX RANGE SCAN (MIN/MAX)，利用索引有序特性直接取到最大值，提高查询效率。INDEX RANGE SCAN (MIN/MAX) 是数据库优化器在处理 MIN 和 MAX 函数时采用的一种索引扫描策略。这种策略从根本上利用了索引的有序性，直接定位到索引的最小或最大值位置，从而快速返回结果。
详解Oracle数据库索引范围扫描原理和优化方法
专注于数据库技术分享，包含但不限于Oracle，MySQL，PostgreSQL，ElasticSearch及国产数据库等
04-01 2362
Oracle数据库索引范围扫描（Index Range Scan）是一种常见的索引访问路径，它主要用于处理涉及索引列范围查询的SQL语句。
实战：看懂并分析执行计划——Clustered Index Scan
技术广场
11-08 1371
通过查看执行计划中的操作，我们可以确认查询的瓶颈在于全表扫描导致了大量 I/O 和 CPU 开销。针对Predicate中使用的条件列创建合适的非聚集索引，以及减少中不必要的字段，可以有效优化查询性能，减少 I/O 和 CPU 消耗。创建索引后，重新查看执行计划，以确认是否成功将转换为Index Seek，达到优化目标。
SQL优化篇 - MySQL查询select语句优化
2301_76868495的博客
07-24 1039
使用MySQL，SQL优化，以及工作中实战的操作
Oracle SQL优化②——访问路径
weixin_52444902的博客
11-22 1502
Oracle SQL优化——访问路径
SQL SERVER INDEX 总结
u013400314的博客
10-26 1566
使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好。在SQL Server中还有include的用法，可以把非聚集索引里包含的列包含进来，而不一定需要建立复合索引。
oracleSQL优化培训(精华整理)(ppt文档).pptx
11-13
例如，在上述示例中，ID为6的操作是一个`INDEX RANGE SCAN`，表示只扫描了索引的一部分，这是优化器根据条件选择的最佳访问路径。 6. **开发人员应具备的优化能力**：除了理解SQL性能分析和执行计划外，开发人员还...
关于索引扫描的极速调优实战（第一篇)
congbao6525的博客
12-11 178
一般在生产环境中，如果某个查询中涉及一个大表，走索引扫描是显然是最值得推荐的方式，但是索引扫描有unique index scan, range scan,skip scan, full scan, fast full scan...
index range scan(索引范围扫描)的计划分析
一切都是浮云
04-15 8412
 一、index range scan(索引范围扫描)： 1、对于unique index（唯一性索引，是带唯一性约束的，普通索引则没有唯一性约束。scott.emp表是主键，带有唯一性约束，同时有索引Rowid）来说，如果where 条件后面出现了&lt;,&gt; ,between ...and...的时候，那么就可能执行index range scan,如果where条件后面是=，那么...
mysql range索引_mysql 索引 index range
weixin_39928818的博客
01-18 1057
今天在调试一个BUG的时候，无意间发现 explain 发现了 index range; 之前对于索引的理解是，单个索引每次查询只能用一个索引。于是赶紧查一下，果然存在这个优化器在适当的时候会选择使用两个索引然后取交集，并集。又一想那效率和符合索引有什么区别呢？ 实验发现还是符合索引速度快，而且快不少。看来出现index range 不是最优的索引。 效率对比：符合索引(user_id,type)...
mysql 索引 index range
weixin_33979363的博客
12-21 2672
今天在调试一个BUG的时候，无意间发现 explain 发现了 i...
oracle skipscan,深入懂得Oracle索引(1)：INDEX SKIP SCAN 和 INDEX RANGE SCAN
weixin_42515045的博客
04-04 1226
深入理解Oracle索引(1)：INDEX SKIP SCAN 和 INDEX RANGE SCAN㈠ Index SKIP SCAN当表有一个复合索引，而在查询中有除了索引中第一列的其他列作为条件，并且优化器模式为CBO，这时候查询计划就有可能使用到SSSkip scan会探测出索引前导列的唯一值个数，每个唯一值都会作为常规扫描的入口，在此基础上做一次查找，最后合并这些查询例如：表employe...
索引范围扫描(INDEX RANGE SCAN)
weixin_33762130的博客
04-28 943
      索引范围扫描（INDEX RANGE SCAN）适用于所有类型的B树索引，当扫描的对象是唯一性索引时，此时目标SQL的where条件一定是范围查询（谓词条件为 BETWEEN、&lt;、&gt;等）；当扫描的对象是非唯一性索引时，对目标SQL的where条件没有限制（可以是等值查询，也可以是范围查询）。 索引范围扫描的结果可能会返回多条记录，其实这就是索引范围扫描中"范围"二字的本质含...
oracle index range scan,INDEX RANGE SCAN (MIN/MAX)很慢的问题
weixin_34188805的博客
04-12 1219
这个问题以前也遇到过，这是第二次了。这个问题比较有意思，一开始考虑的很多因素都被一一排除了，最后才发现问题所在。当使用select min(col1) from t或者select max(col1)的时候，如果xx字段上有合适的索引的话，oracle就会使用index full scan(min/max)的route。这个route是怎么的呢，很简单，如果是min从index(这里指的是asc排...
sql当hints index fast full scan走不了 会走index range scan的情况
zhaoyangjian724的专栏
09-12 415
explain plan for select /*+INDEX_FFS(a t100_idx1)*/ owner from t100 a where a.owner='TLCBUSER'; select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT Plan hash value: 4259608525 -------...
Oracle优化器深入解析：SQL执行效率的关键
索引范围扫描（Index Range Scan）则适用于查询条件能精确匹配索引的部分，这样可以直接定位到所需的数据，避免了扫描全部表。而全索引快速扫描（INDEX Fast Full Scan，INDEX_FFS）是在索引块都驻留在内存中的情况...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值