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數據字典內已經包含了該索引的信息，但是卻實際沒有為該索引分配段，導致重新建立索引，并且刪除索引報錯。

|  作者簡介

李譽軍，沃趣科技數據庫工程師

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