MySQL模糊搜索优化
时间:2019-05-14 17:38 来源:linux.it.net.cn 作者:IT
InnoDB引擎对FULLTEXT索引的支持是MySQL5.6新引入的特性，之前只有MyISAM引擎支持FULLTEXT索引。对于FULLTEXT索引的内容可以使用MATCH()…AGAINST语法进行查询。
全文搜索的语法：
MATCH(col1,col2,…) AGAINST (expr[search_modifier])。
其中MATCH中的内容为已建立FULLTEXT索引并要从中查找数据的列，AGAINST中的expr为要查找的文本内容，search_modifier为可选搜索类型。
search_modifier的可能取值有：
IN NATURAL LANGUAGEMODE
IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
IN BOOLEAN MODE
WITH QUERY EXPANSION
search_modifier的每个取值代表一种类型的全文搜索，分别为自然语言全文搜索、带查询扩展的自然语言全文搜索、布尔全文搜索、查询扩展全文搜索（默认使用IN NATURAL LANGUAGE MODE）。 这篇博客主要学习的是默认的自然语言全文搜索。
MySQL中全文索引的关键字为FULLTEXT，目前可对MyISAM表和InnoDB表的CHAR、VARCHAR、TEXT类型的列创建全文索引。全文索引同其他索引一样，可在创建表是由CREATE TABLE语句创建也可以在表创建之后用ALTER TABLE或者CREATE INDEX命令创建(对于要导入大量数据的表先导入数据再创建FULLTEXT索引比先创建索引后导入数据会更快)。
自然语言全文搜索是MySQL全文搜索的默认搜索方式，实现从一个文本集合中搜索给定的字符串。这里，文本集合指的是指由FULLTEXT索引的一个或者多个列。
示例：
建表，并给content字段加FULLTEXT索引
create table articles (
id int unsigned auto_increment not null primary key,
title varchar(200),
content text,
fulltext (content)
) engine=InnoDB;
或者
create table articles (
id int unsigned auto_increment not null primary key,
title varchar(200),
content text,
) engine=InnoDB;
alter table articles add fulltext index_content(content) ;
导入数据：
insert into articles (title,content) values
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you ...'),
('Optimizing MySQL','In this tutorial we will ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
例1：
select * from articles where match (content) against ('database');
可以看到，语句查找到了包含指定内容的行。实际上，返回的行是按与所查找内容的相关度由高到低的顺序排列的。这个相关度的值由WHERE语句中的MATCH (…) AGAINST (…)计算所得，是一个非负浮点数。该值越大表明相应的行与所查找的内容越相关，0值表明不相关。该值基于行中的单词数、行中不重复的单词数、文本集合中总单词数以及含特定单词的行数计算得出。
例2： 由上例可知MATCH (…) AGAINST (…)实际上会计算一个相关值，可通过下例来验证。
select id, match(content) against ('database') as score from articles;
可以看到，所得结果的第二列即为改行与查找内容的相关度。例1中所得结果的顺序就是按此相关度排列的。
例3： 若想既看到查找到的结果又需要了解具体的相关度，可用下述方法达成。
select id, content, match (content) against ('database') as score from articles where match (content) against ('databse');
可以看到，通过在查找部分和条件部分分别使用相同的MATCH(…) AGAINST(…)可以同时获取两方面的内容（不会增加额外开销，优化器知道两个MATCH(…) AGAINST(..)是相同的，只会执行一次该语句）。
注意事项 ：
1）默认情况下全文搜索大小写不敏感，可以通过为FULLTEXT索引列所使用的字符集指定一个特定的校对集来改变这种行为。
2）考虑下述两个SELECT语句：
1. SELECT COUNT(*) FROM articles WHERE MATCH (content) AGAINST('database');
2. SELECT COUNT(IF(MATCH (content) AGAINST('database'), 1, NULL)) AS count FROM articles;
这两条查询语句均可返回匹配的行数。但第一条语句可以利用基于WHERE从句的索引查找，因此在匹配的行数较少时速度较第二句更快。第二句执行了全表扫描，因此在匹配的行数较多时较第一句更快。
3）MATCH()函数中的列必须与FULLTEXT索引中的列相同。如MATCH(content)与FULLTEXT(content)。若要搜索某两列，如title和content列，则需另外为该两列建全文索引FULLTEXT(title,content)，然后用MATCH(title,content)搜索。
4）对于InnoDB表MATCH()中的列仅能来自于同一个表，因为索引不能跨多张表（MyISAM表的的布尔搜索因为可以不使用索引，所以可以跨多张表中的列，但速度很慢）。 全文搜索不仅可以搜索类似例1中‘database’这样的单个的单词，还可以搜索句子（这才是其被称为‘全文搜索‘的关键）。
5）全文搜索把任何数字、字母、下划线序列看作是单词，还可以包含 “ ’ ” 如 aaa’bbb 被解析为一个单词，但aaa’’bbb被解析为两个单词，FULLTEXT解析器自动移除首尾的 “ ’ ” ，如 ’aaa’bbb’ 被解析为 aaa’bbb 。
6）FULLTEXT解析器用“ ”（空格）、“,”（逗号）“.”（点号）作为默认的单词分隔符，因此对于不使用这些分隔符的语言如汉语来说，FULLTEXT解析器不能正确的识别单词，对于这种情况需做额外处理。
7）全文搜索中一些单词会被忽略。首先是过短的单词，InnoDB全文搜索中默认为3个字符，MyISAM默认4个字符，可通过在创建FULLTEXT索引前改变配置参数来改变默认行为，对于InnoDB该参数为：innodb_ft_min_token_size，对于MyISAM为ft_min_word_len；另外stopword列表中的单词会被忽略。stopword列表包含诸如“the”、“or”、“and”等常用单词，这些词通常被认为没有什么语义价值。
MySQL有内建的停止字列表，但是可以所使用自定义的停止字列表来覆盖默认列表。对于InnoDB控制停止字的配置参数为innodb_ft_enable_stopword，innodb_ft_server_stopword_table， innodb_ft_user_stopword_table对于MyISAM参数为ft_stopword_file。
8）文本集合和查询语句中的单词的权重由该单词在集合或语句中的重要性确定。单词在越多的行中出现则该单词的权重越低，因为这表明其在文本集合中的语义价值较小。反之权重越高。
无论是LIKE搜索还是FULLTEXT搜索，其性能都会随着记录数的增长而下降，因此，若网站项目中的文本搜索数据库记录数庞大的一定规模后，可能需要考虑使用MySQL数据库全文搜索以外的文本搜索解决方案了。
(责任编辑：IT)
InnoDB引擎对FULLTEXT索引的支持是MySQL5.6新引入的特性，之前只有MyISAM引擎支持FULLTEXT索引。对于FULLTEXT索引的内容可以使用MATCH()…AGAINST语法进行查询。
全文搜索的语法：
MATCH(col1,col2,…) AGAINST (expr[search_modifier])。
其中MATCH中的内容为已建立FULLTEXT索引并要从中查找数据的列，AGAINST中的expr为要查找的文本内容，search_modifier为可选搜索类型。
search_modifier的可能取值有：
IN NATURAL LANGUAGEMODE
IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
IN BOOLEAN MODE
WITH QUERY EXPANSION
search_modifier的每个取值代表一种类型的全文搜索，分别为自然语言全文搜索、带查询扩展的自然语言全文搜索、布尔全文搜索、查询扩展全文搜索（默认使用IN NATURAL LANGUAGE MODE）。 这篇博客主要学习的是默认的自然语言全文搜索。
MySQL中全文索引的关键字为FULLTEXT，目前可对MyISAM表和InnoDB表的CHAR、VARCHAR、TEXT类型的列创建全文索引。全文索引同其他索引一样，可在创建表是由CREATE TABLE语句创建也可以在表创建之后用ALTER TABLE或者CREATE INDEX命令创建(对于要导入大量数据的表先导入数据再创建FULLTEXT索引比先创建索引后导入数据会更快)。
自然语言全文搜索是MySQL全文搜索的默认搜索方式，实现从一个文本集合中搜索给定的字符串。这里，文本集合指的是指由FULLTEXT索引的一个或者多个列。
示例：
建表，并给content字段加FULLTEXT索引
create table articles (
id int unsigned auto_increment not null primary key,
title varchar(200),
content text,
fulltext (content)
) engine=InnoDB;
或者
create table articles (
id int unsigned auto_increment not null primary key,
title varchar(200),
content text,
) engine=InnoDB;
alter table articles add fulltext index_content(content) ;
导入数据：
insert into articles (title,content) values
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you ...'),
('Optimizing MySQL','In this tutorial we will ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
例1：
select * from articles where match (content) against ('database');
可以看到，语句查找到了包含指定内容的行。实际上，返回的行是按与所查找内容的相关度由高到低的顺序排列的。这个相关度的值由WHERE语句中的MATCH (…) AGAINST (…)计算所得，是一个非负浮点数。该值越大表明相应的行与所查找的内容越相关，0值表明不相关。该值基于行中的单词数、行中不重复的单词数、文本集合中总单词数以及含特定单词的行数计算得出。
例2： 由上例可知MATCH (…) AGAINST (…)实际上会计算一个相关值，可通过下例来验证。
select id, match(content) against ('database') as score from articles;
可以看到，所得结果的第二列即为改行与查找内容的相关度。例1中所得结果的顺序就是按此相关度排列的。
例3： 若想既看到查找到的结果又需要了解具体的相关度，可用下述方法达成。
select id, content, match (content) against ('database') as score from articles where match (content) against ('databse');
可以看到，通过在查找部分和条件部分分别使用相同的MATCH(…) AGAINST(…)可以同时获取两方面的内容（不会增加额外开销，优化器知道两个MATCH(…) AGAINST(..)是相同的，只会执行一次该语句）。
注意事项 ：
1）默认情况下全文搜索大小写不敏感，可以通过为FULLTEXT索引列所使用的字符集指定一个特定的校对集来改变这种行为。
2）考虑下述两个SELECT语句：
1. SELECT COUNT(*) FROM articles WHERE MATCH (content) AGAINST('database');
2. SELECT COUNT(IF(MATCH (content) AGAINST('database'), 1, NULL)) AS count FROM articles;
这两条查询语句均可返回匹配的行数。但第一条语句可以利用基于WHERE从句的索引查找，因此在匹配的行数较少时速度较第二句更快。第二句执行了全表扫描，因此在匹配的行数较多时较第一句更快。
3）MATCH()函数中的列必须与FULLTEXT索引中的列相同。如MATCH(content)与FULLTEXT(content)。若要搜索某两列，如title和content列，则需另外为该两列建全文索引FULLTEXT(title,content)，然后用MATCH(title,content)搜索。
4）对于InnoDB表MATCH()中的列仅能来自于同一个表，因为索引不能跨多张表（MyISAM表的的布尔搜索因为可以不使用索引，所以可以跨多张表中的列，但速度很慢）。 全文搜索不仅可以搜索类似例1中‘database’这样的单个的单词，还可以搜索句子（这才是其被称为‘全文搜索‘的关键）。
5）全文搜索把任何数字、字母、下划线序列看作是单词，还可以包含 “ ’ ” 如 aaa’bbb 被解析为一个单词，但aaa’’bbb被解析为两个单词，FULLTEXT解析器自动移除首尾的 “ ’ ” ，如 ’aaa’bbb’ 被解析为 aaa’bbb 。
6）FULLTEXT解析器用“ ”（空格）、“,”（逗号）“.”（点号）作为默认的单词分隔符，因此对于不使用这些分隔符的语言如汉语来说，FULLTEXT解析器不能正确的识别单词，对于这种情况需做额外处理。
7）全文搜索中一些单词会被忽略。首先是过短的单词，InnoDB全文搜索中默认为3个字符，MyISAM默认4个字符，可通过在创建FULLTEXT索引前改变配置参数来改变默认行为，对于InnoDB该参数为：innodb_ft_min_token_size，对于MyISAM为ft_min_word_len；另外stopword列表中的单词会被忽略。stopword列表包含诸如“the”、“or”、“and”等常用单词，这些词通常被认为没有什么语义价值。
MySQL有内建的停止字列表，但是可以所使用自定义的停止字列表来覆盖默认列表。对于InnoDB控制停止字的配置参数为innodb_ft_enable_stopword，innodb_ft_server_stopword_table， innodb_ft_user_stopword_table对于MyISAM参数为ft_stopword_file。
8）文本集合和查询语句中的单词的权重由该单词在集合或语句中的重要性确定。单词在越多的行中出现则该单词的权重越低，因为这表明其在文本集合中的语义价值较小。反之权重越高。
无论是LIKE搜索还是FULLTEXT搜索，其性能都会随着记录数的增长而下降，因此，若网站项目中的文本搜索数据库记录数庞大的一定规模后，可能需要考虑使用MySQL数据库全文搜索以外的文本搜索解决方案了。
(责任编辑：IT) |