sql优化 - 按标签搜索内容时，如果按照匹配的标签数量进行排序的话，是否使用 limit 消耗的时间差不多，能优化吗？

Question

CREATE TABLE tag(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
description VARCHAR(1000));

CREATE TABLE content(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
description VARCHAR(1000));

CREATE TABLE tag_content_rel(
rel_id INT PRIMARY KEY AUTO_INCREMENT,
tag_id INT NOT NULL,
content_id INT NOT NULL);

CREATE INDEX tag_content_rel_index ON tag_content_rel(tag_id, content_id);
CREATE INDEX tag_index ON tag_content_rel(tag_id);
CREATE INDEX content_index ON tag_content_rel(content_id);

CREATE INDEX content_tag_index ON tag_content_rel(content_id, tag_id);

WITH target_tags AS (
    SELECT tag_id
    FROM tag_content_rel
    WHERE content_id = 7200  -- 替换为你要查询的 content_id
)
SELECT tcr.content_id, COUNT(tcr.tag_id) AS common_tag_count
FROM tag_content_rel tcr
JOIN target_tags tt ON tcr.tag_id = tt.tag_id
WHERE tcr.content_id != 7200  -- 排除自身
GROUP BY tcr.content_id
ORDER BY common_tag_count DESC
LIMIT 100;

tag_content_rel 9000000 多条记录，我自己生成的测试数据。加不加 limit 都是 0.1 秒多。不加 limit 会返回 58819 条记录。我估计这个是优化不了了。因为对这个排序好像需要全读出来才行，但是我问 DeepSeek 它说可以优化，给我弄懵了。

Answer 1

WITH target_tags AS (
    SELECT tag_id FROM tag_content_rel WHERE content_id = 7200
),
candidate_contents AS (
    SELECT content_id 
    FROM tag_content_rel
    WHERE tag_id IN (SELECT tag_id FROM target_tags)
    AND content_id != 7200
    GROUP BY content_id
    HAVING COUNT(DISTINCT tag_id) >= 1  -- 
)
SELECT cc.content_id, COUNT(tcr.tag_id) AS common_tag_count
FROM candidate_contents cc
JOIN tag_content_rel tcr ON cc.content_id = tcr.content_id
JOIN target_tags tt ON tcr.tag_id = tt.tag_id
GROUP BY cc.content_id
ORDER BY common_tag_count DESC
LIMIT 100;

或者：

CREATE INDEX idx_tag_content_combined ON tag_content_rel(tag_id, content_id);