文章详情页

MySql中的Full Text Search全文索引优化

【字号：大中小】日期：2023-05-08 10:17:32浏览：34作者：猪猪

开篇

在我们的生产环境中,有一个模糊检索的文档框,但是当数据量级别上去之后,频繁对数据库造成压力,所以想使用Full Text全文索引进行优化下面是一个总结的简单案例

一个简单的DEMO

假设我们有客户的地址簿，目标是通过他/她的姓名或电子邮件快速找到人。

CREATE TABLE `address_book` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(128) NOT NULL,
    `email` VARCHAR(128) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8mb4;

我们将用 1_000_000 个随机生成的人填充地址簿。每个人将被插入单独的查询中。姓名将始终采用整齐的形式 - 名字和姓氏。电子邮件会更加混乱——名字/姓氏的顺序和存在不同，分隔符不同，并且有一些随机数。

> SELECT `name`, `email` FROM `addressbook` LIMIT 8;
+--------------------+---------------------------------+
| name       | email   |
+--------------------+---------------------------------+
| Reed Slavik| 664-slavik-reed@example.com     |
| Reilly Isaacson    | reilly972isaacson@example.com   |
| Theodore Klosinski | 942.klosinski@example.com       |
| Duncan Sinke       | 912.duncan@example.com  |
| Maranda Cabrara    | cabrara-809-maranda@example.com |
| Hugh Harrop| hugh765@example.com     |
| Bernard Luetzow    | bernard887luetzow@example.com   |
| Niki Manesis       | niki-247@example.com    |
+--------------------+---------------------------------+

测试将在具有默认配置的库存 MySQL 8.0.32 Docker 映像上执行（除非另有说明）。硬件是 AMD 6800U、32GB RAM、PCIe NVMe 4.0 x4 SSD。操作系统是带有 BTRFS 和 LUKS 磁盘加密的 vanilla Arch Linux。

天下没有免费的午餐

天下没有免费的午餐。索引加快SELECT但减慢INSERT//语句，因为计算的额外 CPU 成本以及额外的磁盘传输和存储空间成本UPDATE。DELETE我会尝试写简短的总结何时使用每种方法，有什么好处和缺点。

无索引

最简单的方法是没有索引列并使用LIKE '%john%'语法。

因为没有索引维护这种方法不会增加数据加载时间和存储空间。

$ time cat address_book.sql | mysql
real    23m 31.43s

> SELECT data_length, index_length FROM information_schema.tables WHERE table_name = "address_book";
+-------------+--------------+
| DATA_LENGTH | INDEX_LENGTH |
+-------------+--------------+
|    71942144 |    0 |
+-------------+--------------+

性能很差。当没有使用索引时，MySQL 使用 Turbo Boyer-Moore 算法来查找匹配的行。

> SELECT * FROM `address_book` WHERE `name` LIKE "%john%" AND `name` LIKE "%doe%";
+--------+----------------+-------------------------------+
| id     | name   | email |
+--------+----------------+-------------------------------+
| 222698 | Johnie Doemel  | doemel.36.johnie@example.com  |
| 316137 | Johnnie Doepel | johnnie-doepel-72@example.com |
+--------+----------------+-------------------------------+
2 rows in set (0.222 sec)

如查询所示，所有行都需要从磁盘中提取以进行分析EXPLAIN。

> EXPLAIN SELECT * FROM `address_book` WHERE `name` LIKE "%john%" AND `name` LIKE "%doe%"\G
   id: 1
  select_type: SIMPLE
table: address_book
   partitions: NULL
 type: ALL
possible_keys: NULL
  key: NULL
      key_len: NULL
  ref: NULL
 rows: 996458
     filtered: 1.23
Extra: Using where

使用： 当您的应用程序很少进行全文搜索并且您愿意接受低查询性能时。在小数据集上效果很好。简单的实施是巨大的好处。

