delimiter // create procedure bu() begin declare done int default 0; declare temp_id int; declare sqlupdate varchar(2560); declare cur cursor for select distinct ServerID from online_time1; declare exit handler for not found set done = 1; open cur; quan:loop if done = 1 then leave quan; else fetch from cur into temp_id; set sqlupdate = concat('update player_dynamic_info_',temp_id,' as t1, online_time1 as t2 set t1.OnlineTimeAmount = t2.Online where t1.Datetime = t2.Date and t1.PlayerID = t2.PlayerID'); set @in2 = sqlupdate; prepare stmt from @in2; execute stmt; end if; end loop; end;//
PlayerID Datetime Date 上建立索引没有
都建了。 总表是分开建的 分表上建的是组合索引—— Datetime和PlayerID的。
online_time1:Date PlayerID建立索引没有
真的都建了。 只是索引的问题百度一下都解决了。 会不会还是表太大的问题? 总表有2亿条数据, 用双重游标太慢了? 我昨天放着跑了一晚上还卡在sending data
在MSYQL中直接执行SQL语句,看看速度如何,假设temp_id=1update player_dynamic_info_1' as t1, online_time1 as t2 set t1.OnlineTimeAmount = t2.Online where t1.Datetime = t2.Date and t1.PlayerID = t2.PlayerID
我在show processlist 里看的时候 它就是描述在执行update player_dynamic_info_1' as t1, online_time1 as t2 set t1.OnlineTimeAmount = t2.Online where t1.Datetime = t2.Date and t1.PlayerID = t2.PlayerID 这里 卡在sending data, 应该是一个意思吧?
delimiter // create procedure bu() begin declare done int default 0; declare done_old int default 0; declare temp_id int; declare sqlupdate varchar(2560); declare cur cursor for select distinct ServerID from online_time1; declare exit handler for not found set done = 1;
open cur; quan:loop if done = 1 then leave quan; else
fetch from cur into temp_id; set done_old=done; set sqlupdate = concat('update player_dynamic_info_',temp_id,' as t1, online_time1 as t2 set t1.OnlineTimeAmount = t2.Online where t1.Datetime = t2.Date and t1.PlayerID = t2.PlayerID'); set @in2 = sqlupdate; prepare stmt from @in2; execute stmt; set done=done_old; end if; end loop; end;//
建了索引的。 update 那个存储过程是只用了一个游标的。因为要把总表数据更新到分表 需要用一个游标记录分表的表名。 然后运行的时候就一直停留在sending data
用了双游标的的是另一个表的处理。
有四个字段 日期 用户ID 用户所在服务器ID 数据列
现在是用了一个游标记录服务器ID 来动态更改表名 依据分表和总表日期、用户ID相同 来更新分表中的数据列
delimiter //
create procedure bu()
begin
declare done int default 0;
declare temp_id int;
declare sqlupdate varchar(2560);
declare cur cursor for select distinct ServerID from online_time1;
declare exit handler for not found set done = 1;
open cur;
quan:loop
if done = 1 then
leave quan;
else
fetch from cur into temp_id;
set sqlupdate = concat('update player_dynamic_info_',temp_id,' as t1, online_time1 as t2 set t1.OnlineTimeAmount = t2.Online where t1.Datetime = t2.Date and t1.PlayerID = t2.PlayerID');
set @in2 = sqlupdate;
prepare stmt from @in2;
execute stmt;
end if;
end loop;
end;//
都建了。 总表是分开建的 分表上建的是组合索引—— Datetime和PlayerID的。
真的都建了。 只是索引的问题百度一下都解决了。 会不会还是表太大的问题? 总表有2亿条数据, 用双重游标太慢了? 我昨天放着跑了一晚上还卡在sending data
我在show processlist 里看的时候 它就是描述在执行update player_dynamic_info_1' as t1, online_time1 as t2 set t1.OnlineTimeAmount = t2.Online where t1.Datetime = t2.Date and t1.PlayerID = t2.PlayerID 这里 卡在sending data, 应该是一个意思吧?
难道是索引建的不对? 分表建的playerID DATETIME的联合索引 基数是2002 总表建的PLAYERID索引基数2200多万, date索引基数只有18?
create procedure bu()
begin
declare done int default 0;
declare done_old int default 0;
declare temp_id int;
declare sqlupdate varchar(2560);
declare cur cursor for select distinct ServerID from online_time1;
declare exit handler for not found set done = 1;
open cur;
quan:loop
if done = 1 then
leave quan;
else
fetch from cur into temp_id;
set done_old=done;
set sqlupdate = concat('update player_dynamic_info_',temp_id,' as t1, online_time1 as t2 set t1.OnlineTimeAmount = t2.Online where t1.Datetime = t2.Date and t1.PlayerID = t2.PlayerID');
set @in2 = sqlupdate;
prepare stmt from @in2;
execute stmt;
set done=done_old;
end if;
end loop;
end;//