糟糕的MySQL LEFT JOIN性能，逐组最大化 [英] Awful MySQL LEFT JOIN Performance for groupwise maximum

问题描述

我有下表

CREATE TABLE `prod_prices` (
  `id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `date` date NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

还有一些示例数据

INSERT INTO `prod_prices` (`id`, `date`, `price`) VALUES
('plan_a', '2012-06-15', 10.20),
('plan_a', '2012-06-16', 10.30),
('plan_b', '2012-06-15', 5.20),
('plan_b', '2012-06-16', 5.30),
('plan_b', '2012-06-17', 5.50);

，并且想知道每个计划的最新价格.我有以下查询

And want to know the latest price for each plan. I have the following query

SELECT p1.id, p1.date, p1.price
FROM prod_prices p1
LEFT JOIN prod_prices p2 ON p1.id = p2.id
AND p1.date < p2.date
WHERE p2.id IS NULL

可以工作，尽管会产生真正的糟糕表现. EXPLAIN显示

which works, although yields truly abysmal performance. EXPLAIN shows

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE          p1      ALL     NULL             NULL    NULL        NULL   5   
1   SIMPLE          p2      ref     PRIMARY         PRIMARY     98       p1.id   1      Using where; Using index; Not exists

，因此p1表选择未使用任何索引.怎么了?

and hence the p1 table select is not using any indices. What is the matter?

推荐答案

这应该产生相同的结果，并且效果要好得多.

This should give the same results and perform much better.

SELECT  p1.*
FROM    prod_prices p1
        INNER JOIN
        (   SELECT  ID, MAX(Date) AS Date
            FROM    prod_prices
            GROUP BY ID
        ) AS p2
            ON p1.ID = p2.ID 
            AND p1.Date = p2.Date

这篇关于糟糕的MySQL LEFT JOIN性能，逐组最大化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！