如果三个表的左外连接改为inner join，并且t3.customer建立索引，t2.order_release和t1.id都建立索引，并以t3表作为驱动表，效果可能会好些。

嗯，只查t3的id是快很多，但是因为是左连接导致结果不一致了

where 条件上有T3 所以和T3的关联可以直接改为inner join吧。

不能的，我就是这样改的，大概是这样：
select xxx from
t1
left join t2 on t1.id=t2.shipment
left join (select id from t3 where t3.customer=xxx) tt3 on tt3.id=t2.order_release
where
t1.xxxx
因为t3的id是主键，所以快很多，但是结果是不一致的

where子句中有条件t3.customer ，这种null-rejecting的情况是可以转换成inner join的
t2.order_release 建索引或者建（order_release，shipment）的组合索引
t3 (customer,id) 组合索引

用子查询的方式改写 from t1 where …… and id in
(select t2. shipment from t2 join t3 where t3.id=t2.order_release and t3.customer=…)

不用加distinct

是改成如下sql？我看了执行计划和原来是一样的。

SELECT DISTINCT
	t1.id,
	t1.shipment_no,
	t1.vehicle_no,
	t1.driver1_name,
	DATE_FORMAT( t1.latest_pickup_time, '%Y-%m-%d' ) AS latest_pickup_date,
	DATE_FORMAT( t1.latest_pickup_time, '%H:%i' ) AS latest_pickup,
	t1.latest_pickup_time,
	t1.version,
	t1.domain_name,
	t1.insert_user 
FROM
	fsl_shipment t1  
WHERE
	t1.project_code = 'DD' 
	AND t1.shipment_no IS NOT NULL 
	AND t1.shipment_status IN ( '18', '20' ) 
	AND t1.is_a_shipment = 'N' 
	AND t1.sendncicflag IS NULL 
	AND t1.custom_type IN (	'0','4')
	AND t1.id in (select t2.shipment from fsl_order_movement_unit t2 join  fsl_order_release t3 where  t2.order_release = t3.id and t3.customer = '30874729619931136' )

复制

不需要DISTINCT了
SELECT 
	t1.id,
	t1.shipment_no,
	t1.vehicle_no,
	t1.driver1_name,
	DATE_FORMAT( t1.latest_pickup_time, '%Y-%m-%d' ) AS latest_pickup_date,
	DATE_FORMAT( t1.latest_pickup_time, '%H:%i' ) AS latest_pickup,
	t1.latest_pickup_time,
	t1.version,
	t1.domain_name,
	t1.insert_user 
FROM
	fsl_shipment t1  
WHERE
	t1.project_code = 'DD' 
	AND t1.shipment_no IS NOT NULL 
	AND t1.shipment_status IN ( '18', '20' ) 
	AND t1.is_a_shipment = 'N' 
	AND t1.sendncicflag IS NULL 
	AND t1.custom_type IN (	'0','4')
	AND t1.id in (select t2.shipment from fsl_order_movement_unit t2 join  fsl_order_release t3 where  t2.order_release = t3.id and t3.customer = '30874729619931136' )


复制

另外 t2.order_release 建索引或者建（order_release，shipment）的组合索引
t3 (customer,id) 组合索引

跟业务部门确认后，因为这个sql比较重要，所以就不改写了，增加对t1表的时间限制条件，查最近15天，最终耗时16s