MYSQL全文索引及Match() against()踩坑记录-超详细超实用

场景一

当我们使用mysql模糊查询时，经常会遇到如下情况：

如果我们customer_manager_no字段是可能会存多个值，用逗号隔开这种；当我们想查找出customer_manager_no字段包含：'wgx2’的数据，可能我们会直接想到如下写法：

但这样肯定不正确；字段包含：‘wgx22’的数据也出来了；那我们改怎么做呢？
可能一通百度；我们会看到如下写法：

这样确实可以解决问题了。

但是上面的写法会有如下的问题：

1.like关键字‘%obj%’这种写法是不会走索引的，‘obj%’这种使用索引；但是达不到查询效果。
2.FIND_IN_SET（）函数能达到查询效果，但是不会走索引；如果数据量达到几万及以上，效率会非常慢；甚至造成系统无法运行。

问题解决方案：MYSQL全文索引及Match() against()

全文检索在 MySQL 中就是一个 FULLTEXT 类型索引。

一、创建全文索引

格式：
create fulltext index 索引名 on 表名(字段名1,字段名2...);
1
2

二、使用全文索引及Match() against()函数

1.基本用法

SELECT a.customer_id,a.customer_no,a.customer_name,a.customer_manager_no,
	   a.customer_manager_name,a.trust_manager_no
from customer_base_info a 
where Match(a.customer_manager_no) Against('wgx2' )
1
2
3
4

2.扩展用法

当然Match() against()函数还有相应的扩展用法;比如：

格式
select 字段 from 表名 where match(字段) against('搜索字符串' with query expansion);

机制：
首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行
其次，Mysql检查这些匹配行并选择所有有用的词
再次，Mysql再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词
1
2
3
4
5
6
7

3.布尔文本搜索

格式
select 字段 from 表名 where match(字段) against('需要的词 其他条件的词' in boolean mode);
机制：

要匹配的词
要排斥的词（如果某行包含这个词，则不返回该行，即使它包含其他指定的词）
排列提示（指定某些词比其他词更重要，更重要的词等级更高）
表达式分组
另外一些内容

布尔操作符说明：
+	包含，词必须存在
-	排除，词必须不出现
>	包含，而且增加等级值
<	包含，且减少等级值
()	把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~	取消一个词的排序值
*	词尾的通配符
""	定义一个短语（与单个词的列表不样，它匹配整个短语以便包含或排除这个短语）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

三、使用条件

1.存储引擎必须是MyISAM或者mysql5.6以上的InnoDB
2.字段类型必须是char，varchar，text
3.如果存储引擎必须是MyISAM，由于Mysql默认配置的索引词长是4，所以要支持中文单字的话，首先要修改ft_min_word_len参数；如果是2可能会出现搜索不到数字或者英文的情况，可以改为1，一般是/etc/my.cnf，如果没找到
4.如果存储引擎必须是InnoDB，改了ft_min_word_len参数为1；1个、2个数字或字母还是起不到搜索的效果；查不出数据这是一个坑；必须要将innodb_ft_min_token_size的值改为1才行；亲测有效。

四、索引使用分析

1.适用场景

1）表中该字段中的数据量庞大
2）经常被检索，经常出现在where子句中的字段
3）经常被DML操作的字段不建议添加索引

2.优点

1）大大提高检索数据的性能效率
2）在表连接的连接条件，可以加速表与表直接的相连
3）在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)

3.缺点

1）创建与维护索引会消耗时间，并随着数据量的增加而增加
2）索引也会占用物理存储空间
3）在进行DML操作的时候，索引也要动态的维护，会降低数据的维护速度