當索引出現問題時，會導緻嚴重的性能問題，索引問題包括索引不可用、索引碎片導緻性能下降，我們需要一些手段在檢測索引的問題，并解決這些問題。這一篇将為你講述怎麼定位索引問題，并提供了解決的辦法。
索引不可用
索引不可用的原因有很多，包括：
1）索引空間耗盡，導緻SQL*Loader更新索引失敗；
2）建立索引的過程中執行個體失敗；
3）唯一鍵有重複值；
4）某個索引的順序與sorted indexes子句中指定的順序不同；
5）移動表或表分區（alter table move和alter table move partition）；
6）對表執行線上重定義；
7）截斷表分區（alter table truncate partition）；
8）導入分區；
9）删除表分區；
10）拆分表的分區或子分區（alter table split partition）；
11）分區索引的維護操作（alter index split partition）。
除了上述的這些原因之外，你還可以手動的将索引标注為不可用，這樣可以使批量加載速度更快，下面是把索引的狀态改變為不可用的方法：
alter index IDX_HISTORYALARM_HOUR$01 unusable
如果你的索引為分區索引，這個操作将導緻所有分區的索引都不可用，你也可以指定某個分區的索引不可用：
alter index IDX_HISTORYALARM$02 modify partition HISTORYALARM20140731 unusable
通過下面的方法可以檢視索引的狀态：
select ind.INDEX_NAME,ind.status,ind.PARTITIONED from user_indexes ind where index_name like '%HISTORYALARM%'
INDEX_NAME STATUS PARTITIONED
---------------------------------------------------------------------
IDX_HISTORYALARM_HOUR$01 UNUSABLE NO
IDX_HISTORYALARM$02 N/A YES
可以看到，全局索引的狀态已經變為UNUSABLE，但本地索引的狀态辨別為N/A，通過下面的方法可以檢視本地索引在每一個分區中的索引狀态：
INDEX_NAME PARTITION_NAME STATUS
------------------------------------------------------------------------------------------
IDX_HISTORYALARM$02 HISTORYALARM20140731 UNUSABLE
IDX_HISTORYALARM$02 HISTORYALARM20140801 USABLE
IDX_HISTORYALARM$02 HISTORYALARM20140802 USABLE
IDX_HISTORYALARM$02 HISTORYALARM20140803 USABLE
IDX_HISTORYALARM$02 HISTORYALARM20140804 USABLE
IDX_HISTORYALARM$02 HISTORYALARM20140805 USABLE
......
可以看到分區HISTORYALARM20140731的索引已經标注為UNUSABLE。
當索引被标注為不可用後，優化器就會忽略這些索引，Oracle在DML更改表時也不再維護這些索引，如果希望優化器再次使用它，就必須先重建（rebuild）索引。
索引碎片
随着時間的推移，由于大量的删除操作，索引可能會産生碎片。Oracle文檔（Performance Tuning Manual for Oracle DataBase 11.2）建議運作"analyze...validate"語句來識别需要重建的索引，這個操作會将索引的統計資料放到INDEX_STATS視圖中，下面是該視圖中的關鍵列：
1）高度（HEIGHT）：索引的高度，從1開始，1代表隻有根的索引；
2）塊數（BLOCKS）：配置設定給索引的塊數；
3）葉行數（LF_ROWS）：葉行數（包括已删除的行）；
4）已删除的葉行數（DEL_LF_ROWS）：已删除尚未清理的葉行條目數；
5）已用空間（USED_SPACE）：索引内使用的總空間（包括已删除的條目）；
6）已用百分比（PCT_USED）：索引内使用空間的百分比（包括已删除的條目）。。計算公式：(USED_SPACE / BTREE_SPACE) * 100;
7）B樹空間（BTREE_SPACE）：索引的總大小（包括已删除的條目）。
下面通過一個例子來學習該視圖的使用。
先建立一個測試表格，并在上面建立索引：
create table test as select rownum id,'Test' text from dual connect by level <= 100000;
create index idx_test on test(id);
然後執行索引分析語句：
analyze index idx_test validate structure;
注意在執行分析語句之前INDEX_STATS視圖是空的，現在查詢該視圖來檢查被删除的葉行數：
select lf_rows,lf_blks,del_lf_rows from index_stats;
LF_ROWS LF_BLKS DEL_LF_ROWS
--------------------------------------------------------------------------------
100000 222 0
這裡可以看出删除的葉行數為0，接下來我們删除表中大量的行，再次運作分析語句：
delete test where id <= 99999;
commit;
analyze index idx_test validate structure;
然後查詢被删除的葉行數：
select lf_rows,lf_blks,del_lf_rows from index_stats;
LF_ROWS LF_BLKS DEL_LF_ROWS
-----------------------------------------------------------
100000 222 99999
為了讓Oracle能夠得到正确的執行計劃，我們先收集表和索引的統計資訊：
begin
dbms_stats.gather_table_stats(ownname => user,
tabname => 'TEST',
cascade => TRUE);
end;
然後執行一個索引範圍掃描的查詢：
select * from test where id > 10;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.27 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 1.53 56 224 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 1.81 56 224 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TEST (cr=224 pr=56 pw=0 time=1538212 us)
1 INDEX RANGE SCAN IDX_TEST (cr=223 pr=56 pw=0 time=1527442 us)(object id 58594)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.79 0.83
db file sequential read 56 0.03 0.41
********************************************************************************
查詢使用了索引範圍掃描，雖然整個表隻有一條資料，但由于索引中的索引碎片，導緻索引掃描任然讀取了56個資料塊。下面重建該索引：
alter index IDX_TEST rebuild;
然後按上面的方法生成并檢視索引資訊：
LF_ROWS LF_BLKS DEL_LF_ROWS
-----------------------------------
1 1 0
可以看出索引碎片消失。
注意事項：
1）執行索引分析會鎖定表，直到索引分析完畢才解除鎖定；
2）在大多數情況下，Oracle會盡可能的重用索引中已删除條目的空間。
為什麼重建索引
實際上，重建（rebuild）索引就是重新建立索引，但它比删除原索引再重新建立索引的做法要好，因為在重建索引時存儲空間已配置設定給索引，而不需要再指定索引建立語句。
關于索引重建有很多依據，但其中有一些并不準确，下面列舉了一些：
1）Oracle的B樹索引随着時間的推移變得不平衡
由于B樹索引的根塊和所有的葉塊之間的高度始終是一緻的，是以這不正确。
2）索引中被删除的空間無法重用
實際上Oracle會重用被删除的空間。
3）達到一定層數的索引是低效的
索引的層數取決于索引有多少條目，重建不能解決問題。
4）具有糟糕的聚蔟因子的索引，可以通過重建修複
重建索引并不能改變表中資料行或索引的順序，是以聚蔟因子（見使用索引的聚蔟因子）完全不受索引重建影響。如果想改進聚蔟因子，實際上需要重建表。
那麼具體為什麼重建索引呢？
當索引不可用時，應該重建索引，但我們是否應該重建索引來消除索引碎片呢？
當你的查詢大部分都是通過索引通路讀取單個行，那麼重建索引對性能影響很小。但對于範圍查詢，由于大量的索引碎片會導緻查詢增加大量的無效IO，是以重建索引是有意義的，即使Oracle會重用索引碎片，但重建索引也可以使索引變得更加緊湊，進而提高查詢的效率。
重建索引
上面已經使用到重建索引的方法：
alter index IDX_TEST rebuild;
但重建索引的過程中會對表加鎖，阻止其他對表的操作，直到索引重建完成。從Oracle 10g開始，Oracle提供了線上重建索引的方法：
<pre class="sql" name="code">alter index IDX_TEST rebuild online;
線上重建索引不會再導緻索引鎖定。
重建分區索引則需要帶上分區資訊：
alter index IDX_TEST rebuild partition partition_name online;
Oracle也為創佳和重建索引提供了一些參數，用于提高建立和重建索引的效率。
并行建立或重建索引
為了建立索引，資料庫需要進行全表掃描，并行建立可以加快索引的建立速度，速度的提升由并行度和CPU數量決定：
create index IDX_TEST on test(id) parallel 4 online;
也可以用于重建索引：
alter index IDX_TEST rebuild parallel 4 online;
需要注意的是這個操作将使索引的并行度（見使用索引）永遠變為這個值，如下：
select degree from user_indexes where index_name = 'IDX_TEST';
DEGREE
--------------------
4
如果打算讓資料庫在處理你的索引時使用并行機制，則正好，否則，你需要在執行了并行建立和重建操作後禁用并行：
alter index IDX_TEST noparallel;
如果忘記禁用并行，可能會導緻嚴重的性能問題。
在索引建立或重建時避免生成重做資訊
不把建立或者重建的索引項寫入重做日志，可以大大縮短索引建立或重建的時間：
create index IDX_TEST on test(id) nologging online;
也可以在重建索引時使用：
alter index IDX_TEST rebuild nologging online;
nologging不僅可以極大地提高性能，而且不填充多個重做日志檔案，節省空間。
壓縮索引
在非唯一索引中使用壓縮，可以減少重複鍵占用的空間：
compress <數字，并小于等于索引包括的字段值>
一個執行個體如下：
create index IDX_TEST on test(id) compress 1 online;
同樣可以用于重建索引：
alter index IDX_TEST rebuild compress 1 online;
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)