mysql depended_query 最佳化案例一則

標籤：

 

 

月度利息統計sql最佳化

 

原因：寫的sql語句複雜，理解起來有難度，另一方面，查詢效能比較低

 

原來的語句如下：

SELECT

tp.year,

tp.month,

tp.bid_id,

b.`title`,

DATE(b.`time`) `time`,

tp.receive_date,

u.`name`,

u.`reality_name`,

b.`amount`,

b.`apr`,

b.`period_unit`,

b.`period`,

tp.receive_interest,

tp.bid_invest_count,

IFNULL((SELECT SUM(s.`amount`) FROM t_invests s WHERE s.`bid_id`=tp.bid_id AND s.user_id IN (SELECT r.user_id FROM `t_user_repair` r)), 0)  AS bid_amount1,

IFNULL((SELECT SUM(s.`receive_corpus`) FROM t_bill_invests s WHERE s.`bid_id`=tp.bid_id AND DATE(IFNULL(s.`real_receive_time`, s.`receive_time`))=tp.`receive_date` AND s.user_id IN (SELECT r.user_id FROM `t_user_repair` r)), 0) AS bid_corpus1,

IFNULL((SELECT SUM(s.`receive_interest`) FROM t_bill_invests s WHERE s.`bid_id`=tp.bid_id AND DATE(IFNULL(s.`real_receive_time`, s.`receive_time`))=tp.`receive_date` AND s.user_id IN (SELECT r.user_id FROM `t_user_repair` r)), 0) AS bid_interest1,

IFNULL((SELECT COUNT(1) FROM t_bill_invests s WHERE s.`bid_id`=tp.bid_id AND DATE(IFNULL(s.`real_receive_time`, s.`receive_time`))=tp.`receive_date` AND s.user_id IN (SELECT r.user_id FROM `t_user_repair` r)), 0) AS bid_invest_count1, 

IFNULL((SELECT SUM(s.`receive_corpus`) FROM t_bill_invests s WHERE s.`bid_id`=tp.bid_id AND DATE(IFNULL(s.`real_receive_time`, s.`receive_time`))=tp.`receive_date` AND s.user_id NOT IN (SELECT r.user_id FROM `t_user_repair` r)), 0) AS bid_corpus2

FROM

(

SELECT

t.`bid_id`,

DATE(IFNULL(t.`real_receive_time`, t.`receive_time`)) AS `receive_date`,

YEAR(IFNULL(t.`real_receive_time`, t.`receive_time`)) AS `year`,

MONTH(IFNULL(t.`real_receive_time`, t.`receive_time`)) AS `month`,

 

IFNULL(SUM(t.`receive_interest`),0) receive_interest,

COUNT(1) AS bid_invest_count

FROM t_bill_invests t

WHERE 1=1 AND DATE(IFNULL(t.`real_receive_time`, t.`receive_time`)) >= ‘2015-09-01‘ AND DATE(IFNULL(t.`real_receive_time`, t.`receive_time`)) <= ‘2015-10-31‘

GROUP BY `year`, `month`, t.`bid_id`, DATE(IFNULL(t.`real_receive_time`, t.`receive_time`))

)tp

LEFT JOIN t_bids b ON tp.bid_id=b.`id`

LEFT JOIN t_users u ON b.`user_id`=u.`id`;

 

 

執行結果所需時間資訊如下：

/* Affected rows: 0  已找到記錄: 129  警告: 0  期間 1 query: 28.704 sec. (+ 10.031 sec. network) */

 

 

 

最終最佳化語句：

 

SELECT

YEAR(IFNULL(tp.`real_receive_time`, tp.`receive_time`)) AS `year`,

MONTH(IFNULL(tp.`real_receive_time`, tp.`receive_time`)) AS `month`,

tp.bid_id,

b.`title`,

DATE(b.`time`) `time`,

DATE(IFNULL(tp.`real_receive_time`, tp.`receive_time`)) AS `receive_date`,

u.`name`, 

u.`reality_name`, 

b.`amount`,

b.`apr`, 

b.`period_unit`, 

b.`period`, 

sum(if(tp.real_receive_time,tp.`receive_interest`,0)) receive_interest, 

COUNT(1) AS bid_invest_count , 

sum(if(tp.user_id in (SELECT r.user_id FROM `t_user_repair` r) and tp.real_receive_time is not null,ti.amount,0 )) bid_amount1,

sum(if(tp.user_id in (SELECT r.user_id FROM `t_user_repair` r) and tp.real_receive_time is not null,tp.receive_corpus,0 )) bid_corpus1,

