MySQL5.7における全文検索の実装方法について

以前、MySQLのデータベースエンジンであるInnoDBの全文検索を導入するために、MySQL5.6から5.7へバージョンアップしたのですが、InnoDBにおける全文検索は思ったほどのパフォーマンスは得られず、全文検索に特化したMroongaエンジンを利用することにしました。

本記事ではInnoDBおよびMroongaの両方について紹介致します。

※ おすすめはMroongaです。

全文検索とは簡単にイメージを掴んで頂くと、Google検索のようなキーワードによるデータ検索機能です。

例えば、会社でPCを貸与してもらうとき、交通費などの精算を行うときなど、何か所属部署の上長に許可を頂く際は申請書を提出すると思います。ほとんどの会社では紙での申請ではなく、システム上で申請する会社が多いと思います。

このようなシステムにおいては、自分自身が登録した申請書や、自分が承認しなければならない申請書を検索したり、〇〇申請書やXX申請書といった申請書種類で検索したりなど、目的のデータを発見するために検索機能が備わっています。

※ 業務アプリケーションだけではなく、Amazonなどのショッピングサイトやゲームにおいても商品(アイテム)検索があるように、システム開発では必ずと言っていいほど検索機能に触れることになります。

しかしながら、業務系アプリケーションの検索機能はしばしば検索項目が多くなりがちで、例えば申請中、〇〇承認者承認待ち、XX承認者承認待ちなどといったステータスでの検索、申請書に振られる申請書番号や申請書のタイトルでの検索、申請書作成者の氏名や社員番号での検索などなど、個々の項目ごとに検索ワードを入力することが多いです。

【検索条件の項目が多い画面例】

 

上記のイメージのように検索項目が画面の半分近くを占め、検索結果が画面の半分にしか表示されない、よく使われる検索項目は1つ2つのみで他の項目はほとんど使われないなど、使い勝手が良くないことがほとんどです。

このような課題を解決する一つの手法として登場するのが全文検索になります。全文検索を用いることで、登録データの複数項目に対し、Google検索のような一つのキーワード入力で検索することが可能になります。

※ 特定項目のみにキーワードをヒットさせたい(他の項目のあいまい検索にヒットした結果を排除したい)というユーザ要望によってこのような画面になりがちです。その場合は詳細検索欄を設けて通常は折り畳み表示(意識的に開かない限り表示されない)を利用したりします。

MySQLでは5.6から日本語での全文検索をサポートしておりますが、全文検索を実現するためのキーワード生成が多少面倒であり、あまり使い勝手は良くありません。

MySQL5.6から5.7へバージョンアップすると、InnoDBエンジンにおいてngramパーサを標準で利用することができます。

ngramとはキーワードを1文字、2文字、3文字といった特定のn文字へとぶつ切りにして、入力した検索キーワードが曖昧であっても部分的にヒットするようにしてくれる技術です。

ngramパーサとは全文検索に利用したいキーワード項目を自動的にngramの形に変換してインデックスを作成してくれる機能です。インデックスとはデータベースに登録されたデータを高速に検索するための索引のことです。

MySQL5.7ではFULLTEXTインデックスを全文検索用の項目に付与することで、ngramパーサによって自動的にngramに基づいたインデックスを生成してくれます。そのため、MySQL5.6では全文検索用の項目をわざわざngramの形にあうように登録してFULLTEXTインデックスを付与する必要がありましたが、MySQL5.7ではその必要が無いため全文検索の導入が非常に楽になります。

なお、Mroongaを利用する場合は、Mroongaエンジンがngramをサポートしているため、MySQL5.6のままでも特に導入の手間に違いはありません。

 

---

MySQL5.7 InnoDBエンジンにおけるmy.cnfの設定サンプル

---

InnoDBエンジンでngramパーサを利用する場合には、MySQLの設定ファイルであるmy.cnfにngramで分割する文字数を設定することができます。標準では2文字になっています。

利用するパラメータは「ngram_token_size」です。

ngram_token_size=2

3文字、4文字と変更したい場合は上記の設定値を変更します。

 

---

MySQL5.7 InnoDBエンジンにおける検索キーワードテーブルのサンプル

---

全文検索を実装する場合、検索対象のテーブルにキーワード項目を設けるよりも、対象テーブルのキー項目とキーワード項目で構成された別テーブルを用意したほうが良いと思います。

FULLTEXTインデックスはデータの追加・更新・削除といった操作による再構築時に時間がかかるとされているためです。

また、複数の項目を連結してキーワード項目は生成するため、比較的項目のサイズは大きくなり、分離されていないと検索結果の読み込み時にも時間がかかってしまいます。

例えば商品の購入履歴を全文検索する場合、購入履歴テーブルとは別にキーワードテーブルを用意します。

