mysql - 为什么仅更改 WHERE 子句值时查询的运行速度会慢近 100 倍？

Question

我有以下查询：

SELECT count(m.p1_elo_one)
FROM belgarath.match_result as m
INNER JOIN belgarath.tournament AS tm ON tm.id_ = m.tournament_id
INNER JOIN belgarath.tour AS tr ON tr.id_ = tm.tour_id
INNER JOIN belgarath.rank AS rk ON rk.id_ = tm.rank_id
INNER JOIN belgarath.round AS rd ON rd.id_ = m.round_id
INNER JOIN belgarath.parameters_match AS pm
    ON pm.tour_id = tm.tour_id
    AND pm.rank_id = tm.rank_id
    AND pm.round_id = m.round_id
INNER JOIN belgarath.surf ON belgarath.surf.id_ = tm.surf
INNER JOIN belgarath.player AS p ON p.id_ = m.p1_id 
WHERE 
    belgarath.surf.surf_std_id = 1
    AND pm.engineer = 1
    AND m.date_time_inferred < "2003-06-01"
    AND m.date_time_inferred > "2000-06-01"
    AND m.p1_elo_one IS NOT NULL
    AND p.name_ NOT LIKE "%/%"

该match_result表大约有 1.4m 条记录，查询在大约 0.4 秒内运行良好，并返回大约 20k 的计数。伟大的。

但是，当我更改为时belgarath.surf.surf_std_id = 1，belgarath.surf.surf_std_id = 2查询突然需要大约 35 秒才能返回大约 20k 的计数。

这让我很困惑，因为我只更改了一个输入变量，没有别的。

关于问题可能出在哪里的任何想法？

---

编辑：

SHOW CREATE TABLE对于match_result：

