oracle索引重建到底会提高多少性能？

最新推荐文章于 2025-06-17 19:57:55 发布

原创最新推荐文章于 2025-06-17 19:57:55 发布 · 4.6k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #index #rebuild #performace

Oracle Database 同时被 2 个专栏收录

50 篇文章

订阅专栏

ORACLE索引研究

9 篇文章

订阅专栏

本文探讨了索引重建在不同场景下对应用性能的影响，通过SQL示例展示了索引重建前后性能变化，并指出在查询中索引相关操作占据主导时，重建索引能显著提升性能。特别地，对于使用min/max函数的查询，重建索引能有效减少读操作，提高执行效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

工作中往往会观察到索引重建带来的空间释放和应用性能提升。空间释放比较容易理解，也非常容易度量，那么索引重建到底会对应用的性能有多少影响那？首先我们会问：索引重建为什么会带来性能的提升？毫无疑问，这是因为索引重建后，与索引有关的io操作得到了降低。那么，索引io的降低在多大程度上影响了应用语句的执行效率？这恐怕需要具体问题具体分析了。

首先，我们来看一下多数情况下，索引重建的效果如何

SQL> create table t1 as select rownum rn,dbms_random.string('u',20) name1,dbms_random.string('u',15) name2 from dual connect by level < 1000000;

表已创建。

SQL> create index i1 on t1(rn);

索引已创建。

SQL> analyze index i1 validate structure;

索引已分析

SQL> select height,lf_rows,del_lf_rows,lf_blks,del_lf_rows btree_space,used_space,pct_used from index_stats;

    HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS BTREE_SPACE USED_SPACE   PCT_USED
---------- ---------- ----------- ---------- ----------- ---------- ----------
	 3     999999		0	2226	       0   16006445	    90

SQL> delete from t1 where mod(rn,2) =1;

已删除500000行。

SQL> commit;

提交完成。

SQL> analyze index i1 validate structure;

索引已分析

SQL> select height,lf_rows,del_lf_rows,lf_blks,del_lf_rows btree_space,used_space,pct_used from index_stats;

    HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS BTREE_SPACE USED_SPACE   PCT_USED
---------- ---------- ----------- ---------- ----------- ---------- ----------
	 3     943027	   443028	2226	  443028   15094893	    85

SQL> set timing on
SQL> set autotrace on


SQL> select * from t1 where rn=1;

未选定行

已用时间:  00: 00: 00.00

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

------------------------------------------------------------------------------------
| Id  | Operation		    | Name | Rows  | Bytes | Cost (%CPU)| Time	   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	   |	 1 |  4017 |	 2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |	 1 |  4017 |	 2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	    | I1   |	 1 |	   |	 2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("RN"=1)

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


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

SQL> select * from t1 where rn=100;

	RN
----------
NAME1
----------------------------------------------------------------------------------------------------
NAME2
----------------------------------------------------------------------------------------------------
       100
IWKRROMDHLNJMXVQYRHE
VPTNTMMUJYJJQCM


已用时间:  00: 00: 00.00

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

------------------------------------------------------------------------------------
| Id  | Operation		    | Name | Rows  | Bytes | Cost (%CPU)| Time	   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	   |	 1 |  4017 |	 4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |	 1 |  4017 |	 4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	    | I1   |	 1 |	   |	 3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("RN"=100)

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


统计信息
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	-- 5  consistent gets
	--  1  physical reads
	  0  redo size
	696  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

SQL> select * from t1 where rn=1000;

	RN
----------
NAME1
----------------------------------------------------------------------------------------------------
NAME2
----------------------------------------------------------------------------------------------------
      1000
YTGFFEROGABGKFKQENMW
LBERYHDTRMAWGHV


已用时间:  00: 00: 00.01

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

------------------------------------------------------------------------------------
| Id  | Operation		    | Name | Rows  | Bytes | Cost (%CPU)| Time	   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	   |	 1 |  4017 |	 4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |	 1 |  4017 |	 4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	    | I1   |	 1 |	   |	 3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("RN"=1000)

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


统计信息
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	--  5  consistent gets
	--  4  physical reads
	  0  redo size
	696  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

SQL> alter index i1 rebuild online;

索引已更改。

已用时间:  00: 00: 05.41
SQL> analyze index i1 validate structure;

索引已分析

已用时间:  00: 00: 00.22
SQL> select height,lf_rows,del_lf_rows,lf_blks,del_lf_rows btree_space,used_space,pct_used from index_stats;

    HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS BTREE_SPACE USED_SPACE   PCT_USED
---------- ---------- ----------- ---------- ----------- ---------- ----------
	 3     499999		0	1113	       0    7998149	    90

已用时间:  00: 00: 00.03


SQL> select * from t1 where rn=1;

未选定行

