全文検索もGISもMySQLで！MySQLの色々な使い方

Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
全文検索もGISもMySQLで！ MySQLの色々な使い方 updated: 2019/06/10 Yoshiaki Yamasaki / 山﨑由章 MySQL Senior Solution Engineer, Asia Pacific and Japan

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

MySQLの色々な使い方全文検索機能 GIS機能 MySQLドキュメントストア 3 1 2 3

全文検索とは？ • 複数の文書群から特定の文字列を検索すること – 文字列を格納するデータ型が対象 – CHAR, VARCHAR, TEXT • 全文検索の技術には主に走査型（grep型）と索引型（インデックス型）があるが、本日は索引型の全文検索について紹介 – 走査型 • 検索対象のデータを全て走査する（例：LIKE句を使った文字列の絞り込み検索） • 検索対象の増加に比例して検索速度が低下する – 索引型 • あらかじめ検索対象となる文書を走査して文書の索引（全文検索インデックス）を作成しておく • 検索対象が増加しても検索速度があまり低下しない 5

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

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

全文検索の用途例 • コンテンツ管理 – 情報に対する説明などのメタデータ – 検索結果により有用な情報を提供 • 検索サービス – 特定の用語やトークンを含むドキュメントを検索 – 現在の表示に最も類似したドキュメントを検索 – ユーザが最も興味のあるデータを検索 8

MySQLの全文検索機能の歴史 9

全文検索機能の歴史 • MySQL 5.5以前 – MyISAMでのみ全文検索機能が使えた – 日本語対応できていなかった • MySQL 5.6 – InnoDBで全文検索機能が使えるようになった • トランザクションサポート、ACID、MVCC • パフォーマンスの向上 – 日本語対応できていなかった 10

全文検索機能の歴史 • MySQL 5.7 – InnoDB全文検索の日本語対応 ※CJK対応(中国語、日本語、韓国語対応) • N-gram：日本語、中国語、韓国語 • Mecab：日本語 – プラガブル・全文検索パーサーをサポート 11

全文検索機能の仕組み 12

全文検索インデックス • トークンと文書の関係を表現した転置インデックスを使って文書を探す 13 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 全文検索インデックス(転置インデックス) 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 トークンフィルター文書 Tokenizer Tokenizer Indexer Indexer

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

MySQL 5.7でサポートされた日本語の字句解析方法 • N-gram：一定の文字数で切りだして字句解析する手法 – デフォルトではbi-gram(2文字単位) • MeCab：オープンソースの形態素解析エンジン – 日本語の辞書をベースに字句解析する 15

MySQL 5.7でサポートされた日本語の字句解析方法 • N-gramによる日本語、中国語、韓国語サポート – N文字区切りでトークンを検出 16 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（オープンソースの日本語辞書）を使ってトークンを検出 17 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)

日本語全文検索機能の使い方 • 以下の資料を参照（資料は5.7の時に作成したものですが、8.0でも同様の手順で使用できます） – MySQL 5.7 InnoDB 日本語全文検索（その1） http://www.slideshare.net/yoyamasaki/20160209-inno-dbftsjp • MeCabパーサー使用方法、全文検索インデックス作成方法、転置インデックス確認方法、など – 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 • ストップワードの解説、設定方法、など 18

既知の問題 • ”Baby”などの英単語を含む日本語の文書を正しく検索できない場合がある • トークナイズした後でストップワードを適用してしまうことが原因 – “a”,”by”はデフォルトのストップワードに含まれるため • 以下のバグが報告されていますが、まだ修正されていません (”Affects me”大歓迎です) – Bug#82330：Don't recursively-evaluate stopword after tokenize https://bugs.mysql.com/bug.php?id=82330 ※参考情報：MySQL 5.7のngramパーサーはアルファベットを含む文章を上手くトークナイズできない 19

デモをご覧ください 20

GIS機能の歴史 • MySQL 5.7 – Boost.GeometryというC++のオープンソースライブラリを採用して独自実装をやめ、 InnoDBでGIS関連機能を再実装した • geometryデータ型 • Spatialインデックス • Spatial関数（使える関数の種類も増加） • GeoHashサポート • GeoJSONサポート ※Boost.Geometryコミュニティとも活発に交流し、MySQLチームから Boost.Geometryへのコントリビュートも行っている 22

GIS機能の歴史 • MySQL 8.0 – OpenGIS標準準拠 – 演算、データ変換に役立つ各種のSpatial関数の追加 • st_x(geom, x) • st_y(geom, y) • st_srid(geom, srid) – MySQL 5.7で非推奨になった関数の廃止 –Geography サポート • st_distance() 関数 –Spatial Data、Spatial Index、Spatial関数のSRIDサポート 23

GIS機能の紹介 24

geometryデータ型 • OpenGISジオメトリモデルに基づいた以下のデータが使用可能 – GEOMETRY、GEOMETRYCOLLECTION – 点：POINT、MULTIPOINT – 線：LINESTRING、MULTILINESTRING – 多角形：POLYGON、MULTIPOLYGON ※GEOMETRYには、POINT、LINESTRING、POLYGONを格納可能 ※GEOMETRYCOLLECTIONには、MULTIPOINT、MULTILINESTRING、MULTIPOLYGONを格納可能 25

