批量执行SQL文件,统计每个文件的执行时间
当有一批sql文件需要实行的时候,每个文件执行的时间不一样, 上线之前需要记录运行时间,如果sql文件不能并行执行必须按照顺序执行(假设已经通过测试不会出错),那么就得守在终端前面等待一个sql执行完之后再执行下一个。将所有文件名集中在一个sql里面执行可以省下大把的时光。并且还能记录每个文件执行的时间点。
1、将所有sql文件名写到一个sql文件里,用 ls > all.sql ,打开文件需要删除 all.sql 行 ,如下:
2、利用sed编辑文件
sed 's/\([^;]*\.sql\)/set @start:=unix_timestamp(),@sqlname="\1";source \1/; s/$/; select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime /g ' all.sql
3、结果如下,执行该脚本即可:
1 select @start:=unix_timestamp(),@sqlname:="optimize-1-role.sql";source optimize-1-role.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime; 2 select @start:=unix_timestamp(),@sqlname:="optimize-2-couple.sql";source optimize-2-couple.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime; 3 select @start:=unix_timestamp(),@sqlname:="optimize-2-dance-group.sql";source optimize-2-dance-group.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime; 4 select @start:=unix_timestamp(),@sqlname:="optimize-2-event.sql";source optimize-2-event.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime; 5 select @start:=unix_timestamp(),@sqlname:="optimize-2-friend.sql";source optimize-2-friend.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime; 6 select @start:=unix_timestamp(),@sqlname:="optimize-2-item.sql";source optimize-2-item.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime; 7 select @start:=unix_timestamp(),@sqlname:="optimize-2-magic-lamp.sql";source optimize-2-magic-lamp.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime; 8 select @start:=unix_timestamp(),@sqlname:="optimize-2-mail.sql";source optimize-2-mail.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime; 9 select @start:=unix_timestamp(),@sqlname:="optimize-2-medal-progress.sql";source optimize-2-medal-progress.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime; 10 select @start:=unix_timestamp(),@sqlname:="optimize-2-medal.sql";source optimize-2-medal.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime; 11 select @start:=unix_timestamp(),@sqlname:="optimize-2-other-2.sql";source optimize-2-other-2.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime; 12 select @start:=unix_timestamp(),@sqlname:="optimize-2-other.sql";source optimize-2-other.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime; 13 select @start:=unix_timestamp(),@sqlname:="optimize-2-player.sql";source optimize-2-player.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime; 14 select @start:=unix_timestamp(),@sqlname:="optimize-2-quest.sql";source optimize-2-quest.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime; 15 select @start:=unix_timestamp(),@sqlname:="optimize-3-rank.sql";source optimize-3-rank.sql;select @sqlname,sec_to_time(@end:=unix_timestamp() - @start) as costtime;