568      小米6  小米MIX

一個執行計劃異常變更引發的Oracle性能診斷優化

最近有一個OLTP應用使用的Oracle數據庫突然出現性能問題，DBA發現有一些delete語句執行時間驟長，消耗大量係統資源，導致應用響應時間變長積Q。

輔助信息：

1. 應用已經很久未做過更新上線了。

2. 據開發人員反饋，從之前的應用日誌看，未出現處理時間逐步變長的現象。

3. 這是一套RAC+DG的環境，11g的版本。

4. 這次突然出現大量執行時間超長的SQL語句，是一條刪除語句，delete from table where key1=:1 and key2=:2 and ...(省略此案例不會用到的其他條件)，應用正常的處理邏輯中都會使用這條語句，因此並發較高，使用了綁定變量，key1和key2字段不是主鍵，但有索引，存在直方圖。

接下來會通過理論和實驗相結合的方式，了解這個問題所需要涉及的一些Oracle基礎知識，最後再來分析這個案例。

本文目錄：

一、基礎知識介紹

1. 可能造成SQL執行計劃發生改變的一個示例

2. 綁定變量窺探

3. 查看綁定變量值的幾種方法

4. rolling invalidation  

5. 聚簇因子(Clustering Factor)

6. 查詢執行計劃的幾種方法     

7. AWR

8. ASH

9. SQL AWR

10. 直方圖

11. SQL Profile     

二、案例分析

一、基礎知識介紹

1、可能造成SQL執行計劃發生改變的一個示例

什麼情況下可能造成SQL執行計劃發生改變？有很多種情況，這裏拋磚引玉舉一個例子。

實驗： 創建測試表t1，其中name字段設置索引，取值為10000個A和1個B。 

我們看下用查詢條件name=’A’的SQL使用了什麼執行計劃。

再看下使用查詢條件name=’B’的SQL用了什麼執行計劃。

顯而易見，因為取值為A的記錄占據了10000/10001接近100%的比重，即這查詢條件返回了幾乎表的所有數據，使用全表掃描的成本一般會小於使用索引的成本，由於TABLE ACCESS FULL會掃描表高水位線以下的數據塊，且為多塊讀，即一次IO會讀取多個數據塊，具體數據塊數量取決於參數db_file_multiblock_read_count，而INDEX RANGE SCAN則是單塊讀，同時若select字段不是索引字段的話，還需要回表，累積起來，IO次數就會可能很大，因此相比起來，全表掃描的IO可能會遠小於索引掃描。

取值為B的記錄占據了1/10001很小的比重，因此使用索引掃描，直接訪問B*Tree二叉樹，定位到這一條數據的rowid再回表查詢所有select字段的成本要遠小於掃描整張表數據的成本。

為了證明，可以查看這兩條SQL對應的10053事件，如下是name=’A’的trace，可以看出全表掃描的成本值是49.63，索引掃描的成本值是351.26，全表掃描的成本更低一些。 

如下是name=’B’的trace，可以看出全表掃描的成本值是49.40，索引掃描的成本值是2.00，索引掃描的成本值更低一些。

這個場景可以看出，Oracle的CBO模式會根據字段的取值比重調整對應的執行計劃，無論如何，都會選擇成本值最低的一個執行計劃，這也是CBO優於以前RBO的地方，這裏僅用於實驗，因為一般OLTP的應用會使用綁定變量的寫法，不會像上麵這種使用常量值的寫法，11g之前，可能帶來的一些負麵影響就是綁定變量窺探的作用，即對於使用綁定變量窺探的SQL語句，Oracle會根據第一次執行使用的綁定變量值來用於以後的執行，即第一次做硬解析的時候，窺探了變量值，之後的軟解析，不再窺視，換句話說，如果上麵實驗的SQL語句使用了綁定變量，第一次執行時name=’A’，則接下來即使使用name=’B’的SQL語句仍會使用全表掃描，不會選擇索引掃描，vice versa。相關的實驗dbsnake的書中會有很詳細的說明，可以參考。11g之後，有了ACS自適應遊標的新特性，會根據綁定變量值的情況可以重新生成執行計劃，因此這種問題得到了緩解，當然這些都是有代價的，緩解了綁定變量窺探的副作用，相應地可能會導致有很多子遊標，具體的算法可以參考dbsanke的書，這兒我就不班門弄斧了。11g默認綁定變量窺探是開啟的，由以下隱藏參數控製。

