為何在查詢中索引未被使用(二)——問題解答

昨天只是和大家談了下，為什麼索引沒有被使用的原因，那麼今天來和大家分享下，這這些情況下沒有使用的索引的原因進行分析下以及排錯的步驟。

排錯步驟

“為什麼索引沒有被使用”是一個涉及面較廣的問題。有很多種原因會導致索引沒有被使用。下面我們一起來看排錯的步驟：

快速檢查

• 表上是否存在索引？

  檢查您認為應該透過索引訪問的表上是否真的有定義索引。那些索引可能已經被刪掉或者在建立的時候就失敗了 – 比如一種可能的場景是，在對錶做匯入或 load 操作後，由於軟體或人為錯誤造成索引沒有被建立。下面的語句可以用來檢查索引是否存在。
  
  SELECT index_name FROM user_indexes WHERE table_name = &Table_Name;

• 索引是否應該被使用？

  Oracle 不會僅僅因為有索引存在就一定要使用索引。如果一個查詢需要檢索出這個表裡所有的記錄（比如說表之間做連線操作），那為什麼還要既訪問索引的所有資料又訪問表的所有資料呢？在這種情況下只訪問表的資料會更快。對所有的查詢 Oracle Optimizer 會基於統計資訊來計算各種訪問路徑，包括索引，從而選出最優的一個。

索引本身的問題

• 索引列或者索引的前置列是否在單表（non-join）查詢的 Where 條件中（predicate list）？

  如果不是，至少需要索引前置列在查詢謂詞列表中，查詢才能使用索引。（例外：請見下面的 Skip Scan）。
  
  示例:  
  在列 EMP.EMPNO 上定義了單列索引 EMPNO_I1，同時在列 EMP.EMPNO 和 EMP.DEPT 上定義了聯合索引 EMPNO_DEPT_I2（EMP.EMPNO為索引前置列）。那麼必須在查詢謂詞列表中（where從句）使用列 EMP.EMPNO，最佳化器才能使用這兩個索引中的某一個。
  
  SELECT ename, sal, deptno FROM emp WHEREempno<100;
  
  例外:
  
• 只要索引中包含查詢所需的所有列, 而且至少有一個索引列中含有非空約束，CBO 就能夠使用索引快速全掃描(INDEX_FFS)。執行 INDEX_FFS 不需要索引前置列。需要注意的是 INDEX_FFS 不能保證返回的行是排序的。結果的順序是與讀取索引塊的順序一致的，只有當使用了 'order by' 子句時才能保證結果是排序的。
• CBO 能使用 Index Skip Scan (INDEX_SS). 執行 INDEX_SS 不需要索引前置列。
• CBO 能夠選用一個索引來避免排序，但是索引列必須存於在 order by 子句中才可以。

• 索引列是否用在連線謂詞中（join predicates）？

  例如，下面這個連線謂詞定義瞭如何在表 emp 和 dept 的 deptno 列上做連線：
  
  emp.deptno = dept.deptno
  
  如果索引列是連線謂詞的一部分，那麼查詢在執行時使用了哪種型別的連線？
  
  
  • 雜湊/排序合併連線（Hash / Sort Merge Join）: 對於雜湊連線和排序合併，在連線執行的時候，外部表的資訊還沒有獲得，因此無法進行對內部表的行檢索。它的處理方式是將外部表和內部表分別查詢後將結果合併。雜湊連線和排序合併的內部表不能透過連線的索引列單獨被訪問。這是連線型別的執行機制的限制。巢狀迴圈連線有所不同，它們允許透過索引查詢內部表的連線列。
    
  • 巢狀迴圈連線（Nested Loops Join）:巢狀迴圈連線讀取外部表，然後利用所收集的資訊訪問內部表。該演算法允許對內部表基於索引進行查詢。
  
  只有巢狀迴圈連線（Nested loops join）允許索引在內部表中僅基於連線列進行查詢。
  
  另外，連線的順序（join order）是否允許使用索引?
  一個巢狀迴圈連線的外部表必須已經訪問過，才可以在內部表中使用索引。檢視 explain plan，以確定哪些訪問路徑已經使用。由於這個限制，表的連線順序是很重要的。
  
  例如：如果我們透過"emp.deptno = dept.deptno"來對 EMP 和 DEPT 做連線，並且在 EMP.DEPTNO 有一個索引，並假設查詢中沒有與 EMP.DEPTNO 相關的其他謂詞，EMP 是在 DEPT 前被訪問，然後沒有值可用於在 EMP.DEPTNO 索引中查詢。在這種連線順序下，要想使用這個索引我們只能使用全索引掃描或索引快速全掃描。在這種情況下，全表掃描（FTS）的成本可能更小。

