mysql 性能优化之exists 写法不一样，性能提升400倍

业务需求 查询订单中包含商品《高性能 mysql》 的单据总量

上代码

select count(1) from saleorder a 
                          where a.tid = 180571   and exists(select 1 from saleorderdetail e1 where a.tid = e1.tid and a.billid = e1.billid and e1.goodsid in (1061)

执行时间为 16s

 

mysql 版本为 

5.6.16-log

查看执行计划

1	PRIMARY	a	ref	PRIMARY,bo_billdate,bo_createdate,bo_billno,fk_saleorder_bid,fk_saleorder_cmpid,fk_saleorder_empid,fk_saleorder_storeid,fk_saleorder_userid	bo_billdate	4	const	13994	Using where; Using index
2	DEPENDENT SUBQUERY	e1	ref	PRIMARY,fk_saleorderdetail_skuid,fk_saleorderdetail_storeid	fk_saleorderdetail_skuid	8	userdb0001.a.tid,const	4	Using where; Using index

相同的语句 我再来看 8.0  中执行，只需要0.04S

mysql 版本为 

8.0.18

再 看执行计划 

SIMPLE	<subquery2>		ALL						100.00	Using where
SIMPLE	a		eq_ref	PRIMARY,bo_billdate,bo_createdate,bo_billno,fk_saleorder_bid,fk_saleorder_cmpid,fk_saleorder_empid,fk_saleorder_storeid,fk_saleorder_userid	PRIMARY	12	const,<subquery2>.billid	1	100.00	Using index
MATERIALIZED	e1		ref	PRIMARY,fk_saleorderdetail_skuid,fk_saleorderdetail_storeid	fk_saleorderdetail_skuid	8	const,const	2402	100.00	Using index

 可以看到 8.0对子查询做了优化。

那么在 8.0以前版本中 exists怎么优化呢。

两种方案，1种是改写为 join

      select count(distinct a.tid,a.billid) from saleorder a  inner join saleorderdetail e1 on a.tid = e1.tid and a.billid = e1.billid
                                where a.tid = 180571  and  e1.goodsid in (1061) 

查询时间为0.04S

 

第二种方案，改in

select count(1) from saleorder a 
                                where a.tid = 180571  and (a.tid,a.billid) in (select tid,billid from  saleorderdetail e1 where e1.tid =180571 and  e1.goodsid in (1061))
     同样查询时间也只有 0.04S

 

 

结论 mysql 8.0以前的版本 要注意 exists与in 的子查询 会不会产生DEPENDENT SUBQUERY 如果产生，则必须优化它。

mysql 8.0及以后版本在子查询方面，做了较大优化。可以放心写。