不等式運算子如 <、> 與 between，和前面說明的等值運算子一樣可以使用索引。連 LIKE 篩選在特定條件下，也能像範圍條件那樣用上索引。

使用這些運算會限縮多欄索引中欄位順序的選擇。這個限制甚至可能排除所有最佳索引方案——有些查詢根本無法定義出「正確」的欄位順序。

大於、小於與 BETWEEN#

INDEX RANGE SCAN 最大的效能風險是葉節點的走訪。

檢驗方法是問自己：這次索引掃描從哪裡開始、到哪裡結束？

當 SQL 敘述明確寫出起訖條件時，答案很容易：

SELECT first_name, last_name, date_of_birth
  FROM employees
 WHERE date_of_birth >= TO_DATE(?, 'YYYY-MM-DD')
   AND date_of_birth <= TO_DATE(?, 'YYYY-MM-DD')

DATE_OF_BIRTH 上的索引只會掃描指定範圍：從第一個日期開始、第二個日期結束，範圍已無法再縮小。

加入第二個欄位後，順序決定一切#

SELECT first_name, last_name, date_of_birth
  FROM employees
 WHERE date_of_birth >= TO_DATE(?, 'YYYY-MM-DD')
   AND date_of_birth <= TO_DATE(?, 'YYYY-MM-DD')
   AND subsidiary_id = ?

理想索引當然要涵蓋兩個欄位，但順序該怎麼排？以「找出子公司 27 中、生於 1971 年 1 月 1 日至 1 月 9 日之間的所有員工」為例。

方案一：(DATE_OF_BIRTH, SUBSIDIARY_ID)

索引先按出生日期排序，只有在兩位員工同一天出生時，SUBSIDIARY_ID 才決定順序。但查詢涵蓋的是一段日期範圍，因此 SUBSIDIARY_ID 的排序在樹走訪期間毫無用處——分支節點中根本沒有子公司 27 的條目（儘管葉節點裡有）。結果：只有 DATE_OF_BIRTH 這個條件限制了掃描範圍，從符合日期範圍的第一筆掃到最後一筆。

方案二：(SUBSIDIARY_ID, DATE_OF_BIRTH)

差別在於等值運算子把第一個索引欄限縮為單一值。在該值（子公司 27）的範圍內，索引依第二欄（出生日期）排序，因此連第一個葉節點都不必造訪——分支節點已經指出，第一個葉節點中沒有子公司 27 裡生於 1969 年 6 月 25 日之後的員工。樹走訪直接抵達第二個葉節點，且所有 where 條件都限制了掃描範圍，掃描在同一個葉節點就結束。

實際效能差距取決於資料與搜尋條件。若 DATE_OF_BIRTH 篩選本身就極具選擇性，差距可能微不足道；日期範圍越大，差距越大。

順便推翻「最具選擇性的欄位放最左邊」迷思#

看看上面的圖：若只考慮第一欄的選擇性，兩個條件都匹配 13 筆記錄——不論只用 DATE_OF_BIRTH 篩選還是只用 SUBSIDIARY_ID 篩選都一樣。

存取述詞 vs. 篩選述詞#

要優化效能，關鍵是知道被掃描的索引範圍。多數資料庫的執行計畫都看得出來——只要知道該找什麼。以下 Oracle 執行計畫明確指出 EMP_TEST 索引以 DATE_OF_BIRTH 開頭：

--------------------------------------------------------------
|Id | Operation                    | Name      | Rows | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT             |           |    1 |    4 |
|*1 | FILTER                       |           |      |      |
| 2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES |    1 |    4 |
|*3 |    INDEX RANGE SCAN          | EMP_TEST  |    2 |    2 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:END_DT >= :START_DT)
3 - access(DATE_OF_BIRTH >= :START_DT
       AND DATE_OF_BIRTH <= :END_DT)
    filter(SUBSIDIARY_ID = :SUBS_ID)

