关于索引的常见问题和神话

最新推荐文章于 2024-03-04 14:27:01 发布

weixin_34344403

最新推荐文章于 2024-03-04 14:27:01 发布

阅读量146

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/1862478/blog/1926330

版权

2019独角兽企业重金招聘Python工程师标准>>>

1视图能使用索引吗？

与这个问题相关的另一个问题是：“能对视图加索引吗？”视图实际上就是一个存储查询（stored query）。Oracle会把查询中访问视图的有关文本代之以视图定义本身。视图只是为了方便最终用户或程序员，优化器还是会对基表使用查询。使用视图时，完全可以考虑使用为基表编写的查询中所能使用的所有索引。“对视图建立索引”实际上就是对基表建立索引。

2 Null和索引能协作吗？

B*树索引（除了聚簇B*树索引这个特例之外）不会存储完全为null的条目，而位图好聚簇索引则不同。

要看到不存储null值所带来的影响，请考虑下面这个例子：

scott@ORCL>create table t ( x int, y int );

表已创建。

scott@ORCL>create unique index t_idx on t(x,y);

索引已创建。

scott@ORCL>insert into t values ( 1, 1 );

已创建 1 行。

scott@ORCL>insert into t values ( 1, NULL );

已创建 1 行。

scott@ORCL>insert into t values ( NULL, 1 );

已创建 1 行。

scott@ORCL>insert into t values ( NULL, NULL );

已创建 1 行。

scott@ORCL>analyze index t_idx validate structure;

索引已分析

scott@ORCL>select name, lf_rows from index_stats;

NAME                              LF_ROWS
------------------------------ ----------
T_IDX                                   3

这个表有4行，而索引只有3行。前三行（索引键元素中至少有一个不为null）都在索引中。最后一行的索引键是（NULL,NULL）,所以这一行不在索引中。倘若索引是一个惟一索引（如上所示），这就是可能产生混淆的一种情况。考虑以下3个INSERT语句的作用：

scott@ORCL>insert into t values ( NULL, NULL );

已创建 1 行。

scott@ORCL>insert into t values ( NULL, 1 );
insert into t values ( NULL, 1 )
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.T_IDX)


scott@ORCL>insert into t values ( 1, NULL );
insert into t values ( 1, NULL )
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.T_IDX)

这里并不认为新的（NULL,NULL）行与原来的（NULL,NULL）行相同：

scott@ORCL>select x, y, count(*)
  2  from t
  3  group by x,y
  4  having count(*) > 1;

         X          Y   COUNT(*)
---------- ---------- ----------
                               2

看上去好像不可能的，如果考虑到所有null条目，这就说明我们的惟一键并不惟一。事实上，在Oracle中，考虑惟一性时（NULL,NULL）与（NULL,NULL）并不相同，这是SQL标准要求的。不过对于聚集来说（NULL,NULL）和（NULL,NULL）则认为是相同的。两个（NULL,NULL）在比较时并不相同，但是对GROUP BY 子句来说却是一样的。所以应当考虑到：每个惟一约束应该至少有一个确实惟一的NOT NULL列。

关于索引和null值还会提出这样一个疑问是：“为什么我的查询不使用索引？”下面是一个有问题的查询：

select * from T where x is null;

这个查询无法使用我们刚才创建的索引，（NULL,NULL）行并不在索引中，因此使用索引的话实际上会返回错误的答案。只有当索引键中至少有一个列定义为NOT NULL时查询才会使用索引。例如，以下显示了Oracle会对X IS NULL谓词使用索引（如果索引的索引键最前面是X列，而且索引中其他列中至少有一列是NOT NULL）：

scott@ORCL>create table t ( x int, y int NOT NULL );

表已创建。

scott@ORCL>create unique index t_idx on t(x,y);

索引已创建。

scott@ORCL>insert into t values ( 1, 1 );

已创建 1 行。

scott@ORCL>insert into t values ( NULL, 1 );

已创建 1 行。

scott@ORCL>begin
  2  dbms_stats.gather_table_stats(user,'T');
  3  end;
  4  /

PL/SQL 过程已成功完成。

再来查询这个表，会发现：

scott@ORCL>set autotrace on
scott@ORCL>select * from t where x is null;

         X          Y
---------- ----------
                    1


