mysql批量插入耗时问题,求解 20C

使用mysql批量插入时,发现进行sql的string语句拼接特别耗时,数据库执行插入时间还算比较快。想问有什么好的解决方案可以提高sql的string语句拼接速度(一次插入200条,每条数百列数据)?另外进行insert批量插入时,插入的记录是否会被更改?比如替换或者删除?

8个回答

使用springbuffer试试

insert 不会更改和删除,如果存在相同主键,数据库会报错,不让插入。

1、可考虑不在业务层拼接而在dao层:

 <insert id="insertBatch" parameterType="List">
INSERT INTO TStudent(name,age)
<foreach collection="list" item="item" index="index" open="("close=")"separator="union all">
SELECT #{item.name} as a, #{item.age} as b FROM DUAL
</foreach>
</insert>

2、然后就是 如果程序不需要考虑安全性的话可使用StringBuilder效率会高一点,考虑安全性的话使用StringBuffer。还有就是#{}和${}的使用也是一样的,参数可能存在注入的话就别选${}方式了,不存在的一律使用${}效率更高。
3、还有就是线性增加批量插入的数据条数,执行时间并非呈线性增长,而是3倍的比例增长。在此做一个大胆的推理,数量如果很大,时间将以指数级增长。
针对此种问题,最简单的解决方案是,将一批数据分多批插入,例如一批数据有220条,每次批量插入100条,则分三次执行全部插入。所耗费的时间要比一次执行的时间要短。

推荐使用存储过程,看是否有帮助

•用顺序主键策略(例如自增主键,或者修改业务逻辑,让插入的记录尽可能顺序主键)

采用多值表(10条)插入方式最为合适

将进程/线程数控制在2倍CPU数目相对合适

  1. 首先 已经insert 过的记录不会被修改, 就像1楼说的那样,如果表中有主键, 则会提示插入失败
  2. 拼接SQL 语句, 并不涉及到数据库层, 所以效率慢和应该数据库没有关系, 不知道你的SQL 拼接是怎样完成的, 是根据外部界面用户的选择拼接SQL, 还是根据一定规则生成SQL, 建议你在程序中使用输出语句将每一步的耗时显示出来, 或者使用工具(网上有很多此类工具)来检测耗时主要发生在什么地方. 对症下药.
cyang421
cyang421 根据一定规则生成的 已经查过就是生成SQL语句的时候比较耗时
接近 2 年之前 回复

1使用springbuffer试试.

插入:

MySql提供了可以一次插入多条数据的用法:

[sql]

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12)...;

在程序中可以通过循环,添加Values对应的列表,最后使用一次executeUpdate完成插入操作。但是Mysql语句并不是越长越好,MYsql语句长度有限制,可以查看mysql的配置文件my.in中max_allowed_packet属性,并进行相应设置。

更新:

Mysql中没有提供像Insert一样一次更新多条记录,需要逐条语句拼接。

[sql]

update weibo set userName = 'xyw' where id = '22';update weibo set userID = '143' where id = '35';

你可以使用addBatch语句,将拼接起来的SQL语句进行一次性处理,但是效率并不高。

还需要处理resultset的释放问题,否则mysql会报错:"Commands out of sync; you can't run this command now"

针对update语句,虽然并没有resultset返回,但仍然需要释放。而由于未知原因(可能是sql语句太长?),释放resultset非常耗时,最终算下来得不偿失。

针对以上的不足,可以使用另一种方法执行批量更新。

[sql]

INSERT INTO tbl_name [col_name1, col_name2,...)] VALUES(col_value1,col_value2,...), (col_value1,col_value2,...)ON DUPLICATE KEY UPDATE userName=VALUES(userName)

使用这种方法必须满足条件:col_name1, col_name2,...中必须有主键或者唯一键。

userName是要更新的列。

如果想一次更新多列,可以在userName=VALUES(userName)后面继续添加,例如:

[sql]

INSERT INTO tbl_name [col_name1, col_name2,...)] VALUES(col_value1,col_value2,...), (col_value1,col_value2,...)ON DUPLICATE KEY UPDATE userName=VALUES(userName), userID = VALUES(userID)

这样就可以同时更新userName和userID两个字段。

它的实现原理是,首先Mysql根据表名后面列出的主键,查找表(因为是主键,所以在表中唯一存在)。如果存在该行数据,则按照最后的col_name = values(col_name)列表对相应的字段,按照values列表中给出的值进行更新。建议:表名后面的字段列表,除了主键之外,列出来的最好都作为更新的对象,即在语句最后都要有相应的col_name = values(col_name),否则,你在表名后罗列出来字段,在values中赋值了,但是不是更新的对象,显然是浪费。

如果不存在该行数据,则进行插入操作,没有作为更新对象的列按照默认值填充(前提是Mysql运行在非严格模式下。如果在严格模式下,没列都需要有默认值,否则运行出错)。

注意:

主键可以作为更新的对象 ,但是只是在表中不存在该记录时起作用,即执行了插入操作,如果表中已经存在了该主键对应的行数据,下次更新时不会再插入该行,而是执行除了主键之外的其他列的更新操作。所以最好不要将主键设置为更新的对象。

实例:

[sql]

INSERT INTO keywordtable(id, keyword, userName, userID) VALUES(1, '你好', 'Eliot', 22), (2, 'hello', 'Jhon', 23),

(3, '嘻嘻', 'Jim', 24) ON DUPLICATE KEY UPDATE keyword=VALUES(keyword), userName=VALUES(userName),userID=VALUES(userID);

除了id外,字段有keyword, userName, userID,他们是要更新的字段。

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