深入了解重建索引(原創)

什麼時候需要重建索引

索引在普遍意義上能夠給資料庫帶來帶來提升，但索引的額外開銷也是不容小視的，而索引的重建也是維護索引的重要工作之一。 經過維護的索引可帶來以下好處：

1、CBO對于索引的使用可能會産生一個較小的成本值，進而在執行計劃中選擇使用索引。

2、使用索引掃描的查詢掃描的實體索引塊會減少，進而提高效率。

3、于需要緩存的索引塊減少了，進而讓出了記憶體以供其他元件使用。

重建索引的原因主要包括：

1、 删除的空間沒有重用，導緻 索引出現碎片

2、 删除大量的表資料後，空間沒有重用，導緻 索引"虛高"

3、索引的 clustering_facto 和表不一緻

也有人認為當索引樹高度超過4的時候需要進行重建，但是如果表數量級較大，自然就不會有較高的樹，而且重建不會改變索引樹高度，除非是由于大量引起的索引樹“虛高”，重建才會改善性能，當然這又回到了索引碎片的問題上了。

索引出現碎片

由于索引中隻有删除和插入操作，且索引中更新完全不同于表達更新。如果索引中的記錄關鍵字需要更新，就需要将舊記錄的位置标記為删除，并在相應的葉子節點插入新的索引紀錄。這種删除标記并非真正的删除索引塊中的記錄，索引塊中被标記為删除的記錄隻有在相同索引條目插入到相同塊的相同位置時才能重用。由于即使相同的索引記錄也不一定插入到被删除的空間中，故如果對索引頻繁進行update和delete操作很容易導緻索引出現碎片。較高的PCTFREE也容易出現索引碎片。索引的碎片也就導緻了，通路索引資料時需要通路更多的索引塊

索引虛高

上面的說的是頻繁update和delete導緻索引塊中有碎片，那如果進行大量的delete操作把整個索引塊的資料都删了呢？索引中的索引條目仍然隻被标記為删除而沒有被真正清空。設想下，如果這時候的索引關鍵字是一個不斷增大的id，那麼被标記為删除的索引條目就永遠不會被重用，那樹就不會不斷增長，也就出現了，表的資料空間減少了，而索引的資料空間卻在不斷增大的情況。由于索引的高度不斷增加，通路索引資料時需要通路更多的索引塊。

clustering_factor對 B樹索引 的影響

對于clustering_factor來說，它是用來比較索引的順序程度與表的雜亂排序程度的一個度量。Oracle在計算某個clustering_factor時，會對每個索引鍵值查找對應到表的資料，在查找的過程中，會跟蹤從一個表的資料塊跳轉到另外一個資料塊的次數（當然，它不可能真的這麼做，源代碼裡隻是簡單的掃描索引，進而獲得ROWID，然後從這些ROWID獲得表的資料塊的位址）。每一次跳轉時，有個計數器就會增加，最終該計數器的值就是clustering_factor。下圖描述了這個原理。

在上圖中，我們有一個表，該表有4個資料塊，以及20條記錄。在列N1上有一個索引，上圖中的每個小黑點就表示一個索引條目。列N1的值如圖所示。而N1的索引的葉子節點包含的值為：A、B、C、D、E、F。如果oracle開始掃描索引的底部，葉子節點包含的第一個N1值為A，那麼根據該值可以知道對應的ROWID位于第一個資料塊的第三行裡，是以我們的計數器增加1。同時，A值還對應第二個資料塊的第四行，由于跳轉到了不同的資料塊上，是以計數器再加1。同樣的，在處理B時，可以知道對應第一個資料塊的第二行，由于我們從第二個資料塊跳轉到了第一個資料塊，是以計數器再加1。同時，B值還對應了第一個資料塊的第五行，由于我們這裡沒有發生跳轉，是以計數器不用加1。

在上面的圖裡，在表的每一行的下面都放了一個數字，它用來顯示計數器跳轉到該行時對應的值。當我們處理完索引的最後一個值時，我們在資料塊上一共跳轉了十次，是以該索引的clustering_factor為10。

注意第二個資料塊，clustering_factor為8出現了4次。因為在索引裡N1為E所對應的4個索引條目都指向了同一個資料塊。進而使得clustering_factor不再增長。同樣的現象出現在第三個資料塊中，它包含三條記錄，它們的值都是C，對應的clustering_factor都是6。