綜上所述，針對這場景，如果值的選擇性顯著影響執行計劃，則綁定變量的使用並不可靠，此時選擇字麵值的方式可能會更合適一些，如果值的選擇性幾乎相同，執行計劃不會顯著改變，此時使用綁定變量是最優的選擇，當然前提是OLTP係統。

對於多次執行SQL語句，執行計劃發生變化的情況可能還有很多，例如11g的新特性Cardinality Feedback帶來的一些bug，包含直方圖的字段作為查詢條件但統計信息不準等。

2、綁定變量窺探

首先什麼是綁定變量？ 

一條SQL語句在解析階段，會根據SQL文本對應的哈希值在庫緩存中查找是否有匹配的Parent Cursor，進而找出是否有可重用的解析樹和執行計劃，若沒有則要重新生成一遍，OLTP係統中，高並發的SQL若每次均需要重複執行這些操作，即所謂的硬解析，消耗會比較大，進而影響係統性能，所以就需要使用綁定變量。綁定變量其實就是一些占位符，用於替換SQL文本中具體輸入值，例如以下兩條SQL：

select * from t1 where id = 1;

select * from t1 where id = 2;

在Oracle看來，是兩條完全不同的SQL，即對應SQL文本哈希值不同，因為where條件中一個id是1，一個是2，1和2的ASCII是不同的，可實際上這兩條SQL除了查詢條件不同，其他的文本字符均一致，盡管如此，這種情況下，Oracle還是會重複執行解析的操作，生成各自的遊標。

兩條記錄，說明Oracle認為這兩條SQL是不同。

如果使用綁定變量：

select * from t1 where id = :1;

每次將不同的參數值帶入:1中，語義和上麵兩條相同，但對應哈希值可是1個，換句話說，解析樹和執行計劃是可以重用的。

使用綁定變量除了以上可以避免硬解析的好處之外，還有其自身的缺陷，就是這種純綁定變量的使用適合於綁定變量列值比較均勻分布的情況，如果綁定變量列值有一些非均勻分布的特殊值，就可能會造成非高效的執行計劃被選擇。

如下是測試表： 

其中name列是非唯一索引，NAME是A的有100000條記錄，NAME是B的有1條記錄，值分布是不均勻的，上一篇文章中我們使用如下兩條SQL做實驗。

select * from t1 where name = 'A';

select * from t1 where name = 'B';

其中第一條使用的是全表掃描，第二條使用了索引範圍掃描，過程和原因上篇文章中有敘述，此處就不再贅述。

如上SQL使用的是字麵值或常量值作為檢索條件，接下來我們使用綁定變量的方式來執行SQL，為了更好地說明，此處我們先關閉綁定變量窺探(默認情況下，是開啟的狀態)，他是什麼我們稍後再說。

首先A為條件。

顯示使用了全表掃描。

再以B為條件。

發現仍舊是全表掃描，我們之前知道B值記錄隻有一條，應該使用索引範圍掃描，而且這兩個SQL執行計劃中Rows、Bytes和Cost值完全一致。之所以是這樣，是因為這兒用的未開啟綁定變量窺探情況下的綁定變量，Oracle不知道綁定變量值是什麼，隻能采用常規的計算Cardinality方式，參考dbsnake的書，CBO用來估算Cardinality的公式如下：

Computed Cardinality = Original Cardinality * Selectivity
Selectivity = 1 / NUM_DISTINCT

收集統計信息後，計算如下：

Computed Cardinality = 100001 * 1 / 2