Spatial関数 • ジオメトリデータを扱える各種の関数 • 例 – ST_Distance() • 2つのジオメトリを入力し、2地点間の距離を出力 – ST_Distance_Sphere() • 2つのジオメトリを入力し、2地点間の最小球面距離を出力 – ST_Contains() • あるジオメトリに別のジオメトリが含まれているかどうかを判定する • MySQL 8.0で使えるSpatial関数一覧 – MySQL 8.0 Reference Manual / ... / 12.15.1 Spatial Function Reference https://dev.mysql.com/doc/refman/8.0/en/spatial-function-reference.html 26

GeoHash • 経緯度の情報を文字列化したもの – 例：グランフロント大阪タワーC（経度：135.495889、緯度：34.707472）のGeoHash ⇒ xn0m7q31ej9wuqt<<後略>> • 特徴 – 文字列なので、データベースで扱いやすい（インデックスも活用可能） – GeoHashの情報は点ではなく範囲（矩形のエリア）を表す – 文字列の桁数で精度を変えることができる • 10桁を超えると矩形の一辺が1m以下になるので、経緯度の変わりとしても十分利用可能 • 近接エリアの判定等に利用可能 27

GeoHash • MySQL 5.7以降では、GeoHashを扱うための関数が使用できる – ST_GeoHash()：経度、緯度(POINT型のデータでも可)を入力し、GeoHashを出力 – ST_LatFromGeoHash()：GeoHashを入力し、経度を出力 – ST_LongFromGeoHash()：GeoHashを入力し、緯度を出力 – ST_PointFromGeoHash()：GeoHashを入力し、POINT型のデータを出力 28 ※詳細を解説しているマニュアル MySQL 8.0 Reference Manual / ... / 12.15.10 Spatial Geohash Functions https://dev.mysql.com/doc/refman/8.0/en/spatial-geohash-functions.html

GeoJSON • 空間データを扱うためのフォーマットの一つ • JSONによる表現で空間データを扱える – 例：グランフロント大阪タワーC（経度：135.495889、緯度：34.707472）GeoJSONによる表現 ⇒ {"type": "Point", "coordinates": [135.495889, 34.707472]} 29

GeoJSON • MySQL 5.7以降では、GeoJSONを扱うための関数が使用できる – ST_AsGeoJSON()：ジオメトリ型のデータを入力し、GeoJSONデータを出力 – ST_GeomFromGeoJSON()：GeoJSONデータを入力し、ジオメトリ型のデータを出力 30 ※詳細を解説しているマニュアル MySQL 8.0 Reference Manual / ... / 12.15.11 Spatial GeoJSON Functions https://dev.mysql.com/doc/refman/8.0/en/spatial-geojson-functions.html

デモをご覧ください ※当日のデモ内容の一部は、以下の資料にまとめています。 MySQL 8.0にShapeファイルをインポートする https://speakerdeck.com/yoshiakiyamasaki/20190608-mysql8-dot-0nishapehuairuwoinpotosuru 31

33 NoSQL JSON ドキュメントスキーマレス JSON コレクション MySQL リレーショナルテーブル外部キー X Dev API SQL CRUD MySQL ドキュメントストア

34 リレーショナルデータベースハイブリッドデータベースドキュメントデータベース SQL リレーショナルテーブル外部キー NoSQL JSONドキュメントスキーマレスJSONコレクション

MySQL Connectors include X Dev API • Use SQL, CRUD APIs スキーマレスドキュメントおよびリレーショナルテーブルに対応 - Classic APIsに加えて、これらの全てが追加されます 35 Operation Document Relational Create Collection.add() Table.insert() Read Collection.find() Table.select() Update Collection.modify() Table.update() Delete Collection.remove() Table.delete() 参照) http://dev.mysql.com/doc/x-devapi-userguide/en/crud-operations-overview.html

アプリケーションからみたアーキテクチャドキュメントストアがどのように動作するか？ 36 フロントエンド CRUD リクエスト + JSON バックエンド MySQL JSON ドキュメントストアアプリケーション

各コンポーネントの役割ドキュメントストアがどのように動作するか？ 37 アプリケーションコネクタ MySQL X プラグイン DevAPI Protobuf / X Protocol / TCP/IP SQL InnoDB

MySQL Shell • Javascript, Python, SQL でスクリプト利用可能 • MySQLスタンダードプロトコルと Xプロトコルの両方をサポート • ドキュメント＆リレーショナルモデル • CRUDドキュメント＆リレーショナルAPI • テーブル、JSON、タブ区切りの出力フォーマット • インタラクティブ操作＆バッチ操作 MySQLの開発と管理のためのインターフェース 38

参考資料、ドキュメント • ドキュメントストアのチュートリアル – JavaScript https://dev.mysql.com/doc/refman/8.0/en/mysql-shell-tutorial-javascript.html – Python https://dev.mysql.com/doc/refman/8.0/en/mysql-shell-tutorial-python.html • X DevAPI User Guide – https://dev.mysql.com/doc/x-devapi-userguide/en/ • Getting started MySQL as Document Data Store ※日本語の注釈あり – https://www.slideshare.net/ChihiroIto1/getting-started-mysql-as-document-data-store-141740924 – デモプログラム(Java)のサンプルコード https://github.com/chiroito/msql_document_store 39

全文検索もGISもMySQLで！MySQLの色々な使い方

全文検索もGISもMySQLで！MySQLの色々な使い方

More Decks by YoshiakiYamasaki

Other Decks in Technology

Featured

Transcript