本公众号只做学习分享,本公众号会不定期的给大家分享视频教学教程,项目源码,文章最新动态等,感谢大家的支持!

图书推荐(程序员必备)

文章正文

作者:xxx来源:开源中国原文:

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

cpu i7 5500U(低电压伤不起,以后再也不买低电压的U了)

内存 8G ddr3 1600

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<=; $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      *      */    public function insertTest(){        set_time_limit(0);   //防止超300s 500错误        $t1 = microtime(true);        //随机插入num条        for ($i=1; $i<=; $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文档:

当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      *      */    public function insertTest(){        set_time_limit(0);   //防止超300s 500错误        $t1 = microtime(true);        $sql = "insert into innodb (value) VALUES";        //随机插入num条        for ($i=1; $i<=; $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 接受数据包大小。

以上是文章的全部内容,有需要交流与学习的,可以加小编为好友,一起学习。