执行计划
----------------------------------------------------------
Plan hash value: 2946670127

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |     5 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T_IDX |     1 |     5 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("X" IS NULL)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        584  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

前面说过，B*树索引中不存储完全为null的条目。假设你有一个表，其中每一列只有两个可取值。这些值分布得很不均匀，例如，90%以上的行（多数行）都取某个值，而另外不到10%的行（少数行）取另外一个值。可以有效地对这个列建立索引，来快速访问那些少数行。如果你想使用一个索引访问少数行，同时又想通过全面扫描来访问多数行，另外还想节省空间，这个特性就很有用。解决方案是：对多数行使用null，而对少数行使用你希望的任何值；或者如前所示，使用一个基于函数的索引，只索引函数的非null返回值。
既然知道了B*树如何处理null值，所以可以充分利用这一点，并预防在全都允许有null值的列上建立惟一约束（当心这种情况下可能有多个全null的行）.

3外键是否应该加索引？

外键未加索引是我导致死锁的最主要的原因；这是因为，无论是更新父表主键，或者删除一个父记录，都会在子表中加一个表锁（在这条语句完成前，不允许对子表做任何修改）。这会不必要地锁定更多的行，而影响并发性。人们在使用能自动生成SQL来修改表的某个工具时，就经常遇到这种问题。这样的工具会生成一个更新语句，它将更新表中的每一列，而不论这个值是否被UPDATE语句修改。这就会导致更新主键（即使主键值其实从未改变过）。

如果有一个ON DELETE CASCADE，而且没有对子表建索引。例如，EMP是DEPT的子表。DELETE FROM DEPT WHERE DEPTNO = 11.会级联至EMP。如果EMP中的DEPTNO没有加索引，就会导致对EMP执行一个全表扫描。这种完全扫描可能是不必要的，而且如果从父表删除了多行，对于删除的每一个父行，都会把子表扫描一次。
从父表查询子表时。还是考虑EMP/DEPT的例子。在DEPTNO上下文查询EMP表相当常见。如果频繁地执行以下查询来生成一个报告或某个结果：

select *
from dept, emp
where emp.deptno = dept.deptno
and dept.dname = :X;

如果没有索引,会使查询减慢。建议对嵌套表中的NESTED_COLUMN_ID加索引。嵌套表的隐藏列NESTED_COLUMN_ID实际上就是一个外键。
那么，什么时候不需要对外键加索引呢？一般来说，如果满足以下条件则可如此：
未删除父表中的行。
不论是有意还是无意（如通过一个工具），总之未更新父表的惟一/主键值。
不论从父表联结到子表，或者更一般地讲，外键列不支持子表的一个重要的访问途径，而且你在谓词中没有使用这些外键列从子表中选择数据（如DEPT到EMP）。
如果满足上述所有3个条件，就完全可以不加索引，也就是说，对外键加索引是不必要的，还会减慢子表上DML操作的速度。如果满足了其中某个条件，就要当心不加索引的后果。
如果你认为某个子表会由于外键未加索引而被锁住，你想避免这种情况，可以发出以下命令：

ALTER TABLE <child table name> DISABLE TABLE LOCK;

现在，对父表的可能导致表锁的任何UPDATE或DELETE都会接收到以下错误：

ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for <child table name>

4为什么没有使用我的索引？

1. 情况1

我们在使用一个B*树索引，而且谓词中没有使用索引的最前列。如果是这种情况，可以假设有一个表T，在T(X,Y)上有一个索引。我们要做以下查询：SELECT * FROM T WHERE Y = 5。此时，优化器就不打算使用T(x,y)上的索引，因为谓词中不涉及X列。在这种情况下，倘若使用索引，可能就必须查看每一个索引条目，而优化器通常更倾向于T对做一个全表扫描。但这并不完全排除使用索引。如果查询是SELECT X, Y FROM T WHERE Y = 5，优化器就会注意到，它不必全面扫描表来得到X或Y（X和Y都在索引中），对索引本身做一个快速的全面扫描会更合适，因为这个索引一般比底层表小得多。还要注意，仅CBO能使用这个访问路径。

