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

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之InnoDB存储引擎中的索引用法及说明

《MySQL之InnoDB存储引擎中的索引用法及说明》:本文主要介绍MySQL之InnoDB存储引擎中的索引用法及说明,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐... 目录1、背景2、准备3、正篇【1】存储用户记录的数据页【2】存储目录项记录的数据页【3】聚簇索引【4】二

全面解析MySQL索引长度限制问题与解决方案

《全面解析MySQL索引长度限制问题与解决方案》MySQL对索引长度设限是为了保持高效的数据检索性能,这个限制不是MySQL的缺陷,而是数据库设计中的权衡结果,下面我们就来看看如何解决这一问题吧... 目录引言:为什么会有索引键长度问题?一、问题根源深度解析mysql索引长度限制原理实际场景示例二、五大解决

MySQL中的索引结构和分类实战案例详解

《MySQL中的索引结构和分类实战案例详解》本文详解MySQL索引结构与分类,涵盖B树、B+树、哈希及全文索引,分析其原理与优劣势,并结合实战案例探讨创建、管理及优化技巧,助力提升查询性能,感兴趣的朋... 目录一、索引概述1.1 索引的定义与作用1.2 索引的基本原理二、索引结构详解2.1 B树索引2.2

python3如何找到字典的下标index、获取list中指定元素的位置索引

《python3如何找到字典的下标index、获取list中指定元素的位置索引》:本文主要介绍python3如何找到字典的下标index、获取list中指定元素的位置索引问题,具有很好的参考价值,... 目录enumerate()找到字典的下标 index获取list中指定元素的位置索引总结enumerat

从入门到精通MySQL 数据库索引(实战案例)

《从入门到精通MySQL数据库索引(实战案例)》索引是数据库的目录,提升查询速度,主要类型包括BTree、Hash、全文、空间索引,需根据场景选择,建议用于高频查询、关联字段、排序等,避免重复率高或... 目录一、索引是什么?能干嘛?核心作用:二、索引的 4 种主要类型(附通俗例子)1. BTree 索引(

MySQL 添加索引5种方式示例详解(实用sql代码)

《MySQL添加索引5种方式示例详解(实用sql代码)》在MySQL数据库中添加索引可以帮助提高查询性能,尤其是在数据量大的表中,下面给大家分享MySQL添加索引5种方式示例详解(实用sql代码),... 在mysql数据库中添加索引可以帮助提高查询性能,尤其是在数据量大的表中。索引可以在创建表时定义,也可

正则表达式r前缀使用指南及如何避免常见错误

《正则表达式r前缀使用指南及如何避免常见错误》正则表达式是处理字符串的强大工具,但它常常伴随着转义字符的复杂性,本文将简洁地讲解r的作用、基本原理,以及如何在实际代码中避免常见错误,感兴趣的朋友一... 目录1. 字符串的双重翻译困境2. 为什么需要 r?3. 常见错误和正确用法4. Unicode 转换的

MySQL索引失效问题及解决方案

《MySQL索引失效问题及解决方案》:本文主要介绍MySQL索引失效问题及解决方案,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql索引失效一、概要二、常见的导致MpythonySQL索引失效的原因三、如何诊断MySQL索引失效四、如何解决MySQL索引失

C# foreach 循环中获取索引的实现方式

《C#foreach循环中获取索引的实现方式》:本文主要介绍C#foreach循环中获取索引的实现方式,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录一、手动维护索引变量二、LINQ Select + 元组解构三、扩展方法封装索引四、使用 for 循环替代

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分