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

php performance mysql database-performance temp-tables

2022-08-31 01:15:46

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

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

INSERT INTO `patternmatch` VALUES ".$someValues

位置是具有布局的一组数据 - 或者基本上是用户搜索的内容。然后，我根据表中的数据搜索主表，如下所示：$someValues('some', 'search', 'query')imagespatternmatch

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

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

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

答案 1

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

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

接下来，对于您和数据库来说，将直接添加到 tables 子句中会更容易。它将执行相同的操作，而不会产生创建 TEMP TABLE 并联接它的开销。LIKE '%xyz%'imagesWHERE

无论如何 - 无论你走哪条路 - WHERE都会非常慢。即使您在上添加索引，您也可能需要而不是，因此该索引不会被使用。images.nameLIKE '%xyz%'LIKE 'xyz%'

我问的是，用于处理用户搜索输入的特定于会话的临时表（在搜索时创建，在会话结束时删除）是否是处理搜索功能的适当方法。

编号 :)

替代选项

MySQL有一个内置的全文搜索（因为InnoDB也有5.6），甚至可以给你评分：我强烈建议你阅读并尝试一下。您可以确定数据库比您更知道如何有效地进行该搜索。

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

您可能想要查看的其他内容，例如Solr（阅读该主题本身的漂亮介绍将是 http://en.wikipedia.org/wiki/Apache_Solr 的开始）。我们正在公司使用它，它做得很好，但它需要相当多的学习。

总结

当前问题本身（搜索）的解决方案是使用全文功能。

如果我每秒有数十万次搜索，我可能会遇到什么样的性能问题？有没有更好的方法来实现搜索功能？

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

答案 2

在磁盘上创建临时表的成本相对较高。在你的场景中，它听起来会比它的价值慢。
通常只值得在内存中创建临时表。但是您需要知道您始终有足够的可用内存。如果您计划每秒支持如此多的搜索，这不是一个好的解决方案。
MySQL内置了全文搜索功能。它适用于小型系统。这可能比临时表和 JOIN 的性能要好得多。但是，如果您想每秒支持数千次搜索，我不建议这样做。它可能会消耗太多的整体数据库性能。此外，您被迫使用MyISAM进行存储，这在您的方案中可能存在自己的问题。
对于如此多的搜索，您需要将工作卸载到另一个系统。已经存在许多带有评分的搜索系统。看看ElasticSearch，Solr/Lucene，Redis等。