索引優化和維護

hooca發表於2014-10-22
索引優化
索引設計的建議（針對B樹索引）

1）選擇可選性高的欄位建立索引。
2）對於多表連線，選擇被驅動表的連線欄位建立索引
3）如果選擇欄位有多個但可選性都不高，建議建立複合索引，複合索引的首個欄位應選擇使用率最高的欄位

為了避免索引被抑制，SQL語句中應避免以下情況：

1）欄位型別不匹配
2）條件語句中使用了函式（包括自定義函式）
3）條件語句中使用了公式

函式索引可針對上面第2）條情況，但因為DML操作對維護函式索引的成本較高，應慎重使用。

與索引有關的等待事件主要是db file sequential read，原因可能是

1）應用設計好，索引使用率高（正常現象）。
2）索引建立得過多，DML操作帶來索引維護的開銷。
3）索引碎片過多。
4）索引設計不好，可選度低，近似於全表掃描。
監控索引是否被使用

多餘的索引會增加維護開銷，因此有必要確認是否存在多餘索引。


點選(此處)摺疊或開啟

  1. alter index price_pk_id monitoring usage;
  2. alter index bom_pk_id monitoring usage;
檢視索引是否被使用


點選(此處)摺疊或開啟

  1. select * from v$object_usage;

INDEX_NAME   TABLE_NA MONITO USED   START_MONITORING
------------ -------- ------ ------ -------------------------
END_MONITORING
-------------------------
PRICE_PK_ID  PRICE    YES    NO     10/22/2014 11:59:51




BOM_PK_ID    BOM      YES    YES    10/22/2014 12:03:51
最後關閉索引監控，


點選(此處)摺疊或開啟

  1. alter index price_pk_id nomonitoring usage;
  2. alter index bom_pk_id nomonitoring usage;

檢視索引碎片

大量索引碎片會降低索引效能，檢視索引碎片：


點選(此處)摺疊或開啟

  1. analyze index price_pk_id validate structure offline;
檢視結果


點選(此處)摺疊或開啟

  1. select name,del_lf_rows_len,lf_rows_len
  2. ,(del_lf_rows_len/lf_rows_len)*100 as rate
  3. from index_stats;

NAME            DEL_LF_ROWS_LEN LF_ROWS_LEN       RATE
--------------- --------------- ----------- ----------
PRICE_PK_ID                5459     5480620 .099605519

當索引碎片超過20%，表示已經非常嚴重，應選擇重建索引。

而此時，索引的叢集因子應該也非常大，檢視：


點選(此處)摺疊或開啟

  1. SELECT i.index_name, i.CLUSTERING_FACTOR FROM dba_indexes i WHERE i.index_name like ’PK_TESTCF%’;
重建索引：

點選(此處)摺疊或開啟

  1. alter index price_pk_id rebuild;
重建後再分析索引，碎片就不存在了。

2015.4.23更新

對於已經重建的索引，立即查詢叢集因子，可能並沒有看到有什麼變化：

點選(此處)摺疊或開啟

  1. col index_name format a15
  2. col index_type format a10
  3. col num_rows format 999,999,999
  4. col LEAF_BLOCKS format 999,999,999
  5. col CLUSTERING_FACTOR format 999,999,999

  7. SELECT INDEX_NAME, INDEX_TYPE, NUM_ROWS, LEAF_BLOCKS, CLUSTERING_FACTOR
  8. FROM DBA_INDEXES
  9. WHERE INDEX_NAME='IOB';


INDEX_NAME      INDEX_TYPE     NUM_ROWS  LEAF_BLOCKS CLUSTERING_FACTOR
--------------- ---------- ------------ ------------ -----------------
IOB             NORMAL                0            0                 0

需要在進行索引分析後，才能看到索引統計資訊的變化：

點選(此處)摺疊或開啟

  1. analyze index HK.IOB validate structure offline;

  3. col index_name format a15
  4. col index_type format a10
  5. col num_rows format 999,999,999
  6. col LEAF_BLOCKS format 999,999,999
  7. col CLUSTERING_FACTOR format 999,999,999

  9. SELECT INDEX_NAME, INDEX_TYPE, NUM_ROWS, LEAF_BLOCKS, CLUSTERING_FACTOR
  10. FROM DBA_INDEXES
  11. WHERE INDEX_NAME='IOB';


INDEX_NAME      INDEX_TYPE     NUM_ROWS  LEAF_BLOCKS CLUSTERING_FACTOR
--------------- ---------- ------------ ------------ -----------------
IOB             NORMAL            7,217           17               126









來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22621861/viewspace-1307169/，如需轉載，請註明出處，否則將追究法律責任。

相關文章