SQL

DROP DATABASE tag_test;
CREATE DATABASE tag_test;
USE tag_test;

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);

DELIMITER $$
CREATE PROCEDURE `add_tag`(IN n INT)
BEGIN
    DECLARE i INT UNSIGNED DEFAULT 0;
    WHILE i < n DO
        INSERT INTO `tag`()
        VALUES
        -- 一次插入多行可以显著加快插入速度。
        (),(),(),(),(),(),(),(),(),(),
        (),(),(),(),(),(),(),(),(),(),
        (),(),(),(),(),(),(),(),(),(),
        (),(),(),(),(),(),(),(),(),(),
        (),(),(),(),(),(),(),(),(),(),
        (),(),(),(),(),(),(),(),(),(),
        (),(),(),(),(),(),(),(),(),(),
        (),(),(),(),(),(),(),(),(),(),
        (),(),(),(),(),(),(),(),(),(),
        (),(),(),(),(),(),(),(),(),();
        SET i = i + 1;
    END WHILE;
END $$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `add_content`(IN n INT)
BEGIN
    DECLARE i INT UNSIGNED DEFAULT 0;
    WHILE i < n DO
        INSERT INTO `content`()
        VALUES
        (),(),(),(),(),(),(),(),(),(),
        (),(),(),(),(),(),(),(),(),(),
        (),(),(),(),(),(),(),(),(),(),
        (),(),(),(),(),(),(),(),(),(),
        (),(),(),(),(),(),(),(),(),(),
        (),(),(),(),(),(),(),(),(),(),
        (),(),(),(),(),(),(),(),(),(),
        (),(),(),(),(),(),(),(),(),(),
        (),(),(),(),(),(),(),(),(),(),
        (),(),(),(),(),(),(),(),(),();
        SET i = i + 1;
    END WHILE;
END $$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `add_tag_content_rel`(IN n INT, IN tag_count INT, IN content_count INT)
BEGIN
    DECLARE i INT UNSIGNED DEFAULT 0;
    WHILE i < n DO
        INSERT INTO `tag_content_rel`(tag_id, content_id)
        VALUES
        -- 为了不学 MySQL 的过程的语法所以才复制粘贴这么多次。
        (FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),
        (FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),
        (FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),
        (FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),
        (FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),
        (FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),
        (FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),
        (FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),
        (FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),
        (FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count)),(FLOOR(RAND()*tag_count), FLOOR(RAND()*content_count));
        SET i = i + 1;
    END WHILE;
END $$
DELIMITER ;

CALL add_tag(1000);
CALL add_content(1000);
CALL add_tag_content_rel(100000, 100000, 100000);

-- 耗时 00:04:40.9

-- 使用下面的语句获取某个 content 的所有 tag：
USE tag_test;

SELECT *
FROM tag_content_rel
WHERE content_id = 50000;

-- 然后使用上面那条语句获取的 tag_id 修改下面的语句：

USE tag_test;

SELECT content_id, COUNT(*)
FROM tag_content_rel
USE INDEX(tag_content_rel_index)
WHERE tag_id IN (
74097,36293,32597,45665,69924,42073,3844,52738,97224,31397,5446,50339,7489,4153,10688,18796,61802,61934,64280,48422,67511,34606,74402,14939,56910,23882,63452,5273,72271,25664,32101,98346,76611,32674,68923,66303,75038,18239,53693,52185,92531,90051,65825,63719,51141,78243,91453,87551,36030,10445,67367,71868,90933,45010,90762,94204,47964,54863,85144,47911,97501,27354,84274,26607,17689,47092,8397,48070,54266,40221,51098,25623,45015,57871,90732,52866,13103,70145,83931,76962,14663,54249,71576,73313,55536,21321,89844,80231,92971,18815,26451,19336,59254,57524,35275,40903,86684,72234,8368,77221,5672,62121,58058,63069
)
GROUP BY content_id
ORDER BY COUNT(*) DESC;