有这么一条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 |