更快地获取所有查询的多工会
我有一个与多个工会所有语句的查询有关的性能问题。我需要将不同表的数据添加到同一列中。查询需要用于在mySQL中创建视图，因此，这里一个示例：
CREATE OR REPLACE
ALGORITHM = UNDEFINED
DEFINER = usr
SQL SECURITY DEFINER
VIEW my_view AS
SELECT DISTINCT
column 1,
column 2,
column 3
FROM
table 1
WHERE
condition 1
UNION ALL
SELECT DISTINCT
column 1,
column 2,
column 3
FROM
table 2
WHERE
condition 2
UNION ALL
SELECT DISTINCT
column 1,
column 2,
column 3
FROM
table 3
WHERE
condition 3
完成所有多个联合alls只是从相同功能中添加（不仅是3列）例如，我还有来自不同表的更多），因为这是需要大量资源从数据库中获得的，因此由于运行的时间而导致“查询期间丢失的连接错误”。
有什么方法可以优化这种查询？
提前致谢。
I have an issue of performance with a query with multiple UNION ALL statements. I need to add (row by row) data from different tables into the same columns. The query need to be used to create a view in MySQL, so, here an example:
CREATE OR REPLACE
ALGORITHM = UNDEFINED
DEFINER = usr
SQL SECURITY DEFINER
VIEW my_view AS
SELECT DISTINCT
column 1,
column 2,
column 3
FROM
table 1
WHERE
condition 1
UNION ALL
SELECT DISTINCT
column 1,
column 2,
column 3
FROM
table 2
WHERE
condition 2
UNION ALL
SELECT DISTINCT
column 1,
column 2,
column 3
FROM
table 3
WHERE
condition 3
It seems pointless to do all the multiple UNION ALLs just to add (row by row) data from the same features (not just 3 columns as in the example, I have many more) coming from different tables because this is something that requires lots of resources from the DB, leading to "lost connection error during the query" due to the time it takes to run.
Is there any way to optimize this kind of query?
Thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
联合所有是串联结果集的最佳方法。 （Union的速度较慢，因为它会删除重复。）当您使用的视图而不是创建时，您的超时会发生。
您的性能问题源于
Union中的一个或多个选择查询，所有级联都非常慢。您或您的“数据工程师”同事可能需要在表1，表2，表3表上创建适当的索引。为了弄清楚这一点，请执行这些事情。
显示创建表WhatevertableName;。查看输出。它将向您显示索引。divell前缀的同一表运行选择查询。它将向您显示其用于满足查询的索引。或者，您的大查询结果可能很广泛。没有比O（n）更快地处理数百万行的魔法。
UNION ALLis the most performant way of concatenating result sets. (UNIONis slower because it removes duplicates.)Surely your timeout occurs when you use the view, not when you create it.
Your performance issue stems from one or more of the SELECT queries in your
UNION ALLcascade being very slow. You, or your "data engineer" colleagues, may need to create appropriate indexes on yourtable 1,table 2,table 3tables.To figure this out, do these things.
SHOW CREATE TABLE whateverTableName;. Look at the output. It will show you the indexes.EXPLAIN. It will show you the indexes it used to satisfy the query.Or, it's possible your resultset from your big query is vast. There's no magic that can process millions of rows faster than O(n).