MySQL 5.7 InnoDB 日本語全文検索

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

MySQL 5.7 InnoDB 日本語全文検索（その1） Yoshiaki Yamasaki / 山﨑由章 MySQL Senior Sales Consultant, Asia Pacific and Japan

updated: 2016/09/30

Copyright © 2016, Oracle and/or its affiliates. All rights reserved.

Safe Harbor Statement

以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことはできません。以下の事項は、マテリアルやコード、機能を提供することをコミットメントするものではない為、購買決定を行う際の判断材料になさらないで下さい。オラクル製品に関して記載されている機能の開発、リリースおよび時期については、弊社の裁量により決定されます。

2


第2回、第3回の資料

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

• MySQL 5.7 InnoDB 日本語全文検索（その3） http://www.slideshare.net/yoyamasaki/20160929-inno-dbftsjp

3

http://www.slideshare.net/yoyamasaki/mysql-57-innodb






http://www.slideshare.net/yoyamasaki/20160929-inno-dbftsjp







全文検索とは？

4


全文検索とは？

•文書内の要素を検索する –文字列を格納するデータ型が対象

– CHAR, VARCHAR, TEXT

•検索文字列 –単語の組み合わせ

–フレーズ: “検索する文字列”

–ワイルドカード: *

–ブール全文検索演算子: +, -, ~

–関連重み付け文字: <, >

5

6

全文検索インデックス無しでの検索

7

全文検索インデックス有りでの検索


全文検索の用途例

• コンテンツ管理 –情報に対する説明などのメタデータ

–検索結果により有用な情報を提供

•検索サービス –特定の用語やトークンを含むドキュメントを検索

–現在の表示に最も類似したドキュメントを検索

–ユーザが最も興味のあるデータを検索

8


MySQLの全文検索機能の歴史

9

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 10

MySQLの歴史

4.0 全文検索/GIS (MyISAM) 複数テーブルUPDATE/DELETE 組み込みライブラリ型サーバ

Oracle MySQL Sun

3.23 MyISAM InnoDB レプリケーション

5.1 プラグガブル・ストレージエンジン・アーキテクチャパーティショニングタスクスケジューラ

5.6 全文検索(InnoDB) memcached API UNDO表領域 Global Transaction ID マルチスレッドスレーブオンラインALTER TABLE トランスポータブル表領域

5.5 InnoDBがデフォルトに準同期型レプリケーション PERFORMANCE_SCHEMA

1.0-3.22以前ストレージエンジン (ISAM, HEAP) マルチスレッド Windows対応/64bit対応日本語文字コード (SJIS/UJIS)

5.0 ストアドプロシージャストアドファンクションカーソル/トリガ/ビュー XAトランザクション INFORMATION_SCHEMA

4.1 Unicode対応サブクエリ CSV, ARCHIVE ndbcluster

1995 2000 2005 2010 2015

5.7 2015年10月21日 GA 全文検索CJK対応/GIS (InnoDB) 新コストモデルオプティマイザロスレスレプリケーションマルチソースレプリケーショングループレプリケーションセキュリティ強化データディクショナリ NoSQLオプション

・ 1995年：スウェーデンにてMySQL AB設立・(2005年：オラクルがInnobase Oyを買収) ・ 2008年：Sun MicrosystemsがMySQL ABを買収・ 2010年：オラクルがSun Microsystemsを買収


全文検索機能の歴史

• MySQL 5.5以前 –MyISAMでのみ全文検索機能が使えた

–日本語対応できていなかった

• MySQL 5.6

– InnoDBで全文検索機能が使えるようになった • トランザクションサポート、ACID、MVCC

• パフォーマンスの向上

–日本語対応できていなかった

11


全文検索機能の歴史

• MySQL 5.7

– InnoDB全文検索の日本語対応 ※CJK対応(中国語、日本語、韓国語対応) • N-gram：日本語、中国語、韓国語

• Mecab：日本語

