使用来自同一表的 2x LEFT JOIN 优化查询-解网

问：

我有以下疑问：

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_mainmatch_team

match_main包含大约 5M 条记录，包含大约 10M 条记录。match_team

上面的查询需要 5 分钟多才能返回大约 1700 条记录，我不知道如何进一步优化它。

这是查询输出：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

我需要保留两个别名表，因为在展开此查询时，我需要将这些表链接到这些表。这样我就可以在一行上返回一场比赛的输出，其中列显示了比赛中所有球队的详细信息。我排除了这一点，以保持 MRE 的简单性。match_teamteam

MySQL 查询优化 mysql-8.0

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

它应该对和都使用新索引，然后通过其主键加入。这消除了上 400 万行的表扫描。uq__match_team__team_num__version__matchmt1mt2match_mainmatch_main

这是我测试时得到的 EXPLAIN 报告：

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mt1
   partitions: NULL
         type: ref
possible_keys: uq__match_team__match__team_id__version,uq__match_team__team_num__version__match,ix__match_team__match_main_id
          key: uq__match_team__team_num__version__match
      key_len: 2
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: mt2
   partitions: NULL
         type: eq_ref
possible_keys: uq__match_team__match__team_id__version,uq__match_team__team_num__version__match,ix__match_team__match_main_id
          key: uq__match_team__team_num__version__match
      key_len: 6
          ref: const,const,test.mt1.match_main_id
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: m
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.mt1.match_main_id
         rows: 1
     filtered: 100.00
        Extra: NULL

你好。谢谢你 - 一个简洁的解决方案，它绝对让我思考如何索引这样的情况。但是，我不确定它是否适用于这个特定示例，因为在某些边缘情况下，并不总是有 a 为 1（或 2）。这就是为什么我必须使用上面的原因 - 我仍然想返回记录:(match_teamteam_numberLEFT JOINmatch_main

0赞 slaakso 11/16/2023 #2

第二次JOIN的目的是什么？您可以只尝试一个：

SELECT 
    mm.checked,
    mm.match_main_id,
    mm.updated,
    mm.created
FROM match_team AS mt 
   JOIN match_main AS mm ON mm.match_main_id = mt.match_main_id 
WHERE mt.team_id = 557949 AND mt.version_number = 0 
  AND mt.team_number in (1,2);

此外，当您将条件移动到 -子句时，这将成为正常现象。LEFT JOINJOINWHERE

查看 dbfiddle

使用来自同一表的 2x LEFT JOIN 优化查询

Optimise query with 2x LEFT JOIN from same table

评论

评论

评论