针对MyISAM引擎百万级甚至亿级数据量表高效率更新,可以采用 select into outfile方式将所需数据进行产出文件, 采用load data infile对产出文件进行导入操作。具体操作与解释如下:
1.编写数据更新运行shell脚本a_run.sh
#!/bin/sh
starttime=`date +'%Y-%m-%d %H:%M:%S'` #记录开始时间
echo "正在导出a表所需信息";
rm -f /home/a_old.dat #删除之前存在的产出文件a_old.dat
sh /home/export_shell/export_a.sh $database #执行导出a.dat文件脚本 $database为所选数据库名
wait; #等待导出脚本执行完毕
if [ $? -eq 0 ];then #执行成功后
wc -l /home/a_new.dat #查看导出脚本a_new.dat行数
echo "a_new.dat export success path:/home/a_new.dat";
else
echo "fail";
exit;
fi
echo "创建a_new表==>sh /home/create_a_new.sh $database";
sh /home/create_a_new.sh $database
wait;
if [ $? -eq 0 ];then
echo "create a_new success";
else
echo "fail";
exit;
fi
echo "刷新a_new表==>sh /home/flush_a_new.sh $database";
sh /home/flush_a_new.sh $database
wait;
echo "删除a_new表索引"
myisamchk --keys-used=0 -rq /home/database/$database/a_new
wait;
echo "插入数据";
sh /home/import_shell/insert_a_new.sh $database
wait;
if [ $? -eq 0 ];then
echo "a_new import success";
else
echo "fail";
exit;
fi
echo "插入数据成功后重新组建a_new表索引";
myisamchk -rf /home/database/$database/a_new --sort_buffer_size=10G
wait;
echo "再次刷新a_new表";
sh /home/flush_a_new.sh $database
wait;
echo "备份a_old到a_old_copy"
mysql -uuser -ppwd <<EOF #连接数据库,进行旧表a的备份,并重命名新表a到旧表a
USE $database;
RENAME TABLE a_old_name TO a_old_name_copy,
a_new TO a_old_name,
a_old_name_copy TO a_new;
EOF
endtime=`date +'%Y-%m-%d %H:%M:%S'`
start_seconds=$(date --date="$starttime" +%s);
end_seconds=$(date --date="$endtime" +%s);
echo "本次运行时间: "$((end_seconds-start_seconds))"s";
echo "结束时间:"$(endtime);
2.利用 select into outfile编写导出数据shell脚本export_a.sh
#!/bin/bash
mysql -uuse -ppwd <<! #连接数据库
USE ${1}; #设置数据库名参数
SELECT t1.id,substring_index(substring_index(REPLACE(REPLACE(t1.company_org_type,'(','('),')',')'),')',1),'(',-1),REPLACE(REPLACE(IFNULL(t2.status_value,''), CHAR(10), ''), CHAR(13), ''),IF(t1.company_org_type LIKE '%个体%' OR t1.company_org_type in('个人','个人经营'),1,2),REPLACE(REPLACE(IFNULL(t3.province_id,''), CHAR(10), ''), CHAR(13), '')
INTO OUTFILE '/home/a_new.dat' FIELDS TERMINATED BY '\t' ESCAPED BY '' OPTIONALLY ENCLOSED BY '^K' LINES TERMINATED BY '\n' #定义输出文件名
FROM company t1 LEFT JOIN reg_status t2 ON(t1.reg_status=t2.reg_status) LEFT JOIN company_base_code t3 ON(t1.base = t3.base);
!
exit;
3.利用 load data infile编写导入数据shell脚本 insert_a_new.sh
#!/bin/bash
mysql -uuser -ppwd <<EOF
use ${1};
LOAD DATA INFILE "/home/a_new.dat" REPLACE INTO TABLE a_new FIELDS TERMINATED BY '\t' ESCAPED BY '' OPTIONALLY ENCLOSED BY '^K' LINES TERMINATED BY '\n'
(@column_names1,@column_names2,@column_names3,@column_names4,@column_names5,@update_time)
set
column_names1=if(@column_names1='',NULL,@column_names1),
column_names2=if(@column_names2='',NULL,@column_names2),
column_names3=if(@column_names3='',NULL,@column_names3),
column_names4=if(@column_names4='',NULL,@column_names4),
column_names5=if(@column_names5='',NULL,@column_names5),
update_time=now(); #插入录入时间;
EOF
exit;
4.编写创建表shell脚本create_a_new.sh
#!/bin/bash
mysql -uuser -ppwd <<EOF
USE ${1};
CREATE TABLE IF NOT EXISTS a_new LIKE a_old; #复制a_old表结构
TRUNCATE TABLE a_new; #若存在则清空数据
EOF
5.编写刷新表shell脚本flush_a_new.sh
#!/bin/sh
mysql -uuser -ppwd <<EOF
USE ${1};
FLUSH TABLE a_new;
EOF
6.输入crontab -e设置定时
40 15 * * * sh /home/a_run.sh
crontab时间格式说明
minute(分)可以设置0-59分
hour(小时)可以设置0-23小时
day of month(日期)可以设置1-31号
month(月份):可以设置1-12月
day of week(星期):可以设置0-7星期几,其中0和7都代表星期天,或者也可以使用名称来表示星期天到星期一,例如sun表示星期天,mon表示星期一等等
crontab范例
每五分钟执行 */5 * * * *
每小时执行 0 * * * *
每天执行 0 0 * * *
每周执行 0 0 * * 0
每月执行 0 0 1 * *
每年执行 0 0 1 1 *