最有意义的视图view优化过程，从30分钟到0.08秒_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 2231 | 回复: 0

主题： 最有意义的视图view优化过程，从30分钟到0.08秒

无止境

注册用户

等级：新兵
经验：64
发帖：74
精华：0
注册：2011-11-14
状态：离线
发送短消息息给无止境

加好友发送短消息息给无止境

发消息

发表于：

2014-12-22 10:42:12 | [全部帖] [楼主帖]

楼主

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

先解析一下：

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

--转自