約等於50001。因此無論是A還是B值，CBO認為結果集都是50001，占據一半的表記錄總量，自然會選擇全表掃描，而不是索引掃描。

下麵我們說說綁定變量窺探，是9i引入的一個新特性，其作用就是會查看SQL謂詞的值，以便生成最佳的執行計劃，其受隱藏參數控製，默認為開啟。 

我們在綁定變量窺探開啟的情況下，再次執行上述兩條SQL(區別僅是不用explain plan，使用dbms_xplan.display_cursor可以得到更詳細的信息)，首先A為條件的SQL。
 

這次使用了全表掃描，窺探了綁定變量值是A。

再使用以B為條件的SQL：

仍舊采用了全表掃描，綁定變量窺探值是A，因為隻有第一次硬解析的時候才會窺探綁定變量值，接下來執行都會使用第一次窺探的綁定變量值。B的記錄數隻有1條，1/100001的選擇率，顯然索引範圍掃描更合適。

為了讓SQL重新窺探綁定變量值，我們刷新共享池：

alter system flush shared_pool;

此時清空了所有之前保存在共享池中的信息，包括執行計劃，因此再次執行就會是硬解析，這次我們先使用B為條件。
 

可見窺探了綁定變量值是B，因為可以知道這個綁定變量:x的具體值，根據其值分布特點，選擇了索引範圍掃描。

再用A為查詢條件：

此時仍舊窺探綁定變量值為B，因此還會選擇索引範圍掃描，即使A值應該選擇全表掃描更高效。

總結：

綁定變量窺探會於第一次硬解析的時候，“窺探“綁定變量的值，進而根據該值的信息，輔助選擇更加準確的執行計劃，就像上述示例中第一次執行A為條件的SQL，知道A值占比重接近全表數據量，因此選擇了全表掃描。但若綁定變量列分布不均勻，則綁定變量窺探的副作用會很明顯，第二次以後的每次執行，無論綁定變量列值是什麼，都會僅使用第一次硬解析窺探的參數值，這就有可能選擇錯誤的執行計劃，就像上麵這個實驗中說明的，第二次使用B為條件的SQL，除非再次硬解析，否則這種情況不會改變。

簡而言之，數據分布不均勻的列使用綁定變量，尤其在11g之前，受綁定變量窺探的影響，可能會造成一些特殊值作為檢索條件選擇錯誤的執行計劃。11g的時候則推出了ACS(自適應遊標)，緩解了這個問題。

以上主要介紹了11g之前使用綁定變量和非綁定變量在解析效率方麵的區別，以及綁定變量在綁定變量窺探開啟的情況下副作用的效果。雖然OLTP係統，建議高並發的SQL使用綁定變量，避免硬解析，可不是使用綁定變量就一定都好，尤其是11g之前，要充分了解綁定變量窺探副作用的原因，根據綁定變量列值真實分布情況，才能綜合判斷綁定變量的使用正確。

3、查看綁定變量值的幾種方法

上一章我們了解了，綁定變量實際是一些占位符，可以讓僅查詢條件不同的SQL語句可以重用解析樹和執行計劃，避免硬解析。綁定變量窺探則是第一次執行SQL硬解析時，會窺探使用的綁定變量值，根據該值的分布特征，選擇更合適的執行計劃，副作用就是如果綁定變量列值分布不均勻，由於隻有第一次硬解析才會窺探，所以可能接下來的SQL執行會選擇錯誤的執行計劃。

有時可能我們需要查看某條SQL使用了什麼綁定變量值，導致執行計劃未用我們認為最佳的一種。以下就介紹一些常用的查看綁定變量值的方法。

使用level=4的10046事件，查看生成的trace文件。

可以看出綁定變量值是’Z’。 

首先找出SQL對應的sql_id：

從v$sql_bind_capture可以看出兩個綁定變量占位符以及對應的值。

這裏有一點值得注意的就是，DATATYPE_STRING列的描述是“綁定變量數據類型的文本表示”，開始我認為就是綁定變量字段的數據類型，但實際看來不是，DATATYPE_STRING列隻是來告訴你綁定變量列是字符型，還是數值型。

