MySQL查询优化：如何将follower通知查询提速至毫秒级？

Let's break down why your current query is slow and how to fix it to get that sub-10ms response you're after.

The Root Cause of the Slowdown #

Your original query starts by scanning potentially large portions of the notification_followers table, then performs two joins, filters on nf.created_at > uf.created_at, and finally sorts all matching records to get the top 10. With 100k+ records, this creates a huge intermediate dataset that takes time to process—hence the 9-second wait.

Optimized Query Approach #

Instead of processing all possible notifications first, we'll prioritize the most recent notifications (since you only need the latest 10), filter down to those that match the follower's active follows, and then fetch read status. This drastically reduces the data we need to process at each step.

Here's the optimized query:

SELECT 
    nf.id, 
    nf.uuid, 
    nf.leader_id, 
    nf.data, 
    nf.created_at, 
    nfr.read_at 
FROM (
    -- Grab a slightly larger batch of recent notifications to account for non-qualifying ones
    SELECT * 
    FROM notification_followers 
    ORDER BY id DESC 
    LIMIT 20
) nf
-- Join only with follows that are active and where the notification was posted after follow started
JOIN user_follows uf 
    ON uf.follower_id = 14 
    AND uf.leader_id = nf.leader_id 
    AND uf.follow_status = 'follow'
    AND nf.created_at > uf.created_at
-- Left join to get read status (no need to filter here)
LEFT JOIN notification_followers_read nfr 
    ON nfr.follower_id = 14 
    AND nfr.notification_followers_id = nf.id
ORDER BY nf.id DESC 
LIMIT 10;

Why This Works #

1. Small Initial Dataset: The subquery uses the primary key index on id to fetch the latest 20 notifications in milliseconds. We use 20 instead of 10 to account for cases where some recent notifications are from leaders the follower doesn't follow, or were posted before the follower started following.
2. Efficient Joins:
   • user_follows uses your existing uf_fid_lid_fs_ca_idx index, which is a covering index—it includes all fields needed for the join and filter, so no need to hit the table itself.
   • notification_followers_read uses your nfr_fid_nfid_ra_idx covering index to fetch read status without table lookups.
3. Minimal Sorting: Since we're already fetching notifications in descending id order, the final sort is trivial (we're just reordering a tiny dataset).

Additional Checks #

• Verify Index Usage: Run EXPLAIN on both the original and optimized queries to confirm the optimized version uses indexes for all steps. You should see Using index in the Extra column for the user_follows and notification_followers_read joins.
• Adjust Batch Size: If the follower follows hundreds of leaders and often misses the 10 qualifying notifications in the first 20, bump the subquery limit to 50 or 100—this is still negligible compared to scanning the entire table.
• Confirm ID/Created_at Correlation: If notification_followers.id is an auto-incrementing primary key, id and created_at should be strictly correlated (newer notifications have higher IDs). If not, replace the subquery's ORDER BY id DESC with ORDER BY created_at DESC, id DESC to ensure correct recency.

内容的提问来源于stack exchange，提问作者Wonka