位图索引与 B-tree 索引：选择与时间(转载)

了解每个索引的正确应用对性能会有很大影响。

一般认为，位图索引最适合于具有低相异值的列（如 GENDER、MARITAL_STATUS 和 RELATION）。然合，这种观点并不完全正确。实际上，对于那些并不经常由许多并行系统更新其数据的系统来说，总是建议采用位图索引。事实上，正如我此处所阐明的，在具有 100% 唯一值（主键的列候选键）的列上的位图索引与 B-tree 索引同样有效。

在本文中，我将提供一些示例和优化程序决策，它们对于低基数列和高基数列上的索引类型是通用的。这些示例将帮助 DBA 了解位图索引的使用实际上并不依赖于基数，而是依赖于应用程序。

比较索引

在唯一列上使用位图索引（ Oracle 不建议这种方法）有几个缺点，其中一个对充足空间的需求。然而，位图索引的大小依赖于列的基数，位图索引是在该列上创建,同时进行数据分配。因此，GENDER 列上的位图索引将小于相同列上的 B-tree 索引。相反，EMPNO（主键的候选键）上的位图索引将比此列上的 B-tree 索引大得多。但是因为访问决策支持系统 (DSS) 的用户比访问交易处理 (OLTP) 系统的用户要少，所以资源对于这些应用程序不是问题。

为演示这一点，我创建了两个表 TEST_NORMAL 和 TEST_RANDOM。我使用 PL/SQL 块在 TEST_NORMAL 表中插入一百万行，然后随机在 TEST_RANDOM 表中插入这些行：

注意 TEST_NORMAL 是条理的，而 TEST_RANDOM 表是随机创建的，因此 TEST_RANDOM 表的数据是无条理的。在上面的表中，列 EMPNO 包含完全相异的值，是很好的一个候选主键。如果您将此列定为主键，则您将创建一个 B-tree 索引而非位图索引，因为 Oracle 不支持位图主键索引。

要分析这些索引的行为，我们将执行下列步骤：

1. 在 TEST_NORMAL 上：
   1. 在 EMPNO 列上创建一个位图索引，并使用等式谓词执行某些查询。
   2. 在 EMPNO 列上创建一个 B-tree 索引，使用等式谓词执行某些查询，并比较由查询做出的逻辑和物理输入/输出，以取出不同值集的结果。
2. 在 TEST_RANDOM 上：
   1. 与步骤 1A 相同。
   2. 与步骤 1B 相同。
3. 在 TEST_NORMAL 上：
   1. 与步骤 1A 相同，除了查询是使用在一定范围的谓词执行之外。
   2. 与步骤 1B 相同，除了查询是使用一定范围的谓词执行之外。现在比较统计值。
4. 在 TEST_RANDOM 上：
   1. 与步骤 3A 相同。
   2. 与步骤 3B 相同。
5. 在 TEST_NORMAL 上：
   1. 在 SAL 列上创建一个位图索引，然后使用等式谓词执行某些查询，及使用范围谓词执行某些查询。
   2. 在 SAL 列上创建一个 B-tree 索引，然后使用等式谓词执行某些查询，和使用范围谓词（与步骤 5A 中相同的某些值集）执行某些查询。比较由查询完成的输入/输出以取出结果。
6. 将 GENDER 列添加到全部两个表中，并使用三个可能值更新列：M 代表男，F 代表女，null 代表不适用。基于一定条件使用这些值对列进行更新。
7. 在此列上创建一个位图索引，并使用等式谓词执行某些查询。
8. 在 GENDER 列上创建一个 B-tree 索引，并使用等式谓词执行某些查询。与步骤 7 的结果比较。

步骤 1 到步骤 4 涉及一个高基数（100% 不同）列，步骤 5 涉及一个普通基数列，而步骤 7 和步骤 8 涉及一个低基数列。

步骤 1A（在 TEST_NORMAL 上）

在此步骤中，我们将在 TEST_NORMAL 表上创建一个位图索引，然后检查此索引的大小、其群集因子和表的大小。然后我们将使用等式谓词运行某些查询，并使用此位图索引注明这些查询的输入/输出。

您会看到在前面表中索引的大小是 28 MB，而群集因子等于表中的行数。现在让我们使用等式谓词针对不同值集执行查询：

步骤 1B（在 TEST_NORMAL 上）

现在我们将丢弃此位图索引并在 EMPNO 列上创建一个 B-tree 索引。同以前一样，我们将检查索引的大小及其群集因子并针对相同的值集执行相同的查询，以比较输入/输出。

