如何高效编写匹配多子类别的MySQL集合查询语句？

现有表结构 #

当前使用的两个简化数据表定义如下：

books表 #

CREATE TABLE books (
    book_id VARCHAR(25) NOT NULL,
    subgenre_1 VARCHAR(6) NOT NULL,
    subgenre_2 VARCHAR(6),
    subgenre_3 VARCHAR(6),
    mood_1 VARCHAR(4) NOT NULL,
    mood_2 VARCHAR(4),
    mood_3 VARCHAR(4),  
    PRIMARY KEY (book_id)
);

collections表 #

CREATE TABLE collections (
    collection_id VARCHAR(25) NOT NULL,
    subgenre_1 VARCHAR(6) NOT NULL,
    subgenre_2 VARCHAR(6),
    subgenre_3 VARCHAR(6),
    mood_1 VARCHAR(4) NOT NULL,
    mood_2 VARCHAR(4),
    mood_3 VARCHAR(4),
    PRIMARY KEY (collection_id)
);

需求与现有查询 #

需求是展示与指定书籍匹配的集合，现有查询语句（结合PHP变量）如下：

$bk_sg1 = "FICT";
$bk_sg2 = "HIST";
$bk_sg3 = "JUVE";
$bk_md1 = "ROMA";
$bk_md2 = "TENS";
$bk_md3 = "EMOT";

SELECT collection_id FROM collections
WHERE (subgenre_1 = $bk_sg1 OR subgenre_2 = $bk_sg1 OR subgenre_3 = $bk_sg1) OR
    (subgenre_1 = $bk_sg2 OR subgenre_2 = $bk_sg2 OR subgenre_3 = $bk_sg2) OR
    (subgenre_1 = $bk_sg3 OR subgenre_2 = $bk_sg3 OR subgenre_3 = $bk_sg3) OR
    (mood_1 = $bk_md1 OR mood_2 = $bk_md1 OR mood_3 = $bk_md1) OR
    (mood_1 = $bk_md2 OR mood_2 = $bk_md2 OR mood_3 = $bk_md2) OR
    (mood_1 = $bk_md3 OR mood_2 = $bk_md3 OR mood_3 = $bk_md3)

当collections表数据量增大后，上述查询会因大量OR条件和反范式结构导致性能下降，以下是几种优化方案：

优化方案 #

1. 范式化表结构（长期最优方案） #

当前表结构采用多字段存储同类型数据（subgenre_1/2/3、mood_1/2/3），属于反范式设计，大数据量下查询效率极低。建议拆分为关联表，将子类型和情绪字段单独存储：

创建关联表

-- 集合子类型关联表
CREATE TABLE collections_subgenres (
    collection_id VARCHAR(25) NOT NULL,
    subgenre VARCHAR(6) NOT NULL,
    PRIMARY KEY (collection_id, subgenre),
    FOREIGN KEY (collection_id) REFERENCES collections(collection_id)
);

-- 集合情绪关联表
CREATE TABLE collections_moods (
    collection_id VARCHAR(25) NOT NULL,
    mood VARCHAR(4) NOT NULL,
    PRIMARY KEY (collection_id, mood),
    FOREIGN KEY (collection_id) REFERENCES collections(collection_id)
);

优化后的查询语句

通过UNION合并两个关联表的查询结果（自动去重），效率远高于原查询：

SELECT collection_id FROM collections_subgenres 
WHERE subgenre IN ('FICT', 'HIST', 'JUVE')
UNION
SELECT collection_id FROM collections_moods 
WHERE mood IN ('ROMA', 'TENS', 'EMOT');

该方案的优势在于：

• 关联表的主键索引可被高效利用，查询速度随数据量增长的衰减远低于原结构
• 支持扩展更多子类型/情绪（无需修改主表结构）
• 查询逻辑更简洁，维护成本低

2. 不修改表结构的临时优化 #

若无法重构表结构，可先简化查询语句并添加针对性索引：

简化查询语句

用IN子句替代重复的OR条件，逻辑等价且可读性更强：

SELECT collection_id FROM collections
WHERE subgenre_1 IN ('FICT', 'HIST', 'JUVE')
   OR subgenre_2 IN ('FICT', 'HIST', 'JUVE')
   OR subgenre_3 IN ('FICT', 'HIST', 'JUVE')
   OR mood_1 IN ('ROMA', 'TENS', 'EMOT')
   OR mood_2 IN ('ROMA', 'TENS', 'EMOT')
   OR mood_3 IN ('ROMA', 'TENS', 'EMOT');

添加索引

创建覆盖子类型和情绪字段的复合索引，帮助数据库快速定位匹配数据：

CREATE INDEX idx_collections_subgenres ON collections(subgenre_1, subgenre_2, subgenre_3);
CREATE INDEX idx_collections_moods ON collections(mood_1, mood_2, mood_3);

注意：OR条件会导致索引的利用率受限，该方案仅能在一定程度上提升性能，无法从根本上解决反范式结构的瓶颈。

3. 全文索引方案（可选） #

若数据库支持全文索引（如MySQL），可将所有subgenre和mood字段合并为一个文本字段，创建全文索引后进行匹配：

-- 先添加合并字段（或使用虚拟字段）
ALTER TABLE collections ADD COLUMN tags TEXT;
-- 更新tags字段，拼接所有子类型和情绪值
UPDATE collections SET tags = CONCAT(subgenre_1, ' ', COALESCE(subgenre_2,''), ' ', COALESCE(subgenre_3,''), ' ', mood_1, ' ', COALESCE(mood_2,''), ' ', COALESCE(mood_3,''));
-- 创建全文索引
CREATE FULLTEXT INDEX idx_collections_tags ON collections(tags);

查询语句

SELECT collection_id FROM collections
WHERE MATCH(tags) AGAINST('FICT HIST JUVE ROMA TENS EMOT' IN BOOLEAN MODE);

该方案适合快速实现，但匹配精度和性能略逊于范式化方案，且需要维护合并字段的同步更新。

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