另一种情况下CBO也会使用T(x,y)上的索引，这就是索引跳跃式扫描。当且仅当索引的最前列（在上一个例子中，最前列就是X）只有很少的几个不同值，而且优化器了解这一点，跳跃式扫描（skip scan）就能很好地发挥作用。例如，考虑（GENDER, EMPNO）上的一个索引，其中GENDER可取值有M和F，而且EMPNO是惟一的。对于以下查询：

select * from t where empno = 5;

可以考虑使用T上的那个索引采用跳跃式扫描方法来满足这个查询，这说明从概念上讲这个查询会如下处理：

select * from t where GENDER='M' and empno = 5
UNION ALL
select * from t where GENDER='F' and empno = 5;

它会跳跃式地扫描索引，以为这是两个索引：一个对于值M，另一个对应值F。在查询计划中可以很容易地看出这一点。我们将建立一个表，其中有一个二值的列，并在这个列上建立索引：

scott@ORCL>create table t
  2  as
  3  select decode(mod(rownum,2), 0, 'M', 'F' ) gender, all_objects.*
  4  from all_objects
  5  /

表已创建。

scott@ORCL>create index t_idx on t(gender,object_id)
  2  /

索引已创建。

scott@ORCL>begin
  2  dbms_stats.gather_table_stats
  3  ( user, 'T', cascade=>true );
  4  end;
  5  /

PL/SQL 过程已成功完成。

做以下查询时，可以看到结果如下：

scott@ORCL>set autotrace traceonly explain
scott@ORCL>select * from t t1 where object_id = 42;

执行计划
----------------------------------------------------------
Plan hash value: 2053318169

--------------------------------------------------------------------------------
-----
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time
    |
--------------------------------------------------------------------------------
-----
|   0 | SELECT STATEMENT            |       |     1 |    99 |     4   (0)| 00:00
:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    99 |     4   (0)| 00:00
:01 |
|*  2 |   INDEX SKIP SCAN           | T_IDX |     1 |       |     3   (0)| 00:00
:01 |
--------------------------------------------------------------------------------
-----

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=42)
       filter("OBJECT_ID"=42)

INDEX SKIP SCAN步骤告诉Oracle要跳跃式扫描这个索引，查找GENDER值有改变的地方，并从那里开始向下读树，然后在所考虑的各个虚拟索引中找到OBJECT_ID = 42。

2. 情况2

我们在使用一个SELECT COUNT(*) FROM T查询（或类似的查询），而且在表T上有一个B*树索引。不过，优化器并不是统计索引条目，而是在全面扫描这个表（尽管索引比表要小）。在这种情况下，索引可能建立在一些允许有null值的列上。由于对于索引键完全为null的行不会建立相应的索引条目，所以索引中的行数可能并不是表中的行数。

3. 情况3

对于一个有索引的列，做以下查询：

select * from t where f(indexed_column) = value

却发现没有使用INDEX_COLUMN上的索引。原因是这个列上使用了函数。我们是对INDEX_COLUMN的值建立了索引，而不是对F(INDEXED_COLUMN)的值建索引。在此不能使用这个索引。如果愿意，可以另外对函数建立索引。

4. 情况4

我们已经对一个字符创建了索引。这个列只包含数值数据。如果所用以下语句来查询:

select * from t where indexed_column = 5

注意查询中的数字5是常数5（而不是一个字符串），此时就没有使用INDEX_COLUMN上的索引。这是因为，前面的查询等价于一些查询：

select * from t where to_number(indexed_column) = 5

我们对这个列隐式地应用了一个函数，如情况3所述，这就会禁止使用这个索引。通过一个小例子能很容易地看出这一点。在这个例子，我们将使用内置包DBMS_XPLAN。

scott@ORCL>create table t ( x char(1) constraint t_pk primary key,y date );

表已创建。

scott@ORCL>insert into t values ( '5', sysdate );

已创建 1 行。

scott@ORCL>delete from plan_table;

已删除0行。

scott@ORCL>explain plan for select * from t where x = 5;

已解释。

scott@ORCL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    12 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    12 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("X")=5)

Note
-----
   - dynamic sampling used for this statement (level=2)

已选择17行。

可以看到，它会全面扫描表；另外即使我们对查询给出了以下提示：

scott@ORCL>explain plan for select /*+ INDEX(t t_pk) */ * from t where x = 5;

已解释。

scott@ORCL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
Plan hash value: 1399892806

--------------------------------------------------------------------------------
----
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time
   |
