合适的索引与执行计划

最新推荐文章于 2021-06-29 17:20:00 发布
最新推荐文章于 2021-06-29 17:20:00 发布
阅读量93 收藏
点赞数
分类专栏： 性能优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xxzhaobb/article/details/108683777
版权

RDBMS12.2.0.1

-- 创建测试用的表

create table t2 as select * from dba_objects ; 
SYS@test>select count(*) from t2;    

  COUNT(*)
----------
 113246208

-- 测试内容
1 查询不使用索引
select object_name from t2 where object_id=2;
2 查询使用where条件中的索引
select object_name from t2 where object_id=2;
3 select和where中都有索引
select object_name from t2 where object_id=2;
4 创建组合索引，where条件为前导列
select object_name from t2 where object_id=2;
5 创建组合索引，select的字段为前导列
select object_name from t2 where object_id=2;
6 查询使用select语句中的字段
select object_name from t2 where object_id=2;
7 修改object_id=2 ，object_id上有索引（该索引较差）
update t2 set object_id=2;
commit;
-- 走了索引INDEX FAST FULL SCAN ，没有回表，效率相对比全表扫描少
select object_id from t2 where object_id=2 ;
select /*+ full(T2) */ object_id from t2 where object_id=2 ;
-- 换个写法，因为object_id都是2，所以优化器认为使用全表扫描效率比较好。
select * from t2 where object_id=2 ;
select /*+ index(T2 IDX_OBJID_T2) */ * from t2 where object_id=2 ;


-- 测试过程

1 没有索引。执行计划走了全表扫描。成本93632

select object_name from t2 where object_id=2;

SYS@test>select object_name from t2 where object_id=2;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  3794 |   292K| 93632   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T2   |  3794 |   292K| 93632   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=2)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
     688360  consistent gets
     344839  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        596  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SYS@test>

2 object_id有索引 ，执行计划使用了索引扫描，回了表

create index idx_objid_t2 on t2(object_id) ;
select object_name from t2 where object_id=2;

SYS@test>select object_name from t2 where object_id=2;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2765286821

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |    79 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T2           |     1 |    79 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_OBJID_T2 |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=2)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
        109  consistent gets
          2  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        596  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SYS@test>

3 object_id和object_name有索引 ，但是只用了object_id列上的索引

create index idx_obname on t2(object_name);
select object_name from t2 where object_id=2;

--select /*+ index_join(T2 IDX_OBJID_T2 idx_obname)*/ object_name from t2 where object_id=2;   -- 不适合索引join，适合索引join的情况，where条件join 

SYS@test>select object_name from t2 where object_id=2;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2765286821

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |    79 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T2           |     1 |    79 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_OBJID_T2 |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=2)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
        110  consistent gets
          0  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        596  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SYS@test>

4 object_id和object_name组合索引 ，使用了组合索引

drop index idx_objid_t2;
drop index idx_obname;
create index idx_obj_id_name on t2(object_id,object_name);
select object_name from t2 where object_id=2;

SYS@test>select object_name from t2 where object_id=2;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 438204577

------------------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |     1 |    79 |     3   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_OBJ_ID_NAME |     1 |    79 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"=2)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
        111  consistent gets
          3  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        596  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SYS@test>

5 object_name和object_id组合索引 ，使用了组合索引，但是成本天壤之别（索引的前导列不同）

drop index idx_obj_id_name ;
create index idx_obj_name_id on t2(object_name,object_id);
select object_name from t2 where object_id=2;

SYS@test>select object_name from t2 where object_id=2;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3993461820

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |  3794 |   292K| 31757   (1)| 00:00:02 |
|*  1 |  INDEX FAST FULL SCAN| IDX_OBJ_NAME_ID |  3794 |   292K| 31757   (1)| 00:00:02 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=2)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
     116967  consistent gets
     116822  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        596  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SYS@test>

6 object_name列上有索引，不会走索引，加hint提示，强行走索引 ，尽管有hint ，但是O还是忽略了

select object_name from t2 where object_id=2;
select /*+ index(t2 idx_obname)*/ object_name from t2 where object_id=2;

SYS@test>select object_name from t2 where object_id=2;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  3794 |   292K| 93632   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T2   |  3794 |   292K| 93632   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=2)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
         49  recursive calls
          0  db block gets
     688392  consistent gets
     344839  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        596  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          0  rows processed

SYS@test>

SYS@test>select /*+ index(t2 idx_obname)*/ object_name from t2 where object_id=2;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  3794 |   292K| 93632   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T2   |  3794 |   292K| 93632   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=2)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
     688355  consistent gets
     344839  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        596  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SYS@test>

