SQLのWHERE句で文字列を検索するときは、完全一致、前方一致、部分一致、単語検索を区別することが出発点です。本記事では共通する考え方を説明し、具体的な構文・実行計画・全文検索の例はMySQL 8.4（主にInnoDB）を前提にします。他のDBMSへそのまま転用せず、照合順序やインデックスの条件も確認しましょう。
検索条件を選ぶ：完全一致・部分一致・NULL
完全一致なら=、前方一致ならLIKE 'John%'を使います。以下は条件の違いを示す例です。結果は列の照合順序に従うため、「完全一致」は必ずしもバイト単位の一致を意味しません。
SELECT * FROM users WHERE name = 'John';
SELECT * FROM users WHERE name LIKE 'John%';
SELECT * FROM users WHERE name LIKE '%John%';
SELECT * FROM users WHERE name IS NULL;NULLは空文字とは異なります。name = NULLでは検索せず、IS NULLを使います。name NOT LIKE '%John%'もNULL行を含まないため、含めたい場合はOR name IS NULLを明示します。
MySQLでは_ci・_cs等の照合順序により大文字小文字の扱いが変わります。要件に合う列定義・照合順序を先に選び、必要もなくLOWER(name)や別のCOLLATEを条件に追加しないでください。検索結果やインデックスの利用条件が変わり得ます。
インデックスの利用
インデックスは、条件に合う行へ効率的に到達するための仕組みです。文字列の完全一致や前方一致ではB-treeインデックスが候補になります。ただし、テーブルサイズ、条件の選択性、照合順序、統計情報によって効果は変わり、作成すれば必ず速くなるわけではありません。
インデックスの作成
インデックスを作成するには、以下のSQL文を使用します。ここでは、nameカラムにインデックスを作成する例を示します。
CREATE INDEX idx_name ON users(name);このインデックスはname = 'John'などの検索で利用候補になります。既存のインデックスを確認し、同名・同等のものを重複作成しないでください。DDLはロック、書き込み負荷、ディスク使用量へ影響するため、本番への適用前に検証します。
インデックスの種類
列の数で分類すると、単一カラムと複合インデックスがあります。これはB-tree・HASH・FULLTEXTなどの実装・用途による分類とは別です。
単一カラムインデックス
一つのカラムに対して作成されるインデックスです。特定のカラムの値を検索する場合に有効です。
複合インデックス
複数のカラムを指定するインデックスです。MySQLの通常の複合B-treeでは列の順序が重要なので、検索条件と順序を合わせて設計します。
CREATE INDEX idx_name_age ON users(name, age);(name, age)は通常、左端のname、またはnameとageの組み合わせに利用しやすい構成です。ageだけの条件を同じ効率で検索できるとは限りません。左端一致の原則や例外的な最適化を含め、実行計画で確認します。
インデックスの使用時の注意点
インデックスは便利ですが、いくつかの注意点もあります。
書き込み性能への影響
インデックスはデータの挿入、更新、削除時に追加の処理を必要とするため、これらの操作のパフォーマンスに影響を与えることがあります。
不要なインデックスの削除
不要なインデックスは書き込み負荷や容量を増やします。ただし短期間に使われなかっただけで削除せず、定期処理・制約・他のクエリへの影響を確認します。以下はMySQLの削除構文です。PostgreSQL等のDROP INDEX idx_name;と構文が異なります。
-- MySQL：不要と判断し、影響を確認した場合だけ実行する
DROP INDEX idx_name ON users;適切なインデックスの使用は、SQLクエリのパフォーマンスを大きく改善します。次は、LIKE演算子の最適化について解説します。
LIKE演算子の最適化
SQLのLIKE演算子は、部分一致検索に使用されます。しかし、パフォーマンスに影響を与える可能性があるため、効率的に使用する方法を知っておくことが重要です。
基本的なLIKE検索
基本的なLIKE検索は、次のように行います。
SELECT * FROM users WHERE name LIKE 'John%';このクエリは照合順序の比較規則に従って「John」で始まる値を検索します。大文字小文字やアクセントを区別するかは列・式の照合順序によって異なり、文字コードが同じでも検索結果が同じとは限りません。
ワイルドカードの使い方
LIKE演算子には、いくつかのワイルドカードが使用できます。
% ワイルドカード
0文字以上の任意の文字列にマッチします。LIKE '%John%'は「John」を含む値、LIKE 'John%'は「John」で始まる値、LIKE '%John'は「John」で終わる値を探します。
_ ワイルドカード
任意の一文字にマッチします。例えば、LIKE 'J_n'は、「J」で始まり、「n」で終わる三文字の文字列にマッチします。
インデックスとの組み合わせ
LIKE演算子を使用した検索でインデックスを活用するためには、ワイルドカードの位置に注意が必要です。
先頭一致の場合
MySQLのB-treeでは、LIKE 'John%'のように定数パターンがワイルドカードで始まらない場合、範囲アクセスの候補になります。対象列に適したインデックスがあり、型・照合順序などの条件が合うことが前提です。
-- idx_name が作成済みなら再作成しない
EXPLAIN SELECT * FROM users WHERE name LIKE 'John%';候補になっても、オプティマイザーが必ず採用するわけではありません。対象行が多い場合などは別のプランが選ばれるため、EXPLAINで範囲アクセスか、走査する推定行数はいくつかを確認します。
先頭にワイルドカードがある場合
LIKE '%John'やLIKE '%John%'は、通常のB-treeで文字列の開始位置から検索範囲を絞れません。ただし「インデックスが一切使えない」とは限らず、別の条件での絞り込みやインデックス全体のスキャンが選ばれる場合もあります。
SELECT * FROM users WHERE name LIKE '%John';この条件だけで通常のB-treeによる効率的な範囲検索を期待するのは難しく、大量の行を調べる可能性があります。単語を探したいなら全文検索、任意の部分文字列が必要ならDBMSに応じた専用索引などを検討します。両者の検索結果は同一ではありません。
エスケープシーケンスの利用
%や_自体を探すときはESCAPEで通常の文字として扱います。MySQLのバックスラッシュはSQL文字列とLIKEパターンの二段階で解釈され、SQLモードにも依存するため、ここでは!を明示します。
SELECT * FROM users WHERE name LIKE '100!%' ESCAPE '!';
-- 文中に 100% を含む値を探す場合
SELECT * FROM users WHERE name LIKE '%100!%%' ESCAPE '!';1つ目は「100%」に一致するパターン、2つ目は「100%」を含むパターンです。!_はアンダースコア、!!は感嘆符そのものを表します。パラメーター化だけではLIKEのワイルドカードの意味は消えないため、利用者の入力を文字どおり検索するなら別途エスケープします。
入力値はパラメーターで渡す
SQL文字列へ入力を連結しないでください。次はMySQL Connector/Pythonの既存カーソルcursorを使う例で、%sを値のプレースホルダーにします。これはPythonの文字列補間ではありません。LIKEで文字どおり探すため、!を先に二重化し、その後%と_を処理します。
def escape_like_literal(value):
return value.replace("!", "!!").replace("%", "!%").replace("_", "!_")
term = "100%_off!" # 説明用の検索値
pattern = "%" + escape_like_literal(term) + "%"
cursor.execute(
"SELECT id, name FROM users WHERE name LIKE %s ESCAPE '!'",
(pattern,),
)検索モードを利用者が選ぶ設計なら、ワイルドカードを許可するか、通常の文字として扱うかを分けます。空の入力は%%となって多くの行に一致するため、アプリ側で空入力の扱いと返却件数を制限してください。テーブル名や列名は値パラメーターでは置き換えず、許可した識別子から選びます。
文字列操作関数の使用
LEFT、RIGHT、SUBSTRINGでも条件を書けますが、列を関数で包むと元の列の通常のインデックスを範囲検索に使いにくくなることがあります。高速化のために無条件で置き換える方法ではありません。
SELECT * FROM users WHERE LEFT(name, 4) = 'John';これは先頭4文字を比較する例です。単純な前方一致なら、まずname LIKE 'John%'を検討してください。関数索引や生成列の索引が必要かはDBMS・式・実行計画次第です。照合順序や末尾空白によって=とLIKEの比較結果が異なることもあります。
LIKE演算子の最適化により、文字列検索のパフォーマンスを向上させることができます。次に、フルテキスト検索の活用について解説します。
フルテキスト検索の活用
全文検索は、文書内の単語やトークンを探すための機能です。大量の文章の検索に向きますが、任意の部分文字列を探すLIKE '%文字列%'と同じ条件ではありません。以下のFULLTEXT、MATCH ... AGAINSTはMySQLの構文です。
フルテキストインデックスの作成
以下はMySQLのInnoDBテーブルでcontentが対応する文字列型（CHAR・VARCHAR・TEXT）の場合の例です。全文検索用索引の作成前に、既存索引、ディスク空き容量、ロックや運用への影響を確認します。
CREATE FULLTEXT INDEX idx_content ON articles(content);InnoDBのMATCHで指定する列の組み合わせに対応したFULLTEXT索引が必要です。複数列を検索する場合は、索引定義とMATCHの列を合わせて設計してください。
フルテキスト検索の実行
フルテキスト検索を実行するには、MATCHとAGAINST句を使用します。以下の例では、contentカラム内で「database」という単語を検索します。
SELECT * FROM articles
WHERE MATCH(content) AGAINST('database');既定の自然言語モードで「database」に対する関連度が0でない行を選びます。「database」という連続した文字列さえ含めば必ず一致する、という意味ではありません。パーサーやストップワードの設定も影響します。
自然言語検索とブールモード検索
ここではMySQLの自然言語モードとブールモードを紹介します。MySQLには、このほか検索語を広げるクエリ拡張もあります。
自然言語検索
自然言語検索では、単語の関連性に基づいて結果を返します。例えば、以下のクエリは自然言語検索を実行します。
SELECT * FROM articles
WHERE MATCH(content) AGAINST('efficient SQL search' IN NATURAL LANGUAGE MODE);ブールモード検索
MySQLのブールモードでは、検索文字列内の+が必須語、-が除外語、演算子なしが任意語を表します。SQLのAND・OR・NOTをそのまま検索文字列へ書く方式ではありません。
SELECT * FROM articles
WHERE MATCH(content) AGAINST('+efficient +SQL -slow' IN BOOLEAN MODE);各語が索引化対象であることを前提に、「efficient」と「SQL」を必須にし、「slow」を除外します。短い語やストップワード、パーサーの設定により結果が変わり、ブールモードは関連度順に自動整列しません。必要な順序は明示してください。
インデックスのメンテナンス
通常の追加・更新・削除に伴う索引の維持はDBMSが処理します。単に更新が多いという理由で定期的にDROP/ADDする必要はありません。トークン長やストップワード等の設定変更を反映するために再構築が必要な場合は、公式手順に従って計画します。以下は構文の例で、実行には負荷・ロック・空き容量などの事前確認が必要です。
-- 再構築が必要と判断した場合の MySQL の構文例
-- 通常のデータ更新のたびに実行するものではない
ALTER TABLE articles
DROP INDEX idx_content,
ADD FULLTEXT INDEX idx_content(content);フルテキスト検索の制限
短い語やストップワードは検索対象にならない場合があります。日本語など空白で単語を区切らない文章では、標準パーサーのまま期待した結果になるとは限りません。MySQLではWITH PARSER ngramなどを検討し、対象言語・トークンサイズ・検索結果を検証します。ngramの条件を通常の単語長設定と混同しないでください。
フルテキスト検索を活用することで、大量のテキストデータを効率的に検索することが可能になります。次に、正規表現検索の実装について解説します。
正規表現検索の実装
正規表現（Regular Expression）は、複雑な文字列パターンを柔軟に検索するための強力なツールです。SQLでも正規表現を利用して、より高度な検索を実行することができます。
正規表現検索の基本
MySQL 8.4ではREGEXP（RLIKE）とREGEXP_LIKE()が使えます。以下はREGEXP_LIKE()の第3引数'c'で大文字小文字を区別する例です。例の値は改行を含まない1行の文字列とします。
SELECT * FROM users
WHERE REGEXP_LIKE(name, '^[A-Za-z]+$', 'c');1行の値から、ASCII英字A〜Z・a〜zで構成されるものを探す例です。すべての言語の「アルファベット」を表すものではありません。正規表現の文字クラスと照合・大文字小文字の条件を区別します。
正規表現の基本構文
正規表現の基本的な構文を理解することが重要です。以下にいくつかの例を示します。
文字クラス
特定の文字の集合を定義します。ここでは[A-Za-z]をASCII英字、[0-9]をASCII数字として使います。Unicode文字全般を許可したいときは別途要件を決めます。
アンカー
^と$で先頭・末尾を指定します。ただし改行やマルチラインモードの扱いは正規表現エンジンに依存し、$は末尾改行の直前にも一致し得ます。改行を含む入力や厳密な入力検証へ、そのまま転用しないでください。
量指定子
特定のパターンの出現回数を指定します。例えば、{2,4}は2回から4回の出現を意味します。
より高度な正規表現の使用
正規表現を使って、複雑なパターンを検索することも可能です。例えば、メールアドレスの形式を検索する場合は、次のようにします。
SELECT * FROM users
WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+[.][A-Za-z]{2,}$', 'c');これはメールアドレスらしい値を抽出する簡易パターンです。RFC上の全形式、国際化、ドメインの存在、メール到達性を検証するものではありません。ドットは[.]と書き、MySQLのSQL文字列でバックスラッシュが消える問題を避けています。
正規表現のパフォーマンス
MySQLの正規表現を使っただけで通常のB-treeによる範囲検索ができるわけではありません。意味を変えない別条件で候補行を減らし、実行計画を確認します。外部入力を正規表現として許可する場合は、複雑なパターンによる処理時間・資源消費にも注意が必要です。
正規表現検索の実装例
以下に、正規表現を使ったいくつかの実装例を示します。
電話番号の検索
SELECT * FROM contacts
WHERE REGEXP_LIKE(phone, '^[(][0-9]{3}[)] [0-9]{3}-[0-9]{4}$', 'c');改行を含まない値について、(123) 456-7890のような表示形式を探す例です。番号の割り当てや有効性は判定しません。[(]・[)]・[0-9]を使い、SQLリテラル内の二重エスケープを避けています。
郵便番号の検索
SELECT * FROM addresses
WHERE REGEXP_LIKE(postal_code, '^[0-9]{5}(-[0-9]{4})?$', 'c');改行を含まない値から、米国の5桁またはZIP+4形式（5桁・ハイフン・4桁）に合うものを探します。実在する郵便番号かは判定しません。
正規表現を使用することで、複雑なパターンの文字列検索が可能になります。次に、クエリプランの確認について解説します。
クエリプランの確認
クエリプランの確認は、SQLクエリのパフォーマンスを最適化するための重要なステップです。クエリプランを分析することで、データベースがどのようにクエリを実行しているかを理解し、ボトルネックを特定できます。
クエリプランとは
クエリプランは、SQLデータベースがクエリを実行するための内部計画を示します。この計画には、テーブルスキャン、インデックス使用、結合方法などの詳細が含まれます。
EXPLAINコマンドの使用
以下はMySQL 8.4の例です。後述する列形式に合わせてFORMAT=TRADITIONALを明示します。DBMSによって構文や出力項目が異なるため、別製品の実行計画と同じ名前・意味とは限りません。
EXPLAIN FORMAT=TRADITIONAL
SELECT * FROM users WHERE name = 'John';このコマンドの結果は、クエリの実行計画を示し、どのインデックスが使用されているか、テーブルスキャンが行われているかなどの情報を提供します。
クエリプランの読み方
以下はMySQLの従来形式の主な項目です。推定計画であり、実測時間や実際の読み取り件数ではないことに注意してください。
テーブル
クエリで使用されるテーブルの名前を示します。
タイプ
typeはアクセス方式です。ALLはテーブル全体、indexはインデックス全体の走査、rangeは範囲アクセスです。constやeq_refは一意キー等を使う限定的な参照を表します。単にindexと表示されたことだけで高速とは判断できません。
可能なキー
possible_keysは検索に使える候補です。候補にあることは採用を意味せず、全体のプランとの関係で決まります。
キー
keyはオプティマイザーが選択したインデックスです。通常のEXPLAINでは実行結果ではなく、予定される計画を表します。
キーの長さ
key_lenは利用するキー部分の長さをバイト数で示し、複合索引のどの部分を使うかを考える手掛かりになります。文字数そのものではありません。
行数
rowsは調査する行数の推定です。実測値ではありません。filteredは条件通過率の推定なので、両者と結合順序を合わせて読みます。
追加情報
クエリ実行に関する追加の詳細情報を提供します。
クエリプランの最適化
クエリプランを分析し、以下の方法でクエリのパフォーマンスを最適化できます。
インデックスの追加
既存定義と利用状況を確認し、必要な索引だけを追加します。ここまでの例ですでにidx_nameを作成した場合、再度作成しないでください。各コード例は独立した説明であり、上から順にすべて実行する手順ではありません。
-- 同名・同等のインデックスがないことを先に確認する
SHOW INDEX FROM users;
-- 必要と判断した場合だけ追加する
CREATE INDEX idx_name ON users(name);結合方法の最適化
結合条件の列にも索引が役立つ場合があります。ただしINNER JOINとLEFT JOINは、対応する行がない場合の結果が異なります。速度だけを理由に置き換えず、必要な結果を保ったまま計画を調べます。
EXPLAIN SELECT * FROM users
INNER JOIN orders ON users.id = orders.user_id
WHERE users.name = 'John';クエリの再構築
必要な列だけを返し、重複行やNULLの扱いを維持したままクエリを見直します。サブクエリよりJOINが常に速いわけではなく、オプティマイザーによる変換もあるため、同じ結果になるかと実行計画の両方を確認します。
SELECT users.name, orders.order_date
FROM users
JOIN orders ON users.id = orders.user_id
WHERE users.name = 'John';EXPLAIN ANALYZEは対象クエリを実際に実行して計測するため、単なる計画表示とは異なります。重い検索を本番で気軽に実行せず、許可された検証環境・代表データで確認してください。本記事は速度向上を実測したベンチマークではありません。
ツールの活用
多くのデータベース管理ツールやIDEには、クエリプランの分析機能が組み込まれています。これらのツールを活用することで、クエリのパフォーマンスを簡単に分析し、最適化できます。
クエリプランを定期的に確認することで、SQLクエリのパフォーマンスを維持し、データベースの効率的な運用が可能になります。最後に、これまでの内容をまとめます。
PostgreSQL・SQL Serverへ移すときの注意
PostgreSQLにはILIKEや正規表現演算子~等があります。照合順序やバージョンによる対応差を確認してください。pg_trgmのGiST/GIN索引はLIKE等の部分一致を支援できますが、短いパターンなどは効きにくい場合があり、通常のB-treeとは別の設計です。
SQL ServerのLIKEは%・_に加えて[...]のパターンも扱います。大文字小文字等は照合順序に依存し、全文検索もMySQLのMATCH ... AGAINSTとは構文が異なります。SQL Serverの一部関連サービスではESCAPEに対応しないものもあるため、利用製品の仕様を確認してください。
まとめ
文字列検索は、検索したい意味を決めてから条件と索引を選びます。完全一致は=、前方・後方・部分一致はLIKE、単語検索は全文検索、複雑な形式は正規表現が候補です。照合順序、NULL、入力のエスケープを確認し、MySQL固有の構文を他のDBMSへ混在させず、実行計画と必要な検証で効果を確かめましょう。
公式資料
MySQL 8.4：LIKE・ESCAPE・照合順序・NULL
MySQL 8.4：EXPLAIN・EXPLAIN ANALYZE
コメント