避免： 当频繁使用全文搜索时——你会在这里消耗大量的数据库性能，尤其是在大数据集上。此外，由于全行扫描，它可能会阻止应用程序中需要FOR UPDATE锁定此类表的其他查询。

使用 B 树索引

不幸的是，在一个字段上打一个索引并称之为一天是行不通的。在 B 树索引中，文本从搜索短语的开始到结束被转换为一系列二元（真/假）测试树。对于示例数据：

1 John
2 Joseph
3 Joseph
4 Ann

它看起来像这样。

   <="a"?
    /  \
  yes   no
  /       \
     <="nn"?     <="jo"
       /  /
     yesyes
     /  /
   [4]      <="h"?
     /  \
   yes   no
   /      \
<="n"?    <="seph"?
 /  /
       yesyes
       /  /
     [1][2,3]

如果你正在寻找Joseph你测试第一个字符。因为j>a你经过no路径。然后你测试前两个字符。因为jo=jo你从短语中删除它们并通过yes路径。然后你测试下一个不匹配的字符是h......你继续执行这些系列的测试，直到你最终到达包含你正在寻找的短语的行列表，在这种情况下是2和3。但这表明这种类型的索引必须从短语的开始到结束起作用，这意味着短语不能以通配符开头。

让我们把它添加到我们的表中。

> ALTER TABLE `address_book` ADD KEY (`name`), ADD KEY (`email`);

如您所见，当搜索的短语以通配符索引开头时将不会被使用。

> EXPLAIN SELECT * FROM `address_book` WHERE `name` LIKE "%john%" AND `name` LIKE "%doe%"\G
   id: 1
  select_type: SIMPLE
table: address_book
   partitions: NULL
 type: ALL
possible_keys: NULL
  key: NULL
      key_len: NULL
  ref: NULL
 rows: 996458
     filtered: 1.23
Extra: Using where

如果您知道文本具有某种特定结构（在我们的例子中，名称在前），我们可以利用这些知识并在不使用通配符的情况下询问名称。

> SELECT * FROM `address_book` WHERE `name` LIKE "john%" AND `name` LIKE "%doe%";
+--------+----------------+-------------------------------+
| id     | name   | email |
+--------+----------------+-------------------------------+
| 222698 | Johnie Doemel  | doemel.36.johnie@example.com  |
| 316137 | Johnnie Doepel | johnnie-doepel-72@example.com |
+--------+----------------+-------------------------------+
2 rows in set (0.003 sec)

Explain 显示这次使用了索引，所有以开头的名称john都在索引中找到，并且 Boyer-Moore 必须仅用于针对对该集合进行精细过滤doe。

> EXPLAIN SELECT * FROM `address_book` WHERE `name` LIKE "john%" AND `name` LIKE "%doe%"\G
   id: 1
  select_type: SIMPLE
table: address_book
   partitions: NULL
 type: range
possible_keys: name
  key: name
      key_len: 514
  ref: NULL
 rows: 3602
     filtered: 100.00
Extra: Using index condition

当涉及到电子邮件时，这种方法很快就会显示出局限性。它太混乱了——可能以名字开头，可能以姓氏开头，甚至可能以完全不同的东西开头。在这种情况下，查询时间就像没有索引的情况一样。

> SELECT * FROM `address_book` WHERE `email` LIKE "%john%" AND `email` LIKE "%doe%";
+--------+----------------+-------------------------------+
| id     | name   | email |
+--------+----------------+-------------------------------+
| 222698 | Johnie Doemel  | doemel.36.johnie@example.com  |
| 316137 | Johnnie Doepel | johnnie-doepel-72@example.com |
+--------+----------------+-------------------------------+
2 rows in set (0.314 sec)

在性能方面，它会稍微减慢数据加载速度并使存储空间增加一倍，但并不是很有用。

$ time cat address_book.sql | mysql
real    24m 12.81s

