文档章节

MySQL批量SQL插入各种性能优化

 阿狼仔
发布于 2016/06/28 10:45
字数 1522
阅读 33
收藏 0

MySQL批量SQL插入各种性能优化

最近在工作中正好遇到了在数据备份/还原的时候耗时太长, 下面这个方案作为记录。因为线上环境和工作环境网络互相隔绝, 每个星期只能对数据进行人工拷贝还原,现有数据大概在300W左右, 每次导出要1H+,还原也需要1H+。打算重写个导出工具导出增量日志, 下面的文章正好提高了我的导入导出效率。mark

 

对于一些数据量较大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长。特别像报表系统,每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久。因此,优化数据库插入性能是很有意义的。
经过对MySQL innodb的一些性能测试,发现一些可以提高insert效率的方法,供大家参考参考。

1. 一条SQL语句插入多条数据。
常用的插入语句如:

 

MySQL

 

1

2

3

4

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);

 

修改成:

 

MySQL

 

1

2

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语句进行导入,分别测试1百、1千、1万条数据记录。

 

 

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

 

MySQL

 

1

2

3

4

5

6

7

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万的情况。

 

3. 数据有序插入。
数据有序的插入是指插入记录在主键上是有序排列,例如datetime是记录的主键:

 

MySQL

 

1

2

3

4

5

6

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);

 

修改成:

 

MySQL

 

1

2

3

4

5

6

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进行分裂合并等处理,会消耗比较多计算资源,并且插入记录的索引定位效率会下降,数据量较大时会有频繁的磁盘操作。
下面提供随机数据与顺序数据的性能对比,分别是记录为1百、1千、1万、10万、100万。

从测试结果来看,该优化方法的性能有所提高,但是提高并不是很明显。

 

性能综合测试:
这里提供了同时使用上面三种方法进行INSERT效率优化的测试。

从测试结果可以看到,合并数据+事务的方法在较小数据量时,性能提高是很明显的,数据量较大时(1千万以上),性能会急剧下降,这是由于此时数据量超过了innodb_buffer的容量,每次定位索引涉及较多的磁盘读写操作,性能下降较快。而使用合并数据+事务+有序数据的方式在数据量达到千万级以上表现依旧是良好,在数据量较大时,有序数据索引定位较为方便,不需要频繁对磁盘进行读写操作,所以可以维持较高的性能。

 

注意事项:
1. SQL语句是有长度限制,在进行数据合并在同一SQL中务必不能超过SQL长度限制,通过max_allowed_packet配置可以修改,默认是1M,测试时修改为8M。
2. 事务需要控制大小,事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项,超过这个值会把innodb的数据刷到磁盘中,这时,效率会有所下降。所以比较好的做法是,在数据达到这个这个值前进行事务提交。

/********************************/

等工具写完再来更新。

来自:UC技术博客

作者:琪钛 

链接:http://tech.uc.cn/?p=634

本文转载自:http://tech.uc.cn/?p=634

粉丝 0
博文 52
码字总数 13158
作品 0
南京
私信 提问
MySQL Insert语句单个批次数量过多导致的CPU性能问题分析

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

杰克.陈
2018/10/21
0
0
SQL-SQL优化-索引

图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用 Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一...

掘金官方
2017/12/25
0
0
MySQL批量SQL插入各种性能优化

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

snowing1990
2016/03/21
13
0
mysql数据库sql优化原则

这里的原则 只是针对mysql数据库,其他的数据库 某些是殊途同归,某些还是存在差异。我总结的也是mysql普遍的规则,对于某些特殊情况得特殊对待。在构造sql语句的时候养成良好的习惯 原则1、...

rin9958
2016/06/27
107
1
MySQL批量SQL插入性能优化

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

为梦而来
2013/09/22
253
3

没有更多内容

加载失败,请刷新页面

加载更多

Android面试常客之Handler全解

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 本文链接:https://blog.csdn.net/fnhfire_7030/article/details/79518819 前言:又到了一年...

shzwork
9分钟前
0
0
position sticky 定位

本文转载于:专业的前端网站➫position sticky 定位 1、兼容性 https://caniuse.com/#search=sticky chrome、ios和firefox兼容性良好。 2、使用场景 sticky:粘性。粘性布局。 在屏幕范围内时...

前端老手
15分钟前
1
0
CentOS 7 yum 安装 PHP7.3 教程

参考:https://www.mf8.biz/centos-rhel-install-php7-3/ 1、首先安装 EPEL 源: yum install epel-release 安装 REMI 源: yum install http://rpms.remirepo.net/enterprise/remi-release......

dragon_tech
30分钟前
1
0
Linux物理网卡聚合及桥接

Linux内部实现的bridge可以把一台机器上的多张网卡桥接起来,从而把自己作为一台交换机。同时,LInux bridge还支持虚拟端口,即桥接的不一定都是物理网卡接口,还可以是虚拟接口。目前主要表...

xiangyunyan
31分钟前
1
0
一起来学Java8(一)——函数式编程

在这篇文章中,我们将了解到在Java8下如何进行函数式编程。 函数式编程 所谓的函数式编程就是把函数名字当做值进行传递,然后接收方拿到这个函数名进行调用。 首先来看下JavaScript如何进行函...

猿敲月下码
今天
6
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部