MySQL 8.0查询执行计划为何按mp.id排序？

执行的SQL查询 #

SELECT mp.id
FROM meme_post mp
JOIN meme_post_tag mpt ON mp.id = mpt.meme_post_id
WHERE deleted_at IS NULL AND mp.media_type = 'STATIC' AND mpt.tag_id IN (11, 30, 24) -- Selected tag IDs
GROUP BY mp.id
HAVING COUNT(DISTINCT mpt.tag_id) = 3 
ORDER BY mp.created_at DESC
LIMIT 21 OFFSET 0; 

EXPLAIN输出 #

1   SIMPLE  mp      ref PRIMARY,FK_meme_post_user,idx_deleted_at_created_at idx_deleted_at_created_at   9   const   166682  50.00   Using index condition; Using where; Using temporary; Using filesort
1   SIMPLE  mpt     ref idx_meme_post_tag_post_tag,FK_meme_post_tag_tag,FK_meme_post_tag_post_id    idx_meme_post_tag_post_tag  8   findmymeme_db.mp.id 2   75.35   Using where; Using index

执行计划详情 #

-> Limit: 21 row(s)  (actual time=1571..1571 rows=21 loops=1)
    -> Sort: mp.created_at DESC  (actual time=1571..1571 rows=21 loops=1)
        -> Filter: (`count(distinct mpt.tag_id)` = 3)  (actual time=802..1566 rows=8238 loops=1)
            -> Stream results  (cost=182898 rows=333364) (actual time=802..1556 rows=219023 loops=1)
                -> Group aggregate: count(distinct mpt.tag_id)  (cost=182898 rows=333364) (actual time=802..1411 rows=219023 loops=1)
                    -> Nested loop inner join  (cost=145301 rows=375967) (actual time=802..1343 rows=322453 loops=1)
                        -> Sort: mp.id  (cost=11577 rows=166682) (actual time=802..832 rows=249318 loops=1)
                            -> Filter: (mp.media_type = 'STATIC')  (cost=11577 rows=166682) (actual time=0.206..642 rows=249318 loops=1)
                                -> Index lookup on mp using idx_deleted_at_created_at (deleted_at=NULL), with index condition: (mp.deleted_at IS NULL)  (cost=11577 rows=166682) (actual time=0.204..615 rows=332220 loops=1)
                        -> Filter: (mpt.tag_id IN (11,30,24))  (cost=1.01 rows=2.26) (actual time=0.0015..0.00191 rows=1.29 loops=249318)
                            -> Covering index lookup on mpt using idx_meme_post_tag_post_tag (meme_post_id=mp.id)  (cost=1.01 rows=2.99) (actual time=0.00129..0.00169 rows=3 loops=249318)

补充信息 #

• 索引idx_meme_post_tag_post_tag是(meme_post_id, tag_id)上的唯一索引。

问题解答 #

你的假设完全正确——这个针对mp.id的排序和GROUP BY无关（MySQL 8.0确实已移除GROUP BY的隐式排序行为），它是MySQL为优化嵌套循环连接效率而主动选择的操作。

具体逻辑：

• mpt表的idx_meme_post_tag_post_tag索引按meme_post_id有序存储，当驱动表mp的连接字段id有序时，访问mpt可以顺着索引顺序读取数据，避免随机IO，改用更高效的顺序IO。
• MySQL先对mp.id排序，让驱动表的连接字段保持有序，这样在嵌套循环连接时，遍历mpt的索引能减少磁盘寻道开销，大幅提升连接阶段的性能。
• 从执行计划也能验证这一点：排序步骤出现在嵌套循环连接的驱动表处理阶段，早于GROUP BY的聚合操作，说明这是连接优化的环节，和GROUP BY的逻辑无关。

内容的提问来源于stack exchange，提问作者안채연