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

We're trying to get the latest 10 notifications for a follower from the database. There are a few joins we do to ensure we get the correct set of notifications for the follower. If the person they follow (their leader) added a new post, the follower should only get the notification for posts that were added after they started following the leader (no sense in showing them their leader's older posts as new notifications). The other join is to make sure we get the notification's read_at time,so the follower knows if it was already read or not. Here is the query, but it takes ~9 secs which is too slow. It should ideally only take a few ms, specially with the indexes:

我们正在尝试从数据库中获取最新的10个关注者通知。我们做了一些连接，以确保我们为关注者获取正确的通知集。如果他们关注的人（他们的领导者）添加了一个新帖子，则关注者应该只收到他们开始关注领导者后添加的帖子的通知（没有意义将他们的领导者的旧帖子显示为新通知）。另一个连接是确保我们获得通知的read_at时间，因此关注者知道它是否已被读取。这是查询，但它需要~9秒，这太慢了。它理想情况下只需要几毫秒，特别是索引：

Query:

查询：

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

Indexes:

索引：

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`);

Explain:

说明：

Correct Results (take ~9 secs):

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

SQL DUMP:

SQL DUMP：

SQL DUMP TO REPRODUCE LOCALLY just create speed_test database locally and import file to see the slow query issue live with all the table data (~100K rows).

SQL DUMP TO REPRODUCE LOCALLY只需在本地创建speed_test数据库并导入文件以查看所有表数据（~100K行）的慢查询问题。

How can we optimize the above to get the correct results within a few ms?

我们如何优化上述内容以在几毫秒内获得正确的结果？

2 个解决方案

#1

For this query:

对于此查询：

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;

I would recommend indexes on user_follower(leader_id, follower_id, follow_status, created_at) and notification_followers_read(notification_followers_id, follower_id, read_at). The order of the columns in the indexes matters.

我建议在user_follower（leader_id，follower_id，follow_status，created_at）和notification_followers_read（notification_followers_id，follower_id，read_at）上建立索引。索引中列的顺序很重要。

Notice that I changed the first JOIN to an inner join, because the WHERE clause turns it into one anyway.

请注意，我将第一个JOIN更改为内连接，因为WHERE子句无论如何都将它转换为一个连接。

Hmmm, let's try rewriting the query:

嗯，让我们尝试重写查询：

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;

For this, you want to be sure you have an index on notification_followers(id) as well.

为此，您需要确保在notification_followers（id）上也有索引。

Depending on your data, the inner subquery might be faster with this approach:

根据您的数据，使用此方法可以更快地使用内部子查询：

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

For this, the indexes are user_follows(follower_id, follow_status, leader_id, created_at) and notification_followers(leader_id, created_at, id). This might be faster.

为此，索引是user_follows（follower_id，follow_status，leader_id，created_at）和notification_followers（leader_id，created_at，id）。这可能会更快。

#2

You should try this.

你应该试试这个。

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;

Create indexes on.

创建索引。

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`);

#1