Mysql 批量写入数据性能优化

最新推荐文章于 2024-09-29 16:19:38 发布

weixin_33763244

最新推荐文章于 2024-09-29 16:19:38 发布

阅读量178

点赞数

文章标签：数据库 python php

原文链接：https://my.oschina.net/famoustone/blog/856736

版权

2019独角兽企业重金招聘Python工程师标准>>>

测试环境

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

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/>";
	
	}

执行结果：