Question

sql 优化问题： 写了一个sql出现错误：Lock wait timeout exceeded; try restarting transaction

谁能优化优化：
SELECT
d.paymoney,d.owemoney, (select sum(spending) from cdn_account ) as spending,
(select sum(money) from cdn_financedetail ) as charge ,
(select sum(money) from cdn_transaction) as consume
FROM
(select sum(p.paymoney) as paymoney,sum(p.paytotal-p.paymoney)as owemoney from cdn_pay p,cdn_user u where p.userid=u.userid ) as d

Answer 1

1 不需要嵌套子查询
select sum(p.paymoney) as paymoney,
sum(p.paytotal-p.paymoney) as owemoney
(select sum(spending) from cdn_account ) as spending,
(select sum(money) from cdn_financedetail ) as charge ,
(select sum(money) from cdn_transaction) as consume
from cdn_pay p,cdn_user u where p.userid=u.userid ；
即可。
按道理来讲，单纯的这条语句是不会引起Lock wait timeout exceeded; try restarting transaction 的问题。可能是与其它事务产生了资源争用。
你先用上面的句子试试，如果仍然出现上述的错误 那么你需要找到数据库还有那些事务对 cdn_account cdn_financedetail cdn_transaction cdn_pay ,cdn_user 五张表进行了操作。

Answer 2

你不就是对多个表的数据各自求和吗？（外加一个userid的条件）
你这得多少个子查询啊，以下这样写难道有问题：
SELECT sum(p.paymoney) as paymoney,
sum(p.paytotal-p.paymoney)as owemoney
sum(cdn_account .spending) as spending,
sum(cdn_financedetail .money) as charge ,
sum(cdn_transaction.money) as consume
FROM
cdn_account ,cdn_financedetail ,cdn_transaction, cdn_pay p,cdn_user u
WHERE p.userid=u.userid

追问

不嵌套的话五个表一块儿查是不是有点慢，分开查求和数据会不会比一块儿查要快些?

追答

或许吧，你有够多的数据的话可以测试比较一下

Answer 3

SELECT
paymoney,owemoney, spending, charge , consume
FROM
(select sum(p.paymoney) as paymoney,
sum(p.paytotal-p.paymoney)as owemoney,
(select sum(spending) from cdn_account ) as spending,
(select sum(money) from cdn_financedetail ) as charge ,
(select sum(money) from cdn_transaction) as consume
from cdn_pay p,cdn_user u where p.userid=u.userid )

as d