–プラガブル・全文検索パーサーをサポート

12


全文検索機能の仕組み

13


全文検索インデックス

• トークンと文章の関係を表現した転置インデックス

14

This movie is about a boy going to war.

This movie is about a

girl starting an auto-

shop.

This movie is about

flowers.

a about an are as at be by com de en for from

how i in is it la of

on or that the this to

was what when where

who will with und

the www

Min Token Size

Max Token Size

Document 1

Document 2

Document 3

Stop Words Token Size

Full Text / Inverted Index

ID TOKEN DOCUMENT

1 movie 1,2,3

2 boy 1

3 girl 2

4 going 1

5 starting 2

6 war 1

7 auto-shop 2

8 flowers 3

Token Filters Documents

Tokenizer

Tokenizer

Indexer

Indexer


InnoDB全文検索機能の日本語対応

15


全文検索機能の日本語対応とは？

•日本語の文章を字句解析して、全文検索用のインデックスを作成できる –英語などは、スペースを区切り文字として字句解析できるが、日本語には区切り文字が無いため、追加の字句解析機能が必要

16


MySQL 5.7でサポートされた日本語の字句解析方法

• N-gram：一定の文字数で切りだして字句解析する手法 –デフォルトではbi-gram(2文字単位)

• MeCab：オープンソースの形態素解析エンジン –日本語の辞書をベースに字句解析する

17


MySQL 5.7での強化点

• N-gramによる日本語、中国語、韓国語サポート –N文字区切りでトークンを検出

18

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 | 15 | 2 | 1 | 0 | | ール | 1 | 16 | 3 | 1 | 3 | | ルと | 1 | 1 | 1 | 1 | 6 | | とグ | 1 | 1 | 1 | 1 | 9 | | グル | 1 | 7 | 2 | 1 | 12 | | ルー | 1 | 16 | 3 | 1 | 15 | | ープ | 1 | 7 | 2 | 1 | 18 | | プベ | 1 | 1 | 1 | 1 | 21 | | 環境 | 1 | 1 | 1 | 1 | 21 | | ベー | 1 | 7 | 4 | 1 | 24 | +--------+--------------+-------------+-----------+--------+----------+ 10 rows in set (0.01 sec)c


MySQL 5.7での強化点

• MeCabによる日本語サポート –MeCab（オープンソースの日本語辞書）を使ってトークンを検出

19

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 | +--------------------+--------------+-------------+-----------+--------+----------+ 10 rows in set (0.00 sec)


InnoDB日本語全文検索機能使用方法事前準備

20


事前準備

• N-gram

–特別な準備は不要

• MeCab

–MeCabパーサープラグインをインストールする

21


MeCabパーサープラグインのインストール方法

• 1．システム変数の設定(my.cnfファイルの編集)

• 2．使用する辞書の指定(mecabrcファイルの編集)

• 3．MySQLサーバー再起動

• 4．MeCabパーサープラグインのインストール

• 5．インストール後の確認

22

※詳細情報(マニュアル) 12.9.9 MeCab Full-Text Parser Plugin http://dev.mysql.com/doc/refman/5.7/en/fulltext-search-mecab.html

http://dev.mysql.com/doc/refman/5.7/en/fulltext-search-mecab.html









•以下の例のようにシステム変数を設定する

23

1．システム変数の設定(my.cnfファイルの編集)

character_set_server=utf8mb4 loose-mecab-rc-file=/usr/local/mysql/lib/mecab/etc/mecabrc innodb_ft_min_token_size=2



• mecab-rc-file

–mecabrcファイルのパスを指定 • 通常は MYSQL_HOME/lib/mecab/etc/mecabrc に存在

• rpmでインストール時(64bit環境)：/usr/lib64/mysql/mecab/etc/mecabrc

