数据库的性能能问题

毕业设计要在数据库中插入200,000条数据。使用两种方法分别插入数据，结构的性能对比让我非常的吃惊。前者插入200,，000条数据需要多月2个小时，后者仅仅需要2分钟。

前者的核心代码是：
for(index=0;index<(int)(sumnum*danxuan);index++){
				//难度
				int nandu=(int)((Math.random()*100)+1);//1---100
				int zhishidian=(int)(Math.random()*100+1);//1---100
				int zhangjie=(int)(Math.random()*10+1);//1---10
				sql="insert into  danxuan (id,nandu,zhishidian,zhangjie)value(?,?,?,?)";
				PreparedStatement ps=conn.prepareStatement(sql);
				ps.setInt(1, index);
				ps.setInt(2, nandu);
				ps.setInt(3, zhishidian);
				ps.setInt(4, zhangjie);
				System.out.println("I am here!danxuan"+index);
				ps.executeUpdate();
				System.out.println("aaa");
			}

这样在每一次插入数据都会起动一次磁盘，效率可想而知。

后者核心代码为：

sql="insert into  danxuan (id,nandu,zhishidian,zhangjie)value(?,?,?,?)";
			PreparedStatement ps=conn.prepareStatement(sql);
			int index=0;
			conn.setAutoCommit(false);
			for(index=0;index<(int)(sumnum*danxuan);index++){
				//难度
				int nandu=(int)((Math.random()*100)+1);//1---100
				int zhishidian=(int)(Math.random()*100+1);//1---100
				int zhangjie=(int)(Math.random()*10+1);//1---10
				ps.setInt(1, index);
				ps.setInt(2, nandu);
				ps.setInt(3, zhishidian);
				ps.setInt(4, zhangjie);
				System.out.println("I am here!danxuan"+index);
				System.out.println("aaa");
				ps.addBatch();
				if((index+1)%1000==0){
					ps.executeBatch();
					conn.commit();
					ps.clearBatch();
					System.out.println("commit one");
				}
			}

每次插入的数据都是1000条，效率大大的提高了。

附：

package lightblue;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
//import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.Statement;
//import java.sql.Statement;
public class ga {
	/*
	 * 试卷数据库参数
	 * sumnum:试题总数
	 * danxuan:单选题所占的比例
	 * duoxuan:多选题所占的比例
	 * tiankong：填空题所占的比例
	 * jianda：简答题所占的比例
	 * wenda：问答题所占的比例
	 * jisuan：计算题所占的比例
	 */
	int sumnum=200000;
	double danxuan=0.35;
	double duoxuan=0.35;
	double tiankong=0.15;
	double jianda=0.05;
	double wenda=0.05;
	double jisuan=0.05;
	/*
	 * 数据库连接参数
	 * addr:数据库地址
	 * usr：用户名
	 * pwd:密码
	 */
	Connection conn=null;
    String addr="jdbc:mysql://localhost:3306/zujuandb";
	String usr="root";
	String pwd="123456";
	String addr2="jdbc:mysql://localhost:3306/schemata";
	String addr3="jdbc:mysql://localhost:3306/test";
	
