「MySQL徹底入門 第4版 MySQL 8.0対応」の感想・備忘録2

「MySQL徹底入門 第4版 MySQL 8.0対応」の感想・備忘録1の続き

全文検索

N-gramとMeCab

パーサーはN-gramとMeCabがあり、デフォルトではN-gramになっている。

N-gram

N-gramでは、N=1はユニグラム(unigram)、N=2はバイグラム(bigram)、N=3はトライグラム(trigram)と呼ばれる。
例えば「徹底入門」という文字列の場合、バイグラムだと「徹底」「底入」「入門」と2文字ずつ索引付けされる。

ただし、この手法はMeCabに比べると「検索ノイズの発生」「インデックスサイズの肥大化」というデメリットがある。 「検索ノイズの発生」は、例えば「東京都」が「東京」「京都」で索引付けされるため「京都」での検索結果に含まれてしまう。

N-gramの使用方法

インデックス作成時にWITH PARSER ngramを追加するだけ。
CREATE TABLE hoge1(
  FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  body TEXT,
  FULLTEXT INDEX idx_fulltext_body(body) WITH PARSER ngram
);

MeCab

MeCabの使用方法

MeCabは辞書からキーワードを抽出するため、N-gramよりインデックスサイズが小さくなる。
ただし、辞書に登録されていない単語は、検索結果に含まれない。

  1. my.cnfの編集
    innodb_ft_min_token_size = 2
    loose-mecab-rc-file=$MYSQL_HOME/lib/mecab/etc/mecabrc
    ※ innodb_ft_min_token_sizeはデフォルトが3だが、MeCab使用時は1または2が推奨されている
    ※ mecab_rc_fileはMeCabのファイルの指定。
    ※ loose接頭辞を付けると、認識できない設定の場合でも起動時にエラーにならない。
  2. mecabrcの編集
    dicdir = /path/to/mysql/lib/mecab/lib/mecab/dic/ipadic_euc-jpdicdir = /usr/local/mysql/lib/mecab/dic/ipadic_utf-8に変更
  3. MySQLサーバを起動
  4. MeCabプラグインのインストール
    INSTALL PLUGIN mecab SONAME 'libpluginmecab.so';
    SHOW PLUGINS;でMeCabプラグインが有効化になっているかどうか確認可能
  5. インデックス作成時にWITH PARSER mecabを追加する

全文検索関連のキーワード

転置インデックス(Inverted Index)

全文検索では、単語の位置情報を格納する「転置インデックス」が作成される。
転置インデックスには「単語」「その単語を含む文書」をリストとして保持する「レコード単位転置インデックス」と、「文書中の中で単語が表れる位置」も保持する「単語単位転置インデックス」がある。
MySQLの全文検索では、単語単位位置インデックスが仕様されている。

インデックスを作成する際に、行と列を「転置」するから「転置インデックス」と呼ぶ。
例えば、「文書1」に「MySQL」「全文」「検索」、「文書2」に「MySQL」「検索」、という単語があるという情報を保持する場合、キーワードを列ではなく行として持つことで、キーワードを含む文書を1行で取得することができる。

文書1文書2
MySQL11
全文10
検索11

ストップワード

転置インデックスのサイズ肥大化を防ぐため、英語の場合はa, the, is, was,at, ofなどは対象外にしている。
これらのワードをストップワードと呼ぶ。

トークンサイズ

インデックスのサイズを小さくするために、キーワード(=トークン)を抽出するときに、最小・最大文字数を指定することができる。
日本語の場合、最小文字数は1または2に設定する。

パーサを使用しない全文検索

スペースを区切り文字としてパースするため、日本語では機能しない。

  1. テーブル作成
    CREATE TABLE hoge1(
      FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      body TEXT,
      FULLTEXT INDEX idx_fulltext_body(body)
    )
    FTS_DOC_ID列を含めなかった場合、自動的に非表示のFTS_DOC_ID列が作成されるが、明示的に作成した方がパフォーマンスが良い。
    FULLTEXT INDEXを作成すると、内部ではFTS_DOC_ID列にFTS_DOC_ID_INDEXというインデックスが自動生成される。
    ただし、テーブル定義には表示されない。
    SHOW CREATE TABLE hoge1では表示されない。SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXESでは表示される)

    ※ 2つの列での複合インデックスの場合
    CREATE TABLE hoge2(
      FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      title varchar(200), body TEXT,
      FULLTEXT INDEX idx_fulltext_title_body(title, body)
    )
    FULLTEXTの複合インデックスの場合、検索時に列を完全に一致させる必要がある。
    上記の例の場合、MATCH(title, body)でなければならない。
  2. データ登録
    INSERT INTO hoge1(body) VALUES('When I find myself in times of trouble');
  3. 検索実行
    SELECT文では、WHERE句にMATCH()句とAGAINST()句を指定する。
    SELECT * FROM hoge1 WHERE MATCH(body) AGAINST('+myself -hogehoge' IN BOOLEAN MODE);
    ブール検索では以下の演算子がサポートされている。
    1. 「+」 AND
    2. 「-」 NOT
    3. 「()」 グループ化。+(aaa bbb)とすると「aaaまたはbbb」が1つのAND条件となる。
    4. 「*」ワイルドカード

3つのモード

検索には3つのモードがあり、一般的にはブール検索が使われる。デフォルトは自然言語検索。

  1. 自然言語検索(NATURAL LANGUAGE MODE)
    OR検索しかできない。
    関連性の高い文書が上位になるようにソートされる。
  2. ブール検索(BOOLEAN MODE)
    任意の演算子を指定できるため、OR, AND, NOT, グルーピング,ワイルドカードなど柔軟な検索が可能。
  3. クエリー拡張検索
    内部で2回検索を実行する特殊な検索。

