UPDATE t_bill_detail a SET a.writeoff_fee = a.writeoff_fee +
(SELECT SUM(b.writeoff_fee) FROM t_writeoff_record b WHERE a.bill_detail_id = b. bill_detail_id AND b.status = 1
AND b.acct_cycle = '%s' AND b.province_code = '%s' AND b.city_code = '%s')
WHERE EXISTS (SELECT '1' FROM t_writeoff_record b WHERE a.bill_detail_id = b. bill_detail_id AND b.status = 1
AND b.acct_cycle = '%s' AND b.province_code = '%s' AND b.city_code = '%s');
1000条数据没问题,
10000条数据用了2分钟,但是期间操作很容易就死掉
100000条数据,直接就死了,一个小时都没响应
该如何优化?
还有循环插入数据的问题,
插100000条数据总是到10000多条就卡主了
这个该如何做
select *,
a.writeoff_fee = a.writeoff_fee +
(SELECT SUM(b.writeoff_fee) FROM t_writeoff_record b WHERE a.bill_detail_id = b. bill_detail_id AND b.status = 1
AND b.acct_cycle = '%s' AND b.province_code = '%s' AND b.city_code = '%s')
from t_bill_detail a
WHERE EXISTS (SELECT '1' FROM t_writeoff_record b WHERE a.bill_detail_id = b. bill_detail_id AND b.status = 1
AND b.acct_cycle = '%s' AND b.province_code = '%s' AND b.city_code = '%s');
create temporary table t_writeoff_record_temp
select SUM(b.writeoff_fee) FROM t_writeoff_record b
group by b.acct_cycle, b.province_code, b.city_code;另外,我觉得没必要加WHERE EXISTS条件
select SUM(b.writeoff_fee),b.acct_cycle, b.province_code, b.city_code
FROM t_writeoff_record b
group by b.acct_cycle, b.province_code, b.city_code;sorry,写错了
还是慢,用的Limit,有什么好方法
是优化的么?结果出来后没明白什么意思,如下
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY a ALL 100000 Using where
3 DEPENDENT SUBQUERY b index PRIMARY 238 100000 Using where; Using index
2 DEPENDENT SUBQUERY b ALL 100000 Using where
数据库关闭或者连接断开都会清除内存表,不过先建立内存表再去update会快一些,尤其是如果还建立了索引