解析索引

最新推荐文章于 2021-08-29 14:56:50 发布
最新推荐文章于 2021-08-29 14:56:50 发布
阅读量519 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_21127313/article/details/78376415
版权 
解析索引
参考《oracle DBA工作笔记》
在oracle中对于索引的存储是采用btree索引来实现。btree是树形结构，索引分为根节点，分支节点和叶子节点。最后叶子节点的数据和表的数据行就会有一个最终的映射。

1、关于索引的内部信息
我们创建一个表。
create table index_test as select *from user_objects;
然后创建一个索引。
create index inx_test on index_test(object_id);
使用analyze来分析索引的信息，尽管在新版本中我们建议使用dbms_stats来替代analyze,但是analyze validate structure这个功能时analyze独有的，dbms_stats在这方面还不能完全替代analyze.
analyze index inx_test validate structure;
select height,blocks,br_blks,lf_blks,lf_rows,del_lf_rows from index_stats; --高度  索引总块数   枝干块数    叶子块数  叶子内行数    叶子中被删除的行数  

2、关于索引的访问方式
针对不同的使用场景使用不认同的访问方式。
2.1、索引唯一扫描
如下使用了INDEX UNIQUE SCAN，这是最快的索引访问方式,适用于'where object_id=258'。
SQL> create table a as select object_id,object_name,object_type from dba_objects;

Table created.

SQL>  analyze table a compute statistics;

Table analyzed.

SQL> create unique index ind_a on a(object_id);  

Index created.

SQL> set autotrace on
SQL> set linesize 150
SQL> select * from a where object_id=258;

 OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
       258 PLSCOPE_IDENTIFIER$
TABLE



Execution Plan
----------------------------------------------------------
Plan hash value: 2087649606

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    36 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| A     |     1 |    36 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_A |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=258)


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

2.2、快速索引全扫描
因为不涉及排序，所以快一些。
select object_id from a;

Execution Plan
----------------------------------------------------------
Plan hash value: 2248738933

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 86264 |   336K|   142   (0)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| A    | 86264 |   336K|   142   (0)| 00:00:02 |
--------------------------------------------------------------------------
没有走索引，排除null干扰
SQL> alter table a modify(object_id not null);

Table altered.

SQL> set autot traceonly exp
SQL> select object_id from a;

Execution Plan
----------------------------------------------------------
Plan hash value: 672397539

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       | 86264 |   336K|    50   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| IND_A | 86264 |   336K|    50   (0)| 00:00:01 |
------------------------------------------------------------------------------
2.3、索引全扫描
如果要对索引做排序，可以使用索引全扫描，适用于'order by'。
可以做个对比
SQL> select object_id from a order by object_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1197305525

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       | 86264 |   336K|   181   (1)| 00:00:03 |
|   1 |  INDEX FULL SCAN | IND_A | 86264 |   336K|   181   (1)| 00:00:03 |
--------------------------------------------------------------------------

SQL> select object_id from a;

Execution Plan
----------------------------------------------------------
Plan hash value: 672397539

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       | 86264 |   336K|    50   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| IND_A | 86264 |   336K|    50   (0)| 00:00:01 |
------------------------------------------------------------------------------
2.4、区间扫描
如果涉及索引列的区间值，可以用区间，适用于扫描between。
SQL> select * from a where object_id between 2000 and 3000;

Execution Plan
----------------------------------------------------------
Plan hash value: 1070634250

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   989 | 35604 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| A     |   989 | 35604 |    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_A |   989 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID">=2000 AND "OBJECT_ID"<=3000)
2.5、跳跃索引扫描
索引有好几列，语句只用一列。
SQL> create index ind_a on a(object_type,object_id,object_name);

Index created.

SQL> analyze table a compute statistics for all indexed columns;

Table analyzed.

SQL> select * from a where object_id=258;

Execution Plan
----------------------------------------------------------
Plan hash value: 4117514

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    36 |    46   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | IND_A |     1 |    36 |    46   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - access("OBJECT_ID"=258)
       filter("OBJECT_ID"=258)

3、索引和空值
创建索引会因为空值出现偏差；
空值不在索引中，查询会因为空值走不了索引。
如果需要输出非空的数据，加入is NOT NULL的过滤条件，索引就能正常启用。

4、约束和索引
用alter table test add constraint con_test_id_uq unique(id);添加约束，不要用modify。
约束和索引要尽量分开，因为约束disable的情况下，索引会连带删除，如果是分区表的全局索引那么影响也是全局性的。
索引和约束分离，索引列顺序不当会导致不走索引。

5、虚拟索引
使用虚拟索引，通过查看执行计划来比较前后的变化，如果提升幅度大，再来考虑创建对应的索引。
SQL>  create table t as select *from dba_objects where object_id is not null and rownum<100000;

Table created.
正常情况
SQL> select *from t where object_id=8040;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |  2898 |   336   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T    |    14 |  2898 |   336   (1)| 00:00:05 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=8040)

