mysql-left连接耗时太长，如何优化查询？

w8biq8rn 于 2021-06-24 发布在 Mysql

关注(0)|答案(3)|浏览(302)

一个领导者可能有很多追随者。一 notification_followers 表在引线添加带有条目的post时获取单个通知 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 table进来了。它存储 follower_id 以及 notification_uuid 对于所有读取通知及其 read_at 时间戳。 notification_followers_read （通知\u uuid、关注者\u id的综合索引）

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

我们现在要返回自动递增 nf.id 用户说明 14 . 他们应该看到来自 notification_followers ，因为此用户尚未读取其中的任何一个。前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 10
``` `notification_followers` 有10万条记录，我们正在使用innodb。这是你的名字 `EXPLAIN` 对于查询：
![](https://i.stack.imgur.com/CdSqx.jpg)
如何优化查询，使其在几毫秒内运行？
使用联合更新
下面是 `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

![](https://i.stack.imgur.com/BnbwI.png)
使用sql转储更新
sql转储在本地复制只需创建 `speed_test` 本地数据库和导入文件，以查看所有表数据（约10万行）的实时慢速查询问题。

sql mysql left-join select innodb

来源：https://stackoverflow.com/questions/50031870/mysql-left-join-takes-too-long-how-to-optimize-query

3条答案

按热度按时间

jucafojl1#

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

赞(0）回复(0）举报 2021-06-24

kg7wmglp2#

我使用了您提供的转储文件在我的pc上复制此环境。原始查询的执行时间最初为0.8秒，没有任何架构更改。可能时间差是因为我的数据库在ssd上运行？
总之，添加以下索引时，执行时间缩短为50ms。

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）回复(0）举报 2021-06-24

qyzbxkaa3#

您的查询是：

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) .

赞(0）回复(0）举报 2021-06-24

我来回答

mysql-left连接耗时太长，如何优化查询？

3条答案

相关问题

热门标签

最新问答