云社区 博客 博客详情

轻松掌握Oracle索引(一)

GaussDB100_DBA 发表于 2019-07-29 09:19:06 2019-07-29
0
0

【摘要】 1 引言业务系统都需要数据库,而数据库都离不开索引。大部分的数据库性能问题是由于索引使用不当导致的,因此索引的设计和使用非常重要。开发者在开发SQL和设计表时如果不考虑索引,或者随便添加索引,将给系统埋下性能隐患。为什么查询这么慢,CPU这么高?用了索引为什么还是这么慢?应该用这个索引还是那个索引?应该全表扫描还是索引扫描?为什么明明有这个索引就是用不上?应该创建单字段索引还是组合...

1      引言

业务系统都需要数据库,而数据库都离不开索引。大部分的数据库性能问题是由于索引使用不当导致的,因此索引的设计和使用非常重要。开发者在开发SQL和设计表时如果不考虑索引,或者随便添加索引,将给系统埋下性能隐患。

为什么查询这么慢,CPU这么高?

用了索引为什么还是这么慢?

应该用这个索引还是那个索引?

应该全表扫描还是索引扫描?

为什么明明有这个索引就是用不上?

应该创建单字段索引还是组合索引,组合索引是不是把查询用到的列都用上?

索引范围扫描、全索引扫描、快速全索引扫描、索引跳跃扫描,这么多索引扫描方式有什么不同,应该如何选择?

……

初学者遇到这些问题往往一头雾水,不知从何下手。这些问题大都跟索引相关,需要有索引的相关知识才能解释清楚。

首先,必须了解索引和表的内部结构,知道数据在表和索引中是如何存放的;其次,需要知道每种访问方式的不同特点,包括全表扫描、索引范围扫描、全索引扫描等。再扩展一些,还需要了解组合索引、函数索引和表的连接方式等技术。

大家不要被这些专用名词吓倒,只要掌握原理,多实践和思考,必能融会贯通、游刃有余。本文会用通俗的文字结合实例来讲解Oracle索引的原理,希望你读完后能有豁然开朗的感觉。


 

2      索引的结构

2.1      基础概念

为了掌握索引的原理,首先需要掌握一些基本概念。

(1)索引和表在磁盘上是存放在一起的还是分开的?

(2)一个表上的所有索引是在一起存放的还是独立的?

(3)索引和表在磁盘上的存放方式有什么不同?

(4)索引和表之间是如何关联的?

(5)索引的作用是什么?

 

2.1.1        索引和表是独立的对象

Oracle中有很多种对象,包括表、索引、存储过程、触发器、序列、视图等。这些对象都有自己的定义语句,保存在数据字典(Oracle系统创建的表)中。对于表和索引,数据字典中除了需要保存对象的定义,还需要保存对象的数据。

索引和表是独立的对象,它们分别拥有自己的存储空间,并不是放在一起的,所以它们可以存放在不同的表空间(每个表空间是一组数据文件的集合)中。一个表上如果有多个索引,那每个索引也是独立的对象,拥有自己的存储空间。两个索引之间是没有关系,好比字典的拼音索引和部首索引,在编排和使用时都是相互独立的

 

2.1.2        索引和表在磁盘上的存放方式

    表和索引的数据都存放在磁盘的数据块中,每个数据块一般固定为8K。数据块是数据读写的最小单位,即使只需要读取一条数据,也会把整块的数据读到内存中,写数据也一样。

表中的数据是无序的存放在数据库块中的,两个数据块完全没有关系,同一个数据块中的数据也没有关系。数据在表中的顺序也不一定是录入的顺序,后录入的数据可能在前面。

如果想从表中查询某条数据,在没有索引的情况下就只能扫描表的所有数据块了,即全表扫描。这就好比一本书没有目录,要找某个内容只能把整本书翻一遍。

与表的无序存放不同,索引是有序存放的。在存放索引的数据块上,数据都是有序的,并且数据块之间使用指针进行关联。

 

2.1.3        索引和表之间是如何关联的

索引中保存:索引的键值(索引列的值);数据在表中的地址(rowid)。

查字典时,根据拼音索引可以找到某个字的页码然后直接到该页查看详细内容。Oracle索引与查字典也很类似,如果要找userid=10的数据,那么就到userid索引中先找到目标数据的地址(rowid),然后根据数据地址到表中查看目标数据的详情。

