My SQLのファジークエリはもはや+%のようではない

InnoDBがあいまいなクエリデータで“%xx”を使用するとインデックスが無効になることは誰もが知っていますが、時には需要があります。例えば、検索エンジンはユーザーデータのキーワードに応じて全文検索する必要があり、電子商取引サイトはユーザーのクエリ条件に応じて必要であり、商品の詳細な紹介で検索する必要があるかもしれません。

数値比較、範囲フィルタリングなどにより、必要なクエリの大部分を完了できます。しかし、キーワードマッチングによってクエリフィルタリングを行いたい場合は、元の正確な数値比較ではなく、類似性に基づくクエリが必要になります。全文インデックスはこのシナリオのために設計されています。

フルテキスト検索は、データベースに格納されている本全体または記事全体から任意の情報を検索する技術です。必要に応じて、テキストの関連する章、セクション、段落、文、単語などの情報を取得することができ、様々な統計や分析を行うことができます。

初期のMy SQLでは、InnoDBは全文検索をサポートしていなかったが、My SQL 5.6以降、InnoDBは全文検索をサポートしている。

反転インデックスの作成

全文検索は通常逆転（inverindex）を用いて実现されるが、逆転もB+Treeと同様に一种のである。1つ以上のドキュメント内の単語と単語自体の位置との間のマッピングを補助テーブルに格納します。通常は連想配列を使用して実装され、2つの表現があります。

inverted file index：{単語、単語が存在するドキュメントのID}
full inverted index：{単語，（単語が存在する文書のid，および文書内の位置）}

上の図はinverted file index連想配列で、その中の単語“code”がドキュメント1，4に存在することがわかります。これにより、保存してから全文検索を行うことが簡単になり、ドキュメントに基づいてクエリキーワードを含むドキュメントを直接得ることができます。一方、full inverted indexはペア、すなわちDocumentId Positionを格納します。したがって、キーワード“code”がドキュメント1の6番目の単語とドキュメント4の8番目の単語に存在するように、格納される反転インデックスは次の図のようになります。対照的に、完全反転インデックスはより多くのスペースを占有しますが、データの位置をより良くし、他の検索機能を拡張します。

全文検索→

全文索引を作成する

1、表作成時の全文索引作成構文は次のとおりです。

CREATE TABLE table_name ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, author VARCHAR(200),
title VARCHAR(200), content TEXT(500), FULLTEXT full_index_name (col_name) ) ENGINE=InnoDB;

クエリー文を入力する

SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_TABLES
WHERE name LIKE 'test/%';

上記の6つのインデックステーブルは、補助インデックステーブルと呼ばれる反転インデックスを構成します。受信文書がトークン化されると、個々の単語は位置情報と関連付けられたDOC_IDとともに完全にソートされ、単語の最初の文字の文字セットソート重みに基づいて6つのインデックステーブルに分割されます。

作成されたテーブルに全文索引を作成する構文は次のとおりです。

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

全文索引を使用する

MySQLデータベースは全文検索のクエリをサポートしています。全文インデックスはInnoDBまたはMyISAMのテーブルでのみ使用でき、char、varchar、text型のカラムの作成にのみ使用できます。

文法は以下の通り。

MATCH(col1,col2,...) AGAINST(expr[search_modifier])
search_modifier:
{
    IN NATURAL LANGUAGE MODE
    | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
    | IN BOOLEAN MODE
    | WITH QUERY EXPANSION
}

全文搜索使用 MATCH() AGAINST()语法进行，其中，MATCH() 采用逗号分隔的列表，命名要搜索的列。AGAINST()接收一个要搜索的字符串，以及一个要执行的搜索类型的可选修饰符。全文检索分为三种类型：自然语言搜索、布尔搜索、查询扩展搜索，下面将对各种查询模式进行介绍。

Natural Language

自然语言搜索将搜索字符串解释为自然人类语言中的短语，MATCH()默认采用 Natural Language 模式，其表示查询带有指定关键字的文档。

