數據庫性能優化是無止境的無論哪種優化技術只是一種手段但最重要的不是技術而是思想掌握了索引優化技術僅僅剛入門只有融會貫通舉一反三才能成為高手
本文引用一套實驗室信息管理系統（LIS）使用的數據庫假設我們要查詢年月做檢驗的患者記錄條件是大於歲姓周的患者最終結果按檢查日期進行倒序排列要使用的表有三個
◆lis_report報告主表我們要用到的字段包括i_checkno（檢查號）d_checkdate（檢查日期）i_patientid（患者ID）
◆comm_patient患者信息表我們要用到的字段包括i_patientid（患者ID）s_name（患者姓名）s_code（患者住院號）i_age（患者年齡）i_dept（患者所在病區）
◆lis_code_dept病區信息表我們要用到的字段包括i_id（病區ID主鍵與comm_patient中的i_dept關聯）s_name（病區名）
最終我們構造的SQL如下
select ai_checkno ad_checkdate
bs_name bs_code bi_age
cs_name from lis_report a
inner join comm_patient b on ai_patientid =
bi_patientid inner join lis_code_dept c
on bi_dept = ci_id where ad_checkdate >
and ad_checkdate <
and bi_age>= and bs_name like 周%
order by ad_checkdate desc
我們的SQL使用的這三張表除了創建主鍵時自動創建的索引外均未創建其它索引下圖是無索引時的執行計劃
表comm_patient和lis_report都使用了全表掃描comm_patient全表掃描的成本是lis_report全表掃描的成本是只有表lis_code_dept因關聯時使用的是其主鍵因此這裡使用了主鍵索引從而避免了全表掃描它的成本是我們知道提高查詢性能的目標之一就是消滅掉全表掃描因此我們應該給表comm_patient和lis_report加上適當的索引在SQL代碼的where子句中對comm_patient表我們引用了i_age和s_name字段對lis_report表我們引用了d_checkdate字段通常給這些條件中引用的字段加上索引會提高查詢速度我們先給comm_patient的i_gae字段加上索引下面是對應的執行計劃
表comm_patient的全表掃描消失了取而代之的是索引唯一性掃描成本從一下子降低到了注意這裡並未使用我們給i_age增加的索引但卻靠它觸發了使用表主鍵對應的索引但表lis_report仍然是全表掃描由於where子句中引用了該表的d_checkdate字段因此我們給該字段加上索引看看效果
表lis_report的全表掃描消失了取而代之的是索引范圍降序掃描（INDEX RANGE SCAN DESCENDING）成本也從下降到注意這裡的索引范圍降序掃描的來歷因為我的where子句中引用d_checkdate是介於至的一個范圍這時引用的這種字段上建立的索引通常都是執行范圍掃描因為這種條件返回的值往往不止一行使用降序掃描的原因是order by子句使用了降序排序如果我們將SQL代碼中的order by ad_checkdate desc改為order by ad_checkdate則變為索引范圍掃描（INDEX RANGE SCAN）
至此我們全部消除了全表掃描我們看到加上索引後查詢執行的成本開銷也有所降低因為數據庫表中的記錄數不大因此效果不太明顯如果有上百萬條記錄則會更直觀
雖然索引能提高查詢性能但索引也不能濫用一是因為索引會降低寫入性能二是索引過多給索引管理帶來麻煩有些索引根本就沒有使用這樣的索引只會帶來負面影響基於這些弊端的考慮在設計數據庫結構時應綜合考慮表的使用頻率（使用次數越多越應重點考慮是否建立索引）表中字段的使用頻率（字段使用次數越多越應建立索引）字段類型（數值型字段越應建立索引）值的唯一性（最應建立索引的字段）值的重復性（值重復度越高建立索引的必要性越低）值是否可為空（允許為空的字段一般不建立索引）表中記錄數（記錄數很少時一般不宜建立索引）表是讀操作多一些還是寫操作多一些（讀操作越多的表越應建立索引寫操作越多的表越應避免建立索引）等創建索引的一般原則是在大表的常用且值重復幾率小的字段上創建索引
From:http://tw.wingwit.com/Article/program/Oracle/201311/18362.html
