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+是什么意思，它就是一种树形结构，每个节点是一个数据块，每个父节点可以有多个子节点。

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      IO，CPU和network的关系

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

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

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

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

如何降低IO和CPU呢？

降低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个块的开销大，但是肯定比使用128次IO小的多。

  顺序读和随机读

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

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

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

重要知识点

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

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

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