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