sum(if(tp.user_id in (SELECT r.user_id FROM `t_user_repair` r) and tp.real_receive_time is not null,tp.receive_interest,0 )) bid_interest1, 

sum(if(tp.user_id in (SELECT r.user_id FROM `t_user_repair` r),1,0 )) bid_invest_count1, 

sum(if(tp.user_id not in (SELECT r.user_id FROM `t_user_repair` r) and tp.real_receive_time is not null,tp.receive_corpus,0 )) bid_corpus2

 

FROM t_bill_invests tp

LEFT JOIN t_bids b ON tp.bid_id=b.`id`

LEFT JOIN t_users u ON b.`user_id`=u.`id`

left join (select id,amount from t_invests) ti on ti.id=tp.invest_id

 

WHERE 1=1 AND DATE(IFNULL(tp.`real_receive_time`, tp.`receive_time`)) >= ‘2015-09-01‘  AND DATE(IFNULL(tp.`real_receive_time`, tp.`receive_time`)) <= ‘2015-10-31‘

GROUP BY tp.`bid_id`, DATE(tp.`receive_time`);

 

 

 

執行時間:

/* Affected rows: 0  已找到記錄: 129  警告: 0  期間 1 query: 0.671 sec. */

 

結果一樣，但最終結果只需0.67s

 

 

最佳化思路，原來語句explain中：

 

在select_type中有大量的dependent_subquery,此種類型的查詢極耗效能，在sql的編寫中應該極力避免。

 

1. 結合業務需求，查看group by寫法，發現沒有必要寫那麼多的group by，只需要抓住核心的tp.`bid_id`, DATE(tp.`receive_time`)便可確定一組帳單資訊。

 

      2.  盡量改寫查詢列，讓其為subquery,如下：

 

SELECT

YEAR(IFNULL(tp.`real_receive_time`, tp.`receive_time`)) AS `year`,

MONTH(IFNULL(tp.`real_receive_time`, tp.`receive_time`)) AS `month`,

tp.bid_id,

b.`title`,

DATE(b.`time`) `time`, 

DATE(IFNULL(tp.`real_receive_time`, tp.`receive_time`)) AS `receive_date`, 

u.`name`,

u.`reality_name`, 

b.`amount`,

b.`apr`, 

b.`period_unit`, 

b.`period`, 

sum(if(tp.real_receive_time,tp.`receive_interest`,0)) receive_interest, 

COUNT(1) AS bid_invest_count ,

IFNULL((SELECT SUM(s.`amount`) FROM t_invests s    WHERE s.`bid_id`=tp.bid_id and s.user_id IN (SELECT r.user_id FROM `t_user_repair` r)), 0) as bid_amount1, 

sum(if(tp.user_id in (SELECT r.user_id FROM `t_user_repair` r) and tp.real_receive_time is not null,tp.receive_corpus,0 )) bid_corpus1, 

sum(if(tp.user_id in (SELECT r.user_id FROM `t_user_repair` r) and tp.real_receive_time is not null,tp.receive_interest,0 )) bid_interest1,

sum(if(tp.user_id in (SELECT r.user_id FROM `t_user_repair` r),1,0 )) bid_invest_count1, 

sum(if(tp.user_id not in (SELECT r.user_id FROM `t_user_repair` r) and tp.real_receive_time is not null,tp.receive_corpus,0 )) bid_corpus2 

FROM t_bill_invests tp

LEFT JOIN t_bids b ON tp.bid_id=b.`id`

LEFT JOIN t_users u ON b.`user_id`=u.`id`

 

WHERE 1=1 AND DATE(IFNULL(tp.`real_receive_time`, tp.`receive_time`)) >= ‘2015-04-01‘  AND DATE(IFNULL(tp.`real_receive_time`, tp.`receive_time`)) <= ‘2015-10-31‘

GROUP BY tp.`bid_id`, DATE(tp.`receive_time`);

 

 

在其explain中還有殘留的依賴外查詢：

 

 

主要是由框中的語句引起，於是再改寫，讓其改成暫存資料表串連，就是最後的sql語句：

 

 

只剩暫存資料表與了查詢，效能有了大幅度的提升，最佳化結束。

 

總結：統計類查詢經常不小心就會寫成depended_subquery的形式，可以結合sum count case if條件判斷來改寫，讓其可以達到subquery的形式，另一方面也可以改成暫存資料表的方式並通過串連來提高效能。

mysql depended_query 最佳化案例一則