mysql - 从单表性能问题创建多对多

Question

我正在使用一个相当大的数据库(500 多个条目)，并且正在尝试优化搜索引擎。

我的主表如下:

项目(id, mixed, value)

mixed 包含与此项相关的关键字(例如:“Jason Bourne is cool”)。我要做的是填充两个新表:

item_terms(id, 术语)

item_term_results(id, item_terms_id, items_id)

来自主表项。我已经按照以下算法编写了一个 MySQL 过程来遍历该主表的所有结果:

• 拆分项目.mixed
  • 遍历我们得到的值:
    • 在 item_terms 中插入值
    • 在 item_term_results 中插入一个新结果，将 items.id 放入 item_term_results.items_id 并在 item_term_results 中放入 last_inserted_id()

问题是，该过程花费了方式太多时间来处理。有了那么多数据，该过程需要 150 天才能完成，即使我们在非常强大的服务器上，这也太多了。

知道如何做到这一点吗？我们这样做基本上是为了让搜索比简单的点赞快得多，因为 item_terms.item 中的值是唯一的。

谢谢

Answer 1

假设您已经完成研究以确定您的不同术语(例如“Jason”、“Bourne”、“cool”)的数量在大小上受到合理限制，即使您有一个很大的项目表。

想必您也已经确定 MySQL 的 FULLTEXT 搜索功能不适合您的应用程序。如果您还没有研究过 FULLTEXT 搜索，我强烈建议您这样做。这可能是完美的事情。

您的 item_term_results 表不需要也不应该有一个单独的 id柱子。使主键成为其他两个列的组合，并按照 (item_terms_id, itemid) 的顺序将它们放入键中

我建议您弄清楚如何离线处理批量索引，即与数据库断开连接。这是我的建议:

1. 转储一个简单但很大的文本文件，其中每一行包含 id然后是一个标签然后是mixed
2. 编写一个程序来执行以下操作。
   1. 阅读表格的每一行。
   2. 将行拆分为单独的项目
   3. 将每个项目插入到内存中的 HashMap 中，对其进行去重并赋予其唯一的 item_terms_id , 在内存中，而不是在数据库中。
   4. 对于每个项目，将一行写到文本文件中 item_id然后选择 item_terms_id .
3. 处理完输入文件中的所有行后，将每个术语写到另一个文本文件中，格式为 item_terms_id然后选择 term 的文本.
4. 最后，使用 LOAD DATA INFILE 将这两个文件批量导入 MySQL。

这应该可以消除逐行插入的巨大开销。对于半个 gigarecord，它将使用大量 RAM 用于内存中 item_terms hash，它可能会运行很多小时，但不会运行很多天。

不过，在咬住这颗大子弹之前，请再次调查 FULLTEXT。它旨在完成您似乎正在做的事情。