• 索引列在 IN 或者多個 OR 語句中？

  比如：
  
  emp.deptno IN (10,23,34,....)
  或
  
  emp.deptno = 10 OR emp.deptno = 23 OR emp.deptno = 34 ....
  
  這種情況下查詢可能已經被轉化為不能使用索引的語句。

• 索引列是否被函式修改？

  索引不能用於被函式修改的列。函式索引（function based  indexes）可以用來解決這個問題。
  
  
  Oracle? Database Concepts
  11g Release 2 (11.2)
  Part Number E25789-01
  Chapter 3 Indexes and Index-Organized Tables
  Section: Function-Based Indexes
  
  
  Oracle? Database Performance Tuning Guide
  11g Release 2 (11.2)
  Part Number E16638-07
  Chapter 14 Using Indexes and Clusters
  Section 14.2 Using Function-based Indexes for Performance
  

• 隱式型別轉換（implicit type conversion）是什麼？

  如果進行比較的兩個值的資料型別不同，則 Oracle 必須將其中一個值進行型別轉換使其能夠比較。這就是所謂的隱式型別轉換。通常當開發人員將數字儲存在字元列時會導致這種問題的產生。Oracle 在執行時會強制轉化其中一個值，（由於固定的規則）在索引字元列使用 to_number。由於新增函式到索引列所以導致索引不被使用。實際上，Oracle 也只能這麼做，型別轉換是一個應用程式設計因素。由於轉換是在每行都進行的，這會導致效能問題。

• 是否在語義（semantically）上無法使用索引？

  出於對查詢整體成本的考慮，一個成本較低的執行計劃中可能是無法使用索引的。某索引可能已經被考慮在某種連線排序及方法中，但是成本最低的那個執行計劃中卻無法從“語義”角度使用該索引。

• 錯誤型別的索引掃描？

  例如：快速全索引掃描而不是索引範圍掃描
  這可能是最佳化器選擇了所需的索引，但卻使用了客戶不希望的掃描方法。在這種情況下，利用 INDEX_FFS，INDEX_ASC 和 INDEX_DESC 提示來強制使用需要的掃描型別。 
  我們還可以定義索引的排序順序為遞增或遞減。Oracle 對待降序索引就好像它是基於函式的索引，因此與預設使用的升序的執行計劃不同。透過檢視執行計劃，您看不到使用升序或降序，需要額外檢查檢視 DBA_IND_COLUMNS 的'DESCEND'列。

• 是否索引列為可空？

  索引不儲存 NULL 值，除非該索引為聯合索引（即多列索引），或者它是一個點陣圖索引。
  只有至少有一個索引列有值，聯合索引才儲存空值。聯合索引中尾部的空值也會被存放在索引中。如果所有列的值都為空，這行將不會儲存在索引中。由於索引中缺乏 NULL 值，那麼一些結果中可能會返回 NULL 值（如count）的操作可能會被禁用索引。這是因為最佳化器不能保證在單獨使用索引時可以獲得準確的資訊。
  點陣圖索引允許儲存空值。因此最佳化器會使用這些索引,無論它們的結果可信與否。索引上的空值有時很有用，特別對於某些型別的 SQL 語句，如與聚合函式 COUNT 查詢。示例:
  
  SELECT count(*) FROM emp;

• NLS_SORT是否設定為二進位制（BINARY）？

  如果 NLS_SORT 未設定為二進位制，索引將不會被使用。這是因為索引是基於 Key 值的二進位制順序來建立的（pre-sorted使用二進位制值）。無論最佳化器設定為何種方法，NLS_SORT 不是二進位制時，將使用全表掃描。

