MySQL全文索引的使用（MySQL 5.6或MariaDB10.0.5版本以上支持InnoDB）

简介

• 在做性能调优时，必不可少的一个环节便是数据库调优，而数据库索引的使用可以大大提升信息的检索效率，我们经常使用的一般是对某列建立聚簇索引或者Hash索引，这样能大大提升信息的查询速度。在一些场景下我们不仅仅满足于对单列单词的检索，比如我们需要对某个字段做实时的全文检索，数据量很小直接用like %xxx%也是可以解决的。但是如果数据量进一步变大，比如说达到几百万条记录时，我们便可以使用MySQL自带的全文索引满足我们的简单检索需求。如果数据再大一些我们就需要用像类似elasticsearch这种专门用于实时分词检索数据库了。本文主要讲解如何使用MySQL（MariaDB）提供的全文索引实现一个简单的搜索引擎。

本文内容有

• 数据库版本要求
• 全文索引分词配置设置
• 全文索引语法说明
• 全文索引如何支持中文分词
• 全文索引使用示例

参考文章

• MariaDB官方全文索引说明
• 原文传送门

---

1、数据库版本要求

1.1 MySQL 版本要求

5.6版本之后InnoDB存储引擎开始支持全文索引，5.7版本之后通过使用ngram插件开始支持中文。之前仅支持英文，因为是通过空白字符、英文逗号、句点等等作为分词的分隔符，对于中文来说是不合适的
MySQL允许在char、varchar、text类型上建立全文索引，全文索引对MyISAM和InnoDB引擎有效。

1.3 MariaDB版本要求

MariaDB 从10.0.5版本之后，InnoDB开始支持全文索引，MariaDB从10.0.15版本之后，mroonga也开始支持全文索引， MyISAM和Aria存储引擎也支持全文索引功能。其他使用方法跟MySQL类似，mroonga存储引擎支持中文分词

2、全文索引分词配置设置

• 查看数据库全文索引默认配置

  SHOW VARIABLES LIKE '%ft%';
  
  aria_force_start_after_recovery_failures	0
  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	
  
  

• 修改分词最小字符数为2

  
  innodb_ft_min_token_size=2
  ft_min_word_len=2
  
  
  

3、全文索引语法说明

全文搜索把任何数字、字母、下划线序列看作是单词，还可以包含“’”如aaa’bbb备解析为一个单词，但aaa’’bbb备解析为两个单词，FULLTEXT解析器自动移除首尾的“’”，如’aaa’bbb’被解析为aaa’bbb。FULLTEXT解析器用“ ”（空格）、“,”（逗号）“.”（点号）作为默认的单词分隔符，因此对于不使用这些分隔符的语言如汉语来说FULLTEXT解析器不能正确的识别单词，对于这种情况需做额外处理。

全文搜索中一些单词会被忽略。首先是过短的单词，InnoDB全文搜索中默认为3个字符，MyISAM默认4个字符，可通过在创建FULLTEXT索引前改变配置参数来改变默认行为，对于InnoDB该参数为：innodb_ft_min_token_size，对于MyISAM为ft_min_word_len；另外stopword列表中的单词会被忽略。stopword列表包含诸如“the”、“or”、“and”等常用单词，这些词通常被认为没有什么语义价值。MySQL由内建的停止字列表，但是可以所使用自定义的停止字列表来覆盖默认列表。对于InnoDB控制停止字的配置参数为innodb_ft_enable_stopword，innodb_ft_server_stopword_table， innodb_ft_user_stopword_table对于MyISAM参数为ft_stopword_file。

文本集合和查询语句中的单词的权重由该单词在集合或语句中的重要性确定。单词在越多的行中出现则该单词的权重越低，因为这表明其在文本集合中的语义价值较小。反之权重越高。

• 查询语法

  SELECT * FROM 表名 WHERE MATCH (列名1,…,列名N) AGAINST (‘关键词1,…,关键词N’ 查询模式关键字);

3.1 索引的建立

• 单列索引

  
  FULLTEXT(column_name)
  
  FULLTEXT KEY index_name(column_name)
  
  ALTER TABLE table_name ADD FULLTEXT INDEX index_name(column_name);
  
  

• 多列索引

  
  FULLTEXT(column_name1,column_name2)
  
  FULLTEXT KEY index_name(column_name1,column_name2)
  
  ALTER TABLE table_name ADD FULLTEXT INDEX index_name(column_name1,column_name2);
  
  

3.2 查询方式

• 自然语言模式 ( IN NATURAL LANGUAGEMODE )

  遵从自然语言分词规则，匹配完整的单词，查询关键字也会根据分词规则自动分词，只要匹配分词后的各个单词中其中任意一个便可以查询到记录，默认就是这种模式

  where 条件后接 MATCH(column_name1,column_name2) AGAINST('keyword')
  
  where 条件后接 MATCH(column_name1,column_name2) AGAINST('keyword' IN NATURAL LANGUAGE MODE)
  
  

  • eg1. MATCH(food_name) AGAINST(‘apple’) 可以匹配出 apple，apples，Apples 等完整单词
  • eg2. MATCH(food_name) AGAINST(‘apple orange’) 可以匹配出 apple，apples，Apples 、orange、oranges、Oranges等完整单词

