MySQL 仅查询大表的最近项目？

Question

我们在整个论坛中实现了“致谢”系统。我们还有一个“查看您最近的感谢”页面，随着“感谢”表增长到数百万，该页面现在变得越来越慢。

这是“感谢”表的结构:

Field    Type        Null   Key Default Extra 
id       int(10)     NO     PRI NULL    auto_increment 
userid   int(10)     NO     MUL NULL 
username varchar(50) NO         NULL 
date     int(10)     NO         NULL 
postid   int(10)     NO     MUL NULL 

在“Recent Thanks”页面上，为当前用户($uid)运行以下查询:

  SELECT post_thanks.postid, post_thanks.date, post_thanks.username, post_thanks.userid, thread.title 
    FROM " . TABLE_PREFIX . "post_thanks AS post_thanks
         LEFT JOIN " . TABLE_PREFIX . "post AS post
                ON post_thanks.postid = post.postid
         LEFT JOIN " . TABLE_PREFIX . "thread AS thread
                ON thread.threadid = post.threadid      
   WHERE post.userid = '$uid'
   ORDER BY post_thanks.id DESC
   LIMIT 20

这导致了 20 个最近的帖子，感谢该用户。现在 post_thanks 表超过一百万行，这个查询需要一秒钟多的时间才能完成。有时它来自缓存的速度更快，但我一直在努力对其进行优化，因为用户通常只有在收到新通知(即缓存已更改)时才会查看他们的“谢谢”。

我的第一个问题是这个查询使用的是文件排序而不是索引。鉴于表结构，ORDER BY post_thanks.id 不应该使用索引吗？

我的第二个问题是我不需要查询 500 万行只是为了获得最近的感谢。整个论坛每周通常有 5,000 个感谢。如果用户一周不查看论坛，那么他们最近的感谢可能已经消失了，这没关系。所以我像这样修改了查询(唯一更改的行是#2)

  SELECT post_thanks.postid, post_thanks.date, post_thanks.username, post_thanks.userid, thread.title 
  FROM (SELECT * FROM " . TABLE_PREFIX . "post_thanks ORDER BY id DESC LIMIT 5000) AS post_thanks
  LEFT JOIN " . TABLE_PREFIX . "post AS post
  ON post_thanks.postid = post.postid
  LEFT JOIN " . TABLE_PREFIX . "thread AS thread
  ON thread.threadid = post.threadid    
  WHERE post.userid = '$uid' 
  ORDER BY post_thanks.id DESC
  LIMIT 20

即使禁用缓存，此查询的运行时间始终少于 .001 秒。

这是解决我们性能问题的好方法吗？或者有没有更好的方法来加速这个查询而不忽略旧数据？我仍然对为什么没有以任何方式使用 Index 感到困惑。

Answer 1

尝试使用 inner join 而不是 left join 运行查询:

SELECT post_thanks.postid, post_thanks.date, post_thanks.username, post_thanks.userid, thread.title 
FROM " . TABLE_PREFIX . "post_thanks post_thanks INNER JOIN
     " . TABLE_PREFIX . "post AS post
     ON post_thanks.postid = post.postid INNER JOIN
     " . TABLE_PREFIX . "thread AS thread
     ON thread.threadid = post.threadid      
WHERE post.userid = '$uid'
ORDER BY post_thanks.id DESC
LIMIT 20;

接下来，我注意到 where 条件是 post.userid = '$uid'。这可能是真的还是也是:post_thanks.userid = '$uid'？

我建议使用内部联接，使用 where post_thanks.userid = '$uid' 和索引:post_thanks(userid, id)。