'CREATE TABLE `match_result` (
  `id_` int NOT NULL AUTO_INCREMENT,
  `date_time_op_scheduled` datetime DEFAULT NULL,
  `date_time_op_actual` datetime DEFAULT NULL,
  `date_time_oc_actual` datetime DEFAULT NULL,
  `date_time_inferred` datetime DEFAULT NULL,
  `tournament_id` int DEFAULT NULL,
  `round_id` tinyint DEFAULT NULL,
  `p1_id` int DEFAULT NULL,
  `p2_id` int DEFAULT NULL,
  `result` varchar(45) DEFAULT NULL,
  `uncertainty` float DEFAULT NULL,
  `uncertainty_bin` int DEFAULT NULL,
  `p1_win_pred` float DEFAULT NULL,
  `p1_win_SD` float DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `winning_player` int DEFAULT NULL,
  `completed_sets` int DEFAULT NULL,
  `result_type_id` int DEFAULT NULL,
  `p1_pinnacle_closing_odds` float DEFAULT NULL,
  `p2_pinnacle_closing_odds` float DEFAULT NULL,
  `post_match_data_retrieved` int DEFAULT NULL,
  `p1_elo_all` float DEFAULT NULL,
  `p1_elo_exc_ret_all` float DEFAULT NULL,
  `p1_h2h_win_one_time` float DEFAULT NULL,
  `p1_h2h_win_one_none` int DEFAULT NULL,
  `p1_h2h_win_all_time_surf` float DEFAULT NULL,
  `p1_h2h_win_all_surf` float DEFAULT NULL,
  `p1_h2h_win_all_time` float DEFAULT NULL,
  `p1_h2h_win_all_none` int DEFAULT NULL,
  `p1_h2h_one_time` float DEFAULT NULL,
  `p1_h2h_one_none` int DEFAULT NULL,
  `p1_h2h_all_time_surf` float DEFAULT NULL,
  `p1_h2h_all_surf` float DEFAULT NULL,
  `p1_h2h_all_time` float DEFAULT NULL,
  `p1_h2h_all_none` int DEFAULT NULL,
  `p1_win_one_time` float DEFAULT NULL,
  `p1_win_one_none` int DEFAULT NULL,
  `p1_win_all_time_surf` float DEFAULT NULL,
  `p1_win_all_surf` float DEFAULT NULL,
  `p1_win_all_time` float DEFAULT NULL,
  `p1_win_all_none` int DEFAULT NULL,
  `p1_match_one_time` float DEFAULT NULL,
  `p1_match_one_none` int DEFAULT NULL,
  `p1_match_all_time_surf` float DEFAULT NULL,
  `p1_match_all_surf` float DEFAULT NULL,
  `p1_match_all_time` float DEFAULT NULL,
  `p1_match_all_none` int DEFAULT NULL,
  `p1_elo_one` float DEFAULT NULL,
  `p1_elo_exc_ret_one` float DEFAULT NULL,
  `p1_jet_lag` float DEFAULT NULL,
  `p1_fatigue_short_term` int DEFAULT NULL,
  `p1_fatigue_long_term` int DEFAULT NULL,
  `p1_retired_last_match` tinyint DEFAULT NULL,
  `p2_elo_all` float DEFAULT NULL,
  `p2_elo_exc_ret_all` float DEFAULT NULL,
  `p2_h2h_win_one_time` float DEFAULT NULL,
  `p2_h2h_win_one_none` int DEFAULT NULL,
  `p2_h2h_win_all_time_surf` float DEFAULT NULL,
  `p2_h2h_win_all_surf` float DEFAULT NULL,
  `p2_h2h_win_all_time` float DEFAULT NULL,
  `p2_h2h_win_all_none` int DEFAULT NULL,
  `p2_h2h_one_time` float DEFAULT NULL,
  `p2_h2h_one_none` int DEFAULT NULL,
  `p2_h2h_all_time_surf` float DEFAULT NULL,
  `p2_h2h_all_surf` float DEFAULT NULL,
  `p2_h2h_all_time` float DEFAULT NULL,
  `p2_h2h_all_none` int DEFAULT NULL,
  `p2_win_one_time` float DEFAULT NULL,
  `p2_win_one_none` int DEFAULT NULL,
  `p2_win_all_time_surf` float DEFAULT NULL,
  `p2_win_all_surf` float DEFAULT NULL,
  `p2_win_all_time` float DEFAULT NULL,
  `p2_win_all_none` int DEFAULT NULL,
  `p2_match_one_time` float DEFAULT NULL,
  `p2_match_one_none` int DEFAULT NULL,
  `p2_match_all_time_surf` float DEFAULT NULL,
  `p2_match_all_surf` float DEFAULT NULL,
  `p2_match_all_time` float DEFAULT NULL,
  `p2_match_all_none` int DEFAULT NULL,
  `p2_elo_one` float DEFAULT NULL,
  `p2_elo_exc_ret_one` float DEFAULT NULL,
  `p2_jet_lag` float DEFAULT NULL,
  `p2_fatigue_short_term` int DEFAULT NULL,
  `p2_fatigue_long_term` int DEFAULT NULL,
  `p2_retired_last_match` tinyint DEFAULT NULL,
  `engineered` tinyint DEFAULT NULL,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_`),
  KEY `ix_belgarath_match__completed_sets` (`completed_sets`),
  KEY `ix_belgarath_match__post_match_data_retrieved` (`post_match_data_retrieved`),
  KEY `ix_belgarath_match__date_time_oc_actual` (`date_time_oc_actual`),
  KEY `ix_belgarath_match__tournament_id` (`tournament_id`),
  KEY `ix_belgarath_match__round_id` (`round_id`),
  KEY `ix_belgarath_match__player_id_2` (`p2_id`),
  KEY `ix_belgarath_match__winning_player` (`winning_player`),
  KEY `ix_belgarath_match__result_type_id` (`result_type_id`),
  KEY `ix_belgarath_match__uncertainty_bin` (`uncertainty_bin`),
  KEY `ix_belgarath_match__date_time_inferred` (`date_time_inferred`),
  KEY `ix_belgarath_match__date_time_op_actual` (`date_time_op_actual`),
  KEY `ix_belgarath_match__player_id_1` (`p1_id`),
  KEY `ix_belgarath_match__date_time_op_scheduled` (`date_time_op_scheduled`) /*!80000 INVISIBLE */,
  KEY `ix_belgarath_match__engineered` (`engineered`),
  CONSTRAINT `match_result__player_1` FOREIGN KEY (`p1_id`) REFERENCES `player` (`id_`),
  CONSTRAINT `match_result__player_2` FOREIGN KEY (`p2_id`) REFERENCES `player` (`id_`),
  CONSTRAINT `match_result__round` FOREIGN KEY (`round_id`) REFERENCES `round` (`id_`),
  CONSTRAINT `match_result__tournament` FOREIGN KEY (`tournament_id`) REFERENCES `tournament` (`id_`)
) ENGINE=InnoDB AUTO_INCREMENT=1408022 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'

EXPLAIN对于查询：

标签： mysql