使用 UNION 或以编程方式联合两个 MySQL 查询
我有两个 MySQL 查询，它们都将数据插入表中。 两者都具有以下格式：
CREATE TABLE IF NOT EXISTS `data` (
`id` BIGINT NOT NULL AUTO_INCREMENT UNIQUE,
PRIMARY KEY (`id`)
)
SELECT `field1`, `field2`
WHERE `active` = 1
两个查询之间的唯一区别是如何确定 field1 和 field2，以及条件子句中的一些细微差别。 两者都运行多达 12K 甚至更多的记录。
现在，什么会更有效：
A. 分别运行两个查询：
if (mysql_query($query1)) {
return mysql_query($query2);
}
return false;
B. OR 将两个查询与 UNION 结合起来，然后运行一次：
$query = 'SELECT `field1`, `field2` WHERE `active` = 1
UNION
SELECT DO_ONE(`field1`), DO_TWO(`field2`) WHERE `active` = 1
ORDER BY `field1`';
return mysql_query('CREATE TABLE IF NOT EXISTS `data` (
`id` BIGINT NOT NULL AUTO_INCREMENT UNIQUE,
PRIMARY KEY (`id`)
) ' . $query)
如果没有另一个查询的数据，一个查询的数据将毫无用处，因此两个查询都需要成功。 DO_ONE 和 DO_TWO 是用户定义的 MySQL 函数，它们根据某些规范更改字段数据。
I've got two MySQL queries that both insert data into a table. Both have the following format:
CREATE TABLE IF NOT EXISTS `data` (
`id` BIGINT NOT NULL AUTO_INCREMENT UNIQUE,
PRIMARY KEY (`id`)
)
SELECT `field1`, `field2`
WHERE `active` = 1
The only differences between the two queries are how field1 and field2 are determined, and some minor differences in the conditions clause. Both run up to 12K and more records.
Now, what will be more efficient:
A. Run both queries separately:
if (mysql_query($query1)) {
return mysql_query($query2);
}
return false;
B. OR combine the two queries with a UNION, and run once:
$query = 'SELECT `field1`, `field2` WHERE `active` = 1
UNION
SELECT DO_ONE(`field1`), DO_TWO(`field2`) WHERE `active` = 1
ORDER BY `field1`';
return mysql_query('CREATE TABLE IF NOT EXISTS `data` (
`id` BIGINT NOT NULL AUTO_INCREMENT UNIQUE,
PRIMARY KEY (`id`)
) ' . $query)
The data from the one query is useless without the data from the other, so both need to succeed. DO_ONE and DO_TWO are user defined MySQL functions that change the field data according to some specs.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
Aaronmccall 的答案可能是最好的——UNION 方法在一个 SQL 调用中完成了这一切。 一般来说，这将是最“有效”的，但可能会出现一些附带问题，这些问题可能会发挥作用并影响特定应用程序的“有效”衡量标准。
具体来说，如果 UNION 需要一个临时表来收集中间结果，并且您正在处理非常大的数据集，那么在您的特定情况下，对新表执行两个单独的直接 SELECT 可能会更有效。 这将取决于数据库引擎内的内部工作、完成的优化等（这可能会根据您使用的数据库引擎的版本而变化）。
最终，回答此类特定问题的唯一方法可能是针对您的特定应用程序和环境进行计时。
您可能还想考虑两个单独的查询与“一体化”查询所需的时间之间的差异在宏伟的计划中可能微不足道......您可能正在谈论几毫秒的差异（或甚至微秒？）除非你的 mysql 数据库位于单独的服务器上，并且存在巨大的延迟问题。 如果您一次性执行数千个这样的调用，那么差异可能会很大，但如果您只执行其中一两个调用，并且您的应用程序花费 99.99% 的时间执行其他事情，那么两者之间的差异两个可能根本不会被注意到。
---劳伦斯
Aaronmccall's answer is probably the best in general -- the UNION approach does it all in one SQL call. In general that will be the most "efficient", but there could be side issues that could come into play and affect the measure of "efficient" for your particular application.
Specifically, if the UNION requires a temporary table to gather the intermediate results and you are working with very large sets of data, then doing two separate straight SELECTs into the new table might turn out being more efficient in your particular case. This would depend on the internal workings, optimizations done, etc within the database engine (which could change depending on the version of the database engine you are using).
Ultimately, the only way to answer your question on such a specific question like this might be to do timings for your particular application and environment.
You also might want to consider that the difference between the time required for two separate queries vs an "all in one" query might be insignificant in the grand scheme of things... you are probably talking about a difference of a few milliseconds (or even microseconds?) unless your mysql database is on a separate server with huge latency issues. If you are doing thousands of these calls in one shot, then the difference might be significant, but if you are only doing one or two of these calls and your application is spending 99.99% of its time executing other things, then the difference between the two probably won't even be noticed.
---Lawrence
UNION 方法肯定会更快，因为从 php 进行两次 mysql api 调用比一次调用要花费更多的费用。
The UNION approach should definitely be faster due to the expense of making two mysql api calls from php vs. one.
您的选择会产生不同的作用。 如果第一个查询正确执行，第一个查询将返回第二个查询的结果（顺便说一句，这与其返回的结果无关，它可能返回一个空行集）。 第二个返回第一个查询和第二个查询的结果。 第一个选项在我看来非常无用，可能你想要实现的就是你对 UNION 所做的事情（除非我误解了你）。
编辑：阅读您的评论后，我认为您正在寻找类似的内容：
SELECT true where (EXISTS(SELECT field1, field2 ...) AND EXISTS (SELECT Field1, field2 ...))。
这样，您将只有一个对数据库的查询，这样可以更好地扩展，从连接池中占用更少的资源，并且如果您的数据库引擎位于另一台服务器上，则延迟的影响不会加倍，但您仍然会中断查询如果第一个条件失败，这就是您通过嵌套分离查询寻找的性能改进。
作为一种优化，首先尝试拥有执行速度更快的条件，以防它们不相同。 我认为如果其中之一需要这些字段计算会更慢。
Your options do different things. First one returns the results from the second query if the first query executes correctly (which is BTW independent of the results that it returns, it can be returning an empty rowset). Second one returns the results from the first query and the second query together. First option seems to me pretty useless, probably what you want to achieve is what you did with the UNION (unless I missunderstood you).
EDIT: After reading your comment, I think you are after something like this:
SELECT true where (EXISTS(SELECT field1, field2 ...) AND EXISTS (SELECT Field1, field2 ...)).
That way you will have only one query to the DB, which scales better, takes less resources from the connection pool and doesn't double the impact of latency if you have your DB engine in a different server, but you will still interrupt the query if the first condition fails, which is the performance improvement that you where looking for with the nested separated queries.
As an optimization, try to have first the condition that will execute faster, in case they are not the same. I assume that if one of them requires those field calculations would be slower.