mysql查询优化步骤或如何优化查询

Question

我不太了解查询优化，但我知道执行查询的顺序

1. FROM条款
2. WHERE条款
3. 按条款分组
4. 拥有条款
5. 选择子句
6. 逐条命令

这是我写的查询

SELECT 
    `main_table`.forum_id,
     my_topics.topic_id,
     (
        SELECT MAX(my_posts.post_id) FROM my_posts WHERE my_topics.topic_id = my_posts.topic_id 
     ) AS `maxpostid`,
     (
       SELECT my_posts.admin_user_id FROM my_posts WHERE my_topics.topic_id = my_posts.topic_id ORDER BY my_posts.post_id DESC LIMIT 1
     ) AS `admin_user_id`, 
     (
       SELECT my_posts.user_id FROM my_posts WHERE my_topics.topic_id = my_posts.topic_id ORDER BY my_posts.post_id DESC LIMIT 1
     ) AS `user_id`,
     (
      SELECT COUNT(my_topics.topic_id) FROM my_topics WHERE my_topics.forum_id = main_table.forum_id ORDER BY my_topics.forum_id DESC LIMIT 1
     ) AS `topicscount`,
     (
      SELECT COUNT(my_posts.post_id) FROM my_posts WHERE my_topics.topic_id = my_posts.topic_id ORDER BY my_topics.topic_id DESC LIMIT 1
     ) AS `postcount`, 
     (
      SELECT CONCAT(admin_user.firstname,' ',admin_user.lastname) FROM admin_user INNER JOIN my_posts ON my_posts.admin_user_id = admin_user.user_id WHERE my_posts.post_id = maxpostid ORDER BY my_posts.post_id DESC LIMIT 1
     ) AS `adminname`, 
    (
     SELECT forum_user.nick_name FROM forum_user INNER JOIN my_posts ON my_posts.user_id = forum_user.user_id WHERE my_posts.post_id = maxpostid ORDER BY my_posts.post_id DESC LIMIT 1
    ) AS `nickname`, 
    (
      SELECT CONCAT(ce1.value,' ',ce2.value) AS fullname FROM my_posts INNER JOIN customer_entity_varchar AS ce1 ON ce1.entity_id = my_posts.user_id INNER JOIN customer_entity_varchar AS ce2 ON ce2.entity_id=my_posts.user_id WHERE (ce1.attribute_id = 1) AND (ce2.attribute_id = 2) AND my_posts.post_id = maxpostid ORDER BY my_posts.post_id DESC LIMIT 1
    ) AS `fullname`
    FROM `my_forums` AS `main_table`
       LEFT JOIN `my_topics` ON main_table.forum_id = my_topics.forum_id 
    WHERE  (forum_status = '1')

现在我想知道是否有任何方法来优化它？因为所有的逻辑都是用Select部分编写的，而不是From部分，但是我不知道如何在查询的From部分中编写相同的逻辑？

有什么区别吗?还是两者是一样的？

谢谢

Answer 1

相关的子查询实际上应该是最后的手段，它们通常会被执行RBAR，并且考虑到您的许多子查询非常相似，尝试使用联接获得相同的结果将导致少得多的表扫描。

我注意到的第一件事是，您的所有子查询都包括表my_posts，而且大多数都包含ORDER BY my_posts.post_id DESC LIMIT 1，那些没有组计数的查询，所以顺序和限制无论如何都是多余的，所以我的第一步是加入到my_posts：

SELECT  *
FROM    my_forums AS f
        LEFT JOIN my_topics AS t
            ON f.forum_id = t.forum_id 
        LEFT JOIN
        (   SELECT  topic_id, MAX(post_id) AS post_id
            FROM my_posts 
            GROUP BY topic_id
        ) AS Maxp
            ON Maxp.topic_id = t.topic_id 
        LEFT JOIN my_posts AS p
            ON p.post_id = Maxp.post_id
WHERE   forum_status = '1';

在这里，子查询确保每个topic_id都能得到最新的帖子。为了方便起见，我缩短了您的表别名，我不知道您为什么要使用比实际表名更长的表别名？

现在，您可以开始在列中添加大量的查询，为了获得post计数，我在子查询Maxp中添加了一个计数，我还必须添加几个联接来获取一些细节，例如名称：