那么,如果要找username=’abc’的数据呢,userid的索引就不好用了,需要使用username的索引了。这就好比你只知道汉字的写法就不能使用拼音检索,而只能用部首查字法了。

 

2.1.4        索引的作用

使用索引是为了快速找到目标数据。因为全表扫描很慢,这个前面说过。

只要你有查字典的经历,你就应该明白这个道理。一般而言,先根据拼音查到汉字的页码比直接翻字典内容快。有人可能会说,我查字典都是根据拼音直接翻字典的,也很快啊。那是因为汉语词典的内容也是按照拼音排序的。如果只知道一个汉字的写法,而不知道拼音,那么要从字典中找到这个字就没有那么快了吧?这时候,根据部首查到页码再查内容就快的多了。

 

重要知识点

数据块是数据读写的最小单位。

在表中数据是无序的堆放在一起的,而在索引中,数据块之间使用指针关联起来的,每个数据块中的数据也是有序的。

索引中只保存索引键的值而不保存整条数据,而且索引中还保存了数据在表中的地址。

使用索引的作用是为了快速查找到目标数据。

2.2      B+索引结构

Oracle有多种类型的索引,最常用的是B+树索引。如下图所示,这就是一个B+树索引的例子。不用管B+是什么意思,它就是一种树形结构,每个节点是一个数据块,每个父节点可以有多个子节点。

image.png

2.2.1        根节点、分支节点和叶子节点

与表不同的是,索引的数据块会组织成一个树形结构,索引块之间以指针的形式链接(所谓指针就是保存了对方的地址)。索引树有根节点块、分支节点块和叶子节点块。图中绿色部分表示表的数据,不是索引的结构,绿色部分实际是无序的,不要以为数据在表中也是排序的。

每个索引只有一个根节点块,但可以有多个分支节点块和叶子节点块。索引的层高是从根节点到叶子节点的高度。每个索引可以包含多层分支节点,但只能包含一层叶子节点。当索引层高是1时,索引只有一个根节点,并且根节点也是叶子节点。

索引的所有叶子节点在同一层,这一点不太好理解,记住这个结论即可。

 

2.2.2        索引块的内容

每个索引块包含N条数据。与表不同,它不需要包含一条完整的数据,只需要包含该条数据中索引的键值。例如,在userid列上创建的索引就只需要保存userid的值,而不用保存username的值。

除了键值外,每条索引数据还需要保存一个指针/地址,指向下一级的索引块或数据块。对于根节点和分支节点,它需要根据这个指针找到对应的子节点。对于叶子节点,它需要根据这个指针找到对应的数据地址,它保存的指针实际上就是rowid——数据在表中的地址。

在叶子节点中,每个数据块还会保存相邻的叶子节点的地址,这点对索引扫描非常重要,后面会详细解释。在每一层索引节点和每个索引块中,索引的键值都是有序的,默认是升序。

 

重要知识点

索引树有根节点块、分支节点块和叶子节点块。索引的所有叶子节点在同一层。

每个父节点中如果有N条数据,那么就有N个指针指向子节点。

在叶子节点中,每个数据块还会保存相邻的叶子节点的地址。

在每一层索引节点和每个索引块中,索引的键值都是有序的,默认是升序。

 

2.3      表和索引的存储分析

表和索引结构对开发者来说是看不到的,很多人缺少一个感性认识:

(1)在表中,一个数据块能放多少条数据?

(2)在索引中,一个数据块能放多少条数据?

(3)索引的层高一般是多少?

(4)索引和表谁占的空间大?

我们可以找一个数据量比较大的表来分析下表和索引内部特征。通过下面的语句可以收集表的统计信息,从而了解表和索引的内部情况。

SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname =>  't_userserviceinfo');

 

2.3.1        表的存储结构分析

从统计信息查询可以看到,该表有217万条数据,占用了53912个数据块,平均每行的字节数是164,平均每个块中存放了40个行。

SQL> select num_rows "数据行数", blocks "数据块数", avg_row_len "平均每行的字节

", trunc(num_rows/blocks) "平均每个块的行数" from user_tables x where x.table_

