如何从具有 UNION ALL 的查询中获取优化的分页列表？

发布于 2024-12-07 09:42:04 字数 724 浏览 0 评论 0原文

我有一个由两个表的 UNION ALL 形成的查询。结果必须排序并分页（就像 Web 应用程序的典型列表）。

原来的查询（简化）是：

SELECT name, id
FROM _test1 -- conditions WHERE
UNION ALL
SELECT name, id
FROM _test2 -- conditions WHERE
ORDER BY name DESC LIMIT 10,20

问题是2个表各有超过100万行，查询速度非常慢。

如何从 UNION ALL 获得优化的分页列表？

Postdata：

我用Stack Overflow搜索，发现了一些类似的问题，但答案不正确或者问题不完全相同。两个示例：

优化 UNION mysql 查询

在 MySQL 查询中组合 UNION 和 LIMIT 操作

我很惊讶在 Stack Overflow 中没有人能回答这个问题。也许不可能更有效地执行此查询？这个问题的解决方案是什么？

原文

I have a query formed by an UNION ALL from two tables. The results have to be ordered and paginated (like the typical list of a web application).

The original query (simplified) is:

SELECT name, id
FROM _test1 -- conditions WHERE
UNION ALL
SELECT name, id
FROM _test2 -- conditions WHERE
ORDER BY name DESC LIMIT 10,20

The problem is that the 2 tables have more than 1 million rows each, and the query is very slow.

How can I get an optimized paginated list from a UNION ALL?

Postdata:

I've used the search of Stack Overflow and I've found some similar questions of this, but the answer was incorrect or the question isn't exactly the same. Two examples:

Optimize a UNION mysql query

Combining UNION and LIMIT operations in MySQL query

I'm surprised that in Stack Overflow nobody could answered this question. Maybe it is impossible to do this query more efficiently? What could be a solution of this problem?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

奢望 2024-12-14 09:42:04

我认为您可以使用与第二个链接中的解决方案类似的方法来至少提高性能，但我怀疑您能否在后面的页面上获得出色的性能。例如：

(   SELECT name, id
    FROM _test1 -- conditions WHERE
    ORDER BY name DESC LIMIT 0, 30
)
UNION ALL
(   SELECT name, id
    FROM _test2 -- conditions WHERE
   ORDER BY name DESC LIMIT 0, 30
)
ORDER BY name DESC
LIMIT 10, 20

您基本上将每个子查询限制为给定页面上可能存在的行的子集。这样，您只需从每个表中检索并合并 20 行，然后确定要返回哪 10 行。否则，服务器可能会从每个表中获取所有行，对它们进行排序和合并，然后开始尝试查找正确的行。

不过，我不经常使用 MySQL，所以我不能保证引擎会按照我认为应该的方式运行:)

无论如何，一旦你到达后面的页面，你仍然会合并越来越大的数据集。然而，我强烈认为，UI 绝对不应该允许用户检索一组让他们转到（例如）第 5000 页的记录。对于人类大脑来说，一次性发现有用的数据太多了，而且应该需要进一步过滤。也许让他们看到前 100 页（或其他数字），但否则他们必须更好地限制结果。只是我的意见。

I would think that you could use something similar to the solution in your second link to at least help performance, but I doubt that you'll be able to get great performance on later pages. For example:

(   SELECT name, id
    FROM _test1 -- conditions WHERE
    ORDER BY name DESC LIMIT 0, 30
)
UNION ALL
(   SELECT name, id
    FROM _test2 -- conditions WHERE
   ORDER BY name DESC LIMIT 0, 30
)
ORDER BY name DESC
LIMIT 10, 20

You're basically limiting each subquery to the subset of possible rows that might be on the given page. In this way you only need to retrieve and merge 20 rows from each table before determining which 10 to return. Otherwise the server will potentially grab all of the rows from each table, order and merge them, then start trying to find the correct rows.

I don't use MySQL a lot though, so I can't guarantee that the engine will behave how I think it should :)

In any event, once you get to later pages you're still going to be merging larger and larger datasets. HOWEVER, I am of the strong opinion that a UI should NEVER allow a user to retrieve a set of records that let them go to (for example) page 5000. That's simply too much data for a human mind to find useful all at once and should require further filtering. Maybe let them see the first 100 pages (or some other number), but otherwise they have to constrain the results better. Just my opinion though.

回复收藏 0 原文

~没有更多了~