理解索引（1）（摘自老白DBA日记）-CSDN博客

索引是什么东西？恐怕很多DBA玩了几年Oracle还没有彻底理解索引到底是什么东西。为什么有时候通过索引访问一张表会比较快呢？我们需要从头去了解一下索引到底是什么东西。可能70后的朋友小时候都用过新华字典，而80后、90后就可能没用过了。新华字典是按照拼音字母排序的，因此我们查找字典的时候可以通过汉字的拼音来查找。最简单的方式是根据拼音字母的大体位置先随便翻开一页，然后根据这一页的内容在此翻页，直到找到这个汉字。这种翻字典的方式有点土，速度也比较慢，不过这是我们使用的一种最原始的索引方式。为了提高查字典的速度，我们一般都会在字典侧面标出某个字母所在的位置，这样我们可以首先根据字母所在的位置，更为精确的判断某个字可能的位置。这种具有两级的索引，加快了定位的速度。

实际上Oracle的索引访问和我们刚才看到的翻字典是有点类似的，Oracle的索引是一种b树结构，学过《数据结构》这门课的朋友可能对b树比较熟悉，既然是树，就应该有树根、树枝和树叶。对一棵树的访问肯定要从根出发，然后经过树枝，最终到达树叶。如下图：

如果我们要查找一个ID=3的记录，而我们在ID字段上有索引，那么可以通过索引，直接找到ID=3的所有索引项，然后根据这些索引项一条一条的从表中找出所有需要的字段。这就是我们常见的索引访问的方式。

从索引的原理大家可以看出，通过访问索引是可以提高系统的访问速度的。但是通过索引访问就一定会提高性能吗？答案当然是否定的。大家都知道数据库访问数据的主要开销分为IO开销和CPU开销两部分。通过一次访问需要访问的数据块的总数量，我们就可以初步判断出操作的大体开销。如果有一个查询，需要查出一张有3000万记录的表中的2000万条记录，那么通过索引访问这张表可能就会比直接对这张表做全表扫描要慢许多。鉴于索引访问的这个特点，我们不能想当然的认为索引访问就是好的。

从上面的讨论我们已经知道了其实索引并不神秘，索引只是存储在数据库里的一个树状结构的数据，通过这个数据，我们可以提高某些对表访问的性能。那么下一个问题就是我们怎么在应用中设计合理的索引，从而达到最好的效果呢？可能有朋友要说了，既然索引有这么好的效果，那么我们给每个字段都创建一个索引不就可以了。刚才我们说了，索引是一种数据库中的特殊存储结构，那么当表中的数据变化的时候，索引是必须做同步的更新的，因此索引带来的并不仅仅是查询性能的提升，还会带来一个副作用，索引的更新是需要成本的，过多的索引可能会带来对写入操作性能的负面影响。基于这个原理，我们在做索引设计的时候需要统筹考虑，尽可能用最少的索引达到最佳的效果。事实上，我们的应用系统在设计索引的时候，很少索引是被统筹考虑后创建的，大多数索引都是系统运行过程中随意添加的。一个系统运行时间长了以后，系统中的索引就十分混乱了。我在做性能优化的时候经常会碰到一些表上面存在大量的索引，5/6个算少的，多的情况可能会有十多个。这些索引都是在出现了一些性能问题后，为了单一解决某个问题而添加的。一张表中存在这么多的索引，就不仅仅是维护索引所增加的那点开销的问题了。最为关键的是这张表上存在很多索引字段很相近的索引，经常会由于分析数据不准确而出现索引选择错误的现象，从而导致系统性能极为不稳定。

在这里，我又想到了一个十分著名的问题，就是一张表上到底设计多少个索引比较好。经常有朋友问我这个问题，而且也有很多文章和书籍上给出了一个十分明确的数字，就是最好一张表上不超过6个索引。我不知道6这个数字是怎么得出的，因为我从来没有在任何官方资料或者学术性论文中看到过6个索引这样的描述。好像TOAD里有一个简单的数据库健康检查工具，里面就有一项是检查表中索引超过6个的表，也许某些DBA就认为超过6个索引的设计可能是有问题的，而少于6个一般不会有问题。实际上用6个索引来判断索引设计是否合理是十分不恰当的，我曾经见到过有的表上面有十多个索引，但是这些索引都是必须的，也有些表上只有2/3个索引，但是索引的设计是存在问题的，用一个数字来区分合理不合理明显就有点偏颇。我想TOAD中的这个工具只是提醒系统中存在一些表上的索引过多，需要检查一下索引设计是否合理，而并不是说6个索引是合理不合理的分界线。另外一点要说明的，虽然索引会增加索引维护的成本，影响DML语句的性能，但是一般的OLTP系统中，SELECT操作和DML操作相比而言，SELECT操作所占的比重要高得多，大多数系统中SELECT 操纵所占的比例高达80%，甚至90%，在这种系统中，如果少一个索引，可能导致某张大表经常进行全表扫描，增加的CPU和IO开销可能达到这个索引维护成本的几十倍甚至100倍，如果你明白了这一点，就会知道判断索引是否合理不仅仅是6个这么简单的事情了吧。

我们一般分析索引是否合理的方法是将和某张表相关的SQL都查找出来，按照BUFFER GET或者PHYSICAL READ排序，分析排在前面的对系统性能影响较大的SQL，从中找出WHERE条件和连接条件，从而判断索引如何创建索引才更为合理。这是一项十分艰苦的工作，不仅仅需要技术，更需要的是认真的态度和坚韧的精神。不过这种方法下得出的索引设计原则是比较合理的。随后我们将会通过案例来详细介绍分析索引的方法，今天我们就不对这个方法进行深入的讨论了。