php - 从性能的角度来看，将 MySQL 临时表用于高使用率的网站功能的效率如何？

Question

我正在尝试为网站编写搜索功能，并且我决定使用 MySQL 临时表来处理数据输入，通过以下查询:

CREATE TEMPORARY TABLE `patternmatch`
  (`pattern` VARCHAR(".strlen($queryLengthHere)."))

INSERT INTO `patternmatch` VALUES ".$someValues

其中 $someValues 是一组数据，其布局为 ('some', 'search', 'query') - 或者基本上是用户搜索的内容。然后我根据表 patternmatch 中的数据搜索我的主表 images，如下所示:

SELECT images.* FROM images JOIN patternmatch ON (images.name LIKE patternmatch.pattern)

然后我根据每个结果与输入的匹配程度应用启发式或评分系统，并通过该启发式等显示结果。

我想知道的是创建临时表需要多少开销？我知道它们只存在于 session 中，并且在 session 结束后立即被丢弃，但是如果我每秒有数十万次搜索，我可能会遇到什么样的性能问题？有没有更好的方法来实现搜索功能？

Answer 1

您所说的完全正确，临时表仅对当前用户/连接可见。尽管如此，仍然存在一些开销和其他一些问题，例如:

• 对于您将要创建并填充该表(并稍后删除)的数千个搜索中的每一个 - 不是每个用户，每个搜索。因为每次搜索很可能会重新执行脚本，并且“每个 session ”并不意味着 PHP session - 它意味着数据库 session (打开连接)。
• 您将需要 可能没有的 CREATE TEMPORARY TABLES 权限。
• 不过，该表确实应该具有 MEMORY 类型，这比看起来更能窃取您的 RAM。因为即使有 VARCHAR，MEMORY 表也使用固定长度的行存储。
• 如果您的启发式算法稍后需要引用该表两次(例如 SELECT xyz FROM patternmatch AS pm1, patternmatch AS pm2 ...) - 这对于 MEMORY 表是不可能的。

接下来，对您和数据库而言，将 LIKE '%xyz%' 直接添加到您的 images 表 WHERE 会更容易 子句。它会做同样的事情，而无需创建 TEMP TABLE 并加入它。

在任何情况下 - 无论你走哪条路 - WHERE 都会非常缓慢。即使您在 images.name 上添加索引，您也很可能需要 LIKE '%xyz%' 而不是 LIKE 'xyz%'，这样该索引就不会被使用。

I'm asking whether a session-specific temporary table to handle the search input by the user (created on a search, dropped on the end of a session) is an appropriate way of handling a search functionality.

没有。 :)

备选方案

MySQL 有一个内置 Fulltext-Search (从 5.6 开始也适用于 InnoDB)甚至可以为您提供评分:我强烈建议您阅读并尝试一下。您可以确定数据库比您更了解如何有效地进行搜索。

如果您打算使用 MyISAM 而不是 InnoDB，请注意经常被忽视的限制，即 FULLTEXT 搜索仅在结果数少于表总行数的 50% 时才返回任何内容。

您可能想要查看的其他内容，例如 Solr(对该主题本身的很好的介绍将是 http://en.wikipedia.org/wiki/Apache_Solr 的开头)。我们公司正在使用它，它做得很好，但需要大量学习。

总结

当前问题本身(搜索)的解决方案是使用 FULLTEXT 功能。

If I have hundreds of thousands of searches per second, what sort of performance issues might I encounter? Is there any better way of implementing a search functionality?

给您一个数字，每秒 10.000 次调用已经不是“微不足道的”——每秒数十万次搜索，您将遇到的性能问题在您的设置中无处不在。您将需要几台服务器、负载平衡和大量其他令人惊叹的技术垃圾。其中之一就是 Solr ;)