• Windows環境でインストーラー使用時： C:¥Progra~1¥MySQL¥MYSQLS~1.7¥lib¥mecab¥etc¥mecabrc (C:¥Program Files¥MySQL¥MySQL Server 5.7¥lib¥mecab¥etc¥mecabrcを指定すると後述の"INSTALL PLUGIN"コマンドがエラーになるため、空白を含めないように上記の通り指定)

–接頭辞 "loose-" を付けておかないと、MySQLサーバーの起動がエラーになる • MeCabパーサープラグインのインストール完了後は、接頭辞 "loose-" を外しても起動可能

(残しておいても問題無い)

24




• innodb_ft_min_token_size

–トークンの最小文字数を指定

–MeCabを使う時の推奨値は1 or 2 (デフォルト値は3)

25




•以下の例のようにdicdirに使用したい辞書を指定する (既存の"dicdir="のエントリはコメントアウトしておく)

※ipadic_utf-8は、utf8 と utf8mb4 をサポート

※MySQL 5.7のバイナリには、以下の辞書も同梱されている

・ipadic_euc-jp: ujis と eucjpms をサポート

・ ipadic_sjis: sjis と cp932 をサポート

26

2．使用する辞書の指定(mecabrcファイルの編集)

dicdir=/usr/local/mysql/lib/mecab/dic/ipadic_utf-8



• MySQL サーバーを再起動する

27

3．MySQLサーバー再起動



• MySQLサーバーにログインしてから以下のコマンドを実行し、 MeCabパーサープラグインをインストールする

28

4．MeCabパーサープラグインのインストール

mysql> INSTALL PLUGIN mecab SONAME 'libpluginmecab.so';

※Windows環境の場合は libpluginmecab.so ではなく libpluginmecab.dllを指定


InnoDB日本語全文検索機能使用方法全文検索インデックスの作成、検索

30


全文検索インデックスの作成

• N-gramを使った全文検索インデックスの作成例

–全文検索インデックス(FULLTEXT INDEX)のパーサーとしてngramを指定

–一意な識別子として、FTS_DOC_ID列を明示的に作成することを推奨(※)

31

CREATE TABLE ngram.articles ( FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(100), description VARCHAR(1000), FULLTEXT INDEX ngram_idx(description) WITH PARSER ngram ) ENGINE=InnoDB CHARACTER SET utf8mb4;

※詳細情報(マニュアル) 14.2.7.3 InnoDB FULLTEXT Indexes ⇒ InnoDB Full-Text Document ID and FTS_DOC_ID Column http://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html 14.2.13.3 FULLTEXT インデックス ⇒ InnoDB の全文ドキュメント ID および FTS_DOC_ID カラム http://dev.mysql.com/doc/refman/5.6/ja/innodb-fulltext-index.html

http://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html







http://dev.mysql.com/doc/refman/5.6/ja/innodb-fulltext-index.html








全文検索インデックスの作成

• MeCabを使った全文検索インデックスの作成例

–全文検索インデックス(FULLTEXT INDEX)のパーサーとしてmecabを指定

–一意な識別子として、FTS_DOC_ID列を明示的に作成することを推奨(※)

32

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;

※詳細情報(マニュアル) 14.2.7.3 InnoDB FULLTEXT Indexes ⇒ InnoDB Full-Text Document ID and FTS_DOC_ID Column http://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html 14.2.13.3 FULLTEXT インデックス ⇒ InnoDB の全文ドキュメント ID および FTS_DOC_ID カラム http://dev.mysql.com/doc/refman/5.6/ja/innodb-fulltext-index.html
















全文検索インデックスを使った検索

•デモをご覧下さい

33


転置インデックスの確認

• innodb_ft_aux_table に値を設定してから検索する – "スキーマ名/テーブル名" の形式で、転置インデックスを確認したいテーブル名を指定する

•全文検索インデックスの情報がディスクにフラッシュされていないと表示されない –任意のタイミングでフラッシュするためには、innodb_optimize_fulltext_only=ON に設定後、OPTIMIZE TABLEする

