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

[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` `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)

  &