mysql 大量数据删除插入变慢的原因?

帅狗 发布于 2016/05/08 08:45
阅读 1K+
收藏 0

用java对表一次性操作包括根据id删除和保存操作,每次保存三条数据。

表四十万数据,程序刚开始挺快的,不一会就插入几千条数据,现在数据库90多万条吧,但是却很慢了,差不多一秒执行一次,引擎是innodb,程序不要求事物。

是不是因为删除操作影响了速度?

以前没有删除操作的时候程序跑一晚上就执行完了,现在有删除操作都执行三天了,数据还没执行到七分之二。

请问该如何优化?

用的是jdbc+dbutils

以下是代码,忽略代码美观,这代码只为处理数据用下

import java.net.InetAddress;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

public class IPconfigTest extends Thread{

	//test
	public static void main(String[] args) {
//		findData();
//		ipchange2("1.0.0.255");
//		IPconfigTest ip = new IPconfigTest();
//		ip.start();
	}

	static Connection conn = DBUtils.getConnection();
	
	static QueryRunner runner = new QueryRunner();
	public void run(){
		
		//查询sql
		String findIpCountrySql =  "SELECT "+
					  "ipc.id AS ipcid ,"+
//					  "ipc.insert_time AS ipcinserttime ,"+
//					  "ipc.last_update_time AS ipclastupdatetime ,"+
					  "ipc.start_str AS ipcstartstr,"+
					  "ipc.end_str AS ipcendstr,"+
					  "ipc.start_int AS ipcstartint ,"+
					  "ipc.end_int AS ipcendint ,"+
					  "ipc.continent AS ipccontinent ,"+
					  "ipc.country_zh AS ipccountryzh ,"+
					  "ipc.province AS ipcprovince ,"+
					  "ipc.city AS ipccity ,"+
					  "ipc.county AS ipccounty ,"+
//					  "ipc.sp AS ipcsp ,"+
					  "ipc.area_code AS ipcareacode ,"+
					  "ipc.country_en AS ipccountryen ,"+
					  "ipc.country_short AS ipccountryshort "+
//					  "ipc.longitude AS ipclongitude ,"+
//					  "ipc.latitude AS ipclatitude "+
					"FROM "+
					  " ip_country ipc";
		
		//执行查询
		try {
			List<IP> ipcountrys = (List<IP>)runner.query(conn, findIpCountrySql, new BeanListHandler<IP>(IP.class));
			
			if( null!=ipcountrys && ipcountrys.size()>0 ){
				
				for ( int i = 0; i < ipcountrys.size(); i++ ) {
					IP ipcountry = ipcountrys.get(i);
					String ipcstartint = ipcountry.getIpcstartint();
					String ipcendint = ipcountry.getIpcendint();
					
					
//					while(true){
						String sql = "select ipa.id as ipaid,ipa.start_str as ipastartstr,"
								+ "ipa.end_str as ipaendstr,"
								+ "ipa.start_int as ipastartint,"
								+ "ipa.end_int as ipaendint,"
								+ "ipa.longitude AS ipalongitude,"
								+ "ipa.latitude AS ipalatitude"
								+ " from ip_address_copy2 ipa where "
								+ ""+ipcstartint+" >= ipa.start_int and"
										+ " "+ipcendint+" <=ipa.end_int";
						List<IP> ipaddresses = (List<IP>)runner.query(conn, sql, new BeanListHandler<IP>(IP.class));
						
						if( null!=ipaddresses && ipaddresses.size()>0 ){
//							System.err.println("ipaddresses.size="+ipaddresses.size());
							if(ipaddresses.size()>1){
								try {
//									throw new Exception("size大于�?��");
								} catch (Exception e) {
									// TODO Auto-generated catch block
									e.printStackTrace();
								}
							}
							for (int j = 0; j < ipaddresses.size(); j++) {
								IP ipaddress = ipaddresses.get(j);
								//拆分
								System.err.println("i="+i+" j="+j);
								chaifenIPandSave(ipaddress,ipcountry);
								break;
							}
							
						}else{
							save(ipcountry.getIpcstartstr(),
									ipcountry.getIpcendstr(),
									ipcountry.getIpcstartint(),
									ipcountry.getIpcendint(),
									ipcountry.getIpccontinent(),
									ipcountry.getIpccountryzh(),
									ipcountry.getIpcprovince(),
									ipcountry.getIpccity(),
									ipcountry.getIpccounty(),
									ipcountry.getIpcareacode(),
									ipcountry.getIpccountryen(),
									ipcountry.getIpccountryshort(),
									ipcountry.getIpclongitude(),
									ipcountry.getIpclatitude());
						}
//					}
					
					
					
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	
	}

	private static void chaifenIPandSave(IP ipaddress, IP ipcountry) {
		if( 
			null!=ipaddress.getIpastartint()
			&&
			null!=ipcountry.getIpcstartint()
			&&
			ipaddress.getIpastartint().equals(ipcountry.getIpcstartint())
				){
			//满足以上条件拆分2段ip
			save(
					ipcountry.getIpcstartstr(),
					ipchange2( (Integer.parseInt(ipcountry.getIpcendint())-1)+"" ),
					ipcountry.getIpcstartint(),
					(Integer.parseInt(ipcountry.getIpcendint())-1)+"",
					ipcountry.getIpccontinent(),
					ipcountry.getIpccountryzh(),
					ipcountry.getIpcprovince(),
					ipcountry.getIpccity(),
					ipcountry.getIpccounty(),
					ipcountry.getIpcareacode(),
					ipcountry.getIpccountryen(),
					ipcountry.getIpccountryshort(),
					ipaddress.getIpalongitude(),
					ipaddress.getIpalatitude()
					);
			save(
					ipcountry.getIpcendstr(),
					ipaddress.getIpaendstr(),
					ipcountry.getIpcendint(),
					ipaddress.getIpaendint(),
					ipcountry.getIpccontinent(),
					ipcountry.getIpccountryzh(),
					ipcountry.getIpcprovince(),
					ipcountry.getIpccity(),
					ipcountry.getIpccounty(),
					ipcountry.getIpcareacode(),
					ipcountry.getIpccountryen(),
					ipcountry.getIpccountryshort(),
					ipaddress.getIpalongitude(),
					ipaddress.getIpalatitude()
					);
			//删除原数数据
			String sql = "delete from ip_address_copy2 where id=?";
			try {
				runner.update(conn, sql, ipaddress.getIpaid());
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		} else if( 
				null!=ipaddress.getIpaendint()
				&&
				null!=ipcountry.getIpcendint()
				&&
				ipaddress.getIpaendint().equals(ipcountry.getIpcendint())
				){
			//满足以上条件拆分2段ip
			save(
					ipaddress.getIpastartstr(),
					ipchange2( (Integer.parseInt(ipcountry.getIpcstartint())-1)+"" ),
					ipaddress.getIpastartint(),
					(Integer.parseInt(ipcountry.getIpcstartint())-1)+"",
					ipcountry.getIpccontinent(),
					ipcountry.getIpccountryzh(),
					ipcountry.getIpcprovince(),
					ipcountry.getIpccity(),
					ipcountry.getIpccounty(),
					ipcountry.getIpcareacode(),
					ipcountry.getIpccountryen(),
					ipcountry.getIpccountryshort(),
					ipaddress.getIpalongitude(),
					ipaddress.getIpalatitude()
					);
			save(
					ipcountry.getIpcstartstr(),
					ipcountry.getIpcendstr(),
					ipcountry.getIpcstartint(),
					ipcountry.getIpcendint(),
					ipcountry.getIpccontinent(),
					ipcountry.getIpccountryzh(),
					ipcountry.getIpcprovince(),
					ipcountry.getIpccity(),
					ipcountry.getIpccounty(),
					ipcountry.getIpcareacode(),
					ipcountry.getIpccountryen(),
					ipcountry.getIpccountryshort(),
					ipaddress.getIpalongitude(),
					ipaddress.getIpalatitude()
					);
			//删除原数据
			String sql = "delete from ip_address_copy2 where id=?";
			try {
				runner.update(conn, sql, ipaddress.getIpaid());
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}else if(null!=ipcountry.getIpcstartint()
				&& null!=ipcountry.getIpcendint()
				&& null!=ipaddress.getIpastartint()
				&& null!=ipaddress.getIpaendint()){
			//满足以上条件拆分为三段ip
			save(
					ipaddress.getIpastartstr(),
					ipchange2((Integer.parseInt(ipcountry.getIpcstartint())-1)+""),
					ipaddress.getIpastartint(),
					(Integer.parseInt(ipcountry.getIpcstartint())-1)+"",
					ipcountry.getIpccontinent(),
					ipcountry.getIpccountryzh(),
					ipcountry.getIpcprovince(),
					ipcountry.getIpccity(),
					ipcountry.getIpccounty(),
					ipcountry.getIpcareacode(),
					ipcountry.getIpccountryen(),
					ipcountry.getIpccountryshort(),
					ipaddress.getIpalongitude(),
					ipaddress.getIpalatitude()
					);
			save(
					ipcountry.getIpcstartstr(),
					ipchange2((Integer.parseInt(ipcountry.getIpcendint())-1)+""),
					ipcountry.getIpcstartint(),
					(Integer.parseInt(ipcountry.getIpcendint())-1)+"",
					ipcountry.getIpccontinent(),
					ipcountry.getIpccountryzh(),
					ipcountry.getIpcprovince(),
					ipcountry.getIpccity(),
					ipcountry.getIpccounty(),
					ipcountry.getIpcareacode(),
					ipcountry.getIpccountryen(),
					ipcountry.getIpccountryshort(),
					ipaddress.getIpalongitude(),
					ipaddress.getIpalatitude()
					);
			save(
					ipcountry.getIpcendstr(),
					ipaddress.getIpaendstr(),
					ipcountry.getIpcendint(),
					ipaddress.getIpaendint(),
					ipcountry.getIpccontinent(),
					ipcountry.getIpccountryzh(),
					ipcountry.getIpcprovince(),
					ipcountry.getIpccity(),
					ipcountry.getIpccounty(),
					ipcountry.getIpcareacode(),
					ipcountry.getIpccountryen(),
					ipcountry.getIpccountryshort(),
					ipaddress.getIpalongitude(),
					ipaddress.getIpalatitude()
					);
			//删除原数据
			String sql = "delete from ip_address_copy2 where id=?";
			try {
				runner.update(conn, sql, ipaddress.getIpaid());
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void save(
			String startstr,
			String endstr,
			String startint,
			String endint,
			String continent,
			String countryzh,
			String province,
			String city,
			String county,
			String areacode,
			String countryen,
			String countryshort,
			String longitude,
			String latitude
			){
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH🇲🇲ss");
		String insert = "insert into ip_address_copy2("
				+ "insert_time,"
				+ "start_str,"
				+ "end_str,"
				+ "start_int,"
				+ "end_int,"
				+ "continent,"
				+ "country_zh,"
				+ "province,"
				+ "city,"
				+ "county,"
//				+ "sp,"
				+ "area_code,"
				+ "country_en,"
				+ "country_short,"
				+ "longitude,"
				+ "latitude"
				+ ") values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
		System.err.println("sql="+insert);
		try {
			int i = runner.update(conn, insert,
					sdf.format(new Date()),
					startstr,
					endstr,
					startint,
					endint,
					continent,
					countryzh,
					province,
					city,
					county,
					areacode,
					countryen,
					countryshort,
					longitude,
					latitude
					);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private static String ipchange(String ipAddr) {
		int toint = 0;
		 try {
	            toint = bytesToInt(ipToBytesByInet(ipAddr));
	        } catch (Exception e) {
	            throw new IllegalArgumentException(ipAddr + " is invalid IP");
	        }
		return intToIp(toint+1);
	}
	
	private static String ipchange2(String ipAddr) {
		int toint = 0;
		 try {
	            toint = bytesToInt(ipToBytesByInet(ipAddr));
	        } catch (Exception e) {
	            throw new IllegalArgumentException(ipAddr + " is invalid IP");
	        }
		return intToIp(toint);
	}

	private static void insertIP(IP nip1) {
		//insert
		//...
		
	}
	 /**
     * 把IP地址转化为字节数�?
     * @param ipAddr
     * @return byte[]
     */
	public static byte[] ipToBytesByInet(String ipAddr) {
        try {
            return InetAddress.getByName(ipAddr).getAddress();
        } catch (Exception e) {
            throw new IllegalArgumentException(ipAddr + " is invalid IP");
        }
	 }
	 /**
     * 根据位运算把 byte[] -> int
     * @param bytes
     * @return int
     */
    public static int bytesToInt(byte[] bytes) {
        int addr = bytes[3] & 0xFF;
        addr |= ((bytes[2] << 8) & 0xFF00);
        addr |= ((bytes[1] << 16) & 0xFF0000);
        addr |= ((bytes[0] << 24) & 0xFF000000);
        return addr;
    }
    /**
     * 把int->ip地址
     * @param ipInt
     * @return String
     */
    public static String intToIp(int ipInt) {
        return new StringBuilder().append(((ipInt >> 24) & 0xff)).append('.')
                .append((ipInt >> 16) & 0xff).append('.').append(
                        (ipInt >> 8) & 0xff).append('.').append((ipInt & 0xff))
                .toString();
    }
}




加载中
0
开源中国首席公关
开源中国首席公关
不要求事务,改INNODB为MYISAM
0
chenzengpeng
chenzengpeng

1. 改为批量删除及插入

2. 在数据库负载不高的情况下,引入多线程

返回顶部
顶部