sql语句优化-心德

   一般来说子查询和关联子表的性能，相对来说 关联表要好一些；

 

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快