mysql 전체 텍스트 인덱스란 무엇입니까?

MySQL에서 전체 텍스트 인덱싱은 데이터베이스에 저장된 책 전체 또는 기사 전체에서 어떤 정보를 찾아내는 기술입니다. 필요한 쿼리는 대부분 수치 비교, 범위 필터링 등을 통해 완성할 수 있습니다. 하지만 키워드 매칭을 통해 쿼리를 필터링하려면 원래의 정확한 수치 비교와 전체 텍스트가 아닌 유사성을 기반으로 한 쿼리가 필요합니다. 인덱싱은 이 시나리오를 위해 설계되었습니다.

이 튜토리얼의 운영 환경: windows7 시스템, mysql8 버전, Dell G3 컴퓨터.

전체 텍스트 검색은 데이터베이스에 저장된 책이나 기사 전체에서 어떤 정보든 찾아내는 기술입니다. 필요에 따라 전문의 장, 절, 문단, 문장, 단어 등에 대한 정보를 얻을 수 있으며, 다양한 통계 및 분석도 수행할 수 있습니다. 전체 텍스트 인덱싱은 일반적으로 역인덱싱을 통해 구현됩니다.

필요한 쿼리는 대부분 수치비교, 범위 필터링 등을 통해 완성할 수 있습니다. 하지만 키워드 매칭을 통해 쿼리를 필터링하려면 원래의 정확한 수치비교가 아닌 유사도 기반의 쿼리가 필요합니다. 전체 텍스트 인덱싱은 이 시나리오를 위해 설계되었습니다.

퍼지 일치를 달성하기 위해 like + %를 사용할 수 있는데 왜 전체 텍스트 인덱싱이 필요한가요? like + %는 텍스트가 상대적으로 작을 때 적합하지만, 대량의 텍스트 데이터를 검색하는 경우에는 상상할 수 없습니다. 많은 양의 데이터가 있는 경우 전체 텍스트 인덱싱은 +%보다 N배 더 빠를 수 있습니다. 속도는 일정하지 않지만 전체 텍스트 인덱싱에는 정확성 문제가 있을 수 있습니다. 전체 텍스트 인덱싱에 관심을 두지 않았을 수도 있지만 적어도 하나의 전체 텍스트 인덱싱 기술인 다양한 검색 엔진에 대해 잘 알고 있어야 합니다. 검색 엔진의 인덱스 개체는 매우 많은 양의 데이터이고 일반적으로 그 뒤에 관계형 데이터베이스가 없지만 전체 텍스트 인덱싱의 기본 원칙은 동일합니다.

---

버전 지원시작하기 전에 전체 텍스트 인덱스 버전, 스토리지 엔진 및 데이터 유형 지원에 대해 이야기해 보겠습니다.

이전 버전의 MySQL 5.6에서는 MyISAM 스토리지 엔진만 전체 텍스트를 지원했습니다. index;

MySQL 5.6 이상 버전, MyISAM 및 InnoDB 스토리지 엔진 모두 전체 텍스트 인덱스를 지원합니다.

전체 텍스트 인덱스는 필드의 데이터 유형이 char, varchar, text 및 해당 시리즈인 경우에만 구축할 수 있습니다.

전체 텍스트 인덱스를 테스트하거나 사용할 때는 먼저 MySQL 버전, 스토리지 엔진 및 데이터 유형이 전체 텍스트 인덱스를 지원하는지 확인해야 합니다.

전체 텍스트 인덱스 작업

인덱싱 작업은 검색만으로 수행할 수 있지만 여기서 다시 설명하겠습니다.

2. Create

테이블 생성 시 전체 텍스트 인덱스 생성

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. O를 수정하고 삭제하고 직접 다시 빌드하세요.

1. Delete

전체 텍스트 인덱스를 삭제하려면 DROP INDEX를 직접 사용하세요.

drop index content_tag_fulltext    on fulltext_test;

SQL 문 ALTER TABLE

alter table fulltext_test    drop index content_tag_fulltext;

전체 텍스트 인덱스 사용

및 일반적으로 사용됨 + % Different와 같은 퍼지 일치 사용의 경우 전체 텍스트 인덱스에는 자체 구문 형식이 있으며

select * from fulltext_test 
    where match(content,tag) against('xxx xxx');

와 같은 일치 및 반대 키워드를 사용합니다. 참고:
1. 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의 전체 텍스트 인덱스에는 최소 검색 길이와 최대 검색 길이라는 두 가지 변수가 있습니다. 길이가 최소 검색 길이보다 작고 최대 검색 길이보다 큰 단어는 인덱스되지 않습니다. 일반인의 관점에서 보면, 단어에 대해 전체 텍스트 색인 검색을 사용하려면 단어의 길이가 위의 두 변수 범위 내에 있어야 합니다.

이 두 가지의 기본값은 다음 명령을 사용하여 볼 수 있습니다

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 중국어 웹사이트의 기타 관련 기사를 참조하세요!