测试环境
配置直接影响执行速度，先上一下测试机配置：
- 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代码如下：
1/** 2 * 3 * 测试插入效率 4 * 5 * @return void 6 * 7 */ 8 public function insertTest(){ 9 10 set_time_limit(0); //防止超300s 500错误 11 12 $t1 = microtime(true); 13 14 15 //随机插入num条 16 for ($i=1; $i<=200000; $i++){ 17 18 $result = $this->db->insert('myisam', ['value' => uniqid().$i]); 19 } 20 21 //程序运行时间 22 $t2 = microtime(true); 23 echo '耗时：'.round($t2-$t1,3).'秒<br>'; 24 echo '内存消耗：'.round(memory_get_usage()/1048576,2)." M<br/>"; 25 26 }
执行结果：
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表）
还是上代码
1/** 2 * 3 * 测试批量插入效率 4 * 5 * @return void 6 * @author lidalin.se@gmail.com 7 * 8 */ 9 public function insertTest(){ 10 11 set_time_limit(0); //防止超300s 500错误 12 13 $t1 = microtime(true); 14 15 16 //随机插入num条 17 for ($i=1; $i<=200000; $i++){ 18 19 $data[$i] = ['value' => uniqid().$i]; 20 } 21 //程序运行时间 22 $t2 = microtime(true); 23 echo '循环耗时：'.round($t2-$t1,3).'秒<br>'; 24 25 $this->db->insert_batch('myisam', $data); //批量插入 26 27 $t3 = microtime(true); 28 echo '插入耗时：'.round($t3-$t2,3).'秒<br>'; 29 30 echo '内存消耗：'.round(memory_get_usage()/1048576,2)." M<br/>"; 31 32 }
执行结果：
比起之前的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源码：
1/** 2 * The "set_insert_batch" function. Allows key/value pairs to be set for batch inserts 3 * 4 * @param mixed 5 * @param string 6 * @param bool 7 * @return CI_DB_query_builder 8 */ 9 public function set_insert_batch($key, $value = '', $escape = NULL) 10 { 11 $key = $this->_object_to_array_batch($key); 12 13 if ( ! is_array($key)) 14 { 15 $key = array($key => $value); 16 } 17 18 is_bool($escape) OR $escape = $this->_protect_identifiers; 19 20 $keys = array_keys($this->_object_to_array(current($key))); 21 sort($keys); 22 23 foreach ($key as $row) 24 { 25 $row = $this->_object_to_array($row); 26 if (count(array_diff($keys, array_keys($row))) > 0 OR count(array_diff(array_keys($row), $keys)) > 0) 27 { 28 // batch function above returns an error on an empty array 29 $this->qb_set[] = array(); 30 return; 31 } 32 33 ksort($row); // puts $row in the same order as our keys 34 35 if ($escape !== FALSE) 36 { 37 $clean = array(); 38 foreach ($row as $value) 39 { 40 $clean[] = $this->escape($value); 41 } 42 43 $row = $clean; 44 } 45 46 $this->qb_set[] = '('.implode(',', $row).')'; 47 } 48 49 foreach ($keys as $k) 50 { 51 $this->qb_keys[] = $this->protect_identifiers($k, FALSE, $escape); 52 } 53 54 return $this; 55 }
我们传入的数据，方法会再循环，判断。所以建议语句自己拼接
代码修改如下：
1/** 2 * 3 * 测试插入效率 4 * 5 * @return void 6 * @author lidalin.se@gmail.com 7 * 8 */ 9 public function insertTest(){ 10 11 set_time_limit(0); //防止超300s 500错误 12 13 $t1 = microtime(true); 14 15 16 $sql = "insert into innodb (value) VALUES"; 17 //随机插入num条 18 for ($i=1; $i<=200000; $i++){ 19 20 $val = uniqid().$i; 21 22 $sql .= "('{$val}'),"; 23 24 } 25 26 $sql = substr($sql,0,-1); 27 28 //程序运行时间 29 $t2 = microtime(true); 30 echo '循环耗时：'.round($t2-$t1,3).'秒<br>'; 31 32 $this->db->query($sql); //批量插入 33 34 $t3 = microtime(true); 35 echo '插入耗时：'.round($t3-$t2,3).'秒<br>'; 36 37 echo '内存消耗：'.round(memory_get_usage()/1048576,2)." M<br/>"; 38 39 }
执行结果：
20W条数据 InnoDB 循环1.6秒，插入1.2秒。速度是不是很爽了。。
拼接语句可能会报错
设置一下
max_allowed_packet = 500M
允许mysql 接受数据包大小。
欢迎转载：