我們此時換一下綁定變量值，發現v$sql_bind_capture信息未變，dbsnake的書中曾說過當SQL執行硬解析時綁定變量值被捕獲，並可從視圖v$sql_bind_capture中查詢。 

對於執行軟解析/軟軟解析的SQL，默認情況下間隔15分鍾才能被捕獲，為了避免頻繁捕獲綁定變量值帶來的係統性能開銷，而且從常理上認為，既然使用了綁定變量，最佳方式就是值分布均勻，隻需要SQL執行第一次硬解析時窺探一下，後續執行的SQL執行計劃應該比較穩定，因此隻要能比較實時地查看第一次綁定變量值即可。間隔15分鍾受隱藏參數_cursor_bind_capture_interval控製，默認值是900s，15分鍾。 

我們嚐試將捕獲綁定變量的間隔時間調短，該參數不支持session級別修改。

執行alter system級別操作。

等大約一分鍾，此時可以從v$sql_bind_capture查詢剛使用的綁定變量值。

(1) DBA_HIST_SQLBIND視圖包含了v$sql_bind_capture的快照。

因此對應的SQL語句，和v$sql_bind_capture很像。

select name,datatype_string,value_string,datatype from DBA_HIST_SQLBIND where sql_id='...'

(2) 另一個視圖，DBA_HIST_SQLSTAT記錄了SQL統計信息的曆史信息，他是基於一些標準，捕獲來自於V$SQL的統計信息。可以使用如下SQL：

select

snap_id,

dbms_sqltune.extract_bind(bind_data,1).value_string bind1,

dbms_sqltune.extract_bind(bind_data,2).value_string bind2,

dbms_sqltune.extract_bind(bind_data,3).value_string bind3

from dba_hist_sqlstat

where sql_id = '...'

order by snap_id;

其中dbms_sqltune.extract_bind(bind_data,1).value_string取決於SQL中綁定變量的數量。

第一次執行這兩條SQL時，並未有任何結果返回，我猜測可能是這條SQL不符合AWR采集的標準。從MOS中查到這篇文章：《How to Control the Set of Top SQLs Captured During AWR Snapshot Generation (文檔 ID 554831.1)》，用其中的方法修改下AWR采集topnsql參數。

默認值是 

含義是

此時重新執行SQL，默認AWR會一小時采集一次，此時可以手工采集AWR快照。

此時再次查詢DBA_HIST_SQLBIND

再次查詢DBA_HIST_SQLSTAT

綁定變量值可以使用很多方法獲取，這裏隻是列舉了三種最常見的方法，我從網上看到有朋友還有用wrh$_sqlstat、v$sql等視圖查詢的例子，沒有深究，我覺得碰見問題時，可以快速使用一些常用的方法解決問題就可以了，當然時間充裕的話，建議還是多從原理層了解一些，做到觸類旁通則最好。

4、rolling invalidation

有一條SQL，使用了綁定變量，查看V$SQLAREA發現version_count是2

查看V$SQL，發現有兩條記錄，分別對應了0和1兩個child cursor： 

再查看這兩個child cursor對應的執行計劃： 

child cursor：0 

child cursor：1 

發現除了成本代價略有不同，其他訪問路徑完全一致。應用保證使用的相同用戶執行這條SQL語句，綁定變量窺探關閉。問題就來了，為何同一條SQL有兩個child cursor，且執行計劃一致？

再拋一下，通過V$SQL_SHARED_CURSOR視圖可以查看遊標失效的原因，對比這兩個cursor，不同之一就是這個ROLL_INVALID_MISMATCH字段的值，0號cursor值為N，1號cursor值為Y。

另外，REASON字段，0號cursor顯示了內容，1號cursor該字段值為空。

Rolling Invalidate Window Exceeded(3)

這個問題通過Rolling Cursor Invalidations with DBMS_STATS.AUTO_INVALIDATE (文檔 ID 557661.1)這篇文章能夠很好地解釋。

