mysql教程栏目介绍批量SQL插入
推荐(免费):mysql教程
对于一些数据量较大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长。特别像报表系统,每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久。因此,优化数据库插入性能是很有意义的。
一条SQL语句插入多条数据
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 ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);
- 第二种SQL执行效率高的主要原因是合并后日志量[mysql的binlog和InnoDB的事务让日志]减少了,降低日志刷盘的数据量和频率,从而提高效率。
- 通过合并SQL语句,同时也能减少SQL语句解析的次数,减少网络传输的IO。
测试对比数据,分别是单条数据的导入与转换成一条SQL语句进行导入。
在事务中进行插入处理
START TRANSACTION;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);...COMMIT;
- 使用事务可以提高数据的插入效率,这是因为进行一个insert操作时,MySQL内部都会建立一个事务,在事务内才进行真正插入处理操作。
- 通过使用事务减少创建事务的消耗,所有插入都在执行后才进行提交操作
测试对比数据,分笔试不适用事务和使用事务操作
数据有序插入
数据有序的插入是插入记录在主键上的有序排序
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进行分裂合并等处理,会消耗比较多计算资源,并且插入记录的索引定位效率会下降,数据量较大时会有频繁的磁盘操作。
测试对比数据,随机数据与顺序数据的性能对比
先删除索引,插入完成后重建索引
性能综合测试
- 合并数据+事务的方法在较少数据量时,性能提升很明显,数据量较大时,性能急剧下降,这是由于此时数据量超过了innodb_buffer的容量,每次定位索引涉及较多的磁盘读写操作,性能下降较快。
- 合并数据+事务+有序的方法在数据量达到千万级以上表现依然良好,在数据量较大时,有序数据索引定位较为方便,不需要频繁对磁盘进行读写操作,可以维持较高
注意事项
SQL语句是有长度限制,在进行数据合并在同一SQL中务必不能超过SQL长度限制,通过
max_allowed_packet
配置可以修改,默认1M
,测试时可以修改为8M
。事务需要控制大小,事物太大可能影响执行的效率。MySQL有
innodb_log_buffer_size
配置项,超过这个值会把innodb的数据刷到磁盘中,这时,效率会有所下降。所以较好的做法是,在数据达到这个值前执行事务提交。
以上是详解MySQL批量SQL插入的性能优化的详细内容。更多信息请关注PHP中文网其他相关文章!

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于架构原理的相关内容,MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层,下面一起来看一下,希望对大家有帮助。

mysql的msi与zip版本的区别:1、zip包含的安装程序是一种主动安装,而msi包含的是被installer所用的安装文件以提交请求的方式安装;2、zip是一种数据压缩和文档存储的文件格式,msi是微软格式的安装包。

方法:1、利用right函数,语法为“update 表名 set 指定字段 = right(指定字段, length(指定字段)-1)...”;2、利用substring函数,语法为“select substring(指定字段,2)..”。

在mysql中,可以利用char()和REPLACE()函数来替换换行符;REPLACE()函数可以用新字符串替换列中的换行符,而换行符可使用“char(13)”来表示,语法为“replace(字段名,char(13),'新字符串') ”。

转换方法:1、利用cast函数,语法“select * from 表名 order by cast(字段名 as SIGNED)”;2、利用“select * from 表名 order by CONVERT(字段名,SIGNED)”语句。

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于MySQL复制技术的相关问题,包括了异步复制、半同步复制等等内容,下面一起来看一下,希望对大家有帮助。

在mysql中,可以利用REGEXP运算符判断数据是否是数字类型,语法为“String REGEXP '[^0-9.]'”;该运算符是正则表达式的缩写,若数据字符中含有数字时,返回的结果是true,反之返回的结果是false。

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了mysql高级篇的一些问题,包括了索引是什么、索引底层实现等等问题,下面一起来看一下,希望对大家有帮助。


热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

AI Hentai Generator
免费生成ai无尽的。

热门文章

热工具

EditPlus 中文破解版
体积小,语法高亮,不支持代码提示功能

ZendStudio 13.5.1 Mac
功能强大的PHP集成开发环境

安全考试浏览器
Safe Exam Browser是一个安全的浏览器环境,用于安全地进行在线考试。该软件将任何计算机变成一个安全的工作站。它控制对任何实用工具的访问,并防止学生使用未经授权的资源。

Dreamweaver Mac版
视觉化网页开发工具

VSCode Windows 64位 下载
微软推出的免费、功能强大的一款IDE编辑器