将MySQL数据导入到SQL,批量拆分SQL语句为作业并批处理

作为DBA,时不时会遭受将数据导入到数据库的情事,借使工作或研发提供一个包括上百万行INSERT语句的本子文件,而且这个INSERT
语句没有使用GO来进行批处理拆分,那么直接运用SQLCMD来进行会发觉该进度消耗大批量物理内存并缓缓没有数据写入,尽管脚本中每一行都添加了GO,你仍旧会发觉那插入成效太差,让您无法忍受,怎么搞呢,下边小代码帮你折腾下:

用作DBA,时不时会遇中校数据导入到数据库的状态,若是工作或研发提供一个涵盖上百万行INSERT语句的剧本文件,而且这一个INSERT
语句没有应用GO来举行批处理拆分,那么直接拔取SQLCMD来执行会意识该进度消耗多量大体内存并暂缓没有多少写入,尽管脚本中每一行都添加了GO,你依旧会意识那插入效用太差,让你无法忍受,怎么搞呢,上边小代码帮您折腾下:

乘机一代的前行,社会的前行,各类技术无独有偶五花八门杂乱无章不可胜计(写作文吗!!!)

对于部分数据量较大的系统,数据库面临的标题除了查询功能低下,还有就是数量入库时间长。更加像报表系统,天天消费在数码导入上的时日或许会长达多少个小时或十多少个时辰之久。因而,优化数据库插入质量是很有含义的。

$old_file_path= 'C:\SQL001.TXT'
$new_file_path='C:\SQL001_New'
$tran_rows=10
$file_rows=30
$current_file_id=0

$line_num=0
$sr1=[IO.StreamReader]$old_file_path
$line_content=""
$row_content=""
while(-not $sr1.EndOfStream)
{
    if(($line_num%$file_rows) -eq 0)
    {
        $current_file_id=[int]$current_file_id+1
        $current_file_path = $new_file_path+"_"+$current_file_id
        $line_content=""
        $line_content |Out-File $current_file_path
    }

    if(($line_num%$tran_rows) -eq 0)
    {
        $line_content="
SET NOCOUNT ON
GO
BEGIN TRAN
BEGIN TRY
"
        $line_content |Out-File -Append $current_file_path
        $line_content=""
    }
    $tmp_content=$sr1.ReadLine()
    $row_content=$row_content+"`r"+$tmp_content

    $line_num=$line_num+1

    if((($line_num%$tran_rows) -eq 0) -or ($sr1.EndOfStream))
    {
        $row_content | Out-File -Append $current_file_path
        $row_content=""
        $batch_first_line=([int](($line_num+1)/$tran_rows-1))*$tran_rows+1
        $line_content="
COMMIT
--PRINT '执行第"+($batch_first_line)+"行到第"+($line_num)+"行成功'
END TRY
BEGIN CATCH
ROLLBACK
PRINT '执行第"+($batch_first_line)+"行到第"+($line_num)+"行失败'
END CATCH
GO
"
        $line_content | Out-File -Append $current_file_path
        Write-Host "处理到行" $line_num
    }



}

$sr1.Close()
$old_file_path= 'C:\SQL001.TXT'
$new_file_path='C:\SQL001_New'
$tran_rows=10
$file_rows=30
$current_file_id=0

$line_num=0
$sr1=[IO.StreamReader]$old_file_path
$line_content=""
$row_content=""
while(-not $sr1.EndOfStream)
{
    if(($line_num%$file_rows) -eq 0)
    {
        $current_file_id=[int]$current_file_id+1
        $current_file_path = $new_file_path+"_"+$current_file_id
        $line_content=""
        $line_content |Out-File $current_file_path
    }

    if(($line_num%$tran_rows) -eq 0)
    {
        $line_content="
SET NOCOUNT ON
GO
BEGIN TRAN
BEGIN TRY
"
        $line_content |Out-File -Append $current_file_path
        $line_content=""
    }
    $tmp_content=$sr1.ReadLine()
    $row_content=$row_content+"`r"+$tmp_content

    $line_num=$line_num+1

    if((($line_num%$tran_rows) -eq 0) -or ($sr1.EndOfStream))
    {
        $row_content | Out-File -Append $current_file_path
        $row_content=""
        $batch_first_line=([int](($line_num+1)/$tran_rows-1))*$tran_rows+1
        $line_content="
COMMIT
--PRINT '执行第"+($batch_first_line)+"行到第"+($line_num)+"行成功'
END TRY
BEGIN CATCH
ROLLBACK
PRINT '执行第"+($batch_first_line)+"行到第"+($line_num)+"行失败'
END CATCH
GO
"
        $line_content | Out-File -Append $current_file_path
        Write-Host "处理到行" $line_num
    }



}