Note
-----
   - dynamic sampling used for this statement (level=2)
创建虚拟索引
SQL> create unique index inx_t on t(object_id) nosegment;

Index created.
发现没有使用虚拟索引
SQL> select *from t where object_id=8040;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   207 |   336   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |   207 |   336   (1)| 00:00:05 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=8040)

Note
-----
   - dynamic sampling used for this statement (level=2)
在会话级别启用虚拟索引
SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true;

Session altered.
虚拟索引生效
SQL> select *from t where object_id=8040;

Execution Plan
----------------------------------------------------------
Plan hash value: 1855406669

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   207 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |   207 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | INX_T |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=8040)

Note
-----
   - dynamic sampling used for this statement (level=2)

6、不可见索引
创建不可见索引会锁表，对业务有影响，对比虚拟索引，不可见索引有索引段，也有数据字典信息，这些虚拟索引没有。
SQL> create table t as select *from dba_objects where object_id is not null and rownum<100000;

Table created.

SQL> set autot trace exp stat
正常状态
SQL> select *from t where object_id=9445 ;


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |  2898 |   336   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T    |    14 |  2898 |   336   (1)| 00:00:05 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=9445)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         24  recursive calls
          0  db block gets
       1321  consistent gets
       1464  physical reads
          0  redo size
       1628  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
创建不可见索引
SQL> create unique index inx_t on t(object_id) invisible;

Index created.
不可见索引没有生效
SQL> select *from t where object_id=9445 ;


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1005 |   203K|   336   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T    |  1005 |   203K|   336   (1)| 00:00:05 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=9445)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1235  consistent gets
       1231  physical reads
          0  redo size
       1628  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
使不可见索引生效
SQL> alter session set "optimizer_use_invisible_indexes"=true;

Session altered.

SQL> select *from t where object_id=9445 ;


Execution Plan
----------------------------------------------------------
Plan hash value: 1855406669

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   207 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |   207 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | INX_T |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=9445)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          2  physical reads
          0  redo size
       1495  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
使不可见索引无效
SQL> alter session set "optimizer_use_invisible_indexes"=false;

Session altered.
将不可见索引变为正常索引
SQL> alter index inx_t visible;

Index altered.

SQL> select *from t where object_id=9445 ;


Execution Plan
----------------------------------------------------------
Plan hash value: 1855406669

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   207 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |   207 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | INX_T |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=9445)


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

