专栏首页程序生涯MySQL批量插入数据库实现语句性能分析

MySQL批量插入数据库实现语句性能分析

假定我们的表结构如下

CREATE TABLE example (

			example_id INT NOT NULL,

			name VARCHAR( 50 ) NOT NULL,

			value VARCHAR( 50 ) NOT NULL,

			other_value VARCHAR( 50 ) NOT NULL

			)

  通常情况下单条插入的sql语句我们会这么写:

INSERT INTO example

			(example_id, name, value, other_value)

			VALUES

			(100, 'Name 1', 'Value 1', 'Other 1');

MySQL允许我们在一条sql语句中批量插入数据,如下sql语句:

INSERT INTO example

			(example_id, name, value, other_value)

			VALUES

			(100, 'Name 1', 'Value 1', 'Other 1'),

			(101, 'Name 2', 'Value 2', 'Other 2'),

			(102, 'Name 3', 'Value 3', 'Other 3'),

			(103, 'Name 4', 'Value 4', 'Other 4');

  如果我们插入列的顺序和表中列的顺序一致的话,还可以省去列名的定义,如下sql

INSERT INTO example

			VALUES

			(100, 'Name 1', 'Value 1', 'Other 1'),

			(101, 'Name 2', 'Value 2', 'Other 2'),

			(102, 'Name 3', 'Value 3', 'Other 3'),

			(103, 'Name 4', 'Value 4', 'Other 4');

  上面看上去没什么问题,下面我来使用sql语句优化的小技巧,下面会分别进行测试,目标是插入一个空的数据表200W条数据

  第一种方法:使用insert into 插入,代码如下:

$params = array('value'=>'50');

			set_time_limit(0);

			echo date("H:i:s");

			for($i=0;$i<2000000;$i++){

			$connect_mysql->insert($params);

			};

			echo date("H:i:s");

  最后显示为:23:25:05 01:32:05 也就是花了2个小时多!

  第二种方法:使用事务提交,批量插入数据库(每隔10W条提交下)最后显示消耗的时间为:22:56:13 23:04:00 ,一共8分13秒 ,代码如下:

echo date("H:i:s");

			$connect_mysql->query('BEGIN');

			$params = array('value'=>'50');

			for($i=0;$i<2000000;$i++){

			$connect_mysql->insert($params);

			if($i%100000==0){

			$connect_mysql->query('COMMIT');

			$connect_mysql->query('BEGIN');

			}

			}

			$connect_mysql->query('COMMIT');

			echo date("H:i:s");

  第三种方法:使用优化SQL语句:将SQL语句进行拼接,使用 insert into table () values (),(),(),()然后再一次性插入,如果字符串太长,

  则需要配置下MYSQL,在mysql 命令行中运行 :set global max_allowed_packet = 2*1024*1024*10;消耗时间为:11:24:06 11:25:06;

  插入200W条测试数据仅仅用了1分钟!代码如下:

$sql= "insert into twenty_million (value) values";

			for($i=0;$i<2000000;$i++){

			$sql.="('50'),";

			};

			$sql = substr($sql,0,strlen($sql)-1);

			$connect_mysql->query($sql);

  最后总结下,在插入大批量数据时,第一种方法无疑是最差劲的,而第二种方法在实际应用中就比较广泛,第三种方法在插入测试数据或者其他低要求时比较合适,速度确实快。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 常见黑客术语

    相信大家在很多科幻电影中都看到过黑客的身影,网络上将黑客定义为“热心于计算机技术,水平高超的电脑专家,尤其是程序设计人员”,在我们的印象中,黑客既可以维护网络安...

    用户7657330
  • 网页小图标Favicon

    Favicon是favorites icon的缩写,亦被称为website icon(网页图标)、page icon(页面图标)或urlicon(URL图标)。...

    用户7657330
  • MySQL 中ANALYZE [LOCAL NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

    ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

    用户7657330
  • mysql防止网站被sql注入攻击 的3种方法

    mysql数据库一直以来都遭受到sql注入攻击的影响,很多网站,包括目前的PC端以及手机端都在使用php+mysql数据库这种架构,大多数网站受到的攻击都是与s...

    技术分享达人
  • awr性能问题排查第一篇(r3笔记第42天)

    对于awr,里面涵盖的内容比较杂,有时候看报告的时候总是不知道该怎么下手。时间长了,可能会有一些阅读习惯或者心得。今天在看大师chris lawson的一篇博文...

    jeanron100
  • SQL中PIVOT和UNPIVOT行列转换

    用户1112962
  • loadrunner笔记(二):飞机订票系统--客户信息注册

    free赖权华
  • Spring boot 自定义banner

    Spring Boot启动的时候会在命令行生成一个banner,其实这个banner是可以自己修改的,本文将会将会讲解如何修改这个banner。

    程序那些事
  • 剑指Offer LeetCode 面试题59 - I. 滑动窗口的最大

    输入: nums = [1,3,-1,-3,5,3,6,7], 和 k = 3 输出: [3,3,5,5,6,7] 解释:

    TrueDei
  • Leetcode 41 First Missing Positive 正解不是盛传的桶排序

    Given an unsorted integer array, find the first missing positive integer. For ...

    triplebee

扫码关注云+社区

领取腾讯云代金券