很清楚在此表中，B-tree 索引比 EMPNO 列上的位图索引更小。B-tree 索引的群集因子更加接近表中的块数；由于这一原因，B-tree 索引对于范围谓词查询效率更佳。

现在我们将使用我们的 B-tree 索引针对相同的值集运行相同的查询。

正如您可以看到的那样，针对不同的值集执行查询时，对于在完全唯一列上的位图和 B-tree 索引，一致性读请求和物理读取的数量是相同的。

位图		EMPNO	B-TREE
一致性读数	物理读数		一致性读数	物理读数
5	0	1000	5	0
5	2	2398	5	2
5	2	8545	5	2
5	2	98008	5	2
5	2	85342	5	2
5	2	128444	5	2
5	2	858	5	2

步骤 2A（在 TEST_RANDOM 上）

现在我们将在 TEST_RANDOM 上执行相同的实验：

                     1000000

再次，统计值（大小和群集因子）对于 TEST_NORMAL 表上的那些索引是相同的。

步骤 2B（在 TEST_RANDOM 上）

现在，与步骤 1B 中一样，我们将丢弃该位图索引并在 EMPNO 列上创建一个 B-tree 索引。

此表显示此索引的大小等于其在 TEST_NORMAL 表上的大小，但群集因子更加接近行数，这使得此索引对于范围谓词查询无效（我们将在步骤 4 中看到这种情况）。此群集因子将不会影响等式谓词查询，因为行具有全异的值，且每个键的行数为 1。

现在让我们来运行具有等式谓词和相同值集的查询。

同样，结果几乎与步骤 1A 和 1B 中的的结果一样。数据分配不影响唯一列的一致性读请求和物理读取的数量。

步骤 3A（在 TEST_NORMAL 上）

在这一步中，我们将创建位图索引（与步骤 1A 相似）。我们知道索引的大小和群集因子，它等于表中的行数。现在让我们使用范围谓词运行某些查询。

步骤 3B（在 TEST_NORMAL 上）

在这一步中，我们将对具有 B-tree 索引的 TEST_NORMAL 表执行查询。

为不同的范围集执行这些查询时，结果会显示如下：

位图		EMPNO（范围）	B-TREE
一致读数	物理读数		一致读数	物理读数
331	0	1-2300	329	0
285	0	8-1980	283	0
346	19	1850-4250	344	16
427	31	28888-31850	424	28
371	27	82900-85478	367	23
2157	149	984888-1000000	2139	35

您会发现，这一次两个索引的一致读数和物理读数又几乎一样。最后一个范围 (984888-1000000) 几乎返回 15,000 行，这是针对上面给出的所有范围所取得的行的最大数量。因此，我们请求了全表扫描（通过给出提示 /*+ full(test_normal) */），一致性读数和物理读数的分别为 7,239 和 5,663。

步骤 4A（在 TEST_RANDOM 上）

在这一步中，我们将在具有位图索引的 TEST_RANDOM 表上使用范围谓词运行查询，并检查一致性读数和物理读数。此处您将看到群集因子的影响。

步骤 4B（在 TEST_RANDOM 上）

在这一步中，我们将具有 B-tree 索引的 TEST_RANDOM 上执行范围谓词查询。记住，此索引的群集因子与表中的行数非常接近（且因而无效）。此处是优化程序关于这一点的建议：

此优化程序选择全表扫描，而不是使用索引，原因是群集因子：

位图		EMPNO（范围）	B-TREE
一致性读数	物理读数		一致性读数	物理读数
2463	1200	1-2300	6415	4910
2114	31	8-1980	6389	4910
2572	1135	1850-4250	6418	4909
3173	1620	28888-31850	6456	4909
2762	1358	82900-85478	6431	4909
7254	3329	984888-1000000	7254	4909

仅对于最后的范围 (984888-1000000)，优化程序选择了位图索引的全表扫描，然而，对于所有范围，它选择 B-tree 索引的全表扫描。群集因子是导致这一差异的原因：使用位图索引生成执行计划时，该优化程序不考虑群集因子的值，然而，对于 B-tree 索引，它会考虑群集因子的值。在这种情况下，位图索引执行会比 B-tree 索引更加有效。

下面的步骤列举关于这些索引的值得关注的更多情况。

步骤 5A（在 TEST_NORMAL 上）

在 TEST_NORMAL 表的 SAL 列上创建位图索引。此列具有正常的基数。

现在让我们来获取索引的大小和群集因子。

现在开始进行查询，首先使用等式谓词运行它们：

然后使用范围谓词：

