首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >MySQL -左连接耗时太长,如何优化查询?

问MySQL -左连接耗时太长,如何优化查询?
EN

Stack Overflow用户
提问于 2018-04-25 21:51:28
回答 3查看 206关注 0票数 0

一个领导者可能有很多追随者。notification_followers表在领导者添加带有条目leader_id 1和notifiable_id 0 (id 1,2在表中)的帖子时获得单个通知。当当前用户14后面跟着某人时,同一个表只会收到一个通知,其中包含一个条目leader_id 0和notifiable_id 14 (表中的id 3)。

notification_followers (id是主字段,除数据外,每个字段都是自己的索引)

代码语言:javascript
复制
| 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是主字段,每个字段都是自己的索引)

代码语言:javascript
复制
| 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综合指数)

代码语言:javascript
复制
| 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秒太长了

代码语言:javascript
复制
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有100 K的记录,我们正在使用InnoDB。下面是查询的EXPLAIN:

​

​

如何优化查询,使其在几毫秒内运行?

使用联合更新

下面是用于以下EXPLAIN查询的UNION,我还分别为每个子查询包含了EXPLAIN。

代码语言:javascript
复制
(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行)共存。

EN

回答 3

Stack Overflow用户

发布于 2018-04-25 21:58:54

您的查询是:

代码语言:javascript
复制
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

代码语言:javascript
复制
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)。

票数 0
EN

Stack Overflow用户

发布于 2018-04-25 23:31:07

OR经常导致性能问题,因为它使使用索引变得困难。将查询分成两种不同的情况,并将它们与UNION结合起来。

代码语言:javascript
复制
(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
票数 0
EN

Stack Overflow用户

发布于 2018-05-05 09:34:44

我使用你提供的转储文件在我的电脑上复制这个环境。原始查询的执行时间最初为0.8秒,没有任何模式更改。也许时差是因为我的数据库运行在SSD上?

无论如何,当添加以下索引时,执行时间将减少到50 to。

代码语言:javascript
复制
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`);
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/50031870

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档