大體意思是在10g之前，使用dbms_stats采集對象統計信息，除非no_invalidate設為TRUE，否則所有緩存在Library Cache中的遊標都會失效，下次執行時需要做硬解析。隱患就是對於一個OLTP係統，會產生一次硬解析風暴，消耗大量的CPU、庫緩存以及共享池latch的爭用，進而影響應用係統的響應時間。如果設置no_invalidate為FALSE，則現有存儲的遊標不會使用更新的對象統計信息，仍使用舊有執行計劃，直到下次硬解析，要麼因為時間太久，導致cursor被刷出，要麼手工執行flush刷新了共享池，這兩種情況下會重新執行硬解析，根據更新的對象統計信息，生成更新的執行計劃。這麼做其實還是有可能出現硬解析風暴，特別是OLTP係統，高並發時候，有SQL語句頻繁訪問。

使用dbms_stats.gather_XXX_stats的時候，有個參數no_invalidate：

默認是AUTO_INVALIDATE，這表示是由Oracle來決定什麼時候讓依賴的遊標失效。

10g之後，如果采集對象統計信息使用的no_invalidate參數是auto_invalidate，則Oracle會采用如下操作，來緩解可能的硬解析風暴。 

1. 執行dbms_stats，所有依賴於這個已分析對象的緩存cursor遊標會被標記為rolling invalidation，並且記錄此時刻是T0。

2. 下次某個session需要解析這個標記為rolling invalidation的cursor遊標時，會設置一個時間戳，其取值為_optimizer_invalidation_period定義的最大值範圍內的一個隨機數。之所以是隨機數，就是為了分散這些 invalidation的遊標，防止出現硬解析風暴。參數_optimizer_invalidation_period默認值是18000秒，5小時。記錄這次解析時間為T1，時間戳值為Tmax。但此時，仍是重用了已有遊標，不會做硬解析，不會使用更新的統計信息來生成一個新的執行計劃。

3. 接下來這個遊標(標記了rolling invalidation和時間戳)的每次使用時，都會判斷當前時刻T2是否超過了時間戳Tmax。如果未超過，則仍使用已存在的cursor。如果Tmax已經超過了，則會讓此遊標失效，創建一個新的版本(一個新的child cursor子遊標)，使用更新的執行計劃，並且新的子遊標會標記V$SQL_SHARED_CURSOR中ROLL_INVALID_MISMATCH的值。

這些和我上麵碰見的情況基本一致。

MOS是附帶了一個實驗，可以根據實驗來體會下這種情況。 

1.為了容易觀察，設置_optimizer_invalidation_period為1分鍾。 

2.創建測試表，並采集統計信息。

3.執行一次目標SQL，並查看V$SQL_SHARED_CURSOR信息。 

此時查看這條SQL的解析和執行次數都是1。 

4.再執行一次目標SQL，select count(*) from X;，查看這條SQL的解析和執行次數是2。 

有人曾說過，11g中未必會按照_optimizer_invalidation_period參數定義的時間產生新的子遊標，我上麵用的環境是11g，確實如此，等了2分鍾，執行目標SQL，仍隻有一個子遊標。這樣的好處有人也說了，就是更加的隨機，因為如果嚴格按照參數設置的時間失效，則有可能頻繁使用的遊標會在超時後某一時刻集中做硬解析，還是會有資源的影響，隻是時間推遲了，因此如果是在超時值基礎上又有隨機分布，則可能會將硬解析的影響降到最低。

又等了一段時間，再查詢V$SQL。 

確實產生了兩個子遊標，這裏需要注意FIRST_LOAD_TIME的時間是一樣的，因為他是parent父遊標的創建時間，顯然這兩個子遊標肯定是對應同一個父遊標，不同的就是LAST_LOAD_TIME，這是子遊標的使用時間。

再看看V$SQL_SHARED_CURSOR。 

兩個子遊標信息，隻有一個R項值有差別，R是ROLL_INVALID_MISMATCH，0號子遊標是N，1號子遊標是Y，看看官方文檔對這個字段的說明。