• 是否使用的是不可見索引（invisible indexes）？

  從 Oracle Database 11g Release 1開始，您可以建立不可見索引或將一個已經存在的索引標記為不可見。Optimizer 不會考慮不可見索引，除非在 session 或 system 級將引數 OPTIMIZER_USE_INVISIBLE_INDEXES 設定為 TRUE。DML 操作還是會維護這些不可見索引的。詳見：
  
  
  Oracle? Database Administrator's Guide
  11g Release 2 (11.2)
  Part Number E25494-02
  Guidelines for Managing Indexes
  

最佳化器和成本計算相關問題

• 是否存在準確且合適的統計資訊（Statistics）？

  CBO 依賴於準確的、最新的和完整的統計資訊來確定一個特定查詢的最佳執行計劃。如果使用 CBO，請確保統計資訊已經收集。如果沒有統計資訊， CBO 將使用預定義的統計資訊，這樣是很可能不會產生良好的計劃或讓應用程式使用索引。
   

• 請注意，CBO 會根據開銷(COST)來決定使用不同的索引。除了基本的表和索引的資訊之外，如果說在某些列上資料分佈是不均勻的，那麼還需要收集這些列的資料的分佈。
  在一般情況下，物件的資料或結構的改變會使以前的統計資訊不準確，因此應該重新收集新的統計資訊。例如，對錶裝載了大量的資料後，需要收集新的統計資訊。安裝新補丁集（Patchset）後,也建議重新收集統計資訊。表訪問最佳效果是統計資訊是在相同版本的資料庫中生成的。

• 一個索引是否與其它的索引有相同的等級或者成本（cost）？

  對於相同開銷（COST）的索引，CBO 會使用多種辦法將不同的索引區分開，如將索引名稱按字母順序排序，完全匹配的索引掃描會選擇更大的NDK(不同鍵值的個數)的索引（不適用於快速全掃描）或選擇葉塊數量較少的索引。

• 索引的選擇度不高？

  • 索引的選擇度不高
    使用它可能不是一個好的選擇...
    列資料不是平均分佈的。
  • CBO 假定列資料不會傾斜，並均勻分佈。如果不是這樣，那麼統計資訊可能沒有反映真實情況，那麼即使某些值的選擇度高，索引也會因為整個列的選擇度不高而不適用索引。 如果是這種情況，那麼應考慮採用直方圖記錄更準確的列的資料分佈或者採用提示（hint）。
  • 統計資訊不準確導致索引看起來選擇性不高而不被選擇。可能的規避方法:
  • 收集更精確的統計值。  
  • 對於資料分佈不均勻的列考慮收集列的統計資訊
  • 使用 hint 或 outline。 

• 在總體成本中，表掃描的成本佔大部分

  通常來說，當使用索引的時候，我們需要再次檢索表本身來找到索引中不存在的欄位的值，這個操作比檢索索引本身的開銷要大很多。由於 optimizer 是基於總體的成本來計算執行計劃，如果透過索引檢索表的成本很大，並且超過了某個閥值，optimizer 就會考慮其他的訪問路徑。
  
  比如
  
  SELECT empno FROM emp WHERE empno=5
  這條語句可能會使用基於列 empno的索引，因為所有需要的資料都存放在索引中所以不需要再對錶做而外的訪問。反之：
  
  SELECT ename FROM emp WHERE empno=5
  這條語句會需要對錶做而外的訪問，因為 ename 欄位沒有存放在索引中。檢索 ename 的開銷會隨著查詢返回記錄條數的增加而變得昂貴。
  
  Optimizer 使用"Clustering Factor"來判斷如果使用 index 的話需要而外對錶做多少次訪問。

• 訪問空索引並不意味著比訪問有值的索引高效。

  Reorganization, Truncation 或刪除操作不一定會影響 SQL 語句執行的成本。需要注意的是刪除操作並不會從物件中真正釋放空間。也就是說，刪除操作不會重置物件的高水位線。Truncate 操作會重置高水位線。空塊的存在會使索引/表掃描的成本比實際應該的成本高。刪掉並重建會重組物件的結構從而有可能會有幫助（也有可能變壞）。這類問題通常在比較兩個有相同資料的不同系統查詢效能時更容易看到。

