Mysql 批量写入数据 性能优化

Wesley13
• 阅读 1114

测试环境

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

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

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

  • 数据库引擎

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

         先看一下库表结构 :

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

Mysql 批量写入数据 性能优化      

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

Mysql 批量写入数据 性能优化

          缺省状态下对两表插入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/>";
    
    }

    执行结果:

    Mysql 批量写入数据 性能优化

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

    执行结果:

        Mysql 批量写入数据 性能优化

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

    InnoDB 会是什么速度呢?

    执行结果:

        Mysql 批量写入数据 性能优化

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

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

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

        答案显然是:NO

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

innodb_flush_log_at_trx_commit

       参数配置的问题

        如下图默认的值:

Mysql 批量写入数据 性能优化

        关于值请查阅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,再试一下:

        Mysql 批量写入数据 性能优化

         速度又提升了 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/>";
    
    }

执行结果:

Mysql 批量写入数据 性能优化

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

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

max_allowed_packet = 500M

允许mysql 接受数据包大小。

 欢迎转载:

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

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
3个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
3年前
Android蓝牙连接汽车OBD设备
//设备连接public class BluetoothConnect implements Runnable {    private static final UUID CONNECT_UUID  UUID.fromString("0000110100001000800000805F9B34FB");
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
9个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这