MySQL 最有意思的视图view优化过程，从30分钟到0.08秒

开发人员写了一个view，select要30分钟，让我优化下，view如下：CREATE ALGORITHM=UNDEFINED  SQL SECURITY DEFINER VIEW view_offer_label AS   SELECT ol.OFFER_ID AS OFFER_ID,ol.EFFECTIVE_DATE AS EFFECTIVE_DATE   FROM offer_label ol   WHERE( ol.ID =      (SELECT ol2.ID    FROM offer_label ol2    WHERE ((ol.OFFER_ID = ol2.OFFER_ID) AND (ol2.LABEL = 'PROD'))   ORDER BY ol2.EFFECTIVE_DATE DESC,ol2.ID DESC LIMIT 1 ) )   开发人员select一下需要30多分钟： 21068 rows in set (1987.08 sec)   先解析一下： mysql> explain  SELECT `ol`.`OFFER_ID` AS `OFFER_ID`,`ol`.`EFFECTIVE_DATE` AS `EFFECTIVE_DATE`      ->  FROM `offer_label` `ol`      ->  WHERE (`ol`.`ID` =      ->       (SELECT `ol2`.`ID`      ->    FROM `offer_label` `ol2`      ->    WHERE ((`ol`.`OFFER_ID` = `ol2`.`OFFER_ID`) AND (`ol2`.`LABEL` = 'PROD'))     ->    ORDER BY `ol2`.`EFFECTIVE_DATE` DESC,`ol2`.`ID` DESC LIMIT 1)); +----+--------------------+-------+-------+------------------------------------+-------------------+---------+---------------------------+--------+------------------------------------------+ | id | select_type        | table | type  | possible_keys                      | key               | key_len | ref                       | rows   | Extra                                    | +----+--------------------+-------+-------+------------------------------------+-------------------+---------+---------------------------+--------+------------------------------------------+ |  1 | PRIMARY            | ol    | index | NULL                               | offer_label_index | 1542    | NULL                      | 143299 | Using where; Using index                 | |  2 | DEPENDENT SUBQUERY | ol2   | ref   | OFFER_LABEL_FKEY,offer_label_index | offer_label_index | 1534    | const,catalog.ol.OFFER_ID |      1 | Using where; Using index; Using filesort | +----+--------------------+-------+-------+------------------------------------+-------------------+---------+---------------------------+--------+------------------------------------------+ 2 rows in set (0.00 sec)看到有 Using filesort，要优化where后面的子判断，优化如下：   select max(ol2.ID)   from offer_label ol2    where ol2.LABEL = 'PROD'   group by ol2.OFFER_ID   order by ol2.EFFECTIVE_DATE DESC,ol2.ID DESC; mysql> explain    select max(ol2.ID)     ->    from offer_label ol2      ->    where ol2.LABEL = 'PROD'     ->    group by ol2.OFFER_ID     ->    order by ol2.EFFECTIVE_DATE DESC,ol2.ID DESC; +----+-------------+-------+------+-------------------+-------------------+---------+-------+-------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows  | Extra                                                     | +----+-------------+-------+------+-------------------+-------------------+---------+-------+-------+-----------------------------------------------------------+ |  1 | SIMPLE      | ol2   | ref  | offer_label_index | offer_label_index | 767     | const | 71649 | Using where; Using index; Using temporary; Using filesort | +----+-------------+-------+------+-------------------+-------------------+---------+-------+-------+-----------------------------------------------------------+ 1 row in set (0.00 sec)

有些不对劲，再仔细看了view的结构，恍然大悟：   

优化成如下样子：

CREATE ALGORITHM=UNDEFINED  SQL SECURITY DEFINER VIEW view_offer_label AS    SELECT ol2.OFFER_ID, max(EFFECTIVE_DATE)  EFFECTIVE_DATE   FROM offer_label ol2    WHERE  ol2.LABEL = 'PROD'   group by ol2.OFFER_ID ;   执行结果为：

21068 rows in set (0.08 sec)

不到0.08秒，数据完全正确。