插入查询优化

Question

我可以帮助您翻译。这段文本是关于一个包含约2百万行的表格的。该表格有一个基于B-tree的主键，由(id,round)组成，并且还有其他两个（似乎不相关的）索引。

我的问题是：

insert into a1
select * from cars
where (id, round) in (select id, min(round) from cars group by id)

上述查询需要大约30秒才能运行，但为什么下面的查询会更快？

insert into a1
select * from cars
where (id, round) in (select id, max(round) from cars group by id)

似乎运行需要很长时间？我等了几个小时。我改变的唯一一件事是min->max。

PS：这是创建表格的代码。

CREATE TABLE `cars` (
  `id` int(11) NOT NULL,
  `make` varchar(128) NOT NULL,
  `miles` varchar(128) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `round` int(11) NOT NULL,
  `location` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`,`round`),
  KEY `time` (`time`),
  KEY `make` (`make`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

- Tao Song

---

为什么子查询使用round进行分组，而不是id？你知道在这种情况下id是任意的吗？ - OMG Ponies

尝试解释"explain extended select * from cars where (id round) in (select id, min(round) from cars group by round)"和"explain extended select * from cars where (id round) in (select id, max(round) from cars group by round)"。 - penartur

选择 id，min(round) from cars group by id 很快，而选择 id，max(round) from cars group by id 则需要很长时间。我试图向他们解释，只有“行”之间的小差异，大约是 130k 对比 160k。 - Tao Song

id不是自动递增的。大约每个id对应20轮。所以是的，有多行具有相同的id但不同的轮次（20）。@迈克·珀塞尔 - Tao Song

所以你不是在寻找特定的id，而是在寻找最大的(round)，为什么不将其添加到SELECT子句中，而不是WHERE子句中呢？尝试使用SELECT id, make, miles, time, max(round) as max_round, location FROM cars GROUP BY id。 - Mike Purcell

Answer 1

IN子查询在当前MySQL查询优化器中没有得到最佳优化。

我建议使用：

INSERT INTO a1
  SELECT c.* 
  FROM 
        cars AS c
    JOIN 
        ( SELECT id, MAX(round) AS round
          FROM cars 
          GROUP BY id
        ) AS m
      ON  (m.id, m.round) = (c.id, c.round) 

你已经在(id, round)上有索引，这将使子查询运行得相当快，并且只运行一次（作为派生表）。另一种方法是使用IN，此时子查询将为cars表中的每一行运行一次（因此速度较慢）。请使用EXPLAIN检查执行计划。

- ypercubeᵀᴹ

Answer 2

请在id字段上创建一个单一索引 您在“group by”方法中使用单个字段

- silly

---

他已经在 (id, round) 上有一个索引。这可以被 GROUP BY 使用。 - ypercubeᵀᴹ