与えられたキーワードに合致するデータをデータベースから取得することを考えた時、完全一致であれば「=」で検索を行うと思います。
では与えられたキーワードを「含む」検索、つまり部分一致を行いたいのであればどうするか。最も簡単なのは「search_column like '%キーワード%'」といった形で、「like」を使いキーワードの前後にワイルドカードを与える方法でしょう。
ですがこれを何の気無しに使うのはちょっと危険。
上記のようなlike検索ではインデックスが利用されないため（※）、全レコードをスキャンします。
かつキーワードを部分一致で探したいようなカラムは、文章等の比較的大きなサイズであると考えられます。
そのため検索コストは大きく、数千〜数万行であればまだ良いかもしれませんが、数十万ともなると利用には耐えられないでしょう。（実体験）
※ただし前方一致検索であれば、最初のワイルドカードまででインデックスが利用できる。
LIKEがインデックスを使うようにSQLをチューニングする
LIKE検索は使ったらダメな場合もある - 全文検索について - SQLer 生島勘富 の日記
そのような部分一致検索を行うためにMySQL（のストレージエンジン）ではFullText Indexを用いた全文検索機能を提供しています。
今回は全文検索に必要となる準備から実施方法までを一通りまとめたいと思います。
MySQLにおける全文検索
全文検索のステップ
MySQLで全文検索を行うためのステップは以下のとおりです。環境
今回の実施環境は以下のとおり。- CentOS6.2
- MySQL5.7
また以下のようなテーブルの「desctext」カラムを全文検索することを想定します。
+-----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(255) | YES | | NULL | | | price | int(11) | YES | | NULL | | | stock | int(11) | YES | | NULL | | | update_at | datetime | NO | | NULL | | | desctext | text | YES | | NULL | | +-----------+--------------+------+-----+---------+----------------+
1.文章を検索用に分割する（Ngram、形態素解析）
そもそも全文検索とは「ある文章の中に指定したキーワードが含まれるか」ということをチェックすることであり、その判定を行うには「その文章がどのような単語で構成されているのか」が分かるようになっている必要があります。この時対象の文章が英語であればとても簡単。英語は「This is a pen」のように、「空白」で単語が分かれています。だから空白を目印にすればその文章に含まれる言葉をすぐに得ることができます。
一方で上記の英文を日本語で表すと「これはペンです」となります。日本語には英文のような決まった空白、つまり単語の境目が無いためそのままでは含まれる言葉を得ることができません。
MySQLにデフォルトで導入されているストレージエンジンのMyISAMやInnoDBでも、日本語文章を全文検索するためのカラムは単語と単語を空白やカラムで区切った状態にしておく必要があります。
そこで文章中に含まれる単語を抽出するために利用される代表的手法が、Ngramと形態素解析です。
Ngram
Ngramの「N」は任意の数字を表し、文章をN文字ずつで分けていく手法です。
前述の「これはペンです」をNを2としてNgramで分解すると、「これ れは はペ ペン ンで です」となります。
Ngramはわかりやすく非常にシンプルです。自分で実装もできるでしょう。（もちろんお手軽なライブラリもあります。）
形態素解析
一方で形態素解析はある「辞書」と文章を照らしあわせて、文章に出現する言葉を全て抽出するという方法です。
「辞書」の中にはあらゆる品詞の言葉が登録されており、1文字の単位から文章中の言葉がどの品詞に当てはまるのかをチェック、さらに言葉をつなげてチェックを繰り返し、最も文章らしい言葉の候補を選び出していきます。（すみません、詳しいところまでは理解してません・・・）
日本語形態素解析エンジン・言語郎 | 形態素解析とは？
「これはペンです」を例に取ると、最終的に「これ（名詞）は（助詞）ペン（名詞）です（助動詞）」という結果が得られます。
Free Dynamic DNS(DDNS) by POP3,IMAP4,FTP,HTTP-BASIC for Home Server, VPS | MyDNS.JP
どちらの方法を利用にするにせよ、得られた言葉を空白やカンマ区切りで連結した形の文章にします。
- Ngramなら「これ れは はペ ペン ンで です」
- 形態素解析なら「これ は ペン です」
をテーブルのレコードに登録します。（この時カラム型はchar、varchar、textのどれでもよい。）
ということで、今回利用するfruitsテーブルについて各フルーツの説明文を分解してカラムdescに登録したいと思います。
（説明文はWikipediaに載っているものを拝借しました。)
文章は2-gramで分割して単語の区切りに「,」を使い、「メロン」「ミカン」「いちご」「ぶどう」についてそれぞれ登録しました。
mysql> select `id`,`name`,`desctext` from fruits; +----+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | name | desctext | +----+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | メロン | メロ,ロン,ン（,（和,和名,名：,：メ,メロ,ロン,ン、,、英,英名,名：,：m,me,el,lo,on,n、,、学,学名,名：,：C,Cu,uc,cu,um,mi,is,me,el,lo,o）,）は,は、,、果,果実,実を,を食,食用,用に,にす,する,るウ,ウリ,リ科,科の,の一,一年,年生,生草,草本,本植,植物,物で,であ,ある,る。,。ま,また,た、,、そ,その,の果,果実,実の,のこ,こと,と。 | | 2 | ミカン | ミカ,カン,ン科,科（,（R,Ru,ut,ta,ac,ce,ea,ae,e）,）は,は双,双子,子葉,葉植,植物,物の,の科,科で,で約,約1,15,50,0属,属、,、9,90,00,0種,種か,から,らな,なる,る。,。木,木（,（一,一部,部草,草本,本）,）の,の状,状態,態で,で存,存在,在し,し、,、温,温帯,帯か,から,ら熱,熱帯,帯に,に分,分布,布す,する,る。,。精,精油,油を,を含,含み,み芳,芳香,香（,（異,異臭,臭の,の場,場合,合も,もあ,ある,る）,）を,を有,有す,する,る。,。花,花に,に芳,芳香,香の,のあ,ある,るも,もの,のも,も多,多い,い。 | | 3 | いちご | イチ,チゴ,ゴ（,（苺,苺、,、F,Fr,ra,ag,ga,ar,ri,ia,a）,）は,はバ,バラ,ラ科,科の,の多,多年,年草,草。,。食,食用,用と,とし,して,て供,供さ,され,れて,てい,いる,る部,部分,分は,は花,花托,托（,（花,花床,床と,とも,もい,いう,う）,）で,であ,あり,り果,果実,実で,では,はな,ない,い。,。イ,イチ,チゴ,ゴに,にと,とっ,って,ての,の果,果実,実は,は一,一見,見し,して,て種,種子,子に,に見,見え,える,る一,一粒,粒一,一粒,粒で,であ,あり,り、,、正,正確,確に,には,は痩,痩果,果と,とい,いう,う。,甘み,みが,があ,ある,るた,ため,め果,果物,物と,とし,して,て位,位置,置づ,づけ,けら,られ,れる,るこ,こと,とが,が多,多い,いが,が、,、草,草本,本性,性の,の植,植物,物で,であ,ある,るの,ので,で野,野菜,菜と,とし,して,て扱,扱わ,われ,れる,るこ,こと,とも,もあ,ある,る[,[2,2],]。 | | 4 | ぶどう | ブド,ドウ,ウ（,（葡,葡萄,萄、,、英,英名,"G,Gr,ra,ap,pe,e","、,、学,学名,Vi,it,ti,is,sp,pp,p.,.）,）は,は、,、ブ,ブド,ドウ,ウ科,(V,Vi,it,ta,ac,ce,ea,ae,e),のつ,つる,る性,性落,落葉,葉低,低木,木で,であ,ある,る。,。ま,また,た、,、そ,その,の果,果実,実の,のこ,こと,と。 | +----+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
これで検索用の文章は完成です。
2.FullText Indexの作成
MySQLで全文検索を行うためには、検索対象のカラムに「FullText」という種類のIndexを作成する必要があります。といっても作成方法は通常のインデックスと同じです。
では、最初に挙げたfruitsテーブルのdesctextカラムにfulltext indexを追加してみます。
mysql> alter table fruits add fulltext(`desctext`); Query OK, 0 rows affected, 1 warning (0.18 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> show index in fruits; +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | fruits | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | | | fruits | 1 | desctext | 1 | desctext | NULL | 4 | NULL | NULL | YES | FULLTEXT | | | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3.ストレージエンジンの設定
MySQLにデフォルトで搭載されているストレージエンジンでは、MyISAMとInnoDBが全文検索をサポートしています。今回はInnoDBを利用します。
基本的にはそのままでも利用できるのですが、ひとつだけ設定を変更したいと思います。
#/etc/my.cnf
[mysqld]
innodb_ft_min_token_size=2
innodb_ft_min_token_sizeはカラム中で検索対象とする単語の最低文字数を定義します。
デフォルト値は4であるため、そのままだと4文字以上の単語だけを探します。
今回は2-gramでdesctextカラムを登録したので、値を2に変更しました。これで2文字以上の単語を対象に検索を行ってくれます。
ここまでで準備は完了。いよいよSQLを発行してみます。
4.全文検索クエリの作成
いよいよ検索です。MySQLの全文検索における最も基本となる形は以下のようになります。
select * from テーブル名 where match(FullText Indexのカラム) against('検索ワード')
実際に検索をしてみます。
mysql> select `id`,`name` from fruits where match(`desctext`) against('果実'); +----+-----------+ | id | name | +----+-----------+ | 1 | メロン | | 3 | いちご | | 4 | ぶどう | +----+-----------+
※MyISAMの場合は全レコードの50%以上に含まれる言葉は無視されるため、上記の結果は得られません。
詳しくは公式リファレンスを参照して下さい。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.9.1 自然言語全文検索
注意点として今回は2-gramで分割しているため、検索ワードにもNgramを適用する必要があります。
/* 3文字では結果が得られない */ select `id`,`name` from fruits where match(`desctext`) against('メロン'); Empty set (0.00 sec)
では2-gramで3文字以上の言葉を検索する場合どうすればよいのか。結論からすると以下のとおりです。
mysql> select `id`,`name` from fruits where match(`desctext`) against('+"メロ ロン" in boolean mode'); +----+-----------+ | id | name | +----+-----------+ | 1 | メロン | +----+-----------+
against句の最後に「in boolean mode」を指定することで、全文検索に対して演算子を用いた条件付けを行うことができます。
in boolean modeの演算子
+演算子
「+」は単語の先頭につけることで「必ずその言葉が含まれなければならない」ことを表します。
例えば「果実」はメロン、いちご、ぶどうに含まれますが、「食用」はメロンといちごにのみ含まれるため、+演算子を用いた結果は次のとおりになります。
mysql> select `id`,`name` from fruits where match(`desctext`) against('+果実 +食用' in boolean mode); +----+-----------+ | id | name | +----+-----------+ | 1 | メロン | | 3 | いちご | +----+-----------+
ブランク
ブランクは暗黙的なOR条件となります。
例えば以下のSQLでは「双子"または"一粒を含む」という意味になるため、「双子（双子葉）」を含むみかんと「一粒」を含むいちごの両方が抽出されます。
mysql> select `id`,`name` from fruits where match(`desctext`) against('双子 一粒' in boolean mode); +----+-----------+ | id | name | +----+-----------+ | 3 | いちご | | 2 | ミカン | +----+-----------+
フレーズ検索（""）
前述の「メロン」検索で利用したフレーズ検索は、「""」（ダブルクウォート）で囲んだブランク区切りの単語について、それと全く同じ順番で単語が並んでいるデータを抽出します。
Ngramを用いた方法ではこのフレーズ検索を上手く使い、「キーワードをNgramで分割=>分割した1語1語をブランクで連ねる=>フレーズ検索の引数に渡してSQL発行」という処理を行う必要があります。
/* 「メロン」または「ミカン」を含む行を探す 条件。 この場合フレーズ中のブランクは単語の並び順を指定し、フレーズの間のブランクが「OR」の条件を果たす*/ mysql> select `id`,`name` from fruits where match(`desctext`) against('"メロ ロン" "ミカ カン"' in boolean mode); +----+-----------+ | id | name | +----+-----------+ | 1 | メロン | | 2 | ミカン | +----+-----------+ /* フレーズ検索と他演算子の併用も可能。 この場合「"ミカ カン"のフレーズを必ず含まなければならない」という意味 */ mysql> select `id`,`name` from fruits where match(`desctext`) against('"メロ ロン" +"ミカ カン"' in boolean mode); +----+-----------+ | id | name | +----+-----------+ | 2 | ミカン | +----+-----------+
これ以外にもNot条件、ワイルドカードの利用などが可能です。
詳細は公式リファレンスを見れば大体分かると思います。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.9.2 ブール全文検索
まとめ
次は実際にlike検索と全文検索の速度の違いについて確認したいと思います。また、InnoDB（やMyISAM）とMroonga等のストレージエンジンでは検索速度が異なる点についてもそのうち書いてみます。
参考にさせていただいたページ
MySQLで全文検索 - FULLTEXTインデックスの基礎知識｜blog｜たたみラボ
InnoDB のフルテキストインデックスで日本語 NGRAM
- 作者: 西沢夢路
- 出版社/メーカー: SBクリエイティブ
- 発売日: 2012/04/27
- メディア: 単行本
- 購入: 2人 クリック: 10回
- この商品を含むブログ (9件) を見る
高速文字列解析の世界――データ圧縮・全文検索・テキストマイニング (確率と情報の科学)
- 作者: 岡野原大輔
- 出版社/メーカー: 岩波書店
- 発売日: 2012/12/27
- メディア: 単行本
- 購入: 15人 クリック: 324回
- この商品を含むブログ (5件) を見る
