MySQL8全文索引

强上cs的Bill
1,227 阅读7分钟

0、官方链接

1、全文索引建立的出发点

  • 虽然使用 like+% 也可以实现模糊匹配，但是对于大量的文本检索，全文索引速度更快
  • 全文索引是为了解决需要基于相似度的查询，而不是精确数值比较。

2、一些概念

  • 全文索引的关键词为FULLTEXT

  • MySQL5.6以及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引。

  • 全文索引只能用于InnoDB或MyISAM表，只能为CHAR、VARCHAR、TEXT列创建

  • FULLTEXT索引定义可以在创建表时在CREATE TABLE语句中给出，或者稍后使用ALTER TABLE或CREATE index添加。

  • 对于大型数据集，将数据加载到没有FULLTEXT索引的表中，然后再创建索引，这比将数据加载到已有FULLTEXT索引的表中要快得多。（但是这样做不符合先建表再插入数据的业务逻辑）

  • 全文索引搜索的三种类型

    • 自然语言搜索（natural language search）：将搜索字符串解释为自然人类语言中的短语(自由文本中的短语)。除了双引号(")字符外，没有特殊操作符。IN NATURAL LANGUAGE MODE修饰符指定自然语言搜索。

    • 布尔搜索（boolean search）：使用特殊查询语言的规则解释搜索字符串。字符串包含要搜索的单词。它还可以包含指定要求的操作符，例如一个单词必须在匹配行中出现或不出现，或者它的权重应该比通常的高或低。某些常用词(停止词)将从搜索索引中省略，如果在搜索字符串中出现，则不匹配。IN BOOLEAN MODE修饰符指定布尔搜索。

    • 查询扩展搜索：是对自然语言搜索的改进。搜索字符串用于执行自然语言搜索。然后将搜索返回的最相关行的单词添加到搜索字符串中，并再次执行搜索。该查询返回来自第二次搜索的行。“IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION”或“WITH QUERY EXPANSION”修饰符指定查询展开搜索。有点类似于联想搜索，如搜“database”会返回含有“MySQL”等关键词的结果。

  • 默认一般采用自然语言搜索.一般是返回相关度最高的行结果。相关性是根据行(文档)中的字数、行中惟一单词的数量、集合中的单词总数以及包含特定单词的行数计算的。

  • 默认情况下，搜索不区分大小写。若要执行区分大小写的全文搜索，对索引列使用区分大小写或二进制排序规则。例如，可以为使用utf8mb4字符集的列分配utf8mb4_0900_as_cs或utf8mb4_bin排序规则，以使其在全文搜索时区分大小写。

  • 由于一些文本的语言（如中文、韩文、日文等）句子中的词语不是通过空格划分，MySQL提供了ngram全文索引解析器供这些语言使用。

  • “停止词”的概念：

    • InnoDB的默认停止词列表相对较短，因为来自技术、文学和其他来源的文档经常使用简短的单词作为关键字或重要短语。

    • 搜索时会默认忽略这些词，认为这些词是出现频率很高的助词。

    • mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a     |
| about |
| an    |
| are   |
| as    |
| at    |
| be    |
| by    |
| com   |
| de    |
| en    |
| for   |
| from  |
| how   |
| i     |
| in    |
| is    |
| it    |
| la    |
| of    |
| on    |
| or    |
| that  |
| the   |
| this  |
| to    |
| was   |
| what  |
| when  |
| where |
| who   |
| will  |
| with  |
| und   |
| the   |
| www   |
+-------+
36 rows in set (0.00 sec)

  • 配置最小搜索长度

SHOW VARIABLES LIKE 'ft%';

# 看配置ft_min_word_len
# windows修改需要在mysql的安装目录下找到my.ini文件，追加如下文字，然后重启mysql，并修复或重建索引

# 最小全文搜索长度
innodb_ft_min_token_size = 1
ft_min_word_len = 1


# 如果是添加索引后再修改配置，需要执行修复索引命令
repair table test quick;
  • 为存在的表添加全文索引
ALTER TABLE fulltext_index_test ADD FULLTEXT INDEX content_fulltext(content);
  • 删除索引
MySQL [bill_test]> DROP INDEX content_fulltext ON fulltext_index_test;
Query OK, 0 rows affected (0.49 sec)
Records: 0  Duplicates: 0  Warnings: 0

3、检索语句

  • 自然语言全文检索
SELECT * FROM 表名 WHERE Match(列名1,列名2) Against (检索内容1 检索内容2);
  • 下面是双引号符号的官方使用解释，个人理解是相当于双引号内的短语拆分的关键词，需要按照顺序出现，单词之间如果出现特殊符号不影响
  '"some word"' 
  A phrase that is enclosed within double quote (") characters matches only rows that contain the phrase literally, as it was typed. The full-text engine splits the phrase into words and performs a search in the FULLTEXT index for the words. Nonword characters need not be matched exactly: Phrase searching requires only that matches contain exactly the same words as the phrase and in the same order. For example, "test phrase" matches "test, phrase".

If the phrase contains no words that are in the index, the result is empty. The words might not be in the index because of a combination of factors: if they do not exist in the text, are stopwords, or are shorter than the minimum length of indexed words.

4、测试

4.1 插入数据

  • 新建表fulltext_index_test，添加索引
CREATE TABLE `fulltext_index_test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `content` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '脚本内容',
  PRIMARY KEY (`id`),
  FULLTEXT KEY `content_fulltext` (`content`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='全文索引测试表';

  • 生成10000行的xlsx文件，每行指定为35KB左右，用navicat for MySQL导入

    • 生成xlsx的python脚本如下，其中sql.txt为存放SQL内容的文件（尝试过用python的xlwt、xlwxwriter写入，但是xlwt会报错字段过长，而xlwxwriter源码中会默认截断。。。结果用openpyxl也是一样会截断。。。）

    • #-*- encoding:utf-8 -*-
import sys   #reload()之前必须要引入模块
reload(sys)
sys.setdefaultencoding('utf-8')
import openpyxl

if __name__ == '__main__':
    f1 = open("sql.txt", 'r')
    str1 = f1.read()
    f1.close()
    book = openpyxl.Workbook()  # 创建一个新的工作簿
    sheet = book.active  # 添加一个工作表
    sheet.title = 'sheet1'
    sheet.cell(row = 1, column = 1, value = 'id')
    sheet.cell(row = 1, column = 2, value = 'content')
    for num in range(1, 10001):
        str2 = "This is test line " + str(num) + "\n" #添加一行作为标记
        str4 = str2 + str1
        sheet.cell(row = num + 1, column = 1, value = 1000 + num)
        sheet.cell(row = num + 1, column = 2, value = str4)
    book.save('test3.xlsx')
    print('写入成功')

  • 尝试先建表，然后导入数据，再建索引，对比一下速度

CREATE TABLE `fulltext_index_test2` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `content` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '脚本内容',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='全文索引测试表2(后建索引)';

MySQL [bill_test]> ALTER TABLE fulltext_index_test2 ADD FULLTEXT INDEX content_fulltext(content);
Query OK, 0 rows affected (2 min 15.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.2 全文索引测试（直接在机器上mysql客户端命令行执行）

  • 尝试模糊搜索：使用like关键词
MySQL [bill_test]> select id from fulltext_index_test WHERE content LIKE "%This is test line 999%";
+-------+
| id    |
+-------+
|  1999 |
| 10990 |
| 10991 |
| 10992 |
| 10993 |
| 10994 |
| 10995 |
| 10996 |
| 10997 |
| 10998 |
| 10999 |
+-------+
11 rows in set (2.89 sec)
  • 使用全文索引
MySQL [bill_test]> select id FROM fulltext_index_test where MATCH(content) against('"This is test line 999"' in natural language mode);
+------+
| id   |
+------+
| 1999 |
+------+
1 row in set (0.05 sec)
  • 使用全文索引的搜索返回的是一个相似度概率值
MySQL [bill_test]> select id, MATCH(content) against('"This is test line 999"' in natural language mode) as score FROM fulltext_index_test where MATCH(content) against('"This is test line 999"' in natural language mode);
+------+--------------------+
| id   | score              |
+------+--------------------+
| 1999 | 13.451529502868652 |
+------+--------------------+
1 row in set (0.06 sec)

4.3 尝试在添加索引以及数据后，增删改的速度

  • fulltext_index_test表带全文索引，fulltext_index_test2表不带全文索引，两个表存储的内容相同

  • 添加数据：带索引的表插入速度快

# 插入约600K文本
MySQL [bill_test]> insert into fulltext_index_test(content) select REPEAT('adegte ', 102400);
Query OK, 1 row affected (0.19 sec)
Records: 1  Duplicates: 0  Warnings: 0

MySQL [bill_test]> insert into fulltext_index_test2(content) select REPEAT('adegte ', 102400);
Query OK, 1 row affected (0.32 sec)
Records: 1  Duplicates: 0  Warnings: 0
  • 更新数据：带索引的表更新速度快
MySQL [bill_test]> update fulltext_index_test set content = REPEAT('adegte ', 102400) where id = 1024;
Query OK, 1 row affected (0.52 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [bill_test]> update fulltext_index_test2 set content = REPEAT('adegte ', 102400) where id = 1024;
Query OK, 1 row affected (4.39 sec)
Rows matched: 1  Changed: 1  Warnings: 0
  • 删除数据：不带索引的表删除速度快
MySQL [bill_test]> delete from fulltext_index_test where id = 1024;
Query OK, 1 row affected (0.56 sec)

MySQL [bill_test]> delete from fulltext_index_test2 where id = 1024;
Query OK, 1 row affected (0.14 sec)
avatar
文章
阅读
粉丝