发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

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

漂亮的大白

6

主题

6

帖子

18

积分

新手上路

Rank: 1

积分
18
显示全部楼层
开篇

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

一个简单的DEMO

假设我们有客户的地址簿，目标是通过他/她的姓名或电子邮件快速找到人。
  1. CREATE TABLE `address_book` (
  2.     `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  3.     `name` VARCHAR(128) NOT NULL,
  4.     `email` VARCHAR(128) NOT NULL,
  5.     PRIMARY KEY (`id`)
  6. ) ENGINE=InnoDB CHARSET=utf8mb4;
复制代码
我们将用 1_000_000 个随机生成的人填充地址簿。每个人将被插入单独的查询中。姓名将始终采用整齐的形式 - 名字和姓氏。电子邮件会更加混乱——名字/姓氏的顺序和存在不同，分隔符不同，并且有一些随机数。
  1. > SELECT `name`, `email` FROM `addressbook` LIMIT 8;
  2. +--------------------+---------------------------------+
  3. | name               | email                           |
  4. +--------------------+---------------------------------+
  5. | Reed Slavik        | 664-slavik-reed@example.com     |
  6. | Reilly Isaacson    | reilly972isaacson@example.com   |
  7. | Theodore Klosinski | 942.klosinski@example.com       |
  8. | Duncan Sinke       | 912.duncan@example.com          |
  9. | Maranda Cabrara    | cabrara-809-maranda@example.com |
  10. | Hugh Harrop        | hugh765@example.com             |
  11. | Bernard Luetzow    | bernard887luetzow@example.com   |
  12. | Niki Manesis       | niki-247@example.com            |
  13. +--------------------+---------------------------------+
复制代码
测试将在具有默认配置的库存 MySQL 8.0.32 Docker 映像上执行（除非另有说明）。硬件是 AMD 6800U、32GB RAM、PCIe NVMe 4.0 x4 SSD。操作系统是带有 BTRFS 和 LUKS 磁盘加密的 vanilla Arch Linux。

天下没有免费的午餐

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

无索引

最简单的方法是没有索引列并使用
  1. LIKE '%john%'
复制代码
语法。
因为没有索引维护这种方法不会增加数据加载时间和存储空间。
  1. $ time cat address_book.sql | mysql
  2. real    23m 31.43s
复制代码
  1. > SELECT data_length, index_length FROM information_schema.tables WHERE table_name = 'address_book';
  2. +-------------+--------------+
  3. | DATA_LENGTH | INDEX_LENGTH |
  4. +-------------+--------------+
  5. |    71942144 |            0 |
  6. +-------------+--------------+
复制代码
性能很差。当没有使用索引时，MySQL 使用 Turbo Boyer-Moore 算法 来查找匹配的行。
  1. > SELECT * FROM `address_book` WHERE `name` LIKE '%john%' AND `name` LIKE '%doe%';
  2. +--------+----------------+-------------------------------+
  3. | id     | name           | email                         |
  4. +--------+----------------+-------------------------------+
  5. | 222698 | Johnie Doemel  | doemel.36.johnie@example.com  |
  6. | 316137 | Johnnie Doepel | johnnie-doepel-72@example.com |
  7. +--------+----------------+-------------------------------+
  8. 2 rows in set (0.222 sec)
复制代码
如查询所示，所有行都需要从磁盘中提取以进行分析
  1. EXPLAIN
复制代码
。
  1. > EXPLAIN SELECT * FROM `address_book` WHERE `name` LIKE '%john%' AND `name` LIKE '%doe%'\G
  2.            id: 1
  3.   select_type: SIMPLE
  4.         table: address_book
  5.    partitions: NULL
  6.          type: ALL
  7. possible_keys: NULL
  8.           key: NULL
  9.       key_len: NULL
  10.           ref: NULL
  11.          rows: 996458
  12.      filtered: 1.23
  13.         Extra: Using where
复制代码
使用： 当您的应用程序很少进行全文搜索并且您愿意接受低查询性能时。在小数据集上效果很好。简单的实施是巨大的好处。
避免： 当频繁​​使用全文搜索时——你会在这里消耗大量的数据库性能，尤其是在大数据集上。此外，由于全行扫描，它可能会阻止应用程序中需要
  1. FOR UPDATE
复制代码
锁定此类表的其他查询。

使用 B 树索引

不幸的是，在一个字段上打一个索引并称之为一天是行不通的。在 B 树索引中，文本从搜索短语的开始到结束被转换为一系列二元（真/假）测试树。对于示例数据：
  1. 1 John
  2. 2 Joseph
  3. 3 Joseph
  4. 4 Ann
复制代码
它看起来像这样。
  1.                    <="a"?
  2.                     /  \
  3.                   yes   no
  4.                   /       \
  5.              <="nn"?     <="jo"
  6.                /          /
  7.              yes        yes
  8.              /          /
  9.            [4]      <="h"?
  10.                      /  \
  11.                    yes   no
  12.                    /      \
  13.                 <="n"?    <="seph"?
  14.                  /          /
  15.                yes        yes
  16.                /          /
  17.              [1]        [2,3]
复制代码
如果你正在寻找
  1. Joseph
复制代码
你测试第一个字符。因为
  1. j>a
复制代码
你经过
  1. no
复制代码
路径。然后你测试前两个字符。因为
  1. jo=jo
复制代码
你从短语中删除它们并通过
  1. yes
复制代码
路径。然后你测试下一个不匹配的字符是
  1. h
复制代码
......你继续执行这些系列的测试，直到你最终到达包含你正在寻找的短语的行列表，在这种情况下是
  1. 2
复制代码
和
  1. 3
复制代码
。但这表明这种类型的索引必须从短语的开始到结束起作用，这意味着短语不能以通配符开头。
让我们把它添加到我们的表中。
  1. > ALTER TABLE `address_book` ADD KEY (`name`), ADD KEY (`email`);
复制代码
如您所见，当搜索的短语以通配符索引开头时将不会被使用。
  1. > EXPLAIN SELECT * FROM `address_book` WHERE `name` LIKE '%john%' AND `name` LIKE '%doe%'\G
  2.            id: 1
  3.   select_type: SIMPLE
  4.         table: address_book
  5.    partitions: NULL
  6.          type: ALL
  7. possible_keys: NULL
  8.           key: NULL
  9.       key_len: NULL
  10.           ref: NULL
  11.          rows: 996458
  12.      filtered: 1.23
  13.         Extra: Using where
复制代码
如果您知道文本具有某种特定结构（在我们的例子中，名称在前），我们可以利用这些知识并在不使用通配符的情况下询问名称。
  1. > SELECT * FROM `address_book` WHERE `name` LIKE 'john%' AND `name` LIKE '%doe%';
  2. +--------+----------------+-------------------------------+
  3. | id     | name           | email                         |
  4. +--------+----------------+-------------------------------+
  5. | 222698 | Johnie Doemel  | doemel.36.johnie@example.com  |
  6. | 316137 | Johnnie Doepel | johnnie-doepel-72@example.com |
  7. +--------+----------------+-------------------------------+
  8. 2 rows in set (0.003 sec)
复制代码
Explain 显示这次使用了索引，所有以 开头的名称
  1. john
复制代码
都在索引中找到，并且 Boyer-Moore 必须仅用于针对 对该集合进行精细过滤
  1. doe
复制代码
。
  1. > EXPLAIN SELECT * FROM `address_book` WHERE `name` LIKE 'john%' AND `name` LIKE '%doe%'\G
  2.            id: 1
  3.   select_type: SIMPLE
  4.         table: address_book
  5.    partitions: NULL
  6.          type: range
  7. possible_keys: name
  8.           key: name
  9.       key_len: 514
  10.           ref: NULL
  11.          rows: 3602
  12.      filtered: 100.00
  13.         Extra: Using index condition
复制代码
当涉及到电子邮件时，这种方法很快就会显示出局限性。它太混乱了——可能以名字开头，可能以姓氏开头，甚至可能以完全不同的东西开头。在这种情况下，查询时间就像没有索引的情况一样。
  1. > SELECT * FROM `address_book` WHERE `email` LIKE '%john%' AND `email` LIKE '%doe%';
  2. +--------+----------------+-------------------------------+
  3. | id     | name           | email                         |
  4. +--------+----------------+-------------------------------+
  5. | 222698 | Johnie Doemel  | doemel.36.johnie@example.com  |
  6. | 316137 | Johnnie Doepel | johnnie-doepel-72@example.com |
  7. +--------+----------------+-------------------------------+
  8. 2 rows in set (0.314 sec)
复制代码
在性能方面，它会稍微减慢数据加载速度并使存储空间增加一倍，但并不是很有用。
  1. $ time cat address_book.sql | mysql
  2. real    24m 12.81s
复制代码
  1. > SELECT data_length, index_length FROM information_schema.tables WHERE table_name = 'address_book';
  2. +-------------+--------------+
  3. | DATA_LENGTH | INDEX_LENGTH |
  4. +-------------+--------------+
  5. |    71942144 |    112623616 |
  6. +-------------+--------------+
复制代码
使用： 当您可以将文本拆分为具有自己索引的明确定义的列时。例如重组表以单独
  1. first_name
复制代码
存储
  1. last_name
复制代码
。此外，您必须愿意牺牲起始通配符。
避免： 当文本太不可预测和无序时，例如
  1. email
复制代码
您
  1. name
复制代码
商店中的各种产品。
注意：从右到左的语言也不例外，搜索的词组不能以通配符开头，无论文字的方向是什么。

引入反向索引

首先让我们解释一下什么是反向索引。B树索引是对搜索短语从头到尾的一系列测试。反向索引采用不同的方法，它从单词创建标记。Token 可以是整个单词或 n-gram（来自单词的给定长度的子串，对于
  1. Johnie
复制代码
3 个字母的 n-gram 是：
  1. joh
复制代码
,
  1. ohn
复制代码
,
  1. hni
复制代码
,
  1. nie
复制代码
）。
这允许以稍微不同的方式构建索引。对于示例数据：
  1. 1 Paul
  2. 2 Roland
  3. 3 Carol
复制代码
3 个字母的 n-gram 标记的索引将如下所示：
  1. pau => [p1r1] # that means this n-gram is at position 1 in row 1
  2. aul => [p2r1]
  3. rol => [p1r2,p3r3]
  4. ola => [p2r2]
  5. lan => [p3r2]
  6. and => [p4r2]
  7. car => [p1r3]
  8. aro => [p2r3]
复制代码
现在，如果我们查找，
  1. rol
复制代码
我们会立即知道此标记存在于 rows
  1. 2
复制代码
和中
  1. 3
复制代码
。如果我们搜索更长的短语，比如
  1. roland
复制代码
数据库可能会使用这个索引两次——如果
  1. rol
复制代码
在某个位置找到，那么
  1. and
复制代码
必须在 3 个字符之后找到。只有行
  1. 2
复制代码
符合此条件。

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

反向索引有它自己的语法，让我们在我们的表中添加一个。
  1. ALTER TABLE `address_book` ADD FULLTEXT (`name`), ADD FULLTEXT(`email`);
复制代码
默认分词器使用词边界来查找分词，这意味着一个连续的词就是一个分词。
要利用全文索引
  1. MATCH () AGAINST ()
复制代码
语法必须使用。
  1. AGAINST
复制代码
section 可以在
  1. NATURAL LANGUAGE MODE
复制代码
搜索文本也被标记化的地方工作，或者在
  1. BOOLEAN
复制代码
包含它自己强大的迷你表达式语言的更有用的模式下工作。我不会深入探讨
  1. BOOLEAN MODE
复制代码
语法，基本上是
  1. +
复制代码
指
  1. AND
复制代码
.
  1. > SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ('+johnie +doemel' IN BOOLEAN MODE);
  2. +--------+---------------+------------------------------+
  3. | id     | name          | email                        |
  4. +--------+---------------+------------------------------+
  5. | 222698 | Johnie Doemel | doemel.36.johnie@example.com |
  6. +--------+---------------+------------------------------+
  7. 1 row in set (0.001 sec)
  8. > SELECT * FROM `address_book` WHERE MATCH (`email`) AGAINST ('+johnie +doemel' IN BOOLEAN MODE);
  9. +--------+---------------+------------------------------+
  10. | id     | name          | email                        |
  11. +--------+---------------+------------------------------+
  12. | 222698 | Johnie Doemel | doemel.36.johnie@example.com |
  13. +--------+---------------+------------------------------+
  14. 1 row in set (0.001 sec)
复制代码
哇，真快 比没有索引的方法快 200 倍以上。我们并不局限于像在 B 树索引中那样从短语的开头进行搜索，这意味着在电子邮件中搜索也可以快速进行。我们的索引根据 过滤行
  1. EXPLAIN
复制代码
。
  1. > EXPLAIN SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ('+johnie +doemel' IN BOOLEAN MODE)\G
  2.            id: 1
  3.   select_type: SIMPLE
  4.         table: address_book
  5.    partitions: NULL
  6.          type: fulltext
  7. possible_keys: name
  8.           key: name
  9.       key_len: 0
  10.           ref: const
  11.          rows: 1
  12.      filtered: 100.00
  13.         Extra: Using where; Ft_hints: no_ranking
复制代码
生活是美好的。或者是吗？
  1. > SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ('+john +doe' IN BOOLEAN MODE);
  2. Empty set (0.002 sec)
复制代码
第一个陷阱！您找不到比标记长度短的短语，默认情况下整个单词都是标记。这是搜索速度和索引构建/存储成本之间的平衡。
  1. $ time cat address_book.sql | mysql
  2. real    29m 34.44s
  3. # du -bc /var/lib/mysql/default/fts_*
  4. 492453888       total
复制代码
那是 126% 的未索引加载时间，仅全文索引占用的时间是数据本身的 7 倍。请注意，没有简单的方法可以从 中检查全文索引大小
  1. INFORMATION_SCHEMA
复制代码
，它必须在 MySQL 服务器文件系统上完成。
用途： 当您想按整个单词进行搜索时。布尔模式表达式允许执行一些很酷的技巧，例如排除某些单词或按相关性查找，您可能会发现这些技巧很有用。但是您必须愿意接受更高的写入时间和更高的存储成本。

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

这次每个单词将被拆分成 n-gram。n-gram 的默认长度在服务器配置变量中定义：
  1. > show variables like 'ngram_token_size';
  2. +------------------+-------+
  3. | Variable_name    | Value |
  4. +------------------+-------+
  5. | ngram_token_size | 2     |
  6. +------------------+-------+
复制代码
索引创建语法必须明确定义分词器（此处命名为“解析器”）。
  1. ALTER TABLE `address_book` ADD FULLTEXT (`name`) WITH PARSER ngram, ADD FULLTEXT(`email`) WITH PARSER ngram;
复制代码
这次按预期找到了行，即使在搜索中没有使用整个单词。
  1. > SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ('+john +doe' IN BOOLEAN MODE);
  2. +--------+----------------+-------------------------------+
  3. | id     | name           | email                         |
  4. +--------+----------------+-------------------------------+
  5. | 222698 | Johnie Doemel  | doemel.36.johnie@example.com  |
  6. | 316137 | Johnnie Doepel | johnnie-doepel-72@example.com |
  7. +--------+----------------+-------------------------------+
  8. 2 rows in set (0.266 sec)
复制代码
但是这种可怕的表现呢？这比没有索引要慢！答案在于 n-gram 大小。如果匹配短语与 n-gram 大小不匹配，则数据库必须查询索引几次并合并结果或进行补充的非索引过滤。让我们重新启动我们的服务器并
  1. --ngram_token_size=3
复制代码
重建表。
  1. > SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ('+john +doe' IN BOOLEAN MODE);
  2. +--------+----------------+-------------------------------+
  3. | id     | name           | email                         |
  4. +--------+----------------+-------------------------------+
  5. | 222698 | Johnie Doemel  | doemel.36.johnie@example.com  |
  6. | 316137 | Johnnie Doepel | johnnie-doepel-72@example.com |
  7. +--------+----------------+-------------------------------+
  8. 2 rows in set (0.087 sec)
复制代码
因此，在这种情况下
  1. doe
复制代码
，匹配的标记大小和索引被直接使用，但
  1. john
复制代码
必须在该索引中间接找到。如果我们要求 ，这一点就更明显了
  1. COUNT
复制代码
。
  1. > SELECT COUNT(*) FROM `address_book` WHERE MATCH (`email`) AGAINST ('+john' IN BOOLEAN MODE);
  2. +----------+
  3. | COUNT(*) |
  4. +----------+
  5. |     3563 |
  6. +----------+
  7. 1 row in set (0.064 sec)   # phrase longer than token
  8. > SELECT COUNT(*) FROM `address_book` WHERE MATCH (`email`) AGAINST ('+doe' IN BOOLEAN MODE);
  9. +----------+
  10. | COUNT(*) |
  11. +----------+
  12. |      431 |
  13. +----------+
  14. 1 row in set (0.003 sec)    # phrase equal to token
复制代码
所以我们牺牲了使用索引按 2 个字符搜索的能力，在按 3 个字符搜索时获得了很大的提升，在其他情况下获得了平庸的提升。
使用这种方法是一堆权衡。不，您不能在同一字段上使用不同 n-gram 大小的索引来解决各种搜索短语长度。更糟的是——配置变量是全局的，所以你甚至不能
  1. FULLTEXT
复制代码
在具有不同 n-gram 大小的不同表上有两个索引。一个配置必须满足您在服务器范围内的所有需求。
写入性能和存储损失如何？
  1. $ time cat address_book.sql | mysql
  2. real    26m 31.05s
  3. # du -bc /var/lib/mysql/default/fts_*
  4. 362430464       total
复制代码
不幸的是它们很大，索引占用的空间是数据的 5 倍。
使用： 当你想按部分单词进行搜索时。布尔模式表达式也适用于此。但首先，您必须找到令牌长度在服务器范围内的正确平衡，并接受更高的写入时间和更高的存储成本。长度不同于标记大小的短语仍然比未索引的方法更快，但没有“哇”因素。
避免： 当您的文本使用表意语言（如中文或日文）并且需要单字符标记时。日语有单独的 MeCab 分词器，但这超出了本文的范围。

InnoDB 反向索引性能下降

让我们使用上一章的数据并删除所有行。
  1. > DELETE FROM `address_book`;
  2. > SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ('+john +doe' IN BOOLEAN MODE);
  3. Empty set (0.233 sec)
复制代码
那么对于有数据的表来说时间是 0.087 秒，但现在对于空表​​来说是 0.233 秒？这是因为当从 InnoDB 表中删除行时，它不会从 FULLTEXT 索引中删除。相反，单独的隐藏表跟踪删除的行，并且在过时的索引中搜索必须将 1_000_000 行的过时结果与已删除的 1_000_000 行的列表进行比较。这变得越来越糟。让我们添加、删除、添加、删除和添加我们的数据。所以我们回到表中的 1_000_000 个原始行。与我们开始时相同的行数。
  1. > SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ('+john +doe' IN BOOLEAN MODE);
  2. +--------+----------------+-------------------------------+
  3. | id     | name           | email                         |
  4. +--------+----------------+-------------------------------+
  5. | 222698 | Johnie Doemel  | doemel.36.johnie@example.com  |
  6. | 316137 | Johnnie Doepel | johnnie-doepel-72@example.com |
  7. +--------+----------------+-------------------------------+
  8. 2 rows in set (7.038 sec)
复制代码
这种情况迅速升级……现在是时候进入非常迷幻的土地了。要重建 InnoDB
  1. FULLTEXT
复制代码
索引并恢复性能，您必须更改整个表。这需要大量的数据库用户权限，并且很可能导致应用程序停机。但不要害怕。有全局
  1. innodb_optimize_fulltext_only=ON
复制代码
标志，全局（！）更改
  1. ALTER
复制代码
/
  1. OPTIMIZE
复制代码
（在 InnoDB 中，它们是同义词）以仅从
  1. FULLTEXT
复制代码
索引中清除旧条目。您可以通过设置标志来配置清除多少令牌
  1. innodb_ft_num_word_optimize
复制代码
，最大值为 10_000。如果你完成了，就没有反馈。我再重复一次——如果你完成了没有反馈，你应该连续运行
  1. ALTER
复制代码
s 希望在某个时候你的
  1. FULLTEXT
复制代码
索引没有过时的条目。
那是垃圾UI设计。
治疗比疾病更糟糕。MyISAM
  1. FULLTEXT
复制代码
即时清除索引，它不会降低数据保留。因此，您可能会将 InnoDB 表转换为 MyISAM，从而丢失所有 InnoDB 好东西。或者您可以构建补充 MyISAM 表，如
  1. address_book_fts
复制代码
，在那里有
  1. FULLTEXT
复制代码
索引并使用触发器从 InnoDB 表同步数据。当您认为自己很厉害时 - GTID 一致性就会发挥作用。如果您在复制中使用 GTID 事务标识符，则无法在同一事务中更新 InnoDB 和 MyISAM 表，这意味着您必须冒在流程中自动提交写入的风险。呸。

备选方案

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


  • 尝试切换到 PostgreSQL。MySQL 中的全文搜索是一些奇怪的、未完成的拼凑而成。PostgreSQL 解决方案要好得多，也许我会写这篇文章的后续文章，但使用 Postgres。
  • 使用MySQL，但使用Sphinx插件而不是内置解决方案。
  • 使用ElasticSearch
以上就是MySql中的Full Text Search全文索引优化的详细内容，更多关于MySql全文索引优化的资料请关注脚本之家其它相关文章！

来源:https://www.jb51.net/article/284081.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

上一篇: MySQL分区表和分桶表的操作详解

下一篇: 手把手教你使用Navicat生成MySQL测试数据

发表于 2023-5-13 02:00:28

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4