在Oracle數據庫中創建索引雖然比較簡單但是要合理的創建索引則比較困難了筆者認為在創建索引時要做到三個適當即在適當的表上適當的列上創建適當數量的索引雖然這可以通過一句話來概括優化的索引的基本准則但是要做到這一點的話需要數據庫管理員做出很大的努力具體的來說要做到這個三個適當有如下幾個要求
一 根據表的大小來創建索引
雖然給表創建索引可以提高查詢的效率但是數據庫管理員需要注意的是索引也需要一定的開銷的為此並不是說給所有的表都創建索引那麼就可以提高數據庫的性能這個認識是錯誤的恰恰相反如果不管三七二十一給所有的表都創建了索引那麼其反而會給數據庫的性能造成負面的影響因為此時濫用索引的開銷可能已經遠遠大於由此帶來的性能方面的收益所以筆者認為數據庫管理員首先需要做到為合適的表來建立索引而不是為所有的表建立索引
一般來說不需要為比較小的表創建索引如在一個ERP系統的數據庫中department表用來存儲企業部門的信息一般企業的部分也就十幾個最多不會超過一百個這條記錄對於人來說可能算是比較多了但是對於計算機來說這給他塞塞牙縫都還不夠所以對類似的小表沒有必要建立索引因為即使建立了索引其性能也不會得到很大的改善相反索引建立的開銷如維護成本等等要比這個要大也就是說付出的要比得到的多顯然違反常理
另外就是對於超大的表也不一定要建立索引有些表雖然比較大記錄數量非常的多但是此時為這個表建立索引並一定的合適如系統中有一張表其主要用來保存數據庫中的一些變更信息往往這些信息只給數據庫管理員使用此時為這張表建立索引的話反而不合適因為這張表很少用到只有在出問題的時候才需要查看其次其即使查看需要查詢的紀錄也不會很多可能就是最近一周的更新記錄等等對於對於一些超大的表建立索引有時候往往不能夠達到預計的效果而且在打表上建立索引其索引的開銷要比普通的表大的多那麼到底是否給大表建立索引呢?筆者認為主要是看兩個方面的內容首先是需要關注一下在這張大表中經常需要查詢的記錄數量一般來說如果經常需要查詢的數據不超過%到%的話那就沒有必要為其建立索引的必要因為此時建立索引的開銷可能要比性能的改善大的多這個比例只是一個經驗的數據如果數據庫管理員需要得出一個比較精確的結論那麼就需要進行測試分析即數據庫管理員需要測試一下全表掃描的時間看看其是否比建立索引後的查詢時間要長或者短如果是長的話則說明有建立索引的必要但是如果沒有的話則說明還是全表掃描速度來的快此時也就沒有必要建立索引了
總之在考慮是否該為表建立索引時一般來說小表沒有建立索引的必要而對於打表的話則需要進行實際情況實際分析簡單一點的可以根據大致的比率來確定如果要精確一點的則可以進行全表掃描性能分析以判斷建立索引後是否真的如預期那樣改善了數據庫性能
二 根據列的特征來創建索引
列的特點不同索引創建的效果也不同數據庫管理員需要了解為哪些列創建索引可以起到事倍功半的效果同時也需要了解為哪些列創建索引反而起到的是事倍功半的效果這有利於他們了解到底給為怎麼樣的字段建立索引
根據筆者的經驗往往為如下特征的列創建索引能夠起到比較明顯的效果如對於一些重復內容比較少的列特別是對於那些定義了唯一約束的列在這些列上建立索引往往可以起到非常不錯的效果如對於一些null值的列與非Null值的列混合情況下如果用戶需要經常查詢所有的非Null值記錄的列則最好為其設置索引如果經常需要多表連接查詢在用與連接的列上設置索引可以達到事半功倍的效果
可見索引設置的是否恰當不僅跟數據庫設計架構有關而且還跟企業的經濟業務相關為此對於一些套裝軟件雖然一開始數據庫管理員已經做了索引的優化工作但是隨著後來經濟數據的增加這個索引的效果會越來越打折扣這主要是因為記錄的表化影響到了索引優化的效果所以筆者建議各位數據庫管理員即使采用的是大牌軟件公司的套裝軟件也需要隔一段時間如一年對數據庫的索引進行優化該去掉的去掉該調整的調整以提高數據庫的性能
如在數據庫中有一張表是用來保存用戶信息的其中有個字段身份證號碼這是一個唯一的字段在數據庫設計時給這個字段創建了索引但是當這個數據庫投入使用之後用戶不怎麼輸入用戶的身份證號碼而且平時也基本不按這個號碼來進行查詢當記錄月來月多時這個身份證號碼上的索引字段不但不能夠改善數據庫的查詢性能反而成了雞肋對於這些有很多NULL值的列而且不會經常查詢所有的非NULL值記錄的列數據庫管理員要下決心即使清除這些列上的索引
所以說索引的優化與調整是一個動態的過程並不是說數據庫設計好之後就不需要經過調整數據庫管理員往往需要根據記錄的變化情況來進行適當的變更以提高索引的效果
三 在一個表上創建多少索引合適?
雖然說在表上創建索引的數量沒有限制但是決不是越多越好也就是說在創建索引這項事情上+〉往往不成立有時候創建索引越多其可能會得到適得其反的效果那麼在一個表上到底給創建多少索引合適呢?這個沒有一個明確的標准而是需要數據庫管理員根據實際的用途以及數據庫中記錄的情況來進行判斷
通常來說表的索引越多其查詢的速度也就越快但是表的更新速度則會降低這主要是因為表的更新(如往表中插入一條記錄)速度反而隨著索引的增加而增加這主要是因為在更新記錄的同時需要更新相關的索引信息為此到底在表中創建多少索引合適就需要在這個更新速度與查詢速度之間取得一個均衡點如對於一些數據倉庫或者決策型數據庫系統其主要用來進行查詢相關的記錄往往是在數據庫初始化的時候倒入此時設置的索引多一點可以提高數據庫的查詢性能同時因為記錄不怎麼更新所以索引比較多的情況下也不會影響到更新的速度即使在起初的時候需要導入大量的數據此時也可以先將索引禁用掉等到數據導入完畢後再啟用索引可以通過這種方式來減少索引對數據更新的影響相反如果那些表中經常需要更新記錄如一些事務型的應用系統數據更新操作是家常便飯的事情此時如果在一張表中建立過多的索引則會影響到更新的速度由於更新操作比較頻繁所以對其的負面影響要比查詢效率提升要大的多此時就需要限制索引的數量只在一些必要的字段上建立索引
筆者在平時數據庫優化時往往會根據這些表的用途來為列設置索引可以查詢相關的動態視圖看看對於這張表的操作是更新操作(包括更新刪除插入等等)占的比例大還是查詢操作占的比例大當過多的索引已經影響到更新操作的速度時則數據庫管理員就需要先禁用某些索引以提高數據庫的性能
總之在適當的表適當的列上建立適當的索引這一句話包含的意思有很多以上內容只是一部分內容俗話說師傅領進門修行靠自身筆者在這裡指能夠點到為止一些具體的索引優化內容還是需要各位讀者在日常工作中去體會與總結
From:http://tw.wingwit.com/Article/program/Oracle/201311/18407.html
