mysql的in查询语句_mySql in 语句查询优化

有这么一条sql

UPDATE product set BuyerCount =BuyerCount+1 where ProductId in( SELECT ProductId from orderproductdetail where OrderId=2014042514163700856 );

在数据为几欠条的情况下耗时为20多秒。把这条语句拆分之后

SELECT ProductId from orderproductdetail where OrderId=2014042514163700856

耗时0.01秒 结果为：2012

UPDATE product set BuyerCount =BuyerCount+1 where ProductId in(2012)

耗时0.02秒。这就奇怪了，两个时间都很短，为啥放一起就那么长时间。

下面总结了三种优化方法：

UPDATE product p, orderproductdetail o set p.BuyerCount =p.BuyerCount+1 where p.ProductId=o.ProductId and o.OrderId=2014042514163700856;

UPDATE product p INNER JOIN orderproductdetail o on p.ProductId=o.ProductId set p.BuyerCount =p.BuyerCount+1 where o.OrderId=2014042514163700856;

UPDATE product set BuyerCount =BuyerCount+1 where ProductId in( SELECT ProductId from (SELECT ProductId from orderproductdetail where OrderId=2014042514163700856) as tttt );

执行结果如下：

[SQL] UPDATE product p, orderproductdetail o set p.BuyerCount =p.BuyerCount+1 where p.ProductId=o.ProductId and o.OrderId=2014042514163700856;

受影响的行: 1

时间: 0.053ms

[SQL]

UPDATE product p INNER JOIN orderproductdetail o on p.ProductId=o.ProductId set p.BuyerCount =p.BuyerCount+1 where o.OrderId=2014042514163700856;

受影响的行: 1

时间: 0.053ms

[SQL]

UPDATE product set BuyerCount =BuyerCount+1 where ProductId in( SELECT ProductId from (SELECT ProductId from orderproductdetail where OrderId=2014042514163700856) as tttt );

受影响的行: 1

时间: 0.072ms