34

INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLEから確認可能

※詳細情報(マニュアル) 21.31.23 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table https://dev.mysql.com/doc/refman/5.7/en/innodb-ft-index-table-table.html 21.29.22 INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE テーブル https://dev.mysql.com/doc/refman/5.6/ja/innodb-ft-index-table-table.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-ft-index-table-table.html











https://dev.mysql.com/doc/refman/5.6/ja/innodb-ft-index-table-table.html













•確認例

35


mysql> SET GLOBAL innodb_ft_aux_table="ngram/articles"; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE ORDER BY doc_id, position LIMIT 10; Empty set (0.01 sec) mysql> SET GLOBAL innodb_optimize_fulltext_only=ON; Query OK, 0 rows affected (0.01 sec) mysql> OPTIMIZE TABLE ngram.articles; +----------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+----------+----------+----------+ | ngram.articles | optimize | status | OK | +----------------+----------+----------+----------+ 1 row in set (0.04 sec)



•確認例(続き)

36


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 | +--------+--------------+-------------+-----------+--------+----------+ | ロー | 2 | 16 | 2 | 2 | 0 | | ール | 2 | 17 | 3 | 2 | 3 | | ルと | 2 | 2 | 1 | 2 | 6 | | とグ | 2 | 2 | 1 | 2 | 9 | | グル | 2 | 8 | 2 | 2 | 12 | | ルー | 2 | 17 | 3 | 2 | 15 | | ープ | 2 | 8 | 2 | 2 | 18 | | 環境 | 2 | 2 | 1 | 2 | 21 | | プベ | 2 | 2 | 1 | 2 | 21 | | ベー | 2 | 8 | 4 | 2 | 24 | +--------+--------------+-------------+-----------+--------+----------+ 10 rows in set (0.01 sec)


A Real World Example

37


An Internal Content Management System

• I have tons of valuable business related content

– But it’s spread across various locations and formats • Wiki pages, PPTs, Word Docs, Txt docs, …

– How can I ingest, aggregate, and correlate this data

– How can I provide a useful search tool

• Let’s build something to vastly increase the value of our intranet content

– Something similar to Google Desktop search or Apple’s Spotlight • But for the vast amounts of data strewn across our company intranet

–We can then incorporate the search into a MySQL based intranet tool

38


Gathering The Contents of Our Existing Data

• Use any existing metadata that you already have

• Pull metadata from existing files

– Specialized tools to extract metadata • Exiftool to gather metadata on image files & Exif2maps to pull location data from image files

• Taglib to pull metadata from sound files

• `libreoffice –headess –convert-to …` to extract plain text from Office formats

• GNU Libextractor to pull metadata and location data from all file types

• Extract text content from binary format files (.ppt, .doc, .pdf, etc.)

– Apache Tika (originally part of Lucene) • Auto-detects file format and uses appropriate parsing library

• Extracts metadata and structured text content from all popular/common document and file formats

39


Apache Tika and MySQL

Extract

Plain Text

Load

Text Docs


Apache Tika Example

• Downloads, docs, etc. can be found at https://tika.apache.org

41

shell> java -jar tika-app-1.7.jar -z -t /tmp/MySQL_FTS.pptx Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 1 MySQL Full-Text Search Matt Lord MySQL Product Manager 2 Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 2 3 Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment …

https://tika.apache.org/


