优化含两次同表LEFT JOIN的MySQL查询

我有如下SQL查询语句：

SELECT 
    match_main.checked,
    match_main.match_main_id,
    match_main.updated,
    match_main.created
FROM
    match_main
        LEFT JOIN
    match_team AS mt1 ON mt1.match_main_id = match_main.match_main_id
        AND mt1.team_number = 1
        AND mt1.version_number = 0
        LEFT JOIN
    match_team AS mt2 ON mt2.match_main_id = match_main.match_main_id
        AND mt2.team_number = 2
        AND mt2.version_number = 0
WHERE
    mt1.team_id = 557949
        OR mt2.team_id = 557949

表结构说明 #

• match_main：体育赛事主表，约500万条记录
• match_team：赛事参赛队表，约1000万条记录

性能问题 #

上述查询仅返回约1700条数据，但耗时超过5分钟，我无法自行优化该查询。

EXPLAIN 查询输出 #

+----+-------------+------------+------------+--------+---------------------------------------------------------------------------------------------------------------------+------------------------------------------+---------+------------------------------------------+---------+----------+-------------+
| id | select_type |   table    | partitions |  type  |                                                    possible_keys                                                    |                   key                    | key_len |                   ref                    |  rows   | filtered |    Extra    |
+----+-------------+------------+------------+--------+---------------------------------------------------------------------------------------------------------------------+------------------------------------------+---------+------------------------------------------+---------+----------+-------------+
|  1 | SIMPLE      | match_main | NULL       | ALL    | NULL                                                                                                                | NULL                                     | NULL    | NULL                                     | 4756279 |      100 | NULL        |
|  1 | SIMPLE      | mt1        | NULL       | eq_ref | uq__match_team__match__team_id__version,uq__match_team__match__team_num__version,ix__match_team__match_main_id,comp | uq__match_team__match__team_num__version | 6       | itf.match_main.match_main_id,const,const |       1 |      100 | NULL        |
|  1 | SIMPLE      | mt2        | NULL       | eq_ref | uq__match_team__match__team_id__version,uq__match_team__match__team_num__version,ix__match_team__match_main_id,comp | uq__match_team__match__team_num__version | 6       | itf.match_main.match_main_id,const,const |       1 |      100 | Using where |
+----+-------------+------------+------------+--------+---------------------------------------------------------------------------------------------------------------------+------------------------------------------+---------+------------------------------------------+---------+----------+-------------+

SHOW CREATE TABLE 输出 #

CREATE TABLE `match_main` (
  `match_main_id` int NOT NULL AUTO_INCREMENT,
  `checked` datetime NOT NULL,
  `updated` timestamp NOT NULL,
  `created` timestamp NOT NULL,
  PRIMARY KEY (`match_main_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2121471809 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `match_team` (
  `match_team_id` int NOT NULL AUTO_INCREMENT,
  `match_main_id` int NOT NULL,
  `team_id` int NOT NULL,
  `team_number` tinyint NOT NULL,
  `version_number` tinyint NOT NULL,
  `updated` timestamp NOT NULL,
  `created` timestamp NOT NULL,
  PRIMARY KEY (`match_team_id`),
  UNIQUE KEY `uq__match_team__match__team_id__version` (`match_main_id`,`team_id`,`version_number`),
  UNIQUE KEY `uq__match_team__match__team_num__version` (`match_main_id`,`team_number`,`version_number`),
  KEY `ix__match_team__team_id` (`team_id`),
  KEY `ix__match_team__match_main_id` (`match_main_id`),
  CONSTRAINT `fk__match_team__match_main_id` FOREIGN KEY (`match_main_id`) REFERENCES `match_main` (`match_main_id`),
  CONSTRAINT `fk__match_team__team_id` FOREIGN KEY (`team_id`) REFERENCES `team` (`team_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9297542 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

额外需求 #

需要保留两个match_team别名（mt1、mt2），因为后续扩展查询时要关联team表，以单条记录展示赛事及两队详情，示例中为简化暂未包含该部分。

优化方案 #

1. 调整查询入口，从match_team切入

当前查询全表扫描match_main（475万行），再关联match_team，效率极低。应先从match_team筛选目标队伍的赛事ID，再关联主表和另一支队伍信息：

SELECT 
    mm.checked,
    mm.match_main_id,
    mm.updated,
    mm.created
FROM
    (
        SELECT match_main_id 
        FROM match_team 
        WHERE team_id = 557949 AND version_number = 0
    ) AS target_matches
    JOIN match_main mm ON mm.match_main_id = target_matches.match_main_id
    LEFT JOIN match_team mt1 ON mt1.match_main_id = mm.match_main_id 
        AND mt1.team_number = 1 
        AND mt1.version_number = 0
    LEFT JOIN match_team mt2 ON mt2.match_main_id = mm.match_main_id 
        AND mt2.team_number = 2 
        AND mt2.version_number = 0
WHERE
    (mt1.team_id = 557949 OR mt2.team_id = 557949);

2. 创建覆盖索引加速子查询

match_team现有ix__match_team__team_id索引，但创建联合索引(team_id, version_number, match_main_id)可实现覆盖索引，避免回表查询：

CREATE INDEX idx_team_version_match ON match_team(team_id, version_number, match_main_id);

3. 验证执行计划

优化后执行EXPLAIN，需确认：

• 子查询使用新索引或ix__match_team__team_id，type为ref或range
• match_main通过主键索引关联，type为eq_ref
• 无match_main全表扫描的情况

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