论坛徽章:: 1

电梯直达

1楼 [收藏(0)] [报告]

发表于 2017-04-09 21:50 |只看该作者 |倒序浏览

本帖最后由 gtuiw 于 2017-04-09 21:52 编辑

各位好！

  有shell一个脚本，目的是将远程数据库的数据拉下来写到本地文件。需要拉下来的数据大概有几百万条

  运行了3个小时有6万条数据，之后就放着不管了，2天过去了，登录看了下数据只有30万条。觉得是脚本有效率问题，导致越来越慢。

  下面是运行的脚本，麻烦看下这脚本是不是有问题，还有优化的空间吗？

  先谢了！

#!/usr/bin/env bash
local_mysql='mysql --login-path=local --host=127.0.0.1 -sN -e '
meizu_mysql='mysql --login-path=meizu --host=192.3.XX.XX -sN -e '
declare -a db_name=(“db_1” “db2” “db3” “db4”)
for (( dbn=0; dbn<${#db_name[@]}; dbn++ ))
do
declare db_in_temp="tr.${db_name[dbn]}_in_temp"
declare db_out_temp="tr.${db_name[dbn]}_out_temp"
business_list=`${meizu_mysql}"select distinct business from netflow_${db_name[dbn]}_in_business_cache where clock >= unix_timestamp(subdate(NOW(), INTERVAL 30 DAY)) and clock < unix_timestamp(subdate(NOW(), 1)) and carriers = \"1\";"`
for p_business in `echo ${business_list}`
do
for((carrier=1; carrier<=4; carrier++))
do
count_in_temp=`${meizu_mysql}"SELECT COUNT(*) from (SELECT SUM(value) value, business, carriers, from_unixtime(600*ROUND(clock/(10*60)),'%Y-%m-%d %H:%i') time_stamp, clock FROM netflow_${db_name[dbn]}_in_business_cache WHERE clock < UNIX_TIMESTAMP() and clock >= UNIX_TIMESTAMP(subdate(current_date, 60)) and business = \"$p_business\" and carriers = \"$carrier\" GROUP BY time_stamp)c;"`
count_in=`echo | awk -v count_i=${count_in_temp} '{print int(count_i * 0.05)}'`
business_in_temp=`${meizu_mysql}"SELECT SUM(value) value, business, carriers, from_unixtime(600*ROUND(clock/(10*60)),'%Y-%m-%d %H:%i') time_stamp, clock FROM netflow_${db_name[dbn]}_in_business_cache WHERE clock < UNIX_TIMESTAMP() and clock >= UNIX_TIMESTAMP(subdate(current_date, 60)) and business = \"$p_business\" and carriers = \"$carrier\" GROUP BY time_stamp order by value DESC LIMIT ${count_in},${count_in_temp};"`
count_out_temp=`${meizu_mysql}"SELECT COUNT(*) from (SELECT SUM(value) value, business, carriers, from_unixtime(600*ROUND(clock/(10*60)),'%Y-%m-%d %H:%i') time_stamp, clock FROM netflow_${db_name[dbn]}_out_business_cache WHERE clock < UNIX_TIMESTAMP() and clock >= UNIX_TIMESTAMP(subdate(current_date, 60)) and business = \"$p_business\" and carriers = \"$carrier\" GROUP BY time_stamp)c;"`
count_out=`echo | awk -v count_i=${count_out_temp} '{print int(count_i * 0.05)}'`
business_out_temp=`${meizu_mysql}"SELECT SUM(value) value, business, carriers, from_unixtime(600*ROUND(clock/(10*60)),'%Y-%m-%d %H:%i') time_stamp, clock FROM netflow_${db_name[dbn]}_out_business_cache WHERE clock < UNIX_TIMESTAMP() and clock >= UNIX_TIMESTAMP(subdate(current_date, 60)) and business = \"$p_business\" and carriers = \"$carrier\" GROUP BY time_stamp order by value DESC LIMIT ${count_out},${count_out_temp};"`
echo "${business_in_temp}" | while read in_line
do
business_n=`echo ${in_line} | awk '{print $2}'`
bindwidth=`echo ${in_line} | awk '{printf("%.2f", $1/1024/1024)}'`
time_n=`echo ${in_line} |awk '{print $4, $5}'`
echo "${business_n},${bindwidth},${time_n},${carrier}" >> /tmp/${db_in_temp}
done
echo "${business_out_temp}" | while read out_line
do
business_out=`echo ${out_line} | awk '{print $2}'`
bindwidth_out=`echo ${out_line} | awk '{printf("%.2f", $1/1024/1024)}'`
time_out=`echo ${out_line} |awk '{print $4, $5}'`
echo "${business_out},${bindwidth_out},${time_out},${carrier}" >> /tmp/${db_out_temp}
done
done
done
done