[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)
    ->    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秒,数据完全正确。



[MySQL View]最有意思的视图view优化过程,从30分钟到0.08秒,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。