SELECT  f.forum_id,
        t.topic_id,
        p.post_id AS `maxpostid`,
        p.admin_user_id,
        p.user_id,
        t2.topicscount,
        maxp.postcount,
        CONCAT(au.firstname,' ',au.lastname) AS adminname, 
        fu.nick_name AS nickname
        CONCAT(ce1.value,' ',ce2.value) AS fullname 
FROM    my_forums AS f
        LEFT JOIN my_topics AS t
            ON f.forum_id = t.forum_id 
        LEFT JOIN
        (   SELECT  topic_id, 
                    MAX(post_id) AS post_id,
                    COUNT(*) AS postcount
            FROM my_posts 
            GROUP BY topic_id
        ) AS Maxp
            ON Maxp.topic_id = t.topic_id 
        LEFT JOIN my_posts AS p
            ON p.post_id = Maxp.post_id
        LEFT JOIN admin_user AS au
            ON au.admin_user_id = p.admin_user_id
        LEFT JOIN forum_user AS fu
            ON fu.user_id = p.user_id
        LEFT JOIN customer_entity_varchar AS ce1
            ON ce1.entity_id = p.user_id
            AND ce1.attribute_id = 1
        LEFT JOIN customer_entity_varchar AS ce2
            ON ce2.entity_id = p.user_id
            AND ce2.attribute_id = 2
        LEFT JOIN
        (   SELECT  forum_id, COUNT(*) AS topicscount
            FROM    my_topics
            GROUP BY forum_id
        ) AS t2
            ON t2.forum_id = f.forum_id
WHERE   forum_status = '1';

我不熟悉您的模式，所以上面的内容可能需要一些调整，但是主体仍然是使用联接而不是子选择。

我要做的下一个优化阶段是去掉您的customer_entity_varchar表，或者至少停止使用它来存储像名字和姓氏这样基本的东西。实体属性值模型是一个SQL反模式，如果将FirstName和LastName两列添加到forum_user表中，您将立即失去查询中的两个联接。我不会过多地参与到EAV诉关系辩论中，因为这已经被广泛讨论了很多次，而且我没有什么要补充的了。

最后阶段将是添加适当的索引，您可以最好地决定什么是合适的，我建议您可能希望索引至少在每个表中的外键上，可能更多。

编辑

要获得每个forum_id中的一行，您需要使用以下方法：

SELECT  f.forum_id,
        t.topic_id,
        p.post_id AS `maxpostid`,
        p.admin_user_id,
        p.user_id,
        MaxT.topicscount,
        maxp.postcount,
        CONCAT(au.firstname,' ',au.lastname) AS adminname, 
        fu.nick_name AS nickname
        CONCAT(ce1.value,' ',ce2.value) AS fullname 
FROM    my_forums AS f
        LEFT JOIN
        (   SELECT  t.forum_id, 
                    COUNT(DISTINCT t.topic_id) AS topicscount,
                    COUNT(*) AS postCount,
                    MAX(t.topic_ID) AS topic_id
            FROM    my_topics AS t
                    INNER JOIN my_posts AS p
                        ON p.topic_id = p.topic_id
            GROUP BY t.forum_id
        ) AS MaxT
            ON MaxT.forum_id = f.forum_id
        LEFT JOIN my_topics AS t
            ON t.topic_ID = Maxt.topic_ID 
        LEFT JOIN
        (   SELECT  topic_id, MAX(post_id) AS post_id
            FROM    my_posts 
            GROUP BY topic_id
        ) AS Maxp
            ON Maxp.topic_id = t.topic_id 
        LEFT JOIN my_posts AS p
            ON p.post_id = Maxp.post_id
        LEFT JOIN admin_user AS au
            ON au.admin_user_id = p.admin_user_id
        LEFT JOIN forum_user AS fu
            ON fu.user_id = p.user_id
        LEFT JOIN customer_entity_varchar AS ce1
            ON ce1.entity_id = p.user_id
            AND ce1.attribute_id = 1
        LEFT JOIN customer_entity_varchar AS ce2
            ON ce2.entity_id = p.user_id
            AND ce2.attribute_id = 2
WHERE   forum_status = '1';