[OracleDB vs MySQL] 日本語の全文検索をしたい！

2017.10.25

OracleDB と MySQL について、日本語の全文検索を取得する方法を比較します。
なお OracleDB は 12.1 Standard Edition 2、MySQLは主に 5.7 Community Edition（InnoDB）を想定しています。

OracleDB で日本語の全文検索をする

OracleDB で日本語の全文検索するには、Oracle Text を使用します。

Oracle Text は Oracle Database 本体のライセンスだけで利用可能です。
追加のオプション・ライセンスは必要ありません。
また、Database Configuration Assistant（DBCA）を使用して作成した場合、Oracle Text はデフォルトでインストールされます。

１．権限の付与

Oracle Text の管理ユーザー（ctxsys）でテキスト索引を作成するユーザに対して CTXAPP ロールを付与します。

SQL> connect ctxsys/<パスワード>
SQL> grant ctxapp to textuser;
2． 表の作成 と データの挿入
SQL> connect textuser/<パスワード>
SQL> create table test (
  2    id number primary key,
  3    text varchar2(200) );
SQL> insert into test ( id, text ) values ( 1, '神奈川県　横浜市' );
SQL> insert into test ( id, text ) values ( 2, '神奈川県　足柄下郡　湯河原町' );
SQL> insert into test ( id, text ) values ( 3, '神奈川県　足柄上郡　山北町' );
SQL> commit;
3．レクサーの設定(定義)

索引付けを行なう際の言語を指定します。

SQL> connect textuser/<パスワード>
SQL>
SQL> --プリファレンス作成: test_lexer プリファレンスを JAPANESE_VGRAM_LEXERレクサーとして作成
SQL> execute ctx_ddl.create_preference('test_lexer','JAPANESE_VGRAM_LEXER');
4． テキスト検索の実行

“山北”という文字を含むデータを検索します。

SQL> col text for a30
SQL> select id, text from test
  2   where contains ( text, '山北') > 0;

  ID TEXT
---- ------------------------------
   3 神奈川県　足柄上郡　山北町

 

詳細は、下記を確認して下さい。

Oracle Text 詳細解説
https://www.oracle.com/jp/a/tech/docs/technical-resources/oracletext-ver12.pdf

MySQL で日本語の全文検索をする

MySQL で日本語の全文検索するには、以下いずれかを使用します。

  • N-gram　一定の文字数で切り出して字句解析する。デフォルトでは bi-gram(2文字単位)
  • MeCab 　日本語の辞書をベースに字句解析する
1．事前準備
  • N-gram　特別な準備は不要
  • MeCab　 MeCabパーサープラグインをインストールする
2．全文検索インデックスの作成
mysql> --MeCabを使った全文検索インデックスの作成例
mysql> CREATE TABLE mecab.articles(
         fts_doc_id  BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
         title       VARCHAR(100),
         description VARCHAR(1000),
         FULLTEXT INDEX mecab_idx(description) WITH PARSER mecab
         ) ENGINE=InnoDB CHARACTER SET utf8mb4;
3．転置インデックスの確認

InnoDB の FULLTEXT インデックスにより、「転置インデックス」が作成されます。
転置インデックスには、単語のリスト、および 単語ごとにその単語が出現するドキュメントのリストが格納されます。

転置インデックスの内容は INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE から確認可能です。
ただし全文検索インデックスの情報がディスクにフラッシュされていない場合、表示されません。
任意のタイミングでフラッシュを行うためには innodb_optimize_fulltext_only=ON に設定後、OPTIMIZE TABLE を実行してください。

mysql> --innodb_ft_aux_table に値を設定 
mysql> --"スキーマ名/テーブル名" の形式で転置インデックスを確認したいテーブル名を指定する 
mysql> SET GLOBAL innodb_ft_aux_table="macab/articles"

mysql> --全文検索インデックスの情報がディスクにフラッシュ 
mysql> SET GLOBAL innodb_optimize_fulltext_only=ON; 
mysql> OPTIMIZE TABLE mecab.articles;
+----------------+-----------+---------------+---------------+
| Table　　　　　| Op　　　　| Msg_type　　　| Msg_text　　　|
+----------------+-----------+---------------+---------------+
| mecab.atricles | optimize　| status　　　　| OK　　　　　　|
+----------------+-----------+---------------+---------------+

mysql> --転置インデックスを確認 
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE ORDER BY doc_id, position LIMIT 10;

+---------------+---------------+--------------+------------+---------+-----------+
| WORD　　　　　| FIRST_DOC_ID　| LAST_DOC_ID　| DOC_COUNT　| DOC_ID　| POSITION　|
+---------------+---------------+--------------+------------+---------+-----------+
| ロール　　　　|　　　　　　　1| 　　　　　　1| 　　　　　1|　　　　1|　　　　　0|
| グループ　　　|　　　　　　　1| 　　　　　　7| 　　　　　2|　　　　1| 　　　　12|
| 環境　　　　　|　　　　　　　1| 　　　　　　1| 　　　　　1|　　　　1| 　　　　21|
| ベース　　　　|　　　　　　　1| 　　　　　　7| 　　　　　2|　　　　1| 　　　　24|
| アクセス　　　|　　　　　　　1| 　　　　　　1| 　　　　　1|　　　　1| 　　　　36|
| コントロール　|　　　　　　　1| 　　　　　　1| 　　　　　1|　　　　1| 　　　　48|
| により　　　　|　　　　　　　1| 　　　　　　1| 　　　　　1|　　　　1| 　　　　66|
| mysql 　　　　|　　　　　　　1|　　　　　　16|　　　　　12|　　　　1| 　　　　78|
| dba 　　　　　|　　　　　　　1|　　　　　　16| 　　　　　4|　　　　1| 　　　　83|
| きめ細か　　　|　　　　　　　1| 　　　　　　1| 　　　　　1|　　　　1| 　　　　91|
+---------------+---------------+--------------+------------+---------+-----------+
4．全文検索関数を使用して検索

全文検索は、MATCH～AGAINST 構文を使用します。

mysql> --「ディスク」を含む全文検索
mysql> SELECT  * FROM mecab.articles 
       WHERE   MATCH (description)
       AGAINST ('ディスク' IN BOOLEAN MODE)/G

mysql> --「ディスク」と「トレンド」を含む全文検索
mysql> SELECT * FROM mecab.articles 
       WHERE  MATCH (description)
       AGAINST ('+ディスク+トレンド' IN BOOLEAN MODE)/G

mysql> --「ディスク」を含む かつ 「トレンド」を含まない全文検索
mysql> SELECT * FROM mecab.articles 
       WHERE  MATCH (description)
       AGAINST ('+ディスク-トレンド' IN BOOLEAN MODE)/G

 

詳細は、下記を確認して下さい。

MySQL 5.7 InnoDB 日本語全文検索
https://www.slideshare.net/yoyamasaki/20160209-inno-dbftsjp

MySQL 5.7 InnoDB 日本語全文検索（その２）
https://www.slideshare.net/yoyamasaki/mysql-57-innodb

タイトルとURLをコピーしました