时间戳

首页 > 解决方案 > MySQL / MariaDB 全文搜索非常慢

mysql - MySQL / MariaDB 全文搜索非常慢

问题描述

全文搜索需要几分钟而不是几秒钟。该表有 50K 行。每个 ocr_text 包含大量文本，所有文本来自 2 整页报纸。

MariaDB [scu_db]> describe frames;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| dbRollID    | int(10) unsigned | NO   | MUL | 0       |                |
| frame_num   | int(10) unsigned | NO   |     | 0       |                |
| xLeadEdge   | int(11)          | NO   |     | 0       |                |
| yTrailEdge  | int(11)          | NO   |     | 0       |                |
| wLeftEdge   | int(11)          | NO   |     | 0       |                |
| hRightEdge  | int(11)          | NO   |     | 0       |                |
| ocr_text    | mediumtext       | NO   | MUL | NULL    |                |
| ocr_rects   | longblob         | NO   |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

此外，一个词在数据中出现的频率越高，查询速度就越慢。

此查询需要 10 秒：

SELECT dbRollID 
   FROM frames 
   WHERE MATCH (ocr_text) AGAINST ('+1912' IN BOOLEAN MODE) 
ORDER BY id

查询一个常用词需要 2.5 分钟：

SELECT dbRollID 
   FROM frames 
   WHERE MATCH (ocr_text) AGAINST ('+john' IN BOOLEAN MODE) 
ORDER BY id

如果我添加LIMIT 50（我认为应该这样做？），这没有什么区别。

这看起来慢得可怕。我在这里做错了什么？

SHOW CREATE TABLE结果：

CREATE TABLE `frames` (  
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  
  `dbRollID` int(10) unsigned NOT NULL DEFAULT 0,  
  `frame_num` int(10) unsigned NOT NULL DEFAULT 0,  
  `xLeadEdge` int(11) NOT NULL DEFAULT 0,  
  `yTrailEdge` int(11) NOT NULL DEFAULT 0,  
  `wLeftEdge` int(11) NOT NULL DEFAULT 0,  
  `hRightEdge` int(11) NOT NULL DEFAULT 0,  
  `ocr_text` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,  
  `ocr_rects` longblob NOT NULL,  
  PRIMARY KEY (`id`),  
  KEY `fk_roll_id_1` (`dbRollID`),  
  FULLTEXT KEY `ocr_text` (`ocr_text`),  
  CONSTRAINT `fk_roll_id_1` FOREIGN KEY (`dbRollID`) REFERENCES   `scansettings` (`dbRollID`) ON DELETE CASCADE ON UPDATE CASCADE  
) ENGINE=InnoDB AUTO_INCREMENT=474139 DEFAULT CHARSET=utf8mb4   COLLATE=utf8mb4_unicode_ci;

explain SELECT dbRollID FROM frames WHERE MATCH (ocr_text) AGAINST ('+john' IN BOOLEAN MODE) ORDER BY id结果：

+------+-------------+--------+----------+---------------+----------+---------+------+------+-----------------------------+
| id   | select_type | table  | type     | possible_keys | key      | key_len | ref  | rows | Extra                       |
+------+-------------+--------+----------+---------------+----------+---------+------+------+-----------------------------+
|    1 | SIMPLE      | frames | fulltext | ocr_text      | ocr_text | 0       |      | 1    | Using where; Using filesort |
+------+-------------+--------+----------+---------------+----------+---------+------+------+-----------------------------+

编辑/更新：

好的，我有一个假设，但它可能基于不正确的假设。

所以有人告诉我，通过在文本列上使用 FULLTEXT KEY，MySQL 会为该列创建一个索引，然后在您进行全文搜索时巧妙地使用该索引（即 innodb 引擎中的 MATCH AGAINST）。所以理论上我不应该过度考虑这个并创建我自己的索引系统。这是真的？

好的，假设这是真的，那么 MySQL 会以某种巧妙的方式创建这个词索引，因为实际上并没有那么多不同的词。正确的？

但。我的文字是 OCR 文字。这是非常糟糕的 OCR 文本。这是在数以百万计的图像上执行的非常糟糕的 OCR 文本，其中许多甚至不包含 OCRable 文本，而是草书书写。所以，我的文字中有数以亿计的垃圾词。也许它主要是垃圾词。我不知道。但这是否意味着 MySQL 建立的索引会很大呢？并且足够大以至于不能缓存在 innodb 的内存缓冲区中？

想法？这可能是真的吗？如果是这样，如果我可以清除文本中的所有垃圾，也许它会起作用？

SHOW TABLE STATUS LIKE "frames";
+--------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
| Name   | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment | Max_index_length | Temporary |
+--------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
| frames | InnoDB |      10 | Dynamic    | 51419 |          80792 |  4154245120 |               0 |      3997696 |   7340032 |         474566 | 2021-08-17 13:26:24 | 2021-08-19 18:03:18 | NULL       | utf8mb4_unicode_ci |     NULL |                |         |                0 | N         |
+--------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+------------------+-----------+

EXPLAIN SELECT on RDS（不同但相似的服务器，MySQL 而不是 MariaDB）

