一把双刃剑：复合索引

已有 3310 次阅读2012-12-25 14:29 |个人分类:索引| 复合, 的, 双刃剑

一把双刃剑：复合索引（转载）

4.4.1 复合索引的重要性

问：“是单字段索引好，还是多字段的复合索引好？”

答：“当然是复合索引。但是…”。大千世界，的确复杂，充满辨证。

首先，在大多数情况下，复合索引比单字段索引好。以税务系统的SB_ZSXX（申报类_征收信息表）为例，该表为税务系统最大的交易表。如果分别按纳税人识别号、税务机关代码、月份三个字段查询，每个字段在该表中的可选性或约束性都不强，如一个纳税人识别号有很多纳税记录，一个税务机关代码和同一月份记录就更多了，所以三个字段合起来，“某个纳税人识别号+某个税务机关代码+某月”的记录就少多了。因此，复合索引比单字段索引效率高多了。本人经验，很多系统就是靠新建一些合适的复合索引，效率大幅度提高。

但是，复合索引比单字段索引内部原理复杂，复合索引有两个重要原则需要把握：前缀性和可选性。如果糊里糊涂地滥用复合索引，效果适得其反。

所以说复合索引是把双刃剑，用好了，极大提高系统性能。用不好，在数据库里面放了一堆垃圾。

以本人的经验和见识，大胆放言：国内很多IT系统开发人员没有意识到应该优先设计复合索引，更没有充分理解复合索引的前缀性和可选性这两个重要原则。

4.4.2 我如何“戏弄”客户

虽然不是专业老师，但毕竟讲过多次课，而且教师世家出身，应该有点遗传因子，深知培训中互动和了解客户状况的重要性。

又想起一个中外教育方面的一个小小差异：当老师问，有什么问题吗？中国学生一般都以缄默假装已经懂了。而美国学生则喜欢以海阔天空方式乱提问题，假装也已经懂了。

于是乎，总结经验之后，我往往在讲座的第一天，就会以一个生动的例子，来与客户互动。既活跃讲座的气氛，也是在观察和了解客户的实际能力。遗憾的是，迄今为止，大部分客户都没有准确无误地回答出这个例子的正确答案。客户们一上来就被我“戏弄”了一把。

例子如下：

假设在员工表(emp)的(ENAME, JOB, MGR)三个字段上建了一个索引，例如索引名叫IDX_1。三个字段分别为员工姓名、工作和所属经理号。然后，写如下一个查询语句，并不断进行查询条件和次序的排列组合，例如：

Select * from emp where ENAME=’a’ and JOB=’b’ and MGR=3;

Select * from emp where JOB=’b’ and MGR=3 and ENAME=’a’;

Select * from emp where JOB=’b’ and ENAME=’a’ and MGR=3;

Select * from emp where JOB=’b’ and MGR=3;

Select * from emp where ENAME=’a’ and MGR=3;

Select * from emp where ENAME=’a’;

Select * from emp where JOB=’b’;

Select * from emp where MGR=3;

… …

问题很简单，在各种条件组合情况下，刚才建的索引(IDX_1)是用还是不用？也就是说对emp表的访问是全表扫描和还是按索引(IDX_1)访问？

只见课堂上马上开始活跃起来了：

“用！”

“不用！”

“可能用吧…”

“不知道”

…

如果您现在有条件，您先别着急看后面的答案，自己也测自己一把。看看你自己对Oracle复合索引原理掌握地如何。

再次表示遗憾的是，大部分客户都没有准确无误地回答出正确答案。原因是什么呢？不了解Oracle复合索引的第一个原理：复合索引的前缀性。

现在可以告诉大家正确答案：上述语句中只要有ENAME=’a’条件，就能用上索引(IDX_1)，而不是全表扫描。

4.4.3 复合索引原理和设计建议

? 复合索引第一个原则：前缀性(Prefixing)

先从例子说起。假设省、市、县分别用三个字段存储数据，并建立了一个复合索引。请记住：Oracle索引，包括复合索引都是排序的。例如该复合索引在数据库索引树上是这样排列的，即先按省排序，再按市排序，最后按县排序：

省市县（区）

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

北京北京东城

北京北京西城

北京北京海淀

… …

黑龙江哈尔滨道里区

黑龙江哈尔滨道外区

黑龙江哈尔滨香坊区

… …

黑龙江齐齐哈尔龙沙区

黑龙江齐齐哈尔铁锋区

黑龙江齐齐哈尔富拉尔基区

… …

湖南长沙芙蓉区

湖南长沙岳麓区

湖南长沙开福区

… …

湖南衡阳雁峰区

湖南衡阳珠晖区

湖南衡阳石鼓区

… …

Oracle不是智能的，它只会按图索骥，该索引结构是先按省排序的，所以只要给出省名，就能使用索引。如果没有省名，Oracle就成了无头苍蝇，乱找一气，变成全表扫描了。例如，如果你只给一个县（区）条件，如“开福区”，Oracle肯定不会使用该索引了。

? 关于skip scan index

有经验的读者读到这儿，可能会与作者商榷了。你说的不一定对吧，有时候复合索引第一个字段没有在语句中出现，Oracle也会使用该索引。

对，这叫Oracle的skip scan index功能，Oracle 9i才提供的。

skip scan index功能适合于什么情况呢？如果Oracle发现第一个字段值很少的情况下，例如假设EMP表有GENDER(性别)字段，并且建立了(GENDER，ENAME, JOB, MGR)。因为性别只有男和女，所以为了提高索引的利用率，Oracle可将这个索引拆成(‘男’，ENAME, JOB, MGR)，(‘女’，ENAME, JOB, MGR)两个复合索引。这样即便没有GENDER条件，Oracle也会分别到男索引树和女索引树进行搜索。

