いろいろ考えるとn日本語の全文検索もnMySQLがいいね!

: author

須藤功平

: institution

日本MySQLユーザ会

: content-source

OSC2014 Tokyo/Fall

: date

2014/10/18

: allotted-time

45m

: theme

.

目標

日本語対応のn 全文検索機能をn (('note:(そこそこ仕組みをわかった上で)'))n 実装できる

前提

* MySQLを使っている
* まあまあかそこそこのデータ量
  * ビッグデータ云々じゃない\n
    (('note:(そういう人はHadoopの枠に行っているよね?)'))
* 日本語テキストを検索したい
* でも、全文検索をよく知らない

全文検索について

全文検索とは…

とりあえず動かそう

* データベース作成
* テーブル作成
* データ投入
* 全文検索!

データベース作成

# coderay sql
CREATE DATABASE full_text_search;
USE full_text_search;

テーブル作成

# coderay sql
CREATE TABLE memos (
  content TEXT
) DEFAULT CHARSET=utf8mb4;

データ投入

# coderay sql
INSERT INTO memos
  VALUES ("Hello world!"),
         ("Good-bye world!"),
         ("Hello MySQL!");

全文検索!

# coderay sql
SELECT *
  FROM memos
  WHERE content LIKE "%Hello%";
-- +--------------+
-- | content      |
-- +--------------+
-- | Hello world! |
-- | Hello MySQL! |
-- +--------------+

全文検索! - もっと

* AND
* OR
* 大文字小文字無視
* 日本語

全文検索! - AND

# coderay sql
SELECT *
  FROM memos
  WHERE content LIKE "%Hello%" AND
        content LIKE "%world%";
-- +--------------+
-- | content      |
-- +--------------+
-- | Hello world! |
-- +--------------+

全文検索! - OR

# coderay sql
SELECT *
  FROM memos
  WHERE content LIKE "%Good%" OR
        content LIKE "%MySQL%";
-- +-----------------+
-- | content         |
-- +-----------------+
-- | Good-bye world! |
-- | Hello MySQL!    |
-- +-----------------+

大文字小文字無視

# coderay sql
SELECT *
  FROM memos
  WHERE content LIKE "%mysql%";
-- +--------------+
-- | content      |
-- +--------------+
-- | Hello MySQL! |
-- +--------------+

大文字小文字無視 - 理由

# coderay sql
SHOW TABLE STATUS LIKE "memos"\G
-- ...
-- Collation: utf8mb4_general_ci
-- ...

Collation

* 照合順序(('note:(って言われてわかる?)'))
* 文字の比較ルール
  * 「a」と「b」はどっちが大きい?
  * 「a」と「A」は等しい?
* utf8mb4_general_ci
  * 同じようなアルファベットは同一視\n
    (('note:(直感的だけど雑な説明)'))

日本語 - データ投入

# coderay sql
INSERT INTO memos
  VALUES ("こんにちは"),
         ("こんばんは");

日本語 - 全文検索!

# coderay sql
SELECT *
  FROM memos
  WHERE content LIKE "%こんにち%";
-- +-----------------+
-- | content         |
-- +-----------------+
-- | こんにちは      |
-- +-----------------+

全文検索の実装方法まとめ

* (({DEFAULT CHARSET=utf8mb4}))
* (({INSERT}))
* (({LIKE "%キーワード%"}))
  * ANDもORも日本語も可
  * 大文字小文字無視も可

日本語をもっと!

* (('note:いわゆる'))全角アルファベット対応

全角アルファベット

# coderay sql
SELECT *
  FROM memos
  WHERE content LIKE "%Hello%";
-- +-----------------+
-- | content         |
-- +-----------------+
-- +-----------------+

Collation変更

# coderay sql
ALTER TABLE memos
  MODIFY COLUMN
    content
      TEXT
      COLLATE utf8mb4_unicode_ci;

utf8mb4_unicode_ci

* Unicode的に同じ文字を同一視\n
  (('note:(直感的だけど雑な説明)'))\n
  * 例:全角文字半角文字を同一視\n
    (('note:参考:MySQL 5.5 の unicode collation で同一視される文字'))\n
    (('note:http://tmtms.hatenablog.com/entry/20110416/mysql_unicode_collation'))
* utf8mb4_general_ciより遅い

全角アルファベット

# coderay sql
SELECT *
  FROM memos
  WHERE content LIKE "%Hello%";