--------------------------------------------------------------------------------
----
|   0 | SELECT STATEMENT            |      |     1 |    12 |    34   (0)| 00:00:
01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |    12 |    34   (0)| 00:00:
01 |
|*  2 |   INDEX FULL SCAN           | T_PK |     1 |       |    26   (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER("X")=5)

Note
-----
   - dynamic sampling used for this statement (level=2)

已选择18行。

在此使用了索引，但是并不是惟一扫描（UNIQUE SCAN），而是完成了全面扫描（FULL SCAN）。原因从最后一行输出可以看出：filter(TO_NUMBER(“X”)=5)。这里对这个数据库列应用了一个隐式函数。X中存储的字符串必须转换为一个数字，之后才能与值5进行比较。在此无法把5转换为一个串，因为我们的NLS（国家语言支持）设置会控制5转换成串时的具体形式（而这是不确定的，不同的NLS设置会有不同的控制），所以应当把串转换为数字。而这样一来（由于应用了函数），就无法使用索引来快速地查找这一行了。如果只是执行串与串的比较：

scott@ORCL>delete from plan_table;

已删除5行。

scott@ORCL>explain plan for select * from t where x = '5';

已解释。

scott@ORCL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
Plan hash value: 1303508680

--------------------------------------------------------------------------------
----
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time
   |
