MySql 全文检索两个字符的内容无法得到结果
问题描述
数据库中有如下的地址信息表,需要实现一个更具用户输入的任何内容进行搜索可能匹配的地址信息。
容易想到利用如下的 sql 进行检索。
然而对于太短的输入,如 "TX",即使数据库中存在 state = TX 的数据,该 SQL 也是无法检索到任何结果。或者输入 "Irvin" 也是无法查找到内容的。下面将对该问题进行分析和解决,使用"Irvin,TX"作为用户输入进行分析(不含双引号)。
原因分析
实现使用的是 MySQL 的FULLTEXT INDEX
对(address, city, state, country, zip_code)进行了索引。FULLTEXT INDEX
的配置保留了 MySQL 的默认配置,如下:
FULLTEXT 索引是按照“词”进行的索引,MySQL 默认的分词方法是所有非字母和数字的特殊符号都是分词符(如果希望对中文进行分词,则可以使用 MySQL 内置的 ngram 全文检索插件)。按照分词方法,"Irving,TX" 将被划分为 "Irving" 和 "TX" 两个词。
再看下配置的内容,其中 innodb_ft_min_token_size
表示最短的索引词项,也就是只会对 3 个英文字符或者 3 个英文字符以上的关键字进行建立索引操作。MySQL 不会对"TX"创建索引,这也就是没法搜索到"TX"的数据的原因。而之所以没法搜索到"Irvin",是因为 Fulltext 是对“词”进行构建索引,也就是索引文件中只有”Irving“的索引,没有“Irvin”的索引。
解决方法
方法一: 修改 FULLTEXT INDEX 配置
修改最小词项长度为 2,允许对长度为 2 的词进行索引。并使用IN BOOLEAN MODE
匹配不完整单词。
修改最小词项长度为 2,允许对长度为 2 的词进行索引
觉得 1 太小了,一般的单词都不会是一个字母的,而且如果这个数值太小,会导致索引文件过大,不利于索引的更新。因而修改为 2 就行了。在 MyISAM 数据库引擎中使用的是ft_min_word_len
,而 InnoDB 中使用的是innodb_ft_min_token_size
。
在修改之前执行,即使数据库中含有 state=TX 的数据,查询的结果还是会为空。
修改 my.cnf,在 [mysqld] 后面加入配置项。
配置内容
重启 mysql 服务。
重新构建索引文件。
对于使用 MyISAM 的表需要手动修复。详细见:what to do when ' repair table ' query won't work in mysql? 以及 Fine-Tuning MySQL Full-Text Search或者其中文翻译微调MySQL全文搜索。
而对于使用 InnoDB 的表,可以使用如下指令对表进行索引的重新构建。该操作会获取到表的读锁。
使用优化指令也可以起到同样的作用,同时这个指令会完成更多的优化作用。OPTIMIZE TABLE 运行过程中,MySQL 会锁定表。
查看是否生效。
在修改之后执行,如果数据库中含有 state=TX 的数据都会被查询出来。
使用
IN BOOLEAN MODE
匹配不完整单词
用户输入的内容的顺序为从左到右输入,也就是如果用户要输入多个单词,那么最左侧的单词必然是完整的,最右侧的单词可能是不完整的。那么可以在用户输入的检索地址末尾添加 * 通配符,使得可以匹配不完整单词。可以修改为:
该方法依赖于 MySQL 自身的配置,如果确定要使用FULLTEXT INDEX
,应该在创建数据库的时候就配置好需要的配置,以免影响已经上线的系统。
注意:如果用户本身的输入中也含有了ft_boolean_syntax
指定的通配符,那么我们需要在程序中先进行一次格式化,以防出现非法查询,如 “Irvin**” 等。或者也可以限制ft_boolean_syntax
的字符。
方法二: 使用 LIKE 做匹配
曲线救国,使用 Like
代替 FULLTEXT INDEX
。在程序中按照 MySQL 的方法进行分词,并在每个单词之间增加通配符 %
。
使用该方法,将会失去对地址信息的索引,并因为使用了 Like
,且查询的内容是以而导致需要对整表进行扫描。此外,Like
是没有匹配对度的,也就是结果的顺序将和匹配度无关。
注:Like
只有在非通配符开始的语句中才会使用到索引,如: "Irvin%" 将会使用索引,而 "%Irvin" 则不会使用到索引。
方法三: 增加 full_address 字段,并使用 LIKE 做匹配
添加一个完整的 full_address
字段,full_address
值为 address, city, state zip_code, country
。
该方法主要是利用空间换时间,解决了方法二中每次查询都需要拼接字符串的耗时操作。
方法四: 使用专业的搜索引擎
使用更为专业的搜索引擎,如 Elasticsearch 或者 Solr。
参考
版权声明: 本文为 InfoQ 作者【DoneSpeak】的原创文章。
原文链接:【http://xie.infoq.cn/article/8b864de43dd9a70561b628c5b】。文章转载请联系作者。
评论