-- +--------------+
-- | content      |
-- +--------------+
-- | Hello world! |
-- | Hello MySQL! |
-- +--------------+

採用を検討

* 機能は十分?
  * やりたいことと相談
* 性能は十分?
  * データ量・リソースと相談
  * 実データが望ましい!!!
  * Webの情報は参考程度で実際に計測

性能検討例

* データ:livedoorグルメ\n
  (('note:https://github.com/livedoor/datasets'))
  * 件数:約20万口コミ
* CPU:Core i7 2.80GHz

文字数の傾向

# coderay sql
SELECT
    AVG(CHAR_LENGTH(comment)) AS average,
    MIN(CHAR_LENGTH(comment)) as min,
    MAX(CHAR_LENGTH(comment)) as max
  FROM ratings_all;
-- average:  380.2013
-- min:        2
-- max:     6243

%ラーメン%

# coderay sql
SELECT COUNT(*) AS count
  FROM ratings_all
  WHERE comment LIKE "%ラーメン%";
-- count: 31428
-- 0.898sec

%ラーメン%の傾向

(('tag:center'))実行時間は総件数に比例

# RT

総件数, 時間(秒)

1000, 0.01
5000, 0.03
10000, 0.05
100000, 0.50
205832, 0.89

AND

# coderay sql
SELECT COUNT(*) AS count
  FROM ratings_all
  WHERE
    comment LIKE "%ラーメン%" AND
    comment LIKE "%焼き肉%";
-- count: 69
-- 1.01sec

ANDの傾向

(('tag:center'))条件数1の場合とあまり変わらない

# RT

総件数, 時間(秒), 条件数1

1000, 0.01, 0.01
5000, 0.03, 0.03
10000, 0.06, 0.05
100000, 0.57, 0.50
205832, 1.01, 0.89

OR

# coderay sql
SELECT COUNT(*) AS count
  FROM ratings_all
  WHERE
    comment LIKE "%ラーメン%" OR
    comment LIKE "%焼き肉%";
-- count: 31994
-- 1.37sec

ORの傾向

(('tag:center'))2倍いかないくらいには増える

# RT

総件数, 時間(秒), 条件数1

1000, 0.02, 0.01
5000, 0.05, 0.03
10000, 0.09, 0.05
100000, 0.77, 0.50
205832, 1.37, 0.89

考察

* 自分たちのデータ量は?
  * 各レコードのテキストサイズ
  * 総件数
* どのくらい性能が必要?
  * 1リクエストのレスポンスタイム
  * 単位時間あたりの処理数

考察例

* 自分たちのデータ量は?
  * 各テキストサイズ:400文字くらい\n
    (('note:(データセットと同じくらい)'))
  * 総件数:2,3万くらい
* どのくらい性能が必要?
  * s/req: 0.5秒以内
  * queries/s: 10

実行時間

(('tag:center'))0.2秒以内には終わりそう

# RT

総件数, 時間(秒)

10000, 0.05
100000, 0.50

スループット

* 1クエリー0.2秒
* 1秒で5クエリー
* CPUコア2つで10qpsいけそう

(('tag:center'))(('note:(実際は他の条件も加わる→もっと時間がかかるはず)'))

考察結果は?

* LIKEで十分?
  * 機能面と性能面を検討
  * MySQLでLIKEで日本語全文検索!\n
    (('note:(全文検索エンジンが必要ないなら使わなくてよい)'))
* LIKEだと不十分?
  * 機能面?性能面?
  * 別の選択肢を検討

別の選択肢

* MySQLベースの全文検索機能
* 全文検索サーバーと連携

全文検索機能のスキーマ

# coderay sql
CREATE TABLE ratings_all_index (
  comment TEXT,
  FULLTEXT INDEX (comment)
  -- ↑を追加するだけ
) DEFAULT CHARSET=utf8mb4;

全文検索機能の検索方法

# coderay sql
SELECT COUNT(*) AS count
  FROM ratings_all_index
  WHERE
    MATCH (comment)
    AGAINST ("+ラーメン +焼き肉"
               IN BOOLEAN MODE);

全文検索機能のよいところ

* 簡単!
* 高速な検索!
  * インデックスを使った検索\n
    (('note:(LIKEは逐次検索)'))
* MySQLとよく統合されている
  * データ登録→インデックス自動更新
  * トランザクションにも対応

全文検索機能の悪いところ

* 日本語未対応
* 更新が遅い

日本語未対応

