SQL優化之統計資訊和索引

大部分情況下的sql優化的場景，都是發生在我們想讓我們的sql語句執行的更快一點，這個時候我們往往會採取一些“措施”調整sql的執行路徑以便它們能更快的執行結束。

剛入dba行業，常常聽到導師和我說的一句話“20%的sql優化技能就能優化80%的sql效能問題”。大部分情況下sql優化並不複雜，而很關鍵的一步是我們能不能發現問題，然後才能對症下藥。今天我們先從sql優化中的統計資訊、索引這2個方面來學習瞭解一下sql。

對於cbo模式，統計資訊極為重要，這是影響sql語句執行的一個重要的因素。對於資料變化量大的資料庫，會很容易出現統計資訊過舊的問題即系統記錄的表資訊和實際表的資訊不一致。那麼優化器在選擇最優執行路徑的時候就可能選擇不合適的執行路徑（非最優路徑）。所以我們一線運維常常需要定期的收集統計資訊。那麼問題來了，統計資訊收集依據呢？

既然要收集，那麼很多時候我們可以通過dba_tables檢視中的LAST_ANALYZED欄位看看上一次的收集日期，做個是否有必要做統計收集的預判斷。

sys@RAC>alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
Session altered.    
sys@RAC>select owner,TABLE_NAME,LAST_ANALYZED from dba_tables where table_name='T1';
OWNER                TABLE_NAME           LAST_ANALYZED
-------------------- -------------------- -------------------
TEST                 T1                   2017-10-28 15:08:25

既然有近一年沒有收集過統計資訊，那麼這類表統計資訊不準的嫌疑就會很大。

還有一點就是當我們在看sql執行計劃最後有輸出dynamic sampling used for thisstatement ，那麼說明本次sql執行發生了 動態取樣 。動態取樣是指在段（表，索引，分割槽）沒有分析的情況下，為了使CBO 優化器得到足夠的資訊以保證做出正確的執行計劃而發明的一種技術，可以把它看做系統自動收集統計資訊的一種手段。當物件沒有統計資訊時（即沒有做分析），動態取樣技術可以通過直接從需要分析的物件抽樣資料塊來抽象出CBO需要的統計資訊。

動態取樣需要額外的消耗資料庫資源，所以如果SQL執行頻率很高，這部分資源的消耗可能對sql執行效率的影響也會很大，這一點需要我們注意，在這樣一個環境中，是不宜使用動態取樣的。所以為了防止動態取樣帶來的系統效能的額外消耗，所以我們建議定期對資料庫中的缺失或者過舊的統計資訊表做定期的統計資訊收集。

在上述兩種情況中，我們一般需要收集統計資訊。那麼，如何收集又是另外一個問題。

示例：

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'xxxxx',
                            TABNAME => 'SR_PROBLEM_xxxx',
                            PARTNAME=>'P201802',  
                            ESTIMATE_PERCENT => 1,
                            METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',
                            NO_INVALIDATE=> FALSE,
                            FORCE=>TRUE,
                            CASCADE => TRUE,
                            DEGREE  => 10);
END;
/

引數說明:

·  ownname:要分析表的擁有者

·  tabname:要分析的表名

·  partname:分割槽的名字，只對分割槽表或分割槽索引有用

·  estimate_percent:取樣行的百分比，取值範圍[0.000001,100]

·  method_opt:決定histograms資訊是怎樣被統計的，method_opt的取值如下(預設值為FOR ALL COLUMNS SIZE AUTO)

·  for columns <list> SIZE <N> |REPEAT | AUTO | SKEWONLY:統計指定列的histograms，N的取值範圍[1,254]

·  force:即使表鎖住了也收集統計資訊

·  cascade:是收集索引的資訊，預設為FALSE

·  degree:決定並行度，預設值為null

那麼只有上述兩種情況需要收集統計資訊麼？答案顯然是否定的。而且統計資訊收集的引數豐富，這裡也只是示例，夠日常場景下使用而已。很多情況下還需要根據實際情況動態調整的。

 

下面我們看看索引，在大多數場景下合理的使用索引會使sql語句的執行會有指數級的效能提升，但是索引雖然好，但是千萬不能過度使用，因為維護索引需要消耗一定的資源的。

如果一個資料表中存有海量的資料記錄，當對錶執行指定條件的查詢時，常規的查詢方法會將所有的記錄都讀取出來，然後再把讀取的每一條記錄與查詢條件進行對比，最後返回滿足條件的記錄。這樣進行操作的時間開銷和I/O開銷都很大。對於這種情況，就可以考慮通過建立索引來減小系統開銷。

 

如果要在表中查詢指定的記錄，在沒有索引的情況下，必須遍歷整個表，而有了索引之後，只需要在索引中找到符合查詢條件的索引欄位值，就可以通過儲存在索引中的ROWID快速找到表中對應的記錄。形象的描述就是類似字典目錄的作用，能快速的查詢到相關資料。

現在我們常用的索引技術按照索引資料的儲存方式可以將索引分為B樹索引、點陣圖索引、反向鍵索引和基於函式的索引等；按照索引列的唯一性可以分為唯一索引和非唯一索引；建立唯一性索引還能保證相關列內的資料的唯一性。按照索引列的個數可以分為單列索引和複合索引。

 

建立和規劃一個合理的能提速sql訪問效率的索引需注意以下幾點：

1. 索引應該建立在WHERE子句頻繁引用列表上，如果在大表上頻繁使用某列或某幾個列作為條件執行索引操作，並且檢索行數低於總行數的15%（經驗值），那麼可以考慮在這些列上建立組合索引。

2. 如果經常需要基於某列或者某幾個列排序操作，那麼應該在這些列上建立索引可以加快資料排序速度。

3. 限制表的索引個數。索引主要用於加快查詢速度，但會降低DML操作的速度。索引越多，DML操作速度越慢，尤其會極大地影響INSERT和DELETE操作的速度。因此，規劃索引時，必須仔細權衡查詢和DML的需求。

4. 不適合建立索引的列的特點：很少進行搜尋的列上；列取值比較少的列上；blob型別的列上等。

5. 在建立過程中，千萬不要中斷，因為會導致oracle資料字典內已經包含了該索引的資訊，但是卻實際沒有為該索引分配段，導致重新建立索引，並且刪除索引報錯。

|  作者簡介

李譽軍，沃趣科技資料庫工程師

主要參與公司產品實施、測試、維護以及優化。

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