非空+預設值——一種選擇方案思路

 

同事和我討論一個問題：非關鍵業務欄位（比如狀態欄位）如果從業務上允許為空，那麼在資料庫裡面，是否有必要透過一個預設值將這個欄位作為非空欄位。筆者將個人的一點拙見在這裡和大家分享。

 

舉一個例子，有一個資料表T，包括兩個欄位tkttype和tktsts。其中，tkttype表示資料型別資訊，非空且型別為varchar2(1)。Tktsts表示特定型別票證的狀態資訊，只有在tkttype為特定值“P/W”的時候，才賦予有業務含義的狀態取值。環境如下：

 

 

SQL> desc t;

Name    Type        Nullable Default Comments

------- ----------- -------- ------- --------

ID      NUMBER      Y                        

TKTTYPE VARCHAR2(1)                          

TKTSTS  VARCHAR2(1) Y                        

 

 

我們的問題是，有沒有必要將tktsts欄位設定為非空，並且給原來允許為空的tktsts欄位設定預設值。這樣做的好處是可以透過在該列上加索引來提高效能，如果列值為空的話，查詢路徑就不會走索引。

 

這樣做的確是有一定道理。如果在包括空值null的列上建立索引（非複合索引），由於空值比較排序的原因，空值對應行是不會進入索引樹結構的。進而在執行計劃中，涉及到空列取值的檢索是不可能出現索引路徑的。這也就是為什麼我們經常說“is null/is not null”條件是不走索引。

 

 

在筆者看來，這個問題本身是一個很複雜的決策問題，涉及到很多需要考量的因素。盲目規定說這種情況欄位應該加或者不應該加預設值，是比較武斷的。簡單的整理，我們可以發現這個問題實際上是由兩個問題決策組成的。首先，是該列是否加索引來提升檢索效率。其次就是新增索引時，是否需要考慮進列空值行的資料。針對不同的場景，兩個問題的回答不同，進行決策判斷的結果也必然是不同的。

 

具體可以考慮下面幾個方面

 

 

1、是否有必要進行索引最佳化

 

“加不加索引，是一個問題”。在筆者過去的文章中，一直在強調索引並非一份免費的午餐，一旦新增入索引，之後資料庫都會為維護索引消耗CPU和儲存資源。我們新增一個索引專案，都要儘可能實現“高回報”的效能收益。首先，我們需要確定的問題是，是否有需要在該列上進行加索引來提升效能。進一步說就是，我們加入的索引，究竟能不能被應用使用到。我們付出了成本，就一定要有最大化的收益。

 

 

確定索引列是一個全域性考慮的問題。由於應用的複雜，可能出現所有列均出現在不同功能用例搜尋條件中的情況。這種時候，我們必然不能將所有列都做成索引列，就需要我們從用例重要程度和系統價值層面對用例進行排序。使用者最關注、最常用，系統核心模組的最佳化級別排序要高於其他模組。對於一些不常用、非核心模組的用例，最佳化順序可以適當後移或者甚至不進行最佳化。

 

 

舉個例子，目前系統中資料表A兩個資料列A1和A2，分別對應兩個功能模組檢索的要求。如果從最佳化角度看，兩個列都需要建立索引來滿足兩個模組的要求。但是，由於系統資源等條件的限制，只能建立一個索引。這種時候，就需要評估功能模組的優先順序，有限構建高優先順序別使用者的索引。一個一年只執行一次的查詢操作，慢幾秒鐘給使用者體驗帶來的影響是有限甚至是可以忽略的。但是一個每天執行的大作業慢幾個量級卻是絕不可以忍受的。

 

 

回到本文中的問題。我們首選要確認該狀態欄位在系統功能模組中是否會作為檢索條件，出現在where條件後。where條件是我們藉助索引的一個重要場景（注意：但不是唯一場景）。如果對高優先順序核心用例來說，該欄位根本就不會作為檢索條件出現，通常我們就不會考慮為該列建立專門的索引。進而也就不存在透過default值來最佳化索引的問題了。

 

 

