DoneSpeak

MySql 全文检索两个字符的内容无法得到结果

Exception MySQL
字数: 1.6k时长: 6 min
2019/05/15 Share

问题描述

数据库中有如下的地址信息表，需要实现一个更具用户输入的任何内容进行搜索可能匹配的地址信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
-- MySQL版本: 5.7.25
CREATE TABLE Address 
(
    id BIGINT NOT NULL AUTO_INCREMENT,
    address VARCHAR(100) NOT NULL DEFAULT '',
    city VARCHAR(50) NOT NULL DEFAULT '',
    state VARCHAR(50) NOT NULL DEFAULT '',
    country VARCHAR(50) NOT NULL DEFAULT '',
    zip_code VARCHAR(10) NOT NULL DEFAULT '',
    FULLTEXT ftidx_location(address, city, state, country, zip_code)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

insert into Address(city, state) values ('Irving', 'TX');

容易想到利用如下的sql进行检索。

1
2
-- 这里的 ${input} 为用户输入的内容
select * from Address where match(address, city, state, country, zip_code) against (${input});

然而对于太短的输入，如 “TX”，即使数据库中存在 state = TX 的数据，该SQL也是无法检索到任何结果。或者输入 “Irvin” 也是无法查找到内容的。下面将对该问题进行分析和解决，使用”Irvin,TX”作为用户输入进行分析（不含双引号）。

原因分析

实现使用的是MySQL的FULLTEXT INDEX对(address, city, state, country, zip_code)进行了索引。FULLTEXT INDEX的配置保留了MySQL的默认配置，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> SHOW VARIABLES LIKE '%ft%';
+---------------------------------+----------------+
| Variable_name                   | Value          |
+---------------------------------+----------------+
| ft_boolean_syntax               | + -><()~*:""&| |
| ft_max_word_len                 | 84             |
| ft_min_word_len                 | 4              |
| ft_query_expansion_limit        | 20             |
| ft_stopword_file                | (built-in)     |
| innodb_ft_aux_table             |                |
| innodb_ft_cache_size            | 8000000        |
| innodb_ft_enable_diag_print     | OFF            |
| innodb_ft_enable_stopword       | ON             |
| innodb_ft_max_token_size        | 84             |
| innodb_ft_min_token_size        | 3              |
| innodb_ft_num_word_optimize     | 2000           |
| innodb_ft_result_cache_limit    | 2000000000     |
| innodb_ft_server_stopword_table |                |
| innodb_ft_sort_pll_degree       | 2              |
| innodb_ft_total_cache_size      | 640000000      |
| innodb_ft_user_stopword_table   |                |
+---------------------------------+----------------+

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的数据，查询的结果还是会为空。

1
select * from Address where match(address, city, state, country, zip_code) against ('TX');

  1. 修改 my.cnf，在 [mysqld] 后面加入配置项。

    1
    sudo vim /etc/mysql/my.cnf

    配置内容

    1
    2
    innodb_ft_min_token_size=2
    ft_min_word_len=2

  2. 重启mysql服务。 

    1
    sudo service mysql restart

  3. 重新构建索引文件。

    对于使用MyISAM的表需要手动修复。详细见：what to do when ‘ repair table ‘ query won’t work in mysql? 以及 Fine-Tuning MySQL Full-Text Search或者其中文翻译微调MySQL全文搜索。 

    1
    REPAIR TABLE Address QUICK;

    而对于使用InnoDB的表，可以使用如下指令对表进行索引的重新构建。该操作会获取到表的读锁。

    1
    ALTER TABLE Address ENGINE=INNODB;

    使用优化指令也可以起到同样的作用，同时这个指令会完成更多的优化作用。OPTIMIZE TABLE运行过程中，MySQL会锁定表。

    1
    2
    3
    OPTIMIZE TABLE Project;
    -- 执行之后会返回如下信息，但实际上是执行成功的
    -- Table does not support optimize, doing recreate + analyze instead

  4. 查看是否生效。 

    1
    2
    show variables like 'innodb_ft_min_token_size'; 
    show variables like 'ft_min_word_len';

    在修改之后执行，如果数据库中含有state=TX的数据都会被查询出来。 

    1
    select * from Address where match(address, city, state, country, zip_code) against ('TX');

使用IN BOOLEAN MODE匹配不完整单词

用户输入的内容的顺序为从左到右输入，也就是如果用户要输入多个单词，那么最左侧的单词必然是完整的，最右侧的单词可能是不完整的。那么可以在用户输入的检索地址末尾添加 * 通配符，使得可以匹配不完整单词。可以修改为:

1
select * from Address where match(address, city, state, country, zip_code) against ('TX,Irvin*' IN BOOLEAN MODE);

该方法依赖于MySQL自身的配置，如果确定要使用FULLTEXT INDEX，应该在创建数据库的时候就配置好需要的配置，以免影响已经上线的系统。

注意：如果用户本身的输入中也含有了ft_boolean_syntax指定的通配符，那么我们需要在程序中先进行一次格式化，以防出现非法查询，如 “Irvin**” 等。或者也可以限制ft_boolean_syntax的字符。

方法二: 使用LIKE做匹配

曲线救国，使用 Like 代替 FULLTEXT INDEX。在程序中按照MySQL的方法进行分词，并在每个单词之间增加通配符 %。

1
select * from Address where concat(address, ',', city, ',', state, ' ', zip_code, ' ', country) like '%Irvin%TX%';

使用该方法，将会失去对地址信息的索引，并因为使用了 Like，且查询的内容是以而导致需要对整表进行扫描。此外，Like 是没有匹配对度的，也就是结果的顺序将和匹配度无关。

注：Like 只有在非通配符开始的语句中才会使用到索引，如: “Irvin%” 将会使用索引，而 “%Irvin” 则不会使用到索引。

方法三: 增加 full_address 字段，并使用LIKE做匹配

添加一个完整的 full_address 字段，full_address 值为 address, city, state zip_code, country。

1
select * from Address where full_address like '%Irvin%TX%';

该方法主要是利用空间换时间，解决了方法二中每次查询都需要拼接字符串的耗时操作。

方法四: 使用专业的搜索引擎

使用更为专业的搜索引擎，如 Elasticsearch 或者 Solr。

参考

CATALOG
  1. 1. 问题描述
  2. 2. 原因分析
  3. 3. 解决方法
    1. 3.1. 方法一: 修改FULLTEXT INDEX配置
    2. 3.2. 方法二: 使用LIKE做匹配
    3. 3.3. 方法三: 增加 full_address 字段，并使用LIKE做匹配
    4. 3.4. 方法四: 使用专业的搜索引擎
  4. 4. 参考
Total : 22
2021
2020
2019
2018
SpringBoot1 maven0 algorithm3 设计模式0 Spring3 AOP0 事件驱动0 RESTful1 Git5 Teamwork1 Payment1 Exception1 MySQL1 JavaTool1 DataExchange1 Protobuf3 Json2 Serialization1 excel1 spring-scurity1 spring2 security1 leetcode1 Shell3 Python1 Springboot1
Design-Pattern0