已用时间:  00: 00: 00.00

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

------------------------------------------------------------------------------------
| Id  | Operation		    | Name | Rows  | Bytes | Cost (%CPU)| Time	   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	   |	 1 |  4017 |	 2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |	 1 |  4017 |	 2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	    | I1   |	 1 |	   |	 2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("RN"=1)

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


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

SQL> select * from t1 where rn=100;

	RN
----------
NAME1
----------------------------------------------------------------------------------------------------
NAME2
----------------------------------------------------------------------------------------------------
       100
IWKRROMDHLNJMXVQYRHE
VPTNTMMUJYJJQCM


已用时间:  00: 00: 00.00

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

------------------------------------------------------------------------------------
| Id  | Operation		    | Name | Rows  | Bytes | Cost (%CPU)| Time	   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	   |	 1 |  4017 |	 4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |	 1 |  4017 |	 4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	    | I1   |	 1 |	   |	 3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("RN"=100)

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


统计信息
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	--  5  consistent gets
	--  4  physical reads
	  0  redo size
	696  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

SQL> select count(name1) from t1 where rn<100;

COUNT(NAME1)
------------
	  49

已用时间:  00: 00: 00.00

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

-------------------------------------------------------------------------------------
| Id  | Operation		     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	    |	  1 |  2015 |	  4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE 	     |	    |	  1 |  2015 |		 |	    |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |	 49 | 98735 |	  4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN	     | I1   |	 49 |	    |	  3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   3 - access("RN"<100)

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


统计信息
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	--  4  consistent gets --
	--  4  physical reads --
	  0  redo size
	531  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

这个测试非常简单，但是也可以看出，在多数情况下，重建索引并没有给我们带来很好的性能提升，这一方面是由于索引操作在整个查询操作中所占的比例有限，另一方面是因为索引的物理结构决定的（索引不会因为数据量的增大而效率降低，这也是索引设计的目标）

但是，在某些情况下，重建索引确实给我们带来了好处。

1）使用min/max

SQL> delete from t1 where rn < 990000;

已删除494999行。

已用时间:  00: 01: 06.81
SQL> commit;

提交完成。

已用时间:  00: 00: 00.09
SQL> analyze index i1 validate structure;

索引已分析

已用时间:  00: 00: 00.21
SQL> select height,lf_rows,del_lf_rows,lf_blks,del_lf_rows btree_space,used_space,pct_used from index_stats;

    HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS BTREE_SPACE USED_SPACE   PCT_USED
---------- ---------- ----------- ---------- ----------- ---------- ----------
	 3     499999	   494999	1113	  494999    7998149	    90

已用时间:  00: 00: 00.03
SQL> set autotrace on
SQL> select min(rn) from t1;

   MIN(RN)
----------
    990000

已用时间:  00: 00: 00.03

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

-----------------------------------------------------------------------------------
| Id  | Operation		   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	   |	  |	1 |    13 |	3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE 	   |	  |	1 |    13 |	       |	  |
|   2 |   INDEX FULL SCAN (MIN/MAX)| I1   |	1 |    13 |	3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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


统计信息
----------------------------------------------------------
	  4  recursive calls
	  0  db block gets
      -- 1180  consistent gets
      -- 1350  physical reads
	  0  redo size
	526  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

SQL> set autotrace off
SQL> alter index i1 rebuild;

索引已更改。

已用时间:  00: 00: 00.15
SQL> analyze index i1 validate structure;

索引已分析

已用时间:  00: 00: 00.02
SQL> select height,lf_rows,del_lf_rows,lf_blks,del_lf_rows btree_space,used_space,pct_used from index_stats;

    HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS BTREE_SPACE USED_SPACE   PCT_USED
---------- ---------- ----------- ---------- ----------- ---------- ----------
	 2	 5000		0	  12	       0      80031	    77

已用时间:  00: 00: 00.02
SQL> set autotrace on
SQL> select min(rn) from t1;

   MIN(RN)
----------
    990000

已用时间:  00: 00: 00.00

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

-----------------------------------------------------------------------------------
| Id  | Operation		   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	   |	  |	1 |    13 |	2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE 	   |	  |	1 |    13 |	       |	  |
|   2 |   INDEX FULL SCAN (MIN/MAX)| I1   |	1 |    13 |	2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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


统计信息
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	 -- 2  consistent gets
	--  2  physical reads
	  0  redo size
	526  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

索引重建带来了较大的性能提升，这是因为使用min函数，首先会定位到索引的最左节点，然后依次访问右侧节点直到找到第一个row，如果索引中的左侧存在大量的空值，则需要进行大量的读操作，从而降低性能。

总结：如果在查询中，索引相关的操作占据了总操作的大部分（如COUNT MIN 等），则重建索引可以带来很好的性能。