有这么一条sql
|
1 |
UPDATE
product set
BuyerCount =BuyerCount+1 where
ProductId in( SELECT
ProductId from
orderproductdetail where
OrderId=2014042514163700856 ); |
在数据为几欠条的情况下耗时为20多秒。把这条语句拆分之后
|
1 |
SELECT ProductId from
orderproductdetail where
OrderId=2014042514163700856 |
耗时0.01秒 结果为：2012
|
1 |
UPDATE
product set
BuyerCount =BuyerCount+1 where
ProductId in(2012) |
耗时0.02秒。这就奇怪了，两个时间都很短，为啥放一起就那么长时间。
下面总结了三种优化方法：
|
1
2
3
4
5
6
7
8
9 |
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 ); |
执行结果如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 |
[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 |
mySql in 语句查询优化,布布扣,bubuko.com
原文：http://www.cnblogs.com/mulaba/p/3689085.html