如何提高Mysql 批量插入和更新 数据库的效

需要将大量数据(大概5W条)插入MySQL数
据库，用普通的SQL
Statement执行,时间大概是几分钟。于是想到用PreparedStatement，但是改了之后发现效率并没有很大的提升。不成，想到了
load data local
infile命令，以前一直认为这条命令仅限MySQL终端上使用而不是标准的SQL语句，今天看了几篇文章之后有了很大的收获。
1 使用PreparedStatement batch operation
以前使用PreparedStatement性能没有很大提升的原因在于：
没有使用批处理方法
在语句执行之前应关闭事务自动提交，语句执行完之后再提交
public
void batchLoad(Connection connection)
{
try
{
connectionsetAutoCommit(false);
BufferedReader reader =
new BufferedReader(new
FileReader("tfacts_result"));
String sqlString =
"insert into test(node1, node2, weight) values(, , )";
PreparedStatement pstmt = connectionprepareStatement(sqlString);
String line =
null;
while(true)
{
line = readerreadLine();
if(line == null)
{
break;
}
String[] columns = linesplit("\t");
for(int
i = 1; i <= columnslength; i++)
{
pstmtsetString(i, columns[i-1]);
}
pstmtaddBatch();
}
pstmtexecuteBatch();
connectioncommit();
pstmtclose();
readerclose();
}
catch (FileNotFoundException e) {
eprintStackTrace();
}catch
(SQLException e){
eprintStackTrace();
}catch
(IOException e){
eprintStackTrace();
}
2使用load data local infile into tabel XXX（注意在文件中用\t将每列数据隔开）
public
void loadData(Connection connection)
{
long
starTime = SystemcurrentTimeMillis();
String sqlString =
"load data local infile into table test";
PreparedStatement pstmt;
try
{
pstmt = connectionprepareStatement(sqlString);
pstmtsetString(1,
"tfacts_result");
pstmtexecuteUpdate();
pstmtclose();
}
catch (SQLException e) {
eprintStackTrace();
}
long
endTime = SystemcurrentTimeMillis();
Systemoutprintln("program runs "
+ (endTime - starTime) + "ms");
}
测试了5W条数据，PreparedStatement耗时10s，而load data infile耗时3s。

实现语句改成: Update [member] Set card='00' || card
PostgreSQL 81 中文文档:>//将数据库中的数据条数分段
public void division(){
//获取要导入的总的数据条数
String sql3="SELECT count() FROM [CMD][dbo][mycopy1]";
try {
pss=consprepareStatement(sql3);
rss=pssexecuteQuery();

while(rssnext()){
Systemoutprintln("总记录条数:"+rssgetInt(1));
sum=rssgetInt(1);
}
//每30000条记录作为一个分割点
if(sum>=30000){
n=sum/30000;
residue=sum%30000;
}else{
residue=sum;
}

Systemoutprintln(n+" "+residue);

} catch (SQLException e) {
// TODO Auto-generated catch block
eprintStackTrace();
}

}
线程类
public MyThread(int start,int end) {
thisend=end;
thisstart=start;
Systemoutprintln("处理掉余数");
try {

Systemoutprintln("--------"+ThreadcurrentThread()getName()+"------------");
ClassforName(SQLSERVERDRIVER);
Systemoutprintln("加载sqlserver驱动");
cons = DriverManagergetConnection(CONTENTS,UNS,UPS);
stas = conscreateStatement();
Systemoutprintln("连接SQLServer数据库成功！！");

Systemoutprintln("加载mysql驱动");
ClassforName(MYSQLDRIVER);
con = DriverManagergetConnection(CONTENT,UN,UP);
sta = concreateStatement();
// 关闭事务自动提交
consetAutoCommit(false);
Systemoutprintln("连接mysql数据库成功！！");

} catch (Exception e) {
eprintStackTrace();
}
// TODO Auto-generated constructor stub
}
public ArrayList<Member> getAll(){
Member member;
String sql1="select from (select row_number() over (order by pmcode) as rowNum," +
" from [CMD][dbo][mycopy1]) as t where rowNum between "+start+" and "+end;
try {
Systemoutprintln("正在获取数据");
allmembers=new ArrayList();
rss=stasexecuteQuery(sql1);
while(rssnext()){
member=new Member();
membersetAddress1(rssgetString("address1"));
membersetBnpoints(rssgetString("bnpoints"));
membersetDbno(rssgetString("dbno"));
membersetExpiry(rssgetString("expiry"));
membersetHispoints(rssgetString("hispoints"));
membersetKypoints(rssgetString("kypoints"));
membersetLevels(rssgetString("levels"));
membersetNames(rssgetString("names"));
membersetPmcode(rssgetString("pmcode"));
membersetRemark(rssgetString("remark"));
membersetSex(rssgetString("sex"));
membersetTelephone(rssgetString("telephone"));
membersetWxno(rssgetString("wxno"));
membersetPmdate(rssgetString("pmdate"));
allmembersadd(member);
// Systemoutprintln(membergetNames());
}
Systemoutprintln("成功获取sqlserver数据库数据！");
return allmembers;

} catch (SQLException e) {
// TODO Auto-generated catch block
Systemoutprintln("获取sqlserver数据库数据发送异常！");
eprintStackTrace();
}
try {
rssclose();
stasclose();
} catch (SQLException e) {
// TODO Auto-generated catch block
eprintStackTrace();
}
return null;
}

public void inputAll(ArrayList<Member> allmembers){
Systemoutprintln("开始向mysql中写入");
String sql2="insert into testmycopy2 values (,,,,,,,,,,,,,)";
try {
ps=conprepareStatement(sql2);
Systemoutprintln("-------------------------等待写入数据条数: "+allmemberssize());
for(int i=0;i<allmemberssize();i++){
pssetString(1, allmembersget(i)getPmcode());
pssetString(2, allmembersget(i)getNames());
//Systemoutprintln(allmembersget(i)getNames());
pssetString(3, allmembersget(i)getSex());
pssetString(4, allmembersget(i)getTelephone());
pssetString(5, allmembersget(i)getAddress1());
pssetString(6, allmembersget(i)getPmdate());
pssetString(7, allmembersget(i)getExpiry());
pssetString(8, allmembersget(i)getLevels());
pssetString(9, allmembersget(i)getDbno());
pssetString(10, allmembersget(i)getHispoints());
pssetString(11, allmembersget(i)getBnpoints());
pssetString(12, allmembersget(i)getKypoints());
pssetString(13, allmembersget(i)getWxno());
pssetString(14, allmembersget(i)getRemark());
//插入命令列表
//psaddBatch();
psexecuteUpdate();
}
//psexecuteBatch();
concommit();

psclose();
conclose();
thisflag=false;
Systemoutprintln(ThreadcurrentThread()getName()+"--->OK");
} catch (SQLException e) {
// TODO Auto-generated catch block
Systemoutprintln("向mysql中更新数据时发生异常！");
eprintStackTrace();
}
}
@Override
public void run() {
// TODO Auto-generated method stub
while(true&&flag){
thisinputAll(getAll());
}
}

数据切分 关系型数据库本身比较容易成为系统瓶颈，单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后，由于查询维度较多，即使添加从库、优化索引，做很多 *** 作时性能仍下降严重。此时就要考虑对其进行切分了，切分的目的就在于减少数据库

C#利用update更新数据到oracle数据库：
第一步：打开oracle数据库
public static string connString = "";
public static OracleConnection conn = null;
//打开数据库连接
public static bool Open()
{
//从配置文件中获取连接字符串
//配置文件需要放在项目目录下的bin\Release中
connString = getXmlValue("connString");
conn = new OracleConnection(getXmlValue("connString"));
try
{
connOpen();
ConsoleWriteLine("数据库连接成功");
return true;
}
catch (SystemException ex)
{
ConsoleWrite(exMessage);
MessageBoxShow("未能连接到数据库");
return false;
}
第二步：执行更新方法：
public static int update(int ruleId, int equipmentId, String equipmentName, String propertyName, int ruleType, String ruleRequest, String ruleRequestOther, String ruleExplain)
{
String sql = "update Device_Attr t set tBASEID = :equipmentId, tBASENAME = :equipmentName,tCA_NAME = :propertyName,tCA_RULETYPE = :ruleType,tCA_RULETEXT = :ruleRequest,tCA_RULETEXT2 = :ruleRequestOther,tCONTENT = :ruleExplain where tCA_ID =:ruleId";
OracleCommand cmd = new OracleCommand(sql, conn);
OracleParameter param_1 = new OracleParameter(":equipmentId", equipmentId);
cmdParametersAdd(param_1);
OracleParameter param_2 = new OracleParameter(":equipmentName", equipmentName + "$");
cmdParametersAdd(param_2);
OracleParameter param_3 = new OracleParameter(":propertyName", propertyName);
cmdParametersAdd(param_3);
OracleParameter param_4 = new OracleParameter(":ruleType", ruleType);
cmdParametersAdd(param_4);
OracleParameter param_5 = new OracleParameter(":ruleRequest", ruleRequest);
cmdParametersAdd(param_5);
OracleParameter param_6 = new OracleParameter(":ruleRequestOther", ruleRequestOther);
cmdParametersAdd(param_6);
OracleParameter param_7 = new OracleParameter(":ruleExplain", ruleExplain);
cmdParametersAdd(param_7);
OracleParameter param_8 = new OracleParameter(":ruleId", ruleId);
cmdParametersAdd(param_8);
int result = cmdExecuteNonQuery();
return result;
}

---