为什么递归查询比join和group by快?

I am using MySQL server 5.5 and have next db structure

我正在使用MySQL server 5.5，并拥有下一个db结构

    -- -----------------------------------------------------
    -- Table `mydb`.`User`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`User` (
      `id` INT NOT NULL ,
      `Name` VARCHAR(45) NOT NULL ,
      PRIMARY KEY (`id`) )
    ENGINE = InnoDB;


    -- -----------------------------------------------------
    -- Table `mydb`.`Payments`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`Payments` (
      `id` INT NOT NULL ,
      `Amount` DOUBLE NOT NULL ,
      `User` INT NOT NULL ,
      PRIMARY KEY (`id`) ,
      INDEX `fk_Payments_User` (`User` ASC) ,
      CONSTRAINT `fk_Payments_User`
        FOREIGN KEY (`User` )
        REFERENCES `mydb`.`User` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;


    -- -----------------------------------------------------
    -- Table `mydb`.`Extras`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`Extras` (
      `id` INT NOT NULL ,
      `ExtraAmount` DOUBLE NOT NULL ,
      PRIMARY KEY (`id`) ,
      INDEX `fk_Extras_Payments1` (`id` ASC) ,
      CONSTRAINT `fk_Extras_Payments1`
        FOREIGN KEY (`id` )
        REFERENCES `mydb`.`Payments` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;

And question is: Why this query

问题是:为什么这个查询。

    SELECT u.*, sum(`amount`),sum(`ExtraAmount`)
    FROM `user` AS `u`
    LEFT JOIN payments AS p ON u.id = p.`user`
    INNER JOIN extras AS e ON p.id = e.id
    WHERE `Name` = 'abc'
    GROUP BY `Name`

works slower than this one

比这个慢

    SELECT *, IFNULL((SELECT sum(`amount`)
                      FROM payments AS p
                      WHERE u.id = p.`user`),0) AS `TotalAmount`,
              IFNULL((SELECT sum(`ExtraAmount`)
                      FROM payments AS p
                      INNER JOIN extras AS e ON p.id = e.id
                      WHERE p.`User` = u.id),0) AS `sum2`
    FROM `user` AS `u`
    WHERE `Name` = 'abc'

There is difference even on empty DB(0.0001-0002s, not much, but still). But if you populate each table with 50k records, difference will be more noticeable.

即使是空DB(0.0001-0002s，不是很多，但仍然是)也存在差异。但是如果您用50k记录填充每个表，差异将更加明显。

From my point of view second query executes way more sub queries, so it should work slower.

从我的观点来看，第二个查询执行了更多的子查询，因此它应该工作得更慢。

Can somebody explain it for me please?

谁能给我解释一下吗?

1 个解决方案

#1

The difference is that on your second inner query (SELECT sum(`amount`) FROM payments AS p WHERE u.id = p.`user`) mysql is using an index, compared to the first query, where the elements are determined without the index. Using explain you will be able to see the execution plan, how many rows are involved and if the subqueries are using or not indexes.

不同之处在于，在第二个内部查询(从付款中选择sum(' amount '))作为p (u)。与第一个查询相比，mysql使用的是索引，在第一个查询中，元素是在没有索引的情况下确定的。使用explain，您将能够看到执行计划、涉及多少行以及子查询是否使用索引。

See sqlFiddle (note the extra columns) for the execution plan of your scenario.

请参阅场景执行计划的sqlFiddle(注意额外的列)。