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

最新推荐文章于 2024-11-06 14:18:55 发布

原创最新推荐文章于 2024-11-06 14:18:55 发布 · 4.3k 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

性能优化专栏收录该内容

2 篇文章

订阅专栏

本文探讨了MySQL中使用EXISTS与IN子查询时的性能问题，特别是在不同版本间的表现差异，并提出了有效的优化策略。

业务需求查询订单中包含商品《高性能 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

查看执行计划

PRIMARY

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

const

13994

Using where; Using index

DEPENDENT SUBQUERY

ref

PRIMARY,fk_saleorderdetail_skuid,fk_saleorderdetail_storeid

fk_saleorderdetail_skuid

userdb0001.a.tid,const

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及以后版本在子查询方面，做了较大优化。可以放心写。