mysql - 在连接两个大表时优化 ORDER BY

Question

我们的系统有两个表:user_posts(帖子)和 user_post_like_counts(点赞数)。我实现了一个功能来按喜欢计数对帖子(asc/desc)进行排序。

问题是:当数据表很大时，大约有 200 万条记录。执行 sql 需要很长时间(2 分钟)。

SELECT DISTINCT user_posts.*, `user_post_like_counts`.total_count
FROM `user_posts`
LEFT OUTER JOIN `user_post_like_counts` ON `user_post_like_counts`.`user_post_id` = `user_posts`.`id`
WHERE `user_posts`.`deleted_at` IS NULL
       AND `user_posts`.`created_at` <= '2017-04-24 01:47:00'
ORDER BY `user_post_like_counts`.total_count DESC
LIMIT 50
OFFSET 0;

如果我删除order by，上面的 sql 只需不到 1 秒即可执行。

这是此 sql 查询的 EXPLAIN。

/image/sBK4j.png

非常感谢任何帮助。谢谢

Answer 1

i) 你不能删除 order by 因为你必须使用分页，这很好

ii) 删除不同

iii) 删除“*”并只使用选定的列名

iii)希望user_post_like_counts.user_post_id和user_posts.id都是CI

iv) 由于 total_count 将始终按 desc 排序，使其成为 NCI 并在索引级别本身按 desc 排序

v) 如果您通过使用 deleted_at IS NULL 获得几乎所有记录 (80%)，则无需在已删除的表上创建索引。

vi) 因为 post 表包含很多记录并且是最重要的表。不要将已删除的记录保存在同一个表中。创建单独的历史记录表。这是一个重要因素

vii) 所以我认为在 created_at 上创建 NCI 并覆盖 index.inlude post 表的那些列。