改进MySQL查询左外部连接

Question

我们正在维护内容的历史记录。我们希望获得每个内容的更新条目，创建时间和更新时间应该是内容的第一个条目。该查询包含多个选择，其中有许多左连接的子句。数据集非常大，因此查询需要超过60秒的时间才能执行。请帮助改善同样的东西。查询：

select * from (select * from (
    SELECT c.*, initCMS.initcreatetime, initCMS.initupdatetime, user.name as partnerName, r.name as rightsName, r1.name as copyRightsName, a.name as agelimitName, ct.type as contenttypename, cat.name as categoryname, lang.name as languagename FROM ContentCMS c 
        left join ContentCategoryType ct on ct.id = c.contentType 
        left join User user on c.contentPartnerId = user.id 
        left join Category cat on cat.id = c.categoryId 
        left join Language lang on lang.id = c.languageCode 
        left join CopyRights r on c.rights = r.id 
        left join CopyRights r1 on c.copyrights = r1.id 
        left join Age a on c.ageLimit = a.id 
        left outer join (
            SELECT contentId, createTime as initcreatetime, updateTime as initupdatetime from ContentCMS cms where cms.deleted='0'
        ) as initCMS on initCMS.contentId = c.contentId WHERE c.deleted='0' order by c.id  DESC
) as temp group by contentId) as c where c.editedBy='0'

任何帮助将不胜感激。谢谢。

Answer 1

只是部分评估和建议，因为您的查询似乎未正确形成

这个左连接似乎无用

    FROM ContentCMS c 
    ......
    left join (
        SELECT contentId
            , createTime as initcreatetime
            , updateTime as initupdatetime 
        from ContentCMS cms 
        where cms.deleted='0'
    ) as initCMS on initCMS.contentId = c.contentId 

同一表

在联接中的子查询中的顺序(无限(是无用的，因为加入有序值或无序值会产生相同的结果

contentid的组是奇怪的beacuse，没有聚合函数，而没有聚集功能的组的sue of of cop of cop of contrecation by of contrecation by contrecation by of contrecation debrecation to ret overecation在最新的mysql中，不允许(通过deafult((如果您需要独特的值或仅为每个contentid的行(，则应以不随意的方式使用独特的值或检索该值不汇总列的休闲价值。

对于部分评估，应将您的查询重构为

SELECT c.*
          , c.initcreatetime
          , c.initupdatetime
          , user.name as partnerName
          , r.name as rightsName
          , r1.name as copyRightsName
          , a.name as agelimitName
          , ct.type as contenttypename
          , cat.name as categoryname
          , lang.name as languagename 
      FROM ContentCMS c 
      left join ContentCategoryType ct on ct.id = c.contentType 
      left join User user on c.contentPartnerId = user.id 
      left join Category cat on cat.id = c.categoryId 
      left join Language lang on lang.id = c.languageCode 
      left join CopyRights r on c.rights = r.id 
      left join CopyRights r1 on c.copyrights = r1.id 
      WHERE c.deleted='0' 
) as temp 

在其余

也只是为了不适当地降低行没有帮助的性能……您还应该重新评估数据建模和设计。