転置インデックスの確認

転置インデックスの内容を確認したい場合は、SET GLOBAL innodb_ft_aux_table=DB名/テーブル名;の後にSELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;で検索する。

INNODB_FT_INDEX_TABLEにはデータが即時反映されないため、最新の情報を確認する場合はSET GLOBAL innodb_optimize_fulltext_only=ON;の後にOPTIMIZE TABLE テーブル名;を実行する。

  1. SET GLOBAL innodb_optimize_fulltext_only=ON;
  2. OPTIMIZE TABLE テーブル名;
  3. SET GLOBAL innodb_ft_aux_table='DB名/テーブル名';
  4. SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;

全文検索の注意事項

メモリ使用量が増加する

  • データ更新時、更新内容はメモリ上にキャッシュされ、キャッシュがいっぱいになったらディスクにフラッシュされる。
  • テーブル毎のキャッシュサイズはシステム変数innodb_ft_cache_sizeであり、デフォルトは8MBである。
  • サーバ全体でのキャッシュサイズはシステム変数innodb_ft_total_cache_sizeであり、デフォルトは640MBである。

データ削除はすぐには反映されない

データ削除時は、IDをDELETEDテーブル(システムテーブル)に記録だけし、インデックスの更新を遅延して実行される。
即座に反映したい場合は、SET GLOBAL innodb_optimize_fulltext_only=ON;の後にOPTIMIZE TABLE テーブル名;を実行する。

以下のシステム変数を変更した場合、ALTER TABLEのDROP INDEXとADD INDEXによるインデックス再作成が必要

  • innodb_ft_min_token_size
  • innodb_ft_max_token_size
  • innodb_ft_server_stop_word_table
  • innodb_ft_user_stopword_table
  • innodb_ft_enable_stopword
  • ngram_tokne_size

パラメータチューニング

パラメータを変更したところで劇的に良くなることはない。
最も重要なパラメータはinnodb_buffer_pool_sizeである。SELECTのときにキャッシュとして使われるだけでなく、InnoDBの全ての操作はバッファプールを使うため、あらゆる速度に影響を及ぼす。

文字コード

Charset

MySQL8.0からデフォルトのCharsetがutf8mb4になった。
utf8(utfmb3)は最大3バイトなので、絵文字は使えない。
何も考えずにutf8mb4にしておけば問題ない。

character_ser_server変数の値が、データベースのCharsetのデフォルトである。
テーブルのCharsetのデフォルトはデータベースのCharsetである。
つまり、character_ser_serverがutfmb4になっていれば、charasetは何も設定しなくてよい。

Collation

文字列を比較やソートするときにCollationが影響する。
Collationの指定は、データベースやテーブルのCharaset指定時にCHARSET xxx COLLATE xxx;とする。

デフォルトはutf8mb4_0900_ai_ciとなっている。
ひらがなとカタカナを区別する場合はutf8mb4_ja_0900_as_cs_ks、区別しない場合はutf8mb4_ja_0900_as_cs、にする。
utf8mb4_ja_0900_as_csにしておけばOKだと思う。

日本語環境の主なCollation

utf8mb4_0900_ai_ci

  • 「は」「ば」を区別しない
  • 「あ」「ぁ」を区別しない
  • 全角/半角を区別しない
  • ひらがな/カタカナを区別しない

utf8mb4_0900_as_ci

  • 「は」「ば」を区別する
  • 「あ」「ぁ」を区別しない
  • 全角/半角を区別しない
  • ひらがな/カタカナを区別しない

utf8mb4_0900_as_cs

  • 「は」「ば」を区別する
  • 「あ」「ぁ」を区別する
  • 全角/半角を区別する
  • ひらがな/カタカナを区別する

utf8mb4_ja_0900_as_cs

  • 「は」「ば」を区別する
  • 「あ」「ぁ」を区別する
  • 全角/半角を区別しない
  • ひらがな/カタカナを区別しない

utf8mb4_ja_0900_as_cs_ks

  • 「は」「ば」を区別する
  • 「あ」「ぁ」を区別する
  • 全角/半角を区別しない
  • ひらがな/カタカナを区別する

utf8mb4_general_ci

  • MySQL5.7でcharasetをutfmb4にした際のデフォルトcollation
  • MySQL8.0では使わないと思う
  • 「あ」「ぁ」を区別しない
  • ひらがな/カタカナを区別する
  • 最大3バイトなので絵文字が使えない

utf8mb4_unicode_ci

  • MySQL8.0では使わないと思う
  • 「は」「ば」を区別しない
  • 「あ」「ぁ」を区別しない
  • 全角/半角を区別しない
  • ひらがな/カタカナを区別しない

Collation名の意味

「Charset名_言語名_nucodeバージョン_ai/as_ci/cs_ks」となっている

  • 言語名
    指定がないものもある
  • nucodeバージョン
    unicode: 4.0.0
    unicode_520: 5.2.0
    0900: 9.0.0
  • ai/as
    アクセントの違いを区別するかどうか。
    指定がないものもある。
  • ci/cs
    大文字小文字の違い、その他の形の違い、を区別するかどうか。
  • ks
    ひらがなとカタカナの違いを区別するかどうか。
    言語名がjsの場合のみ。

【新しい記事】

【古い記事】

コメントを残す

メールアドレスが公開されることはありません。