MySql中的Full Text Search全文索引优化
开篇
在我们的生产环境中,有一个模糊检索的文档框,但是当数据量级别上去之后,频繁对数据库造成压力,所以想使用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"
/ /
yes yes
/ /
[4] <="h"?
/ \
yes no
/ \
<="n"? <="seph"?
/ /
yes yes
/ /
[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全文索引优化的资料请关注我们其它相关文章！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/14.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
