mysql全文インデックスとは何ですか

mysql における全文インデックス作成は、データベースに保存されている書籍全体または記事全体の情報を検索するテクノロジーです。必要なクエリのほとんどは、数値比較や範囲フィルタリングなどを通じて完了できますが、キーワード一致によってクエリをフィルタリングしたい場合は、元の正確な数値比較ではなく、類似性に基づいたクエリが必要になります。テキストのインデックス作成は、このシナリオ向けに設計されています。

このチュートリアルの動作環境: Windows7 システム、mysql8 バージョン、Dell G3 コンピューター。

---

## コンセプトの紹介

全文インデックス (全文検索) は、データベースに保存されている書籍全体または記事全体から任意の情報を検索する技術です。全文の章、節、段落、文章、単語などの情報を必要に応じて取得でき、各種統計や分析も行えます。フルテキスト インデックスは通常、逆インデックスによって実装されます。

必要なクエリのほとんどは、数値比較や範囲フィルタリングなどを通じて完了できます。ただし、キーワード一致によってクエリをフィルタリングしたい場合は、元の正確な数値比較ではなく、類似性に基づいてクエリを実行する必要があります。 。フルテキスト インデックス作成は、このシナリオ向けに設計されています。

「% のようなものを使用するとあいまい一致を実現できるのに、なぜ全文インデックスが必要なのでしょうか?」と思われるかもしれません。 like % はテキストが比較的小さい場合には適していますが、大量のテキストデータを取得する場合は考えられません。全文インデックス作成は、大量のデータに直面した場合、% のような速度よりも N 倍高速になります。速度は桁違いではありませんが、全文インデックス作成には精度の問題が発生する可能性があります。

フルテキスト インデックス作成に注意を払ったことがないかもしれませんが、少なくとも 1 つのフルテキスト インデックス作成テクノロジ、つまりさまざまな検索エンジンについてはよく知っているはずです。検索エンジンのインデックス オブジェクトは非常に大量のデータであり、通常、その背後にリレーショナル データベースはありませんが、全文インデックス作成の基本原理は同じです。

バージョンのサポート

始める前に、フルテキスト インデックスのバージョン、ストレージ エンジン、およびデータ型のサポートについて説明します。

MySQL 5.6 より前では、MyISAM ストレージ エンジンのみがフルテキスト インデックスをサポートします。

1. MySQL 5.6 以降のバージョンでは、MyISAM と InnoDB ストレージ エンジンの両方がフルテキスト インデックスをサポートします。
2. フィールドのみ フルテキスト インデックスは、データ型が char、varchar、text、およびそれらの系列の場合にのみ構築できます。
3. フルテキスト インデックスをテストまたは使用する場合は、まず MySQL のバージョン、ストレージ エンジン、およびデータ型がフルテキスト インデックスをサポートしているかどうかを確認する必要があります。

全文インデックスの操作

インデックスの操作はどの検索でも見つけることができますが、ここでは繰り返します。

---

作成

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

1. 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);

# を使用してフルテキスト インデックスを作成する
1. ##Modify

O を変更し、削除して直接再構築します。 #削除

DROP INDEX を直接使用してフルテキスト インデックスを削除します

drop index content_tag_fulltext    on fulltext_test;

SQL ステートメント ALTER TABLE によりフルテキスト インデックスが削除されます

1. alter table fulltext_test    drop index content_tag_fulltext;

フルテキスト インデックスを使用します

一般的に使用される % などのあいまい一致とは異なります、フルテキスト インデックスには独自の構文があります。形式は、

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');

フルテキスト インデックスの構文に従って次のクエリを実行します

select * from test where match(content) against('aaaa');

慣性的思考によれば、 4 つのレコードが表示されるはずです。はい、しかし結果はレコードがありません。次のクエリが実行された場合にのみ、

show variables like '%ft%';

は

aaaa

レコードを見つけます。 ＃＃＃＃＃＃なぜ？この問題には多くの理由がありますが、最も一般的なのは

最小検索長

