文档章节

MySQL insert性能优化

 如风达
发布于 2016/06/30 15:46
字数 843
阅读 207
收藏 6

对于一些数据量较大的系统,面临的问题除了是查询效率低下,还有一个很重要的问题就是插入时间长。我们就有一个业务系统,每天的数据导入需要4-5个钟。这种费时的操作其实是很有风险的,假设程序出了问题,想重跑操作那是一件痛苦的事情。因此,提高大数据量系统的MySQL insert效率是很有必要的。

    经过对MySQL的测试,发现一些可以提高insert效率的方法,供大家参考参考。

1. 一条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执行效率高的主要原因有两个,一是减少SQL语句解析的操作, 只需要解析一次就能进行数据的插入操作,二是SQL语句较短,可以减少网络传输的IO。

这里提供一些测试对比数据,分别是进行单条数据的导入与转化成一条SQL语句进行导入,分别测试1百、1千、1万条数据记录。

记录数 单条数据插入 多条数据插入
1百 0.149s 0.011s
1千 1.231s 0.047s
1万 11.678s 0.218s


2. 在事务中进行插入处理。
把插入修改成:

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内部会建立一个事务,在事务内进行真正插入处理。通过使用事务可以减少数据库执行插入语句时多次“创建事务,提交事务”的消耗,所有插入都在执行后才进行提交操作。

这里也提供了测试对比,分别是不使用事务与使用事务在记录数为1百、1千、1万的情况。

记录数 不使用事务 使用事务
1百 0.149s 0.033s
1千 1.231s 0.115s
1万 11.678s 1.050s


性能测试:
这里提供了同时使用上面两种方法进行INSERT效率优化的测试。即多条数据合并为同一个SQL,并且在事务中进行插入。

记录数 单条数据插入 合并数据+事务插入
1万 0m15.977s 0m0.309s
10万 1m52.204s 0m2.271s
100万 18m31.317s 0m23.332s

从测试结果可以看到,insert的效率大概有50倍的提高,这个一个很客观的数字。

注意事项:

1. SQL语句是有长度限制,在进行数据合并在同一SQL中务必不能超过SQL长度限制,通过max_allowed_packe配置可以修改,默认是1M。

2. 事务需要控制大小,事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项,超过这个值会日志会使用磁盘数据,这时,效率会有所下降。所以比较好的做法是,在事务大小达到配置项数据级前进行事务提交。

本文转载自:http://blog.csdn.net/tigernorth/article/details/8094277

共有 人打赏支持
粉丝 7
博文 255
码字总数 22313
作品 0
深圳
私信 提问
MySQL批量SQL插入性能优化

对于一些数据量较大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长。特别像报表系统,每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久。因此,优化数据库...

liaolzy2
2014/11/27
0
1
MySQL批量SQL插入性能优化

MySQL批量SQL插入性能优化 原文出处:UC技术博客 对于一些数据量较大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长。特别像报表系统,每天花费在数据导入上的时间可能会...

为梦而来
2013/09/22
0
3
mysql InnoDB UUID 主键 性能优化【性能分析篇】.md

mysql uuid 主键 性能优化【性能分析篇】.mdmysql InnoDB UUID 主键 性能优化【实践篇】.md 有序uuidmysql InnoDB UUID 主键 性能优化【原理篇】.mdmysql InnoDB UUID 主键 性能优化【性能分...

北京起航
2015/05/22
0
1
MySQL批量SQL插入各种性能优化

对于一些数据量较大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长。特别像报表系统,每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久。因此,优化数据库...

snowing1990
2016/03/21
3
0
MySQL Insert语句单个批次数量过多导致的CPU性能问题分析

原文:MySQL Insert语句单个批次数量过多导致的CPU性能问题分析 【问题】 最近有台服务器比较频繁的CPU报警,表现的特征有CPU sys占比偏高,大量慢查询,大量并发线程堆积。后面开发对insert的...

杰克.陈
2018/10/21
0
0

没有更多内容

加载失败,请刷新页面

加载更多

centos7重置密码、单用户模式、救援模式、ls命令、chmod命令

在工作当中如果我们错误的配置了文件使服务器不能正常启动或者忘记密码不能登录系统,如何解决这些问题呢?重装系统是可以实现的,但是往往不能轻易重装系统的,下面用忘记密码作为例子讲解如...

李超小牛子
30分钟前
0
0
Python如何开发桌面应用程序?Python基础教程,第十三讲,图形界面

当使用桌面应用程序的时候,有没有那么一瞬间,想学习一下桌面应用程序开发?行业内专业的桌面应用程序开发一般是C++,C#来做,Java开发的也有,但是比较少。本节课会介绍Python的GUI(图形用...

程序员补给栈
今天
5
0
kafka在的使用

一、基本概念 介绍 Kafka是一个分布式的、可分区的、可复制的消息系统。它提供了普通消息系统的功能,但具有自己独特的设计。 这个独特的设计是什么样的呢? 首先让我们看几个基本的消息系统...

狼王黄师傅
今天
3
0
Android JNI总结

0x01 JNI介绍 JNI是Java Native Interface的缩写,JNI不是Android专有的东西,它是从Java继承而来,但是在Android中,JNI的作用和重要性大大增强。 JNI在Android中起着连接Java和C/C++层的作...

天王盖地虎626
昨天
3
0
大数据教程(11.8)Hive1.2.2简介&初体验

上一篇文章分析了Hive1.2.2的安装,本节博主将分享Hive的体验&Hive服务端和客户端的使用方法。 一、Hive与hadoop直接的关系 Hive利用HDFS存储数据,利用MapReduce查询数据。 二、Hive与传统数...

em_aaron
昨天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部