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();
}
}