検索

ホームページ >データベース >mysql チュートリアル >MySQL のフルテキスト インデックスが、あいまい一致クエリのような遅い問題をどのように解決するかについて話しましょう。

MySQL のフルテキスト インデックスが、あいまい一致クエリのような遅い問題をどのように解決するかについて話しましょう。

青灯夜游
青灯夜游オリジナル
2022-10-31 20:03:222816ブラウズ

MySQL のフルテキスト インデックスが、あいまい一致クエリのような遅い問題をどのように解決するかについて話しましょう。

ファジー クエリ (名前に「xiao」が含まれるユーザーをクエリする場合など)、一般的な書き方は「%xiao%」のようなものです。MySQL ではテーブル全体をスキャンするため、データ量が少ないので良いですが、フルテーブルスキャンも速いですが、データが増えると遅くなり、ESの読み込みが非常に重いです。この記事では、あいまい一致クエリのように遅い場合の解決策である MySQL フルテキスト インデックスを紹介します。

要件

単語をクエリするにはあいまい一致が必要です

select * from t_phrase where LOCATE('Chan',phrase) = 0 ;

select * from t_chinese_phrase where instr(phrase,'Chan') > 0;

select * from t_chinese_phrase where フレーズ '%长%'

実行計画を説明して見てください

MySQL のフルテキスト インデックスが、あいまい一致クエリのような遅い問題をどのように解決するかについて話しましょう。

結果からわかりますインデックスは設定されていますが、クエリを実行するとインデックスが無効になるというフレーズを作成しましたが、そのインデックスが無効であることを説明します。 ＃＃＃＃＃＃理由： mysql のインデックスは B ツリー構造になっており、データのあいまいクエリ時に InnoDB が "%xx" を使用するとインデックスが失敗します (ここでは詳細は説明しません)

クエリ時間の観点から見ると、費やした時間: 90ms


現在のデータ量: 93230 (9.3W) はすでに 90ms を必要としています。この時間は許容できません。データ量が増加すると、この時間はさらに増加し​​ます。

解決策:

データ量が大きくない場合は、mysql の全文インデックスを使用します;
データ量が比較的多い場合、またはフルテキスト インデックスを使用します。 mysql のテキスト インデックスは期待を満たしていません。ES の使用を検討してください。


以下は主に MySQL のフルテキスト インデックスに関連しています。

フルテキスト インデックスはじめに

1. 開発の歴史

    MySQL の古いバージョンのフルテキスト インデックスは char でのみ使用できます。 、MyISAM ストレージ エンジンの varchar および text フィールド。


    • MySQL5.6.24 の InnoDB エンジンには、フルテキスト インデックスも追加されました。

2. 全文インデックス

    #全文検索
  • はデータベースに保存されます。本全体または記事全体のコンテンツから情報を検索します。全文中の章、節、段落、単語などの情報を必要に応じて取得でき、各種統計や分析も行うことができます
3. 全文インデックスの作成

大量のデータに対してフルテキスト インデックスを設定する必要がある場合は、最初にデータを追加してからインデックスを作成することをお勧めします。

1. テーブル作成時にフルテキスト インデックスを作成する 

create table 表名(
字段名1,
字段名2,
字段名3,
字段名4,
FULLTEXT full_index_name (字段名)
)ENGINE=InnoDB;

2. 既存のテーブルにフルテキスト インデックスを追加する

フルテキスト インデックスインデックスを作成するテーブル名 (フィールド名) の名前;

例:

create table t_word
(
    id        int unsigned auto_increment comment '自增id' primary key,
    uid       char(32)     not null comment '32位唯一id',
    word      varchar(256) null comment '英文单词',
    translate varchar(256) null
);

create fulltext index full_idx_translate
    on t_word (translate);

create fulltext index full_idx_word
    on t_word (word);