$sr1.Close()

不扯废话,简单而言,很多供销社都会同时接纳多种数据库,由此数据在不一样数据库之间导入导出就改为一个令人蛋疼的难点,对于周期行的须求,可以付出专门的程序处理,不过对于突发性不了然的必要,就到了亟待DBA献身的时候啊,当须求将MySQL数据导入到SQL
Server中时,该怎么搞呢?

由此对MySQL
innodb的一些性质测试,发现部分可以增强insert效用的方式,供大家参考参考。

 

997755.com澳门葡京 , 

当然使用工具最简易,不过自己就忽略工具!!!

  1. 一条SQL语句插入多条数据。

二零一六年七月2日进展了下优化,当单个文件包罗过多行时,会很难编辑处理,增加了遵循行数拆分成文件的功效,同时优化下代码的可读性。

二零一六年六月2日开展了下优化,当单个文件包含过多行时,会很难编辑处理,扩展了服从行数拆分成文件的效应,同时优化下代码的可读性。

在MySQL中开创测试数据:

常用的插入语句如:

抑或看点疗效吧,原始SQL为:

要么看点疗效吧,原始SQL为:

create table tb001(c1 int auto_increment primary key,c2 varchar(2000));
insert into tb001(c2) select 'abc\r\n';
insert into tb001(c2) select '你好啊\r\n';
insert into tb001(c2) select '你好啊\n';
insert into tb001(c2) select '双引号"';
insert into tb001(c2) select '全角双引号“';
insert into tb001(c2) select '单引号''';
insert into tb001(c2) select '全角单引号’';
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('0', 'userid_0', 'content_0', 0); 
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('1', 'userid_1', 'content_1', 1); 

997755.com澳门葡京 1

997755.com澳门葡京 2

##=====================================================================##
行使mysqldump来导出与MS SQL SERVER包容的INSERT 语句:

修改成:

因此此脚本修改过的成为:

经过此脚本修改过的成为:

mysqldump --host='192.168.166.169' --port=3358 --user='mysql_admin' --password='mysql@Admin@Pwd' --skip-add-locks --compatible=mssql --complete-insert --compact --extended-insert=false --default-character-set=utf8 -t --databases 'test' --table 'tb001' >/tmp/t4.sql
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1); 

997755.com澳门葡京 3

997755.com澳门葡京 4

地方脚本的片段诠释表达:

修改后的插入操作可以压实程序的插入功能。那里第三种SQL执行效用高的严重性原因是统一前日志量(MySQL的binlog和innodb的工作让
日志)收缩了,下跌日志刷盘的数据量和频率,从而进步效用。通过统一SQL语句,同时也能压缩SQL语句解析的次数,减弱互联网传输的IO。

那般完结有以下有点:

这么落成有以下有点:

–compatible=mssql ##导出的SQL与MS SQL Server兼容
–complete-insert ##导出的INSERT语句包罗列名
–compact ##动用简单方式,不出口各样MySQL音信
–extended-insert=false ##应用一行数据一条INSERT的办法
将MySQL数据导入到SQL,批量拆分SQL语句为作业并批处理。–default-character-set=utf8 ##指定导出的字符集
-t ##-t代表只导出多少,-d代表只导出数据结构
–databases ‘test’ ##数据库名称
–table ‘CityMatchup’ ##表名

这里提供部分测试对照数据,分别是开展单条数据的导入与转化成一条SQL语句进行导入,分别测试1百、1千、1万条数据记录。

1.  采纳“SET NOCOUNT
ON”来遮掩每条语句重回影响行数,减弱与cmd窗口交互的次数;

1.  采用“SET NOCOUNT
ON”来遮掩每条语句再次来到影响行数,裁减与cmd窗口交互的次数;

导出结果为:

997755.com澳门葡京 5

2.  将每50条语句拆分到一个批处理中,下跌数据库进行语法检查分析的成本,在封装到一个事情中开展提交,减弱写日记的次数;

2.  将每50条语句拆分到一个批处理中,下降数据库举办语法检查分析的成本,在封装到一个事情中开展提交,裁减写日记的次数;

