MySQL WHERE NOT IN查询性能优化：排除含NULL字段的分组数据

筛选出所有组成单词均存在于requested_words表中的search_phrases记录，即排除存在单词不在requested_words中的短语分组（对应关联后rw.word为NULL的情况）。

表结构 #

CREATE TABLE `search_phrases` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `phrase` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `frequency` bigint NOT NULL,
  PRIMARY KEY (`id`),
  KEY `search_phrases_phrase_index` (`phrase`),
  KEY `search_phrases_frequency_index` (`frequency`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `search_words` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `word` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `search_words_word_unique` (`word`)
) ENGINE=InnoDB AUTO_INCREMENT=128287 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `search_combinable` (
  `search_phrase_id` int unsigned NOT NULL,
  `search_word_id` int unsigned NOT NULL,
  KEY `search_combinable_search_phrase_id_foreign` (`search_phrase_id`),
  KEY `search_combinable_search_word_id_foreign` (`search_word_id`),
  CONSTRAINT `search_combinable_search_phrase_id_foreign` FOREIGN KEY (`search_phrase_id`) REFERENCES `search_phrases` (`id`),
  CONSTRAINT `search_combinable_search_word_id_foreign` FOREIGN KEY (`search_word_id`) REFERENCES `search_words` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `requested_words` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `word` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ts` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `requested_words_word_index` (`word`)
) ENGINE=InnoDB AUTO_INCREMENT=436 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* 样本数据 */
INSERT INTO search_phrases VALUES (1, 'men''s shorts', 1031588), (2, 'red shorts', 456000), (3, 'green shorts', 436000);
INSERT INTO search_words VALUES (1, 'men''s'), (2, 'shorts'), (3, 'red'), (4, 'green');
INSERT INTO search_combinable VALUES (1, 1), (1, 2), (2, 3), (2, 2), (3, 4), (3, 2);
INSERT INTO requested_words VALUES (1, 'men''s', '2023-03-01 07:45:49'), (2, 'shorts', '2023-03-01 07:45:49'), (3, 'red', '2023-03-01 07:45:49');

原查询语句 #

select sp.id, sp.phrase, sp.frequency from search_phrases as sp
inner join search_combinable as sc on sc.search_phrase_id = sp.id
inner join search_words as sw on sw.id = sc.search_word_id
inner join requested_words as rw on rw.word = sw.word
where sp.id not in (
    select sp.id from search_phrases as temp_sp
    inner join search_combinable as sc on sc.search_phrase_id = temp_sp.id
    inner join search_words as sw on sw.id = sc.search_word_id
    left join requested_words as rw on rw.word = sw.word
    where rw.word is null and temp_sp.id = sp.id
)
group by sp.id
order by sp.frequency desc

问题现状 #

该查询执行时间约29秒，且耗时随requested_words表数据量增长而增加。执行计划如下：

期望结果 #

返回短语：men's shorts 和 red shorts

后续尝试 #

为search_combinable表添加联合主键后，改用NOT EXISTS查询：

修改后的search_combinable表结构 #

CREATE TABLE `search_combinable` (
  `search_phrase_id` int unsigned NOT NULL,
  `search_word_id` int unsigned NOT NULL,
  PRIMARY KEY (`search_word_id`,`search_phrase_id`),
  KEY `search_combinable_search_word_id_search_phrase_id_index` (`search_word_id`,`search_phrase_id`),
  KEY `search_combinable_search_phrase_id_foreign` (`search_phrase_id`),
  CONSTRAINT `search_combinable_search_phrase_id_foreign` FOREIGN KEY (`search_phrase_id`) REFERENCES `search_phrases` (`id`),
  CONSTRAINT `search_combinable_search_word_id_foreign` FOREIGN KEY (`search_word_id`) REFERENCES `search_words` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

优化后的查询语句 #

select sp.id, sp.phrase, sp.frequency
from search_phrases as sp
where not exists (
    select 1
    from search_phrases sp2
    left join search_combinable as sc on sc.search_phrase_id = sp2.id
    left join search_words as sw on sw.id = sc.search_word_id
    left join requested_words as rw on rw.word = sw.word
    where sp2.id = sp.id
    and rw.word is null
)
order by sp.frequency desc;

新执行计划 #

性能优化方案 #

1. 重构查询逻辑：统计匹配数简化判断 #

原查询的嵌套关联逻辑可以替换为分组统计的方式，直接对比每个短语的总单词数和匹配requested_words的单词数，仅保留两者相等的短语：

SELECT sp.id, sp.phrase, sp.frequency
FROM search_phrases sp
JOIN (
    SELECT sc.search_phrase_id,
           COUNT(*) AS total_words,
           COUNT(rw.word) AS matched_words
    FROM search_combinable sc
    JOIN search_words sw ON sc.search_word_id = sw.id
    LEFT JOIN requested_words rw ON sw.word = rw.word
    GROUP BY sc.search_phrase_id
    HAVING total_words = matched_words
) valid_phrases ON sp.id = valid_phrases.search_phrase_id
ORDER BY sp.frequency DESC;

这种方式避免了重复关联表，减少了查询的复杂度。

2. 优化索引减少回表和排序开销 #

• 为search_combinable添加反向联合索引(search_phrase_id, search_word_id)，让分组统计可以直接利用索引完成，无需回表：

  ALTER TABLE search_combinable ADD INDEX idx_phrase_word (search_phrase_id, search_word_id);
  

• 将requested_words的word索引改为唯一索引，提升单词匹配的查找效率：

  ALTER TABLE requested_words DROP INDEX requested_words_word_index;
  ALTER TABLE requested_words ADD UNIQUE INDEX idx_requested_word_unique (word);
  

• 为search_phrases添加覆盖索引，包含排序字段和查询字段，让排序和数据获取直接从索引完成：

  ALTER TABLE search_phrases ADD INDEX idx_phrase_freq (frequency DESC, id, phrase);
  

3. 简化NOT EXISTS子查询 #

去掉子查询中冗余的search_phrases sp2关联，直接通过search_combinable关联判断：

SELECT sp.id, sp.phrase, sp.frequency
FROM search_phrases sp
WHERE NOT EXISTS (
    SELECT 1
    FROM search_combinable sc
    JOIN search_words sw ON sc.search_word_id = sw.id
    LEFT JOIN requested_words rw ON sw.word = rw.word
    WHERE sc.search_phrase_id = sp.id
      AND rw.word IS NULL
)
ORDER BY sp.frequency DESC;

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