现在丢弃位图索引，并在 TEST_NORMAL 上创建一个 B-tree 索引。

查看索引和群集因子的大小。

在上面的表中，您可以看到此索引比相同列上的位图索引大。群集因子也接近此表中的行数。

下面要进行测试了，首先使用等式谓词：

然后，范围谓词：

针对不同的值集执行查询时，输出结果（如下表中所示）揭示一致性读数和物理读数是相同的。

位图		SAL（等式）	B-TREE		取出的行数
一致性读数	物理读数		一致性读数	物理读数
165	0	1869	177	164
169	163	3548	181	167
174	166	6500	187	172
75	69	7000	81	73
177	163	2500	190	175

位图		SAL（范围）	B-TREE		取出的行数
一致性读数	物理读数		一致性读数	物理读数
11778	5850	1500-2000	11778	3891	83743
11765	5468	2000-2500	11765	3879	83328
11753	5471	2500-3000	11753	3884	83318
17309	5472	3000-4000	17309	3892	166999
39398	5454	4000-7000	39398	3973	500520

对于范围谓词，优化程序选择为所有的不同值集进行全表扫描（它根本不使用索引）然而，对于等式谓词，该优化程序使用索引。和以前一样，一致性读数和物理读数是相同的。

因此，您可以得出结论：对于一般基数列，优化程序针对两种类型索引做出的决策都是相同的，且在输入/输出之间不存在显著的差异。

步骤 6（添加 GENDER 列）

在低基数列上执行测试之前，让我们将一个 GENDER 列添加到此表中，并使用 M、F 和 null 值更新它。

此列上的位图索引的大小大约是 570 KB，如下表中所示：

相反，此列上的 B-tree 索引的大小为 13 MB，它比此列上的位图索引要大得多。

现在，如果我们使用等式谓词执行查询，则优化程序将不利用此索引，不管它是位图索引或 B-tree 索引。它将选择完全表扫描。

结论

现在我们已经了解优化程序是如何对这些技术作出反应的，接下来让我们分析一种情况，它将清楚地演示位图索引和 B-tree 索引各自的最佳应用。

在 GENDER 列已有位图索引，在 SAL 列上创建另一个位图索引，然后执行某些查询。将针对这些带 B-tree 索引的列重新执行这些查询。

从 TEST_NORMAL 表中，您需要得到月工资为下列任何值的所有男性雇员的数量：

1000
1500
2000
2500
3000
3500
4000
4500

执行：

这是一个典型的数据仓库查询，当然，您应该永远都不会在 OLTP 系统执行该查询。下面是在两个列上都有位图索引时得到的结果：

为 B-tree 索引时：

正如您此处可以看到的那样，使用 B-tree 索引，优化程序选择了全表扫描，然而在位图索引情形中，它使用索引来响应该查询。您可以根据取出结果所需的输入/输出数量来判断性能高低。

总之，不管基数如何，位图索引最适合于 DSS，原因如下：

• 使用位图索引，优化程序可以有效地响应包括 AND、OR 或 XOR 的查询。（Oracle 支持 B-tree 到位图的动态转换，但它可能效率很低。）
  
• 使用位图，在搜索空值或对其计数时，优化程序可以响应查询。位图索引也可以索引空值（与 B-tree 索引不同）。
  
• 最重要的是，DSS 系统中的位图索引支持即席查询，然而，B-tree 索引不支持即席查询。更为特殊的是，如果您有一个具有 50 列的表，且用户经常查询它们中的 10 个（查询全部 10 个或有时查询其中一个），则创建 B-tree 索引将非常困难。如果在所有这些列上创建 10 个位图索引，则所有查询都可以由这些索引来响应，而不管针对全部 10 列的查询，或其中 4 或 6 列的查询，或者是单个列上的查询。AND_EQUAL 提示为 B-tree 索引提供此功能，但一个查询仅能针对少于五个索引。使用位图索引将不会有这一限制。

但 B-tree 索引更适合于 OLTP 应用程序，在该应用程序中用户的查询是相对固定的（并在生产部署之前进行过很好的调整），这与即席查询相反，这些相对固定的查询出现频率更低，且在非高峰工作时间执行。因为数据在 OLTP 应用程序中经常更新，并已从 OLTP 应用程序中删除，所以在这些情形下，位图索引会导致严重的死锁问题。

本文提供的数据能让你清楚的了解各种情况。这两个索引都具有一个相似的目的：尽快地返回结果。但您选择使用哪个索引应该完全取决于应用程序的类型，而不是取决于基数的级别。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10805681/viewspace-417593/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10805681/viewspace-417593/