MySQL - Left Join 耗时太长，如何优化查询？

Question

一个领导者可能有很多追随者。当领导者添加条目为 leader_id 1 和 notific_id 0(表中的 id 1,2)的帖子时，notification_followers 表会收到一条通知。当当前用户 14 被某人关注时，同一个表会收到一条通知，条目为 leader_id 0 和 notabilly_id 14(id 3 in表)。

notification_followers(id 是 PRIMARY，除数据之外的每个字段都是自己的索引)

| id | uuid               | leader_id | notifable_id | data   | created_at
-----------------------------------------------------------------------------------
| 1  | 001w2cwfoqzp8F3... | 1         | 0            | Post A | 2018-04-19 00:00:00
| 2  | lvbuX4d5qCHJUIN... | 1         | 0            | Post B | 2018-04-20 00:00:00
| 3  | eEq5r5g5jApkKgd... | 0         | 14           | Follow | 2018-04-21 00:00:00

所有与关注者相关的通知现在都集中在一个地方，这是完美的。

我们现在需要检查用户 14 是否是 leader_id 1 的关注者，以了解是否向他们显示通知 1 和 2。为此，我们扫描 user_follows 表，查看登录用户是否作为 leader_id 的 followed_id 存在，以便他们了解通知，但前提是他们在发布通知之前关注了领导者(当关注用户时，新关注者不应收到较旧的帖子通知，只有新关注者才会收到)。

user_follows (id 是 PRIMARY，每个字段都是自己的索引)

| id | leader_id | follower_id | created_at
----------------------------------------------------
| 1  | 1         | 14         |  2018-04-18 00:00:00 // followed before, has notifs
| 2  | 1         | 15         |  2018-04-22 00:00:00 // followed after, no notifs

最后要注意的是，用户应该知道通知是否被读取，这就是 notification_followers_read 表的用武之地。它存储 follower_id 以及包含所有已读通知的 notification_uuid 及其 read_at 时间戳。

notification_followers_read (notification_uuid、follower_id 的复合索引)

| notification_uuid | follower_id | read_at
--------------------------------------------------------
  qIXE97AP49muZf... | 17          | 2018-04-21 00:00:00 // not for 14, we ignore it

我们现在想要返回用户 14 的按自动递增 nf.id desc 排序的最新 10 条通知。他们应该会看到来自 notification_followers 的全部 3 条通知，因为该用户尚未阅读其中任何通知。前 2 个，因为他们在领导者发帖之前关注了领导者，第三个通知，因为他们被关注并且他们的 notABLE_id 为 14.

这是有效的查询，但花费的时间太长~9秒:

SELECT nf.id, nf.uuid, nf.leader_id, nf.data, nf.created_at, nfr.read_at
FROM notification_followers nf
LEFT JOIN user_follows uf ON uf.leader_id = nf.leader_id AND uf.follower_id = 14
LEFT JOIN notification_followers_read nfr ON nf.uuid = nfr.notification_uuid AND nfr.follower_id = 14
WHERE (nf.created_at > uf.created_at OR notifiable_id = 14)
ORDER BY nf.id DESC LIMIT 10

notification_followers 有大约 100K 条记录，我们使用的是 InnoDB。以下是查询的EXPLAIN:

我们如何优化查询，使其在几毫秒内运行？

使用 UNION 更新

下面是以下 UNION 查询的 EXPLAIN，我还分别为每个子查询添加了 EXPLAIN。

(SELECT nf.id, nf.uuid, nf.leader_id, nf.data, nf.created_at, nfr.read_at
FROM notification_followers nf
LEFT JOIN user_follows uf ON uf.leader_id = nf.leader_id AND uf.follower_id = 14 AND nf.created_at > uf.created_at
LEFT JOIN notification_followers_read nfr ON nf.uuid = nfr.notification_uuid AND nfr.follower_id = 14
ORDER BY nf.id DESC
LIMIT 10)

UNION DISTINCT

(SELECT nf.id, nf.uuid, nf.leader_id, nf.data, nf.created_at, nfr.read_at
FROM notification_followers nf
LEFT JOIN notification_followers_read nfr ON nf.uuid = nfr.notification_uuid AND nfr.follower_id = 14
WHERE nf.notifiable_id = 14
ORDER BY nf.id DESC
LIMIT 10)

ORDER BY id desc
LIMIT 10

使用 SQL 转储更新

SQL DUMP TO REPRODUCE LOCALLY只需在本地创建 speed_test 数据库并导入文件即可查看所有表数据的慢查询问题(约 10 万行)。

Answer 1

您的查询是:

SELECT nf.id, nf.uuid, nf.leader_id, nf.data, nf.created_at, nfr.read_at
FROM notification_followers nf LEFT JOIN
     user_follows uf
     ON uf.leader_id = nf.leader_id AND uf.follower_id = 14 LEFT JOIN
     notification_followers_read nfr
     ON nf.uuid = nfr.notification_uuid AND nfr.follower_id = 14
WHERE nf.created_at > uf.created_at OR nf.notifiable_id = 14
ORDER BY nf.id DESC
LIMIT 10;

这有点难。 or 子句是真正的 killer 。但根据你的逻辑，我认为你想要更多的 and 而不是 or:

SELECT nf.id, nf.uuid, nf.leader_id, nf.data, nf.created_at, nfr.read_at
FROM notification_followers nf LEFT JOIN
     user_follows uf
     ON uf.leader_id = nf.leader_id AND nf.created_at > uf.created_at AND 
        uf.follower_id = 14 LEFT JOIN
     notification_followers_read nfr
     ON nf.uuid = nfr.notification_uuid AND nfr.follower_id = 14
WHERE nf.notifiable_id = 14
ORDER BY nf.id DESC
LIMIT 10;

(请注意，它移至 ON 子句。)

明显的索引是:notification_followers(notifying_id,leader_id,created_at)、user_follows(leader_id,follower_id,created_at)和notification_followers_read(notification_uuid,notifying_id).