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のドキュメントの 「演算子 クラスと演算子族」を参照してください。

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

図2.4 色々なLIKEでの検索


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

重要

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

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

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

Tweet this tip

ヒント

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

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

このウェブサイトにぴったりのカップは僕たちのショップにあります。
#見た目もいい感じだし、ここでの僕の仕事を支えてくれています

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

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

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

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

ワイルドカードは直接SQL文に書き込みますが、検索語はバインドパラメータで 与えます。最終的なLIKE式は、文字列連結の演算子である || (OracleとPostgreSQLの場合)を使って、データベースが 組み立てます。バインドパラメータを使うことで、最終的な 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を使った検索に対して、インデックスを作るには どうしたらよいでしょうか?

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