背景
面对批量频繁的数据库操作，本文尝试从经验整理mysql批量查询，插入等操作的方法
批量查询
情景1：查询同个字段多个值
采用方法：
采用In方式。返回的结果顺序对应In中值的顺序。这种同一字段不同值，采用In方式可以避免多次的任务提交。当然，也可以通过任务的START TRANSACTION和COMMIT方式将多个select语句一次性进行提交。这种事务命令可以用以一序列的数据库操作。
注意该方式对于值不在In中的查询是不会有返回的结果，那么就造成一定程度上不清楚In中的值对应的是select返回结果中的哪一条。此时，可以通过该记录的带有主键字段，对结果进行标识的。
代码：
int reqsize=500;
Json::Value rspTemp;
boost::unordered_map<uint32_t, uint32_t> idMap;//记录krcid和对应的位置
string krcids="(";
for(int i=0; i<reqsize; i++)
{
//批量请求,in操作
uint32_t temp = JsonReq[i].asInt();//即，In中的所有候选值
idMap[temp] = i;
rspTemp[i]["krcid"]=temp;
rspTemp[i]["fmt"] ="";
krcids +=Uint32ToString(temp);
if(i != reqsize-1)
{
krcids.append(",");
}
}
krcids.append(")");
string sql = "select ID, UC from table1 where ID IN ";
sql = sql + krcids;
DataTable dbTable;
db.ExecSQL(sql, dbTable);
int rowCount = dbTable.GetRowCount();
for (int i = 0; i < rowCount; ++i)
{
int krcid = dbTable[i][0].AsInt();//对krcid对应的fmt进行赋值
int fmt = dbTable[i][1].AsInt();//返回结果用json格式
uint32_t rowId =idMap[krcid];
rspTemp[rowId]["krcid"]=krcid;
rspTemp[rowId]["fmt"]=fmt;
}情景2：涉及到联合查询
批量插入
采用方法1：Value方式合并数据
最简单的就是批量的语句，一次次执行insert操作，最慢这种。使用START TRANSACTION和COMMIT的事务型方式进行批量插入，效率会比一条条插入要高得多。这是因为进行一个INSERT操作时，MySQL**内部会建立一个事务**，在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交操作。采用value的方法可以合并后日志量（MySQL的binlog和innodb的事务让日志）减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO。同时，也
代码：
int linecount=0;
time_t start,stop;
start = time(NULL);
string finalstr;
vector<uint32_t> vecorigina;
vector<uint32_t> vectrans;
vector<uint32_t> vecsound;
vector<uint32_t> veckrci;
int tasknum =5000;
for(int i=0; i<rowCount; i++)
{
linecount++;
uint32_t krcid = dbTable[i][0].AsInt();
uint32_t origiuid = VecUids[i];//构造出来的数据
uint32_t transuid = VecUids[i+rowCount];
uint32_t sounduid = VecUids[i+2*rowCount];
string sql_insert = "insert into tbl1 (id1,id2,id3,id4,modifytime) VALUES";
string condition = "(?,?,?,?,now())";
if(linecount%tasknum != 0 && linecount<rowCount)
{
finalstr = finalstr +condition+",";
veckrci.push_back(krcid);
vecorigina.push_back(origiuid);
vectrans.push_back(transuid);
vecsound.push_back(sounduid);
}
else
{
finalstr = finalstr +condition;
finalstr = sql_insert+ finalstr;
veckrci.push_back(krcid);
vecorigina.push_back(origiuid);
vectrans.push_back(transuid);
vecsound.push_back(sounduid);
dblyric_Debug.PrepareStatment(finalstr);
int z=1;
if(linecount==rowCount)//尾数处理
{
for(int j=0;j<veckrci.size();j++)
{
dblyric_Debug.SetInt(z++, veckrci[j]);
dblyric_Debug.SetInt(z++, vecorigina[j]);
dblyric_Debug.SetInt(z++, vectrans[j]);
dblyric_Debug.SetInt(z++, vecsound[j]);
}
if (!dblyric_Debug.ExecuteUpdate())
{
std::cout<<"update 失败! "<<linecount<<std::endl;
}
break;
}
if(veckrci.size()!=tasknum || vecorigina.size() !=tasknum || vectrans.size() !=tasknum||vecsound.size()!=tasknum)
{
std::cout<<"信息缺失!"<<linecount<<std::endl;
}
int j=0;
for(;j<veckrci.size();j++)
{
dblyric_Debug.SetInt(z++, veckrci[j]);
dblyric_Debug.SetInt(z++, vecorigina[j]);
dblyric_Debug.SetInt(z++, vectrans[j]);
dblyric_Debug.SetInt(z++, vecsound[j]);
}
if (!dblyric_Debug.ExecuteUpdate())
{
std::cout<<"update1 失败! "<<linecount<<std::endl;
}
finalstr = "";
veckrci.clear();
vecorigina.clear();
vectrans.clear();
vecsound.clear();
stop =time(NULL);
std::cout<<"5K耗时："<<double(stop-start)<<std::endl;
start = stop;
}
} 采用方法2：数据有序插入
数据有序插入可以提高插入效率。数据有序的插入是指插入记录在主键上是有序排列，例如datetime是记录的主键：
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('1', 'userid_1', 'content_1', 1);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('2', 'userid_2', 'content_2',2);修改成下面语句，效率更高：
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('1', 'userid_1', 'content_1', 1);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('2', 'userid_2', 'content_2',2);这是由于数据库插入时，需要维护索引数据，无序的记录会增大维护索引的成本。可以参照innodb使用的B+tree索引，如果每次插入记录都在索引的最后面，索引的定位效率很高，并且对索引调整较小；如果插入的记录在索引中间，需要B+tree进行分裂合并等处理，会消耗比较多计算资源，并且插入记录的索引定位效率会下降，数据量较大时会有频繁的磁盘操作。
合并数据+事务的方法在较小数据量时，性能提高是很明显的，数据量较大时（1千万以上），性能会急剧下降，这是由于此时数据量超过了innodb_buffer的容量，每次定位索引涉及较多的磁盘读写操作，性能下降较快。而使用合并数据+事务+有序数据的方式在数据量达到千万级以上表现依旧是良好，在数据量较大时，有序数据索引定位较为方便，不需要频繁对磁盘进行读写操作，所以可以维持较高的性能。
注意事项：
1. SQL语句是有长度限制，在进行数据合并在同一SQL中务必不能超过SQL长度限制，通过max_allowed_packet配置可以修改，默认是1M，测试时修改为8M。
2. 事务需要控制大小，事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项，超过这个值会把innodb的数据刷到磁盘中，这时，效率会有所下降。所以比较好的做法是，在数据达到这个这个值前进行事务提交。
采用方法3：通过文件（LOAD DATA LOCAL INFILE)
该方法测试过程中发现，数据量越大优势体现越是明显。按照网络上的说法是，20倍的提升：
According to the MySQL documentation, LOAD DATA INFILE is “roughly 20 times faster” than a MySQL INSERT statement。
SQL语句如下：
LOAD DATA LOCAL INFILE 'D:\\Users\\jason\\Desktop\\1009.txt'
INTO TABLE tbl1(Hash,Song,Id);注意，可能需要使用到的SELECT … INTO OUTFILE，进行数据的构造。
757
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
