从性能角度来看，将MySQL临时表用于频繁使用的网站功能有多有效？ [英] From a performance perspective, how efficient is it to use a MySQL temporary table for a highly used website feature?

问题描述

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

I'm attempting to write a search functionality for a website, and I've decided upon an approach of using MySQL temporary tables to handle the data input, via the query below:

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

INSERT INTO `patternmatch` VALUES ".$someValues

其中 $ someValues 是一组布局为（'some'，'search'，'query'）的数据-或基本上是用户搜索的内容，然后搜索我的主表图片基于表 patternmatch 中的数据，如下所示：

Where $someValues is a set of data with the layout ('some', 'search', 'query') - or basically what the user searched. I then search my main table images based on the data within table patternmatch like so:

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

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

I then apply a heuristic or scoring system based on how well each result matched the input and display the results by that heuristic etc.

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

What I'm wondering is how much overhead does creating a temporary table require? I understand that they only exist in session, and are dropped as soon as the session is ended, but 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?

推荐答案

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

What you stated is totally correct, the temporary table will only be visible to the current user/connection. Still, there is some overhead and some other problems such as:

• 对于数千个搜索中的每一个，您都将创建并填充该搜索表（稍后放下）-不是针对每个用户，而是针对每次搜索。因为每个搜索很可能都会重新执行脚本，并且每个会话并不意味着PHP会话-而是意味着数据库会话（开放连接）。


• 您将需要创建临时表特权，您可能没有。


• 仍然，该表实际上应该具有MEMORY类型，该类型窃取您的RAM的次数超过了预期。因为即使有VARCHAR，MEMORY表也使用固定长度的行存储。


• 如果以后您的试探法需要两次引用该表（例如 SELECT pattern xyz FROM patternmatch AS pm1） ，patternmatch AS pm2 ... ）-内存表无法实现。

• For each of the thousands of searches you are going to create and fill that table (and drop it later) - not per user, per search. Because each search most likely will re-execute the script, and "per session" does not mean PHP session - it means database session (open connection).
• You will need the CREATE TEMPORARY TABLES privilege, which you might not have.
• Still, that table really should have MEMORY type, which steals your RAM more than it looks like. Because even having VARCHAR, MEMORY tables use fixed length row-storage.
• If your heuristics later need to refer to that table twice (like SELECT xyz FROM patternmatch AS pm1, patternmatch AS pm2 ...) - this is not possible with MEMORY tables.

接下来，它对于您-甚至对于数据库-都更容易将 LIKE'％xyz％'直接添加到您的图像表 WHERE 子句。

Next, it would be easier for you - and also for the database - to add the LIKE '%xyz%' directly to your images tables WHERE clause. It will do the same without the overhead of creating a TEMP TABLE and joining it.

在任何情况下-无论采用哪种方式-WHERE都将非常慢。即使您在 images.name 上添加索引，您很可能也需要 Like'％xyz％'而不是 Like'xyz％'，这样索引就不会被使用。

In any case - no matter which way you go - that WHERE will be horribly slow. Even if you add an index on images.name you most likely will need LIKE '%xyz%' instead of LIKE 'xyz%', so that index will not get used.

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

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具有内置的全文搜索（对于InnoDB，也是5.6版本）甚至可以给您得分：我强烈建议您阅读并尝试一下。您可以确定数据库比您更有效地进行搜索。

MySQL has a build-in Fulltext-Search (since 5.6 also for InnoDB) that even can give you that scoring: I highly recommend giving it a read and a try. You can be sure that the database knows better than you how to do that search efficiently.

如果要使用MyISAM代替InnoDB，请注意经常被忽略的地方限制是FULLTEXT搜索仅在结果数少于表总行数的50％时返回任何内容。

If you are going to use MyISAM instead of InnoDB, be aware of the often overlooked limitation that FULLTEXT searches only return anything if the number of results is less than 50% of the total table rows.

您可能要查看的其他内容示例Solr（阅读该主题本身的不错的介绍将是 http://en.wikipedia.org的开头/ wiki / Apache_Solr ）。我们在公司中使用它，虽然它做得很好，但是需要大量学习。

Other things that you might want to look at, are for example Solr (Nice introduction read to that topic itself would be the beginning of http://en.wikipedia.org/wiki/Apache_Solr ). We are using it in our company and it does a great job, but it requires quite some learning.

摘要

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

The solution to your current problem itself (the search) is to use the FULLTEXT capabilities.

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

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;）

To give you a number, 10.000 calls per second is not "trivial" already - with hundreds of thousands of searches per second the sort of performance issues you will encounter are everywhere in your set-up. You are going to need a couple of servers, load balancing and tons of other amazing tech crap. And one of this will be for example Solr ;)

这篇关于从性能角度来看，将MySQL临时表用于频繁使用的网站功能有多有效？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！