INSERT INTO "tb001" ("c1", "c2") VALUES (1,'abc\r\n');
INSERT INTO "tb001" ("c1", "c2") VALUES (2,'你好啊\r\n');
INSERT INTO "tb001" ("c1", "c2") VALUES (3,'你好啊\n');
INSERT INTO "tb001" ("c1", "c2") VALUES (4,'双引号\"');
INSERT INTO "tb001" ("c1", "c2") VALUES (5,'全角双引号“');
INSERT INTO "tb001" ("c1", "c2") VALUES (6,'单引号\'');
INSERT INTO "tb001" ("c1", "c2") VALUES (7,'全角单引号’');
  1. 在作业中开展插队处理。

3.  打印输出事务执行结果,方便排查错误(可修改代码只输出执行破产的事体)

3.  打印输出事务执行结果,方便排查错误(可修改代码只输出执行破产的业务)

对此列名用双引号的难题,可以选用SET QUOTED_IDENTIFIER ON
来处理,也足以利用SQLCMD加-I参数来拍卖
然而对文件中的单引号就无解了,MySQL中行使”\”来作为转义符,而SQL
Server中拔取多个单引号来表示一个单引号。

把插入修改成:

履行结果截图:

执行结果截图:

MySQLdump可以将数据导成INSERT语句,并提供配置包容其余数据库的参数,但出于差距数据库转义字符分歧,因而尽管使用compatible=mssql也不可以确保导出的本子能在SQL
Server中正常执行。

START TRANSACTION; 
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('0', 'userid_0', 'content_0', 0); 
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('1', 'userid_1', 'content_1', 1); 
... 
COMMIT; 

997755.com澳门葡京 6

997755.com澳门葡京 7

##===========================================================================##

使用工作可以拉长多少的插入功用,那是因为举行一个INSERT操作时,MySQL内部会树立一个业务,在业务内才开展真正插入处理操作。通过运用工作可以削减创立工作的消耗,所有插入都在实践后才开展付出操作。

====================================================

====================================================

使用SELECT INTO OUTFILE来导出多少

此处也提供了测试对照,分别是不使用工作与使用工作在记录数为1百、1千、1万的景况。

在个人电脑测试,以100条一个工作来拆分,大约1分钟可以导入50万到60万,按分裂的行数进行拆分插入作用分化,具体适用的拆分行数需求按照实际意况测试为准。

在个体电脑测试,以100条一个事务来拆分,大约1分钟可以导入50万到60万,按不一致的行数进行拆分插入效能不一致,具体适用的拆分行数必要按照实际情状测试为准。

SELECT * INTO OUTFILE '/tmp/tb001.txt' 
FIELDS TERMINATED BY '||--||' 
LINES TERMINATED BY '||==||' FROM test.tb001;

997755.com澳门葡京 8

对于超大数据量的导入,依旧引进应用csv+bcp的艺术来导入,INSERT+SQLCMD的频率始终太低太低!

对于超大数据量的导入,仍旧引进应用csv+bcp的办法来导入,INSERT+SQLCMD的频率始终太低太低!

在Linux下看看的是那般:

  1. 数量有序插入。

====================================================

====================================================

997755.com澳门葡京 9

多少有序的插入是指插入记录在主键上是形影不离排列,例如datetime是记录的主键:

没啥技术含量,厚脸拿出去分享,只是因为很久没写博客啦!

没啥技术含量,厚脸拿出去分享,只是因为很久没写博客啦!

即便如此有些乱,可是忍啦!

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('1', 'userid_1', 'content_1', 1); 
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('0', 'userid_0', 'content_0', 0); 
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('2', 'userid_2', 'content_2',2); 

997755.com澳门葡京 10

997755.com澳门葡京 11

接下来下载文件,使用notepad++打开,选取“格式”>> “转为ANSI编码格式”
,然后另存为新文件,在SQL Server中利用BULK INSERT来导入:

修改成:

CREATE TABLE tmp_tb001(id NVARCHAR(2000),c1 NVARCHAR(2000))
GO
BULK INSERT tmp_tb001
FROM 'D:\tb002.txt'
WITH(FIELDTERMINATOR='||--||',
ROWTERMINATOR='||==||'
)
GO
SELECT * FROM tmp_tb001
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('0', 'userid_0', 'content_0', 0); 
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('1', 'userid_1', 'content_1', 1); 
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('2', 'userid_2', 'content_2',2); 

