[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秒，數據完全正確。
聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