逝鸿
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql索引原理深入解析
08-17
不生产资源，只是资源的搬运工，如有侵权，请及时联系，谢谢！ 目录： 一、 索引是什么？ 二、 索引存储模型推演 三. B+Tree 落地形式 四、 索引使用原则 五. 索引的创建与使用
索引大解析
weixin_44183721的博客
08-19 254
索引，索引分类，聚簇索引与非聚簇索引，索引覆盖，回表，索引失效，索引优化
查询无法解析索引的几种情况
后台开发
05-23 1万+
mysql使用索引能提高查询效率，但在有些情况下，你使用索引查询，索引并没有起作用。mysql&gt; select * from stu; +------+------+----------+ | id | age | name | +------+------+----------+ | 1 | 1 | zhangsan | | 2 ...
Oracle-表分析和索引分析解读
热门推荐
小工匠
12-01 4万+
概述当表没有做分析的时候，Oracle 会使用动态采样来收集统计信息。 获取准确的段对象（表，表分区，索引等）的分析数据，是CBO存在的基石，CBO的机制就是收集尽可能多的对象信息和系统信息，通过对这些信息进行计算，分析，评估，最终得出一个成本最低的执行计划。所以对于CBO，数据段的分析就非常重要。分析SQLanalyze table tablename compute statistics等同于
索引解析
龙傲天的博客
05-03 295
USE pubs GO /*表:authors 列:phone 索引:IX_cxphone*/ IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_cxphone') DROP INDEX authors.IX_cxphone /*--笔试列创建非聚集索引：填充因子为 30％--*/ CREATE NONCLUSTERED I
mysql之explain详解（分析索引的最佳使用）
乐乐的博客
04-17 3万+
在这里对explain的各个字段进行详细的分析，来帮助大家分析自己所写的sql是否最佳的使用了索引。
MySql索引算法原理解析
03-01
百度去查，但发现好多说得太复杂不好理解，结合各个文章总结一下（建议大概看文字，不理解不要紧，然后再看图的执行步骤然后在结合文字，这样一切就清晰好多）B-tree，B是balance，一般用于数据库的索引。...
毕设 Lucene解析索引PDF文档的内容
05-25
----使用iText解析PDF 文档代码 PDFBoxHello.java ----------- --PDFBox测试代码 PDFBoxLuceneIndex.java ------ --PDFBox创建PDF文件的Lucene索引 PDFBoxPathIndex.java ------- --PDFBox创建指定目录PDF文档...
matlab索引解析
12-01
这里主要详细的讲述了matlab中的索引解析
索引详解
sky_zzk的博客
03-28 2469
1.什么是索引？ 1.索引是一种能提高数据库查询效率的数据结构。它可以比作一本字典的目录，可以帮你快速找到对应的记录。 2.索引一般存储在磁盘的文件中，它是占用物理空间的。 3.适当的索引能提高查询效率，过多的索引会影响数据库表的插入和更新功能。 2.索引类型 数据结构维度 B+树索引：所有数据存储在叶子节点，复杂度为O(logn)，适合范围查询。 哈希索引: 适合等值查询，检索效率高，一次到位。 全文索引：MyISAM和InnoDB中都支持使用全文索引，一般在文本类型char,text,varch
索引详细解读
cc76825的博客
08-29 872
索引 1、概述 索引类似图书中的目录索引，可以提高数据检索的效率，降低数据库的IO成本。 Mysql官方的定义： 索引(Index)是帮助MySQL高效获取数据的数据结构。 可以理解为：快速查找排好序的一种数据结构。 MySQL在300万条记录左右性能开始逐渐下降，虽然官⽅方⽂文档说500~800w记录，所以我们就有必要使用上索引了。 2、索引的分类 主键索引 唯一索引 普通索引 全文索引 组合索引 2.1、主键索引 即主索引，根据主键创建的索引，不允许重复，不
SQL索引详解
L___Ping的博客
03-08 1万+
SQL索引详解转自：http://www.cnblogs.com/AK2012/archive/2013/01/04/2844283.htmlSQL索引在数据库优化中占有一个非常大的比例， 一个好的索引的设计，可以让你的效率提高几十甚至几百倍，在这里将带你一步步揭开他的神秘面纱。　　1.1 什么是索引？　　SQL索引有两种，聚集索引和非聚集索引，索引主要目的是提高了SQL Server系统的性能，...
MySQL索引解析
天行健，君子以自强不息
07-04 199
目录 1.MyISAM的索引实现 2.InnoDB的索引模型 3.B-Tree和B+Tree分析 4.覆盖索引 5.最左前缀原则 6.唯一索引和普通索引的性能差异 索引的出现是为了提高查询效率，但是实现索引的方式却有很多种，本质上就是用于提高读写效率的数据结构，主要有哈希表、有序数组和搜索树。这里主要讲MyISAM和InnoDB两个存储引擎的索引实现方式。 1.MyISAM的索引实...
chmod: changing permissions of `$ORACLE_HOME/bin/extjobO': Operation not permitted
逝鸿的博客
04-14 5413
APPLIES TO:Oracle Database - Enterprise Edition - Version 11.2.0.4 and laterInformation in this document applies to any platform.SYMPTOMSApplying Proactive Bundle / PSU Patch fails with ErrorFor Examp...
数据库的索引详解
请叫我王老魔
04-08 6316
索引的重要性：当你的数据库的性能出现问题了，那么就重新优化你的索引吧，这能够解决80%的性能问题，由此可见索引的重要性，尤其在数据量越来越大的时候，影响更加的明显，一个最优的索引能够轻易的将查询性能提高好几个数量级。索引的作用和优点： 1. 能够大大的提高数据的查询检索速度 2. 通过创建唯一性索引可以保证数据库中每一行的唯一性 3. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方
B+/-Tree原理及mysql的索引分析
tonyXf121的专栏
12-23 3万+
B+/-Tree原理 B-Tree介绍 B-Tree是一种多路搜索树（并不是二叉的）：        1.定义任意非叶子结点最多只有M个儿子；且M>2；        2.根结点的儿子数为[2, M]；        3.除根结点以外的非叶子结点的儿子数为[M/2, M]；        4.每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
数据库--索引详解
一只IT小小鸟
08-08 1万+
索引概述 数据库索引是用于提高数据库表的数据访问速度的。想要理解索引原理必须清楚一种数据结构「平衡树」(非二叉)，也就是b tree或者 b+ tree，重要的事情说三遍：“平衡树，平衡树，平衡树”。当然， 有的数据库也使用哈希桶作用索引的数据结构 。然而，主流的RDBMS都是把平衡树当做数据表默认的索引数据结构的。 索引特点  避免进行数据库全表的扫描，大多数情况，只需要扫描较少的索引页和...
MYSQL索引详解
酒吧七的博客
06-11 3万+
本文优化并补充了大佬的文章：https://www.cnblogs.com/chenshishuo/p/5030029.html索引的定义（索引别称index，key，键）在关系数据库中，索引是对表中一列或多列的值进行排序的一种存储结构，它是表中一列或多列的值的集合，而且其中包含了对应表中记录的引用指针。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。要注意的是，索引也是表的组...
c++ 解析索引文件shx
最新发布
07-25
c 解析索引文件shx是指对shx文件进行解析和读取操作。shx文件是ESRI Shapefile文件格式中的一种索引文件，用于加快对shp文件中几何对象的访问速度。 解析索引文件shx的过程包括以下几个步骤： 1. 打开shx文件：...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值