> SELECT data_length, index_length FROM information_schema.tables WHERE table_name = "address_book";
+-------------+--------------+
| DATA_LENGTH | INDEX_LENGTH |
+-------------+--------------+
|    71942144 |    112623616 |
+-------------+--------------+

使用： 当您可以将文本拆分为具有自己索引的明确定义的列时。例如重组表以单独first_name存储last_name。此外，您必须愿意牺牲起始通配符。

避免： 当文本太不可预测和无序时，例如email您name商店中的各种产品。

注意：从右到左的语言也不例外，搜索的词组不能以通配符开头，无论文字的方向是什么。

引入反向索引

首先让我们解释一下什么是反向索引。B树索引是对搜索短语从头到尾的一系列测试。反向索引采用不同的方法，它从单词创建标记。Token 可以是整个单词或 n-gram（来自单词的给定长度的子串，对于Johnie3 个字母的 n-gram 是：joh, ohn, hni, nie）。

这允许以稍微不同的方式构建索引。对于示例数据：

1 Paul
2 Roland
3 Carol

3 个字母的 n-gram 标记的索引将如下所示：

pau => [p1r1] # that means this n-gram is at position 1 in row 1
aul => [p2r1]
rol => [p1r2,p3r3]
ola => [p2r2]
lan => [p3r2]
and => [p4r2]
car => [p1r3]
aro => [p2r3]

现在，如果我们查找，rol我们会立即知道此标记存在于 rows2和中3。如果我们搜索更长的短语，比如roland数据库可能会使用这个索引两次——如果rol在某个位置找到，那么and必须在 3 个字符之后找到。只有行2符合此条件。

在默认解析器中使用反向索引

反向索引有它自己的语法，让我们在我们的表中添加一个。

ALTER TABLE `address_book` ADD FULLTEXT (`name`), ADD FULLTEXT(`email`);

默认分词器使用词边界来查找分词，这意味着一个连续的词就是一个分词。

要利用全文索引MATCH () AGAINST ()语法必须使用。AGAINSTsection 可以在NATURAL LANGUAGE MODE搜索文本也被标记化的地方工作，或者在BOOLEAN包含它自己强大的迷你表达式语言的更有用的模式下工作。我不会深入探讨BOOLEAN MODE语法，基本上是+指AND.

> SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ("+johnie +doemel" IN BOOLEAN MODE);
+--------+---------------+------------------------------+
| id     | name  | email|
+--------+---------------+------------------------------+
| 222698 | Johnie Doemel | doemel.36.johnie@example.com |
+--------+---------------+------------------------------+
1 row in set (0.001 sec)
> SELECT * FROM `address_book` WHERE MATCH (`email`) AGAINST ("+johnie +doemel" IN BOOLEAN MODE);
+--------+---------------+------------------------------+
| id     | name  | email|
+--------+---------------+------------------------------+
| 222698 | Johnie Doemel | doemel.36.johnie@example.com |
+--------+---------------+------------------------------+
1 row in set (0.001 sec)

哇，真快比没有索引的方法快 200 倍以上。我们并不局限于像在 B 树索引中那样从短语的开头进行搜索，这意味着在电子邮件中搜索也可以快速进行。我们的索引根据过滤行EXPLAIN。

> EXPLAIN SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ("+johnie +doemel" IN BOOLEAN MODE)\G
   id: 1
  select_type: SIMPLE
table: address_book
   partitions: NULL
 type: fulltext
possible_keys: name
  key: name
      key_len: 0
  ref: const
 rows: 1
     filtered: 100.00
Extra: Using where; Ft_hints: no_ranking

生活是美好的。或者是吗？

> SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ("+john +doe" IN BOOLEAN MODE);
Empty set (0.002 sec)

第一个陷阱！您找不到比标记长度短的短语，默认情况下整个单词都是标记。这是搜索速度和索引构建/存储成本之间的平衡。

$ time cat address_book.sql | mysql
real    29m 34.44s
# du -bc /var/lib/mysql/default/fts_*
492453888       total

