by Hayato Matsuura.

LIKEフィルタに対するインデックス


SQLのLIKE演算子は、予期せぬパフォーマンス上の 問題の原因になることが非常に多くあります。これは、検索語によっては、インデックスの効率的な使用を妨げてしまうからです。つまり、 インデックスをうまく作成できる検索語と、そうでない語があるということです。ワイルドカード文字の場所が、その違いです。

以下の例では、%ワイルドカードを検索語の真ん中で使っています。

SELECT first_name, last_name, date_of_birth
  FROM employees
 WHERE UPPER(last_name) LIKE 'WIN%D'
DB2
Explain Plan
----------------------------------------------------
ID | Operation         |                 Rows | Cost
 1 | RETURN            |                      |   13
 2 |  FETCH EMPLOYEES  |     1 of 1 (100.00%) |   13
 3 |   IXSCAN EMP_NAME | 1 of 10000 (   .01%) |    6

Predicate Information
 3 - START ('WIN....................................
      STOP (Q1.LAST_NAME <= 'WIN....................
      SARG (Q1.LAST_NAME LIKE 'WIN%D')

この例では、クエリはWHERE last_name LIKE 'WIN%D' (UPPERがない)と読み替えています。DB2 LUW 10.5は、関数インデックスに対するLIKEにアクセス述語を 使えないようです(フルインデックススキャンを使ってしまう)。

一方DB2は、最初のワイルドカードより前の部分からなる STARTSTOP条件を明確に表示していて、しかも フィルタ述語としてパターンすべてが適用されていることも表してくれています。

MySQL
+----+-----------+-------+----------+---------+------+-------------+
| id | table     | type  | key      | key_len | rows | Extra       |
+----+-----------+-------+----------+---------+------+-------------+
|  1 | employees | range | emp_name | 767     |    2 | Using where |
+----+-----------+-------+----------+---------+------+-------------+
Oracle
---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |    1 |    4 |
| 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |    1 |    4 |
|*2 |   INDEX RANGE SCAN          | EMP_UP_NAME |    1 |    2 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(UPPER("LAST_NAME") LIKE 'WIN%D')
       filter(UPPER("LAST_NAME") LIKE 'WIN%D')
PostgreSQL
                       QUERY PLAN
----------------------------------------------------------
Index Scan using emp_up_name on employees
   (cost=0.01..8.29 rows=1 width=17)
   Index Cond: (upper((last_name)::text) ~>=~ 'WIN'::text)
           AND (upper((last_name)::text) ~<~  'WIO'::text)
       Filter: (upper((last_name)::text) ~~ 'WIN%D'::text)

インデックスのツリー走査においてLIKEが 有効なのは、最初のワイルドカードの前までだけ です。残りの文字は、スキャンされるインデックスの範囲を狭めてくれない、単なるフィルタ述語になります。LIKEが 1つしかない表現には、(1) 最初のワイルドカードの前の、アクセス述語として使われる部分、(2) フィルタ述語となるその他の部分、の 2つの述語タイプのみがあり得ます。

注意

PostgreSQLでは、LIKEをアクセス述語として使うには、 演算子クラス(例、varchar_pattern_ops)を指定する 必要があります。詳しくは、PostgreSQLのドキュメントの「演算子 クラスと演算子族」を参照してください。

最初のワイルドカードの前部分の選択性が高ければ高いほど、スキャンされるインデックスの範囲は小さく、インデックス走査は 高速になります。で、 この関係性について3つの異なるLIKE式を使った例で説明しています。

図色々なLIKEでの検索

最初の式では、ワイルドカードの前に2文字があります。これにより、 スキャンされるインデックスの範囲は18行に制限されています。しかし、 LIKEに完全に一致しているのは1行だけで、他の17行は テーブルから取得されるけれども捨てられてしまいます。2番目の式では、3文字というより長いプレフィックスのため、スキャンされるインデックスの 範囲を2行まで小さくなっています。この式だと、データベースは最終結果に必要のない行を1行しか読み込まなくてよいのです。最後の式は、フィルタ 述語を全く使わず、データベースはLIKEに完全に一致する行だけを読み込めばよくなります。

重要

最初のワイルドカードの前の部分だけが、アクセス述語になります。

残りの文字は、スキャンされるインデックスの範囲を狭めては くれません。マッチしなかったエントリは、無視されてしまいます。

逆のケース、つまりワイルドカードから始まるLIKE式も あり得ます。こういったLIKE式は、アクセス述語としては 使えません。アクセス述語となり得る他の条件がない場合、データベースはテーブル全体をスキャンしなくてはなりません。

ヒント

ワイルドカードから始まるLIKE式を 使うのはやめましょう(例、'%TERM')。

少なくとも理屈の上では、ワイルドカード文字はインデックスの使われ方に影響があることが分かりました。 実際には、バインドパラメータを 使って検索語が与えられると、オプティマイザは標準的な実行計画を立案します。この時、オプティマイザは先頭にワイルドカードがある 文を実行する方が多いか少ないかを推測します。

協力してください

この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。

多くのデータベースでは、バインドパラメータで与えられる LIKE条件の最適化時には、ワイルドカードから始まる表現は ないと仮定しますが、LIKEが全文検索に使われる際には この思い込みは間違いです。残念ながら、LIKEが全文検索用なのかどうか目印をつける方法はありません。LIKEでの全文検索を明示するの ボックスでは、この回避法を挙げています。バインドパラメータを使わずに検索語を与えるのが一番分かりやすい方法ですが、最適化のオーバー ヘッドを増やし、SQLインジェクションの脆弱性を大きくしてしまいます。効果的かつセキュアで、移植性のある方法としては、意図的に LIKEを抽象化してしまうことです。列の連結で詳しく解説しています。

LIKEでの全文検索を明示する

全文検索のためにLIKEを使う時は、検索語の前後に ワイルドカードを分離することもできます。

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

PostgreSQLにおいては、LIKE式にバインドパラメータを 使った場合、PostgreSQLはその先頭にワイルドカードが 存在すると仮定してしまうという問題があります。 PostgreSQLでは、このような場合インデックスを使ってくれないのです。 LIKE式でインデックスにアクセスする唯一の方法は、実際の 検索語をオプティマイザに見せてやることです。バインドパラメータを使わずに検索語をSQL文に直接書き込むと、SQLインジェクション攻撃に 対する予防措置を別に行わなければならない点に注意しましょう!

データベースが、先頭にワイルドカードがある時にも 実行計画を最適化してくれたとしても、十分なパフォーマンスは得られません。そのような場合には、where句の別の部分を使って、 データに効率よくアクセスすることができます。これについては、フィルタ述語の意図的な使用を参照してください。もし 他のアクセスパスが存在しない時は、以下のようなプロプライエタリな全文検索インデックスのソリューションを使用することもできます。

DB2

DB2は、containsキーワードをサポートします。詳しくは、IBM developerWorksの 「DB2 Text Search tutorial」を参照してください。

MySQL

MySQLでは、全文検索に対してmatchおよび againstキーワードが用意されています。以前は MyISAMテーブルに対してのみ可能だった全文検索インデックスが、MySQL 5.6以降では、InnoDBのテーブルに対しても作成できるようになりました。詳しくは、MySQLドキュメントの「Full-Text Search Functions」を参照してください。

Oracle

Oracleでは、containsキーワードが用意されています。詳しくは、「Oracle Textアプリケーション開発ガイド」を参照してください。

PostgreSQL

PostgreSQLでは、全文検索を実装するのに@@演算子が 用意されています。詳しくは、PostgreSQLドキュメントの「全文 検索」を参照してください。

LIKE式を直接最適化してしまう方法として、WildSpeedを 使うこともできます。この拡張機能は、どの文字も1文字目になるように文字列をずらしたものを格納します。つまり、文字列は1度だけ インデックスに保存されるのではなく、文字数分繰り返して保存されるので、多くの容量を必要とします。

SQL Server

SQL Serverでは、containsキーワードが用意されています。詳しくは、SQL Serverドキュメントの「フルテキスト 検索」を参照してください。

考えてみよう

最初に1つだけワイルドカードのある('%TERM')のような 検索語のLIKEを使った検索に対して、インデックスを作るにはどうしたらよいでしょうか?

著者について

Markus Winandの写真

Markus Winand氏は、開発者がSQLパフォーマンスを改善するお手伝いをしています。彼は、SQL Performance Explainedの 著者でもあり、出張トレーニングhttp://winand.at/での リモート講義も 行っています。

彼の本

カバー『SQLパフォーマンス詳解』

核心をわかりやすく 解説。

Markusから購入します
(送料無料+PDF)

Amazonで購入
(印刷版のみ)

“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | 接触 | 無保証 | 商標 | Privacy | CC-BY-NC-ND 3.0 license