7 修改object_id=2 ，object_id上有索引（该索引较差）

SYS@test>select object_id from t2 where object_id=2 ;

20277760 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 170535381

-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |    23M|   297M| 10851   (1)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| IDX_OBJID_T2 |    23M|   297M| 10851   (1)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=2)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
    1389106  consistent gets
      39877  physical reads
       6644  redo size
  359592737  bytes sent via SQL*Net to client
   14870957  bytes received via SQL*Net from client
    1351852  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
   20277760  rows processed

SYS@test>
SYS@test>select /*+ full(T2) */ object_id from t2 where object_id=2 ;

20277760 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    20M|    58M| 93605   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T2   |    20M|    58M| 93605   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=2)


Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
    2018085  consistent gets
     344839  physical reads
          0  redo size
  359592737  bytes sent via SQL*Net to client
   14870957  bytes received via SQL*Net from client
    1351852  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
   20277760  rows processed

SYS@test>

-- 直接走全表扫描，效率相对走索引好一些

SYS@test>select * from t2 where object_id=2 ;


20277760 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    20M|  2185M| 93847   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T2   |    20M|  2185M| 93847   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=2)


Statistics
----------------------------------------------------------
         26  recursive calls
          0  db block gets
    2018151  consistent gets
     344839  physical reads
          0  redo size
 1166602921  bytes sent via SQL*Net to client
   14870957  bytes received via SQL*Net from client
    1351852  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
   20277760  rows processed

SYS@test>SYS@test>

-- 强行走索引，效率更差

SYS@test>select /*+ index(T2 IDX_OBJID_T2) */ * from t2 where object_id=2 ;

20277760 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2765286821

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |    20M|  2185M|   401K  (1)| 00:00:16 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T2           |    20M|  2185M|   401K  (1)| 00:00:16 |
|*  2 |   INDEX RANGE SCAN                  | IDX_OBJID_T2 |    19M|       | 37665   (1)| 00:00:02 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=2)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    3406712  consistent gets
     335716  physical reads
   30345216  redo size
 2708848447  bytes sent via SQL*Net to client
   14870957  bytes received via SQL*Net from client
    1351852  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
   20277760  rows processed

SYS@test>

 

END