です。また、フルテキスト インデックスを使用する場合は、テスト テーブルに少なくとも 4 つのレコードが存在する必要があります。そうしないと、予期しない結果が発生します。

MySQL の全文インデックスには、最小検索長と最大検索長の 2 つの変数があります。長さが最小検索長より短く、最大検索長より長い単語はインデックス付けされません。平たく言えば、単語に対して全文インデックス検索を使用する場合、単語の長さが上記の 2 つの変数の範囲内である必要があります。 これら 2 つのデフォルト値は、次のコマンドを使用して表示できます

show variables like '%ft%';

可以看到这两个变量在 MyISAM 和 InnoDB 两种存储引擎下的变量名和默认值

// 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 的词语建立索引，而刚刚搜索的只有 aaaa 的长度大于等于 4。

配置最小搜索长度

全文索引的相关参数都无法进行动态修改，必须通过修改 MySQL 的配置文件来完成。修改最小搜索长度的值为 1，首先打开 MySQL 的配置文件 /etc/my.cnf，在 [mysqld] 的下面追加以下内容

[mysqld]innodb_ft_min_token_size = 1ft_min_word_len = 1

然后重启 MySQL 服务器，并修复全文索引。注意，修改完参数以后，一定要修复下索引，不然参数不会生效。

两种修复方式，可以使用下面的命令修复

repair table test quick;

或者直接删掉重新建立索引，再次执行上面的查询，a、aa、aaa 就都可以查出来了。

但是，这里还有一个问题，搜索关键字 a 时，为什么 aa、aaa、aaaa 没有出现结果中，讲这个问题之前，先说说两种全文索引。

两种全文索引

---

自然语言的全文索引

默认情况下，或者使用 in natural language mode 修饰符时，match() 函数对文本集合执行自然语言搜索，上面的例子都是自然语言的全文索引。

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语的在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。上面提到的，测试表中必须有 4 条以上的记录，就是这个原因。

这个机制也比较好理解，比如说，一个数据表存储的是一篇篇的文章，文章中的常见词、语气词等等，出现的肯定比较多，搜索这些词语就没什么意义了，需要搜索的是那些文章中有特殊意义的词，这样才能把文章区分开。

布尔全文索引

在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。

MySQL 内置的修饰符，上面查询最小搜索长度时，搜索结果 ft_boolean_syntax 变量的值就是内置的修饰符，下面简单解释几个，更多修饰符的作用可以查手册

• + 必须包含该词
• - 必须不包含该词
• > 提高该词的相关性，查询的结果靠前
• < 降低该词的相关性，查询的结果靠后
• (*)星号 通配符，只能接在词后面

对于上面提到的问题，可以使用布尔全文索引查询来解决，使用下面的命令，a、aa、aaa、aaaa 就都被查询出来了。

select * test where match(content) against('a*' in boolean mode);

总结

---

好了，差不多写完了，又到了总结的时候。

MySQL 的全文索引最开始仅支持英语，因为英语的词与词之间有空格，使用空格作为分词的分隔符是很方便的。亚洲文字，比如汉语、日语、汉语等，是没有空格的，这就造成了一定的限制。不过 MySQL 5.7.6 开始，引入了一个 ngram 全文分析器来解决这个问题，并且对 MyISAM 和 InnoDB 引擎都有效。

事实上，MyISAM 存储引擎对全文索引的支持有很多的限制，例如表级别锁对性能的影响、数据文件的崩溃、崩溃后的恢复等，这使得 MyISAM 的全文索引对于很多的应用场景并不适合。所以，多数情况下的建议是使用别的解决方案，例如 Sphinx、Lucene 等等第三方的插件，亦或是使用 InnoDB 存储引擎的全文索引。

几个注意点

1. 使用全文索引前，搞清楚版本支持情况；
2. 全文索引比 like + % 快 N 倍，但是可能存在精度问题；
3. 如果需要全文索引的是大量数据，建议先添加数据，再创建索引；
4. 对于中文，可以使用 MySQL 5.7.6 之后的版本，或者第三方插件。

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

以上がmysql全文インデックスとは何ですかの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。