name = 'T_USERSERVICEINFO';

 

  数据行数   数据块数 平均每行的字节数 平均每个块的行数

---------- -----  --- ---------  ------ ----------------  ----------------------------

   2170606      53912              164               40

一个表占用多少数据块由多方面的因素决定:

1)数据行数,数据越多占用的空间越大。

2)每条数据的长度,如果表的列很少,并且每个列的长度都很短,那么表占用的数据块就少,反之则占用的数据块多。

3)如果很多数据被delete了,数据占用的数据块并不会被释放,这就是所谓的高水位,当有新数据insert时会优先占用空闲的数据块。

我们还可以计算出平均每个数据块被数据占用了多少字节:164*40=6560。数据块的大小是8K,其中6K存放的是数据,这是正常的,因为数据块还会有一些系统开销,而且数据块也会预留一部分空间(默认10%)以便数据被update的更长。

 

2.3.2        索引的存储结构分析

下面再来看看这个表上索引的情况。

SQL> select x.index_name, num_rows "数据行数", blevel +1"层高", leaf_blocks "叶子块数",

  2         trunc(num_rows/decode(leaf_blocks, 0, 1, leaf_blocks)) "平均每个叶子块的行数", x.avg_leaf_blocks_per_key "每个键值占的叶子块数", x.avg_data_blocks_per_key "每个键指向的表块数"

  3    from user_indexes x

  4   where x.table_name = 'T_USERSERVICEINFO';

 

INDEX_NAME                        数据行数   层高   叶子块数  平均每个叶子块的行数  每个键值占的叶子块数  每个键指向的表块数

------------------------------ ---------- ---------- ---------- -------------------- ------------------------- --------------------

IX_USERSERVICEINFO_FBEOPSTATUS          0    1          0         0         0        0

IX_USERSERVICEINFO_FBECOSTATUS          0    1          0         0         0        0

IX_USERSERV_CHANGETIME            1727428    3       4625       373         1        1

IX_USERSERVICEINFO_UPDATE         2159099     3       5787       373         1        1

IX_USERSERVICEINFO_RESERVETIME    2152584    3       5761       373         1        4

IX_USERSERVICEINFO_PHONE           2124917    3       6287       337         1        1

IX_USERSERVICEINFO_ID                2253141   3       4994       451         1        1

IX_USERSERVICEINFO_BRANDMODDA    2099463   3       5600       374         4      151

PK_T_USERSERVICEINFO                 2122099   3       6869       308         1        1

IX_USERSERVICEINFO_FSUSPSTATUS        9811   2         20       490        20     8729

IX_USERSERVICEINFO_FOPENSTATUS         15    1          1        15         1       15

IX_USERSERVICEINFO_FDELSTATUS           0    1          0         0         0        0

1)同一个表的索引存的数据并不一样多:索引中不包含空的键值

对于字典来说,不管是拼音索引还是部首索引,保存的汉字是一样多的。但是在Oracle索引却不是这样。通过上面的查询结果可以看到,每个索引的数据量都不一样,有的甚至是0。除了统计信息的误差外,主要的原因是索引中不包含空的键值。例如,对于username索引,如果某条数据的username是空,那么索引中就没有这条数据。

如果某个索引的大部分键值是空值,那么它占用的索引块就很少。这个特性也告诉我们,查找空的键值是不能走索引的。比如,如果想找username为空的数据,那么是不能使用username索引的,因为该索引中根本不包含这种数据。

 

2)索引的层高不会太高

对于200多万数据的表,索引的层高只有3,为什么这么小呢?

我们可以算一下一个3层索引大概可以存放多少索引数据。从上面的查询可以看到一个索引块大概能存放400个左右的数据。当索引只有1层时,可以保存400条数据。因为每个索引数据都有一个指向子节点的指针。所以当索引有3层时,最大可以容纳400*400*400=6400万条数据。如果索引是4层,就可以容纳上百亿的数据了。所以索引树的层级不会太高。

为什么一个数据块只能存放大约40条数据,而一个索引块却可以存放400条数据。那是因为一条数据的长度比一个索引键值的长度大的多。

如果一个索引键值也很长呢,例如在一个description列上建索引,那么一个索引块就只能存放几条数据,导致索引的层高增长很快,从而导致索引查询效率低。所以不建议在长度很大的列上创建索引。

