MySQL左连接查询耗时过长如何优化？附业务场景说明

Alright, let's tackle this slow left join issue with your notification_followers table. Based on the business logic and schema you've shared (two core notification scenarios plus individual indexes on most fields), here are practical, targeted optimizations to speed things up:

1. Refine Indexes for Your Exact Query Patterns #

You mentioned individual indexes on fields like leader_id and notifiable_id, but single-column indexes often fall short for multi-condition queries or left joins. Instead, build composite indexes tailored to your two key notification scenarios:

• For leader post notifications (filtering leader_id = ? AND notifiable_id = 0):

  CREATE INDEX idx_leader_zero ON notification_followers(leader_id, notifiable_id);
  

• For user-follow notifications (filtering leader_id = 0 AND notifiable_id = ?):

  CREATE INDEX idx_zero_notifiable ON notification_followers(notifiable_id, leader_id);
  

If your query uses an OR condition (e.g., fetching all notifications for user 14: leader_id = 14 OR (leader_id = 0 AND notifiable_id = 14)), composite indexes still work better than single ones—but read on for an even better fix for OR logic.

2. Replace Slow OR Conditions with UNION ALL #

MySQL often struggles to use indexes efficiently with OR clauses. Instead, split your query into two focused subqueries and combine results with UNION ALL (faster than UNION since it skips deduplication):

-- Get notifications where user 14 is the leader
SELECT nf.*, u.username AS leader_name
FROM notification_followers nf
LEFT JOIN users u ON nf.leader_id = u.id
WHERE nf.leader_id = 14
UNION ALL
-- Get notifications where user 14 was followed
SELECT nf.*, u.username AS follower_name
FROM notification_followers nf
LEFT JOIN users u ON nf.notifiable_id = u.id
WHERE nf.leader_id = 0 AND nf.notifiable_id = 14;

Each subquery will hit the composite indexes we created earlier, drastically reducing the amount of data scanned.

3. Reduce Data Before Joining #

Left joins are slow when you're joining large datasets. Instead, filter the notification_followers table first to get only the records you need, then join to other tables:

SELECT nf.*, COALESCE(u1.username, u2.username) AS related_user
FROM (
    -- Filter first: get latest 20 notifications for user 14
    SELECT id, uuid, leader_id, notifiable_id, created_at
    FROM notification_followers
    WHERE leader_id = 14 OR (leader_id = 0 AND notifiable_id = 14)
    ORDER BY created_at DESC
    LIMIT 20
) nf
LEFT JOIN users u1 ON nf.leader_id = u1.id AND nf.leader_id != 0
LEFT JOIN users u2 ON nf.notifiable_id = u2.id AND nf.leader_id = 0;

This way, you're only joining 20 records instead of potentially thousands. Also, avoid SELECT *—only fetch the fields you need. If your selected fields are all in the composite index, MySQL can use a covering index and skip costly table lookups.

4. Diagnose with EXPLAIN to Fix Bottlenecks #

Before making any changes, run EXPLAIN on your query to see exactly where MySQL is struggling:

EXPLAIN SELECT ...; -- Replace with your slow query

• Look for type: ALL in the output—this means a full table scan, so your indexes aren't being used.
• Check Extra: Using filesort—this means MySQL is sorting data outside memory. Add your ORDER BY field (e.g., created_at) to your composite index to eliminate this.

5. Scale for Large Datasets #

If your notification_followers table has millions of rows, consider these longer-term fixes:

• Archive old data: Move notifications older than a certain time (e.g., 6 months) to an archive table. Keep only recent, active data in the main table.
• Shard the table: Split the table by leader_id or notifiable_id (e.g., one shard for users 1-1000, another for 1001-2000). This reduces the size of each table and makes queries faster.

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