一个领导者可能有很多追随者。notification_followers表在领导者添加带有条目leader_id 1和notifiable_id 0 (id 1,2在表中)的帖子时获得单个通知。当当前用户14后面跟着某人时,同一个表只会收到一个通知,其中包含一个条目leader_id 0和notifiable_id 14 (表中的id 3)。
notification_followers (id是主字段,除数据外,每个字段都是自己的索引)
| 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表,以查看登录用户是否以followed_id的形式存在到leader_id中,以便他们知道通知的情况,但只有当他们在发布通知之前遵循领导者(新的追随者不应该在跟踪用户时收到旧的帖子通知,只有新的通知)。
user_follows (id是主字段,每个字段都是自己的索引)
| 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现在,我们希望返回由自动递增nf.id desc为用户14命令的最新10项通知。他们应该会看到来自notification_followers的所有3个通知,因为非这些通知都是由这个用户读取的,而不是。前2位,因为他们在发布之前跟随了领导者,第3位通知,因为他们被跟踪,他们的notifiable_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 10notification_followers有100 K的记录,我们正在使用InnoDB。下面是查询的EXPLAIN:

如何优化查询,使其在几毫秒内运行?
使用联合更新
下面是用于以下EXPLAIN查询的UNION,我还分别为每个子查询包含了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转储只需在本地创建speed_test数据库并导入文件,以查看慢速查询问题与所有表数据(~100 K行)共存。
发布于 2018-04-25 21:58:54
您的查询是:
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条款是真正的杀手。但是根据你的逻辑,我认为你更想要的是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(notifiable_id, leader_id, created_at)、user_follows(leader_id, follower_id, created_at)和notification_followers_read(notification_uuid, notifiable_id)。
发布于 2018-04-25 23:31:07
OR经常导致性能问题,因为它使使用索引变得困难。将查询分成两种不同的情况,并将它们与UNION结合起来。
(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 ALL
(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发布于 2018-05-05 09:34:44
我使用你提供的转储文件在我的电脑上复制这个环境。原始查询的执行时间最初为0.8秒,没有任何模式更改。也许时差是因为我的数据库运行在SSD上?
无论如何,当添加以下索引时,执行时间将减少到50 to。
ALTER TABLE `notification_followers` ADD INDEX `notification_followe_idx_id_uuid_at_id_data` (`leader_id`,`uuid`,`created_at`,`id`,`data`(255));
ALTER TABLE `notification_followers_read` ADD INDEX `notification_followe_idx_id_uuid_at` (`follower_id`,`notification_uuid`,`read_at`);
ALTER TABLE `user_follows` ADD INDEX `user_follows_idx_id_id_at` (`follower_id`,`leader_id`,`created_at`);https://stackoverflow.com/questions/50031870
复制相似问题