mysql更新10万条数据卡主

[terhack]

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多条就卡主了
这个该如何做

[hyrongg]

分批次update或者insert,每次1000条，做完了就commit;

[terhack]

引用 1 楼 hyrongg 的回复:

分批次update或者insert,每次1000条，做完了就commit;

关键怎么分批次？a,b表一次全部关联出来了，有什么好的办法

[WWWWA]

explain 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');

[ACMAIN_CHM]

分批执行吧。 另外关闭事务。

[hithink]

建立内存表，

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条件

[hithink]

create temporary table t_writeoff_record_temp
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,写错了

[terhack]

引用 4 楼 ACMAIN_CHM 的回复:

分批执行吧。 另外关闭事务。

还是慢，用的Limit，有什么好方法

[terhack]

引用 6 楼 hithink 的回复:

SQL code?1234create temporary table t_writeoff_record_tempselect SUM(b.writeoff_fee),b.acct_cycle, b.province_code, b.city_codeFROM t_writeoff_record bgroup by b.acct_cycle, b.province_co……

数据库关闭数据容易丢失？而且数据量很大会不会出现内存不够用的情况？

[terhack]

引用 3 楼 WWWWA 的回复:

explain
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_cy……

是优化的么？结果出来后没明白什么意思,如下


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

[hithink]

引用 8 楼 terhack 的回复:

引用 6 楼 hithink 的回复:SQL code?1234create temporary table t_writeoff_record_tempselect SUM(b.writeoff_fee),b.acct_cycle, b.province_code, b.city_codeFROM t_writeoff_record bgroup by b.acct_c……

数据库关闭或者连接断开都会清除内存表，不过先建立内存表再去update会快一些，尤其是如果还建立了索引