mysql - 为什么这个查询搜索所有行，尽管是 'guided'

Question

我有各种比赛中马匹赔率的历史数据，所以我有 3 个表

CREATE TABLE `horses` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
)

CREATE TABLE `odds` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `raceId` int(11) DEFAULT NULL,
  `horseId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `raceId` (`raceId`),
  KEY `horseId` (`horseId`),
)

CREATE TABLE `races` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `venueId` int(10) unsigned NOT NULL,
  `raceDate` date DEFAULT NULL,
  `raceTime` time DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `locationId` (`venueId`,`raceDate`,`raceTime`)
)

马匹和比赛表有几千行，但赔率表有 2000 万多行，根据raceId 和 horseId 索引(每个raceId、horseId 有多行)

我想提取给定日期的马匹。我第一次尝试了天真的查询1

SELECT `h`.* FROM `races` `r` 
    LEFT JOIN `odds` `o`  ON `o`.`raceId` = `r`.`id` 
    LEFT JOIN `horses` `h` ON `h`.`id` = `o`.`horseId`
    WHERE `r`.`raceDate` = '2018-02-10'
    AND `o`.`horseId` IS NOT NULL
    GROUP BY `h`.`id`;

但是花了2分钟+

知道我只想考虑给定日期的赔率，我这样做了(查询 2):

SELECT DISTINCT horseId FROM odds 
WHERE raceId IN (SELECT id FROM races WHERE raceDate = '2018-02-10');

它的运行时间不到一秒，并给出了我需要的 69 个 horseId。所以看来使用查询3获取马匹应该是一件简单的事情...

SELECT * FROM horses WHERE horses.id IN (SELECT DISTINCT horseId FROM odds 
WHERE raceId IN (SELECT id FROM races WHERE raceDate = '2018-02-10'));  

它可以工作，但需要与第一个查询相同的时间，事实上“解释”非常相似。

但是如果我将括号中的整个部分替换为实际的 69 个 horseId，如下所示(查询 4):

SELECT * FROM horses WHERE horses.id IN (4885,3653,3356,4886,451,941...)

它再次在不到一秒的时间内运行。

我有一个查询现在运行得很快，但我想了解为什么查询 3 很慢？如果我从内到外分块执行它，就像我直觉上认为 mySql 会做的那样，它会很快。

我确实知道优化器并不总是能创建良好的执行计划。但为什么 mySql 运行效率这么低呢？对我来说，这似乎是一个完全有效的查询，与非常快的查询 5

没有太大区别

SELECT h.* FROM horses h 
LEFT JOIN (SELECT DISTINCT horseId 
       FROM odds WHERE raceId IN (
            SELECT id FROM races WHERE raceDate = '2018-02-10')) st 
        ON st.horseId = h.id 
        WHERE horseId IS NOT NULL;

我意识到联接可能比 IN 更好 - 我可以接受如果查询 4 ​​也很慢但事实并非如此，它只是一秒钟的几分之一。希望“啊哈”对我将来有帮助。

Answer 1

select distinct h.*
from races r
join odds o on o.raceId = r.id
join horses h on h.id = o.horceId
where r.date = '2018-02-10'

它应该可以很好地与给定的索引配合使用。