《MySQL中级系列教程-第七章-全文索引》

￡神魔★判官ぃ 2023-01-03 01:48 135阅读 0赞

# 《MySQL中级系列教程-第七章-全文索引》 #

## 1.1 全文索引概念 ##

如果希望通过关键字的匹配度来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。MySQL也提供有类似于Lucene的全文检索技术，用于处理大规模数据检索，其处理速度比like高效的多

全文索引使用说明：

*  MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引；
 *  只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

创建全文索引：

create fulltext index index_name on table_name(col_name);
    
    alter table table_name add fulltext index index_name(col_name);

使用全文索引：

select * from table_name where match(fulltext_col) against('content');

> match函数中指定全文索引列，asainst函数中指定具体要搜索的内容；

## 1.2 匹配长度 ##

MySQL 中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。通俗点就是说，想对一个词语使用全文索引搜索，那么这个词语的长度必须在以上两个变量的区间内。

我们可以通过以下命令查询：

show variables like '%ft%';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center]

其中前缀带有innodb的为innodb引擎变量，其余为myisam引擎变量

// MyISAM变量
    ft_min_word_len = 4;
    ft_max_word_len = 84;
    
    // InnoDB变量
    innodb_ft_min_token_size = 3;
    innodb_ft_max_token_size = 84;

> 我们可以看出innodb的最小搜索长度为3，最大搜索长度为84；实际开发中最大搜索长度可能会设大

创建测试表：

create table test (
        id int(11) not null auto_increment,
        content varchar(30) not null,
        PRIMARY KEY (`id`) USING BTREE,
        FULLTEXT INDEX (`content`)
    ) engine=myisam default charset=utf8;
    
    insert into test(content) values('a'),('aa'),('aaa'),('aaaa');

执行查询：

select * from test where match(content) against('a');
    select * from test where match(content) against('aa');
    select * from test where match(content) against('aaa');
    select * from test where match(content) against('aaaa');

发现只有搜索`aaa`和`aaaa`时才会出现记录；

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 1]

我们可以尝试修改mysql的系统配置（`/etc/my.cnf`）：

[mysqld]
    innodb_ft_min_token_size = 1
    ft_min_word_len = 1

重启mysql服务：

systemctl restart mysqld

修复索引：

repair table test quick;		-- myisam(修复表)
    
    optimize table test;			-- innodb(优化表)

> 修改完参数以后，一定要修复下索引，因为当初建立索引是基于`innodb_ft_min_token_size`=1的

## 1.3 全文索引的模式 ##

### 1.3.1 自然语言处理模式 ###

*  `in natural language mode`：自然语言处理模式；默认情况下，MySQL使用 in natural language mode 修饰符，match() 函数对文本集合执行自然语言搜索，实现从一个文本集合中搜索给定的字符串

创建测试表：

CREATE TABLE `goods`  (
      `id` int(11) NOT NULL,
      `title` varchar(255),
      PRIMARY KEY (`id`) USING BTREE,
      FULLTEXT INDEX (`title`)
    ) ENGINE = InnoDB ;
    
    INSERT INTO `goods`(`id`, `title`) VALUES (1, 'huawei 5G zhineng paizhao shouji');
    INSERT INTO `goods`(`id`, `title`) VALUES (2, 'xiaomi 5G zhineng youxi shouji');
    INSERT INTO `goods`(`id`, `title`) VALUES (3, 'xiaomi 4G fashao shouji');
    INSERT INTO `goods`(`id`, `title`) VALUES (4, 'huawei 4G youxi qumianping shouji');

查询：

select * from goods where match(title) against('xiaomi 5G' in natural language mode);
    
    select * from goods where match(title) against('xiaomi 5G');		-- 简写

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 2]

### 1.3.2 布尔处理模式 ###

*  `in boolean mode`：布尔处理模式；如果在against()函数中指定了in boolean mode模式，则MySQL会执行布尔全文搜索。在该搜索模式下，待搜索单词前或后的一些特定字符会有特殊的含义。

--------------------