從clustering_factor的計算方法上可以看出，我們可以知道它的最小值就等于表所含有的資料塊的數量；而最大值就是表所含有的記錄的總行數。很明顯，clustering_factor越小越好，越小說明通過索引查找表裡的資料行時需要通路的表的資料塊越少。

是以我們可以得出結論，如果僅僅是為了降低索引的clustering_factor而重建索引沒有任何意義。降低clustering_factor的關鍵在于重建表裡的資料。事實上，生産環境下，我們甚至沒有必要考慮 clustering_factor對索引通路的影響，這個是表資料分布決定的，如果想考慮，就得先建立索引，然後分析 clustering_factor，最後對表進行排序，再重新建立索引，可行性非常低。是以，這裡隻是作為研究讨論，實際環境下還是要結合具體情況進行分析。針對索引碎片和索引的"虛高"，如果查詢範圍主要是通過unique index通路資料，可以不用理會 索引碎片和索引的"虛高"，如果資料範圍，主要是通過range scan的方式則需要重建索引，至于原理，相信讀了筆者下面的文章後肯定會明白

​​​ http://czmmiao.iteye.com/blog/1481227​​​ 。關于索引是否需要重建，Oracle有這麼一句話

Generally speaking, the need to rebuild b-tree indexes is very rare, basically because a b-tree index is largely self-managed or self-balanced.

如何查找出需要重建的索引 我們通過下面實驗來具體看下如何查找需要重建的索引

準備實驗環境如下

SQL> create table ind (id int,name varchar2(100));

Table created.

SQL> begin

  2  for i in 1..10000 loop

  3  insert into ind values(i,to_char(i)||'aaa');

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> create index ind_id_idx on ind(id);

Index created.

SQL> analyze index ind_id_idx validate structure;

Index analyzed.

注意:index_stats隻能在同一個session裡先執行完analyze index indexname validate structure後才能查到資料，在其他的session裡查index_stats是查不到資料的，即使那個初始的session已經執行過analyze index indexname validate structure。順帶提一句， analyze index indexname validate structure會對整張表加排他鎖，阻止表上的所有DML語句。 我們也可以使用online關鍵字，analyze index indexname validate structure online，這樣就可以不對表加鎖，但不會填充index_stats視圖。

index_stats的主要相關字段如下

--LF_ROWS Number of values currently in the index

--LF_ROWS_LEN Sum in bytes of the length of all values

--DEL_LF_ROWS Number of values deleted from the index

--DEL_LF_ROWS_LEN Length of all deleted values

col name         heading 'Index Name'          format a30

col del_lf_rows  heading 'Deleted|Leaf Rows'   format 99999999

col lf_rows_used heading 'Used|Leaf Rows'      format 99999999

col ibadness     heading '% Deleted|Leaf Rows' format 999.99999

SQL> SELECT name,

2       del_lf_rows,

3      lf_rows - del_lf_rows lf_rows_used,

4      to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness

5    FROM index_stats

6       where name = upper('&&index_name');

                                 Deleted      Used % Deleted

Index Name                     Leaf Rows Leaf Rows Leaf Rows

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

IND_ID_IDX                             0     10000     .00000

可以看到沒有删除的索引

更新1000條記錄

SQL> update ind set id=id+1 where id> 9000;

1000 rows updated.

SQL> commit;

Commit complete.

SQL> analyze index ind_id_idx validate structure;

Index analyzed.

SQL> col name         heading 'Index Name'          format a30

    col del_lf_rows  heading 'Deleted|Leaf Rows'   format 99999999

    col lf_rows_used heading 'Used|Leaf Rows'      format 99999999

    col ibadness     heading '% Deleted|Leaf Rows' format 999.99999

    SELECT name,

       del_lf_rows,

       lf_rows - del_lf_rows lf_rows_used,

       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness

    FROM index_stats

       where name = upper('&&index_name');SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6 

old   6:        where name = upper('&&index_name')

new   6:        where name = upper('ind_id_idx')

                                 Deleted      Used % Deleted

Index Name                     Leaf Rows Leaf Rows Leaf Rows

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

IND_ID_IDX                          1000     10000    9.09091

删除的索引占了9.09%，如果删除的索引條目占了10~15%，則可以考慮重建索引或者coalesce

