注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

@fc_lamp

关注Web应用解决方案MySql/PHP/Python一盏名为"飞川"的灯~

 
 
 

日志

 
 

如何优化MYSQL 的 INSERT语句(LOAD DATA INFILE 要点 MySQL数据库备份)  

2012-11-21 10:53:04|  分类: Web技术-Mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
如何优化MYSQL 的 INSERT语句(下面给出一些意见)

   一 单行INSERT:如果同时从同一个客户端插入很多行,使用含多个VALUE的INSERT语句同时插入几行。这比使用单行INSERT语句快(在某些情况下快几倍)。如果你正向一个非空表添加数据,可以调节bulk_insert_buffer_size变量,使数据插入更快。

               eg: 

                  insert into  table_name (fieldone,fieldtwo) values (xx,xx); 单条的插入SQL语句
                insert into  table_name (fieldone,fieldtwo) values (xx,xx),(xx,xx),(xx,xx);所有数据组成的长的插入SQL语句

但是这有一个问题,执行单行的insert语句会出现:MYSQL SERVER HAS GONE AWAY 的错误,产生的原因是:
一种可能是发送的SQL语句太长,以致超过了max_allowed_packet的大小,解决方法为:你只要修改my.cnf,加大max_allowed_packet的值即可,或者采用锁表操作(下面将会讲到)。


    二   INSERT DELAYED:如果你从不同的客户端插入很多行,能通过INSERT DELAYED语句加快速度。

           有关insert delayed的语法,请看《官网手册》


       用MyISAM如果在表中没有删除的行,能在SELECT语句正在运行的同时插入行


    四   使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍(以下是LOAD DATA INFILE要点,以及备份数据库)。

          1 LOAD DATA INFILE语句用于高速地从一个文本文件中读取行,并装入一个表中。文件名称必须为一个文字字符串。

          2  注意,目前不能载入UCS2数据文件。

          3 文件路径问题:

             1) 如果指定了LOCAL,则文件会被客户主机上的客户端读取,并被发送到服务器。文件会被给予一个完整的路径名称,以指定确切的位置。如果给定的是一个相对的路径名称,则此名称会被理解为相对于启动客户端时所在的目录。

            2) 如果LOCAL没有被指定,则文件必须位于服务器主机上,并且被服务器直接读取。

            3) 当在服务器主机上为文件定位时,服务器使用以下规则:

                     a)如果给定了一个绝对的路径名称,则服务器使用此路径名称。

                     b)如果给定了带有一个或多个引导组件的相对路径名称,则服务器会搜索相对于服务器数据目录的文件。

                     c)如果给定了一个不带引导组件的文件名称,则服务器会在默认数据库的数据库目录中寻找文件。

             4) 出于安全原因,当读取位于服务器中的文本文件时,文件必须位于数据库目录中,或者是全体可读的。另外,要对服务器文件使用LOAD DATA INFILE,您必须拥有FILE权限。与让服务器直接读取文件相比,使用LOCAL速度略慢,这是因为文件的内容必须通过客户端发送到服务器上。不过,您不需要FILE权限来载入本地文件。

        4 索引问题

              在某些极端情况下,您可以在把文件载入到表中之前使用ALTER TABLE...DISABLE KEYS关闭LOAD DATA INFILE,或者在载入文件之后使用ALTER TABLE...ENABLE KEYS再次创建索引,使创建索引的速度更快。

        5 要从一个表中把数据写入一个文件中,应使用SELECT...INTO OUTFILE。要读取文件,放回到表中,应使用LOAD DATA INFILE。 

        6 文本格式问题:

                   文本里面只是相关的数据,不是SQL语句。你的文件内容可能是这样的:

