SQL Tuning 基礎概述05 - Oracle 索引類型及介紹

標籤：style   blog   io   color   使用   sp   strong   on   資料   

一、B-Tree索引

三大特點：高度較低、儲存列值、結構有序

1.1利用索引特性進行最佳化

   外鍵上建立索引：不但可以提升查詢效率，而且可以有效避免鎖的競爭（外鍵所在表delete記錄未提交，主鍵所在表會被鎖住）。

   統計類查詢SQL：count(), avg(), sum(), max(), min()

   排序操作：order by欄位建立索引

   去重操作：distinct

   UNION/UNION ALL：union all不需要去重，不需要排序

1.2聯合索引
   應用情境一：SQL查詢列很少，建立查詢列的聯合索引可以有效消除回表，但一般超過3個欄位的聯合索引都是不合適的.
   應用情境二：在欄位A返回記錄多，在欄位B返回記錄多，在欄位A,B同時查詢返回記錄少，比如執行下面的查詢，結果c1,c2都很多，c3卻很少。

    select count(1) c1 from t where A = 1;    select count(1) c2 from t where B = 2;    select count(1) c3 from t where A = 1 and B = 2;

   聯合索引的列誰在前？
    普遍流行的觀點：重複記錄少的欄位放在前面，重複記錄多的放在後面，其實這樣的結論並不準確。

    drop table t purge;    create table t as select * from dba_objects;    create index idx1_object_id on t(object_id,object_type);    create index idx2_object_id on t(object_type,object_id);

    --等值查詢：
    select * from t where object_id = 20 and object_type = ‘TABLE‘;    select /*+ index(t,idx1_object_id) */ * from t where object_id = 20 and object_type = ‘TABLE‘;    select /*+ index(t,idx2_object_id) */ * from t where object_id = 20 and object_type = ‘TABLE‘;

    觀察語句執行計畫得出結論：等值查詢情況下，複合式索引的列無論哪一列在前，效能都一樣。

    --範圍查詢：
    select * from t where object_id >=20 and object_id < 2000 and object_type = ‘TABLE‘;    select /*+ index(t,idx1_object_id) */ * from t where object_id >=20 and object_id < 2000 and object_type = ‘TABLE‘;    select /*+ index(t,idx2_object_id) */ * from t where object_id >=20 and object_id < 2000 and object_type = ‘TABLE‘;

    結論：複合式索引的列，等值查詢列在前，範圍查詢列在後。 但如果在實際生產環境要確定複合式索引列誰在前，要綜合考慮所有常用SQL使用索引情況，因為索引過多會影響入庫效能。例如這個例子中，如果其他SQL可以頻繁使用到object_id的單列索引，而當單列的查詢列和聯合索引的前置列一樣，單列可以不建立索引，這種情況單列的查詢也可以用到複合式索引。則綜合考慮將object_id放在前面。

1.3索引的危害
    表上有過多索引主要會嚴重影響插入效能；

    對delete操作，刪除少量資料索引可以有效快速定位，提升刪除效率，但是如果刪除大量資料就會有負面影響；

    對update操作類似delete，而且如果更新的是非索引列則無影響。

1.4索引的監控

    --監控    alter index [index_name] monitoring usage;    select * from v$object_usage;    --取消監控：      alter index [index_name] nomonitoring usage;

    根據對索引監控的結果，對長時間未使用的索引可以考慮將其刪除。

1.5索引的常見執行計畫

    INDEX FULL SCAN：索引的全掃描，單塊讀，有序
    INDEX RANGE SCAN：索引的範圍掃描
    INDEX FAST FULL SCAN：索引的快速全掃描，多塊讀，無序
    INDEX FULL SCAN(MIN/MAX)：針對MAX(),MIN()函數的查詢
    INDEX SKIP SCAN：查詢條件沒有用到複合式索引的第一列，而複合式索引的第一列重複度較高時，可能用到

二、位元影像索引

    應用情境：表的更新操作極少，重複度很高的列。

    優勢：count(*) 效率高

              即席查詢效率高（這個特性實際驗證存在版本差異：在10.2.0.1版本驗證位元影像索引的即席查詢效率遠高於複合式索引查詢效率，而在11.2.0.3版本驗證普通複合式索引的即席查詢效率卻比位元影像索引的效率還要高效）

  create table t(  name_id,  gender not null,  location not null,  age_range not null,  data  )as select   rownum,  decode(floor(dbms_random.value(0,2)),0,‘M‘,1,‘F‘) gender,  ceil(dbms_random.value(0,50)) location,  decode(floor(dbms_random.value(0,4)),0,‘child‘,1,‘young‘,2,‘middle‘,3,‘old‘) age_range,  rpad(‘*‘,20,‘*‘) data  from dual connect by rownum <= 100000; 

  create index idx_t on t(gender,location,age_range);  create bitmap index gender_idx on t(gender);  create bitmap index location_idx on t(location);  create bitmap index age_range_idx on t(age_range);

  select * from t where gender = ‘M‘ and location in (1,10,30) and age_range = ‘child‘;  select /*+ index(t,idx_t) */* from t where gender = ‘M‘ and location in (1,10,30) and age_range = ‘child‘;

三、函數索引

    應用情境：不得不對某一列進行函數運算的情境。

    利用函數索引的效率要低於利用普通索引的。

參考書籍：《收穫，不止Oracle》

SQL Tuning 基礎概述05 - Oracle 索引類型及介紹