* 対策
  * アプリケーション側で前処理\n
    (('note:参考:MySQL Casual Talks Vol.4'))\n
    (('note:「MySQL-5.6で始める全文検索 〜InnoDB FTS編〜」'))\n
    (('note:http://www.slideshare.net/y-ken/my-sql-56innodb-fts'))
* トレードオフ
  * インフラの管理コストと\n
    アプリのメンテコスト\n
    (('note:(簡単に使えるというメリットは減る)'))

更新が遅い

* ベンチマーク結果
  * 前述のスライドを参照
* 対策
  * 速いディスクを使う
  * あまり更新しない
* 検討ポイント
  * どのくらい更新があるか

全文検索機能のまとめ

* データは安全
  * トランザクションを使える
  * レプリケーションもできる
* 検索は速い・更新は遅い
* 日本語対応にはひと手間必要

全文検索機能を使う?

* そこそこのデータ量がある
* アプリ側のひと手間を\n
  許せるならアリ
* 更新が少ないならアリ

突然の質問

MySQLの特徴とn 言えば?n (('note:(期待する答えがでるまで聞きます)'))

プラグイン機能

* 一部の機能を追加できる
  * ストレージエンジン・UDF・…
* 全文検索機能も追加できる

全文検索プラグイン

Mroonga

Mroongaのスキーマ

# coderay sql
CREATE TABLE ratings_all_index (
  comment TEXT,
  FULLTEXT INDEX (comment)
  -- ↑と↓を追加するだけ
) ENGINE=Mroonga
  DEFAULT CHARSET=utf8mb4;

Mroongaの検索方法

# coderay sql
-- MySQL標準の方法と同じ
SELECT COUNT(*) AS count
  FROM ratings_all_index
  WHERE
    MATCH (comment)
    AGAINST ("+ラーメン +焼き肉"
               IN BOOLEAN MODE);

Mroongaのよいところ

* 簡単!
* 高速な検索と更新!
* 日本語対応!(('note:(開発者が日本人)'))
* MySQLとそれなりに統合
  * データ登録→インデックス自動更新

Mroongaの悪いところ

* トランザクション非対応
* NULL非対応
* 別途インストールが必要

トランザクション非対応

* 対策
  * レプリケーションをして、\n
    マスターをInnoDB、\n
    スレーブをMroongaにする
* 参考
  * 多くの全文検索システムは\n
    トランザクション非対応

NULL非対応

* 対策
  * NULLを使わない

別途インストールが必要

* 対策:パッケージを使う
* パッケージ
  * CentOS 6, 7
  * Fedora(公式)
  * Debian/Ubuntu 安定版リリース
  * Windows
  * OS X(Homebrew/MacPorts)

Mroongaのまとめ

* 日本語対応
* 検索も更新も速い
* インストール作業が必要
* 使うときは簡単

Mroongaを使う?

* そこそこのデータ量がある
* 更新が多い
* トランザクションとNULLが\n
  なくてもよいならアリ
* 開発者を信頼できるならアリ

ここまでの話のポイント

全文検索方法のn 詳細をn 知らなくてもn 使える

別の選択肢

* (('del:MySQLベースの全文検索機能'))
* 全文検索サーバーと連携

全文検索サーバー

* Solr
* Elasticsearch
* Groonga
* Sphinx(('note:(http://sphinxsearch.com/)'))
* Amazon CloudSearch

違い

* 機能面
  * 全文検索初心者が使う分には\n
    どれも不足なし
* 性能面
  * まあまあかそこそこのデータ量\n
    (('note:(1台のサーバーでさばける量)'))\n
    ならどれも十分な性能

機能面:補足1

* 全文検索対応の進め方1
  * いきなりカンペキを目指さない
  * まず動かして実際に試す
  * 改良したいという箇所に気づく
  * 1つずつ改良しながら\n
    仕組みを学んでいく

機能面:補足2

* 全文検索対応の進め方2
  * 詳しい人に相談
  * MySQLユーザ会のブースへ!

連携

# image
# src = images/integrate-with-full-text-server.svg
# relative_width = 90

アプリの動作:更新

# image
# src = images/full-text-server-update.svg
# relative_width = 90

アプリの動作:更新

* 更新時
  * トランザクション開始
  * MySQLにデータ投入
  * 全文検索サーバーにもデータ投入
  * トランザクション終了
* ロールバックはアプリの仕事

アプリの動作:検索

