mysql - 当查询有 order by 子句时，复杂的 sql 运行速度极慢

Question

我有以下需要使用的复杂查询。当我运行它时，它需要 30 到 40 秒。但是，如果我删除 order by 子句，返回结果需要 0.0317 秒，与 30 秒或 40 秒相比，这真的很快。

select DISTINCT media.*
        , username 
from album as album
     , album_permission as permission
      , user as user, media as media
where ((media.album_id = album.album_id 
        and album.private = 'yes' 
        and album.album_id = permission.album_id 
        and (permission.email = '' or permission.user_id = '') ) 
or (media.album_id = album.album_id 
      and album.private = 'no' ) 
or media.album_id = '0' ) 
and media.user_id = user.user_id
and media.media_type = 'video'
order by media.id DESC 
LIMIT 0,20  

order by 上的 id 也是索引的主键。所以我不知道是什么问题。

我还有相册和相册权限表，只是为了检查媒体是公开的还是私有(private)的，如果私有(private)则检查用户是否有权限。我在想这可能是导致问题的原因。如果我在子查询中这样做，效果会更好吗？如果那是解决方案，也有人可以帮我写那个子查询吗？如果你忍不住要写，至少告诉我。我真的为这个问题发疯了..

可能的解决方案

是的，我认为子查询是最好的解决方案，因为下面的查询运行时间为 0.0022 秒。但是我不确定相册的验证是否准确，请检查。

select media.*, username 
from media as media
      , user as user
where  media.user_id = user.user_id 
and media.media_type = 'video'
and media.id in 
    (select media2.id
    from     media as media2
            , album as album
            , album_permission as permission
     where ((media2.album_id = album.album_id  
             and album.private = 'yes'
             and album.album_id = permission.album_id 
            and (permission.email = '' 
                 or permission.user_id = ''))
             or (media.album_id = album.album_id 
                   and album.private = 'no' ) 
              or media.album_id = '0' ) 
     and media.album_id = media2.album_id )            
order by media.id DESC
LIMIT 0,20   

Answer 1

使用 EXPLAIN 来确定为什么没有 ORDER BY 子句会更快:

http://dev.mysql.com/doc/refman/5.1/en/using-explain.html

我建议也使用 ANSI 连接语法重写查询。这可能有助于提高性能。像这样:

select DISTINCT media.*
        , username 
from album as album
  inner join media as media on media.album_id = album.album_id
  inner join user as user on media.user_id = user.user_id
  left outer join album_permission as permission on album.album_id = permission.album_id
where ((album.private = 'yes' 
        and (permission.email = '' or permission.user_id = '') ) 
or album.private = 'no' 
or media.album_id = '0' ) 
and media.media_type = 'video'
order by media.id DESC 
LIMIT 0,20  

您还可以将查询分成 2 或 3 个不同的查询，然后将它们 UNION 在一起以避免外连接。