案例2　糟糕的結(jié)構(gòu)設(shè)計帶來的問題

1.案例說明

這是某公司后臺的ERP系統(tǒng)，系統(tǒng)已經(jīng)上線運行了10多年。隨著時間的推移，累積的數(shù)據(jù)量越來越大。隨著公司業(yè)務(wù)量的不斷增加，數(shù)據(jù)庫系統(tǒng)運行緩慢的問題日益凸顯。為提高運行效率，公司計劃有針對性地對部分大表進行數(shù)據(jù)清理。在DBA對某個大表進行清理時出現(xiàn)了問題。這個表本身有數(shù)百GB，按照指定的清理規(guī)則只需要根據(jù)主鍵字段范圍（運算符為>=）選擇出一定比例（不超過10%）的數(shù)據(jù)進行清理即可。但在實際使用中發(fā)現(xiàn)，該SQL是全表掃描，執(zhí)行時間大大超出預(yù)期時間。DBA嘗試使用強制指定索引方式清理數(shù)據(jù)，依然無效，整個SQL語句的執(zhí)行效率達不到要求。為了避免影響正常業(yè)務(wù)運行，不得不將此次清理工作放在半夜進行，還需要協(xié)調(diào)庫房等諸多單位進行配合，嚴重影響正常業(yè)務(wù)運行。

為了盡量減少對業(yè)務(wù)的影響，DBA求助筆者幫助協(xié)同分析。這套ERP系統(tǒng)是由第三方公司開發(fā)的，歷史很久遠，相關(guān)的數(shù)據(jù)字典等信息都已經(jīng)找不到了，只能從純數(shù)據(jù)庫的角度進行分析。這是一個普通表（非分區(qū)表），按照主鍵字段的范圍查詢一批記錄并進行清理。按照正常理解，執(zhí)行索引范圍掃描應(yīng)該是效率較高的一種處理方式，但實際情況都是全表掃描。進一步分析發(fā)現(xiàn)，該表的主鍵是沒有業(yè)務(wù)含義的，僅僅是自增長的數(shù)據(jù)，其來源是一個序列。但奇怪的是，這個主鍵字段的類型是變長文本類型，而不是通常的數(shù)字類型。當(dāng)初定義該字段類型的依據(jù)，現(xiàn)在已經(jīng)無從考證，但實驗表明正是這個字段的類型“異常”，導(dǎo)致了錯誤的執(zhí)行路徑。

下面通過一個實驗重現(xiàn)這個問題。

（1）數(shù)據(jù)準(zhǔn)備

兩個表的數(shù)據(jù)類型相似（只是ID字段類型不同），各插入了320萬數(shù)據(jù)，ID字段范圍為1~3200000。

create table t1 as select * from dba_objects where 1=0；
alter table t1 add id int primary key；
create table t2 as select * from dba_objects where 1=0；
alter table t2 add id varchar2（10） primary key；
insert into t1 
select 'test'，'test'，'test'，rownum，rownum，'test'，sysdate，sysdate，'test'，'test'，''，''，''，rownum
from dual 
connect by rownum<=3200000；
insert into t2 
select 'test'，'test'，'test'，rownum，rownum，'test'，sysdate，sysdate，'test'，'test'，''，''，''，rownum
from dual 
connect by rownum<=3200000；
commit；
execdbms_stats.gather_table_stats（ownname => 'hf'，tabname => 't1'，cascade =>true，estimate_percent => 100）；
execdbms_stats.gather_table_stats（ownname => 'hf'，tabname => 't2'，cascade =>true，estimate_percent => 100）；

（2）模擬場景

相關(guān)代碼如下：

select * from t1 where id>= 3199990；
11 rows selected.
--------------------------------------------------------------------------------
| Id | Operation                | Name       |Rows |Bytes|Cost （%CPU）|  Time    |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT         |            | 11  | 693 |   4  （0） | 00：00：01 |
|  1 | TABLE ACCESS BY INDEX ROWID| T1         | 11  | 693 |   4  （0） | 00：00：01 |
|* 2 | INDEX RANGE SCAN         |SYS_C0025294| 11  |     |   3  （0） | 00：00：01 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
6  consistent gets
0  physical reads

對于普通的采用數(shù)值類型的字段，范圍查詢就是正常的索引范圍掃描，執(zhí)行效率很高。

select * from t2 where id>= '3199990'；
755565 rows selected.
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost （%CPU）| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2417K|   149M|  8927   （2）| 00：01：48 |
|*  1 |  TABLE ACCESS FULL| T2   |  2417K|   149M|  8927   （2）| 00：01：48 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
82568  consistent gets
0  physical reads

對于文本類型字段的表，范圍查詢就是對應(yīng)的全表掃描，效率較低是顯而易見的。

（3）分析結(jié)論

• 字符類型在索引中是“亂序”的，這是因為字符類型的排序方式與我們的預(yù)期不同。從“select*from t2 where id>='3199990'”執(zhí)行返回755565條記錄可見，不是直觀上的10條記錄。這也是當(dāng)初在做表設(shè)計時，開發(fā)人員沒有注意的問題。
• 字符類型還導(dǎo)致了聚簇因子很大，原因是插入順序與排序順序不同。詳細點說，就是按照數(shù)字類型插入（1..3200000），按字符類型（'1'...'32000000'）t排序。

selecttable_name，index_name，leaf_blocks，num_rows，clustering_factor
fromuser_indexes
wheretable_name in （'T1'，'T2'）；
TABLE_NAME         INDEX_NAME      LEAF_BLOCKS   NUM_ROWS    CLUSTERING_FACTOR
-------------- -------------- ---------------- ---------- ---------------------
T1               SYS_C0025294             6275    3200000                 31520
T2               SYS_C0025295            13271    3200000                632615

• 在對字符類型使用大于運算符時，會導(dǎo)致優(yōu)化器認為需要掃描索引大部分數(shù)據(jù)且聚簇因子很大，最終導(dǎo)致棄用索引掃描而改用全表掃描方式。

（4）解決方法

具體的解決方法如下：

select * from t2 where id between '3199990' and '3200000'；
--------------------------------------------------------------------------------
| Id  | Operation                 | Name         |Rows|Bytes |Cost（%CPU）| Time   |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |             |   6|  390 |   5 （0）|00：00：01|
|   1 |  TABLE ACCESS BY INDEX ROWID| T2           |   6|  390 |   5 （0）|00：00：01|
|*  2 |   INDEX RANGE SCAN        | SYS_C0025295 |   6|      |   3 （0）|00：00：01|
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
13  consistent gets
0  physical reads

將SQL語句由開放區(qū)間掃描（>=），修改為封閉區(qū)間（between xxx and max_value）。使得數(shù)據(jù)在索引局部順序是“對的”。如果采用這種方式仍然走索引掃描，還可以進一步細化分段或者采用“逐條提取+批綁定”的方法。

2.給我們的啟示

這是一個典型的由不好的數(shù)據(jù)類型帶來的執(zhí)行計劃異常的例子。它給我們帶來如下啟示：

• 糟糕的數(shù)據(jù)結(jié)構(gòu)設(shè)計往往是致命的，后期的優(yōu)化只是補救措施。如果從源頭上加以杜絕，這才是優(yōu)化的根本。
• 在設(shè)計初期能引入數(shù)據(jù)庫審核，可以起到很好的作用。