• 布尔全文搜索 （ IN BOOLEAN MODE ）

  如果在AAGAINST()函数中指定了IN BOOLEN MODE模式，则MySQL会执行布尔全文搜索。在该搜索模式下，待搜索单词前或后的一些特定字符会有特殊的含义。

  where 条件后接 MATCH(column_name1,column_name2) AGAINST('keyword' IN BOOLEAN MODE)
  
  

  BOOLEAN 模式操作符使用说明

符号	功能描述
+	符号后面接的单词必须要在搜索结果里
-	符号后面接的单词必须不能出现在搜索结果里
<	符号后面接的单词匹配优先级低一些
>	符号后面接的单词匹配优先级高一些
()	用于匹配表达式中的一组单词
~	可选，相关性级别低的词，匹配后排名会相对靠后
*	通配符，跟在词的后面，进行模糊检索
"	双引号间的内容进行全词匹配（引号间的内容不会被分词），适合短语和子句的匹配，遵循最左匹配原则

• 布尔全文搜索示例
  • eg1. MATCH(food_name) AGAINST(’+apple-orange’) 可以匹配出 包含apple，apples，Apples 等单词，并不包含orange、oranges、Oranges等单词的记录
  • eg2.MATCH(food_name) AGAINST(’“apple orange”’) 可以匹配出 包含apple-orange字句的记录

[

Mysql实现全文检索、关键词跑分的方法实例

下载

](https://download.csdn.net/download/weixin_38597889/13682389)

3.3 多列索引和单列索引

对于普通的索引，如果建立了(column_name1,column_name2)的联合索引，就不必单独建立 column_name1和 column_name2的索引，也可以搜索单列。但是全文索引就不行，索引的列必须和查询的列完全一致。如果查询 column_name1，就要建立 column_name1单列的全文索引；如果查询 (column_name1, column_name2) 就要建立只包含 (column_name1, column_name2) 两列的索引。

4、全文索引如何支持中文分词

• MySQL需要开启ngram插件

  • MySQL 5.7开始，MySQL内置了ngram全文检索插件

  • 使用索引

    alter table table1 add fulltext index idx_ft_test(clumn1,clumn2) with parser ngram;
    
    

    当然也可以在建表时指定索引

    CREATE TABLE table1(
    id INTUNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    name VARCHAR(200),
    love_foodsTEXT,
    FULLTEXT key (name,love_foods) WITH PARSER ngram
    ) ENGINE=InnoDB CHARACTER SET utf8mb4;
    
    

• MariaDB创建表时指定使用mroonga存储引擎即可

  create table table1 (
      id int not null,
      name varchar(100)not null,
      love_foods text,
      fulltext key idx_ft_love_foods(love_foods)
  )engine=mroonga;
  
  

5、全文索引使用示例，以MariaDB为例

5.1 创建表和索引

CREATE TABLE ft_myisam(copy TEXT,FULLTEXT(copy)) ENGINE=MyISAM;

INSERT INTO ft_myisam(copy) VALUES ('Once upon a time'),
  ('There was a wicked witch'), ('Who ate everybody up');

SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('wicked');
+--------------------------+
| copy                     |
+--------------------------+
| There was a wicked witch |
+--------------------------+

多个单词的情况:

SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('wicked,witch');
+
| copy                            |
+
| There was a wicked witch        |
+

如果关键字未找到，则返回空

[

纯Java全文检索

下载

](https://download.csdn.net/download/weixin_42517275/10793850)

SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('Once');
Empty set (0.00 sec)

如果匹配了超过50%的记录，返回空

INSERT INTO ft_myisam(copy) VALUES ('Once upon a wicked time'),
  ('There was a wicked wicked witch'), ('Who ate everybody wicked up');

SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('wicked');
Empty set (0.00 sec)

5.2 使用IN BOOLEAN MODE模式来解决满足条件的记录占比超过50%的情况

SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('wicked' IN BOOLEAN MODE);
+
| copy                            |
+
| There was a wicked witch        |
| Once upon a wicked time         |
| There was a wicked wicked witch |
| Who ate everybody wicked up     |
+

返回了如下结果:

SELECT copy,MATCH(copy) AGAINST('witch') AS relevance 
  FROM ft_myisam WHERE MATCH(copy) AGAINST('witch');
+
| copy                            | relevance          |
+
| There was a wicked witch        | 0.6775632500648499 |
| There was a wicked wicked witch | 0.5031757950782776 |
+

5.3 IN BOOLEAN MODE模式的操作符使用说明与示例

• 模糊匹配

  SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('Maria*' IN BOOLEAN MODE);
  +
  | copy                           |
  +
  | MySQL vs MariaDB database      |
  | Oracle vs MariaDB database     |
  | PostgreSQL vs MariaDB database |
  | MariaDB overview               |
  +
  
  

• 其他操作符的使用，找出包含某单词且不包含某单词的句子

  SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('+MariaDB -database' 
    IN BOOLEAN MODE);
  +
  | copy             |
  +
  | MariaDB overview |
  +
  
  

• 字句匹配示例，忽略字句内的操作符

  SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('"MySQL vs MariaDB"' 
    IN BOOLEAN MODE);
  +
  | copy                       |
  +
  | MySQL vs MariaDB database  |
  +
  
  

  在IN BOOLEAN MODE模式下，各个操作符是可以组合起来一起生效的，有兴趣的小伙伴们可以自行去尝试一下！