使用子查询查询MYSQL中的查询优化？

2018-06-22 06:07:10

在作业中，我收到了一个查询并要求优化它。该查询是：

SELECT
    C.yearID as year,
    name as teamName,
    C.lgID as league,
    D.cnt as totalBatters,
    C.cnt as aboveAverageBatters
FROM
    (SELECT 
        count(masterID) as cnt, A.yearID, A.teamID, A.lgID
    FROM
        (select 
        masterID,
            teamID,
            yearID,
            lgID,
            sum(AB),
            sum(H),
            sum(H) / sum(AB) as avg
    FROM
        batting
    GROUP BY teamID , yearID , lgID , masterID) B, (select 
        teamID,
            yearID,
            lgID,
            sum(AB),
            sum(H),
            sum(H) / sum(AB) as avg
    FROM
        batting
    WHERE ab is not null
    GROUP BY teamID , yearID , lgID) A
    WHERE
        A.avg >= B.avg AND A.teamID = B.teamID
            AND A.yearID = B.yearID
            AND A.lgID = B.lgID
    GROUP BY teamID , yearID , lgID) C,
    (SELECT 
        count(masterID) as cnt, yearID, teamID, lgID
    FROM
        batting
    WHERE ab is not null
    GROUP BY yearID , teamID , lgID) D, 
    teams
WHERE
    C.cnt / D.cnt >= 0.75
        AND C.yearID = D.yearID
        AND C.teamID = D.teamID
        AND C.lgID = D.lgID
        AND teams.yearID = C.yearID
        AND teams.lgID = C.lgID
        AND teams.teamID = C.teamID

我想知道可以做些什么来优化这个？我对这个概念很陌生，对如何着手有点困惑。一般来说，如何优化其中有select语句的子查询？

一般来说，如何优化其中有select语句的子查询？

这里有一些想法让你开始。我会尊重这个事实，即它是一项任务，最终，您将通过自己浏览并一路学习，更好地理解SQL查询。

我希望这个任务包括一个可以导入到MySQL中的数据集，这样您就可以在进行更改时运行查询，并注意到对执行计划和整体性能的影响。

别名

甚至在考虑优化之前，也许你可以看看如何让代码更易于阅读，理解和维护。子查询的行为方式类似于常规表，因此应该给它们别名/名称，这些别名/名称对于数据集意味着什么是有意义的。

它们是别名B ， A ， C和D ，它们看起来几乎被故意命名为混淆，但实际上，您会惊讶于在实际生产SQL代码中，您经常看到糟糕的命名/别名。

尝试查看（如果可以的话，运行）每个子查询，查看字段及其含义，然后用一个好名称替换别名，并相应地更新不同列中的引用。这将优化查询以获得更好的清晰度，并最终实现可维护性。

`JOIN` s

希望在这个任务完成之时，各种类型的JOIN操作已经被覆盖。如果不是，这是一个StackOverflow答案的好摘要。还有大量的其他资源涵盖了JOIN来龙去脉，包括TechOnTheNet上的一篇好文章。

让我们剥离子查询并查看整个查询的结构。我通过评论取代了逻辑，使其更加明显：

SELECT
--columns
FROM 
(
    SELECT  
    --columns
    FROM 
    (
        select 
        --columns
        FROM batting
    ) B, 
    (
        select 
        --columns
    ) A
    WHERE
    --some comparisons of averages
) C,
(
    SELECT 
     --columns
    FROM batting
) D, 
teams
WHERE
    --a filter based on a calculation
    C.cnt / D.cnt >= 0.75
    --um... what is all this stuff doing down here?
    --shouldn't those be in a JOIN?
        AND C.yearID = D.yearID
        AND C.teamID = D.teamID
        AND C.lgID = D.lgID
        AND teams.yearID = C.yearID
        AND teams.lgID = C.lgID
        AND teams.teamID = C.teamID

你有没有注意到奇怪的东西或任何看起来很奇怪的东西？如果您之前没有阅读过这篇文章，我强烈建议您阅读不良习惯：使用Aaron Bertrand的旧式JOIN。

阅读完它后，再次查看这个查询的框架，并且使用现代JOIN所做的改进应该很突出。这将使查询在清晰度和可维护性方面更加优化。

关键字一致性案例

另一种可以使其更具可读性的方法是使用关键字的一致大写。事实上，大约50/50使用CAPITAL CASE和small case 。对于一个脚本来说，它可能看起来并不重要，但是当这种不一致遍布整个代码库时，对于下一个将不得不在其中发展并维护它的人来说，它会变得非常令人不安。

性能

所以，到目前为止，如果你已经应用了一切，代码应该更容易解密。就表演而言，有两件事对我来说显得有害。有很多聚合，因此，大量的GROUP BY 。

首先查看每个子查询，然后查看每个子查询。看看每个字段在整个查询的上下文中是如何使用的。看看你可以删除哪些，或许编写查询的人最初认为他们需要，但最终没有使用并忘记删除它们。

尝试为GROUP BY字段使用相同的策略，这些字段是包含在具有一个或多个聚合操作的查询中的未聚合的每个字段。 GROUP BY可能会非常昂贵，并且派生子查询也具有GROUP BY的事实加剧了这一点。

还有其他一些可以尝试的技巧，它们更高级，可以在I / O的妥协方面改进执行，例如将一个或多个子查询的结果集提取到临时表中，这样可以释放主表上的锁。

像这些优化可能并不总是必然会提高执行速度本身，但在数据库服务器处于负载的生产环境中，速度通常不是优化的主要关注点，而是“轻量级”（或者小到负载尽可能多的服务器）通常比原始速度更有价值，最终使用更多的资源。

我希望这有帮助！

链接地址: http://www.djcxy.com/p/62457.html

上一篇: Query optimization in MYSQL for a query with sub queries?

下一篇: Optimizing my mysql query

使用子查询查询MYSQL中的查询优化？

别名

JOIN s

关键字一致性案例

性能

`JOIN` s