• 引數設定

  某些引數的設定可能會影響索引的使用。比如在大多數情況下都建議使用 DB_FILE_MULTIBLOCK_READ_COUNT 和 OPTIMIZER_INDEX_COST_ADJ 的預設值。除非某些特定的操作有特定的建議，使用其它值會使索引的成本不現實的減少或變大從而極大的降低查詢的效能。

其它問題

• 是否使用了檢視/子查詢？

  查詢涉及到檢視或者子查詢時可能會被改寫，導致不使用索引（儘管該改寫的目標之一是擴充套件更多的訪問路徑）。這些改寫(rewrite)一般來說都是合併(merging)操作。

• 是否存在遠端表(remote table)？

  通常遠端表不會使用索引。索引在分散式查詢中的使用依賴於被髮送到遠端的查詢。CBO 將評估遠端訪問的成本，並評估比較傳送或者不傳送索引的謂詞到遠端站點的成本。因此，CBO 可以做出有關遠端表上使用索引的更加明智的決定。一個非常有效的方法就是,在遠端建立包含相關謂詞的檢視並強制使用索引，之後在本地查詢中使用這個檢視。

• 是否使用並行執行（PX）?

  在並行執行時索引的採用比在序列執行((serial execution))時更加嚴格。一個快速檢測的方法就是禁用並行，然後檢視該索引是否被使用。

• 是否是包含了子查詢的Update語句？

  在一些情況下，基於成本的考慮，索引沒有被選使用是因為它依賴於一個子查詢返回的值。這種情況下，可以使用提示（hint）來強制使用索引。

• 查詢是否使用了繫結變數？

  CBO 對 like 或範圍謂詞的繫結變數不能產生準確的成本(cost)。這可能會導致索引不被選擇。

• 查詢是否引用了帶有延遲約束的列?

  如果一個表中的某一列上含有延遲約束（比如 NOT NULL）並且這一列上有索引，那麼不管這個約束當前是延遲狀態或是被顯式地設定為立即使用，我們都不會考慮使用這一列上的索引。例如：
  

  CREATE TABLE tdc
  ( x INT CONSTRAINT x_not_null NOT NULL DEFERRABLE INITIALLY DEFERRED RELY,
    y INT CONSTRAINT y_not_null NOT NULL,
    z VARCHAR2(30)
  );
  CREATE INDEX t_idx ON tdc(x);

  SET CONSTRAINTS ALL IMMEDIATE;
  SET AUTOTRACE TRACEONLY EXPLAIN

  SELECT COUNT(1) FROM tdc;       
  Execution Plan
  ----------------------------------------------------------
  Plan hash value: 2532426293
  
  -------------------------------------------------------------------
  | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
  -------------------------------------------------------------------
  |   0 | SELECT STATEMENT   |      |     1 |     2   (0)| 00:00:01 |
  |   1 |  SORT AGGREGATE    |      |     1 |            |          |
  |   2 |   TABLE ACCESS FULL| TDC  |     1 |     2   (0)| 00:00:01 |
  -------------------------------------------------------------------

   

• 索引提示（hint）不工作

  請使用表的別名。   有用的 hints:

  FIRST_ROWS	相當於提示使用索引
  ORDERED	強制查詢的關聯順序（join order of a query）。Oracle 推薦使用 LEADING hint 因為它更好用。
  LEADING	這個 hint 告訴 optimizer 先使用指定的表做連線。它比 ORDERED 更好用。
  INDEX	強制使用索引掃描， 並禁用快速模式（INDEX_FFS）
  INDEX_FFS	強制使用快速索引掃描INDEX_FFS
  INDEX_ASC	強制使用升序的索引範圍掃描（Ascending Index Range Scan）
  INDEX_DESC	強制使用降序的索引範圍掃描（Descending Index Range Scan）

  
  

   

  ---

   

  
  
  2014.08.21 09:49
  share you knowledge with the world.