1,"test1",3002,"954101464246"
2,"test2",3002,"954098938390"

                  1) 默认字段与一行记录的格式为:

                     a) 如果不指定FIELDS子句,则默认值为假设您写下如下语句时的值:

                               FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

                                此语解读为:

                          在新行处寻找行的边界。

                          不会跳过任何行前缀。

                在制表符处把行分解为字段。

                  不希望字段被包含在任何引号字符之中。

                        出现制表符、新行、或在‘\’前有‘\’时,理解为作为字段值一部分的文字字符。

                   b) 如果不指定LINES子句,则默认值为假设您写下如下语句时的值:

                            LINES TERMINATED BY '\n' STARTING BY ''

                     此语解读为:

               在字段之间写入制表符。

               不把字段包含在任何引号字符中。

               当字段值中出现制表符、新行或‘\’时,使用‘\’进行转义。

               在行的末端写入新行。

    注意在WINDDOWS中:如果您已经在Windows系统中生成了文本文件,您可能必须使用LINES TERMINATED BY ‘\r\n’来正确地读取文 件,因为Windows程序通常使用两个字符作为一个行终止符。部分程序,比如WordPad,当编写文件时,可能会使用\r作为行终止符。 要读取这样的文件,应使用LINES TERMINATED BY ‘\r’。

select mobile into outfile 'D:/test.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY "\r\n" FROM test;


                2) 导出与导入联合使用

                      联合使用SELECT...INTO OUTFILE和LOAD DATA INFILE来从一个数据库中把数据写入一个文件中,然后再读取文件,返回到数据       库中时,用于两个语句的field-和line-handling选项必须匹配。如:

                      假设您使用SELECT...INTO OUTFILE来编写一个的文件,字段由逗号分隔:

mysql> SELECT * INTO OUTFILE 'data.txt'
-> FIELDS TERMINATED BY ','
-> FROM table2;

                   要读取由逗号分隔的文件并返回,则正确的语句应该是:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
-> FIELDS TERMINATED BY ',';

  一个例子:一个dBASE格式的文件具有以逗号分隔并且包含在双引号中的字段。如果文件中的各行以新行为结尾,则此处所示的 语句描述了您可以用于载入文件的field-和line-handling选项:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
-> LINES TERMINATED BY '\n';


注: 1)FIELDS [OPTIONALLY] ENCLOSED BY用于控制字段的引号:即如果设置了 OPTINALLY,则ENCLOSED BY字符只被用于包 含具有字符串数据类型(比如CHAR, BINARY, TEXT或ENUM)的列中的值(这对导出数据很有用)。

      1,"a string",100.20

      2,"a string containing a , comma",102.20

       2)如果在字段值内出现ENCLOSED BY字符,则通过使用ESCAPED BY字符作为前缀,对ENCLOSED BY字符进行转义。

      4,"a string containing a \", quote and comma",102.20

      如果指定了一个空的ESCAPED BY值,则可能会生成不能被LOAD DATA INFILE正确读取的输出值。

      4,"a string containing a ", quote and comma",102.20  

      这一行中第二个字段在引号后面包含一个逗号,该引号(错误地)显示出来,作为字段的结尾。

    3)FIELDS ESCAPED BY用于控制如何写入或读取特殊字符(即对特殊字段转义)。

  3) 字段数问题

    如果您只想载入一个表的部分列,则应指定一个列清单:

mysql> LOAD DATA INFILE 'persondata.txt'
-> INTO TABLE persondata (col1,col2,...);

    如果输入文件中各字段的顺序与表中各列的顺序不同,您也必须指定一个列清单。列清单可以包含列名称或用户变量。支持SET子句。 这使您可以把输入值赋予用户变量,然后在把结果赋予列之前,对这些值进行变换。SET子句中的用户变量可以采用多种方式使用。以 下例子使用数据文件中的第一列,直接用于t1.column1的值。在用户变量被用于t2.column2值之前,把第二列赋予用户变量。该变量从 属于一个分割运行。

LOAD DATA INFILE 'file.txt'

  INTO TABLE t1

  (column1, @var1)

  SET column2 = @var1/100;

SET子句可以被用于提供不是来源于输入文件的值。以下语句把column3设置为当前的日期和时间:

LOAD DATA INFILE 'file.txt'

  INTO TABLE t1

  (column1, @var1)

  SET column2 = @var1/100;

  INTO TABLE t1

  (column1, column2)

  SET column3 = CURRENT_TIMESTAMP;

这里有一个例子,如果你的表里面有多个字段,但你只想导入某些字段值。你的文本可能是这样的:

1,"test1",3002,"954101464246"
2,"test2",3002,"954098938390"

那么你对应的SQL应该是样:

LOAD DATA LOCAL INFILE 'D:/xx/xxx.sql'

INTO TABLE `xxx_table`

FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'

(`id`,`name`,`p_id`,`code`)

需要注意的是在PHP里写此SQL时,有多个转义点:

$sql = "

LOAD DATA LOCAL INFILE 'D:/xx/xxx.sql'

INTO TABLE `xxx_table`

FIELDS TERMINATED BY ',' ENCLOSED BY '".'"'."'//这里形成 '"' 


ESCAPED BY '\\\' //这里参看:http://fc-lamp.blog.163.com/blog/static/1745666872011411058036/

LINES TERMINATED BY '\n'

(`id`,`name`,`p_id`,`code`) ";



更多信息参考:http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#load-data


补充:在备份数据库时,你可以使用 mysqldump 命令来备份整个数据库,更多参考: http://dev.mysql.com/doc/refman/5.5/en/mysqldump-sql-format.html



    五      如果表索引过多,执行下列过程会使得load data infile 更快:

              (1)有选择地用CREATE TABLE创建表。 

             (2) 执行FLUSH TABLES语句或命令mysqladmin flush-tables。 

             (3)使用myisamchk --keys-used=0 -rq/path/to/db/tbl_name。这将从表中取消所有索引的使用。 

             (4)用LOAD DATA INFILE把数据插入到表中,因为不更新任何索引,因此很快。 

            (5)如果只想在以后读取表,使用myisampack压缩它。参见15.1.3.3节,“压缩表特性”。 

           (6)用myisamchk -r -q/path/to/db/tbl_name重新创建索引。这将在写入磁盘前在内存中创建索引树,并且它更快,因为避免了大量磁盘搜索。结果索引树也被完美地平衡。 

           (7)执行FLUSH TABLES语句或mysqladmin flush-tables命令。


  六    锁表操作:锁定表可以加速用多个语句执行的INSERT操作(解决单行的insert语句过长问题)

          eg:

                   LOCK TABLES a WRITE;

                  INSERT INTO a VALUES (1,23),(2,34),(4,33);

                   INSERT INTO a VALUES (8,26),(6,29);

                   UNLOCK TABLES;

 

               这样性能会提高,因为索引缓存区仅在所有INSERT语句完成后刷新到磁盘上一次。一般有多少INSERT语句即有多少索引缓存区刷新。如果能用一个语句插入所有的行,就不需要锁定。

对于事务表,应使用BEGIN和COMMIT代替LOCK TABLES来加快插入。

INSERT、UPDATE和DELETE操作在MySQL中是很快的,通过为在一行中多于大约5次连续不断地插入或更新的操作加锁,可以获得更好的整体性能。如果在一行中进行多次插入,可以执行LOCK TABLES,随后立即执行UNLOCK TABLES(大约每1000行)以允许其它的线程访问表。这也会获得好的性能


七  为了对LOAD DATA INFILE和INSERT在MyISAM表得到更快的速度,通过增加key_buffer_size系统变量来扩大 键高速缓冲区。详见《调节服务器参数》

注: 更多信息,见《官网手册》


八  充分利用列有默认值的事实。只有当插入的值不同于默认值时,才明确地插入值。这减少MySQL需要做的语法分析从而提高插入速度。

另外:当使用MyISAM存储引擎时,MySQL使用极快速的表锁定,以便允许多次读或一次写。


另外附 mysqli 扩展

<?php
$db = new mysqli ( 'localhost', 'xxx', 'xxx', 'xxx' );
$db->query ( 'SET NAMES UTF8' );

//bind_param(oop风格)第一个参数:表示类型值有:i(整数) d(浮点) s(字符串) b(二进制包)
//第二参数例表是对应的变量值。这种绑定参数值风格像sprinf函数
$pre = $db->prepare ( 'select * from test where id in(?,?)' );
$id1 = 3;
$id2 = 5;
//这里要注意变量值必须以变量形式传入,不能直接写值(也就是不能直接传入3、5)。
$pre->bind_param ( 'ii', $id1, $id2 );
$pre->execute ();
$result = $pre->get_result ();
while ( $row = $result->fetch_assoc () )
{
var_dump ( $row );
}


另:如何安全的的做update 操作:http://dev.mysql.com/doc/refman/5.0/en/mysql-tips.html
  评论这张
 
阅读(1875)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017