表示的就是標記為rolling invalidation的遊標，已經是超過了時間窗口，此時0號子遊標已經過期，1號子遊標使用最新的統計信息，來生成最新的執行計劃。

這就解釋了為何同一條SQL，執行計劃一致，但卻有兩個子遊標的情況。

MOS中還描述了一些遊標使用的場景： 

1. 如果一個遊標被標記為rolling invalidation，但是再不會做解析，則這個遊標不會失效，最終還是可能根據LRU被刷出共享池。

2. 如果一個遊標被標記為rolling invalidation，後麵隻會解析一次，那麼這個遊標依然不會失效(僅僅使用時間戳標記)，最終還是可能根據LRU被刷出共享池。

3. 頻繁使用的遊標，在超過時間戳Tmax值後，下次解析時就會被置為失效。 

很明顯，上麵的這些方法是有效的，因為失效標記僅僅適用於這些頻繁重用的遊標，對於其他場景的遊標可以忽略，未有影響。

5、聚簇因子(Clustering Factor)

聚簇因子，Clustering Factor，聽著名字就很高大上，很學術。題外話，記得幾年前的一次內部分享，dbsnake介紹一案例的時候，曾問過在場同事其中涉及的一個知識點是什麼，如果知道就意味著你對索引的了解很深入，可惜當時沒人反應，作為小白的我自然也不知道，當時的這個知識點就是聚簇因子，下來我仔細了解了下，確實這些東東，如果經常用到自然脫口而出，可惜這種機會隻能靠自己。

我們先看下官方對CF介紹。

索引聚簇因子衡量的是索引字段存儲順序和表中數據存儲順序的符合程度。兩者存儲順序越接近，聚簇因子值就越小。

聚簇因子的用處在於可以粗略估算根據索引回表需要的IO數量。

• 如果CF值高，Oracle執行一個相對較大的索引範圍掃描時就會需要相對多的IO數量。這些索引項指向的是隨機的表塊，數據庫為了根據索引檢索表中數據，不得不一次又一次地讀取相同的數據塊。

• 如果CF值低，Oracle執行一個相對較大的索引範圍掃描時就會需要相對少的IO數量。這些索引鍵值可能指向相同的數據塊，數據庫不需要重複讀取同一個數據塊。

文中還舉了一個例子，如下表EMPLOYEES中數據是按照last name的字母順序存儲的。

如果last name是索引字段，可以看出索引的存儲順序(blockXrowY可以抽象地看作rowid)，即連續的幾個索引鍵值指向的是同一個數據塊。

如果此時id是索引字段，可以看出連續的幾個索引鍵值對應的可能是不同的數據塊，而且有可能幾個順序間隔不多的鍵值指向的是同一個數據塊，如果這是一個龐大的索引和表，buffer cache再小一些，使用id字段作為檢索條件的SQL並發再高一些，很可能之前剛從數據文件中加載至buffer cache，馬上就會根據LRU算法age out，但一會又再次加載至buffer cache，反反複複，各種latch等的資源爭用就會累積起來，進而可能對係統性能造成影響。 

DBA/ALL/USER_INDEXES視圖有一列CLUSTERING_FACTOR，表明該索引的聚簇因子值。

摘自dbsnake書中對於CF值計算算法的敘述：

1. CF初始值是1。

2. Oracle首先定為至目標索引最左邊的葉子塊。

3. 從最左邊的葉子塊的第一個索引鍵值所在的索引行開始順序掃描，Oracle比較當前索引行的roid和他之前相鄰的索引行的rowid，若這兩rowid並不是指向同一個表塊，則將聚簇因子值遞增1，如果指向同一個rowid，則不改變當前聚簇因子值。比對rowid的時候並不需要回表訪問相應的表塊。(注：原因就是根據rowid的值是可以計算出block信息) 
   直到順序掃描完目標索引所有葉子塊的所有索引行。 

