Sql索引介紹

dingonet發表於2008-04-07
Sql索引的介紹[@more@]索引是建立在表的一列或多個列上的輔助物件,目的是加快訪問表中的資料;
  
  Oracle儲存索引的資料結構是B*樹,點陣圖索引也是如此,只不過是葉子節點不同B*數索引;
  
  索引由根節點、分支節點和葉子節點組成,上級索引塊包含下級索引塊的索引資料,葉節點包含索引資料和確定行實際位置的rowid。
  
  使用索引的目的
  加快查詢速度
  減少I/O操作
  消除磁碟排序
  
  何時使用索引
  查詢返回的記錄數
  排序表<40%
  非排序表 <7%
  表的碎片較多(頻繁增加、刪除)
  
  索引的種類
  非唯一索引(最常用)
  唯一索引
  點陣圖索引
  區域性有字首分割槽索引
  區域性無字首分割槽索引
  全域性有字首分割槽索引
  雜湊分割槽索引
  基於函式的索引
  
  管理索引的準則
  
  在表中插入資料後建立索引
  

  。在用SQL*Loader或import工具插入或裝載資料後,建立索引比較有效;
  
  索引正確的表和列
  
  。經常檢索排序大表中40%或非排序表7%的行,建議建索引;
  。為了改善多表關聯,索引列用於聯結;
  。列中的值相對比較唯一;
  。取值範圍(大:B*樹索引,小:點陣圖索引);
  。Date型列一般適合基於函式的索引;
  。列中有許多空值,不適合建立索引
  
  為效能而安排索引列
  
  。經常一起使用多個欄位檢索記錄,組合索引比單索引更有效;
  。把最常用的列放在最前面,例:dx_groupid_serv_id(groupid,serv_id),在where條件中使用groupid或groupid,serv_id,查詢將使用索引,若僅用到serv_id欄位,則索引無效;
  。合併/拆分不必要的索引。
  
  限制每個表索引的數量
  
  。一個表可以有幾百個索引(你會這樣做嗎?),但是對於頻繁插入和更新表,索引越多系統CPU,I/O負擔就越重;
  。建議每張表不超過5個索引。
  
  刪除不再需要的索引
  
  。索引無效,集中表現在該使用基於函式的索引或點陣圖索引,而使用了B*樹索引;
  。應用中的查詢不使用索引;
  。重建索引之前必須先刪除索引,若用alter index … rebuild重建索引,則不必刪除索引。
  
  索引資料塊空間使用
  
  。建立索引時指定表空間,特別是在建立主鍵時,應明確指定表空間;
  。合理設定pctfress,注意:不能給索引指定pctused;
  。估計索引的大小和合理地設定儲存引數,預設為表空間大小,或initial與next設定成一樣大。
  
  考慮並行建立索引
  
  。對大表可以採用並行建立索引,在並行建立索引時,儲存引數被每個查詢伺服器程式分別使用,例如:initial為1M,並行度為8,則建立索引期間至少要消耗8M空間;
  
  考慮用nologging建立索引
  
  。對大表建立索引可以使用nologging來減少重做日誌;
  。節省重做日誌檔案的空間;
  。縮短建立索引的時間;
  。改善了並行建立大索引時的效能。
  
  怎樣建立最佳索引
  
  明確地建立索引
  create index index_name on table_name(field_name)
  tablespace tablespace_name
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
  minextents 1
  maxextents 16382
  pctincrease 0
  );
  
  建立基於函式的索引
  
  。常用與UPPER、LOWER、TO_CHAR(date)等函式分類上,例:
  create index idx_func on emp (UPPER(ename)) tablespace tablespace_name;
  
  建立點陣圖索引
  
  。對基數較小,且基數相對穩定的列建立索引時,首先應該考慮點陣圖索引,例:
  create bitmap index idx_bitm on class (classno) tablespace tablespace_name;
  
  明確地建立唯一索引
  
  。可以用create unique index語句來建立唯一索引,例:
  create unique index dept_unique_idx on dept(dept_no) tablespace idx_1;
  
  建立與約束相關的索引
  
  。可以用using index字句,為與unique和primary key約束相關的索引,例如:
  alter table table_name
  add constraint PK_primary_keyname primary key (field_name)
  using index tablespace tablespace_name;
  
  如何建立區域性分割槽索引
  
  。基礎表必須是分割槽表;
  。分割槽數量與基礎表相同;
  。每個索引分割槽的子分割槽數量與相應的基礎表分割槽相同;
  。基礎表的子分割槽中的行的索引項,被儲存在該索引的相應的子分割槽中,例如:
  Create Index TG_CDR04_SERV_ID_IDX On TG_CDR04(SERV_ID)
  Pctfree 5
  Tablespace TBS_AK01_IDX
  Storage (
  MaxExtents 32768
  PctIncrease 0
  FreeLists 1
  FreeList Groups 1
  )
  local
  /
  
  如何建立範圍分割槽的全域性索引
  
  。基礎表可以是全域性表和分割槽表。
  create index idx_start_date on tg_cdr01(start_date)
  global partition by range(start_date)
  (partition p01_idx vlaues less than (‘0106’)
  partition p01_idx vlaues less than (‘0111’)
  …
  partition p01_idx vlaues less than (‘0401’ ))
  /
  
  重建現存的索引
  
  重建現存的索引的當前時刻不會影響查詢;
  
  重建索引可以刪除額外的資料塊;
  
  提高索引查詢效率;
  alter index idx_name rebuild nologging;
  
  對於分割槽索引:
  alter index idx_name rebuild partition partiton_name nologging;
  
  要刪除索引的原因
  
  。不再需要的索引;
  。索引沒有針對其相關的表所釋出的查詢提供所期望的效能改善;
  。應用沒有用該索引來查詢資料;
  。該索引無效,必須在重建之前刪除該索引;
  。該索引已經變的太碎了,必須在重建之前刪除該索引;
  。語句:drop index idx_name;drop index idx_name drop partition partition_name;
  
  建立索引的代價
  
  基礎表維護時,系統要同時維護索引,不合理的索引將嚴重影響系統資源,主要表現在CPU和I/O上;
  
  插入、更新、刪除資料產生大量db file sequential read鎖等待;
  
  SQL最佳化器簡介
  
  基於規則的最佳化器

  
  。總是使用索引
  。總是從驅動表開始(from子句最右邊的表)
  。只有在不可避免的情況下,才使用全表掃描
  。任何索引都可以
  
  基於成本的最佳化器
  
  。需要表、索引的統計資料
  Analyze table customer compute statistics;
  Analyze table customer estimate statistics sample 5000 rows;
  。表中設定並行度、表分割槽
  
  最佳化器模式
  
  rule模式
  
  。總忽略CBO和統計資訊而基於規則
  choose模式
  
  。Oracle根據情況選擇rule or first_rows or all_rows
  first_rows 模式
  
  。基於成本,以最快的速度返回記錄,會造成總體查詢速度的下降或消耗更多的資源,傾向索引掃描,適合OLTP系統
  all_rows模式
  
  。基於成本,確保總體查詢時間最短,傾向並行全表掃描
  
  例如:
  Select last_name from customer order by last_name;用first_rows時,迅速返回記錄,但I/O量大,用all_rows時,返回記錄慢,但使用資源少。
  
  調整SQL表訪問
  
  全表掃描
  
  。返回記錄:未排序表>40%,排序表>7%,建議採用並行機制來提高訪問速度,DDS;
  
  索引訪問
  
  。最常用的方法,包括索引唯一掃描和索引範圍掃描,OLTP;
  
  快速完全索引掃描
  
  。訪問索引中所有資料塊,結果相當於全表掃描,可以用索引掃描代替全表掃描,例如:
  
  Select serv_id,count(* ) from tg_cdr01 group by serv_id;
  
  評估全表掃描的合法性
  
  如何實現並行掃描
  
  。永久並行化(不推薦)
  alter table customer parallel degree 8;
  
  。單個查詢並行化
  select /*+ full(emp) parallel(emp,8)*/ * from emp;
  
  分割槽表效果明顯
  
  最佳化SQL語句排序
  
  排序的操作:
  
  。order by 子句
  。group by 子句
  。select distinct子句
  。建立索引時
  。union或minus
  。排序合併連線
  
  如何避免排序
  
  。新增索引
  。在索引中使用distinct子句
  。避免排序合併連線
  
  使用提示進行調整
  
  使用提示的原則
  
  。語法:/*+ hint */
  。使用表別名:select /*+ index(e dept_idx)*/ * from emp e
  。檢驗提示
  
  常用的提示
  
  。rule
  。all_rows
  。first_rows
  。use_nl
  。use_hash
  。use_merge
  。index
  。index_asc
  。no_index
  。index_desc(常用於使用max內建函式)
  。index_combine(強制使用點陣圖索引)
  。index_ffs(索引快速完全掃描)
  。use_concat(將查詢中所有or條件使用union all)
  。parallel
  。noparallel
  。full
  。ordered(基於成本)
  
  調整表連線
  
  表連線的型別
  
  。等連線
  where 條件中用等式連線;
  。外部連線(左、右連線)
  
  在where條件子句的等式謂詞放置一個(+)來實現,例如:
  select a.ename,b.comm from emp a,bonus b where a.ename=b.ename(+);
  
  該語句返回所有emp表的記錄;
  。自連線
   Select a.value total, B.value hard, (A.value - b.value) soft ,
  Round((b.value/a.value)*100,1) perc
  From v$sysstat a,v$sysstat b
  Where a.statistic# = 179
  and B.statistic# = 180;
  
  反連線
  
  反連線常用於not in or not exists中,是指在查詢中找到的任何記錄都不包含在結果集中的子查詢;不建議使用not in or not exists;
  
  。半連線
  
  查詢中使用exists,含義:即使在子查詢中返回多條重複的記錄,外部查詢也只返回一條記錄。
  
  巢狀迴圈連線
  
  。被連線表中存在索引的情況下使用;
  。使用use_nl。
  
  hash連線
  
  。Hash連線將驅動表載入在記憶體中,並使用hash技術連線第二個表,提高等連線速度。
  。適合於大表和小表連線;
  。使用use_hash。
  
  排序合併連線
  
  。排序合併連線不使用索引
  。使用原則:
  
  連線表子段中不存在可用索引;
  
  查詢返回兩個表中大部分的資料快;
  
  CBO認為全表掃描比索引掃描執行的更快。
  
  。使用use_merge
  
  使用臨時/中間表
  
  多個大表關聯時,可以分別把滿足條件的結果集存放到中間表,然後用中間表關聯;
  
  SQL子查詢的調整
  
  關聯與非關聯子查詢
  
  。關聯:子查詢的內部引用的是外部表,每行執行一次;
  。非關聯:子查詢只執行一次,存放在記憶體中。
  
  調整not in 和not exists語句
  
  。可以使用外部連線最佳化not in子句,例如:
  select ename from emp where dept_no not in
  (select dept_no from dept where dept_name =‘Math’);
  
  改為:
  select ename from emp,dept
  where emp.dept_no=dept.dept_no
  and dept.dept_name is null;
  
  使用索引調整SQL
  
  Oracle 為什麼不使用索引
  
  。檢查被索引的列或組合索引的首列是否出現在PL/SQL語句的WHERE子句中,這是“執行計劃”能用到相關索引的必要條件。
  
  。看採用了哪種型別的連線方式。ORACLE的共有Sort Merge Join(SMJ)、Hash Join(HJ)和Nested Loop Join(NL)。在兩張表連線,且內表的目標列上建有索引時,只有Nested Loop才能有效地利用到該索引。SMJ即使相關列上建有索引,最多隻能因索引的存在,避免資料排序過程。HJ由於須做HASH運算,索引的存在對資料查詢速度幾乎沒有影響。
  
  。看連線順序是否允許使用相關索引。假設表emp的deptno列上有索引,表dept的列deptno上無索引,WHERE語句有emp.deptno=dept.deptno條件。在做NL連線時,emp做為外表,先被訪問,由於連線機制原因,外表的資料訪問方式是全表掃描,emp.deptno上的索引顯然是用不上,最多在其上做索引全掃描或索引快速全掃描。
  
  。是否用到系統資料字典表或檢視。由於系統資料字典表都未被分析過,可能導致極差的“執行計劃”。但是不要擅自對資料字典表做分析,否則可能導致死鎖,或系統效能下降。
  
  。索引列是否函式的引數。如是,索引在查詢時用不上。
  
  。是否存在潛在的資料型別轉換。如將字元型資料與數值型資料比較,ORACLE會自動將字元型用to_number()函式進行轉換,從而導致上一種現象的發生。
  
  。是否為表和相關的索引蒐集足夠的統計資料。對資料經常有增、刪、改的表最好定期對錶和索引進行分析,可用SQL語句“analyze table xxxx compute statistics for all indexes;”。ORACLE掌握了充分反映實際的統計資料,才有可能做出正確的選擇。
  
  。索引列的選擇性不高。   我們假設典型情況,有表emp,共有一百萬行資料,但其中的emp.deptno列,資料只有4種不同的值,如10、20、30、40。雖然emp資料行有很多,ORACLE預設認定表中列的值是在所有資料行均勻分佈的,也就是說每種deptno值各有25萬資料行與之對應。假設SQL搜尋條件DEPTNO=10,利用deptno列上的索引進行資料搜尋效率,往往不比全表掃描的高。
  
  。索引列值是否可為空(NULL)。如果索引列值可以是空值,在SQL語句中那些要返回NULL值的操作,將不會用到索引,如COUNT(*),而是用全表掃描。這是因為索引中儲存值不能為全空。
  
  。看是否有用到並行查詢(PQO)。並行查詢將不會用到索引。
  
  。如果從以上幾個方面都查不出原因的話,我們只好用採用在語句中加hint的方式強制ORACLE使用最優的“執行計劃”。  hint採用註釋的方式,有行註釋和段註釋兩種方式。  如我們想要用到A表的IND_COL1索引的話,可採用以下方式:  “SELECT /*+ INDEX(A IND_COL1)*/ * FROM A WHERE COL1 = XXX;"
  
  如何遮蔽索引
  
  語句的執行計劃中有不良索引時,可以人為地遮蔽該索引,方法:
  
  。數值型:在索引欄位上加0,例如
  select * from emp where emp_no+0 = v_emp_no;
  
  。字元型:在索引欄位上加‘’,例如
  select * from tg_cdr01 where msisdn||’’=v_msisdn

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/77544/viewspace-1001969/,如需轉載,請註明出處,否則將追究法律責任。

相關文章