ORDER BY id DESC结合NOT EXISTS的慢查询优化咨询

1. 调整blockings表的索引 #

原查询中NOT EXISTS里的OR逻辑（匹配当前用户被ID=1拉黑，或当前用户拉黑了ID=1），单独的user_id/blocked_id单键索引无法高效处理这种分支判断。建议创建两个复合索引：

• blockings(user_id, blocked_id)：快速匹配「用户ID=当前用户ID且被拉黑ID=1」的记录
• blockings(blocked_id, user_id)：快速匹配「被拉黑ID=当前用户ID且用户ID=1」的记录

这两个索引能让数据库分别高效定位两种拉黑关系的记录，避免OR条件导致的索引失效。

2. 拆分NOT EXISTS的OR逻辑 #

把原查询中的OR条件拆成两个独立的NOT EXISTS，让数据库能分别利用上面的复合索引，进一步提升查询效率：

SELECT
    *
FROM
    `users`
WHERE
    `id` != 1
    AND `users`.`activated` = 1
    AND NOT EXISTS (
        SELECT 1
        FROM `blockings`
        WHERE blockings.user_id = users.id AND blockings.blocked_id = 1
    )
    AND NOT EXISTS (
        SELECT 1
        FROM `blockings`
        WHERE blockings.blocked_id = users.id AND blockings.user_id = 1
    )
ORDER BY
    users.id DESC
LIMIT 10 OFFSET 0

3. 缩小前置查询范围 #

因为最终只需要10条数据，可以先通过users表的索引快速筛选出候选用户，再过滤拉黑关系，减少关联blockings表的次数：

SELECT u.*
FROM (
    SELECT id
    FROM `users`
    WHERE `id` != 1 AND `activated` = 1
    ORDER BY id DESC
    LIMIT 20 -- 取略多于需求的数量，避免过滤后不足10条
) AS temp
JOIN `users` u ON temp.id = u.id
WHERE
    NOT EXISTS (
        SELECT 1
        FROM `blockings`
        WHERE blockings.user_id = u.id AND blockings.blocked_id = 1
    )
    AND NOT EXISTS (
        SELECT 1
        FROM `blockings`
        WHERE blockings.blocked_id = u.id AND blockings.user_id = 1
    )
ORDER BY u.id DESC
LIMIT 10 OFFSET 0

如果activated字段的筛选性较高，还可以给users表添加复合索引users(activated, id)，让子查询能直接通过索引定位候选ID，无需回表扫描。

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