主页 / dba / 问题 / 30019
Accepted
Celeritas
Asked: 2012-12-08 23:05:37 +0800 CST

多属性索引的有用性

  • 772

如果一个索引中包含多个属性，那么在select其where子句使用索引中的一个属性的语句中是否会获得任何速度？

例如，获取一个T带有属性索引的表，a并且b. 索引对查询有用吗：

select * from T where a='foo'

我问是因为我正在阅读的书有以下我无法理解的陈述：

如果多属性索引的键确实是按某种顺序连接的属性，那么我们甚至可以使用该索引来查找第一个属性中具有给定值的所有元组。

index physical-design

3 个回答

  1. 例如，假设一个表 T 具有属性 a 和 b 的索引。索引对查询有用吗：

    select * from T where a='foo'

    那里有两个问题：

    • (a,b)这个查询完全可以使用索引吗？

      答案通常是肯定的。也许并非所有数据库都具有这种能力，但大多数主流数据库都有 AFAIK。将select替换为select a,b from ...，引擎不仅可以使用索引，而且根本无法访问实际表来回答查询。

    • 优化器会选择使用索引吗？

      这将取决于数据库系统以及优化器有多少关于数据的信息。如果它可以确定第一列“足够有选择性”，那么它很可能会使用它。如果没有，它可能不会。

    这是关于 Oracle XE 11g 的说明。

    SQL> create table T
  2  as select object_name a, rownum b, rownum c
  3  from all_objects;
Table created.
SQL> create index T_ab on T(a,b);
Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T');
PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from T where a = 'foo';

Execution Plan
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |    27 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |    27 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN      | T_AB |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

    all_objects视图包含有关数据库中存在的所有对象（表、视图、过程等）的信息。字段不是唯一的object_name，但没有很多重复项（至少在此数据库中），因此前导字段本身非常有选择性。
    对索引进行范围扫描，然后通过 rowid 查找估计的一行的成本将远低于进行全表扫描，因此优化器采用该路线。
    这是一个相当常见的情况和计划，您可能会经常遇到它（或其他数据库引擎上的类似情况）。

    现在这是一个不同的场景，优化器可以使用列的实际内容的详细图片来不同地优化事物：

    SQL> create table Q
  2  as select 'a' a, rownum b, rownum c
  3  from all_objects;
Table created.
SQL> create index Q_ab on Q(a,b);
Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'Q');
PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from Q where a = 'a';

Execution Plan
--------------------------------------------------------------------------
| Id  | Operation     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  | 18085 |   176K|    15   (7)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| Q    | 18085 |   176K|    15   (7)| 00:00:01 |
--------------------------------------------------------------------------

SQL> select * from Q where a = 'z';

Execution Plan
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |    10 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| Q    |     1 |    10 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN      | Q_AB |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

    该表是故意完全倾斜的，第一列'a'作为其唯一值。如果优化器知道这一点，那么它可以根据实际查询的键值选择不同的路径，如上所示。
    如果'a'被请求，使用索引是一个糟糕的举动——您需要遍历整个索引和整个表来获取所有行，这（可能很多）比仅仅扫描表更昂贵。
    如果请求的值不是'a'，则扫描索引效率更高，因为它可能不会返回任何行。

    这里有一些可能更令人惊讶的事情：索引 on(a,b)实际上可以在where子句b仅过滤时使用。

    SQL> select * from Q where b = 1000;

Execution Plan
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |    10 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| Q    |     1 |    10 |     3   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN       | Q_AB |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

    这是有效的，因为前导列几乎没有不同的值，但第二列本质上是唯一的。将其视为优化器按第一列对索引进行分区，然后对每个分区进行二进制搜索。如果分区数量很少并且其他标准相当有选择性，那将是有效的。（即在本例中不适用于表格T。）

    • 5
  2. Best Answer

    是的，但这取决于您搜索的索引键。

    把它想象成那些旧的“白页”电话簿之一。在电话簿中，人们在页面上按姓氏、名字的顺序排列。这意味着电话簿索引有两个组成部分。

    如果您要查找姓氏为“Smith”的所有人，您只需找到第一个“Smith”（很容易做到，因为它是有序的），然后继续阅读，直到您看到不是“Smith”的人.

    但是，如果您要寻找所有名叫“威廉”的人，那么您将遇到困难。你必须扫描电话簿中的每一个条目，收集答案，即使名字在电话簿的“索引”中。

    数据库索引（概念上）的工作方式完全相同。

    • 4

  3. PostgreSQL 经验。

    我们有 3 列的表

    table test with columns
 id, first, second

    那么我们有两个索引

    first_second_idx (first, second)
first_idx (first)

    那么如果我们使用语句（1）

     select * from test where first = 'whatever';

    查询规划器应使用索引 first_idx（例如，“whatever”值是所有记录的 5%）。它更快，因为索引更小，因此读取更少。

    查询 (2)

    select * from test where first = 'whatever' and second = 'something'

    很明显将使用索引 first_second_idx

    但是如果我们在两个查询中删除 first_idx，planer 应该使用 first_second_idx。

    如果此表没有索引，则会触发全表扫描。

    因此，如果您有来自应用程序的两种类型的查询：

    1. 仅使用 first_second_idx if
      • (1)查询速度喜人
      • 有很多插入更新，第二个索引会减慢这些操作
      • 表测试有很多记录和磁盘空间和/或内存（从两个索引读取时总是使用更多内存）很重要
    2. 使用两个索引如果
      • 两个查询的速度至关重要
      • 插入更新的速度并不那么重要
      • 磁盘空间和内存不是那么重要
    • 2

相关问题