本节书摘来自华章出版社《高并发oracle数据库系统的架构与设计》一书中的第2章，第2.3节，作者 侯松，更多章节内容可以访问云栖社区“华章计算机”公众号查看
现在，我们知道了b树索引的结构特点，也了解到其对查询和排序优化的意义，但是这并不代表我们就能建好用好索引了。在实际工作中，是不是还是会遇到走了索引反而查询变慢的情况呢？虽然说不是所有的情况下索引扫描都是优于全表扫描的，但是对于一套设计成熟的系统来说，索引扫描往往是值得坚持的，应该定期进行全库sql语句执行计划的审查，抓出全表扫描的sql进行优化。
说一千道一万，我们创建索引就是为了使用索引，尽可能地使查询操作能够走索引。但是，很遗憾，不是我们说走索引就能走索引，还是需要取决于cbo优化器的成本计算和比较情况。这其中会影响cbo优化器计算结果的因素有很多，比如：统计信息过旧、缺少直方图信息、数据分布影响索引选择度、索引聚簇因子偏差，等等。我们要用好索引，就必须要充分地了解这些特性和影响因素。
本节仍然沿袭概念结合实例的方式，首先，给读者介绍索引选择度、索引聚簇因子的概念；再辅以数据分布和数据存储的变化对索引使用的影响；最后，再分析实际工作中的一些常见的索引被无视的情况。
在这里，我们先抛出一个问题：什么时候需要用索引呢？有些人会说选择度20%以下，有些人会说10%以下，这样的回答都不尽然，甚至可以说是不负责的。我们还是需要根据具体情况来具体分析的。
众所周知，需要索引发挥出较高的使用效率，则在通过索引进行查询的时候，其选择度需要控制在一个较小的范围内。但是，也需要考虑数据的实际分布情况。这里我们引入两个概念：
索引理想选择度 = 1 / 索引列的distinct数；
索引实际选择度 = 返回结果集行数 / 全表行数。
通常情况下，我们所说的索引选择度就是上面所说的实际选择度。那么理想选择度是怎么来的呢？
我们知道当一个表的num_distinct值越接近num_rows值，则优化器越倾向于走索引扫描。那么最优情况是num_distinct=num_rows（典型代表为唯一索引，此类索引为理想状态索引，扫描效率最高），最差情况则是num_distinct=1（不考虑空表和null值）。引进一个比例num_rows/num_distinct来表示其关联性，该比例的值越小则越优。通过一般情况与最差情况的对比，可以得到一般情况下的选择度，这个选择度就是一般情况的理想选择度。
理想选择度＝(num_rows/num_distinct)/(num_rows/1)＝1/num_distinct
可以说理想选择度反映了优化器选择的倾向性。换而言之，如果希望优化器尽可能地选择索引扫描，则索引设计上尽可能地参考理想选择度的原则，它给索引设计提供一定的指导意义。
至于num_rows和num_distinct的值可以通过扫描表获取，也可以通过如下方式查询数据字典获取（必须收集统计信息）：
细心的读者读到这里应该已经察觉到了，我们这里说的理想选择度其实就是执行计划中“集势”（cardinality）的一个重要因子。集势因子是cbo优化器cost成本计算的重要标准，可以说它反映了优化器的计算方式，也是决定是否走索引的关键因素。
索引列的集势因子如表2-3所列：
从集势因子来看，不难看出“<>”和“not in”的情况比较难以高效使用索引。而对“＝”，“＜、＞、＜＝、＞＝”和“in”的情况，基本上都可以由1/num_distinct决定，也可以说它们最小公约数单元就是等值查询。这个因子，也就是我们所说的理想选择度，基本上可以反映索引列各种高效查询的优化器成本计算情况。
1.?均匀分布的情况
当数据分布是完全均匀的，则会发现理想选择度和实际选择度是相等的，也就是说理想选择度是一种完全理想化的数据分布状态。当cbo优化器在计算索引扫描成本的时候，如果缺少索引列直方图信息，则会认为数据是完全均匀分布的。这个时候无论col的取值是多少，其选择度都是一样的，即理想选择度等于实际选择度。此时成本的比较上，通过索引扫描再回表取数要远小于全表扫描，如图2-8所示。
然而，这种情况基本上是不存在的。如果索引列上的数据分布较为均匀，那么与cbo优化器计算的结果偏差就不会太大，反之，则会导致执行计划跑偏。所以，索引设计的另一原则就是“尽可能地保证索引列数据分布均匀一些”。
说到数据的分布均匀程度会影响索引的效率，先来使用理想选择度和实际选择度来考察一个均匀分布的例子吧。具体步骤和sql语句如下所示：
步骤1 创建一下相关的表和索引：
步骤2 初始化数据，顺序均匀地插入10万行数据，col1列num_distinct值控制为10：
步骤3 收集一下表和索引的统计信息，设置method_opt参数为'for all columns size 1'，先不收集直方图信息，看看数据分布对索引选择的影响。
此时，可以看到索引列col1上的数据分布是完全均匀的。所以，不论是col1=1，还是col1=5的情况，都是能够很好地利用索引扫描的。理想选择度和实际选择度均为10%，即优化器计算的选择度和实际情况是完全相符的。示例如下所示：
2.?非均匀分布的情况
如果说在索引列上的数据分布不是均匀的呢？如图2-9所示，来看一个比较极端的例子。col=2的行数占总行数的60%，那么查询col=2的时候，实际选择度则为60%，然而col为其他值的实际选择度仅为10%。如果按照实际选择度来走的话，col=2走全表扫描，其他情况走索引扫描是一个比较理想的选择。
但是，优化器的实际处理情况未必如此。这个时候，如果我们没有col列上的直方图，优化器会认为数据是均匀分布的，则理想选择度 = 1/num_distinct = 20%，很有可能都走了全表扫描。这对于col不为2的情况几乎是不能接受的。
这种情况相信在很多应用中都是存在的，只是有可能不会表现得这么极端而已。直方图的收集对于大表来说，是不可想象的，因为收集一次开销太大，对高并发的oltp系统几乎是不可能完成的任务。那如何去解决这个问题呢？这又将说回到索引设计的话题上来。对于这种数据分布极度倾斜的情况就不应该建索引，如果不得不建的话，尽可能收集直方图。
回到前面的例子，接下来我们人为来制造一些麻烦吧，打乱原有的均匀分布。如下例所示，可以看到，col1=5的记录行已经占了全表记录总数的50%，col1索引列已经严重倾斜。但是，抛开col1=5的情况，其他几种情况，都还是有比较好的选择度的。
表经过了大批量的dml操作，统计信息已经过旧了。如果仍采用旧的统计信息，上面基于col1的查询，仍然都将继续索引扫描的方式，这对于col1=5来说，就不是最优的了。
此时，需要重新收集一次表和索引的统计信息，仍然选择不收集直方图。因为没有直方图信息，优化器将无视数据分布的倾斜。下面的例子中，col1=1和col1=5的情况，都走了全表扫描，显然这个结果也是不能让人满意的。
利用理想选择度和实际选择度来分析一下吧。数据分布倾斜后，col1=5的实际选择度变为50%，其他情况仍为10%。但是没有直方图信息，优化器意识不到这一点，它会认为理想选择度是从10%变为了16.7%，都应该走全表扫描了。
换个角度来思考一下，如果我们没有idx_alex_t03_col1这个索引，情况会如何呢？当然也将是全部走全表扫描的。那不得不反问一句，idx_alex_t03_col1这个索引还有存在的意义吗？
要让idx_alex_t03_col1索引有存在的意义，就需要依赖于col1列上的直方图信息收集。我们重新收集一下统计信息和直方图吧，示例如下所示：
再来对比一下col1=1和col1=5查询的执行计划看看，我们神奇地发现了两种截然不同的情况，而且都是最优的。
说到这里，有人可能会提出质疑：“oracle 11g提供的自适应游标共享（acs）不是就可以根据不同的绑定变量生成不同的执行计划吗？”这真的是一个不错的主意，但也仅仅是不错而已。
我们不要忘了使用绑定变量的时候，oracle有一个绑定变量窥探的机制，sql语句第一次执行决定了以后同样的sql语句的执行计划。如果在缺失直方图信息的情况下，其结果和不绑定变量的情况是一样的。如果很幸运地拥有直方图信息呢？我们第一次执行的col1=:vid绑定变量:vid:=1，则会走索引扫描，第二次:vid:=5也将继续索引扫描。反过来的话，先执行:vid:=5，再执行:vid:=1，则都会走全表扫描。这对我们来说是没有什么帮助的。即使oracle 11g通过自适应游标共享进行了优化，但对数据分布倾斜也将是力不从心的。
细心的读者可能已经注意到了，上面我们说的两个例子，是很极端化的例子，一个过分均匀，一个过分倾斜，在实际应用中，我们还是需要中庸一点的。在索引设计和使用的时候，需要关注以下几点：
需要像优化器一样去思考，尽可能地参考理想选择度的原则；
尽可能保证索引列数据分布均匀，否则不要建索引；
如果不得不对数据分布严重倾斜的列建索引，请收集直方图信息，同时关注绑定变量窥探的影响。
再次回到上一节抛出的问题，什么时候应该用索引？前面我们分析过了索引选择度和数据分布的问题，而另一个会影响索引使用的关键因素就是索引聚簇因子（index clustering factor）。
什么是索引聚簇因子呢？索引聚簇因子是指按照索引列值进行了排序的索引条目顺序和其对应表中数据行顺序的相似程度。比较理想的状况就好比一对双胞胎，其相似程度是非常大的，此时索引使用是非常高效的。在oracle中，聚簇因子大小对数据读取效率有着直接的影响，聚簇因子值越小，则说明相似程度越大，索引使用将越趋于高效。
聚簇因子是如何影响索引的使用性能的呢？主要从i/o开销这个角度来思考。如图2-10所示，我们先来对比一下左右两种情况的聚簇因子。我们假设一个表中只有三个数据块（data block），每个数据块只能存储三行数据，共需存储9行记录。现有一个索引列，9行记录对应索引列值为123123123，在索引结构中对键值是有序排列的，那索引条目存储的顺序应该为111222333。右图中，表中数据存储的顺序也是111222333，和索引顺序是一致的；左图中表和索引存储的结构则是大相径庭的。按照聚簇因子的定义，左图可以视为较差（或者较大）的聚簇因子，右图可以视为较好（或者较小）的聚簇因子。
当进行索引扫描的时候，其实左右图并无什么区别，因为索引都是有序的，但是在回表取数的时候，右图的i/o开销明显小于左图的。现在假设每次i/o只能读取一个数据块，那么当查询col=1的时候，右图只需一次i/o，左图需要三次，i/o上的优势立现。优化器在cost计算的时候，对于左图的情况甚至会选择全表扫描，而放弃走索引。
为什么会造成表存储结构和索引存储结构不一致呢？索引结构的存储已经是有序排序了，所以不一致的问题出在表的存储上。我们通常所说的表都是堆表，其最大特征就是数据存储的独立性，数据的存储和数值本身没有任何关系，是随机存储在任意位置上的，即随机存储在任意的数据块上。
很幸运的是，oracle提供了索引的聚簇因子，可以通过数据字典查询到相关索引的聚簇因子信息，如下：
简单来说，clustering_factor反映的是通过索引扫描访问一张表，需要访问的表的数据块数量，即反映i/o的次数。这个clustering_factor是如何计算出来的呢？
1）扫描索引结构；
2）顺序对比相邻索引条目的rowid，如果两个rowid属于不同数据块，那么clustering_factor增加1；
3）整个索引扫描结束后，就可以得到该索引的聚簇因子数值。
了解了clustering_factor的计算方法，我们可以得出以下两个极端的情况，即聚簇因子最大和最小的情况：
clustering_factor最小时，其无限接近于表的数据块数，该表是按照索引字段顺序存储的；
clustering_factor最大时，其无限接近于表的行数，该表是完全不按照索引字段顺序存储的。
结合聚簇因子和选择度，基本上就可以确定索引扫描的cost开销了。
1.?聚簇因子较好的情况
接下来通过一个实例来验证一下聚簇因子对索引性能的影响吧。先考察聚簇因子较好的情况，此时clustering_factor的数值将会比较小。下面例子中，我们按照前面提到的“111222333”的方式进行数据入库。
步骤1 清空表alex_t03的数据：
sql> truncate table alex_t03;
步骤2 按照“11122233”的方式重新插入10万行数据，num_distinct值控制为200：
步骤3 重新收集一下表和索引的统计信息，这里不关注直方图信息：
主键列的数据入库是完全有序的，可以将主键索引视为一个理想的聚簇因子情况，将col1列的索引聚簇因子与之对比一下，可以发现clustering_factor数值上是差不多的，索引idx_alex_t03_col1的聚簇因子比较好，远小于数据行数，接近数据块数。统计结果如下所示：
当进行col1索引列的查询的时候，毋庸置疑的是进行索引扫描了，而且cost开销相对较小，也就是说索引利用比较高效。示例如下所示：
2.?聚簇因子较差的情况
如果按照“123123123”的方式进行数据入库是不是会出现另一个极端的情况呢？同样通过一个例子来看一下吧，仍然使用alex_t03表，仅仅改变数据入库顺序。
步骤1 清空alex_t03的数据：
步骤2 按照“123123123”的方式重新插入10万行数据，num_distinct值控制为200：
如预料的一样，clustering_factor变得很大，远远大于数据块数，更趋于接近数据行数，显示这是一个比较差的聚簇因子的情况。统计结果如下所示：
再进行一次同样的查询操作，发现优化器放弃了索引扫描而进行了全表扫描，这意味着此时全表扫描的开销更小。从下例的统计信息看到，全表扫描的物理读和逻辑读都比较好聚簇因子的情况要大得多，甚至出现了16次额外的内存排序操作。
即便如此，优化器仍认为全表扫描更优，那强制走一下索引扫描看看情况如何呢？如下例所示，比较索引扫描和全表扫描的统计信息似乎相差无几，但是cost开销、索引扫描大了很多。
此时，如果再对比聚簇因子较好和较差两种情况下的索引扫描情况，很容易就验证了我们之前的说法，较好的聚簇因子会带来较大的i/o优势。
在实际应用中，表中数据的入库有一定的随机性，其顺序是我们无法控制的，这意味着索引的聚簇因子也很难得到控制。这对我们在索引设计上也是一个较大的挑战，在新建索引之前，应该尽可能地了解业务数据的存储特性，保证数据存储顺序有一定的规律，即拥有较小的聚簇因子，索引才能高效使用。如果不能保证较小聚簇因子，要想通过后期的维护来修正，那基本上是不可能实现的。
综合以上内容，我们可以来总结一下了，一个设计优秀的索引应该具有以下特点：
具有较好的选择度，能参考理想选择度来界定；
索引列的数据分布足够趋于均匀化；
具有较小的聚簇因子。
当不知道如何把握的时候，可以参考一下唯一索引，其从各方面来思考都是最高效的，尽可能地使你的索引接近于它。
1.?复合索引设计
在工作中，我们时常会被问及一个问题：“我将如何选择索引列来建立复合索引呢？”举一个例子来说说看吧。
现在有一张表的三个列：a、b、c，在日常的业务应用中，会用到两两组合的查询，即：a and b，b and c，a and c，将如何建复合索引呢？这是一个很不明确的需求，我们很难以此为据来作出判断，需要进一步跟需求方沟通。
经过数据量和数据分布等情况的分析，确定是需要建索引的，问题将集中在如何去建，建什么样的索引。
架构师：“这三种场景发生的概率大致如何呢？”
开发者：“各占三分之一吧。”
架构师：“除此之外，还有这三个列上其他查询吗？”
开发者：“可能还有少量的b列和c列的单列查询吧，但是真的比较少。”
架构师：“那这三个列的数据区分度是怎么样的呢？”
开发者：“a列区分度比较低，b列和c列将比较高。”
架构师：“这真的是一个比较棘手的问题。……问题主要集中在这三个列的区分度上，如果a列很低，b和c很高的话，我建议可以考虑不建复合索引，只建b列和c列的单列索引。”
开发者：“这是你的第一个建议吗？但是我并不是很确定a列的区分度是否真的像预期的那么小，或者说b列和c列的区分度未必像预期的那么大。”
架构师：“是的，如果用单列索引就能解决问题，当然是最好不过的了。但是，从你的描述中，我对使用单列索引还是有所担心的，我建议可以考虑建（b，c）复合索引和c列单列索引。这样，（b，c）复合索引可以覆盖b and c查询和a and b查询，c列单列索引可以覆盖a and c查询，因为a列区分度不高，可以不考虑其走索引。”
开发者：“那为什么不干脆建（b，c），（b，a），（c，a）三个复合索引呢？”
架构师：“这个当然很好了，但是索引维护开销太大，未必是好事。”
开发者：“那能不能使用index skip scan的特性呢？”
架构师：“非必要时不要用，尽可能选择区分度比较高的列作为复合索引的前导列，这样前导列单列查询的时候也能很好地使用复合索引。”
开发者：“那能不能建三个列的复合索引呢？”
架构师：“能是能，但是不建议这么干。你这个表数据量比较大的话，你可以相像一下这个索引结构会多复杂，后期维护也是很痛苦的。”
架构师：“好了，我们现在一共有三个方案。第一，b列和c列的单列索引；第二，（b，c）复合索引和c列单列索引；第三，（b，c），（b，a），（c，a）三个复合索引。其中，第二个是最优推荐的。”
开发者：“好的，那就按照第二个来建吧。”
架构师：“还不行。我们需要请开发dba做一次影响分析后，再确定是否可以建。”
开发者：“影响分析？怎么做的呢？”
架构师：“就是对比索引建立前后该表上所有sql语句的执行计划的变化情况。如果变好了，自然没有问题，如果变差了，就不能创建了。如果必须创建，就需要使用outline或者spm固定有可能变差的执行计划后，再行创建。”
开发者：“那不是很麻烦吗？不这么干行不行呢？”
架构师：“是很麻烦，但是为了保证系统的稳定运行，这是必不可少的。宁愿把难度做在设计阶段，也不要把难度留给运维阶段。”
通过上述的一段工作对话，我们大致可以了解到一些复合索引设计和使用的小技巧了吧。其实，上面提到的影响分析，不只是在建复合索引的时候需要做，在建单列索引和收集统计信息等有可能造成执行计划变化的dba操作都需要做。
2.?使用实例
复合索引的设计很大程度上可以参考单列索引来进行，其比较大的一个特点就是前导列和后置列的选择问题。通过下面一个例子，我们来对比看一下吧。具体步骤和sql语句如下所示：
步骤2 初始化数据，制造a列超低区分度，b、c、d三列较高区分度：
步骤3 同样收集一下表和索引的统计信息：
对于查询条件覆盖了所有复合索引列，不论是低区分度的前导列还是高区分度的前导列，同样走了index range scan，达到较好的预期效果。示例如下所示：
在复合索引的使用中，查询条件覆盖所有复合索引列的情况是最优的。如果在建索引的时候，能够充分考虑到这一点自然是最好的。就像前一节的场景对话，如果能创建（b，c），（b，a），（c，a）三个复合索引，完全覆盖查询场景自然是最优的选择，但是也不得不考虑其运维代价。说白了，就是要用更少的、结构更简单的索引来达成更多的场景需求，实现索引的高效使用。
对于复合索引来说，其高效在哪里呢？索引列全覆盖自然不用说，无论如何都是高效的。问题应该集中在前导列和后置列的使用上。众所周知，复合索引的前导列是可以覆盖其单列查询的，而后置列则未必，除非满足index skip scan的条件。如下面的例子所示。
对于较低区分度前导列的复合索引idx_alex_t04_ab来说，当发生前导列单列查询是无法使用到索引的，或者说不使用索引效率更高。
在后置列查询中，我们看到index skip scan的效率还是挺高的。那是因为我们选择的前导列a有极低的区分度，否则的话，其效率不会太高，或者不使用索引扫描而选择全表扫描。
可以这么说，一个前导列区分度不高的复合索引，满足不了前导列单列查询的需要，也未必能较好地满足后置列的单列查询需要。这样的复合索引的使用效率就显得不高了。
反观前导列区分度较高的复合索引idx_alex_t04_cd，前导列的单列查询毋庸置疑地满足了高效索引扫描。如下所示：
对于后置列的单列查询来说，就直接说“不”，简单干脆，便于把握，避免了“未必”这种模棱两可、难以把握的情况，也减少了后期出现问题和偏差的几率。如下所示：
小结一下复合索引的设计和使用吧：
在复合索引的应用中，查询条件覆盖所有的索引列，查询效果最优；
尽可能地用更少的、结构更简单的索引来达成更多的场景需求；
一般来说，在复合索引创建的时候，前导列都建议选择区分度较高的，故index skip scan往往就是被优化对象。
说一千道一万，索引创建了就是要用的，可偏偏很多时候，索引被sql语句无视了，没有被用上，此时往往需要dba的介入进行优化。抛开索引本身的问题不说，哪些情况下索引会被无视呢？下面我们展开几个比较典型的场景来讨论一下吧。准备工作具体步骤如下：
步骤2 收集一下表和索引的统计信息：
1.?列与列的对比
当进行单表查询的时候，发生列和列的对比是无法走索引的，即使对比的两个列上都有索引。这里，我们也可以想象一下，如果需要它走索引，将会是怎么样的呢？pk_alex_t02和idx_alex_t02_objid两个索引进行联立对比，筛选出等值的情况，这样的做法远不如进行全表扫描来得高效了。示例如下所示：
null值一直是一个很难搞的东西，在查询、排序等操作时经常制造麻烦。在表的设计当中，我们就应该尽可能避免null的出现，赋予一些没有实际意义的缺省值来取代null值。在索引建立的时候，我们很难去给null建立合适的条目，进行null值查询的时候，也将不大可能进行索引扫描。也就是说一个表的列值存在null值时，该列的索引是不会为null值创建条目的，那么索引的值是少于表的值的，null值的查询过程自然就忽略了索引。如下所示：
我们知道索引会给每个索引列的值对应一个索引条目，当告诉索引选择出某个值或某个范围的值时，索引就会对应到相应的索引条目。反过来看，当告诉索引不要选择某个值或某个范围的值，索引就很难对应相应条目了。
下面的例子中，<>、not in、not exists的情况都是很难使用到索引的：
4.?like前置通配符
当使用like进行模糊查询的时候，我们一般推荐使用后置的通配符，这样可以较好地利用索引扫描，较为高效。示例如下所示：
| 0 | select statement | | 2 | 198 | 3 (0)|
| 1 | table access by index rowid| alex_t02 | 2 | 198 | 3 (0)|
但是实际使用中，很多时候是我们不得不使用前置通配符，这样的操作是无法使用索引的。为什么呢？我们试想一下，如果进行索引扫描的话，索引结构是有序的，模糊的前置很可能要扫描绝大部分或全部的索引块，再回表取数时，也很有可能扫描绝大部分的数据块，这样的cost开销是非常大的，优化器宁愿选择直接进行全表扫描，实际cost开销会更节省一些。所以，前置通配符的模糊查询是不能走索引的。
业务系统设计的时候，尽可能地考虑到模糊查询，避免使用低效的前置通配符，而更多地使用能走索引的较高效的后置通配符。
5.?条件列上使用函数
在下面的例子中，在索引列上使用了函数，这同样是无法使用索引的。在实际应用中，尽可能地使用第二种方式，在变量上使用函数转换后，再与索引列进行对比。当不得不在索引列上使用函数的时候，就必须在该列上创建函数索引。但是，函数索引不是一个很高效的东西，应尽量避免使用或者少用。示例如下所示：
说到此处，数据类型隐式转换的问题是不得不提的。隐式转换实质上也是一种函数转换的操作，只不过是没有明确写明函数，由oracle自动完成的。如下面的例子，id列是number型的，如果查询id='12345'，oracle会自动转换为id=12345后，再进行查询，转换的过程实质上就是做了一次to_number函数的转换操作。这样同样是无法进行索引扫描的。sql语句如下：
sql> select * from alex_t02 where id = '12345';
sql> select * from alex_t02 where id = to_number('12345');
在实际应用中，数据类型隐式转换是需要尽量避免的，隐式转换不但不能进行索引扫描，而且会影响绑定变量的使用。
6.?高区分度前导列的复合索引后置列查询
高区分度前导列的复合索引无法用于后置列查询，这个例子其实前面的章节已经讨论过了。如果前导列区分度很低，可以走index skip scan，如果前导列区分度高的话，进行index skip scan时分裂逻辑子索引开销将非常大，不如直接走全表扫描。示例如下：