一般来说子查询和关联子表的性能,相对来说 关联表要好一些;
1、左连的的时候把有过滤条件的表放在前面
2、尽的用字段名代替*
sql语句1:
SELECT p.pay_state payState,
o.posting_date postingDate,
o.amount amount,
o.poundage poundage,
s.sp_id spId,
s.sp_code spCode,
s.name spName,
wu.user_id userId,
wu.user_name userName,
cp.product_code productId,
cp.name productName
FROM t_pdss p
LEFT JOIN orfr o ON p.order_id = o.order_id
LEFT JOIN spee s ON o.account_id = s.sp_id
LEFT JOIN prdsct cp ON o.product_id=cp.product_code
LEFT JOIN web wu ON s.sales_user_id = wu.user_id
WHERE o.posting_date = 20160922;
sql语句2:
SELECT p.pay_state pay_state,
p.channel_cost channel_cost,
o.posting_date posting_date,
o.amount amount,
o.poundage poundage,
s.sp_id sp_id,
s.sp_code sp_code,
s.name sp_name,
wu.user_id user_id,
wu.user_name user_name,
c.channel_id channel_id,
c.channel_name channel_name FROM
(SELECT posting_date,amount,poundage,order_id,account_id FROM t_orfr WHERE posting_date = 20160922) o
LEFT JOIN pdss p ON p.order_id = o.order_id
LEFT JOIN spee s ON o.account_id = s.Fsp_id
LEFT JOIN prdsct cp ON o.product_id=cp.product_code
LEFT JOIN web wu ON s.sales_user_id = wu.user_id
语句2比语句1快