--------------------------------------------------------------------------------
----
|   0 | SELECT STATEMENT            |      |     1 |    12 |     1   (0)| 00:00:
01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |    12 |     1   (0)| 00:00:
01 |
|*  2 |   INDEX UNIQUE SCAN         | T_PK |     1 |       |     1   (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"='5')

已选择14行。

这会得到我们期望的INDEX UNIQUE SCAN，而且可以看到这里没有应用函数。一定要尽可能地避免隐式转换。

这里还经常出现一个关于日期的问题。如果做以下查询：

-- find all records for today
select * from t where trunc(date_col) = trunc(sysdate);

而且发现这个查询没有使用DATE_COL上的索引。为了解决这个问题。可以对TRUNC(DATE_COL)建立索引，或者使用区间比较运算符来查询。下面来看对日期使用大于和小于运算符的一个例子。可以认识到以下条件：

TRUNC(DATE_COL) = TRUNC(SYSDATE)

与下面的条件是一样的：

select *
from t
where date_col >= trunc(sysdate)
and date_col < trunc(sysdate+1)

这就把所有函数都移动等式的右边，这样我们就能使用DATE_COL上的索引了（而且与WHERE TRUNC(DATE_COL)=TRUNC(SYSDATE)的效果完全一样）。

如果可能的话，倘若谓词中有函数，尽量不要对数据库列应用这些函数。这样做不仅可以使用更多的索引，还能减少处理数据库所需的工作。在上一种情况中，使用以上条件时：

where date_col >= trunc(sysdate)
and date_col < trunc(sysdate+1)

查询只会计算一次TRUNC值，然后就能使用索引来查找满足条件的值。使用TRUNC(DATE_COL) = TRUNC(SYSDATE)时，TRUNC(DATE_COL)则必须对整个表（而不是索引）中的每一行计算一次。

5. 情况5

此时如果用了索引，实际上反而会更慢。建立一个小表，再执行分析，却发现优化器并没有使用索引。在这种情况下，优化器的做法绝对是英明的。Oracle（对CBO而言）只会在合理地时候才使用索引。考虑下面的例子：

scott@ORCL>create table t
  2  ( x, y , primary key (x) )
  3  as
  4  select rownum x, object_name
  5  from all_objects
  6  /

表已创建。

scott@ORCL>begin
  2  dbms_stats.gather_table_stats
  3  ( user, 'T', cascade=>true );
  4  end;
  5  /

PL/SQL 过程已成功完成。

如果运行一个查询，它只需要表中相对较少的数据，如下：

scott@ORCL>set autotrace on explain
scott@ORCL>select count(y) from t where x < 50;

  COUNT(Y)
----------
        49


执行计划
----------------------------------------------------------
Plan hash value: 3759155194

--------------------------------------------------------------------------------
--
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time
 |
--------------------------------------------------------------------------------
--
|   0 | SELECT STATEMENT  |              |     1 |     5 |     2   (0)| 00:00:01
 |
|   1 |  SORT AGGREGATE   |              |     1 |     5 |            |
 |
|*  2 |   INDEX RANGE SCAN| SYS_C0018998 |    49 |   245 |     2   (0)| 00:00:01
 |
--------------------------------------------------------------------------------
--

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"<50)

此时，优化器会很乐意地使用索引；不过，如果估计通过索引获取的行数超过了一个阀值（取决于不同的优化器设计、物理统计等，这个阀值可能有所变化），就会观察到优化器将开始一个全部扫描：

scott@ORCL>select count(y) from t where x < 60000;

  COUNT(Y)
----------
     59999


执行计划
----------------------------------------------------------
Plan hash value: 4188769234

--------------------------------------------------------------------------------
------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time
     |
--------------------------------------------------------------------------------
------
|   0 | SELECT STATEMENT      |              |     1 |     5 |    43   (3)| 00:0
0:01 |
|   1 |  SORT AGGREGATE       |              |     1 |     5 |            |
     |
|*  2 |   INDEX FAST FULL SCAN| SYS_C0018998 | 60000 |   292K|    43   (3)| 00:0
0:01 |
--------------------------------------------------------------------------------
------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"<60000)

这个例子显示出优化器不一定会使用索引，而且实际上，它会做出正确的选择：采用跳跃式索引。对查询调优时，如果发现你认为本该使用的某个索引实际上并没有用到，就不要冒然强制使用这个索引，而应该先做个测试，并证明使用这个索引后确实会加快速度（通过耗用的时间和I/O次数来判断），然后再考虑让CBO“就范”（强制它使用这个索引）。

6. 情况6

有一段时间没有分析表了。这些表起先很小，但等到查看时，它们已经增长得非常大。现在索引就还有意义（尽管原先并非如此）。如果此时分析这个表，就会使用索引。

5神话：索引中从不重用空间

在索引中确实会重用空间

scott@ORCL>create table t ( x int, constraint t_pk primary key(x) );

表已创建。

scott@ORCL>insert into t values (1);

已创建 1 行。

scott@ORCL>insert into t values (2);

已创建 1 行。

scott@ORCL>insert into t values (9999999999);

已创建 1 行。

scott@ORCL>analyze index t_pk validate structure;

索引已分析

scott@ORCL>select lf_blks, br_blks, btree_space
  2  from index_stats;

   LF_BLKS    BR_BLKS BTREE_SPACE
---------- ---------- -----------
         1          0        8000

因此，根据这个神话所述，如果我从T中删除了X=2的行，这个空间就不会得到重用，除非我再次插入数字2。当前，这个索引使用了一个叶子块空间。如果索引键条目删除后绝对不会重用，只要我不断地插入和删除，而且从不重用任何值，那么这个索引就应该疯狂地增长。我们来看看实际是怎样的：

scott@ORCL>begin
  2     for i in 2 .. 999999
  3     loop
  4             delete from t where x = i;
  5             commit;
  6             insert into t values (i+1);
  7             commit;
  8     end loop;
  9  end;
 10  /

PL/SQL 过程已成功完成。

scott@ORCL>analyze index t_pk validate structure;

索引已分析

scott@ORCL>select lf_blks, br_blks, btree_space
  2  from index_stats;

   LF_BLKS    BR_BLKS BTREE_SPACE
---------- ---------- -----------
         1          0        8000

由此可以看出，索引中的空间确实得到了重用。初始数字2（介于1～9.999.999.999之间）所用的空间会永远保留在这个索引块上。索引不会自行“合并“。这说明，如果我用值1～500,000加载一个表，然后隔行删除表记录（删除所有偶数行），那么这个索引中那一列上就会有250,000个”洞“。只有当我重新插入数据，而且这个数据能在有洞的块中放下时，这些空间才会得到重用。Oracle并不打算“收缩”或压缩索引，不过这可以通过ALTER INDEX REBUILD或COALESCE命令强制完成。另一方面，如果我用值1～500,000加载一个表，然后从表中删除值小于或等于250,000的每一行，就会发现从索引中清除的块将放回到索引的freelist中，这个空间完全可以重用。

如果你还记得，第二个神话：索引空间从不“回收”。据这个神话称：一旦使用了一个索引块，它就会一直呆在索引结构的那个位置上，而且只有当你插入数据，并放回到原来那个位置上时，这个块才会被重用。同样可以证明这是错误的。首先，需要建立一个表，其中大约有500,000行。为此，我们将使用big_table脚本。有了这个表，而且有了相应的主键索引后，我们将测量索引中有多少个叶子块，另外索引的freelist上有多少个块。要记住，对于一个索引，只有当块完全为空时才会放在freelist上，这一点与表不同。所以我们在freelist上看到的块都完全为空，可以重用。

scott@ORCL>select count(*) from big_table;

  COUNT(*)
----------
    100000

sys@ORCL>declare
  2     l_freelist_blocks number;
  3     begin
  4     dbms_space.free_blocks
  5             ( segment_owner => user,
  6             segment_name => 'BIG_TABLE_PK',
  7             segment_type => 'INDEX',
  8             freelist_group_id => 0,
  9
 10             free_blks => l_freelist_blocks );
 11     dbms_output.put_line( 'blocks on freelist = ' || l_freelist_blocks );
 12     end;
 13  /
blocks on freelist = 3

PL/SQL 过程已成功完成。

sys@ORCL>select leaf_blocks from user_indexes
  2  where index_name = 'BIG_TABLE_PK';

LEAF_BLOCKS
-----------
       1875

执行这个批量删除之前，freelist上 3个块，而在索引的“叶子”层上有 1875 块，这些叶子块中包含着数据。下面，我们将执行删除，并再次测量空间的利用情况：

sys@ORCL>delete from big_table where id <= 250000;

已删除250000行。

sys@ORCL>commit;

提交完成。

sys@ORCL>declare
  2     l_freelist_blocks number;
  3     begin
  4             dbms_space.free_blocks
  5             ( segment_owner => user,
  6                     segment_name => 'BIG_TABLE_PK',
  7                     segment_type => 'INDEX',
  8                     freelist_group_id => 0,
  9                     free_blks => l_freelist_blocks );
 10             dbms_output.put_line( 'blocks on freelist = ' || l_freelist_bloc
ks );
 11             dbms_stats.gather_index_stats
 12                     ( user, 'BIG_TABLE_PK' );
 13     end;
 14  /
blocks on freelist = 470

PL/SQL 过程已成功完成。

sys@ORCL>select leaf_blocks from user_indexes
  2  where index_name = 'BIG_TABLE_PK';

LEAF_BLOCKS
-----------
       1408

可以看到，现在，索引中一小半的块都在freelist上（470个块），而且现在只有1408个叶子块。如果将470和1408相加，又得到了原来的1878。这说明freelist上的这些块完全为空的，而且可以重用（索引freelist上的块必须为空，这与堆组织表的freelist上的块不同）。
以上例子强调了两点：
一旦插入了可以重用空间的行，索引块上的空间就会立即重用。
索引块为空时，会从索引结构中取出它，并在以后重用。与表不同，在索引结构中，不能清楚地看出一个块有没有“空闲空间”。在表中，可以看到freelis上的块，即使其中包含有数据。而在索引中，只能在freelist上看到完全为空的块；至少有一个索引条目（但其余都是空闲空间）的块就无法清楚地看到。

6神话：最有差别的元素应该在最前面

对于一个有100,000行的表，如果要在C1和C2列上创建一个索引，你发现C1有100,000个不同的值，而C2有25,000个不同的值，在比较数据向量时（假设C1和C2是向量），把哪一个放在前面都关系不大。考虑以下例子。我们将基于ALL_OBJECTS创建一个表，并基于OWNER、OBJECT_TYPE和OBJECT_NAME列创建一个索引（这些列按从最没有差别到最有差别的顺序排列，即OWNER列差别最小，OBJECT_TYPE次之，OBJECT_NAME列差别最大），另外还在OBJECT_NAME、OBJECT_TYPE和OWNER上创建了另一个索引：

scott@ORCL>create table t
  2  as
  3  select * from all_objects;

表已创建。

scott@ORCL>create index t_idx_1 on t(owner,object_type,object_name);

索引已创建。

scott@ORCL>create index t_idx_2 on t(object_name,object_type,owner);

索引已创建。

scott@ORCL>select count(distinct owner), count(distinct object_type),
  2  count(distinct object_name ), count(*)
  3  from t;

COUNT(DISTINCTOWNER) COUNT(DISTINCTOBJECT_TYPE) COUNT(DISTINCTOBJECT_NAME)   COUNT(*)
-------------------- -------------------------- -------------------------- ----------
                  33                         39                      43526      72081

现在，为了显示这二者在高效使用空间方面难分伯仲，下面测量它们的空间利用情况：

scott@ORCL>analyze index t_idx_1 validate structure;

索引已分析

scott@ORCL>select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave
  2  from index_stats;

BTREE_SPACE   PCT_USED OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------- ---------- -------------- ----------------
    4056128         90              2               28

scott@ORCL>analyze index t_idx_2 validate structure;

索引已分析

scott@ORCL>select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave
  2  from index_stats;

BTREE_SPACE   PCT_USED OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------- ---------- -------------- ----------------
    4048096         90              1               12

它们使用的空间大小完全一样，细到字节级都一样，二者没有什么区别。不过，如果使用索引键压缩，第一个索引更可压缩，这一点由OPT_CMP_PCTSAVE值可知。

下面来看这两个索引的表现，从而确定是否有哪个索引更“优秀”，总比另一个索引更高效。要测试这一点，我们将使用一个PL/SQL代码块（其中包括有提示的查询，指示要使用某个索引或者另一个索引）：

SELECT /*+ INDEX( t t_idx_1 ) */ COUNT(*) FROM T WHERE OBJECT_NAME = :B3 AND OBJECT_TYPE = :B2 AND OWNER = :B1
    CPU                  CPU per             Elapsd                     Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)    Buffer Gets  Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
      1.64       72,081       0.00   10.8       2.01         216,820 2285226462

  Elapsed                Elap per            CPU                        Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)  Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
      2.01       72,081       0.00   11.8       1.64             491 2285226462

  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
        216,820       72,081            3.0   48.5     1.64      2.01 2285226462
        
  Physical Rds   Executions  Rds per Exec   %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
            491       72,081            0.0   11.8     1.64      2.01 2285226462
            
 Executions   Rows Processed   Rows per Exec    Exec (s)   Exec (s)  Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
      72,081          72,081              1.0       0.00        0.00  2285226462