INDEX RANGE SCAN 的述詞資訊給出關鍵線索：它把 where 條件標示為存取述詞（access predicate）或篩選述詞（filter predicate）——這就是資料庫在告訴我們每個條件被怎麼使用。此處只有 DATE_OF_BIRTH 被列為存取述詞，SUBSIDIARY_ID 僅作為篩選。

• 存取述詞是索引查找的起訖條件，定義了被掃描的索引範圍。
• 索引篩選述詞只在葉節點走訪期間套用，不會縮小掃描範圍。

附錄 A 說明如何在其他資料庫辨識存取述詞。

把索引定義反轉過來，所有條件都能成為存取述詞：

---------------------------------------------------------------
| Id | Operation                    | Name      | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT              |           |    1 |    3 |
|* 1 | FILTER                       |           |      |      |
| 2 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES |    1 |    3 |
|* 3 |    INDEX RANGE SCAN          | EMP_TEST2 |    1 |    2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
3 - access(SUBSIDIARY_ID = :SUBS_ID
       AND DATE_OF_BIRTH >= :START_DT
       AND DATE_OF_BIRTH <= :END_DT)

最後是 between 運算子，它讓你在單一條件中指定上下界：

DATE_OF_BIRTH BETWEEN '01-JAN-71'
                  AND '10-JAN-71'

索引 LIKE 篩選#

LIKE 運算子經常造成出乎意料的效能行為，因為某些搜尋詞會阻止索引被有效使用。關鍵在於萬用字元的位置。

SELECT first_name, last_name, date_of_birth
  FROM employees
 WHERE UPPER(last_name) LIKE 'WIN%D'

1. 第一個萬用字元之前的部分 → 存取述詞
2. 其餘字元 → 篩選述詞

在 PostgreSQL 中，可能需要指定運算子類別（operator class，例如 varchar_pattern_ops）才能讓 LIKE 運算式作為存取述詞。詳見 PostgreSQL 文件的「Operator Classes and Operator Families」。

前綴越具選擇性，範圍越小#

三種寫法都選出同一列，掃描範圍卻天差地遠：

• LIKE 'WI%ND'：萬用字元前只有兩個字元，掃描範圍縮到 18 列，其中只有 1 列真正匹配——其餘 17 列被抓取後丟棄。
• LIKE 'WIN%D'：前綴較長，掃描範圍縮到 2 列，只多讀一列無關資料。
• LIKE 'WINA%'：完全沒有篩選述詞，資料庫只讀取真正匹配的那一筆。

反過來，以萬用字元開頭的 LIKE 運算式無法作為存取述詞。若沒有其他條件提供存取述詞，資料庫就得掃描整張表。

避免使用前導萬用字元的 LIKE 運算式（例如 '%TERM'）。

繫結參數讓事情更複雜#

萬用字元的位置影響索引使用——至少理論上如此。現實中，當搜尋詞透過繫結參數傳入時，最佳化工具只能產生通用計畫，必須猜測多數執行是否帶有前導萬用字元。

• 多數資料庫假設沒有前導萬用字元。但若該 LIKE 是用來做全文檢索，這個假設就錯了。可惜沒有直接的方法把 LIKE 標記為全文檢索。
• PostgreSQL 則相反：使用繫結參數時它假設有前導萬用字元，因而乾脆不用索引。唯一取得索引存取的方法，是讓實際搜尋詞對最佳化工具可見；若不用繫結參數而把搜尋詞直接寫進 SQL，你必須另行防範 SQL 注入攻擊。

不用繫結參數是最直觀的解法，但會提高最佳化開銷並打開 SQL 注入的破口。一個有效、安全又可攜的做法是刻意混淆 LIKE 條件——詳見「混淆的條件」一節的「組合欄位」。

WHERE text_column LIKE '%' || ? || '%'

專有的全文索引方案#