自然言語の理解を深めるためのデモ

SELECT
    count(*) AS count
FROM
    `fts_articles`
WHERE
    MATCH ( title, body ) AGAINST ( 'MySQL' );

前述の文は、title body 列に'MySQL'キーワードを含む行の数をクエリします。上記の文は次のように書くこともできます：

SELECT
    count(IF(MATCH ( title, body )
    against ( 'MySQL' ), 1, NULL )) AS count
FROM
    `fts_articles`;

上記の2つのステートメントは同じ結果を得ますが、内部実行の観点からは、2番目のSQLの方が高速です。なぜなら、1番目のSQL（w hereインデックスクエリに基づく方法）も相関統計を必要とし、2番目の方法は必要ありません。

SQL文を使用して依存関係をクエリすることもできます。

SELECT
    *,
    MATCH ( title, body ) against ( 'MySQL' ) AS Relevance
FROM
    fts_articles;

依存関係は以下の4つの条件に基づいて計算されます。

ファイルに単語が表示されますか？
文書内の単語の出現回数
インデックス内の単語の数この単語を含むファイルの数

InnoDBストレージエンジンの全文検索では、以下の要素も考慮する必要があります。

クエリのword stopwordカラムで、文字列のクエリはされます。
クエリーされたwordの文字长が[innodb_ft_min_token_size innodb_ft_max_token_size] 内であるかどうか

単語がstopword内にある場合、単語'for'のように、その単語はクエリされません。結果は次のようになります。

SELECT
    *,
    MATCH ( title, body ) against ( 'for' ) AS Relevance
FROM
    fts_articles;

'for'は文書2，4に出現しているが，stopwordであるため相関は0であることが分かる

引数innodb_ft_min_token_sizeおよびinnodb_ft_max_token_sizeは、InnoDBエンジンのクエリ文字の長さを制御します。これは、長さがinnodb_ft_min_token_sizeより小さい場合、またはinnodb_ft_max_token_sizeより大きい場合、検索を無視します。InnoDBエンジンでは、パラメータinnodb_ft_min_token_sizeのデフォルト値は3で、innodb_ft_max_token_sizeのデフォルト値は84です。

Boolean

ブール検索は、特殊なクエリ言語の規則を使用して、検索対象の単語を含む検索文字列を解釈します。また、一致する行に単語が存在するか存在しないか、またはその重みが通常よりも高いか低いかなどの要件を指定する演算子を含めることもできます。例えば、次の文では、文字列“Pease”があるが“hot”がないドキュメントをクエリする必要があります。ここで、+と-はそれぞれ、単語が存在するか、存在しないかを示します。

select * from fts_test where MATCH(content) AGAINST('+Pease -hot' IN BOOLEAN MODE);

Boolean 全文検索でサポートされている型は次のとおりです。