但是，本人认为，(GENDER，ENAME, JOB, MGR)索引本身设计是不合理的，它违背了复合索引第二个规则：可选性（Selectivity），见下面描述。

? 复合索引第二个规则：可选性（Selectivity）

您可能会问：复合索引中如何排列字段顺序？有什么规则？这就是复合索引第二个规则：可选性（Selectivity）规则。Oracle建议按字段可选性高低进行排列，即字段值越多的排在前面。例如，(ENAME, JOB, MGR，GENDER)，（县、市、省）。这是因为，字段值越多，可选性越强，定位的记录越少，查询效率越高。例如全国可能就一个“开福区”，而湖南省的记录则太多了。

? 复合索引设计建议

现在可以给出是否建单字段索引还是复合索引，以及复合索引的设计建议了。

1. 分析SQL语句中的约束条件字段

2. 如果约束条件字段比较固定，则优先考虑创建针对多字段的普通B*树复合索引。例如同时涉及到月份、纳税人识别号、税务机关代码三个字段的条件，则可以考虑建立一个复合索引

3. 如果单个字段是主键或唯一字段，或者可选性非常高的字段，尽管约束条件字段比较固定，也不一定要建成复合索引，可建成单字段索引，降低复合索引开销。

4. 在复合索引设计中，需首先考虑复合索引第一个设计原则：复合索引的前缀性（prefixing）。即SQL语句中，只有复合索引的第一个字段作为约束条件，该复合索引才会启用。

5. 在复合索引设计中，其次应考虑复合索引的可选性（Selectivity或Cardinality）。即按可选性高低，进行复合索引字段的排序。例如上述索引的的字段排列顺序为：纳税人识别号、税务机关代码、月份。

6. 如果条件涉及的字段不固定，组合比较灵活，则分别为月份、纳税人识别号、税务机关代码三个字段建立索引

7. 如果是多表连接SQL语句，注意是否可以在被驱动表（drived table）的连接字段与该表的其它约束条件字段上，创建复合索引。

8. 通过多种SQL分析工具，分析执行计划并以量化形式评估效果。

上述第7条，将在表连接章节再介绍之。

4.4.3 IT系统是面向客户的，不是给领导看的

我在Oracle经常干这种事情：客户给我们公司打电话：“某某领导要来我们这视察、检查工作了，我们的系统现在很慢，请你们公司赶快派人先来优化一下。”

2004年春节刚过，本人就被客户点名去干这种为领导打前站的工作。于是，赶在领导周三去现场视察的前夕，我偷偷摸摸潜入春寒料峭的上海滩，去从事秘密的地下优化工作。

这种优化工作如何干？对，自底向上。因为这是本人第一次接触这个系统，别说开发人员，连客户都不陪我，把我扔到机房，帮我敲了口令，就没人管我了。没人告诉我这个系统是干什么的，更没有人告诉我这个系统有哪些主要业务模块、主要业务流程等等。以至于那次我把系统优化好了，离开的时候，都不知道这个系统的具体名称。

没关系，我先从底层看起。好老的环境哦，还是Oracle 8.0.6。硬件配置还可以，IBM RS6000机器，4CPU/2G。你才上海一个区的数据嘛，才几个G，处理能力肯定没问题。再看数据库参数，乖乖，从未调过啊。这不相当于把奔驰车常年挂在一档开吗？调参数很简单，但Oracle 8.0.6需要重新启动，白天业务期间无法做。只好看应用了，当然也是我期待已久的。

8.0.6在性能分析方面比较土，只能用蹩脚的utlbstat,utlestat脚本，或者直接去视图里分析。我当然不会象前面的洋‘忽悠’一样，去查询内部视图，况且我一个人呆在黑古隆冬的机房里，想做秀也没人看啊。还是那句话，发现问题并不难，难得是分析和解决问题。喏，以下就是当时在10：00多业务高峰时的一些最消耗资源的语句：

BEGIN htjs.FP_QMKCJZ_T(:1,:2,:3); END

SELECT COUNT（*） FROM HTJS.FP_QYLYC WHERE NSRSBH = :b1 AND YF = :2 AND SFLB != 'S';

SELECT FP_DM, QS_HM,FP_SL FROM HTJS.FP_QYLYC WHERE YF=:b1 and SFLB = 'S' and FP_ZL = 's' and NSRSBH = :b2;

SELECT SWJG_DM,FP_DM,FP_QS_HM,FP_SL,SFLB FROM HTJS.FP_SFD WHERE TO_CHAR(SFRQ,'YYYYMM') = TO_CHAR(:b1) AND NSRSBH = :b2 AND (SFLB = '12' OR SFLB = '21' ) AND OLDFLAG = '0' ORDER BY ID;

SELECT MAX(BSYF) FROM HTJS.CB_QY_BSQK_TJB WHERE NSRSBH = :b1;

… …

以上述第2，3条SQL语句为例，当时系统已经在HTJS.FP_QYLYC表的如下字段上按顺序建立了一个复合索引：

YF ----- 月份

SWJG_DM ----- 税务机关_代码

NSRSBH ----- 纳税人识别号

FP_DM ----- 发票_代码

SFLB ----- 收费类别

QS_HM ----- XX_号码

看出问题了吧？虽然两条语句都含YF（月份）字段，符合前缀性原则，但Oracle实际上没有使用该索引，因为同一月份记录太多了，还不如全表扫描。更重要的是，该复合索引的字段顺序的设计上根本没有考虑各字段的可选性。

于是，我当时新建了一个索引：

create index ora_FP_QYLYC_2 on FP_QYLYC(NSRSBH ,YF,SFLB,FP_ZL) tablespace fpfs;