如何优化IMDB数据库中双演员共演影片的SQL查询性能？

问题背景 #

现有查询可返回George Clooney与Brad Pitt共同出演影片的tconst，但关联title_basics表获取影片名称时执行速度极慢。

原查询（仅返回tconst） #

select ti1.tconst
from title_principals as ti1 
join name_basics as nmb1 on ti1.nconst = nmb1.nconst 
where nmb1.primaryName = 'George Clooney'
and ti1.tconst in 
(
  select ti2.tconst
  from title_principals as ti2
  join name_basics as nmb2 on ti2.nconst = nmb2.nconst
  where nmb2.primaryName = 'Brad Pitt'
);

关联title_basics后的慢查询 #

select ti1.tconst, tb.primaryTitle
from title_basics as tb 
join title_principals as ti1 on tb.tconst = ti1.tconst 
join name_basics as nmb1 on ti1.nconst = nmb1.nconst 
where nmb1.primaryName = 'George Clooney' 
and ti1.tconst in 
(
  select ti2.tconst
  from title_principals as ti2
  join name_basics as nmb2 on ti2.nconst = nmb2.nconst
  where nmb2.primaryName = 'Brad Pitt'
);

预期优化器先过滤title_principals与name_basics的关联结果，再关联title_basics，但实际执行计划未按此逻辑执行。已为name_basics.primaryName添加索引，尝试将过滤条件移至JOIN子句，均未解决问题。

相关执行计划与表结构 #

原查询执行计划 #

关联title_basics后的查询执行计划 #

name_basics表结构 #

CREATE TABLE `name_basics` (
  `primaryProfession` text DEFAULT NULL,
  `nconst` int(11) DEFAULT NULL,
  `deathYear` int(11) DEFAULT NULL,
  `knownForTitles` text DEFAULT NULL,
  `ns_soundex` varchar(5) DEFAULT NULL,
  `sn_soundex` varchar(5) DEFAULT NULL,
  `primaryName` text DEFAULT NULL,
  `s_soundex` varchar(5) DEFAULT NULL,
  `birthYear` int(11) DEFAULT NULL,
  KEY `ix_name_basics_birthYear` (`birthYear`),
  KEY `ix_name_basics_sn_soundex` (`sn_soundex`),
  KEY `ix_name_basics_nconst` (`nconst`),
  KEY `ix_name_basics_s_soundex` (`s_soundex`),
  KEY `ix_name_basics_ns_soundex` (`ns_soundex`),
  KEY `ix_name_basics_deathYear` (`deathYear`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

添加primaryName索引后的执行计划 #

+------+--------------+-------------+--------+-------------------------------------------------------+----------------------------+---------+------------------+------+------------------------------------+
| id   | select_type  | table       | type   | possible_keys                                         | key                        | key_len | ref              | rows | Extra                              |
+------+--------------+-------------+--------+-------------------------------------------------------+----------------------------+---------+------------------+------+------------------------------------+
|    1 | PRIMARY      | nmb1        | ref    | ix_name_basics_nconst,idx1                            | idx1                       | 1027    | const            |    1 | Using index condition; Using where |
|    1 | PRIMARY      | ti1         | ref    | ix_title_principals_tconst,ix_title_principals_nconst | ix_title_principals_nconst | 5       | imdb.nmb1.nconst |    4 | Using where                        |
|    1 | PRIMARY      | tb          | ref    | ix_title_basics_tconst                                | ix_title_basics_tconst     | 5       | imdb.ti1.tconst  |    1 |                                    |
|    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key                                          | distinct_key               | 4       | func             |    1 |                                    |
|    2 | MATERIALIZED | nmb2        | ref    | ix_name_basics_nconst,idx1                            | idx1                       | 1027    | const            |    1 | Using index condition; Using where |
|    2 | MATERIALIZED | ti2         | ref    | ix_title_principals_tconst,ix_title_principals_nconst | ix_title_principals_nconst | 5       | imdb.nmb2.nconst |    4 |                                    |
+------+--------------+-------------+--------+-------------------------------------------------------+----------------------------+---------+------------------+------+------------------------------------+

优化方案 #

1. 改写查询逻辑，用JOIN替代IN子查询 #

先获取两位演员的nconst，再通过title_principals关联找到共同出演的影片，最后关联title_basics获取名称，避免子查询的低效执行：

SELECT tp1.tconst, tb.primaryTitle
FROM name_basics nb1
JOIN title_principals tp1 ON nb1.nconst = tp1.nconst
JOIN title_principals tp2 ON tp1.tconst = tp2.tconst
JOIN name_basics nb2 ON tp2.nconst = nb2.nconst
JOIN title_basics tb ON tp1.tconst = tb.tconst
WHERE nb1.primaryName = 'George Clooney'
  AND nb2.primaryName = 'Brad Pitt'
GROUP BY tp1.tconst, tb.primaryTitle;

或者用CTE先筛选两位演员的影片列表再求交集：

WITH clooney_titles AS (
    SELECT tp.tconst
    FROM name_basics nb
    JOIN title_principals tp ON nb.nconst = tp.nconst
    WHERE nb.primaryName = 'George Clooney'
),
pitt_titles AS (
    SELECT tp.tconst
    FROM name_basics nb
    JOIN title_principals tp ON nb.nconst = tp.nconst
    WHERE nb.primaryName = 'Brad Pitt'
)
SELECT ct.tconst, tb.primaryTitle
FROM clooney_titles ct
JOIN pitt_titles pt ON ct.tconst = pt.tconst
JOIN title_basics tb ON ct.tconst = tb.tconst;

2. 优化索引策略 #

• 由于primaryName是text类型，现有索引长度1027字节过长，改为前缀索引减少索引大小，提升效率：

  ALTER TABLE name_basics ADD INDEX idx_primaryName (primaryName(50));
  

• 为title_principals添加联合索引(nconst, tconst)，通过演员ID找影片时无需回表：

  ALTER TABLE title_principals ADD INDEX idx_nconst_tconst (nconst, tconst);
  

• 确保title_basics.tconst是主键或唯一索引，加快关联速度。

3. 强制优化器执行顺序 #

使用STRAIGHT_JOIN强制优化器按指定表顺序执行，先过滤演员，再关联影片表，最后关联title_basics：

SELECT STRAIGHT_JOIN ti1.tconst, tb.primaryTitle
FROM name_basics as nmb1
JOIN title_principals as ti1 ON nmb1.nconst = ti1.nconst
JOIN title_basics as tb ON ti1.tconst = tb.tconst
WHERE nmb1.primaryName = 'George Clooney' 
AND ti1.tconst IN (
    SELECT ti2.tconst
    FROM name_basics as nmb2
    JOIN title_principals as ti2 ON nmb2.nconst = ti2.nconst
    WHERE nmb2.primaryName = 'Brad Pitt'
);

4. 预查询演员ID #

先查询出两位演员的nconst，再代入主查询，减少重复的名称过滤逻辑：

-- 先获取演员ID
SELECT nconst INTO @clooney_id FROM name_basics WHERE primaryName = 'George Clooney';
SELECT nconst INTO @pitt_id FROM name_basics WHERE primaryName = 'Brad Pitt';

-- 主查询
SELECT tp1.tconst, tb.primaryTitle
FROM title_principals tp1
JOIN title_principals tp2 ON tp1.tconst = tp2.tconst
JOIN title_basics tb ON tp1.tconst = tb.tconst
WHERE tp1.nconst = @clooney_id AND tp2.nconst = @pitt_id;

内容的提问来源于stack exchange，提问作者Васисулий Пупкин