キーワードについては、購入履歴テーブルをもとに商品テーブルや商品の数量単位などを管理する単位テーブルなどから商品名や数量単位名等、キーワード検索の対象にしたい項目を選出し、半角スペース区切りで連結してキーワードテーブルに保存します。

 

-- 商品テーブル
CREATE TABLE `ITEM` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `ITEM_NUMBER` varchar(30) NOT NULL,
  `ITEM_NAME` varchar(100) NOT NULL,
  `UNIT_CODE` varchar(10) DEFAULT NULL,
  `UNIT_PRICE` decimal(15,0) DEFAULT NULL,
  `TAX_CODE` decimal(15,0) DEFAULT NULL,
  `INSERT_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `INSERT_USER` varchar(10) NOT NULL DEFAULT 'SYSTEM',
  `UPDATE_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATE_USER` varchar(10) NOT NULL DEFAULT 'SYSTEM',
  `DELETE_FLG` char(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `idx_itm_item_number` (`ITEM_NUMBER`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 単位テーブル
CREATE TABLE `UNIT` (
  `UNIT_CODE` varchar(10) DEFAULT NULL,
  `UNIT_NAME` varchar(20) NOT NULL,
  `INSERT_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `INSERT_USER` varchar(10) NOT NULL DEFAULT 'SYSTEM',
  `UPDATE_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATE_USER` varchar(10) NOT NULL DEFAULT 'SYSTEM',
  `DELETE_FLG` char(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`UNIT_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 購入履歴テーブル
CREATE TABLE `ITEM_P_HISTORY` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `USER_ID` varchar(10) NOT NULL,
  `PURCHASE_DATE` timestamp NOT NULL,
  `ITEM_NUMBER` varchar(30) NOT NULL,
  `QUANTITY` decimal(15,3) DEFAULT NULL,
  `UNIT_CODE` varchar(10) DEFAULT NULL,
  `UNIT_PRICE` decimal(15,0) DEFAULT NULL,
  `AMOUNT` decimal(15,0) DEFAULT NULL,
  `AMOUNT_TAX` decimal(15,0) DEFAULT NULL,
  `INSERT_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `INSERT_USER` varchar(10) NOT NULL DEFAULT 'SYSTEM',
  `UPDATE_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATE_USER` varchar(10) NOT NULL DEFAULT 'SYSTEM',
  `DELETE_FLG` char(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `idx_iph_item_number` (`ITEM_NUMBER`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- キーワードテーブル
CREATE TABLE `ITEM_P_HISTORY_KEYWORD` (
  `HISTORY_ID` bigint(20) NOT NULL,
  `HISTORY_KEYWORD` mediumtext,
  `INSERT_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `INSERT_USER` varchar(10) NOT NULL DEFAULT 'SYSTEM',
  `UPDATE_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATE_USER` varchar(10) NOT NULL DEFAULT 'SYSTEM',
  `DELETE_FLG` char(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`HISTORY_ID`),
  FULLTEXT KEY `idx_iphk_history_keyword` (`HISTORY_KEYWORD`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

なお、キーワードを生成する場合、例えば「R-1」のように「-」などの記号が含まれているキーワードについては記号を除去して「R 1」に変換します。全文検索の構文上、「-」や「+」といった記号に特別な意味を持つためです。

ただし、ngram_token_size=2の場合、上記のキーワード「R 1」に対し、ngram_token_sizeより短いキーワード「R」や「R*」で検索してもヒットしません。

※「R*」は前方一致検索になりますが、そもそものキーワード「R」がトークンサイズより短いためヒットしません。

そのため、どうしても「R」や「1」の1文字で検索したいといった場合には、「RR」「11」といったキーワードを足して生成しておくことで検索結果に表示されます。

 

---

MySQL5.7 InnoDBエンジンにおける検索キーワード処理サンプル

---

検索画面で入力されたキーワードを受け取り、SQLに渡すキーワードを生成するサンプルになります。

基本的にAND検索による絞り込みを前提にしています。

サンプルはphpコードで記載していますが、別の言語でもやることは一緒です。

サンプルコード中の正規表現における文字プロパティコードについては以下公式ページをご参考ください。

https://www.php.net/manual/ja/regexp.reference.unicode.php

 

【サンプル】

(1) 入力されたキーワードから記号を除去する

ngramのインデックスには「-」や「+」といった記号は含まれないため、入力されたキーワードから数字、アルファベット、アンダースコア以外の文字を除去します。また、全文検索の構文上、「-」や「+」といった記号に特別な意味を持つため、不要な文字を除去します。

(2) ngram_token_sizeに設定した文字数より短いキーワードはワイルドカード検索となるようにする

ngram_token_sizeに設定した文字数より短い文字にはヒットしないため、キーワードのヒット率を上げるためにワイルドカードを利用します。ただし、前方一致のワイルドカードのみなので、ヒットしないケースがあります。

(3) IN BOOLEAN MODEでAND検索を実施するため、キーワードの先頭に「+」を付与する

「+」を付与するとAND検索になり、何も付与しないとOR検索になります。「-」を付与するとNOTの意味になり、対象のキーワードを含んでいないものになります。

$keyword = "R-1 ドリンク"; //画面から入力されたキーワード
$searchkeyword = "";
if (isset($keyword)) {
    $words = preg_replace('/[^\p{L}\p{N}_]+/u', ' ', $keyword); // 記号を除外(+や-は利用できないため)
    $newkeyword = "";
    if (isset($words) && $words != "") {
        foreach (explode(' ', $words) as $token) {
            if (trim($token) == "") { continue; }
            $token_length = mb_strlen($token);
            if ($token_length < 2) {
                $token = $token . "*";  // ngram_token_sizeよりも短いワードは*でワイルドカード検索
            }
            $newkeyword .= "+" . $token . " ";
        }
    }
    $searchkeyword = trim($newkeyword); // 余計な前後の半角スペースを除去
}

上記サンプルでは、最終的に$searchkeywordという変数に「+R* +1* +ドリンク」というキーワードが生成されます。

発行するSQLは以下のようになります。

SELECT * FROM `ITEM_P_HISTORY`
WHERE `ID` IN (
 SELECT `HISTORY_ID` FROM `ITEM_P_HISTORY_KEYWORD` 
 WHERE MATCH(`HISTORY_KEYWORD`) AGAINST('+R* +1* +ドリンク' IN BOOLEAN MODE)
);

 

---

MySQL5.7 Mroongaエンジンのインストール方法

---

Mroongaエンジンのインストールにつきましては、お使いのOS環境にて手順が異なります。

詳細は公式ページを参照ください。

Mroonga インストール手順

例えばCentOS7におけるインストール方法は以下の通りです。

sudo yum install -y https://packages.groonga.org/centos/groonga-release-latest.noarch.rpm
sudo yum install -y http://repo.mysql.com/mysql-community-release-el7-7.noarch.rpm
sudo yum install -y yum-utils
sudo yum-config-manager --disable mysql56-community
sudo yum-config-manager --enable mysql57-community
sudo yum install -y --enablerepo=epel mysql57-community-mroonga
sudo systemctl start mysqld
tmp_password=$(sudo grep 'A temporary password' /var/log/mysqld.log | sed -e 's/^.*: //')
sudo mysqladmin -u root --password="${tmp_password}" password

 

---

MySQL5.7 Mroongaエンジンにおけるmy.cnfの設定サンプル

---

Mroongaを利用する場合はmy.cnfにトークナイザの設定を行います。

トークナイザの種類については公式チュートリアルに記載があります。

トークナイザ一覧

 

英数字かなをBigramで検索するにはTokenBigramSplitSymbolAlphaDigitを利用します。

 

[mysqld]
mroonga_default_tokenizer=TokenBigramSplitSymbolAlphaDigit

 

---

MySQL5.7 Mroongaエンジンにおけるキーワードテーブルサンプル

---

キーワードテーブルは基本的にInnoDBエンジンの場合と同じ構成になりますが、選択するエンジンはMroongaになります。

 

-- キーワードテーブル
CREATE TABLE `ITEM_P_HISTORY_KEYWORD` (
  `HISTORY_ID` bigint(20) NOT NULL,
  `HISTORY_KEYWORD` mediumtext,
  `INSERT_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `INSERT_USER` varchar(10) NOT NULL DEFAULT 'SYSTEM',
  `UPDATE_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATE_USER` varchar(10) NOT NULL DEFAULT 'SYSTEM',
  `DELETE_FLG` char(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`HISTORY_ID`),
  FULLTEXT KEY `idx_iphk_history_keyword` (`HISTORY_KEYWORD`)
) ENGINE=Mroonga DEFAULT CHARSET=utf8;

 

---

MySQL5.7 Mroongaエンジンにおける検索キーワードの渡し方サンプル

---

Mroongaを利用すると、Bigramであっても1文字以上の検索キーワードにヒットするようになります。そのため、InnoDBエンジンの際にはワイルドカードを付与しておりましたが基本的に不要になります。

 

$keyword = "R-1 ドリンク"; //画面から入力されたキーワード
$searchkeyword = "";
if (isset($keyword)) {
    $words = preg_replace('/[^\p{L}\p{N}_]+/u', ' ', $keyword); // 記号を除外(+や-は利用できないため)
    $newkeyword = "";
    if (isset($words) && $words != "") {
        foreach (explode(' ', $words) as $token) {
            if (trim($token) == "") { continue; }
            $newkeyword .= "+" . $token . " ";
        }
    }
    $searchkeyword = trim($newkeyword); // 余計な前後の半角スペースを除去
}

 上記サンプルでは、最終的に$searchkeywordという変数に「+R +1 +ドリンク」というキーワードが生成されます。

発行するSQLは以下のようになります。

SELECT * FROM `ITEM_P_HISTORY`
WHERE `ID` IN (
 SELECT `HISTORY_ID` FROM `ITEM_P_HISTORY_KEYWORD` 
 WHERE MATCH(`HISTORY_KEYWORD`) AGAINST('+R +1 +ドリンク' IN BOOLEAN MODE)
);

 

---

MySQL5.7 InnoDBエンジンのテーブルとMroongaエンジンのテーブルは共存可能

---

通常のトランザクションデータ、マスタデータを保存するテーブルについてはInnoDB、キーワードテーブルはMroongaと、使用するエンジンをわけることが可能です。

Mroongaエンジンはトランザクション機能を提供しないため、業務データを保存するためのエンジンとしては不向きです。

そのため、Mroongaを利用する場合はキーワードテーブルは独立したテーブルとして定義したほうが良いと思います。

※ FULLTEXTインデックスはデータの追加・更新・削除といった操作による再構築時に時間がかかるという理由もありますが、Mroongaの場合は上記理由からしても分ける必要があります。

 

---

MySQL5.7 全文検索導入 まとめ

---

InnoDBとMroonga両方を紹介致しましたが、正直なところInnoDBの全文検索機能を利用するメリットはあまりありません。

全文検索を導入するのであれば検索パフォーマンス、キーワードのヒット率に優れたMroongaを利用したほうが良いと思います。

(1) Mroongaをインストールする (Mroonga インストール手順)

※ CentOS7の場合は以下の通り。

sudo yum install -y https://packages.groonga.org/centos/groonga-release-latest.noarch.rpm
sudo yum install -y http://repo.mysql.com/mysql-community-release-el7-7.noarch.rpm
sudo yum install -y yum-utils
sudo yum-config-manager --disable mysql56-community
sudo yum-config-manager --enable mysql57-community
sudo yum install -y --enablerepo=epel mysql57-community-mroonga
sudo systemctl start mysqld
tmp_password=$(sudo grep 'A temporary password' /var/log/mysqld.log | sed -e 's/^.*: //')
sudo mysqladmin -u root --password="${tmp_password}" password

 

(2) my.cnfにトークナイザの設定を追記する (トークナイザ一覧)

[mysqld]
mroonga_default_tokenizer=TokenBigramSplitSymbolAlphaDigit

 

(3) FULLTEXTインデックス項目を持つMroongaエンジンのキーワードテーブルを個別に用意する

-- キーワードテーブル
CREATE TABLE `ITEM_P_HISTORY_KEYWORD` (
  `HISTORY_ID` bigint(20) NOT NULL,
  `HISTORY_KEYWORD` mediumtext,
  `INSERT_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `INSERT_USER` varchar(10) NOT NULL DEFAULT 'SYSTEM',
  `UPDATE_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATE_USER` varchar(10) NOT NULL DEFAULT 'SYSTEM',
  `DELETE_FLG` char(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`HISTORY_ID`),
  FULLTEXT KEY `idx_iphk_history_keyword` (`HISTORY_KEYWORD`)
) ENGINE=Mroonga DEFAULT CHARSET=utf8;

 

(4) 画面から入力されたキーワードからは記号や前後の不要なスペースを取り除いてSQL文に代入する

 

$keyword = "R-1 ドリンク"; //画面から入力されたキーワード
$searchkeyword = "";
if (isset($keyword)) {
    $words = preg_replace('/[^\p{L}\p{N}_]+/u', ' ', $keyword); // 記号を除外(+や-は利用できないため)
    $newkeyword = "";
    if (isset($words) && $words != "") {
        foreach (explode(' ', $words) as $token) {
            if (trim($token) == "") { continue; }
            $newkeyword .= "+" . $token . " ";
        }
    }
    $searchkeyword = trim($newkeyword); // 余計な前後の半角スペースを除去
}

 上記サンプルでは、最終的に$searchkeywordという変数に「+R +1 +ドリンク」というキーワードが生成されます。

※ BOOLEANモードで検索する

SELECT * FROM `ITEM_P_HISTORY`
WHERE `ID` IN (
 SELECT `HISTORY_ID` FROM `ITEM_P_HISTORY_KEYWORD` 
 WHERE MATCH(`HISTORY_KEYWORD`) AGAINST('+R +1 +ドリンク' IN BOOLEAN MODE)
);

 

以上です。