用合适的索引避免不必要的全表扫描

2024-09-03 13:04

本文主要是介绍用合适的索引避免不必要的全表扫描,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

    Oracle 数据库里大部分SQL优化的问题都可以增加或减少索引的方式来解决,但这绝不是全部。当目标SQL语句所要查询的只是目标表中的一部分数据时,通过创建合 适的索引就能够避免在没有索引的情况下为查询这一小部分数据而不得不采用全表扫描的操作,这样就降低了目标SQL语句的资源消耗,同时也会缩短了执行时 间。创建一张测试表及创建一个普通的单键值B树索引:

复制代码
复制代码
SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create index idx_t1 on t1(object_id);

Index created.
复制代码
复制代码

   清空缓存数据,我们看下一个SQL查询的执行计划及资源消耗:

复制代码
复制代码
SQL> alter system flush buffer_cache;

System altered.

SQL> set timing on
SQL> set autotrace traceonly
SQL> select * from t1 where object_id is null;

no rows selected

Elapsed: 00:00:00.11

Execution Plan

Plan hash value: 3617692013


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

| 0 | SELECT STATEMENT | | 12 | 2484 | 291 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 12 | 2484 | 291 (1)| 00:00:04 |

Predicate Information (identified by operation id):

1 - filter(“OBJECT_ID” IS NULL)

Note

  • dynamic sampling used for this statement (level=2)

Statistics

    308  recursive calls0  db block gets1151  consistent gets1038  physical reads0  redo size1183  bytes sent via SQL*Net to client405  bytes received via SQL*Net from client1  SQL*Net roundtrips to/from client0  sorts (memory)0  sorts (disk)0  rows processed

复制代码
复制代码

  从上面的查询中可以看到,SQL语句查询走的全表扫描,有1151个一致性读,1038个物理读。

为什么没有走索引哪?

原来对于普通的单键值B树索引而言,NULL值不入索引的,所以即便在OBJECT_ID上有单键值B树索引IDX_T1,在执行上面的查询时也用不上。 对于上面的SQL有没有办法让其走索引那?如果你对Oracle数据库里B树索引的结构和原理都很了解的话就不难回答这个问题。

这里只需建立一个复合B树索引就可以了。

删除原来的IDX_T1索引,重新创建一个同名的复合B树索引IDX_T1,其前导列依然是object_id,但第二列是一个常数0,这里利用的原理是 —虽然对于单键值B树索引而言NULL值不入索引,但对于复合B树索引来说,NULL值是入索引的。

SQL> drop index idx_t1;

Index dropped.
SQL> create index idx_t1 on t1(object_id,0);

Index created

 重新执行同样的SQL查询:

复制代码
复制代码
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.11
SQL>
SQL> select * from t1 where object_id is null;

no rows selected

Elapsed: 00:00:00.01

Execution Plan

Plan hash value: 50753647


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

| 0 | SELECT STATEMENT | | 12 | 2484 | 97 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 12 | 2484 | 97 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IDX_T1 | 4314 | | 11 (0)| 00:00:01 |

Predicate Information (identified by operation id):

2 - access(“OBJECT_ID” IS NULL)

Note

  • dynamic sampling used for this statement (level=2)

Statistics

      0  recursive calls0  db block gets2  consistent gets2  physical reads0  redo size1183  bytes sent via SQL*Net to client405  bytes received via SQL*Net from client1  SQL*Net roundtrips to/from client0  sorts (memory)0  sorts (disk)0  rows processed

复制代码
复制代码

     创建复合索引重新执行后可以看到Oracle这次查询就用到了B树复合索引IDX_T1,数据从全表扫描变成了索引范围扫描,数据耗费时间、一致性读和物理读也大幅度下降了。通过创建合适的索引来避免不必要的全表扫描,大幅降低了目标SQL 语句的资源消耗,进而大幅度的缩短了SQL的执行时间。

这篇关于用合适的索引避免不必要的全表扫描的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1133031

相关文章

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

如何来避免FOUC