INSERT INTO t_word (id, uid, word, translate) VALUES (1, '9d592499c65648b0a9519206688ef3f9', 'lion', '狮子');
INSERT INTO t_word (id, uid, word, translate) VALUES (2, 'ce26ac4239514bc6af481bcb1d9b67df', 'panda', '熊猫');
INSERT INTO t_word (id, uid, word, translate) VALUES (3, 'a7d6042853c44904b68275daafb44702', 'tiger', '老虎');
INSERT INTO t_word (id, uid, word, translate) VALUES (4, 'f13bd0a8ecea44fc9ade1625eeb4cc3c', 'goat', '山羊');
INSERT INTO t_word (id, uid, word, translate) VALUES (5, '27d5cbfc93a046388d712085e567474f', 'sheep', '绵羊');
INSERT INTO t_word (id, uid, word, translate) VALUES (6, 'ed35df138cf348aa937781be8ee21cbf', 'lamb', '羊羔');
INSERT INTO t_word (id, uid, word, translate) VALUES (7, 'fba5861d9527440990276e999f47ef8f', 'buffalo', '水牛');
INSERT INTO t_word (id, uid, word, translate) VALUES (8, '3a72e76f210841b1939fff0d3d721375', 'bull', '公牛');
INSERT INTO t_word (id, uid, word, translate) VALUES (9, '272e0b28ea7a48248a86f17533bf9943', 'cow', '母牛');
INSERT INTO t_word (id, uid, word, translate) VALUES (10, '47127adface54e418e4c1b9980af6d16', 'calf', '小牛');
INSERT INTO t_word (id, uid, word, translate) VALUES (11, '10592499c65648b0a9519206688ef3f9', 'little lion', '小狮子');
INSERT INTO t_word (id, uid, word, translate) VALUES (12, '1bf095110b634a01bee5b31c5ee7ee0c', 'little cow', '母牛');
INSERT INTO t_word (id, uid, word, translate) VALUES (13, '4813e588cde54c30bd65bfdbb243ad1f', 'little calf', '小小牛');
INSERT INTO t_word (id, uid, word, translate) VALUES (14, '5e377e281ad344048b6938a638b78ccb', 'little bull', '小公牛');
INSERT INTO t_word (id, uid, word, translate) VALUES (15, '2855ad0da2964c7682c178eb8271f13d', 'little buffalo', '小水牛');
INSERT INTO t_word (id, uid, word, translate) VALUES (16, '72f24c9a77644d57a36f3bdf2b8116b0', 'little lamb', '小羊羔');
INSERT INTO t_word (id, uid, word, translate) VALUES (17, '2d592499c65648b0a9519206688ef3f9', 'I''m a big lion', '我是一只大狮子');

3. フルテキスト インデックスを削除します

alter table table name dropindexインデックス名;

4. フルテキスト インデックスは、自然言語モードでは

構文 

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
}

4.1 を使用します

自然言語モードは、MySQL

default の全文検索モードです。自然言語モードでは演算子を使用できず、出現する必要があるキーワードや出現してはならないキーワードなどの複雑なクエリを指定できません。 

// 默认是使用 in natural language mode
select * from t_word where match(word) against ('lion');
// 或者 显示写
select * from t_word where match(word) against ('lion' in natural language mode);
結果は次のとおりです:

MySQL のフルテキスト インデックスが、あいまい一致クエリのような遅い問題をどのように解決するかについて話しましょう。

4.2 ブール モードの場合

ブール モード

あなた演算子

を使用でき、キーワードを出現させるか出現させないか、キーワードの重みが高いか低いかを指定するなど、複雑なクエリをサポートできます。 ブール モードの使用を推奨します

##演算子説明 は空です Default、単語 includes が含まれます。この単語は存在する必要があります。 例外として、その単語は出現してはなりません。 >(記号より大きい)ランク値を含めて増やすと、クエリ結果が高くなります
#-
## ランク値を含めたり減らしたりします。クエリ結果は後で表示されます。
() 単語を部分式にグループ化します (これらを部分式として含めることができます)。グループ、除外、ランク付けなど)。
~ 否定的な単語の値をランク付けします。
# ワイルドカード文字は単語の末尾にあります。
"" フレーズを定義します (フレーズ全体が一致して含めるか除外する個々の単語のリストではありません)。

示例：

// 默认是使用 in natural language mode
select * from t_word where match(word) against ('lion');
// 或者 显示写
select * from t_word where match(word) against ('lion' in natural language mode);

MySQL のフルテキスト インデックスが、あいまい一致クエリのような遅い問題をどのように解決するかについて話しましょう。