也可以行使SQL Server的导入导出工具来拍卖,紧要修改分隔符。

由于数据库插入时,必要维护索引数据,无序的记录会增大维护索引的血本。大家可以参照innodb使用的B+tree索引,倘诺老是插入记录都在索
引的末尾面,索引的定位成效很高,并且对索引调整较小;要是插入的记录在目录中间,需求B+tree举办分歧合并等处理,会费用比较多划算资源,并且插入
记录的目录定位效能会下滑,数据量较大时会有反复的磁盘操作。

留意利用SELECT INTO OUTFILE导出文件时,NULL值被代表为\N,而\N在导入SQL
Server时会被当下字符串“\N”来拍卖,因而提出先成立一个完全由NVARCHAR类型列组成的表来“暂存”导入的时候,然后通过多少清理后再导入正式表中,对于懒与一列一列折腾的人的话,可以拼下SQL来获取表的富有列转换:

上面提供随机数据与各类数据的属性相比较,分别是记录为1百、1千、1万、10万、100万。

SELECT 
'CASE WHEN ['+T1.name+']=''\N'' THEN NULL ELSE ['+T1.name+'] END AS ['+T1.name+'],'
FROM sys.all_columns T1
WHERE T1.object_id=OBJECT_ID('tmp_tb001')

997755.com澳门葡京 12

鉴于我们强行将\N当成NULL来转换,难免会造成损伤,将忠实数据就为’\N‘的值变为NULL,因而导完数据后检查是必须的。

从测试结果来看,该优化措施的特性有所进步,不过升高并不是很显然。

终极语句为:

特性综合测试:

SELECT 
CASE WHEN [id]='\N' THEN NULL ELSE [id] END AS [id],
CASE WHEN [c1]='\N' THEN NULL ELSE [c1] END AS [c1]
FROM tmp_tb001

此间提供了并且接纳方面三种艺术开展INSERT成效优化的测试。

执行结果为:

997755.com澳门葡京 13

997755.com澳门葡京 14

从测试结果可以观望,合并数据+事务的措施在较小数据量时,质量升高是很明显的,数据量较大时(1千万以上),品质会急剧下跌,那是由于此时数据量
当先了innodb_buffer的容量,每便一定索引涉及较多的磁盘读写操作,质量下落较快。而使用合并数据+事务+有序数据的措施在数据量达到千万级
以上表现如故是卓绝,在数据量较大时,有序数据索引定位较为有利,不需求反复对磁盘进行读写操作,所以可以维持较高的特性。

##=======================================================================##

注意事项:

导出INSERT脚本存在转义字符单引号的难点,同时导出数据不包括GO,在要求大批量数目导入到SQL
SERVER时存在严重的性质难题,能够尝试参考本人的《Powershell–批量拆分SQL语句为业务并批处理》来处理,但也是题材多多。

1.
SQL语句是有长度限制,在举办数据统一在同一SQL中必须不可以领先SQL长度限制,通过max_allowed_packet配置可以修改,默许是1M,测试时修改为8M。

而导出文件然后导入的章程,须求对文本进行两遍转换,文件较大时notepad++可能依然不能打卡,UE能稍微给力点,但面对某些个G的公文文件也是心有余而力不足,同时NULL值处理也亟需慎重对待。

2.
事情需要控制大小,事务太大可能会潜移默化执行的频率。MySQL有innodb_log_buffer_size配置项,超越那个值会把innodb的数码刷到磁盘中,那时,成效会有着下滑。所以相比较好的做法是,在数量达到那些那一个值前进展作业提交。

##========================================================================##

如上就是本文的全体内容,希望对大家的学习抱有辅助,也可望大家多多协助脚本之家。

好啊,是时候上妹子啊。

你或许感兴趣的稿子:

  • MYSQL开发性质探讨之批量陈设数据的优化措施
  • MySql批量插入优化Sql执行功用实例详解
  • mysql怎么样优化插入记录速度
  • MySQL达成批量安排以优化性能的学科
  • 剖析优化MySQL插入方法的七个高招
  • 深深mysql并发插入优化详解
  • MySQL
    大数据量火速插入方法和说话优化分享
  • mysql 数据插入优化措施
  • MySql中把一个表的数据插入到另一个表中的贯彻代码
  • mysql中快捷插入百万条测试数据的方法

997755.com澳门葡京 15

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website