*  **`+`** 必须包含该词
 *  **`-`** 必须不包含该词
 *  **`>`** 提高该词的相关性，查询的结果靠前
 *  **`<`** 降低该词的相关性，查询的结果靠后
 *  **`*`** 通配符，只能接在词后面
 *  **`()`** 分组查询

**1）包含与不包含：**

-- 分词查询
    select * from goods where match(title) against('xiaomi 5G' in boolean mode);
    
    -- 必须不包含5G词条
    select * from goods where match(title) against('xiaomi -5G' in boolean mode);
    
    -- 必须包含5G词条
    select * from goods where match(title) against('xiaomi +5G' in boolean mode);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 3]

**2）提高/降低权重（相关性）**

select * from goods where match(title) against('xiaomi >4G' in boolean mode);
    
    select * from goods where match(title) against('xiaomi <4G' in boolean mode);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 4]

**3）通配符（模糊）查询：**

select * from goods where match(title) against('xiao' in boolean mode);
    
    select * from goods where match(title) against('xiao*' in boolean mode);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 5]

**4）分组查询：**

查询必须包含`5G`并且必须包含`paizhao`或`youxi`的商品，`youxi`商品权重降低

select * from goods where match(title) against('+5G +(paizhao <youxi)' in boolean mode);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 6]

## 1.4 N-gram中文分词 ##

MySQL的全文搜索对于英文是基于空格的分词，由于中文没有空格，因此MySQL的全文索引对中文支持的不是很友好

中文查询测试：

INSERT INTO `test`.`goods`(`id`, `title`) VALUES (5, '华为5G智能拍照手机');
    INSERT INTO `test`.`goods`(`id`, `title`) VALUES (6, '小米5G智能游戏手机');
    INSERT INTO `test`.`goods`(`id`, `title`) VALUES (7, '小米4G发烧 手机');
    INSERT INTO `test`.`goods`(`id`, `title`) VALUES (8, '华为4G游戏曲面屏手机');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 7]

使用ngram中文分词：

CREATE TABLE `article`  (
      `id` int(11) NOT NULL,
      `title` varchar(255) ,
      PRIMARY KEY (`id`) USING BTREE,
      FULLTEXT INDEX `title`(`title`) with parser ngram				-- 使用ngram中文分词器
    ) ENGINE = InnoDB ;
    
    INSERT INTO `article`(`id`, `title`) VALUES (1, '华为5G智能游戏手机');
    INSERT INTO `article`(`id`, `title`) VALUES (2, '小米5G全面屏拍照手机');
    INSERT INTO `article`(`id`, `title`) VALUES (3, 'vivo美颜拍照手机');
    INSERT INTO `article`(`id`, `title`) VALUES (4, '新款oppo高性能音乐拍照全网通');

执行查询：

select * from article where match(title) against("拍照");

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 8]

> MySQL全文索引官方手册：[https://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html][https_dev.mysql.com_doc_refman_5.7_en_innodb-fulltext-index.html]
> 
>   
> 
> 
> n-gram parser：[https://dev.mysql.com/doc/refman/5.7/en/fulltext-search-ngram.html][https_dev.mysql.com_doc_refman_5.7_en_fulltext-search-ngram.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center]: /images/20221119/7170e8d0e08c4d5ea0be70d31c88f458.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221119/092682a6d9d94e4dbeb6314aec3e5c77.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221119/e3ca977861a641d78491c817f9685f49.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221119/a19b47c7e6dd49ad85b3ef8ac34dcea9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221119/a690b63baf0d493898fed33d9072c6aa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221119/2e9afdc5b37e4c2eb93bd3dd9283f113.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20221119/a703215f8fa44bb599c407b85556fda4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20221119/fd00016ff07e4610b573f8a5f1e7aa3a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20221119/55d47efc325549a8b3c186d5e77be970.png
[https_dev.mysql.com_doc_refman_5.7_en_innodb-fulltext-index.html]: https://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html
[https_dev.mysql.com_doc_refman_5.7_en_fulltext-search-ngram.html]: https://dev.mysql.com/doc/refman/5.7/en/fulltext-search-ngram.html