一. 使用insert .. select 优化innodb大表(6000万)的delete操作:
1.create table bought_detail_bymonth_201502_new like bought_detail_bymonth_201502;
2. insert into ....select
图丢了,筛选后1000w执行 17分钟
insert into bought_detail_bymonth_201502_new select * from bought_detail_bymonth_201502 where gift_type != 0;
3. drop table
drop table bought_detail_bymonth_201502;
4. rename
rename table bought_detail_bymonth_201502_new to bought_detail_bymonth_201502;
普通delete
执行了3个半小时
二:screen命令-管理远程会话
之前不知道这个命令,执行脚本时一直傻傻等待,特别是网络终端、锁屏等造成的任务中断痛心不已,见到screen命令感觉如获至宝,相见恨晚。
screen [-S 名称] 启动一个会话
screen -ls 查看所有会话
ctrl-a + d退出会话
screen -r 对应id 进入会话
三:shell脚本处理文件
// 将文件名称整理到一个文件
ls /tmp/* /tmp/a.txt
//批量执行这些文件
#!/bin/bash
for line in `cat /tmp/t.list`
do
echo $line;
echo `date`;
mysql -h 10.10.11.34 -uxie -pSH2bdDHTy5N3QnMj payment -A --local-infile -e " LOAD DATA LOCAL INFILE '/home/log/dumpsql/${line}' INTO TABLE bought_detail_forfree_old FIELDS TERMINATED BY ',' ENCLOSED BY \"'\" LINES TERMINATED BY '\n' (@a, userid,workid,receiver,gift_type,amount,remark,create_time) "
done