《Oracle SQL优化基础》之索引

发表: 2015-10-12 浏览: 6210

索引 Oracle 性能优化

最首先我想还是先百度下索引的定义：

索引，使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。

好的，就是因为这样一种朦胧两可的定义，导致现在绝大部分初学者甚至老油条都无法正确认识并使用索引。

这一篇主要还是讲下基础，再深的原理机制不再此篇幅中，如果大家都懂了，后续深入

下面分这几点给大家讲下：

索引的一些基本概念。
索引类型。
常用的树形索引结构。
索引的用法

ps. 本文是一些理论性、结论性的东西，具体实验不列入，可参考我博客内其他文章，如：

关于位图索引(BitMap index) http://flybi.net/blog/azzo/2015

关于index fast full scan http://flybi.net/blog/azzo/2016

索引的一些基本概念：

索引和表是一样是数据库的一种对象，与普通视图、函数、存储过程等对象不同的是，他是一个实际存在的物理结构，存储在表空间上（可指定）。所以如果索引建多了，要考虑存储的问题。

索引是有序的，可以是正序，也可以是倒序，在创建索引的时候可以指定。所以在做一些需要排序的函数（如sum、max等）的时候有索引的字段比无索引的字段更快。

在插入数据的时候索引会同步维护，所以索引多的表比索引少的表插入速度要慢。

索引不维护空值，也就是索引中不存储NULL。

索引有优势也有劣势，实战使用要评估优势大还是劣势大，不能追求速度一味的建立索引，也不能担心太多而拒绝索引。

索引类型

索引有多种类型，如果不指定则默认为B*树形索引，除此之外还有位图索引、函数索引、复合索引、全文索引等等

B树索引就是我们平时的Normal索引，是树形结构（本篇主要讲解B树索引）。

位图索引也是树形结构，但存储数据的方式和B树索引不同。

函数索引是建立索引时，对索引字段进行函数等处理。

复合索引是多个字段组成一个索引，第一列为引导列，使用不当会引起跳跃扫描或全表扫描。

全文索引就是将表里的内容进行分词，然后重新组合后存储，从而达到快速搜索的目的，一般不建议使用，容易出问题，且维护成本相当高，经常会比表本身还要大。官方解释：

A CONTEXT index is the basic type of Oracle Text index. This is an index on a text column. A CONTEXT index is useful when your source text consists of many large, coherent documents. Query this index with the CONTAINS operator in the WHERE clause of a SELECT statement. This index requires manual synchronization after DML.

http://docs.oracle.com/cd/E11882_01/text.112/e24436/csql.htm#CCREF0105

常用的树形索引结构

我们常说的索引就是B* tree index，也叫B树索引。是我们用得最多的一种，B树索引是树形结构，如下图：

Clipboard Image.png

可以看到每个节点中的数据都是有序的，左边是数据右边是rowid

B树索引中的几个概念：

根节点：就是上图中的最上面的节点，也就是索引中的第一层，相当于树根。

分支节点：除根节点和叶子节点的部分，相当于树枝。

叶子节点：最底层的节点，相当于叶子。

索引高度：有几层节点，索引高度就是几，如上图索引高度就是3（根节点-分支-叶子），非重复的数据量大也有可能出现多个分支节点，如根节点-分支-分支-叶子块，但一般不会超过5层，超过5层的索引比较少。

select t.blevel,t.* from dba_indexes t---Blevel就是索引高度 - 1，减掉的是根节点

聚簇因子：clustering factor，追踪索引项，如果第一行和第二行不在同一个数据块上，则聚簇因子加1，直至最后一行，最终的值就是聚簇因子的值。这是一个为了描述索引有序性和表的混乱度的参数，如果这个数值和索引的块数相同则说明表数据有序，如果和表中的行数相同，则说明表数据混乱。

select t.clustering_factor,t.* from dba_indexes t

选择性：索引的选择性是我这篇要讲的最重要的一点。

我面试过很多人问是否有优化SQL的经验，也有很多人告诉我说建立索引，但我问他们什么时候建立索引的时候，能答上来的基本少了一半，再往底层问基本就没几个能答上来了，能把索引从根源处彻彻底底的理解并回答上来的基本被我招为高级工程师，薪水基本在15k以上。

我说这个的目的是想说明一个现象，基本就是“这条SQL很慢”“加个索引吧，加了就快了”。。但是也有很多人发现加了还是很慢，于是网上开始出现了一批咨询“为什么我的SQL加了索引还是慢？”这种问题的人。

首先大家要搞明白一点，索引是什么，顾名思义索引（index）就是目录，举个栗子。

Clipboard Image.png

假如我们有1亩地（666.67平米），打算建一个图书馆（A，500平米）和图书馆目录（B，100平米）

我要去借一本书（查询数据），书名叫《五年高考三年模拟》，如果没有B，我只能直接去A挨个书架的找，如果书比较多，可能我要找上1天，这个过程叫全表扫描。

如果我又花钱建了个图书馆目录的房子B，那么我就可以先去B去找，看这本书在A里的哪一层、哪个房间、哪个书架、第几排、第几本。然后记录下来，再去A找，可能10分钟就找到了。这就是索引扫描。