那是 126% 的未索引加载时间，仅全文索引占用的时间是数据本身的 7 倍。请注意，没有简单的方法可以从中检查全文索引大小INFORMATION_SCHEMA，它必须在 MySQL 服务器文件系统上完成。

用途： 当您想按整个单词进行搜索时。布尔模式表达式允许执行一些很酷的技巧，例如排除某些单词或按相关性查找，您可能会发现这些技巧很有用。但是您必须愿意接受更高的写入时间和更高的存储成本。

在 n-gram 解析器中使用反向索引

这次每个单词将被拆分成 n-gram。n-gram 的默认长度在服务器配置变量中定义：

> show variables like "ngram_token_size";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 2     |
+------------------+-------+

索引创建语法必须明确定义分词器（此处命名为“解析器”）。

ALTER TABLE `address_book` ADD FULLTEXT (`name`) WITH PARSER ngram, ADD FULLTEXT(`email`) WITH PARSER ngram;

这次按预期找到了行，即使在搜索中没有使用整个单词。

> SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ("+john +doe" IN BOOLEAN MODE);
+--------+----------------+-------------------------------+
| id     | name   | email |
+--------+----------------+-------------------------------+
| 222698 | Johnie Doemel  | doemel.36.johnie@example.com  |
| 316137 | Johnnie Doepel | johnnie-doepel-72@example.com |
+--------+----------------+-------------------------------+
2 rows in set (0.266 sec)

但是这种可怕的表现呢？这比没有索引要慢！答案在于 n-gram 大小。如果匹配短语与 n-gram 大小不匹配，则数据库必须查询索引几次并合并结果或进行补充的非索引过滤。让我们重新启动我们的服务器并--ngram_token_size=3重建表。

> SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ("+john +doe" IN BOOLEAN MODE);
+--------+----------------+-------------------------------+
| id     | name   | email |
+--------+----------------+-------------------------------+
| 222698 | Johnie Doemel  | doemel.36.johnie@example.com  |
| 316137 | Johnnie Doepel | johnnie-doepel-72@example.com |
+--------+----------------+-------------------------------+
2 rows in set (0.087 sec)

因此，在这种情况下doe，匹配的标记大小和索引被直接使用，但john必须在该索引中间接找到。如果我们要求，这一点就更明显了COUNT。

