mysql - 为什么一个查询比另一个查询快？

Question

这是一个查询:

SELECT DISTINCT
    spentits.*,
    username,
    (SELECT count(*) from likes where likes.spentit_id = spentits.id) as like_count,
    (SELECT count(*) from comments where comments.spentit_id = spentits.id) as comment_count,
    (SELECT count(*) from wishlist_items where wishlist_items.spentit_id = spentits.id) as wishlist_count,
    (case when likes.id is null then 0 else 1 end) as is_liked_by_me,
    (case when wishlist_items.id is null then 0 else 1 end) as is_wishlisted_by_me
FROM spentits
LEFT JOIN users ON users.id = spentits.user_id
LEFT JOIN likes ON likes.user_id = 9 AND likes.spentit_id = spentits.id
LEFT JOIN wishlist_items ON wishlist_items.user_id = 9 AND wishlist_items.spentit_id = spentits.id
WHERE spentits.user_id IN
    (SELECT follows.following_id
     FROM follows
     WHERE follows.follower_id = 9)
ORDER BY id DESC 
LIMIT 15;

这平均需要 43ms 来执行。现在另一个查询(如下)，没有 where 子句，更不用说第二个 SELECT 子查询的执行速度慢了 5 倍(240ms)!

SELECT DISTINCT
    spentits.*,
    username,
    (SELECT count(*) from likes where likes.spentit_id = spentits.id) as like_count,
    (SELECT count(*) from comments where comments.spentit_id = spentits.id) as comment_count,
    (SELECT count(*) from wishlist_items where wishlist_items.spentit_id = spentits.id) as wishlist_count,
    (case when likes.id is null then 0 else 1 end) as is_liked_by_me,
    (case when wishlist_items.id is null then 0 else 1 end) as is_wishlisted_by_me
FROM spentits
LEFT JOIN users ON users.id = spentits.user_id
LEFT JOIN likes ON likes.user_id = 9 AND likes.spentit_id = spentits.id
LEFT JOIN wishlist_items ON wishlist_items.user_id = 9 AND wishlist_items.spentit_id = spentits.id
ORDER BY id DESC 
LIMIT 15;

为什么？第二个查询不应该快得多吗，因为没有条件，没有第二个查询。数据库需要做的就是选择最后 15 条记录，与第一条记录进行比较，在执行子查询后需要扫描每条记录并检查其中是否包含 id。我只是真的很困惑，一个应该执行得更快的查询实际上执行速度慢了 5 倍。

Answer 1

存在与统计信息和元数据相关的执行路径，为两者制定一个解释计划并查看执行路径。

此外，当您不按 ID 筛选时，按 ID 筛选可能会跳过没有匹配行的表。