b树索引适合什么样的查询?什么是b树

TheDisguiser 2020-07-13 17:08:16 java常见问答 9055

我们都知道b树索引适合查询,小伙伴们知道b树索引适合什么样的查询吗?到底什么是b树呢?下面就来看看了解了解吧。

b树索引适合哪种查询?

有一张表:

create table cust(cust_id number,first_name varchar2(10),last_name 
varchar2(20));
由于经常使用last_name进行查询,在last_name上有一个索引
create index idx_cust_lname on cust(last_name);
加载数据
insert into cust values(1,'ACER','SCOTT');
insert into cust values(2,'STARK','JIM');
...
insert into cust values(10,'KHAN','BRAD');
加载完数据收集统计信息
exec 
dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'CUST',cascade=>true);

对于插入表的每一行记录,oracle都会在索引条目中存储rowid和列值(last_name),如下图:

两条虚线描述了rowid与表中列物理位置的关系

从表及索引中查询数据时,有三种可能的情况:

1、只需访问索引就可得到全部数据,不需要根据rowid访问表

2、访问索引,并通过rowid访问表

3、只访问表

场景1:所有的数据都在索引中

a、索引范围扫描:当优化器需要通过索引获取数据时,执行索引范围扫描

b、索引快速全扫描:当需要访问索引大多数数据行时,执行快速全扫描,索引结构较小,这通常会出现在计数查询中

a、索引范围扫描

select last_name from cust where last_name='ACER';

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_CUST_LNAME | 1 | 12 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
338 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

对于这个查询,oracle通过索引需要访问1个数据块,因为索引高度为1

b、索引快速全扫描

select count(last_name) from cust;

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FULL SCAN| IDX_CUST_LNAME | 3 | 15 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------

场景2:并不是所有数据都在表中

select first_name,last_name from cust where last_name='ACER';

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

场景3:仅访问表

select * from cust;

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 39 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| CUST | 3 | 39 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

工作原理:

估算创建一个索引所需空间:

可使用dbms_space.create_index_cost进行估算

set serveroutput on
exec dbms_stats.gather_table_stats(user, 'CUST');
variable used_bytes number
variable alloc_bytes number
exec dbms_space.create_index_cost('create index IDX_CUST_LNAME on 
        cust(last_name)
        ',:used_bytes,:alloc_bytes);
        print: used_bytes print: alloc_bytes

使用空间和分配空间

b树是什么?

b树索引适合什么样的查询

B树是相对而言的一种平衡多叉树,一般适用于外查找,它也可以看作是对2-3查找树的一种扩展,即它允许每个节点有M-1个子节点。

B树特点

1.根节点至少有两个子节点

2.每个节点有M-1个key,并且以升序排列

3.位于M-1和M key的子节点的值位于M-1 和M key对应的Value之间

4.其它节点至少有M/2个子节点

以上就是本篇文章的全部内容,关于java架构师如果你还有想了解的疑问,请记得关注我们网站了解具体吧。

推荐阅读:

b树索引和哈希索引的区别是?

b树索引原理是什么?

b树的删除操作,图解