索引的扫描一般是从根节点扫描到叶子节点,索引的层高对索引的扫描效率有很大影响。

 

3)索引占的数据块比较少

从上面的查询可以看到,单个索引只占了几千个叶子块(非叶子块可以忽略),比表占的数据块(5万多个块)要少得多。这个原因也是显而易见的,因为索引单条数据的长度比表单条数据的长度要小得多。虽然索引中还要保存rowid信息,叶子节点还要保存相邻节点的指针,但一般而言,索引占的空间比表要小得多。

当然,这也不是绝对的。当表中的列比较少时,索引和表占的空间也可能是差不多的。而且一个表上索引可能有多个,索引占的总空间可能不比表的空间小。所以我们在分配表空间时,索引和表的表空间一般也需要分配差不多的大小。

如果一个索引大部分都是空值,那么它占的索引空间就会非常小。

 

4)每个键值占的叶子的块数

每个键值占的叶子块数是个平均值,如果索引列是唯一的,那么每个键值最多占一个叶子块,如果索引列的重复值很多,那么就可能占很多个叶子块。

例如索引IX_USERSERVICEINFO_FSUSPSTATUS,它是创建在状态列上的索引,平均每个键值占了20个叶子块。实际上它总共也只有20个叶子块,因为所有的键值都是一样的。

如果一个键值的重复度很高,使用这个索引查询的效率就可能很低。前面讲过,索引高度决定了从根节点到叶子节点要查询几个索引块。而键值的重复度决定了要查询多少个叶子块。一个是纵向查询,一个是横向查询。

还用字典的例子,如果根据拼音查询某个特定字(CHANG),那么它只会对应字典的一个字,如果要查所有拼音是CHANG的字呢,那就可能对应了几十个字,如果要查所有CH开头的字呢,那就更多了。同样是索引扫描,但是键值的选择不同,导致查询的效率差距很大。

 

5)每个键指向的表的块数

每个键指向的表块数也是个平均值。一个索引键如果有40条数据,它一般只需要一个索引叶子块,但是在表中,这40条数据可能在1个块中也可能在40个块中。这个差别对查询的效率影响很大。

就像查字典,如果通过拼音一次检索出40个字,并且他们也都在一页里,那么只需要查看一页的内容。如果通过部首检索出40个字,虽然在索引处他们是在一起的,但实际这40个字很可能位于不同的页上,那么就需要翻40次字典,自然要费力很多。

 

重要知识点

索引中不包含空的键值,一般不能根据索引查询空值。

索引每层能容纳的数据随层高增大呈指数级增加,所以索引的层高不会很大。

不建议在长度很大的列或者很多字段的组合索引上创建索引,这样的索引树会很高,查询效率很低。

索引占的数据块一般比表少的多,如果一个索引大部分都是空值,那么它占的索引空间就会非常小。

如果索引键值的重复度很高,那么查询效率可能比较低。

当要查询的键值对应很多个数据块时,查询的效率比较低。

3      性能调优基础

当你提交一个任务给数据库时,你肯定希望快速得到结果,并且你不希望它消耗过多的系统性能,不能影响其他人的使用。

怎样才能快速得到结果?什么才算性能好呢?

快速得到结果很好理解,就是响应时间短,在最短的时间内完成数据库任务。所谓性能好就是尽量少的消耗系统的资源,尤其是重要的资源,那么这些资源是什么呢?本章将阐述这些内容。虽然看起来跟索引关系不大,但却是理解索引调优的基础。

3.1      IOCPUnetwork的关系

数据库运行资源主要有三部分:IOCPUnetworkIO是指把数据从磁盘读到内存的过程;CPU主要是指处理内存中数据的过程,有时也包括等待IO的时间;network指数据在网络中传输的过程。

首先我们来搞清楚它们之间的关系。如果系统的瓶颈在IO,即需要从磁盘读取很多数据,那么CPUnetwork就比较空闲;如果系统的瓶颈在CPU,即有大量的数据在内存中需要被反复处理,那么IOnetwork就比较空闲;如果系统的瓶颈在network,即有大量的数据需要在客户端和服务器间传输,那么IOCPU就比较空闲。