FOUC(Flash of Unstyled Content)是指在网页加载过程中,由于CSS样式加载延迟或加载顺序不当,导致页面出现短暂的无样式内容闪烁现象。为了避免FOUC,可以采取以下几种方法: 1. 优化CSS加载 内联CSS:将关键的CSS样式直接嵌入到HTML文档的<head>部分,这样可以确保在页面渲染之前样式就已经加载和应用。提前引入CSS:将CSS文件放在HTML文档的<he

贝壳面试:什么是回表?什么是索引下推?

尼恩说在前面 在40岁老架构师 尼恩的读者交流群(50+)中,最近有小伙伴拿到了一线互联网企业如得物、阿里、滴滴、极兔、有赞、希音、百度、网易、美团的面试资格,遇到很多很重要的面试题: 1.谈谈你对MySQL 索引下推 的认识? 2.在MySQL中,索引下推 是如何实现的?请简述其工作原理。 3、说说什么是 回表,什么是 索引下推 ? 最近有小伙伴在面试 贝壳、soul,又遇到了相关的

Mysql高级篇(中)——索引介绍

Mysql高级篇(中)——索引介绍 一、索引本质二、索引优缺点三、索引分类(1)按数据结构分类(2)按功能分类(3) 按存储引擎分类(4) 按存储方式分类(5) 按使用方式分类 四、 索引基本语法(1)创建索引(2)查看索引(3)删除索引(4)ALTER 关键字创建/删除索引 五、适合创建索引的情况思考题 六、不适合创建索引的情况 一、索引本质 索引本质 是 一种数据结构,它用

argodb自定义函数读取hdfs文件的注意点,避免FileSystem已关闭异常

一、问题描述 一位同学反馈,他写的argo存过中调用了一个自定义函数,函数会加载hdfs上的一个文件,但有些节点会报FileSystem closed异常,同时有时任务会成功,有时会失败。 二、问题分析 argodb的计算引擎是基于spark的定制化引擎,对于自定义函数的调用跟hive on spark的是一致的。udf要通过反射生成实例,然后迭代调用evaluate。通过代码分析,udf在

独立按键单击检测(延时消抖+定时器扫描)

目录 独立按键简介 按键抖动 模块接线 延时消抖 Key.h Key.c 定时器扫描按键代码 Key.h Key.c main.c 思考  MultiButton按键驱动 独立按键简介 ​ 轻触按键相当于一种电子开关,按下时开关接通,松开时开关断开,实现原理是通过轻触按键内部的金属弹片受力弹动来实现接通与断开。  ​ 按键抖动 由于按键内部使用的是机

Qt: 详细理解delete与deleteLater (避免访问悬空指针导致程序异常终止)

前言 珍爱生命,远离悬空指针。 正文 delete 立即删除:调用 delete 后,对象会立即被销毁,其内存会立即被释放。调用顺序:对象的析构函数会被立即调用,销毁该对象及其子对象。无事件处理:如果在对象销毁过程中还涉及到信号和槽、事件处理等,直接 delete 可能会导致问题,尤其是在对象正在处理事件时。适用场景:适用于在确定对象已经不再被使用的情况下,并且不涉及异步处理或事件循环中的

ElasticSearch 6.1.1 通过Head插件,新建索引,添加文档,及其查询数据

ElasticSearch 6.1.1 通过Head插件,新建索引,添加文档,及其查询; 一、首先启动相关服务: 二、新建一个film索引: 三、建立映射: 1、通过Head插件: POST http://192.168.1.111:9200/film/_mapping/dongzuo/ {"properties": {"title": {"type":

ElasticSearch 6.1.1运用代码添加索引及其添加,修改,删除文档

1、新建一个MAVEN项目:ElasticSearchTest 2、修改pom.xml文件内容: <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.or

postgres数据库中如何看查询是否走索引,以及在什么情况下走索引

在 PostgreSQL 中,可以通过 EXPLAIN 或 EXPLAIN ANALYZE 查看查询计划,以判断查询是否使用了索引。除此之外,了解索引的使用条件对于优化查询性能也很重要。 1. 如何查看查询是否使用索引 使用 EXPLAIN 查看查询计划 EXPLAIN 显示 PostgreSQL 如何执行查询,包括是否使用索引。 EXPLAIN SELECT * FROM users WH