从性能角度来看，对于一个高使用率的网站特性，使用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)

我随后会运用一种基于搜索结果与输入匹配程度的启发式或评分系统，并根据该启发式等方式展示结果。

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

- marked-down

---

如果你每秒有数十万次搜索，那么表格存储的位置只是你问题的一小部分。你应该为更现实的情况做好计划。使用带有适当索引的普通表格将帮助你解决很多问题。 - Sverri M. Olsen

我觉得你没有理解我的意思。一个名为images的常规表已经被正确地设置和索引了，其中包含了所有可能的结果。我想问的是，是否使用一个特定于会话的临时表来处理用户输入的搜索（在搜索时创建，在会话结束时删除）是处理搜索功能的适当方式。 - marked-down

虽然我对你现有的方法不能多加评论（在我看来似乎不错），但你可能正在寻找Redis。以下是一些链接，以确定这是否是你需要的：Redis基准测试 为什么要将Redis与MySQL一起使用 Redis的用户案例 - 如果你关心数据库性能，不确定这是否符合你的要求，但你应该看一下。 - JofryHS

一个重要的问题是为什么？你为什么想要创建这个表呢？看起来这需要很多额外的工作，但却没有（或者只有很少的）好处。为什么不直接将模式放在图像表的SQL查询中呢？ - barryhunter

为了评估性能，您应该在问题中添加一些定量数据。例如，图像表有多少行，每个查询中会有多少个模式，模式的允许格式是什么（%s将出现在哪里），您预计每个查询会有多少匹配项。 - rsanchez

Answer 1

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

• 对于你将要创建和填充的成千上万个搜索，将会为每个搜索（而不是每个用户）创建并填充该表（稍后会删除）。因为每次搜索很可能会重新执行脚本，并且“每个会话”并不意味着 PHP 会话 - 它意味着数据库会话（打开连接）。
• 你需要拥有CREATE TEMPORARY TABLES权限，你可能没有这个权限。
• 但是，该表真正应该具有 MEMORY 类型，这会占用比看起来更多的 RAM。因为即使有 VARCHAR，MEMORY 表也使用定长行存储。
• 如果你的启发式算法稍后需要两次引用该表（例如SELECT xyz FROM patternmatch AS pm1, patternmatch AS pm2 ...），则 MEMORY 表无法实现此目的。

接下来，你可以更轻松地将 LIKE '%xyz%' 直接添加到 images 表的 WHERE 子句中。这样做可以避免创建临时表和连接所带来的开销，而且也方便数据库处理。

无论哪种方式，WHERE语句都会非常慢。即使在images.name上添加索引，您很可能仍需要使用LIKE '%xyz%'而不是LIKE 'xyz%'，因此该索引将不会被使用。

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

不是。 :)

替代选择

MySQL有内置的全文搜索（自5.6版以来也适用于InnoDB），甚至可以为您提供评分：我强烈建议阅读并尝试一下。您可以确信，数据库比您更了解如何高效地进行搜索。

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

你可能想要查看的其他内容，例如Solr（阅读该主题的良好介绍将是http://en.wikipedia.org/wiki/Apache_Solr的开始）。我们公司正在使用它，它表现出色，但需要相当多的学习。

摘要

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

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

给你一个数字，每秒10,000次调用已经不是“琐事”了 - 每秒数十万次搜索会在设置中随处可见性能问题。您将需要几台服务器，负载平衡和大量其他惊人的技术垃圾。其中之一就是Solr ;)

- nico gawenda

---

非常感谢您的回答！我会调查您提到的选项... - marked-down

Answer 2

1. 在磁盘上创建临时表相对较昂贵。在您的情况下，它听起来比它值得的要慢。
2. 通常只有在内存中创建临时表才值得。但是您需要知道始终有足够的可用内存。如果您计划每秒支持这么多搜索，则这不是一个好的解决方案。
3. MySQL内置全文搜索。对于小型系统很好用。这可能比您的临时表和JOIN表现得更好。但是，如果您想支持数千次搜索每秒，我不建议使用它。它可能会消耗您整体数据库性能的太多。此外，您随后被迫使用MyISAM进行存储，这在您的情况下可能有自己的问题。
4. 对于如此多的搜索，您将希望将工作卸载到另一个系统。已经存在许多具有评分的搜索系统。看看ElasticSearch、Solr/Lucene、Redis等。

- Matt S

Answer 3

从您提供的代码来看，我真的不认为需要临时表，也不需要全文搜索。但是...关于临时表性能：

创建/清理临时表不会写入事务日志，因此操作系统进行涉及I/O的操作将相对快速。如果临时表很小且生命周期短暂，并且您有大量缓冲区可用于操作系统，则实际上甚至不会触及磁盘。如果您仍然认为会触及磁盘，请获取SSD驱动器并获取更多RAM。

但是，如果您现实地认为您正在处理每秒数十万次搜索，那么您手头就有一个重大的工程项目。为什么不直接执行以下操作： select images.* from images where name in ('some', 'search', 'query')

?

- brak2718

---

因为在这种情况下他应该使用像Solr或Sphinx这样的工具 ;) 尝试在您手头的生产DB服务器上执行多个不同的搜索 - 并且要使用可承受的架构。 - nico gawenda