SQLのLIKE演算子は、予期せぬパフォーマンス上の
問題の原因になることが非常に多くあります。これは、検索語によっては、インデックスの効率的な使用を妨げてしまうからです。つまり、
インデックスをうまく作成できる検索語と、そうでない語があるということです。ワイルドカード文字の場所が、その違いです。
以下の例では、%ワイルドカードを検索語の真ん中で使っています。
SELECT first_name, last_name, date_of_birth
FROM employees
WHERE UPPER(last_name) LIKE 'WIN%D'- Db2 (LUW)
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は、最初のワイルドカードより前の部分からなる
STARTとSTOP条件を明確に表示していて、しかも フィルタ述語としてパターンすべてが適用されていることも表してくれています。- 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つの述語タイプのみがあり得ます。
注意
LIKEは文字単位で動作する一方、照合順序によっては複数の文字を1つの並べ替え要素として扱う場合もあります。
つまり、いくつかの照合順序はLIKEに対するインデックスの使用を邪魔します。詳しくはLaurenz Albeの
Indexing “LIKE” in PostgreSQL and Oracle(PostgreSQLとOracleにおけるLIKEに対するインデックス)を読んでください。
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式は、アクセス述語としては
使えません。アクセス述語となり得る他の条件がない場合、データベースはテーブル全体をスキャンしなくてはなりません。
ヒント
ワイルドカードから始まるLIKE式を
使うのはやめましょう(例、'%TERM')。
少なくとも理屈の上では、ワイルドカード文字はインデックスの使われ方に影響があることが分かりました。 実際には、バインドパラメータを 使って検索語が与えられると、オプティマイザは標準的な実行計画を立案します。この時、オプティマイザは先頭にワイルドカードがある 文を実行する方が多いか少ないかを推測します。
協力してください
この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。
多くのデータベースでは、バインドパラメータで与えられる
LIKE条件の最適化時には、ワイルドカードから始まる表現は
ないと仮定しますが、LIKEが全文検索に使われる際には
この思い込みは間違いです。残念ながら、LIKEが全文検索用なのかどうか目印をつける方法はありません。「LIKEでの全文検索を明示する」の
ボックスでは、この回避法を挙げています。バインドパラメータを使わずに検索語を与えるのが一番分かりやすい方法ですが、最適化のオーバー
ヘッドを増やし、SQLインジェクションの脆弱性を大きくしてしまいます。効果的かつセキュアで、移植性のある方法としては、意図的に
LIKEを抽象化してしまうことです。「列の連結」で詳しく解説しています。
PostgreSQLにおいては、LIKE式にバインドパラメータを
使った場合、PostgreSQLはその先頭にワイルドカードが
存在すると仮定してしまうという問題があります。
PostgreSQLでは、このような場合インデックスを使ってくれないのです。
LIKE式でインデックスにアクセスする唯一の方法は、実際の
検索語をオプティマイザに見せてやることです。バインドパラメータを使わずに検索語をSQL文に直接書き込むと、SQLインジェクション攻撃に
対する予防措置を別に行わなければならない点に注意しましょう！
データベースが、先頭にワイルドカードがある時にも
実行計画を最適化してくれたとしても、十分なパフォーマンスは得られません。そのような場合には、where句の別の部分を使って、
データに効率よくアクセスすることができます。これについては、「フィルタ述語の意図的な使用」を参照してください。もし
他のアクセスパスが存在しない時は、以下のようなプロプライエタリな全文検索インデックスのソリューションを使用することもできます。
- Db2 (LUW)
Db2は、
containsキーワードをサポートします。詳しくは、IBM developerWorksの 「Search functions for Db2 Text Search」を参照してください。- 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を使った検索に対して、インデックスを作るにはどうしたらよいでしょうか？
