RDS MySQL全文检索相关问题及处理

RDS MySQL全文检索相关问题及处理

更新时间:2020-02-12 15:45:50

概述

本文主要介绍RDS MySQL全文检索相关问题及处理。

 

详细信息

阿里云提醒您:

  • 如果您对实例或数据有修改、变更等风险操作,务必注意实例的容灾、容错能力,确保数据安全。
  • 如果您对实例(包括但不限于ECS、RDS)等进行配置与数据修改,建议提前创建快照或开启RDS日志备份等功能。
  • 如果您在阿里云平台授权或者提交过登录账号、密码等安全信息,建议您及时修改。

本文通过如下几个方面介绍全文检索。

 

RDS MySQL对全文检索的支持

RDS MySQL 5.5版本仅MyISAM引擎表支持全文索引,RDS MySQL 5.6版本MyISAM和InnoDB引擎都支持全文索引。

 

RDS MySQL全文检索相关参数

  1. 参考如下SQL语句,可以查看相关参数。
    1. 查看MyISAM引擎表全文检索相关参数,SQL语句如下所示。
      show global variables like 'ft_%';
    2. 查看InnoDB引擎表全文检索相关参数,SQL语句如下所示。
      show global variables like 'innodb_ft%';
  2. 如果需要在RDS控制台参数设置页面中设置下列参数。
    # 参数名称 默认值 最小值 最大值 作用
    1 ft_min_word_len 4 1 3600 MyISAM引擎表全文索引包含的最小词长度。
    2 ft_query_expansion_limit 20 0 1000 MyISAM引擎表使用with query expansion进行全文搜索的最大匹配数。
    3 innodb_ft_min_token_size 3 0 16 InnoDB引擎表全文索引包含的最小词长度。
    4 innodb_ft_max_token_size 84 10 84 InnoDB引擎表全文索引包含的最大词长度。

 

RDS MySQL对中文全文检索的支持

MyISAM引擎表和InnoDB引擎表(RDS MySQL 5.6版本)都支持中文全文检索。

 

MyISAM引擎表

MyISAM引擎表需要将ft_min_word_len设置为小于等于需要检索的最小分词长度,设置完毕后建议重新创建表上已有的全文索引。对于中文检索,建议将ft_min_word_len设置为1,否则可能出现查不到数据的情况,如下图所示。

ft_02.png

 

重建全文索引示例

表结构如下所示。

CREATE TABLE `my_ft_test_02` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `idx_ft_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

 

重建全文索引步骤。

  1. 执行如下SQL语句,删除已有的全文索引。
    alter table my_ft_test_02 drop key idx_ft_name;
  2. 执行如下SQL语句,重新添加全文索引。
    alter table my_ft_test_02 add fulltext key idx_ft_name (name);

 

InnoDB引擎表

InnoDB引擎表需要将innodb_ft_min_token_size设置为小于等于需要检索的最小分词长度,设置完毕后建议重新创建表上已有的全文索引,对于中文检索,建议将ft_min_word_len设置为1,否则可能出现查不到数据的情况,如下图所示。

ft_01.png

 

重建全文索引示例

表结构如下所示。

CREATE TABLE `my_ft_test_01` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `idx_ft_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

 

重建全文索引步骤。

  1. 执行如下SQL语句,删除已有的全文索引。
    alter table my_ft_test_01 drop key idx_ft_name;
  2. 执行如下SQL语句,重新添加全文索引。
    alter table my_ft_test_01 add fulltext key idx_ft_name (name);

 

like语句和全文检索的比较

like语句

对于数据量比较小,查询条件无法分词的情况下,可以考虑使用like来进行查询,如下图所示。但是由于like这样的条件无法使用字段上的索引,因此通常执行时间较长,会耗费比较多的资源。建议结合查询其他条件来使用,这样可以尽量使用其他字段的索引来加速查询。

ft_03.png

 

全文检索

全文检索本身是按词进行匹配,因此返回的结果会和like的方式有所区别,如下图所示。

ft_04.png

 

ft_query_expansion_limit参数作用

MySQL全文检索支持扩展检索,具体请参考带查询扩展的全文检索。ft_query_expansion_limit参数的作用是指定MyISAM引擎表使用with query expansion进行全文搜索的最大匹配数,下面以一个例子来说明其作用。

  1. 执行如下SQL语句,确认ft_query_expansion_limit参数值当前设置为20。
    show global variables like 'ft_qu%';
    系统显示类似如下。
    ft_06.png
  2. 执行如下SQL语句,查看当前表中相关的记录情况。
    select * from my_ft_test_01 where id >= 117;
    系统显示类似如下,good和Hello都出现20次。
  3. 执行如下SQL语句,查看使用查询扩展的结果。
    select * from my_ft_test_01 where math(name) AGAINST ('孙悟空' with query expansion);
    系统显示类似如下,返回结果中包含good。

 

适用于

  • 云数据库 RDS MySQL 版