# image
# src = images/full-text-server-search.svg
# relative_width = 90

アプリの動作:検索

* 検索時
  * 全文検索サーバーで検索
  * 見つかったレコードIDを条件に\n
    MySQLで検索
  * スコアとか結果をマージして表示
* JOINはアプリの仕事
  * 全文検索と他の検索を混ぜるときは\n
    どうするか考えてみよう

アプリの開発

* 実装
  * ライブラリーを使う
  * 更新・検索をサポートしてくれる
* テスト
  * 各自全文検索サーバーを用意
  * 開発環境の用意が面倒になる\n
    (('note:(VagrantやDockerを使うといいかも)'))

インフラ

* MySQLとは別に\n
  全文検索サーバーを管理
  * パラメーターの設定
  * 落ちた時どうする?
* システムが必要なリソース増加
  * 例:専用マシンを追加

連携したときのよいところ

* SQLが苦手なクエリーを\n
  効率よく実現できる
  * ファセット・タグ検索
* チューニングできる
  * トークナイザーを変える
  * フレーズ検索→近傍検索
  * トークンの正規化方法を変える等…

トークナイザー

* 空白区切り(('note:(英語やタグの検索に便利)'))
* N-gram
  * 適合率↓検索漏れ↓
  * アルファベットの文章で遅い
* 形態素解析
  * 適合率↑検索漏れ↑
  * 新語に弱い

N-gram

* 文字種が多い言語に向いている
  * 日本語
* 文字種が少ないと効率が悪い
  * 英語
* 文字種により挙動を変えて改善
  * 日本語:N-gram、英語:単語単位

形態素解析

* 区切り方が複数パターンある
  * 全パターンインデックスに登録
  * ↑は検索漏れは↓が適合率も↓かも
* 検索時に使い分ける
  * ヒットしなかったら\n
    N-gramにフォールバック

フレーズ検索→近傍検索

* 「日野でラーメン」で検索
  * ○「日野でラーメン」
  * ×「日野でみそラーメン」
* 「日野 ... ラーメン」で検索
  * ○「日野でみそラーメン」
  * ○「日野で塩ラーメン」

正規化

* 英単語のステミング
* 濁点を無視する?しない?
  * すし=ずし
  * ハハ=パパ=ババ
* 同義語はいつ展開?

連携したときのよいところ

* SQLが苦手なクエリーを\n
  効率よく実現できる
  * ファセット・タグ検索
* チューニングできる
  * トークナイザーを変える
  * フレーズ検索→近傍検索
  * トークンの正規化方法を変える等…

連携したときの悪いところ

* メンテナンスコストが増える
  * 開発面でもインフラ面でも
* 必要なリソースが増える
  * ランニングコストが増える
* ある程度全文検索の知識が必要

サーバー連携のまとめ

* 日本語対応
* 検索も更新も速い
* チューニングできる
* 導入・運用は手間が増える
* 使うときも手間が増える

全文検索サーバーを使う?

* そこそこのデータ量がある
* チューニングしたい
* 導入・運用・開発の手間増加が\n
  割に合うならアリ

参考:PostgreSQL

* 日本語未対応
  * プラグインで対応
* 完全転置インデックスではない
  * インデックスだけ使うと誤検出あり
  * ↑の後にLIKEで誤検出を除去

まとめ

* LIKEで十分ならLIKEでいい
* LIKEで不足ならMroonga
* それでも不足なら
  * マスターデータはMySQL
  * 検索対象は全文検索サーバー

つまり!

いろいろ考えるとn 日本語の全文検索もn MySQLがいいね!n (('note:MySQLが役に立つね'))

お知らせ1

* MySQLユーザ会ブースあり
  * 隣はOracleのMySQLの人たち
* 17:15-別のMySQL枠あり
  * OracleのMySQLの人の話

お知らせ2

* MariaDBにMroongaバンドル!
  * 10.0.15から組み込み!
  * 別途インストールしなくてよい!

お知らせ3

* 検索エンジンについて知りたい
  * 「検索エンジン自作入門」
* いい肉の日に渋谷で\n
  Groongaイベント!
  * 「いい肉 Groonga」で検索
  * 自作本のサイン会をやるよ!

お知らせ4

* Groongaをもっと知りたい
  * Groongaドキュメント読書会
  * 詳細は↑で検索
  * Mroongaが使っている\n
    全文検索エンジンの理解を深める会
  * 1,2ヶ月に1回開催