最近的项目中需要将几个dump文件(文本格式、1~2G)的记录导入到mysql数据库中,由于数据量比较大(几百万、上千万条记录),有插入记录,也有更新记录的,导致插入\更新速度比较慢。
一开始,将单条sql语句交给mysql执行,测试下来,最快一次也要一个半小时。于是想办法改进之。
(1)针对插入记录,使用sql语句一次插入多条记录。实例:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
具体参见sql语法:
在程序中,使用循环拼接sql语句,然后一次性丢给myql_query()处理即可。
需要注意的是,sql语句不是越长越好,太长了可能会超出mysql限制(听同事介绍,mysql的限制是63M)。
我在实测中,我限制sql长度在10M:
1 for(...)2 {3 m_strSQL +=...;4
5 if (m_strSQL.length() > 10485760) //10M
6 {7 m_pMysqlStatement->execute(m_strSQL.substr(0, m_strSQL.length() - 1));8 m_strSQL= "INSERT INTO location (locationid, name, alternatename) VALUES";9 }10
11 }
测试结果为:
8267787条记录,耗时754秒(数据库在局域网内的不同机器上),基本满足要求。
(2)对于更新记录,由于update语法不支持一次更新多条记录,无法使用类似“INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);”的形式进行更新,只能拼接多条sql语句一起更新:
update location set languages = 'zh' where locationid = '12344';update location set postalcode = '14343' where locationid = '3455';
但是这样一来,问题出现了:
(II)还需要处理resultset的释放问题,否则mysql会报错:"Commands out of sync; you can't run this command now"
(III)针对update语句,虽然并没有resultset返回,但仍然需要释放。而由于未知原因(可能是sql语句太长?),释放resultset非常耗时,最终算下来得不偿失。
综合以上情况,同事提出了另一种解决方法:针对更新记录,仍然使用insert语句,不过限制主键重复时,更新字段。如下:
INSERT INTO location (locationid, languages) VALUES('13243', 'zh'),VALUES('13244', 'en') ON DUPLICATE KEY UPDATE languages=VALUES(languages)
但是这样又引出了另外一个问题:在一条sql语句中只能更新某些指定的字段,而程序中可能会有不同的更新条件。比如:上一条记录只更新languages字段,而下一条记录则只更新postalcode字段,这样是没办法蟹岛一条sql语句中的。
解决办法是:针对这两个不同的字段,使用不同的sql语句,保证每个sql语句只更新某个字段:
1 for(...)2 {3 m_strSQL_Language +=...4 m_strSQL_Postcode +=...5 if (m_strSQL_Language.length() > 10485760) //10M
6 {7 m_strSQL_Language.erase(m_strSQL_Language.length() - 1);8 m_strSQL_Language += "ON DUPLICATE KEY UPDATE languages=VALUES(languages)";9 m_pMysqlStatement->execute(m_strSQL_Language);10
11 m_strSQL_Language = "INSERT INTO location (locationid, languages) VALUES";12 }13
14 if (m_strSQL_Postcode.length() > 10485760) //10M
15 {16 m_strSQL_Postcode.erase(m_strSQL_Alternate_Postcode.length() - 1);17 m_strSQL_Postcode += "ON DUPLICATE KEY UPDATE postalcode=VALUES(postalcode)";18 m_pMysqlStatement->execute(m_strSQL_Postcode);19
20 m_strSQL_Postcode = "INSERT INTO location (locationid, postalcode) VALUES";21 }22 }
问题得到了完美解决。