我们是不是发现了什么?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;