如果图书管理有100万本书，我每次都只找一本书的话，那么我肯定会建立一个图书馆目录，这样会快很多，不过我建立图书馆目录会占用我50平米。

那么问题来了，我第一次建立的图书馆目录是根据书名建立的，如果这次我要找老头子写的书，那我又要建立一个图书馆目录B2，再次占用50平米，如果我的目录建立的多了，我这1亩地很快就用完了，就无法建立其他图书馆。这就是索引和表的关系

那么第二个问题来了，老头子的《Oracle SQL优化基础》出新书了，那么我会把书放到图书馆上，并且会在图书馆目录里也记录上这本书的位置，如果老头子的更新速度很快，一秒钟更新一次，那我天天不用干别的事了，就光在几个图书馆目录上记录就够我累的了。这就是索引维护。

好，第三个问题来了，如果来了个大单子，学校要借书，把图书馆一大半的书都要借走，那么如果是你，你会直接去图书馆找书，还是先去图书馆目录找书的位置呢？

我们来算笔账，图书馆有100万本，学校借80万本。

按照刚才找一本书的时间计算：

全表扫描：如果我直接找A，花费10天。

索引扫描：如果我先找B再找A可能花费10分钟。

学校借80万本，那么：

索引扫描：80w*10分钟=800w分钟=15年

全表扫描：10天

以上时间只是打个比方，供大家理解，如果上面讲的都能理解了，下面我们来说下结论：

一般来说，在Oracle数据库中，当我们要查询的数据小于表总体数据量的5%-10%，建立索引，使用索引扫描是非常快的（也有说20%的）。

如果大于这个数字，那么全表扫描的代价将会小于索引扫描。

玩LOL的朋友肯定能看懂下面这个例子：我在小龙的地方使用B键回城不会有人骂我，如果我在高地塔用B键回城，估计对面的人都要骂我了：）

现在大家用的都是Oracle11g，11g的优化器是CBO（Cost-Based Optimization）基于代价的优化器，所以如果你建了索引，发现还是慢，或者发现执行计划中没有走索引扫描，那么就说明CBO认为：索引扫描的代价 > 全表扫描的代价，根据最优法则，从而选择了全表扫描。

这里也有有人会说，我可以用Hint（提示）强制让Oracle走索引！

没错，是可以这样做，那么你就选择了让Oracle花费15年找那80万本书，Oracle会恨你的。

说到这里，大家是否明白了索引的选择性，所谓选择性就是（查询的结果）/（表中全部数据）的比例，比例越小，选择性越高。

索引的用法

讲完了上面，大家该问了，应该怎么合理的使用索引呢？

这里我们不讲解位图索引和复合索引等，只针对B树索引，其他索引等我有时间会再写。

1. 要选择基数大（重复数据较少）的列建立索引

因为我们通常写sql是这样的select xxx from table where id = 1

如果表有100w数据，id是1-100w的序列，那么id = 1就是我只查询了1条数据。

如果针对select xxx from table where gender = 'male'这种性别只有男女的列。

表数据还是100w，除非这100w里只有不到100个男人（应该不会存在这种情况吧 - -），否则Oracle仍旧会选择全表扫描，建立索引不但不会走索引还会增加维护索引的成本。再者，即便真的存在这种“女儿国”的情况，我在gender列建立了索引，也只能适用于查询where gender = 'male'这种SQL，而不适用于查询where gender = 'female'这种SQL了，这就属于基数小的列，这种列上不建议建立B*树索引。

2. 普通索引的索引列不要做函数或计算，如where to_number(id) = 1

这个就涉及索引存储的内容了，在B*树索引中，索引条目存储的内容有（KeyValue + Rowid）

KeyValue就是这个字段的值。也就是说如果我只查询书名，那么我可以只在图书馆目录里找就行了，不用再去图书馆A了，又省了不少时间。

rowid就是每行数据的物理地址，是唯一的，类似这本书在A里的哪一层、哪个房间、哪个书架、第几排、第几本，所以能快速查询数据。

根据索引存储的内容就可以看出来了，如果我的索引字段ID是1，那么我索引里存储的keyvalue也是1，如果我to_char了或者做了加减乘除的计算了，我在索引里就找不到这个值了，自然无法走索引扫描。

where to_char(id) = '1'是不会走索引的，我们有其他的解决方案走索引（如函数索引，不建议盲目使用）。

where id + 1 = 2 是不会走索引的，我们有其他的解决方案走索引（如函数索引，不建议盲目使用）。

where id = 1+1 是可以走索引的

where id = to_number('123')是可以走索引的，因为没有对索引字段做函数处理。

where id is null也不会走索引，因为上面说了，索引中不存储空值，我们有其他的解决方案走索引（如函数索引，不建议盲目使用）。

where id like 'laotouzi%'是可以走索引的

where id like '%laotouzi'是无法走Normal B树索引的，我们有其他的解决方案走索引（如反向索引，不建议盲目使用）。