文档章节

Mysql 批量写入数据 性能优化

程序员补给栈
 程序员补给栈
发布于 2017/03/11 11:22
字数 1387
阅读 1193
收藏 7

测试环境

配置直接影响执行速度,先上一下测试机配置:

  • cpu i7 5500U(低电压伤不起,以后再也不买低电压的U了)
  • 内存 8G ddr3 1600
  • php 7.1
  • mysql 5.5.40
  • 开发框架 CodeIgniter 3.1.2

 

影响写入效率的因素都有什么?

  • 数据库引擎

         开发中常用的数据库引擎 MyISAM,InnoDB 这两种,其他的数据库引擎我在开发中还没用到,所以不在这里测试了。

         先看一下库表结构 :

         test库下有两张表:分别为上面提的两种引擎:

     

         每张表结构如下(一个自增id,一个varchar类型待插入字段):

          缺省状态下对两表插入20w数据看一下效率:

          PHP代码如下:         

/**
	 * 
	 * 测试插入效率
	 * 
	 * @return void
	 * 
	 */
	public function insertTest(){

		set_time_limit(0);   //防止超300s 500错误
	
		$t1 = microtime(true);
	
	
		//随机插入num条
		for ($i=1; $i<=200000; $i++){
			
			$result = $this->db->insert('myisam', ['value' => uniqid().$i]);
		}

		//程序运行时间
		$t2 = microtime(true);
		echo '耗时:'.round($t2-$t1,3).'秒<br>';
		echo '内存消耗:'.round(memory_get_usage()/1048576,2)." M<br/>";
	
	}

    执行结果:

    

    20w 数据 Myisam要 接近3分钟了。

 

    看一下InnoDb缺省状态下执行时间:

    插入1w条数据 用了 6分49秒,没办法等下去了, 按照这个数据量推测 6分49 * 20 = ???

    后期由于数据量增多,也会影响插入性能,所以InnoDb默认状态插入20w单字段数据要2小时以上,无法     忍受。

  •   业务逻辑

      显然上面的业务逻辑是有问题的,每条数据单次插入,增加了mysql的开销,每次插入数据都要重新连         接一下mysql,肯定是相当浪费资源了。所以CI提供了 insert_batch(),批量写入数据。Thinkphp3.2         也有addAll() 这样的方法来支持。其他框架应该都有!

      原理很简单就是把二维数组,拼接为sql

      将单条Sql如下:

      $sql = "INSERT INTO TEST (value) VALUES ('helloworld1')";

      $sql = "INSERT INTO TEST (value) VALUES ('helloworld2')";

      拼接为:

      $sql = "INSERT INTO TEST (value) VALUES ('helloworld1'), ('helloworld2')";

      很明显批量插入速度要快很多。

     

     还是20w数据,MyISAM 下批量查询速度多快?(已有数据会影响插入效率,已清空 myisam表)

     还是上代码

/**
	 * 
	 * 测试批量插入效率
	 * 
	 * @return void
	 * @author lidalin.se@gmail.com
	 * 
	 */
	public function insertTest(){

		set_time_limit(0);   //防止超300s 500错误
	
		$t1 = microtime(true);
	
	
		//随机插入num条
		for ($i=1; $i<=200000; $i++){
			
			$data[$i] = ['value' => uniqid().$i];
		}
		//程序运行时间
		$t2 = microtime(true);
		echo '循环耗时:'.round($t2-$t1,3).'秒<br>';
		
		$this->db->insert_batch('myisam', $data);  //批量插入
		
		$t3 = microtime(true);
		echo '插入耗时:'.round($t3-$t2,3).'秒<br>';
		
		echo '内存消耗:'.round(memory_get_usage()/1048576,2)." M<br/>";
	
	}

    执行结果:

        

     比起之前的167秒的单条插入速度快了 5 倍。内存消耗增加1.5倍左右,内存换时间,可取~~~

 

    InnoDB 会是什么速度呢?

    执行结果:

        

    这次终于执行完了,而且速度很快。和之前的两个小时比,效率也提升了N倍。

 

  • 修改配置参数提升性能:

        InnoDB 引擎 真的这么慢?这么low?     

        答案显然是:NO

        InnoDB写入之所以这么慢的一个原因是:

innodb_flush_log_at_trx_commit

        参数配置的问题

        如下图默认的值:

        关于值请查阅mysql文档:

        https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html 

        

       当innodb_flush_log_at_trx_commit=0时, log buffer将每秒一次地写入log file, 并且log file的flush(刷新          到disk)操作同时进行. 此时, 事务提交是不会主动触发写入磁盘的操作.

       当innodb_flush_log_at_trx_commit=1时(默认), 每次事务提交时, MySQL会把log buffer的数据写入log f          ile, 并且将log file flush(刷新到disk)中去.

       当innodb_flush_log_at_trx_commit=2时, 每次事务提交时, MySQL会把log buffer的数据写入log file, 但          不会主动触发flush(刷新到disk)操作同时进行. 然而, MySQL会每秒执行一次flush(刷新到disk)操作.

        

        把值设置为2,再试一下:

        

         速度又提升了 3倍,和 MyISAM几乎相同。

         所以以后说InnoDB写入速度慢,可能是配置问题

  • 还可以优化?

        还有什么可以优化?

        由于我们使用了 框架的insert_batch,看一下CI源码:

