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

liujjun

开发人员写了一个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秒，数据完全正确。