+：単語が存在することを示す
：単語が存在しないことを示す
(no operator単語はオプションであるが、存在する場合は関連性が高いことを示します。
@distance 表示查询的多个单词之间的距离是否在 distance 之内，distance 的单位是字节，这种全文检索的查询也称为 Proximity Search，如 MATCH(context) AGAINST('"Pease hot"@30' IN - BOOLEAN MODE)语句表示字符串 Pease 和 hot 之间的距离需在 30 字节内
当該単語が出現した場合に関連性を増すことを示す
<：当該単語が出現した場合に関連性が低下することを示す
~：その単語の出現を許可するが、出現時の相関は負であることを示す
- ：表示以该单词开头的单词，如 lik*,表示可以是 lik，like，likes
“”：句を示す

Booleanモードの使用方法を示すデモをいくつか紹介します。

demo1：+ -

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH ( title, body ) AGAINST ( '+MySQL -YourSQL' IN BOOLEAN MODE );

上記の文は、'My SQL'を含むが'Your SQL'を含まない情報をクエリします。

demo2： no operator

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH ( title, body ) AGAINST ( 'MySQL IBM' IN BOOLEAN MODE );

上記のステートメントでは、クエリの'My SQL IBM'は'+'，'-'識別子を持たず、単語がオプションであることを示しており、存在する場合はより関連性が高くなります。

demo3：@

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH ( title, body ) AGAINST ( '"DB2 IBM"@3' IN BOOLEAN MODE );
复制代码
上述语句，代表 "DB2" ，"IBM"两个词之间的距离在3字节之内

demo4：> <

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH ( title, body ) AGAINST ( '+MySQL +(>database <DBMS)' IN BOOLEAN MODE );

上記のステートメントでは、クエリには'My SQL'、'database'、'DBMS'の行情報が含まれますが、'DBMS'を含まない行は'DBMS'を含む行よりも関連性が高いです。

demo5: ~

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH ( title, body ) AGAINST ( 'MySQL ~database' IN BOOLEAN MODE );

上記のステートメントでは、クエリには'My SQL'の行が含まれますが、行に'database'も含まれている場合、相関関係は減少します。

demo6：*

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH ( title, body ) AGAINST ( 'My*' IN BOOLEAN MODE );

上記の文では、クエリキーワードに'My'の行情報が含まれます。

demo7："

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH ( title, body ) AGAINST ( '"MySQL Security"' IN BOOLEAN MODE );

上記のステートメントでは、正確なフレーズ“My SQL Security”を含む行情報をクエリします。

Query Expansion

クエリ拡張検索は、自然言語検索の変更であり、クエリのキーワードが短すぎ、ユーザーが暗黙の知識（暗黙の知識）を必要とする場合に通常実行されます。例えば、データベースクエリの場合、ユーザーはデータベースを含むドキュメントだけでなく、My SQL、Oracle、RDBMSを含む単語をクエリしたい場合があります。

クエリ文にWITH QUERY EXPANSION / IN NATURAL LANGUAGE MODE WITH QUERY EXPANSIONを追加すると、2段階のクエリ展開（自動参照フィードバックとも呼ばれます）が開始されます。

フェーズ1：検索した単語に基づく全文索引クエリ
第2段階：第1段階で生成されたセグメンテーションに基づいて、全文検索クエリを実行する

次に、クエリ拡張の使用方法を例に挙げてみましょう。

-- 创建索引
create FULLTEXT INDEX title_body_index on fts_articles(title,body);

-- 使用 Natural Language 模式查询
SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH(title,body) AGAINST('database');

Query Expansionを使用する前のクエリーの結果は次のとおりです。

-- 当使用 Query Expansion 模式查询
SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH(title,body) AGAINST('database' WITH QUERY expansion);

Query Expansionを使用した結果は次のようになります。

Query Expansionの全文検索は、無関係なクエリが多数発生する可能性があるため、ユーザーは使用に非常に注意する必要があります。

全文索引を削除する

1、全文索引を直接削除する構文は以下のとおりです。

DROP INDEX full_idx_name ON db_name.table_name;

Alter Tableを使用して全文索引を削除する構文は以下のとおりです。

ALTER TABLE db_name.table_name DROP INDEX full_idx_name;

概要まとめまとめ

この記事では、理論と実践の組み合わせの観点からフルテキストインデックスを紹介します。My SQLに興味がある場合は、My SQLコラムをフォローしてください。

My SQLのファジークエリはもはや+%のようではない

前のページ

反転インデックスの作成

全文検索→

全文索引を作成する

全文索引を使用する

Natural Language

Boolean

Query Expansion

全文索引を削除する

概要まとめまとめ

延伸阅读

My SQL最適化テクニック：5つのステップ+ 10のケース

mysql 8.0.28 x 64 for Windows Ultra Light Portable、16MBのみ

プロジェクトでよく使われる19のMy SQL最適化

DockerでMy SQLを実行しています。おめでとう、仕事中!