> SELECT COUNT(*) FROM `address_book` WHERE MATCH (`email`) AGAINST ("+john" IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
|     3563 |
+----------+
1 row in set (0.064 sec)   # phrase longer than token
> SELECT COUNT(*) FROM `address_book` WHERE MATCH (`email`) AGAINST ("+doe" IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
|      431 |
+----------+
1 row in set (0.003 sec)    # phrase equal to token

所以我们牺牲了使用索引按 2 个字符搜索的能力，在按 3 个字符搜索时获得了很大的提升，在其他情况下获得了平庸的提升。

使用这种方法是一堆权衡。不，您不能在同一字段上使用不同 n-gram 大小的索引来解决各种搜索短语长度。更糟的是——配置变量是全局的，所以你甚至不能FULLTEXT在具有不同 n-gram 大小的不同表上有两个索引。一个配置必须满足您在服务器范围内的所有需求。

写入性能和存储损失如何？

$ time cat address_book.sql | mysql
real    26m 31.05s
# du -bc /var/lib/mysql/default/fts_*
362430464       total

不幸的是它们很大，索引占用的空间是数据的 5 倍。

使用： 当你想按部分单词进行搜索时。布尔模式表达式也适用于此。但首先，您必须找到令牌长度在服务器范围内的正确平衡，并接受更高的写入时间和更高的存储成本。长度不同于标记大小的短语仍然比未索引的方法更快，但没有“哇”因素。

避免： 当您的文本使用表意语言（如中文或日文）并且需要单字符标记时。日语有单独的 MeCab 分词器，但这超出了本文的范围。

InnoDB 反向索引性能下降

让我们使用上一章的数据并删除所有行。

> DELETE FROM `address_book`;
> SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ("+john +doe" IN BOOLEAN MODE);
Empty set (0.233 sec)

那么对于有数据的表来说时间是 0.087 秒，但现在对于空表来说是 0.233 秒？这是因为当从 InnoDB 表中删除行时，它不会从 FULLTEXT 索引中删除。相反，单独的隐藏表跟踪删除的行，并且在过时的索引中搜索必须将 1_000_000 行的过时结果与已删除的 1_000_000 行的列表进行比较。这变得越来越糟。让我们添加、删除、添加、删除和添加我们的数据。所以我们回到表中的 1_000_000 个原始行。与我们开始时相同的行数。

> SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ("+john +doe" IN BOOLEAN MODE);
+--------+----------------+-------------------------------+
| id     | name   | email |
+--------+----------------+-------------------------------+
| 222698 | Johnie Doemel  | doemel.36.johnie@example.com  |
| 316137 | Johnnie Doepel | johnnie-doepel-72@example.com |
+--------+----------------+-------------------------------+
2 rows in set (7.038 sec)

这种情况迅速升级……现在是时候进入非常迷幻的土地了。要重建 InnoDBFULLTEXT索引并恢复性能，您必须更改整个表。这需要大量的数据库用户权限，并且很可能导致应用程序停机。但不要害怕。有全局innodb_optimize_fulltext_only=ON标志，全局（！）更改ALTER/ OPTIMIZE（在 InnoDB 中，它们是同义词）以仅从FULLTEXT索引中清除旧条目。您可以通过设置标志来配置清除多少令牌innodb_ft_num_word_optimize，最大值为 10_000。如果你完成了，就没有反馈。我再重复一次——如果你完成了没有反馈，你应该连续运行ALTERs 希望在某个时候你的FULLTEXT索引没有过时的条目。

那是垃圾UI设计。

治疗比疾病更糟糕。MyISAMFULLTEXT即时清除索引，它不会降低数据保留。因此，您可能会将 InnoDB 表转换为 MyISAM，从而丢失所有 InnoDB 好东西。或者您可以构建补充 MyISAM 表，如address_book_fts，在那里有FULLTEXT索引并使用触发器从 InnoDB 表同步数据。当您认为自己很厉害时 - GTID 一致性就会发挥作用。如果您在复制中使用 GTID 事务标识符，则无法在同一事务中更新 InnoDB 和 MyISAM 表，这意味着您必须冒在流程中自动提交写入的风险。呸。

备选方案

我希望通过这篇文章您能更好地了解 MySQL 关于全文搜索的功能。有取舍，也有缺陷。如果您还没有找到符合您需求的解决方案，我建议：

尝试切换到 PostgreSQL。MySQL 中的全文搜索是一些奇怪的、未完成的拼凑而成。PostgreSQL 解决方案要好得多，也许我会写这篇文章的后续文章，但使用 Postgres。
使用MySQL，但使用Sphinx插件而不是内置解决方案。
使用ElasticSearch

以上就是MySql中的Full Text Search全文索引优化的详细内容，更多关于MySql全文索引优化的资料请关注其它相关文章！

MySQL

上一条：详解Mysql日期格式并聚合统计示例下一条：MySQL分区表和分桶表的操作详解

排行榜

					
					DB2 DBA避免性能灾难并获得高性能的技巧
MySQL 字符串函数：字符串截取
Mysql入门系列：需要避免的MYSQL客户机程序设计错误
MySQL如何使用授权命令grant
详解mybatis批量插入10万条数据的优化过程
中国银联应用IBM DB2通用数据库的案例（1）
MySQL横纵表相互转化操作实现方法
教你在Windows环境下Kill掉Oracle的线程
保“库”之路：Oracle数据库性能保护
Centos7 下mysql重新启动MariaDB篇
Sql Server2005学习日记(01)
				