这三种性能瓶颈是此消彼长的关系。在做性能测试的时候,有时候CPU始终压不上去,原因是CPU不是系统的瓶颈,很可能IO是瓶颈。

实际应用中,网络的开销是比较小的,因为大部分的运算是在数据库完成的,所以需要重点关注IOCPU

如何降低IOCPU呢?

降低IO就是减少读取的磁盘数据块,降低CPU就是减少读取的内存数据块,所以读取的数据块越少,消耗的资源也就越少,性能越好。

重要知识点

数据库运行性能瓶颈主要有三种:IO、CPU和network,它们是此消彼长的关系。

做性能测试的时候,如果CPU压不上去,说明CPU不是系统的瓶颈,很可能IO是瓶颈。

读取的数据块越少,消耗的资源越少,性能越好。

 

3.2      逻辑读与物理读

逻辑读和物理读是影响性能的重要因素。

逻辑读是指要读的数据块已经在缓存中而不需要发生实际的IO,这时主要消耗CPU;而物理读是指缓存中没有要读的数据块,需要发生实际的IO,这时消耗的主要是IO。如果一些数据经常被读取,那么就很可能在内存中命中,不需要物理读。

逻辑读和物理读的开销差距很大,因为读内存比读磁盘要快得多,一般可以快10倍以上。那么同样读取一千个数据块,如果全是物理读可能需要1分钟,而如果全是逻辑读则可能只需要几秒钟。这也是为什么同样的语句,第一次执行往往比第二次执行慢的原因了。

 

重要知识点

逻辑读是指要读的数据块已经在缓存中并不需要发生实际的IO,这时主要消耗CPU;而物理读是指缓存中没有要读的数据块,需要发生实际的IO,这时消耗的主要是IO。

逻辑读是指在内存中命中数据块,它比物理读快10倍以上。

 

3.3      多块读、随机读和顺序读

通过前面的分析,我们看到IO是最消耗资源的,减少IO的次数是有效的性能调优方法,那么哪些方法可以减少IO的消耗呢?

*  合理的业务需求:

如果每次查询都要把一个千万级的表统计一遍,那必然带来大量的IO,即使是逻辑读也是非常影响性能的。

*  多块读

如果操作系统的缓存比较大,而且要读取的数据块是相邻的,那么就可以使用多块读,即一次IO读取多个数据块,这样可以减少IO的消耗。

对表做全表扫描是可以使用多块读的,因为表每次申请的空间(extent)都是连续的,而普通的索引扫描就不适合,首先索引块之间是指针相连的,而且同一个索引块中的数据在表中也不一定是连续分布的。想想查字典的例子,你应该能明白为什么通过索引访问内容不能使用多块读。

当然一次IO读取128个块的开销要比只读取1个块的开销大,但是肯定比使用128IO小的多。

*  顺序读和随机读

顺序读和随机读也是一组重要概念。

顺序读比随机读的效率高的多。如果要读100条数据,它们是连续分布的,那么只要读取两三个数据块,如果是随机分布在100个数据块的,那么就要读取100个数据块。而且磁盘读写时需要转动磁头,当然是读取连续的内容效率最高。

Redo的例子也可以说明这个问题,事务commit后,Oracle会确保数据更新信息写入Redo文件,而不直接写数据文件,由后台进程定时批量写入数据文件。为什么要多此一举呢?这样不是写了两遍磁盘吗?我想Oracle也是出于性能考虑,IO是非常消耗性能的,尤其是随机IO,而数据文件的写入位置就是随机的。Redo文件是由Oracle自己控制的,它可以顺序的写入,虽然多写了一次磁盘,但总体的性能是提高的。

重要知识点

性能调优的根本方法就是减少读取的数据块。

全表扫描表可以支持多块读,而索引扫描一般只能支持单块读。

顺序IO比随机IO的效率高,索引扫描一般是随机IO。

登录后可下载附件,请登录或者注册

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区),文章链接,文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:huaweicloud.bbs@huawei.com进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。
评论文章 //点赞 收藏 0
点赞
分享文章到微博
分享文章到朋友圈

下一篇:轻松掌握Oracle索引(二)

评论 (0)


登录后可评论,请 登录注册

评论