如何在MySQL数据库中优化insert的性能

100次阅读

共计 1841 个字符，预计需要花费 5 分钟才能阅读完成。

自动写代码机器人，免费开通

如何在 MySQL 数据库中优化 insert 的性能？相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

MySQL 的 insert 语句语法，关于 mysql 优化 insert 性能的相关介绍。

insert into `table`(`field1`,`field2`) values(value1 , value2

提高 insert 性能的方法

1. 一条 sql 语句插入多条数据

INSERT INTO `insert_table` (`uid`, `content`, `type`) VALUES (userid_0 ,  content_0 , 0); 
INSERT INTO `insert_table` (`uid`, `content`, `type`) VALUES (userid_1 ,  content_1 , 1);

可以写成

INSERT INTO `insert_table` (`uid`, `content`, `type`) VALUES (userid_0 ,  content_0 , 0), (userid_1 ,  content_1 , 1);

2. 使用事务

START TRANSACTION; 
INSERT INTO `insert_table` (`uid`, `content`, `type`) VALUES (userid_0 ,  content_0 , 0); 
INSERT INTO `insert_table` (`uid`, `content`, `type`) VALUES (userid_1 ,  content_1 , 1); 
... 
COMMIT;

注意

1.sql 语句长度有限制，合并 sql 语句时要注意。长度限制可以通过 max_allowed_packet 配置项修改，默认为 1M。

2. 事务太大会影响执行效率，mysql 有 innodb_log_buffer_size 配置项，超过这个值会使用磁盘数据，影响执行效率。

关于事务的配置项说明：

innodb_buffer_pool_size

如果用 Innodb，那么这是一个重要变量。相对于 MyISAM 来说，Innodb 对于 buffer size 更敏感。MySIAM 可能对于大数据量使用默认的 key_buffer_size 也还好，但 Innodb 在大数据量时用默认值就感觉在爬了。Innodb 的缓冲池会缓存数据和索引，所以不需要给系统的缓存留空间，如果只用 Innodb，可以把这个值设为内存的 70%-80%。和 key_buffer 相同，如果数据量比较小也不怎么增加，那么不要把这个值设太高也可以提高内存的使用率。

innodb_additional_pool_size

这个的效果不是很明显，至少是当操作系统能合理分配内存时。但你可能仍需要设成 20M 或更多一点以看 Innodb 会分配多少内存做其他用途。

innodb_log_file_size

对于写很多尤其是大数据量时非常重要。要注意，大的文件提供更高的性能，但数据库恢复时会用更多的时间。我一般用 64M-512M，具体取决于服务器的空间。

innodb_log_buffer_size

默认值对于多数中等写操作和事务短的运用都是可以的。如果经常做更新或者使用了很多 blob 数据，应该增大这个值。但太大了也是浪费内存，因为 1 秒钟总会 flush（这个词的中文怎么说呢？）一次，所以不需要设到超过 1 秒的需求。8M-16M 一般应该够了。小的运用可以设更小一点。

innodb_flush_log_at_trx_commit

抱怨 Innodb 比 MyISAM 慢 100 倍？那么你大概是忘了调整这个值。默认值 1 的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设成 2 对于很多运用，特别是从 MyISAM 表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒 flush 到硬盘，所以你一般不会丢失超过 1 - 2 秒的更新。设成 0 会更快一点，但安全方面比较差，即使 MySQL 挂了也可能会丢失事务的数据。而值 2 只会在整个操作系统挂了时才可能丢数据。

看完上述内容，你们掌握如何在 MySQL 数据库中优化 insert 的性能的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！

向 AI 问一下细节

正文完