MySQLの全文索引

10380 ワード
MySQL
最近、データベースインデックスの復習部分でfulltext、すなわち全文インデックスを見ましたが、全文インデックスは普段の業務ではあまり使われていませんが、少し面白いと感じて、少し時間をかけて検討して、ここに記録します.
導入
コンセプト
ほとんどのクエリーは、数値比較、範囲フィルタリングなどで完了できますが、キーワードのマッチングでクエリーフィルタリングを行うには、元の正確な数値比較ではなく、類似度に基づくクエリーが必要です.全文インデックスはこのようなシーンのために設計されています.
like+%でファジイマッチングが実現できると言えるかもしれませんが、なぜ全文インデックスが必要なのでしょうか.like+%はテキストが少ない場合に適しているが,大量のテキストデータ検索では考えられない.全文インデックスは大量のデータの前でlike+%よりN倍速く、速度は1桁ではないが、全文インデックスには精度の問題がある可能性がある.
全文インデックスに気づいたことがないかもしれませんが、少なくとも1つの全文インデックス技術に詳しいはずです.さまざまな検索エンジンです.検索エンジンのインデックスオブジェクトは超大量のデータであり、通常はその背後には関係型データベースではないが、全文インデックスの基本原理は同じである.
バージョンのサポート
始める前に、全文インデックスのバージョン、ストレージエンジン、データ型のサポート状況についてお話しします
  • MySQL 5.6以前のバージョンでは、MyISAMストレージエンジンのみが全文インデックスをサポートしていました.
  • MySQL 5.6以降のバージョンでは、MyISAMとInnoDBストレージエンジンが全文インデックスをサポートしています.
  • フィールドのデータ型がchar、varchar、textおよびそのシリーズである場合にのみ、全文インデックスを作成できます.

    • 全文インデックスをテストまたは使用する場合は、まず自分のMySQLバージョン、ストレージエンジン、データ型が全文インデックスをサポートしているかどうかを確認します.
    操作全文索引
    インデックスの操作は勝手に探しても、ここではもう一度うるさいです.
    作成
  • テーブルの作成時に全文インデックス
    • を作成
    create table fulltext_test (
    id int(11) NOT NULL AUTO_INCREMENT,
    content text NOT NULL,
    tag varchar(255),
    PRIMARY KEY (id),
    FULLTEXT KEY content_tag_fulltext(content,tag)  //          
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  • 既存のテーブルに全文インデックス
    • を作成
    create fulltext index content_tag_fulltext
    on fulltext_test(content,tag);
  • SQL文ALTER TABLEによる全文インデックス
    • の作成
    alter table fulltext_test
    add fulltext index content_tag_fulltext(content,tag);

    変更
    Oを修正し、直接削除して再構築します.
    削除
  • 直接DROP INDEXを使用して全文インデックス
    • を削除
    drop index content_tag_fulltext
    on fulltext_test;
  • SQL文ALTER TABLEにより全文インデックス
    • を削除
    alter table fulltext_test
    drop index content_tag_fulltext;

    全文索引の使用
    一般的なファジイマッチングでlike+%を使用するのとは異なり、全文インデックスには独自の構文フォーマットがあり、matchやagainstキーワード、例えば
    select * from fulltext_test 
    where match(content,tag) against('xxx xxx');

    注意:match()関数で指定したカラムは、全文インデックスで指定したカラムと完全に同じでなければなりません.そうしないと、全文インデックスはキーワードがどのカラムから来たかを記録しないため、全文インデックスを使用できません.カラムに全文インデックスを使用する場合は、カラムの全文インデックスを個別に作成します.
    全文索引のテスト
    テストデータの追加
    上記の知識があれば、全文インデックスをテストすることができます.
    まずテストテーブルを作成し、テストデータを挿入します.
    create table test (
    id int(11) unsigned not null auto_increment,
    content text not null,
    primary key(id),
    fulltext key content_index(content)
) engine=MyISAM default charset=utf8;

insert into test (content) values ('a'),('b'),('c');
insert into test (content) values ('aa'),('bb'),('cc');
insert into test (content) values ('aaa'),('bbb'),('ccc');
insert into test (content) values ('aaaa'),('bbbb'),('cccc');

    全文インデックスの使用構文に従って、次のクエリを実行します.
    select * from test where match(content) against('a');
select * from test where match(content) against('aa');
select * from test where match(content) against('aaa');

    我々の慣性思考によれば,4つのレコードが表示されるはずであるが,結果として1つのレコードもなく,次のクエリを実行する場合にのみ
    select * from test where match(content) against('aaaa');

    aaaaという記録が見つかりました
    どうして?この問題には多くの原因があり,その中で最もよく見られるのは最小探索長によるものである.また、全文インデックスを使用する場合は、テストテーブルに少なくとも4つ以上の記録が必要です.そうしないと、予想外の結果が出ます.
    MySQLの全文インデックスには、2つの変数、最小検索長さと最大検索長さがあり、最小検索長さよりも小さい語と最大検索長よりも大きい語はインデックスされません.通俗的に言えば、1つの語に対して全文インデックス検索を使用するには、この語の長さは以上の2つの変数の区間内でなければならない.
    この2つのデフォルト値は、次のコマンドで表示できます.
    show variables like '%ft%';

    この2つの変数は、MyISAMとInnoDBの2つのストレージエンジンで変数名とデフォルト値が表示されます.
    // MyISAM
ft_min_word_len = 4;
ft_max_word_len = 84;

// InnoDB
innodb_ft_min_token_size = 3;
innodb_ft_max_token_size = 84;

    最小検索長MyISAMエンジンのデフォルトは4であり、InnoDBエンジンの下では3である.すなわち、MySQLの全文インデックスは4または3以上の長さの語にのみインデックスを作成し、検索したのはaaaの長さが4以上である.
    最小検索長の設定
    全文インデックスの関連パラメータは動的に変更できません.MySQLのプロファイルを変更することで完了する必要があります.最小検索長の値を1に変更するには、まずMySQLのプロファイル/etc/myを開きます.cnf,［mysqld］の下に以下の内容を追加
    [mysqld]
innodb_ft_min_token_size = 1
ft_min_word_len = 1

    その後、MySQLサーバを再起動し、全文インデックスを修復します.パラメータを変更した後は、インデックスを修正する必要があります.そうしないと、パラメータは有効になりません.
    次のコマンドで修復できます.
    repair table test quick;

    あるいは直接削除してインデックスを再構築し、上記のクエリを再実行すると、a、aaa、aaaが検出されます.
    しかし、ここでもう一つ問題があります.キーワードaを検索するとき、なぜaa、aaa、aaaに結果が出なかったのか、この問題を話す前に、まず2つの全文インデックスについて話します.
    2つの全文インデックス
    自然言語の全文インデックス
    デフォルトでは、in natural language mode修飾子を使用する場合、match()関数はテキストセットに対して自然言語検索を実行します.上記の例はすべて自然言語の全文インデックスです.
    ナチュラル言語検索エンジンは、各ドキュメントオブジェクトとクエリーの相関を計算します.ここで,相関度は,一致するキーワードの個数と,キーワードが文書に出現する回数に基づいている.インデックス全体に出現する回数が少ない語ほど,マッチング時の相関度が高くなる.逆に、非常に一般的な単語は検索されず、1つの単語の50%を超える記録に現れた場合、自然言語の検索はこのような単語を検索しません.上記のように、テストテーブルには4つ以上の記録が必要であるのがそのためです.
    このメカニズムもよく理解されています.例えば、1つのデータテーブルに格納されているのは1編の文章で、文章の中のよくある言葉、語気語など、肯定的に多く現れています.これらの言葉を検索するのは意味がありません.検索する必要があるのは、文章の中に特別な意味を持つ言葉で、このようにして文章を区別することができます.
    ブール全文インデックス
    ブール検索では、検索された語の相関をクエリーでカスタマイズできます.ブール検索クエリーを作成するときに、いくつかの接頭辞修飾子で検索をカスタマイズできます.
    MySQLに内蔵された修飾子で、上から最小検索長を検索すると、検索結果ft_boolean_syntax変数の値は内蔵の修飾子で、以下は簡単にいくつか説明して、もっと多くの修飾子の作用はマニュアルを調べることができます
  • +は、この語
    • を含む必要があります.
  • -
    • を含まないでください.
  • >この語の相関性を向上させ、クエリの結果は前の
    • である.
  • <この語の相関性を低減し、クエリの結果は後
    • である.
  • (*)アスタリスクワイルドカードは、語の後ろに
    • しか接続できません.
    上記の問題については、ブール全文インデックスクエリを使用して解決できます.次のコマンドを使用すると、a、aaa、aaaがクエリされます.
    select * test where match(content) against('a*' in boolean mode);

    まとめ
    よし、もう少しで書き終わるところだったが、また総括の時になった.
    MySQLの全文インデックスは最初は英語のみをサポートしていましたが、英語の語と語の間にスペースがあるので、スペースを分詞の区切り文字として使うのが便利でした.アジア文字、例えば中国語、日本語、中国語などは、スペースがなく、一定の制限をもたらしている.しかし、MySQL 5.7.6からは、この問題を解決するためにngram全文アナライザが導入され、MyISAMとInnoDBエンジンに有効です.
    実際、MyISAMストレージエンジンは、パフォーマンスへのテーブル・レベル・ロックの影響、データ・ファイルのクラッシュ、クラッシュ後のリカバリなど、全文インデックスのサポートに多くの制限があり、MyISAMの全文インデックスは多くのアプリケーション・シーンに適していません.したがって、Sphinx、Luceneなどのサードパーティ製プラグインやInnoDBストレージエンジンの全文インデックスなど、他のソリューションを使用することが推奨される場合が多い.
    いくつかの注意点
  • 全文インデックスを使用する前に、バージョンのサポート状況を明らかにします.
  • 全文インデックスはlike+%よりN倍速いが、精度の問題がある可能性がある.
  • 全文インデックスが必要なデータが大量であれば、まずデータを追加してからインデックスを作成することをお勧めします.
  • 中国語では、MySQL 5.7.6以降のバージョン、またはサードパーティ製プラグインを使用できます.

    • 参考記事
    mysql全文索引_MySQL公式リファレンスマニュアル高性能MySQL(第3版)
    本文の元のリンク:MySQLの全文索引
    mysqlのTQL(Transaction Control Language)
    MySQL面接復習2