SELECT /*+ INDEX( t t_idx_2 ) */ COUNT(*) FROM T WHERE OBJECT_NAME = :B3 AND OBJECT_TYPE = :B2 AND OWNER = :B1          
    CPU                  CPU per             Elapsd                     Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)    Buffer Gets  Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
      1.36       72,081       0.00    8.9       1.68         216,793 2770212628

  Elapsed                Elap per            CPU                        Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)  Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
      1.68       72,081       0.00    9.9       1.36             185 2770212628

  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
        216,793       72,081            3.0   48.5     1.36      1.68 2770212628

  Physical Rds   Executions  Rds per Exec   %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
            185       72,081            0.0    4.4     1.36      1.68 2770212628

 Executions   Rows Processed   Rows per Exec    Exec (s)   Exec (s)  Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
      72,081          72,081              1.0       0.00        0.00  2770212628

它们处理的行数完全相同，而且块数也非常类似（之所以存在微小的差别，这是因为表中的行序有些偶然性，而且Oracle相应地会做一些优化），它们使用了同样的CPU时间，而且在大约相同的耗用时间内运行（再运行这个测试，CPU和ELAPSED这两个数字会有一点差别，但是平均来讲它们是一样的）。按照各个列的差别大小来安排这些列在索引中的顺序并不会获得本质上的效率提升，另外如前所示，如果再考虑到索引键压缩，可能还更倾向于把最没有选择性的列放在最前面。如果对索引采用COMPRESS 2，再运行前面的例子，对于给定情况下的这个查询，第一个查询执行的I/O次数大约是后者的2/3。

对于是把C1列放在C2列之前，这必须根据如果使用索引来决定。如果有大量如下的查询：

select * from t where c1 = :x and c2 = :y;
select * from t where c2 = :y;

那么在T(C2,C1)上建立索引就更合理。以上这两个查询都可以使用这个索引。另外，通过使用索引键压缩，如果C2在前，就能建立一个更小的索引。

转载于:https://my.oschina.net/u/1862478/blog/1926330

weixin_34344403

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
关于索引的常见问题和神话

2019独角兽企业重金招聘Python工程师标准>>> ...
复制链接

扫一扫