求WordPress插件中按交集率排序获取关联标签的MySQL优化方案

原代码核心问题验证 #

你的代码能正确获取与目标标签共存的文章数（rel_count），但排序逻辑完全不符合需求：

• 原查询用ORDER BY rel_count/related_rel.object_id DESC，其中related_rel.object_id是单篇文章的ID，不是关联标签的总文章数，和你定义的INTERSECT_RATE = 交集文章数 / 关联标签总文章数公式完全不符。
• 冗余JOIN：无需关联base（terms表），通过base_tax.term_id即可定位目标标签，多此一举会增加查询开销。
• GROUP BY字段不严谨：SELECT的是related.term_id，但GROUP BY用related_tax.term_id，虽逻辑上等价，但保持字段一致更规范。

性能优化建议 #

1. 减少冗余JOIN：移除不必要的base表关联，直接通过base_tax.term_id匹配目标标签。
2. 优化子查询效率：用一次性子查询预计算所有关联标签的总文章数，避免重复执行子查询。
3. 利用索引：确保以下字段有索引（WordPress默认已创建，但可确认）：
   • term_taxonomy(taxonomy, term_id)
   • term_relationships(term_taxonomy_id, object_id)
   • posts(ID, post_type, post_status)
4. 增强缓存策略：给缓存添加过期时间，避免长期缓存 stale 数据，比如设置1小时缓存。

相关性优化建议 #

1. 正确计算交集率：严格按照你定义的公式计算，同时处理除数为0的情况（避免报错）。
2. 排除无效标签：过滤掉总文章数为0的关联标签，避免无意义的计算。
3. 去重计数：用COUNT(DISTINCT posts.ID)替代COUNT(*)，避免同一文章被重复统计（比如一篇文章同时属于多个关联标签的情况）。

优化后的完整代码 #

function get_related_terms($args = []) {
    $defaults = [
        'term_id'         => 0,
        'taxonomy'        => '',
        'rel_taxonomy'    => '',
        'post_type'       => 'post',
        'number'          => 20,
        'cache_expire'    => 3600 // 缓存1小时
    ];
    $args = wp_parse_args($args, $defaults);

    // 校验必填参数
    if (empty($args['term_id']) || empty($args['taxonomy']) || empty($args['rel_taxonomy'])) {
        return [];
    }

    global $wpdb;
    $cache_key = sprintf('%s:%s:%d:%s', $args['taxonomy'], $args['rel_taxonomy'], $args['term_id'], $args['post_type']);

    // 优先读取缓存
    if ($terms = wp_cache_get($cache_key, 'related_terms')) {
        return $terms;
    }

    $query = $wpdb->prepare(
        "SELECT 
            related.term_id,
            COUNT(DISTINCT posts.ID) AS intersect_count,
            COALESCE(total.total_count, 0) AS total_count,
            CASE 
                WHEN COALESCE(total.total_count, 0) = 0 THEN 0 
                ELSE COUNT(DISTINCT posts.ID)/total.total_count 
            END AS intersect_rate
        FROM
            {$wpdb->prefix}terms related
            JOIN {$wpdb->prefix}term_taxonomy related_tax ON related.term_id = related_tax.term_id
            JOIN {$wpdb->prefix}term_relationships related_rel ON related_tax.term_taxonomy_id = related_rel.term_taxonomy_id
            JOIN {$wpdb->prefix}posts posts ON related_rel.object_id = posts.ID
            JOIN {$wpdb->prefix}term_relationships base_rel ON posts.ID = base_rel.object_id
            JOIN {$wpdb->prefix}term_taxonomy base_tax ON base_rel.term_taxonomy_id = base_tax.term_taxonomy_id
            LEFT JOIN (
                SELECT tt.term_id, COUNT(DISTINCT tr.object_id) AS total_count
                FROM {$wpdb->prefix}term_taxonomy tt
                JOIN {$wpdb->prefix}term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
                JOIN {$wpdb->prefix}posts p ON tr.object_id = p.ID
                WHERE tt.taxonomy = %s 
                  AND p.post_type = %s 
                  AND p.post_status = 'publish'
                GROUP BY tt.term_id
            ) AS total ON related.term_id = total.term_id
        WHERE
            related_tax.taxonomy = %s
            AND base_tax.taxonomy = %s
            AND base_tax.term_id = %d
            AND posts.post_type = %s
            AND posts.post_status = 'publish'
            AND related.term_id != %d
        GROUP BY related.term_id
        HAVING total_count > 0
        ORDER BY intersect_rate DESC
        LIMIT 0, %d",
        $args['rel_taxonomy'],
        $args['post_type'],
        $args['rel_taxonomy'],
        $args['taxonomy'],
        $args['term_id'],
        $args['post_type'],
        $args['term_id'],
        $args['number']
    );

    $results = $wpdb->get_results($query);
    $terms = [];

    foreach ($results as $result) {
        $term = get_term((int)$result->term_id, $args['rel_taxonomy']);
        if (!is_wp_error($term)) {
            $term->intersect_count = (int)$result->intersect_count;
            $term->total_count = (int)$result->total_count;
            $term->intersect_rate = (float)$result->intersect_rate;
            $terms[] = $term;
        }
    }

    // 设置缓存
    wp_cache_set($cache_key, $terms, 'related_terms', $args['cache_expire']);

    return $terms;
}

代码说明 #

• 新增参数校验，避免无效调用。
• 用wp_parse_args处理默认参数，提升代码健壮性。
• 正确计算交集率，并用COALESCE和CASE处理除数为0的边界情况。
• 用COUNT(DISTINCT)确保统计的文章数不重复。
• 预计算关联标签的总文章数，减少查询开销。
• 缓存添加过期时间，自动刷新数据。

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