// 排除包含lion记录、查询出包含cow或者little的记录，提升包含calf单词的排名，降低包含cow记录的排名，查询出以go开头的记录
select * from t_word where match(word) against (&#39;-lion cow little >calf <cow  go*&#39; in boolean mode) ;

MySQL のフルテキスト インデックスが、あいまい一致クエリのような遅い問題をどのように解決するかについて話しましょう。

好像问题都解决了， 但是问题才刚开始

回到最开始的需求，我想模糊搜索

select * from t_word where  match(word) against(&#39;lio&#39; in boolean mode);

预期值：把包含lion的都查询出来 实际结果：啥都没有。

MySQL のフルテキスト インデックスが、あいまい一致クエリのような遅い問題をどのように解決するかについて話しましょう。

全匹配查询的时候能查询出来

select * from t_word where  match(translate) against(&#39;小水牛&#39; in boolean mode);

MySQL のフルテキスト インデックスが、あいまい一致クエリのような遅い問題をどのように解決するかについて話しましょう。

只查询部分查询不出来。如：下面只查询 "小水" 或者"水牛" 都没有数据

select * from t_word where  match(translate) against(&#39;小水&#39; in boolean mode);

MySQL のフルテキスト インデックスが、あいまい一致クエリのような遅い問題をどのように解決するかについて話しましょう。

奇怪了，这咋没出来呢？

全文索引默认是只按照空格进行分词的，所以当我完整的单个单词去查询的时候是能查出来的。但是使用部分单词去查询或者使用部分中文去查询时，是查询不出来数据的,像中文需要使用中文分词器进行分词。

中文分词与全文索引

InnoDB默认的全文索引parser非常合适于Latin，因为Latin是通过空格来分词的。但对于像中文，日文和韩文来说，没有这样的分隔符。一个词可以由多个字来组成，所以我们需要用不同的方式来处理。在MySQL 5.7.6中我们能使用一个新的全文索引插件来处理它们：N-gram parser。

什么是N-gram？

在全文索引中，n-gram就是一段文字里面连续的n个字的序列。例如，用n-gram来对“齿轮传动”来进行分词，得到的结果如下：

N=1 : &#39;齿&#39;, &#39;轮&#39;, &#39;传&#39;, &#39;动&#39;;
N=2 : &#39;齿轮&#39;, &#39;轮传&#39;, &#39;传动&#39;;
N=3 : &#39;齿轮传&#39;, &#39;轮传动&#39;;
N=4 : &#39;齿轮传动&#39;;

这个上面这个N是怎么去配置的？

查一下目前的值

show variables like &#39;%token%&#39;;

MySQL のフルテキスト インデックスが、あいまい一致クエリのような遅い問題をどのように解決するかについて話しましょう。

参数解析:

innodb_ft_min_token_size  
默认3，表示最小3个字符作为一个关键词，增大该值可减少全文索引的大小  
innodb_ft_max_token_size
默认84，表示最大84个字符作为一个关键词，限制该值可减少全文索引的大小
ngram_token_size
默认2，表示2个字符作为内置分词解析器的一个关键词,合法取值范围是1-10，如对“abcd”建立全文索引，关键词为’ab’，‘bc’，‘cd’ 当使用ngram分词解析器时，innodb_ft_min_token_size和innodb_ft_max_token_size 无效

修改方式

方式1： 在my.cnf中修改/添加参数

[mysqld]ngram_token_size = 1

方式2： 修改启动参数

mysqld --ngram_token_size=1复制代码

参数均不可动态修改，修改后需重启MySQL服务，并重新建立全文索引

实际使用

初始化测试数据

这里只提供部分测试数据，我下面sql使用全量数据，数据对不上

create table t_chinese_phrase
(
    id     int unsigned auto_increment comment &#39;id&#39;
        primary key,
    phrase varchar(32) not null comment &#39;词组&#39;
)
    collate = utf8mb4_general_ci;

INSERT INTO t_chinese_phrase (id, phrase) VALUES (278911, &#39;阿昌族&#39;);
INSERT INTO t_chinese_phrase (id, phrase) VALUES (279253, &#39;八一南昌起义&#39;);
INSERT INTO t_chinese_phrase (id, phrase) VALUES (282316, &#39;昌明&#39;);
INSERT INTO t_chinese_phrase (id, phrase) VALUES (282317, &#39;昌盛&#39;);
INSERT INTO t_chinese_phrase (id, phrase) VALUES (282318, &#39;昌言&#39;);
INSERT INTO t_chinese_phrase (id, phrase) VALUES (286534, &#39;东昌纸&#39;);
INSERT INTO t_chinese_phrase (id, phrase) VALUES (291525, &#39;海昌蓝&#39;);
INSERT INTO test.t_chinese_phrase (id, phrase) VALUES (346682, &#39;繁荣昌盛&#39;);
INSERT INTO test.t_chinese_phrase (id, phrase) VALUES (282317, &#39;昌盛&#39;);
INSERT INTO test.t_chinese_phrase (id, phrase) VALUES (287738, &#39;繁盛&#39;);
INSERT INTO test.t_chinese_phrase (id, phrase) VALUES (287736, &#39;繁荣&#39;);

添加索引

mysql 全文索引使用倒排索引为 full inverted index 
  结构：{单词，(单词所在文档的ID，单词在具体文件中的位置)}

添加索引：

alter  table t_chinese_phrase add fulltext ful_phrase (phrase) with parser ngram;

建完索引，我们可以通过查询INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE和INFORMATION_SCHEMA.INNODB_FT_TABLE_TABLE来查询哪些词在全文索引里面。这是一个非常有用的调试工具。如果我们发现一个包含某个词的文档，没有如我们所期望的那样出现在查询结果中，那么这个词可能是因为某些原因不在全文索引里面。比如，它含有stopword，或者它的大小小于ngram_token_size等等。这个时候我们就可以通过查询这两个表来确认。下面是一个简单的例子：

# test: 库名  t_chinese_phrase： 表名字
SET GLOBAL innodb_ft_aux_table="test/t_chinese_phrase";
# 查询分词情况
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
# 查询分词情况
select * from information_schema.innodb_ft_index_table;

查询结果如下：

MySQL のフルテキスト インデックスが、あいまい一致クエリのような遅い問題をどのように解決するかについて話しましょう。

因为我们上面设置了分词数是1，所以，可以看到都是按照一个词进行分词的。

字段解析：
FIRST_DOC_ID ：word第一次出现的文档ID
LAST_DOC_ID : word最后一次出现的文档ID
DOC_COUNT ：含有word的文档个数
DOC_ID ：当前文档ID
POSITION : word 当在前文档ID的位置

查询

1、使用自然语言模式 NATURAL LANGUAGE MODE 查询

在自然语言模式（NATURAL LANGUAGE MODE)下，文本的查询被转换为n-gram分词查询的并集。

例如，当ngram_token_size = 1 时，(‘繁荣昌盛’)转换为(‘繁 荣 昌 盛’)。下面一个例子：

SELECT * FROM t_chinese_phrase WHERE MATCH (phrase) AGAINST (&#39;繁荣昌盛&#39; in natural language mode) ;

MySQL のフルテキスト インデックスが、あいまい一致クエリのような遅い問題をどのように解決するかについて話しましょう。

2、使用布尔模式（BOOLEAN MODE）查询

布尔模式（BOOLEAN MODE）文本查询被转化为n-gram分词的短语查询

例如，当ngram_token_size = 1 时，(‘繁荣昌盛’)转换为(‘”繁荣昌盛“’)。下面一个例子：

SELECT * FROM t_chinese_phrase WHERE MATCH (phrase) AGAINST (&#39;繁荣昌盛&#39; in boolean  mode) ;

1MySQL のフルテキスト インデックスが、あいまい一致クエリのような遅い問題をどのように解決するかについて話しましょう。

实际使用

回到我们最开始的查询需求，看看实际的效果

查询包含了“昌”的数据

SELECT * FROM t_chinese_phrase WHERE MATCH (phrase) AGAINST (&#39;昌&#39; IN boolean  MODE) ;
SELECT * FROM t_chinese_phrase WHERE MATCH (phrase) AGAINST (&#39;昌&#39; ) order by id asc;

1MySQL のフルテキスト インデックスが、あいまい一致クエリのような遅い問題をどのように解決するかについて話しましょう。

可以看到结果：目前“昌”在任意位置都能被查询到。

查询执行计划如下：

1MySQL のフルテキスト インデックスが、あいまい一致クエリのような遅い問題をどのように解決するかについて話しましょう。

耗时31ms（不走索引是90ms），耗时差不多是之前的1/3。

注意点

1、自然语言全文索引创建索引时的字段需与查询的字段保持一致，即MATCH里的字段必须和FULLTEXT里的一模一样；

2、自然语言检索时，检索的关键字在所有数据中不能超过50%（即常见词），则不会检索出结果。可以通过布尔检索查询；

3、在mysql的stopword中的单词检索不出结果。可通过

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD

查询所有的stopword。遇到这种情况，有两种解决办法：

（1）stopword一般是mysql自建的，但可以通过设置ft_stopword_file变量为自定义文件，从而自己设置stopword，设置完成后需要重新创建索引。但不建议使用这种方法；

（2）使用布尔索引查询。

4、小于最短长度和大于最长长度的关键词无法查出结果。可以通过设置对应的变量来改变长度限制，修改后需要重新创建索引。

myisam引擎下对应的变量名为ft_min_word_len和ft_max_word_len

innodb引擎下对应的变量名为innodb_ft_min_token_size和innodb_ft_max_token_size

5、MySQL5.7.6之前的版本不支持中文，需使用第三方插件

6、全文索引只能在 InnoDB（MySQL 5.6以后） 或 MyISAM 的表上使用，并且只能用于创建 char,varchar,text 类型的列。

【相关推荐：mysql视频教程】

以上がMySQL のフルテキスト インデックスが、あいまい一致クエリのような遅い問題をどのように解決するかについて話しましょう。の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。

sql mysql Boolean select char 值参数 table 数据库 word
声明：
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。
前の記事：GitHub が MySQL の可用性を高める方法について話しましょう次の記事：GitHub が MySQL の可用性を高める方法について話しましょう

関連記事

続きを見る