【51CTO獨(dú)家特稿】低碳指數(shù)：在這里為了方便計(jì)算和直觀，我們以Intel至強(qiáng)X7500處理器的TDP為標(biāo)準(zhǔn)計(jì)算能耗（TDP=130W/h=2.167W/m=0.036W/s）。另外根據(jù)中國林業(yè)局的數(shù)據(jù)，一棵樹一天吸收二氧化碳量為5.023kg，每一度電產(chǎn)生0.785公斤二氧化碳。

如果按照本文方法優(yōu)化后數(shù)據(jù)庫執(zhí)行時(shí)間由191秒縮減到189秒，也就是單位時(shí)間少1%的能量消耗。那么在一天里將減少0.03kw電能消耗，約合0.023kg二氧化碳排放，按我們的計(jì)算是一天減少0.05棵樹二氧化碳吸收量。

本文引用一套實(shí)驗(yàn)室信息管理系統(tǒng)（LIS）使用的數(shù)據(jù)庫，假設(shè)我們要查詢2008年11月做檢驗(yàn)的患者記錄，條件是大于80歲，姓周的患者，最終結(jié)果按檢查日期進(jìn)行倒序排列。要使用的表有三個(gè)：

◆lis_report：報(bào)告主表，我們要用到的字段包括i_checkno（檢查號(hào)），d_checkdate（檢查日期），i_patientid（患者ID）；

◆comm_patient：患者信息表，我們要用到的字段包括i_patientid（患者ID），s_name（患者姓名），s_code（患者住院號(hào)），i_age（患者年齡），i_dept（患者所在病區(qū)）；

◆lis_code_dept：病區(qū)信息表，我們要用到的字段包括i_id（病區(qū)ID，主鍵，與comm_patient中的i_dept關(guān)聯(lián)），s_name（病區(qū)名）。

最終我們構(gòu)造的SQL如下：

select a.i_checkno, a.d_checkdate, b.s_name, b.s_code, b.i_age, c.s_name  
  from lis_report a  
 inner join comm_patient b on a.i_patientid = b.i_patientid  
 inner join lis_code_dept c on b.i_dept = c.i_id  
 where a.d_checkdate > '2008-11-01' 
   and a.d_checkdate < '2008-11-30' 
   and b.i_age>=80  
   and b.s_name like '周%' 
 order by a.d_checkdate desc 

我們的SQL使用的這三張表除了創(chuàng)建主鍵時(shí)自動(dòng)創(chuàng)建的索引外，均未創(chuàng)建其它索引，下圖是無索引時(shí)的執(zhí)行計(jì)劃。

圖 1 無索引時(shí)的執(zhí)行計(jì)劃

從圖1可以看出，表comm_patient和lis_report都使用了全表掃描，comm_patient全表掃描的成本是18，lis_report全表掃描的成本是191，只有表lis_code_dept因關(guān)聯(lián)時(shí)使用的是其主鍵，因此這里使用了主鍵索引，從而避免了全表掃描，它的成本是0。我們知道提高查詢性能的目標(biāo)之一就是消滅掉全表掃描，因此我們應(yīng)該給表comm_patient和lis_report加上適當(dāng)?shù)乃饕赟QL代碼的where子句中，對(duì)comm_patient表，我們引用了i_age和s_name字段，對(duì)lis_report表，我們引用了d_checkdate字段，通常給這些條件中引用的字段加上索引會(huì)提高查詢速度，我們先給comm_patient的i_gae字段加上索引，下面是對(duì)應(yīng)的執(zhí)行計(jì)劃。

圖 2 給comm_patient的i_age加上索引后的執(zhí)行計(jì)劃

從圖2可以看出，表comm_patient的全表掃描消失了，取而代之的是索引唯一性掃描，成本從18一下子降低到1了，注意這里并未使用我們給i_age增加的索引，但卻靠它觸發(fā)了使用表主鍵對(duì)應(yīng)的索引。但表lis_report仍然是全表掃描，由于where子句中引用了該表的d_checkdate字段，因此我們給該字段加上索引看看效果。

圖 3 給lis_report的d_checkdate字段加上索引后的執(zhí)行計(jì)劃

從上圖可以看出，表lis_report的全表掃描消失了，取而代之的是索引范圍降序掃描（INDEX RANGE SCAN DESCENDING），成本也從191下降到189。注意這里的索引范圍降序掃描的來歷，因?yàn)槲业膚here子句中引用d_checkdate是介于2008-11-01至2008-11-30的一個(gè)范圍，這時(shí)引用的這種字段上建立的索引通常都是執(zhí)行范圍掃描，因?yàn)檫@種條件返回的值往往不止一行。使用降序掃描的原因是order by子句使用了降序排序，如果我們將SQL代碼中的“order by a.d_checkdate desc”改為“order by a.d_checkdate”，則變?yōu)樗饕秶鷴呙瑁↖NDEX RANGE SCAN）。

至此我們?nèi)肯巳頀呙?，我們看到加上索引后，查詢?zhí)行的成本開銷也有所降低，因?yàn)閿?shù)據(jù)庫表中的記錄數(shù)不大，因此效果不太明顯，如果有上百萬條記錄則會(huì)更直觀。

雖然索引能提高查詢性能，但索引也不能濫用，一是因?yàn)樗饕龝?huì)降低寫入性能，二是索引過多給索引管理帶來麻煩，有些索引根本就沒有使用，這樣的索引只會(huì)帶來負(fù)面影響，基于這些弊端的考慮，在設(shè)計(jì)數(shù)據(jù)庫結(jié)構(gòu)時(shí)應(yīng)綜合考慮表的使用頻率（使用次數(shù)越多越應(yīng)重點(diǎn)考慮是否建立索引），表中字段的使用頻率（字段使用次數(shù)越多越應(yīng)建立索引），字段類型（數(shù)值型字段越應(yīng)建立索引），值的唯一性（最應(yīng)建立索引的字段），值的重復(fù)性（值重復(fù)度越高，建立索引的必要性越低），值是否可為空（允許為空的字段一般不建立索引），表中記錄數(shù)（記錄數(shù)很少時(shí)一般不宜建立索引），表是讀操作多一些還是寫操作多一些（讀操作越多的表越應(yīng)建立索引，寫操作越多的表越應(yīng)避免建立索引）等，創(chuàng)建索引的一般原則是：在大表的常用且值重復(fù)幾率小的字段上創(chuàng)建索引。

數(shù)據(jù)庫性能優(yōu)化是無止境的，無論哪種優(yōu)化技術(shù)只是一種手段，但最重要的不是技術(shù)，而是思想，掌握了索引優(yōu)化技術(shù)僅僅剛?cè)腴T，只有融會(huì)貫通，舉一反三才能成為高手。

[[11602]]

【編輯推薦】

1. Oracle數(shù)據(jù)庫索引的優(yōu)點(diǎn)與缺點(diǎn)簡介
2. Oracle使用游標(biāo)觸發(fā)器的實(shí)際存儲(chǔ)過程
3. Oracle使用相關(guān)觸發(fā)器來實(shí)現(xiàn)自增ID
4. Oracle查看和修改其***的游標(biāo)數(shù)
5. Oracle delete數(shù)據(jù)后的釋放表空間問題的解決