explain SELECT dbRollID FROM frames WHERE MATCH (ocr_text) AGAINST ('+john' IN BOOLEAN MODE) ORDER BY id;
+----+-------------+--------+------------+----------+---------------+----------+---------+-------+------+----------+---------------------------------------------------+
| id | select_type | table  | partitions | type     | possible_keys | key      | key_len | ref   | rows | filtered | Extra                                             |
+----+-------------+--------+------------+----------+---------------+----------+---------+-------+------+----------+---------------------------------------------------+
|  1 | SIMPLE      | frames | NULL       | fulltext | ocr_text      | ocr_text | 0       | const |    1 |   100.00 | Using where; Ft_hints: no_ranking; Using filesort |
+----+-------------+--------+------------+----------+---------------+----------+---------+-------+------+----------+---------------------------------------------------+

2021 年 8 月 27 日更新做更多实验：我不明白为什么限制查询的范围，无论是使用额外的 WHERE 条件，还是使用子查询，或者使用 LIMIT，对所花费的时间绝对没有影响：

SELECT dbRollID FROM frames
    WHERE MATCH (ocr_text) AGAINST ('+john' IN BOOLEAN MODE)
    ORDER BY dbRollID;
29219 rows in set (1 min 46.959 sec)

SELECT xLeadEdge FROM frames
    WHERE dbRollID=110
      AND MATCH (ocr_text) AGAINST ('+john' IN BOOLEAN MODE)
    ORDER BY xLeadEdge;
340 rows in set (1 min 45.984 sec)

SELECT * FROM frames
    WHERE dbRollID=110;
512 rows in set (0.272 sec)

SELECT xLeadEdge
    FROM 
    (
        SELECT * FROM frames WHERE dbRollID=110
    ) AS a
    WHERE MATCH (ocr_text) AGAINST ('+john' IN BOOLEAN MODE)
    ORDER BY xLeadEdge;
340 rows in set (1 min 47.044 sec)

SELECT dbRollID FROM frames
    WHERE MATCH (ocr_text) AGAINST ('+john' IN BOOLEAN MODE)
    ORDER BY dbRollID LIMIT 1;
1 row in set (1 min 46.575 sec)

SELECT xLeadEdge FROM frames
    WHERE dbRollID=110 AND MATCH (ocr_text) AGAINST ('+john' IN BOOLEAN MODE)
    ORDER BY xLeadEdge LIMIT 1;
1 row in set (1 min 46.939 sec)

（注意：1 分钟 45 而不是 3 分钟是因为我的 innodb 缓冲区设置比原始测试大得多。但是近 2 分钟的查询仍然很慢。）

标签： mysqlperformancemariadbfull-text-searchquery-optimization

解决方案

拥有更多 RAM 并增加 innodb_buffer_pool_size 会产生显着效果，具体取决于我的表的大小，并且有一些警告。

在我的本地测试服务器上，表中有一半的行，缓冲区设置为 2GB，会发生以下情况：

第一次搜索 +john 需要 2 分钟。连续搜索 +john 不到一秒钟。然后，搜索任何其他常用词只需要 10 到 15 秒。如果我重新启动服务器，第一次搜索总是需要 2 分钟。连续搜索很快。似乎索引正在被缓存。因此，当服务器重新启动时，第一次搜索总是很慢。我想知道，还有哪些其他条件会导致此缓存被刷新，并使搜索再次变慢。我是否只是告诉我的用户，“哦，你每天进行的第一次搜索会非常慢。等待它然后它就会起作用！” 编辑更新：在此找到另一个 SO q/a：mysql 在第一次查询时慢，然后对相关查询快 它被称为预热缓存！

此外，我在代码中添加了超时，因此使用它的网页不会被锁定。但我不能这样做，因为如果我在漫长的 2 分钟第一次搜索之前超时，缓存永远不会被填满（如果这实际上是这样的话）并且所有搜索都很慢，并且总是超时。

此外，如果我的表变得太大，相对于我的 RAM 和 innodb_buffer_pool_size，它就像一个阈值被跨越，并且所有常见的单词搜索开始需要几分钟，就像缓存太小一样。

所以也许答案，或者一个答案，是我只需要一个更强大的服务器，更多的内存。但我仍然认为这不应该是答案。特别是因为我的生产数据集会更大。

我注意到的另一个稍微相关的问题。如果我的 SELECT 请求更多列，尤其是更大的列，它会破坏这一切，并且真的会进一步减慢查询速度。因此，即使我不得不忍受这个，我也将不得不一次编写更智能的多个查询来获取数据。

更新：所以这里是我为缓解这个问题所做的所有事情的完整列表：

  • 增加系统 RAM 和 innodb_buffer_pool_size 以将完整索引放入缓存
  • “预热”缓存 - 编写代码以在服务器重新启动时对 +john 进行全文搜索
  • 从数据中删除所有不超过 3 个字母的单词（也许这也有助于缓存）
  • 修改任何可能进行子查询的查询
  • 修改查询以在执行 MATCH AGAINST 时不选择任何其他列
  • 修改查询以执行后续查询以获取附加列
  • 为最坏的情况添加 MySQL 超时

推荐阅读