複数カラム検索の効率的な方法とは？

2012/11/09 11:52

このQ&Aのポイント

複数カラム検索を効率的に実施する方法について、MySQLの公式サイトなどで取り上げられています。具体的には、concat関数を使用してフィールドを結合し、LIKE演算子を使用して検索語を指定します。
「char(0)」についてですが、文字列結合の際に使用される特殊文字のひとつであり、フィールド間の区切り文字として利用されます。
「%データ%は全件を検索するため効率うんぬんを論ずるような仕組みではない」という意味は、LIKE検索が全件検索を行う仕組みではないため、効率性を論じる必要がないということです。また、「本番ではこんなことはしませんがあくまでも遊びということで」という文は、例示された方法は実際のシステムで使用するようなものではなく、あくまで遊びや実験的な目的で利用することを意味しています。

re999
お礼率61% (476/777)

MySQL
回答数2
ありがとう数2

みんなの回答 （2）
専門家の回答

質問者が選んだベストアンサー

ベストアンサー

mtaka2
ベストアンサー率73% (867/1179)

2012/11/09 13:00 回答No.2

> ・char(0)て何でしょうか？「検索語にはありえない文字」です。char(0)は、文字コード0の文字。以下の説明では\0と書きます。例えば、検索語が"%おか%"で、field1="あいうえお"で、field2="かきくけこ"だった場合、 concat(field1,field2)は"あいうえおかきくけこ"となり、検索条件にマッチします。ところが、concat(field1, char(0), field2) は、"あいうえお\0かきくけこ"となるため、こうしておけば"%おか%"はマッチしない、ということになります。 > ＞%データ%は全件を検索するため効率うんぬんを論ずるような仕組みではない > ・どういう意味でしょうか？前後にワイルドカードをつけたLIKE 検索(いわゆる「部分位置検索」)は、効率が非常に悪いからです。通常の検索(完全一致や範囲指定などによる絞り込み)では、DBにインデックスをつけることで、条件に該当するレコードを高速に抽出することができます。 (LIKEを使う場合でも、後ろにだけワイルドカードをつけた「データ%」のような、いわゆる「前方一致検索」は、インデックスが使えるので問題ありません。) ところが、前にワイルドカードをつけた場合、インデックスが使えませんので、 DBの全レコードを1件ずつ読み込んで条件に合致するかチェックする必要がある、ということになります。検索にはレコード数に比例した時間がかかるという、非常に効率が悪いものになってしまうのです。 > ・あるいは、データ規模が少なければ、LIKE検索を利用しても良いのでしょうか？それが問題にならない程度の規模ならLIKEの前後ワイルドカード(部分一致検索)をしてもいいでしょう。 > ・全件検索する際は、LIKE検索を使わないのでしょうか？全文検索エンジン(?)を利用するのでしょうか？規模が大きくなった場合には、全文検索エンジンを使うのが基本です。 > ・全件検索しなければ(例えば2カラムだけ)、LIKE検索を利用しても良いのでしょうか？「例えば2カラムだけ」というのが意味不明ですが、問題はチェック対象のレコード数(行数)です。カラム数(列数)は問題になりません。全件検索でなくても、チェック対象のレコード数が多い場合にはLIKE部分一致検索はしない方がいいでしょう。 LIKE検索以外の条件で、チェック対象が十分に絞り込めているなら、LIKE部分一致検索をしても大丈夫ということになります。 > ＞本番ではこんなことはしませんがあくまでも遊びということで > ・どういう意味でしょうか？上述のように性能が出せないので、大規模業務システムではそんなものは使わない、ということです。 > ・あるいは、全件検索でLIKE検索を普通は使わない、ということでしょうか？ LIKE検索でも、「あるカラムのみをチェック対象(LIKEの左辺)」とし、「頭には%をつけない前方一致検索」を行うのであれば問題ありません。 > ・concatで LIKE 検索を普通はしない、ということでしょうか？ > ・それとも、LIKE 検索で、char(0)などのセパレータを挟まない、ということでしょうか？ LIKE対象の前に%をつける「部分一致検索」は普通はしない、ということです。さらにいうと、あと、カラムそのものではなく、concat した文字列に対してのLIKE 検索だと、「データ%」のような前方一致検索でも、左辺側にインデックスが使えませんので、やっぱり効率が悪くなりますので使えません。

質問者

お礼 2012/11/13 06:41

詳しい説明ありがとうございました。大変勉強になりましたー

ログインすると、全ての回答が全文表示されます。

その他の回答 (1)

IDii24
ベストアンサー率24% (1597/6506)

2012/11/09 12:45 回答No.1

Char(0)はキャラクターつまり文字数をゼロという意味で、間を詰める事になります。つまりブランクを作るとうまく検索できないということでしょう。 LIKE検索はインデックスのついてないあくまでも簡易検索であり、クエリにとっては重い処理です。普通は検索項目にインデックスをつけて検索を早くしますが、LIKEではそのフィールドを一文字ずつ検査してその文字が無いかを判断しているのです。それに時間が掛かります。カラム数には関係ありません。データー規模が少なければ問題はありません。増えれば増えるだけ遅いということです。通常は全文検索インデックスを作成した項目に対して別のコマンドで処理します。全文検索インデックスは作製に時間がかかります。つまり書き込みが多いテーブルには不向きです。検索系のテーブルにつけるのが普通です。その場合は高速検索ができます。先に述べたようにconcatする事自体に時間がかかり、さらにLIKEで時間がかかります。現実的では無い処理です。 SQLとは別に神様言語ではありません。裏では記憶域に別領域を取ってデーターを比較するなど地道な処理を行います。もしこれらの処理を人間がする場合を想定すればよいだけです。項目をくっつけてさらに一文字ずつ比較するって大変ですよね。先にキーワードを作っておいた方が楽でしょう。同じなのです。

質問者