Apache Tika Example Cont. shell> ls /tmp/*.p* /tmp/MySQL_5.7_GIS.pptx /tmp/MySQL_5.7_GIS_reborn.pptx /tmp/MySQL_FTS.pptx /tmp/MySQLGroupReplication.pdf shell> for file in `ls /tmp/*.p*`; do java -jar tika-app-1.7.jar -z -t $file > $file.txt && echo -n "#DOC_END" >> $file.txt; done shell> ls /tmp/*.txt /tmp/MySQL_5.7_GIS.pptx.txt /tmp/MySQL_5.7_GIS_reborn.pptx.txt /tmp/MySQL_FTS.pptx.txt /tmp/MySQLGroupReplication.pdf.txt shell> sed -n '55,62'p /tmp/MySQLGroupReplication.pdf.txt Program Agenda MySQL Group Replication Background Zoom in: Major Building Blocks Zoom in: The Complete Stack


Our MySQL Table

mysql> show create table intranet_doc¥G *************************** 1. row *************************** Table: intranet_doc Create Table: CREATE TABLE ìntranet_doc` ( ìd` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `type` varchar(50) DEFAULT NULL, `fs_path` varchar(200) DEFAULT NULL, `doc_host` varchar(60) DEFAULT NULL, `txt_content` longtext, PRIMARY KEY (ìd`), KEY `type` (`type`), FULLTEXT KEY `txt_content` (`txt_content`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.01 sec)


Loading in the Text Content

shell> for file in `ls /tmp/*.txt`; do mysql -D intranet_search -e ¥ "load data infile '$file' into table intranet_doc ¥ lines terminated by '#DOC_END' (txt_content) SET fs_path='$file', ¥ doc_host='`uname -n`', ¥ type=substring_index(substring_index('$file', '.', -2), '.', 1) "; done mysql> select fs_path, type, doc_host from intranet_doc; +------------------------------------+------+-------------------+ | fs_path | type | doc_host | +------------------------------------+------+-------------------+ | /tmp/MySQL_5.7_GIS.pptx.txt | pptx | mylab.localdomain | | /tmp/MySQL_5.7_GIS_reborn.pptx.txt | pptx | mylab.localdomain | | /tmp/MySQL_FTS.pptx.txt | pptx | mylab.localdomain | | /tmp/MySQLGroupReplication.pdf.txt | pdf | mylab.localdomain | +------------------------------------+------+-------------------+ 4 rows in set (0.00 sec)


Our Final Search Query

• Search for PowerPoint docs that mention Apache Tika

45

mysql> SELECT fs_path, doc_host, type -> FROM intranet_doc -> WHERE type LIKE "ppt%" -> AND MATCH(txt_content) AGAINST ("+Tika"); +-------------------------+-------------------+------+ | fs_path | doc_host | type | +-------------------------+-------------------+------+ | /tmp/MySQL_FTS.pptx.txt | mylab.localdomain | pptx | +-------------------------+-------------------+------+ 1 row in set (0.00 sec)


What’s Next for MySQL Full-Text Search

52


Additional Features

• Improved performance

• More efficient disk space usage

• Support for stemming and facets

• Support for fuzzy string searches

• Support for aliases, synonyms, abbreviations, etc.

• Proximity search and use in relevancy scores

• Automatic ordering by relevancy

• What else would you like to see?

– Let us know!

53


Appendix : Additional Resources

• Manual

– https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html

• Community forum

– http://forums.mysql.com/list.php?107

• Apache Tika – https://tika.apache.org

• Report Full-Text bugs and submit feature requests

– http://bugs.mysql.com/

54

https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html



http://forums.mysql.com/list.php?107

https://tika.apache.org/

http://bugs.mysql.com/


Appendix : Additional Resources

• MySQL Server Blog の記事 – InnoDB 全文検索 : N-gram Parser

http://mysqlserverteam.com/innodb-%E5%85%A8%E6%96%87%E6%A4%9C%E7%B4%A2-n-gram-parser/

– InnoDB 全文検索 : MeCab Parser http://mysqlserverteam.com/innodb-%E5%85%A8%E6%96%87%E6%A4%9C%E7%B4%A2-mecab-parser/

55












http://mysqlserverteam.com/innodb-%E5%85%A8%E6%96%87%E6%A4%9C%E7%B4%A2-mecab-parser/









MySQL 5.7 InnoDB 日本語全文検索

Technology

Transcript of MySQL 5.7 InnoDB 日本語全文検索