如何重建索引 重建索引有3種方法，具體如下:

1、删除重建立索引可以采用 PARALLEL， NOLOGGING和 COMPUTE STATISTICS 進行處理，該方法是最慢的，最耗時的。一般不建議。

2、實驗alter index .........rebuild指令重建

它使用原索引的葉子節點作為新索引的資料來源。我們知道，原索引的葉子節點的資料塊通常都要比表裡的資料塊要少很多，是以進行的I/O就會減少；同時，由于原索引的葉子節點裡的索引條目已經排序了，是以在重建索引的過程中，所做的排序工作也要少的多。從oracle 8.1.6以後，ALTER INDEX … REBUILD指令可以添加ONLINE關鍵字。這使得在重建索引的過程中，使用者可以繼續對原來的索引進行修改，也就是說可以繼續對表進行DML操作和删除，但在11g之前，在開始和結束建立索引的時刻仍然會鎖表。 由于新舊索引在建立時同時存在，是以，使用這種技巧則需要有額外的磁盤空間可臨時使用，當索引建完後把老索引删除，如果沒有成功，也不會影響原來的索引。利用這種辦法，指定了tablespace關鍵字後 Alter index indexname rebuild  tablespace tablespacename 還可以用來将一個索引以到新的表空間。和 重建索引一樣， alter index indexname rebuild 也可以采用 PARALLEL， NOLOGGING和 COMPUTE STATISTICS 進行處理 ，使用 COMPUTE STATISTICS處理的好處在于 可以在重建索引的過程中，就生成CBO所需要的統計資訊，這樣就避免了索引建立完畢以後再次運作analyze或dbms_stats來收集統計資訊。

這個指令的執行步驟如下：

首先，逐一讀取現有索引，以擷取索引的關鍵字。

其次，按新的結構填寫臨時資料段。

最後，一旦操作成功，删除原有索引樹，降臨時資料段重命名為新的索引。

需要注意的是alter index indexname rebuild 指令中必須使用tablespace字句，以保證重建工作是在現有索引相同的表空間進行。

3、使用alter index indexname coalesce指令或alter index indexname shrik space指令重建索引。該指令主要是 用來合并相鄰的碎片，相比于rebuild， 有如下優點 ：

1、always online，不需要鎖索引

2、不需要消耗接近兩倍的臨時空間

3、當碎片比率小于25%時比rebuild産生更少的redo日志，當然我們可以在重建索引時将日志關閉。

4、 并不重建索引，隻對葉子節點進行整合，不改變索引高度和分支節點數量。當我們重建索引後，索引之間是緊密連接配接的，高度和分支數量都會改變。如果該索引列上繼續進行DML操作，很可能導緻樹的重新增長、分裂，這是非常消耗資源的操作，同時由于DML操作的繼續，達到一定程度後，該索引很可能重新被認為需要rebuild，如此惡性循環。是以往往我們不建議重建索引，不希望重建索引而引起索引高度和分支節點的重構，除非在需要遷移索引到另一個表空間時才選擇rebuild。

Insert/update/delete causes the index to evolve over time as the index splits and grows. When the index is rebuild it will become more tightly packed; however as DML operations continue on the table the index splits have to be redone again until the index reaches it's equilibrium. As a result, the redo activity increases and the index splits are now more likely to impact performance directly as we consume more I/O, CPU, etc to serve the index restructuring. After a certain period of time the index may again experience 'issues' and may be re-flagged for a rebuild, causing the vicious cycle to continue. Therefore it is often better to leave the index in its natural equilibrium and/or at least prevent indexes from being rebuilt on a regular basis.

5、10g以後引入了 alter index indexname shrik space指令，功能上 和alter index indexname coalesce一樣，但經測試産生更多的redo日志(以實際測試環境為準)。

這邊給出如下結論，幫助大家整理下 COALESCE、SHRINK和rebuild的差別 ：

1、當索引中碎片率<=25%，COALESCE與SHRINK比rebuild的産生的redo日志少，消耗資源更少。兩者相比之下SHRINK的成本會更高。

2、當 索引中碎片率> 25%的時，REBUILD的成本更小，産生的redo更少

對測試過程感興趣的朋友可以參見連結

​​​ http://www.shujukuai.com/?p=102​​ http://www.oracledatabase12g.com/archives/alter-index-coalesce-vs-shrink-space.html