文档搬运工
关注
专栏目录
Oracle之索引、执行计划
qq_40187702的博客
07-11 964
对于关系型数据库（Oracle、MySQL）来说，索引是一种可以提高sql执行效率的一种方式，合理的创建索引可以大大提升查询速度，在工作中我们会经常遇到一些慢sql，对于这种sql我们就需要用到执行计划去分析这个sql为什么这么慢，接下来我们一起去探索索引的魅力。关于索引以及sql执行计划的分析就梳理到这儿了，后面如果有新内容会继续更新。
MySQL查询优化：索引与执行计划
最新发布
java专栏
08-14 1548
📚 索引是数据库表中用于加快数据检索速度的数据结构。常见的索引类型包括B-Tree索引、哈希索引、全文索引等。索引可以显著提高查询速度，但同时也会增加写操作的开销和存储空间。
测试博客园代码插入格式显示
weixin_34117211的博客
01-06 166
ABAPABAP傻傻的 | 9 | TABLE ACCESS FULL | FRM_ROADITEM | 5212 | 88604 | 22 (0)| 00:00:01 | ----------------------------------------------------------------------------------...
MySQL索引与执行计划
qq_34481955的博客
12-30 161
索引的定义 索引是存储引擎用于快速寻找记录的一种数据结构 索引本质是一种数据结构（一定要记住） 索引作用于存储引擎层 索引的效果是用来提高查询效率 索引的优点和缺点 减少了服务器需要扫面的数据量 帮助服务器避免排序和临时表 索引可以将随机IO转换成顺序IO 索引分类 基于数据结构分类 B树 R树 哈希 FullText （全文索引，效果不咋地一般用ES代替） ...
MySql（二）索引与执行计划
你能找到这里，说明你有经天纬地之才，扭转乾坤只能，鬼神不测之计包藏宇宙之志，真乃乱世之枭雄，治世之能臣也
05-15 570
什么是索引： 在关系型数据库种，索引是一种单独的、物理的对数据库表种一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值得集合和相应的指向表中物理标识，这些值的数据页的逻辑指针清单。 （什么意思：索引的作用相当于图书的目录，可以根据目录中页码快速找到所需要的内容） 索引的分类： 1、普通索引：即一个索引只包含单个列，一个表可以有多个单列索引； 2、唯一索引：索引列的值必须唯一，但允许有空值，并且多个空值； 3、主键索引：与唯一索引之间的区别就在于不允许有空值，创建主键时会自动创建此索
博客园中的源代码格式显示
weixin_30653023的博客
09-27 305
昨天写了一篇文章，但是在写的时候呢，没有注意，直接将代码复制上去了，今天正好有人提醒，看到了格式的混乱，借此记录整理一下，如何能直接粘贴代码，而且格式（缩进）不会发生变化。当然，网上可能有很多，但是我个人用的是下面的一种，觉得还是比较简单的，如果你们有更简单的，可以跟我分享一下奥。（手动开心，感谢） OK，下面就是解决方案： 直接粘贴的代码： // GET: Common ...
博客园代码样式修改
kingBook928的博客
06-29 459
下载 样式预览：https://highlightjs.org/static/demo/ 样式下载：https://files.cnblogs.com/files/-Tiger/highlight.zip 或者在其官网下载也可以：https://highlightjs.org/download/ 修改 CSS 代码 (1)解压压缩包 -> 打开styles文件夹 -> 找到你...
MongoDB性能篇之创建索引，组合索引，唯一索引，删除索引和explain执行计划
09-10
本篇将深入探讨MongoDB中的索引创建、组合索引、唯一索引、删除索引以及`explain`执行计划。 1. **基础索引** MongoDB在创建集合时，默认为`_id`字段创建一个唯一的升序索引，这是无法删除的。创建其他索引，例如...
浅析SQL Server的聚焦使用索引和查询执行计划
09-09
然而，这并不总是最理想的，因此，了解如何强制使用特定索引和阅读执行计划可以帮助我们微调查询性能。 在数据库优化中，索引是基础，但并非唯一考虑因素。不同的查询场景可能需要不同的索引策略。例如，频繁进行全...
MySQL如何选择合适的索引
weixin_31351409的博客
09-03 524
先来看一个栗子 EXPLAIN select * from employees where name > 'a'; 如果用name索引查找数据需要遍历name字段联合索引树，然后根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比全表扫描还高。可以用覆盖索引优化，这样只需要遍历name字段的联合索引树就可以拿到所有的结果。 EXPLAIN select name,age,p...
自定义博客园代码格式
aifuxun2845的博客
01-21 202
实现方式 在设置->自定义css里面加入css代码即可。比如github风格 /*.cnblogs_code { background-color: #f5f5f5;border:1px} .cnblogs_code {border-right:gray 1px solid;border-top:gray 1px solid;border-left:gray 1px solid;b...
如何在CSDN博客中插入代码块显示行号
热门推荐
迷失的小明的博客
04-22 1万+
最近在学习java语言，想要通过写博客来记录自己的学习过程以便以后查阅，由于本人博客小白，总是在写作过程中需要插入代码片段时而无从下手，排版不忍直视，通过借鉴其他同学的博客，整理如下和大家分享。例如在博客中需要插入一段java代码，如下：public class User {    private String username;    private String password;...
【293】博客园自动添加样式代码
weixin_34163553的博客
01-19 93
代码测试一：按照大写数字查找，只针对一级标题 如下的博客内容，只有标题和目录内容，通过python脚本对其HTML内容进行处理，为其添加标题样式以及锚点，并为目录添加对应的链接地址 目录： 一、测试 二、测试 三、测试 四、测试 五、测试 一、测试 二、测试 三、测试 四、测试 五、测试 对应的HTML源代码如下所示： <p>目录：&lt...
博客园设置代码样式
weixin_30448603的博客
04-07 387
使用syntaxhighlighter插件对代码进行高亮。 1. 下载syntaxhighlighter http://files.cnblogs.com/heyuquan/SyntaxHighlighter%E7%A4%BA%E4%BE%8B%E6%BA%90%E7%A0%81%E4%B8%8B%E8%BD%BD.rar 2. 将需要的css样式上传到博客园（管理--...
教你如何在博客园放“可运行"代码
weixin_33871366的博客
06-26 188
2019独角兽企业重金招聘Python工程师标准>>> ...
博客园_插入代码(推荐)_按钮功能实现_代码高亮显示
abxlep7702的博客
07-20 177
项目结构： 运行效果： xml： java： html： ============================================================= 实现： ============================================================= 步骤一：准备 　　1.下载SyntaxHi...
Oracle执行计划解析：全表扫描与索引优化
例如，创建合适的索引、更新统计信息或调整SQL语句的写法，都可能改善执行计划并提升系统性能。 理解Oracle执行计划是数据库管理员和开发人员提升系统性能的关键技能之一。通过深入分析执行计划，我们可以做出明智...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值