mysql 性能优化(数据导入)

mysql数据导入性能优化（代码级别）
1. 使用preparedStatment
2. 使用批量处理，一次提交
3. mysql 批量插入数据添加参数rewriteBatchedStatements时，性能比不添加时要最高可提高10倍以上的速度。URL如下：
jdbc:mysql://%IP%:3306/%SID%?rewriteBatchedStatements=true
4. 使用load data infile
   a) 使用load data infile SQL如下：
LOAD DATA LOCAL INFILE 'file/loader_data_file' INTO TABLE test_key_value CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' (`key`,`value`);
    1. (`key`,`value`) 为导入指定字段，若不写，默认全部导入。
     2. TERMINATED BY '\r\n', 在这里要注意最后一个字段的值是否正确，有时候最后field value 是'/r'，有时候会出现只能插入一行值。
   b) NULL和“NULL”的插入
        在导入语句中 使用 ENCLOSED BY '\"'， 此时若文件中有NULL值，则插入数据库是NULL对象，若文件中的值是“NULL”, 则，插入数据库中的值是NULL字符串
    c) 一般情况下field值在文件中使用英文逗号分割，对应sql中是 FIELDS TERMINATED BY ','
   d) 设置字符编码格式: CHARACTER SET utf8 注意此处是utf8 而不是UTF-8,
data file demo:

1,key1,value1
2,key2,value2
3,key3,value3
4,key4,value4

Load data infile Demo:
  1. create table:
 

CREATE TABLE `test_key_value` (
  `id` bigint(20) NOT NULL,
  `key` varchar(32) DEFAULT NULL,
  `value` varchar(128) DEFAULT NULL
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;

2. src:

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestJDBCTemp {

	public static void main(String[] args) throws Exception {
		generateData();
		test_mysql_load_data_infile();
	}

	public static void test_mysql_load_data_infile() {
		String url = "jdbc:mysql://10.46.24.93:3306/GHS_RULE_LUCIYU?rewriteBatchedStatements=true";
		String userName = "ghs";
		String password = "acxiom";
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, userName, password);
			conn.setAutoCommit(false);
			String sql = "LOAD DATA LOCAL INFILE 'file/loader_data_file' INTO TABLE test_key_value FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n'";
			PreparedStatement prest = conn.prepareStatement(sql);
			prest.execute();
			conn.commit();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void generateData() throws Exception {
		File file1 = new File("file/loader_data_file");
		BufferedWriter writer1 = new BufferedWriter(new OutputStreamWriter(
				new FileOutputStream(file1), "UTF-8"));
		for (int i = 1; i < 5; i++) {
			writer1.append(i + "");
			writer1.append(",key" + i);
			writer1.append(",value" + i);
			writer1.newLine();
		}
		writer1.flush();
		writer1.close();
	}
}