	/*
	 * addr数据库地址
	 * usr用户名
	 * pwd密码
	 */
	public  Connection getConnectionByJDBC(String addr,String usr,String pwd){
		try {
		Class.forName("com.mysql.jdbc.Driver");
		}catch(ClassNotFoundException e){
			System.out.println("装载驱动包出现异常!请查正！");
			e.printStackTrace();
		}
		try{
			conn=DriverManager.getConnection(addr, usr, pwd);
		}catch(SQLException e) {
			System.out.println("链接数据库发生异常!");
			e.printStackTrace();
		}
		return conn;
	}
	public void createdb(){
		String sql="";
		try{
			//ResultSet rs=null;
			//Statement stmt=null;
			//如果上面检测到了数据库的存在，则随机生成数据存入数据库中
			try{
				getConnectionByJDBC(addr,usr,pwd);
			}
			catch(Exception e){
				System.out.println("sss");
			}
			//清空元数据
			sql="delete from danxuan";
			Statement st=conn.createStatement();
			st.executeUpdate(sql);
			sql="delete from duoxuan";
			st.executeUpdate(sql);
			sql="delete from tiankong";
			st.executeUpdate(sql);
			sql="delete from jianda";
			st.executeUpdate(sql);
			sql="delete from wenda";
			st.executeUpdate(sql);
			sql="delete from jisuan";
			st.executeUpdate(sql);
			//单选题
			sql="insert into  danxuan (id,nandu,zhishidian,zhangjie)value(?,?,?,?)";
			PreparedStatement ps=conn.prepareStatement(sql);
			int index=0;
			conn.setAutoCommit(false);
			for(index=0;index<(int)(sumnum*danxuan);index++){
				//难度
				int nandu=(int)((Math.random()*100)+1);//1---100
				int zhishidian=(int)(Math.random()*100+1);//1---100
				int zhangjie=(int)(Math.random()*10+1);//1---10
				ps.setInt(1, index);
				ps.setInt(2, nandu);
				ps.setInt(3, zhishidian);
				ps.setInt(4, zhangjie);
				System.out.println("I am here!danxuan"+index);
				System.out.println("aaa");
				ps.addBatch();
				if((index+1)%1000==0){
					ps.executeBatch();
					conn.commit();
					ps.clearBatch();
					System.out.println("commit one");
				}
			}
			//多选题
			sql="insert into  duoxuan (id,nandu,zhishidian,zhangjie)value(?,?,?,?)";
			ps=conn.prepareStatement(sql);
			for(index=0;index<(int)(sumnum*duoxuan);index++){
				int nandu=(int)((Math.random()*100)+1);//1---100
				int zhishidian=(int)(Math.random()*100+1);//1---100
				int zhangjie=(int)(Math.random()*10+1);//1---10
				
				ps.setInt(1, index);
				ps.setInt(2, nandu);
				ps.setInt(3, zhishidian);
				ps.setInt(4, zhangjie);
				System.out.println("I am here !duoxuan"+index);
				ps.addBatch();
				if((index+1)%1000==0){
					ps.executeBatch();
					conn.commit();
					ps.clearBatch();
					System.out.println("commit one");
				}
			}
			//填空题
			sql="insert into  tiankong (id,nandu,zhishidian,zhangjie)value(?,?,?,?)";
			ps=conn.prepareStatement(sql);
			for(index=0;index<(int)(sumnum*tiankong);index++){
				int nandu=(int)((Math.random()*100)+1);//1---100
				int zhishidian=(int)(Math.random()*100+1);//1---100
				int zhangjie=(int)(Math.random()*10+1);//1---10
				
				ps.setInt(1, index);
				ps.setInt(2, nandu);
				ps.setInt(3, zhishidian);
				ps.setInt(4, zhangjie);
				System.out.println("I am here tiankong"+index);
				ps.addBatch();
				if((index+1)%1000==0){
					ps.executeBatch();
					conn.commit();
					ps.clearBatch();
					System.out.println("commit one");
				}			
			}
			//简答题
			sql="insert into  wenda (id,nandu,zhishidian,zhangjie)value(?,?,?,?)";
			ps=conn.prepareStatement(sql);
			for(index=0;index<(int)(sumnum*wenda);index++){
				int nandu=(int)((Math.random()*100)+1);//1---100
				int zhishidian=(int)(Math.random()*100+1);//1---100
				int zhangjie=(int)(Math.random()*10+1);//1---10
				
				ps.setInt(1, index);
				ps.setInt(2, nandu);
				ps.setInt(3, zhishidian);
				ps.setInt(4, zhangjie);
				System.out.println("I am here tiankong"+index);
				ps.addBatch();
				if((index+1)%1000==0){
					ps.executeBatch();
					conn.commit();
					ps.clearBatch();
					System.out.println("commit one");
				}
			}
			//问答题
			sql="insert into  jianda (id,nandu,zhishidian,zhangjie)value(?,?,?,?)";
			ps=conn.prepareStatement(sql);
			for(index=0;index<(int)(sumnum*jianda);index++){
				int nandu=(int)((Math.random()*100)+1);//1---100
				int zhishidian=(int)(Math.random()*100+1);//1---100
				int zhangjie=(int)(Math.random()*10+1);//1---10
				
				ps.setInt(1, index);
				ps.setInt(2, nandu);
				ps.setInt(3, zhishidian);
				ps.setInt(4, zhangjie);
				System.out.println("I am here wenda"+index);
				ps.addBatch();
				if((index+1)%1000==0){
					ps.executeBatch();
					conn.commit();
					ps.clearBatch();
					System.out.println("commit one");
				}
			}
			//计算题
			sql="insert into  jisuan (id,nandu,zhishidian,zhangjie)value(?,?,?,?)";
			ps=conn.prepareStatement(sql);
			for(index=0;index<(int)(sumnum*jisuan);index++){
				int nandu=(int)((Math.random()*100)+1);//1---100
				int zhishidian=(int)(Math.random()*100+1);//1---100
				int zhangjie=(int)(Math.random()*10+1);//1---10
				
				ps.setInt(1, index);
				ps.setInt(2, nandu);
				ps.setInt(3, zhishidian);
				ps.setInt(4, zhangjie);
				System.out.println("I am here jisuan"+index);
				ps.addBatch();
				if((index+1)%1000==0){
					ps.executeBatch();
					conn.commit();
					ps.clearBatch();
					System.out.println("commit one");
				}
			}
		}
		catch(Exception e){
			System.out.println("error");
		}
	}
	public  void test(){
		
		String sql="select * from student";
		getConnectionByJDBC(addr3,usr,pwd);
		try{
			sql="insert into student(id,name)value(?,?)";
			PreparedStatement  ps=conn.prepareStatement(sql);
			ps.setInt(1	, 12);
			ps.setString(2, "dd");
			ps.executeUpdate();
		}catch (SQLException e){
			System.out.println(e.getMessage());
			e.printStackTrace();
		}finally{
			try{
				if(conn!=null)
					conn.close();
			} catch(Exception e){
				System.out.println(e.getMessage());
				e.printStackTrace();
			}
		}
	}
	public static void main(String[]args){
		ga g=new ga();
		g.createdb();
		//g.test();
	}
}