SQL

DROP DATABASE tag_test;
CREATE DATABASE tag_test;
USE tag_test;

CREATE TABLE tag(
id INT 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(
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
WHERE tag_id IN (
730,2621,2805,3200,3340,3590,3969,4039,4799,5249,8859,11894,12628,12646,16959,17024,17142,18032,18861,19316,20839,21179,22346,22507,22522,22639,23562,23822,25172,25786,25821,26606,29899,29917,30586,30901,31216,31413,32562,32567,34740,36586,36954,38109,39202,40519,40756,40816,41464,42942,43069,43286,43344,44787,44950,45549,45652,46313,47111,50549,51942,52738,52959,52961,54034,55526,59162,59767,59945,60361,60816,61307,61730,62269,62503,62589,63960,64580,64634,64794,65209,66332,68222,69396,69905,70629,70939,71277,71804,72580,72896,73651,74301,74525,74706,75153,76169,76500,78042,78148,79109,81463,82140,84217,85212,85327,85584,86392,86908,88188,88475,89175,89190,91156,93202,94124,95294,95345,96013,98135,99679
)
GROUP BY content_id
ORDER BY COUNT(*) DESC;

/*
EXPLAIN 结果：
"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "tag_content_rel"   "index" "tag_content_rel_index" "tag_content_rel_index" "8" \N  "1" "Using where; Using index; Using temporary; Using filesort"
*/