4. 掃描操作完成後，聚簇因子當前值就是會被存儲在數據字典中，就是上麵視圖中CLUSTERING FACTOR列。

5. 說了這麼多，CF有什麼實際意義？個人理解，CBO模式的優化器會綜合考慮各種因素來判斷一條SQL不同執行計劃對應的成本值，選擇成本值最低的一個執行計劃，CF實際影響的是根據索引回表需要的IO數量，自然也在其考慮的範圍之內，因此CF值的高低有時會影響CBO對不同執行計劃的選擇。

實驗： 

測試表有兩列NUMBER類型的字段，其中id1是按照順序存儲，id2是無序存儲，id1和id2各有一個非唯一索引。

DBA/ALL/USER_INDEXES中有一注釋：

“Column names followed by an asterisk are populated only if you collect statistics on the index using the DBMS_STATS package.“

即使用DBMS_STATS包收集索引統計信息的時候，CLUSTERING_FACTOR才會有值。

從dba_indexes中可以看出id1對應的索引CF隻有204，id2對應的索引CF有99481，表的數據量是100000，就是說這個id2中所有葉子塊的索引行排列順序幾乎和表中數據存儲的順序完全不一致。

使用id1 between 1 and 1000作為檢索條件，可以看出使用了id1索引範圍掃描。

使用id2 between 1 and 1000作為檢索條件，這次卻選擇了全表掃描，沒有選擇id2索引掃描。

如果我們強製使用id2索引，無論從Cost，還是consistent gets，都要高於全表掃描。

究其原因，還可以參考dbsnake書中對於索引範圍掃描的算法。

IRS Cost = I/O Cost + CPU Cost
I/O Cost = Index Access Cost + Table Access I/O Cost
Index Access Cost = BLEVEL + CEIL(#LEAF_BLOCKS * IX_SEL)
Table Access I/O Cost = CEIL(CLUSTERING_FACTOR * IX_SEL_WITH_FILTERS)

我們可以檢索視圖發現，id1和id2的索引LEAF_BLOCKS等列值均相等，隻有CLUSTERING_FACTOR不同，進而可以粗略認為索引範圍掃描的成本和聚簇因子的大小成正比。

進而我們可以這麼嚐試，人為將id2的索引聚簇因子值改為200。

可以看出此時選擇了id2的索引範圍掃描。

但相應consistent gets值依舊很大，我猜原因就是計算執行計劃成本值，CBO會根據相關統計信息值來計算，我們人為設置了索引的聚簇因子為一個很小的值，計算出來的成本值小於全表掃描，因此選擇了使用索引的執行計劃，但實際回表等操作需要消耗的資源其實並沒有少。

如果要消除聚簇因子的影響，隻能對表中數據按照目標索引鍵值的順序重新存儲，例如，create table t1_cf_0 as select * from t1_cf order by id2;

但這麼做帶來的問題就是，可能id2的聚簇因子下降了，相對id1的聚簇因子上升了，有些顧此失彼的意思。因此根據實際業務需求，選擇正確的表數據組織形式，或者隻能通過其他優化方式，來減小聚簇因子的影響。

之前曾發過一個如何讓CF值小的討論帖，有興趣的朋友可以參考， 
https://www.itpub.net/thread-1910003-1-1.html

總結： 

1. 聚簇因子表示索引鍵值的排列順序和表中數據排列順序的相似程度。

2. 可以粗略認為索引範圍掃描的成本，和聚簇因子的大小成正比，從索引範圍掃描的計算方法可以推出這個結論。

3. 是否需要重新組織表中數據存儲順序，以降低某一個索引的聚簇因子值，需要結合實際需求來判斷，因為若表中存在多個索引，很可能造成顧此失彼的情況。原文發布時間為：2017-05-12

   本文來自雲棲社區合作夥伴DBAplus

最後更新：2017-05-17 14:02:30

  上一篇：  阿裏新生的麵試經，與老人分享的職業進階攻略及規劃

  下一篇：  MySQL索引設計背後的數據結構及算法詳解