索引在ORACLE數據庫中的應用分析
2024-08-29 13:29:10
供稿：網友
索引在數據庫中的應用分析
索引是提高數據查詢最有效的方法，也是最難全面掌握的技術，因為正確的索引可能使效率提高10000倍，而無效的索引可能是浪費了數據庫空間，甚至大大降低查詢性能。
索引的管理成本
1、 存儲索引的磁盤空間
2、 執行數據修改操作（insert、update、delete）產生的索引維護
3、 在數據處理時回需額外的回退空間。
實際數據修改測試：
一個表有字段a、b、c，同時進行插入10000行記錄測試
在沒有建索引時平均完成時間是2.9秒
在對a字段建索引后平均完成時間是6.7秒
在對a字段和b字段建索引后平均完成時間是10.3秒
在對a字段、b字段和c字段都建索引后平均完成時間是11.7秒
從以上測試結果可以明顯看出索引對數據修改產生的影響
索引按存儲方法分類
b*樹索引
b*樹索引是最常用的索引，其存儲結構類似書的索引結構，有分支和葉兩種類型的存儲數據塊，分支塊相當于書的大目錄，葉塊相當于索引到的具體的書頁。一般索引及唯一約束索引都使用b*樹索引。
位圖索引
位圖索引儲存主要用來節省空間，減少oracle對數據塊的訪問，它采用位圖偏移方式來與表的行id號對應，采用位圖索引一般是重復值太多的表字段。位圖索引在實際密集型oltp（數據事務處理）中用得比較少，因為oltp會對表進行大量的刪除、修改、新建操作，oracle每次進行操作都會對要操作的數據塊加鎖，所以多人操作很容易產生數據塊鎖等待甚至死鎖現象。在olap（數據分析處理）中應用位圖有優勢，因為olap中大部分是對數據庫的查詢操作，而且一般采用數據倉庫技術，所以大量數據采用位圖索引節省空間比較明顯。
索引按功能分類
唯一索引
唯一索引有兩個作用，一個是數據約束，一個是數據索引，其中數據約束主要用來保證數據的完整性，唯一索引產生的索引記錄中每一條記錄都對應一個唯一的rowid。
主關鍵字索引
主關鍵字索引產生的索引同唯一索引，只不過它是在數據庫建立主關鍵字時系統自動建立的。
一般索引
一般索引不產生數據約束作用，其功能主要是對字段建立索引表，以提高數據查詢速度。
索引按索引對象分類
單列索引（表單個字段的索引）
多列索引（表多個字段的索引）
函數索引（對字段進行函數運算的索引）
建立函數索引的方法：
create index 收費日期索引 on gc_dfss(trunc(sk_rq))
create index 完全客戶編號索引 on yhzl(qc_bh||kh_bh)
在對函數進行了索引后，如果當前會話要引用應設置當前會話的query_rewrite_enabled為true。
alter session set query_rewrite_enabled=true
注：如果對用戶函數進行索引的話，那用戶函數應加上 deterministic參數，意思是函數在輸入值固定的情況下返回值也固定。例：
create or replace function trunc_add（input_date date）return date deterministic
as
begin
return trunc(input_date+1);
end trunc_add;
應用索引的掃描分類
index unique scan（按索引唯一值掃描）
select * from zl_yhjbqk where hbs_bh='5420016000'
index range scan（按索引值范圍掃描）
select * from zl_yhjbqk where hbs_bh>'5420016000'
select * from zl_yhjbqk where qc_bh>'7001'
index fast full scan（按索引值快速全部掃描）
select hbs_bh from zl_yhjbqk order by hbs_bh
select count(*) from zl_yhjbqk
select qc_bh from zl_yhjbqk group by qc_bh
什么情況下應該建立索引
表的主關鍵字
自動建立唯一索引
如zl_yhjbqk（用戶基本情況）中的hbs_bh（戶標識編號）
表的字段唯一約束
oracle利用索引來保證數據的完整性
如lc_hj（流程環節）中的lc_bh+hj_sx（流程編號+環節順序）
直接條件查詢的字段
在sql中用于條件約束的字段
如zl_yhjbqk（用戶基本情況）中的qc_bh（區冊編號）
select * from zl_yhjbqk where qc_bh=’7001’
查詢中與其它表關聯的字段
字段常常建立了外鍵關系
如zl_ydcf（用電成份）中的jldb_bh（計量點表編號）
select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’
查詢中排序的字段
排序的字段如果通過索引去訪問那將大大提高排序速度
select * from zl_yhjbqk order by qc_bh（建立qc_bh索引）
select * from zl_yhjbqk where qc_bh='7001' order by cb_sx（建立qc_bh+cb_sx索引，注：只是一個索引，其中包括qc_bh和cb_sx字段）
查詢中統計或分組統計的字段
select max(hbs_bh) from zl_yhjbqk
select qc_bh,count(*) from zl_yhjbqk group by qc_bh
什么情況下應不建或少建索引
表記錄太少
如果一個表只有5條記錄，采用索引去訪問記錄的話，那首先需訪問索引表，再通過索引表訪問數據表，一般索引表與數據表不在同一個數據塊，這種情況下oracle至少要往返讀取數據塊兩次。而不用索引的情況下oracle會將所有的數據一次讀出，處理速度顯然會比用索引快。
如表zl_sybm（使用部門）一般只有幾條記錄，除了主關鍵字外對任何一個字段建索引都不會產生性能優化，實際上如果對這個表進行了統計分析后oracle也不會用你建的索引，而是自動執行全表訪問。如：
select * from zl_sybm where sydw_bh='5401'（對sydw_bh建立索引不會產生性能優化）
經常插入、刪除、修改的表
對一些經常處理的業務表應在查詢允許的情況下盡量減少索引，如zl_yhbm，gc_dfss，gc_dfys，gc_fpdy等業務表。
數據重復且分布平均的表字段
假如一個表有10萬行記錄，有一個字段a只有t和f兩種值，且每個值的分布概率大約為50%，那么對這種表a字段建索引一般不會提高數據庫的查詢速度。
經常和主字段一塊查詢但主字段索引值比較多的表字段
如gc_dfss（電費實收）表經常按收費序號、戶標識編號、抄表日期、電費發生年月、操作標志來具體查詢某一筆收款的情況，如果將所有的字段都建在一個索引里那將會增加數據的修改、插入、刪除時間，從實際上分析一筆收款如果按收費序號索引就已經將記錄減少到只有幾條，如果再按后面的幾個字段索引查詢將對性能不產生太大的影響。
如何只通過索引返回結果
一個索引一般包括單個或多個字段，如果能不訪問表直接應用索引就返回結果那將大大提高數據庫查詢的性能。對比以下三個sql，其中對表zl_yhjbqk的hbs_bh和qc_bh字段建立了索引：
1 select hbs_bh,qc_bh,xh_bz from zl_yhjbqk where qc_bh=’7001’
執行路徑:
select statement, goal = choose 11 265 5565
table access by index rowid dlyx zl_yhjbqk 11 265 5565
index range scan dlyx 區冊索引 1 265
平均執行時間（0.078秒）
2 select hbs_bh,qc_bh from zl_yhjbqk where qc_bh=’7001’
執行路徑:
select statement, goal = choose 11 265 3710
table access by index rowid dlyx zl_yhjbqk 11 265 3710
index range scan dlyx 區冊索引 1 265
平均執行時間（0.078秒）
3 select qc_bh from zl_yhjbqk where qc_bh=’7001’
執行路徑:
select statement, goal = choose 1 265 1060
index range scan dlyx 區冊索引 1 265 1060
平均執行時間（0.062秒）
從執行結果可以看出第三條sql的效率最高。執行路徑可以看出第1、2條sql都多執行了table access by index rowid（通過rowid訪問表） 這個步驟，因為返回的結果列中包括當前使用索引(qc_bh)中未索引的列（hbs_bh,xh_bz），而第3條sql直接通過qc_bh返回了結果，這就是通過索引直接返回結果的方法。
如何重建索引
alter index 表電量結果表主鍵 rebuild
如何快速新建大數據量表的索引
如果一個表的記錄達到100萬以上的話，要對其中一個字段建索引可能要花很長的時間，甚至導致服務器數據庫死機，因為在建索引的時候oracle要將索引字段所有的內容取出并進行全面排序，數據量大的話可能導致服務器排序內存不足而引用磁盤交換空間進行，這將嚴重影響服務器數據庫的工作。解決方法是增大數據庫啟動初始化中的排序內存參數，如果要進行大量的索引修改可以設置10m以上的排序內存（oracle缺省大小為64k），在索引建立完成后應將參數修改回來，因為在實際oltp數據庫應用中一般不會用到這么大的排序內存。
葉正盛
2003-08-15
