我們是不是發現了什麼?IF函數對聯表查詢是否走索引有影響,也對單表的查詢速度有影響。上圖中的 t_custmor_credit_record 單表查詢,有IF函數,查詢時間近 8 秒,沒有IF函數,查詢時間 2 秒左右;t_custmor_credit_record 與 t_custmor_deposit 聯表查,有IF函數,t_custmor_credit_record 走的是全表查,查詢時間近 11 秒,沒有IF函數,t_custmor_credit_record 走的是索引,查詢時間 3 秒不到。那麼我們有沒有什麼辦法拿掉這個IF函數呢?
SELECT MIN(tcd.channal) channal, MAX(tccr.id) mId,tccr.login_name,tccr.bill_no,tccr.create_time,
CASE credit_type WHEN 1 THEN amount_before ELSE 0 END AS freeBefore,
CASE credit_type WHEN 1 THEN amount_change ELSE 0 END AS freeChange,
CASE credit_type WHEN 1 THEN amount_after ELSE 0 END AS freeAfter,
CASE credit_type WHEN 2 THEN amount_before ELSE 0 END AS freeChange,
CASE credit_type WHEN 2 THEN amount_change ELSE 0 END AS freeChange,
CASE credit_type WHEN 2 THEN amount_after ELSE 0 END AS freeChange,
CASE credit_type WHEN 3 THEN amount_before ELSE 0 END AS promotionBefore,
CASE credit_type WHEN 3 THEN amount_change ELSE 0 END AS promotionChange,
CASE credit_type WHEN 3 THEN amount_after ELSE 0 END AS promotionAfter
FROM t_customer_credit_record tccr
LEFT JOIN t_customer_deposit tcd ON tccr.bill_no = tcd.bill_no
GROUP BY tccr.bill_no,tccr.login_name,tccr.create_time
ORDER BY mId desc
LIMIT 0, 10;