即使資料庫針對前導萬用字元最佳化了計畫，效能仍可能不足。此時可改用 where 子句的其他部分來有效存取資料（見「刻意運用索引篩選述詞」），若沒有其他存取路徑，則可考慮以下專有方案：

• MySQL：match / against 關鍵字。自 MySQL 5.6 起 InnoDB 表也能建全文索引（先前僅 MyISAM 可用）。
• Oracle Database：contains 關鍵字，見《Oracle Text Application Developer’s Guide》。
• PostgreSQL：@@ 運算子。另有 WildSpeed 擴充可直接優化 LIKE 運算式——它把文字的所有旋轉形式都存起來，讓每個字元都當過一次開頭；代價是索引文字被儲存的次數等於字串長度，極耗空間。
• SQL Server：contains 關鍵字，見文件中的「Full-Text Search」。

索引合併#

這是關於索引最常見的問題之一：該為每個欄位各建一個索引，還是為 where 子句的所有欄位建一個索引？ 多數情況下答案很簡單：一個多欄索引更好。

但確實有些查詢是單一索引無論如何定義都做不好的，例如帶有兩個以上獨立範圍條件的查詢：

SELECT first_name, last_name, date_of_birth
  FROM employees
 WHERE UPPER(last_name) < ?
   AND date_of_birth    < ?

為什麼一個 B-tree 撐不起兩個範圍條件#

要理解原因，只要記住索引是一條鏈結串列：

• 定義成 (UPPER(LAST_NAME), DATE_OF_BIRTH)，串列從 A 排到 Z；只有同名時出生日期才起作用。
• 反過來定義，串列從最年長排到最年輕；此時姓名對排序只有次要影響。

無論怎麼翻轉定義，條目永遠沿著一條鏈排列：一端是小值，另一端是大值。

因此一個索引只能支援一個範圍條件作為存取述詞。要支援兩個獨立範圍條件，需要第二個軸——像西洋棋盤那樣，查詢會匹配棋盤某個角落的所有格子。但索引不是棋盤，是鏈條，沒有角落。

兩種務實做法#

做法一：接受篩選述詞，仍用多欄索引。 多數情況下這反而是最好的解法。此時索引定義應把較具選擇性的欄位放前面，讓它能作為存取述詞使用。

這大概就是「最具選擇性的欄位放最前面」這個迷思的起源——但該規則只在你無法避免篩選述詞時才成立。

做法二：用兩個獨立索引，再合併結果。 資料庫必須先掃描兩個索引再合併：光是重複的索引查找就要走訪兩棵索引樹，額外還需要大量記憶體與 CPU 時間來合併中間結果。

位圖索引與混合方案#

資料庫用兩種方法合併索引。其一是索引 join（第 4 章詳述相關演算法）。其二則來自資料倉儲的世界。

資料倉儲是所有臨時查詢之母：只要點幾下就能把任意條件組成查詢，根本無法預測 where 子句會出現哪些欄位組合，前述的索引方法幾乎派不上用場。資料倉儲用一種特殊索引型態解決此問題：位圖索引（bitmap index）。

• 優點：位圖索引相當容易合併，因此個別索引每個欄位也能得到不錯的效能。
• 但：若你事先知道查詢、能建出量身打造的多欄 B-tree 索引，那仍然比合併多個位圖索引更快。

位圖索引最大的弱點是荒謬的寫入擴展性：insert、update、delete 極差，並行寫入幾乎不可能。這在資料倉儲中不成問題（載入程序是一個接一個排程的），但在線上應用中，位圖索引大多毫無用處——它幾乎不適用於線上交易處理（OLTP）。

許多資料庫產品提供 B-tree 與位圖索引之間的混合方案：在缺乏更好存取路徑時，把數次 B-tree 掃描的結果轉換成記憶體中的位圖結構再高效合併。這些結構不持久化、敘述執行完就丟棄，因而繞開了寫入擴展性的問題；缺點是需要大量記憶體與 CPU 時間。說到底，這是最佳化工具的絕望之舉。