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

Slide 1

Slide 1 text

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

Slide 12

Slide 12 text

Slide 13

Slide 13 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 全文検索インデックス • トークンと文書の関係を表現した転置インデックスを使って文書を探す 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

Slide 14

Slide 14 text

Slide 15

Slide 15 text

Slide 16

Slide 16 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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

Slide 17

Slide 17 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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)

Slide 18

Slide 18 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 日本語全文検索機能の使い方 • 以下の資料を参照（資料は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

Slide 19

Slide 19 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 既知の問題 • ”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

Slide 20

Slide 20 text

Slide 21

Slide 21 text

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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

Slide 24

Slide 24 text

Slide 25

Slide 25 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. geometryデータ型 • OpenGISジオメトリモデルに基づいた以下のデータが使用可能 – GEOMETRY、GEOMETRYCOLLECTION – 点：POINT、MULTIPOINT – 線：LINESTRING、MULTILINESTRING – 多角形：POLYGON、MULTIPOLYGON ※GEOMETRYには、POINT、LINESTRING、POLYGONを格納可能 ※GEOMETRYCOLLECTIONには、MULTIPOINT、MULTILINESTRING、MULTIPOLYGONを格納可能 25

Slide 26

Slide 26 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

Slide 33

Slide 33 text

Slide 34

Slide 34 text

Slide 35

Slide 35 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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

Slide 36

Slide 36 text

Slide 37

Slide 37 text

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 参考資料、ドキュメント • ドキュメントストアのチュートリアル – 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