最近在做一个数据导入功能,数据量为360W,按照我们通常的比较笨的方法则的遍历数据一条条的往数据库插入;经实验证明这个方法确实可以完成这个工作,但是它的插入数据的速度太慢,尤其当随着数据库的数据增加,速度会越来越慢。
所以有的人会说我可以使用事务的方式,确实使用事务的方式可以优化数据的插入速度,一般是可以满足我们的要求;但是我们知道使用事务,是会导致锁表的,当执行事务过程中,数据库会锁表,导致其它的连接无法操作此表,尤其在服务器端,支持多用户的情况下,这是一个致命的缺陷;所以现在很多服务器数据库都是采用分表分库的方式,使得读写分离,可以提高服务器的工作效率。
如果不采用分表分库的方式我们该怎样来优化呢?
首先,我们知道一条条插入数据是比较慢的,插入数据批量插入数据才最快。
单条插入:INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
批量插入:INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....), (值1, 值2,....), (值1, 值2,....).....
当然批量插入数据有数据量限制,开始我插入360w数据把它们写入了一条sql语句中,结果导致程序出现段错误;
所以我们要限定批量插入数据大小,单次插入多少数据合适,我也不知道,我没有去查;
我们需要将数据切分成多段然后插入数据库,切分的方法如下:
std::string AgentRelationNet::getValueListStr(AgentRelationNet mAgentRelationNet) {
std::ostringstream sql;
sql << "(\'" << mAgentRelationNet.id.toStdString() << "\',"
<< "\'" << mAgentRelationNet.origId << "\',"
<< "\'" << mAgentRelationNet.destId << "\')";
return sql.str();
}
std::list<std::string> AgentRelationNet::getBatchInsertSqlList(std::list<AgentRelationNet> &dataList, long maxLength)
{
std::list<std::string> sqls;
if (dataList.size() <= 0)
return sqls;
std::string pre = getInsertPre();
std::ostringstream sqlBuilder;
sqlBuilder << pre;
for each (AgentRelationNet mAgentRelationNet in dataList)
{
std::string valueStr = AgentRelationNet::getValueListStr(mAgentRelationNet);
sqlBuilder << valueStr << ",";
if (sqlBuilder.str().length() >= maxLength) {
std::string sql = sqlBuilder.str();
sql = sql.substr(0, sql.length() - 1);
sqls.push_back(sql);
sqlBuilder.str("");
sqlBuilder.clear();
sqlBuilder << pre;
}
}
if (sqlBuilder.str().length() > 1) { //处理最后一条或没有达到最大值时
std::string sql = sqlBuilder.str();
sql = sql.substr(0, sql.length() - 1);
sqls.push_back(sql);
sqlBuilder.clear();
}
return sqls;
}
std::string AgentRelationNet::getInsertPre() {
std::ostringstream sql;
sql << "insert into agent_relation_net(id, orig_agent_id, dest_agent_id) values ";
return sql.str();
}
总之就是将数据分为多个批量插入语句,在切分成多个sql语句后,我们可以分几次插入数库,在插入语句中我们可以休眠一段时间再执行下一次插入,为的就是中间腾出一些时间给其它的连接操作。