/**
	 * The "set_insert_batch" function.  Allows key/value pairs to be set for batch inserts
	 *
	 * @param	mixed
	 * @param	string
	 * @param	bool
	 * @return	CI_DB_query_builder
	 */
	public function set_insert_batch($key, $value = '', $escape = NULL)
	{
		$key = $this->_object_to_array_batch($key);

		if ( ! is_array($key))
		{
			$key = array($key => $value);
		}

		is_bool($escape) OR $escape = $this->_protect_identifiers;

		$keys = array_keys($this->_object_to_array(current($key)));
		sort($keys);

		foreach ($key as $row)
		{
			$row = $this->_object_to_array($row);
			if (count(array_diff($keys, array_keys($row))) > 0 OR count(array_diff(array_keys($row), $keys)) > 0)
			{
				// batch function above returns an error on an empty array
				$this->qb_set[] = array();
				return;
			}

			ksort($row); // puts $row in the same order as our keys

			if ($escape !== FALSE)
			{
				$clean = array();
				foreach ($row as $value)
				{
					$clean[] = $this->escape($value);
				}

				$row = $clean;
			}

			$this->qb_set[] = '('.implode(',', $row).')';
		}

		foreach ($keys as $k)
		{
			$this->qb_keys[] = $this->protect_identifiers($k, FALSE, $escape);
		}

		return $this;
	}

我们传入的数据,方法会再循环,判断。所以建议语句自己拼接

代码修改如下:

/**
	 * 
	 * 测试插入效率
	 * 
	 * @return void
	 * @author lidalin.se@gmail.com
	 * 
	 */
	public function insertTest(){

		set_time_limit(0);   //防止超300s 500错误
	
		$t1 = microtime(true);
	
	
		$sql = "insert into innodb (value) VALUES";
		//随机插入num条
		for ($i=1; $i<=200000; $i++){
			
			$val = uniqid().$i;
				
			$sql .= "('{$val}'),";
			
		}
		
		$sql = substr($sql,0,-1);
		
		//程序运行时间
		$t2 = microtime(true);
		echo '循环耗时:'.round($t2-$t1,3).'秒<br>';
		
		$this->db->query($sql);  //批量插入
		
		$t3 = microtime(true);
		echo '插入耗时:'.round($t3-$t2,3).'秒<br>';
		
		echo '内存消耗:'.round(memory_get_usage()/1048576,2)." M<br/>";
	
	}

执行结果:

20W条数据  InnoDB   循环1.6秒,插入1.2秒。速度是不是很爽了。。

拼接语句可能会报错
设置一下

max_allowed_packet = 500M

允许mysql 接受数据包大小。

 

 欢迎转载:

转载请注明:https://my.oschina.net/famoustone/blog/856736

© 著作权归作者所有

程序员补给栈
粉丝 37
博文 139
码字总数 56869
作品 0
烟台
后端工程师
私信 提问
redis的使用及感受

近期由于公司业务的发展Mysql写入成为了瓶颈,当然也有方案继续优化,例如分库分表、内存表等等,但成本较高,例如内存占用,聚合运算。。。 redis据说写入性能强悍,实测14w/s(list链表),后...

云秋意
2015/10/24
391
3
《从0到1学习Flink》—— Flink 读取 Kafka 数据批量写入到 MySQL

前言 之前其实在 《从0到1学习Flink》—— 如何自定义 Data Sink ? 文章中其实已经写了点将数据写入到 MySQL,但是一些配置化的东西当时是写死的,不能够通用,最近知识星球里有朋友叫我: 写...

群星纪元
03/18
0
0
针对 MySQL 大规模数据库的性能和伸缩性的优化

在需要支持移动/平板电脑应用及普通桌面浏览器访问的时代,网站的普及率和有效性很大程度上取决于其可用性和性能。一个访问缓慢的网站会使得访问者或潜在的客户流失,并导致商业的失败。一个...

oschina
2017/07/24
2.8K
6
MySQL 数据库性能优化之缓存参数优化

MySQL 数据库性能优化之缓存参数优化 数据库属于 IO 密集型的应用程序,其主要职责就是数据的管理及存储工作。而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取...

蓝狐乐队
2014/07/09
0
0
MySQL数据库性能优化之一(缓存参数优化)

数据库属于 IO密集型的应用程序,其主要职责就是数据的管理及存储工作。而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取一个IO是在毫秒级别,二者相差3个数量级...

爱mili
2016/01/04
19
0

没有更多内容

加载失败,请刷新页面

加载更多

在Javascript中Eval函数的使用

【eval()函数】 JavaScript有许多小窍门来使编程更加容易。 其中之一就是eval()函数,这个函数可以把一个字符串当作一个JavaScript表达式一样去执行它。 举个小例子: var the_unevaled_ans...

花漾年华
18分钟前
3
0
[日更-2019.5.22、23] Android 系统的分区和文件系统(二)--Android 文件系统中的文件

声明 Android系统中有很多分区,每个分区内的文件系统一般都不同的,使用ADB进入系统/目录下可发现挂载这很多的目录,不同的目录中可来自不同的分区及文件系统; 那么,就来分下这些目录里面...

小馬佩德罗
23分钟前
2
0
数组操作相关算法

/*数组的相关的算法操作:1、在数组中找最大值/最小值*/class Test11_FindMax{public static void main(String[] args){int[] array = {4,2,6,8,1};//在数组中找最大...

architect刘源源
今天
2
0
okhttp3 以上版本在安卓9.0无法请求数据的解决方案

应用官方的说明:在 Android 6.0 中,我们取消了对 Apache HTTP 客户端的支持。 从 Android 9 开始,默认情况下该内容库已从 bootclasspath 中移除且不可用于应用。且Android P 限制了明文流量...

chenhongjiang
今天
12
0
简单示例:NodeJs连接mysql数据库

开篇引用网上的说法: 简单的说 Node.js 就是运行在服务端的 JavaScript。Node.js 是一个基于Chrome JavaScript 运行时建立的一个平台。Node.js是一个事件驱动I/O服务端JavaScript环境,基于...

李朝强
今天
8
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部