那麼，是不是該狀態列出現在where條件，而且出現的用例是核心用例，就一定需要建立索引呢？我們說也是未必，要根據具體情況來說。一個資料表在一個查詢檢索（或者一個檢索子句）中，無論有多少索引方案，最終執行路徑下只有一個索引會進入路徑。

 

 

比如：select * from t where id=3 and tkttype=’P’; 此時如果在id和stktype兩個欄位上均有索引。對這個查詢，Oracle最終的執行計劃中最多隻有一個索引進入。當然，在CBO時代，這個選擇是依據兩個方案的Cost成本來定。

 

 

基於這樣的情況，回到我們的問題。如果where條件中出現我們關注的狀態列tktsts和其他的欄位，而且這個用例場景也是需要進行最佳化的核心用例，這時我們就需要關注各個條件列的查詢方式和列選擇性。所謂的列選擇性就是各個where條件列中，那個列資料的選擇性更好。這樣的資料列建立索引後，路徑執行效率相對較高，出現全表掃描的情況較少。這個時候，才考慮建立確定索引列。

 

 

綜合上述的部分，我們可以歸納出第一階段解決的原則：我們對關鍵用例的關鍵列才考慮加索引最佳化的問題。如果分析後結論是狀態列不需要索引最佳化，那麼預設值、非空問題也就不用考慮了。

 

 

2、選擇率、取值規律確定索引構成

 

如果透過了第一步的決策，確定我們需要對狀態列進行加索引操作。接下來就要確定索引型別和結構，包括索引中是否要包括現在列值為空的資料行。

 

 

在這個環節，我們要關注的方向還要包括資料取值的選擇特性和訪問方式。資料列取值通常是有規律的。正常情況下，資料列最大、最小取值，各個取值範圍分佈是有規律可循的。這種規律性，在Oracle下被歸納為統計量資訊。Oracle透過定期或者不定期的統計量收集，提供給CBO最佳化器進行執行路徑生成的依據。

 

 

也就是說，同樣一個SQL結構，如select * from t where tkttype=’XX’；如果資料列tkttype資料分佈偏移嚴重，對於不同的取值，Oracle會制定出不同的執行計劃。可能是走索引，也可能是全表掃描。

 

 

此外，對有空值列建索引，還有一個額外的特性，就是空值行是不會進入索引的。那麼，索引葉節點所涉及到的行都是索引列非空的資料行。那麼，我們對允許為空列構建的索引樹，就是一個相對較小的索引樹，對應的空間消耗和檢索成本就相對較小。

 

 

如果索引列值中，null值佔到了相當比例，此時我們就不建議對索引列加預設值並且設定為非空。首先，如果設定預設值，null值對應預設值在索引列中比例高的現實沒有改變。那麼在生成執行計劃的時候，生成全表掃描的機率較高。這和使用is null的情況沒有區別。同時，使用預設值之後，所有的資料行都要進入索引構建過程，成為葉子節點。這樣，索引樹的體積也就較大，大部分葉節點對應那些不會被查詢到的預設值資料。即使要進行索引掃描，真正需要的值消耗成本也就增加。所以，如果null值比例較高，而且又要在該列加索引的話，不要加預設值為好。

 

 

另一方面，如果null的比例較小或者相對平均。對is null/is not null的檢索需要優勢很強烈，這時候是可以考慮預設值選擇的。

 

 

綜合這部分內容，可以歸納為對資料分佈的分析研究。如果相對比較平衡或者null值較少，才可以最後考慮使用預設值方案。否則，使用預設值方案就需要慎重考慮。

 

 

 

進入CBO時代後，影響Oracle最佳化器的因素越來越多。我們在享受到靈活科學的CBO工作產品的同時，也面臨著選擇的多樣。道無常法，就事論事可能是我們解決實際問題的通用策略。任何方法、秘籍和技巧，都存在適用的特殊場合和背景，理解為什麼這麼做才是我們應該掌握的知識要素。