mysql 优化 视图_[MySQL View]最有意思的视图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` `o