mysql-如何使用索引优化查询？

brc7rcf0 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(387)

我们正试图从数据库中获取跟踪者的最新10个通知。有几个连接可以确保为跟随者获得正确的通知集。如果他们跟随的人（他们的领导者）添加了一个新的帖子，那么追随者应该只得到在他们开始跟随领导者之后添加的帖子的通知（没有意义将他们领导者的旧帖子显示为新通知）。另一个连接是确保我们收到通知 read_at 时间，所以跟随者知道它是否已经被阅读。这是一个查询，但是它需要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 AND uf.follow_status = 'follow'
LEFT JOIN notification_followers_read nfr ON nf.id = nfr.notification_followers_id AND nfr.follower_id = 14
WHERE (nf.created_at > uf.created_at)
ORDER BY nf.id DESC
LIMIT 10

索引：

ALTER TABLE `notification_followers` ADD INDEX `nf_lid_ca_id_idx` (`leader_id`,`created_at`,`id`);
ALTER TABLE `user_follows` ADD KEY`uf_fid_lid_fs_ca_idx` (`follower_id`,`leader_id`,`follow_status`,`created_at`)
ALTER TABLE `notification_followers_read` ADD INDEX `nfr_fid_nfid_ra_idx` (`follower_id`,`notification_followers_id`,`read_at`);

解释：

正确的结果（大约需要9秒）：

sql转储：
sql转储在本地复制只需创建 speed_test 本地数据库和导入文件，以查看所有表数据（约10万行）的实时慢速查询问题。
如何在几毫秒内优化上述结果？

sql mysql left-join select innodb

来源：https://stackoverflow.com/questions/50476360/mysql-how-to-optimize-query-with-indexes

2条答案

按热度按时间

iovurdzv1#

你应该试试这个。

SELECT nf.id, nf.uuid, nf.leader_id, nf.data, nf.created_at, nfr.read_at 
FROM notification_followers nf 
JOIN user_follows uf ON uf.leader_id = nf.leader_id and nf.created_at > uf.created_at AND uf.follow_status = 'follow'  AND uf.follower_id = 14 
LEFT JOIN notification_followers_read nfr ON nf.id = nfr.notification_followers_id AND nfr.follower_id = 14 
ORDER BY nf.id DESC
LIMIT 10;

在上创建索引。

ALTER TABLE `notification_followers` ADD INDEX `nf_lid_ca_id_idx`(`leader_id`,`created_at`,`id`);
ALTER TABLE `user_follows` ADD KEY`uf_fid_lid_fs_ca_idx`(`leader_id`,`created_at`,`follow_status`,`follower_id`)
ALTER TABLE `notification_followers_read` ADD INDEX `nfr_fid_nfid_ra_idx`(`notification_followers_id`,`follower_id`,`read_at`);

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

zaqlnxep2#

对于此查询：

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

我建议在 user_follower(leader_id, follower_id, follow_status, created_at) 以及 notification_followers_read(notification_followers_id, follower_id, read_at) . 索引中列的顺序很重要。
注意，我更改了第一个 JOIN 内部连接，因为 WHERE 从句把它变成了一个。
嗯，让我们尝试重写查询：

SELECT nf.id, nf.uuid, nf.leader_id, nf.data, nf.created_at,
       (SELECT nfr.read_at
        FROM notification_followers_read nfr
        WHERE nf.id = nfr.notification_followers_id AND nfr.follower_id = 14
       ) nfr
FROM (SELECT nf.*
      FROM notification_followers nf 
      WHERE EXISTS (SELECT 1
                    FROM user_follows uf 
                    WHERE uf.leader_id = nf.leader_id AND uf.follower_id = 14 AND
                          uf.follow_status = 'follow' AND nf.created_at > uf.created_at
                   )
      ORDER BY nf.id DESC
      LIMIT 10
     ) nf;

为此，您需要确保在上有索引 notification_followers(id) 也。
根据您的数据，使用这种方法，内部子查询可能会更快：

FROM (SELECT nf.*
      FROM user_follows uf JOIN
           notification_followers nf 
           ON uf.leader_id = nf.leader_id AND nf.created_at > uf.created_at
      WHERE uf.follower_id = 14 AND uf.follow_status = 'follow' 
      ORDER BY nf.id DESC
      LIMIT 10
     ) nf

为此，索引是 user_follows(follower_id, follow_status, leader_id, created_at) 以及 notification_followers(leader_id, created_at, id) . 这可能更快。

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

我来回答

mysql-如何使用索引优化查询？

2条答案

相关问题

热门标签

最新问答