改为如下试试。 update a ,(select id,Count(*) as c from B where `view`>1000 group by id) t set a.hot = t.c where a.uid=t.id 如果仍慢,则贴出explain select * from a ,(select Id,count(*) as c from B where view>1000 group by id) t where a.uid=t.id
explain select * from a ,(select Id,count(*) as c from B where view>1000 group by id) t where a.uid=t.id 执行结果: id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra 1,PRIMARY,<derived2>,ALL,NULL,NULL,NULL,NULL,59388, 1,PRIMARY,A,eq_ref,PRIMARY,PRIMARY,8,t.id,1,Using where 2,DERIVED,B,range,view,view,5,NULL,604164,Using where; Using index; Using temporary; Using filesort
update a ,(select id,Count(*) as c from B where `view`>1000 group by id) t set a.hot = t.c where a.uid=t.id这条语句瞬间就完成了
贴出你的show index from B; show index from a;
show index from B; show index from a; Table,Non_unique,Key_name,Seq_in_index,Column_name,Collation,Cardinality,Sub_part,Packed,Null,Index_type,Comment B,0,PRIMARY,1,itemid,A,7493449,NULL,NULL,,BTREE, B,0,PRIMARY,2,id,A,7493449,NULL,NULL,,BTREE, B,1,view,1,view,A,33754,NULL,NULL,YES,BTREE,Table,Non_unique,Key_name,Seq_in_index,Column_name,Collation,Cardinality,Sub_part,Packed,Null,Index_type,Comment A,0,PRIMARY,1,uid,A,688745,NULL,NULL,,BTREE,
B表有点大。你可以使用explain看一下。 你那个update语句,他会不停的去读取B表数据。
改成这样就不会不停的读了,是吗? update a ,(select id,Count(*) as c from B where `view`>1000 group by id) t set a.hot = t.c where a.uid=t.id
update a ,(select id,Count(*) as c from B where `view`>1000 group by id) t
set a.hot = t.c
where a.uid=t.id
如果仍慢,则贴出explain select * from a ,(select Id,count(*) as c from B where view>1000 group by id) t
where a.uid=t.id
where a.uid=t.id
执行结果:
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,<derived2>,ALL,NULL,NULL,NULL,NULL,59388,
1,PRIMARY,A,eq_ref,PRIMARY,PRIMARY,8,t.id,1,Using where
2,DERIVED,B,range,view,view,5,NULL,604164,Using where; Using index; Using temporary; Using filesort
set a.hot = t.c
where a.uid=t.id这条语句瞬间就完成了
贴出你的show index from B; show index from a;
Table,Non_unique,Key_name,Seq_in_index,Column_name,Collation,Cardinality,Sub_part,Packed,Null,Index_type,Comment
B,0,PRIMARY,1,itemid,A,7493449,NULL,NULL,,BTREE,
B,0,PRIMARY,2,id,A,7493449,NULL,NULL,,BTREE,
B,1,view,1,view,A,33754,NULL,NULL,YES,BTREE,Table,Non_unique,Key_name,Seq_in_index,Column_name,Collation,Cardinality,Sub_part,Packed,Null,Index_type,Comment
A,0,PRIMARY,1,uid,A,688745,NULL,NULL,,BTREE,
你那个update语句,他会不停的去读取B表数据。
update a ,(select id,Count(*) as c from B where `view`>1000 group by id) t
set a.hot = t.c
where a.uid=t.id