刻意使用索引過濾述詞 • SQL 效能解密

索引篩選述詞常常代表索引用得不好——通常源自串接索引的欄位順序錯誤。但它也可以基於正當理由被刻意使用：不是為了改善範圍掃描效能，而是為了把連續存取的資料聚在一起。

適用對象：無法作為存取述詞的條件#

SELECT first_name, last_name, subsidiary_id, phone_number
  FROM employees
 WHERE subsidiary_id = ?
   AND UPPER(last_name) LIKE '%INA%';

記得帶前導萬用字元的 LIKE 運算式無法使用索引樹。因此無論索引 LAST_NAME 還是 UPPER(last_name)，都無法縮小掃描範圍——這個條件本身並不是好的索引對象。

但 SUBSIDIARY_ID 上的條件很適合索引，而且我們甚至不必新增索引——它已經是主鍵索引的前導欄位：

--------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |   17 |  230 |
|*1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES   |   17 |  230 |
|*2 |   INDEX RANGE SCAN          | EMPLOYEE_PK |  333 |    2 |
--------------------------------------------------------------

Predicate Information:
   1 - filter(UPPER("LAST_NAME") LIKE '%INA%')
   2 - access("SUBSIDIARY_ID"=TO_NUMBER(:A))

從 INDEX RANGE SCAN 到後續的 TABLE ACCESS BY INDEX ROWID，成本值暴增了一百倍。換言之：資料表存取才是主要工作量。這是常見的模式，本身不算問題，但它確實是這段查詢執行時間的最大貢獻者。

關鍵在於列的實體分布#

資料表存取未必是瓶頸——如果被存取的列都存在同一個資料表區塊中，資料庫一次讀取就能取回全部。反之，若同樣的列散落在許多不同區塊，資料表存取就會成為嚴重的效能問題。

效能取決於被存取列的實體分布——也就是列的叢集程度。

它間接衡量「兩個相鄰的索引條目指向同一個資料表區塊」的機率。最佳化工具在計算 TABLE ACCESS BY INDEX ROWID 的成本時會把它納入考量。

理論上可以透過重新排列資料表中的列、使其對應索引順序來改善效能。但這個方法很少可行：

資料表的列只能有一種存放順序，你只能為一個索引最佳化資料表。
即使選定了那個索引，多數資料庫也只提供極其簡陋的工具。

所謂的列排序（row sequencing）說到底是相當不切實際的做法。

正解：用索引來叢集#

這正是索引的第二威力登場之處：你可以在索引中加入許多欄位，它們就會自動以明確定義的順序存放。索引因而成為叢集資料既強大又簡單的工具。

套用到上面的查詢，就是把索引擴充到涵蓋 where 子句的所有欄位——即使它們不會縮小掃描範圍：

CREATE INDEX empsubupnam ON employees
       (subsidiary_id, UPPER(last_name));

SUBSIDIARY_ID 是第一欄，可作為存取述詞。
UPPER(last_name) 以索引篩選述詞的形式涵蓋 LIKE 篩選。

索引大寫形式可在執行時省下一些 CPU 週期，但直接索引 LAST_NAME 同樣可行（下一節會說明為何那樣更好）。

--------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |   17 |   20 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES   |   17 |   20 |
|*2 |   INDEX RANGE SCAN          | EMPSUBUPNAM |   17 |    3 |
--------------------------------------------------------------

Predicate Information:
   2 - access("SUBSIDIARY_ID"=TO_NUMBER(:A))
       filter(UPPER("LAST_NAME") LIKE '%INA%')

新計畫的操作完全相同，成本卻大幅下降。述詞資訊顯示 LIKE 篩選已在 INDEX RANGE SCAN 期間套用，不符合的列立刻被丟棄；資料表存取不再有任何篩選述詞——它不會再載入不滿足 where 子句的列。

差異在 Rows 欄位一目了然：

舊計畫：索引掃描交出 333 列，資料庫必須全部從表中載入才能套用 LIKE 篩選，最終縮到 17 列。
新計畫：索引存取一開始就不交出那些列，TABLE ACCESS BY INDEX ROWID 只需執行 17 次。

注意 INDEX RANGE SCAN 的成本從 2 升到 3——多一個欄位讓索引變大了。考量到整體的效能收益，這是可以接受的折衷。

別誤讀成「把 where 子句所有欄位都索引」#

這個簡單例子看似印證了「把 where 子句中每個欄位都索引起來」的常識。但那套「常識」忽略了欄位順序的關鍵性——順序決定哪些條件能作為存取述詞，對效能影響巨大。欄位順序的決定絕不該交給偶然。

索引大小也隨欄位數成長——尤其加入文字欄位時。索引變大效能當然不會變好（雖然對數擴展性大幅限制了衝擊）。
絕不要把 where 子句提到的所有欄位都塞進索引，而應該刻意使用索引篩選述詞，好在較早的執行步驟就縮減資料量。