MySQL批量插入百万条数据

最新推荐文章于 2024-08-13 04:10:37 发布

tx_1509239073

最新推荐文章于 2024-08-13 04:10:37 发布

阅读量2.7w

点赞数 6

分类专栏： mysql thinkphp

本文链接：https://blog.csdn.net/tx_1509239073/article/details/87972866

版权

thinkphp 同时被 2 个专栏收录

14 篇文章 0 订阅

订阅专栏

mysql

12 篇文章 0 订阅

订阅专栏

这是个面试题，我的第一想法是foreach 一条条插入。凉凉！！！

1.建库建表million_data test 字段 id name time

2.由于单条循环插入肯定没有拼接一条sql语句快的，所以直接拼接一条，运行 Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 1099 bytes) in ，果断修改php.ini中，然后重启

;memory_limit = 128M
memory_limit = -1

3.继续运行代码,Error while sending STMT_PREPARE packet. PID=1600,mysql根据配置文件会限制server接受的数据包大小。

有时候大的插入和更新会受max_allowed_packet 参数限制，导致写入或者更新失败。查询然后更改my.ini。更改后重启查询。

max_allowed_packet = 1024M

4.运行代码

Db::transaction(function () {
    $str = '';
    for ($i=0; $i < 1000000; $i++) { 
          $tmp = random_self(6);
          $str .= "('".$tmp."','".date('Y-m-d H:i:s',time())."'),";                     
     }
     $insertSql = 'insert into test (name,time) values'.rtrim($str,',');
     Db::execute($insertSql);
     // $usersList = Db::name('test')->field('id,name,time')->select();
     // dump($usersList);
});

function random_self($length = 6, $type = 'all'){
    $config = array(
        'number' => '1234567890',
        'letter' => 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
        'string' => 'abcdefghjkmnpqrstuvwxyzABCDEFGHJKMNPQRSTUVWXYZ23456789',
        'all' => 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890'
    );

    if (!isset($config[$type]))
        $type = 'string';
    $string = $config[$type];

    $code = '';
    $strlen = strlen($string) - 1;
    for ($i = 0; $i < $length; $i++) {
        $code .= $string{mt_rand(0, $strlen)};
    }
    return  strtolower($code);
}

5.优化参数

innodb_flush_log_at_trx_commit = 0

0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。

1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。

2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。

当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。

当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。。
当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

sync_binlog

该参数控制着二进制日志写入磁盘的过程。

该参数的有效值为0 、1、N：

0：默认值。事务提交后，将二进制日志从缓冲写入磁盘，但是不进行刷新操作（fsync()），此时只是写入了操作系统缓冲，若操作系统宕机则会丢失部分二进制日志。

1：事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存。