MySQL连接查询时Partition Pruning（分区裁剪）失效问题咨询

根因分析 #

• 分区裁剪的核心逻辑是SQL执行前就可以确定分区字段的过滤范围，你当前的查询没有给多对多关联表posts_tags_user_mtom加固定过滤条件，MySQL优化器无法提前预知该表中tag_id的取值范围，自然无法预先排除posts_main表的无关分区，只能扫描全部分区后再做连接匹配。
• 低于8.0的MySQL版本本身不支持连接场景下的动态分区裁剪，即使关联表的数据量很小，优化器也不会主动下推关联字段的取值范围到分区表的过滤逻辑中。

修复方案 #

• 方案一：分两步查询，先单独拿到关联表的所有tag_id，再将tag_id集合作为常量过滤条件传给分区表查询，此时可以正常触发分区裁剪，示例代码如下：

-- 第一步：获取关联表中所有去重的tag_id
SELECT GROUP_CONCAT(DISTINCT tag_id) INTO @valid_tag_ids FROM posts_tags_user_mtom;
-- 第二步：带固定过滤条件查询分区表
EXPLAIN PARTITIONS SELECT
posts.post_id,
posts.owner_id
FROM `posts_main` AS posts
WHERE posts.tag_id IN (@valid_tag_ids)
ORDER BY posts.post_id DESC
LIMIT 50;

• 方案二：如果使用MySQL8.0及以上版本，开启优化器的派生条件下推能力，用子查询先拿到去重的tag_id再做关联，优化器可以自动下推tag_id范围触发裁剪，示例代码如下：

EXPLAIN PARTITIONS SELECT
posts.post_id,
posts.owner_id
FROM `posts_main` AS posts
JOIN (SELECT DISTINCT tag_id FROM `posts_tags_user_mtom`) AS mtom
ON posts.tag_id = mtom.tag_id
ORDER BY posts.post_id DESC
LIMIT 50;

• 方案三：如果业务场景允许，给关联表增加固定过滤条件缩小tag_id范围，比如查询指定用户的关联内容时，加上固定的用户ID过滤，优化器可以提前匹配到对应tag_id的范围触发裁剪，示例代码如下：

EXPLAIN PARTITIONS SELECT
posts.post_id,
posts.owner_id
FROM `posts_main` AS posts
JOIN `posts_tags_user_mtom` AS mtom
ON posts.tag_id = mtom.tag_id
WHERE mtom.user_id = 10086 -- 替换为实际的用户过滤条件
ORDER BY posts.post_id DESC
LIMIT 50;

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