精华内容
下载资源
问答
  • 大数据应用之HBase数据插入性能优化实测教程

    引言:

    大家在使用HBase的过程中,总是面临性能优化的问题,本文从HBase客户端参数设置的角度,研究HBase客户端数据批量插入性能优化的问题。事实胜于雄辩,数据比理论更有说服力,基于此,作者设计了这么一个HBase数据插入性能优化实测实验,希望大家用自己的服务器跑出的结果,给自己一个值得信服的结论。

    一、客户单优化参数

      1.Put List Size
      HBase的Put支持单条插入,也支持批量插入。

      2. AutoFlush  
      AutoFlush指的是在每次调用HBase的Put操作,是否提交到HBase Server。 默认是true,每次会提交。如果此时是单条插入,就会有更多的IO,从而降低性能

      3.Write Buffer Size
      Write Buffer Size在AutoFlush为false的时候起作用,默认是2MB,也就是当插入数据超过2MB,就会自动提交到Server

      4.WAL
      WAL是Write Ahead Log的缩写,指的是HBase在插入操作前是否写Log。默认是打开,关掉会提高性能,但是如果系统出现故障(负责插入的Region Server  挂掉),数据可能会丢失。

    参数

    默认值

    说明

    JVM Heap Size

     

    平台不同值不同自行设置

    AutoFlush

    True

    默认逐条提交

    Put List Size

    1

    支持逐条和批量

    Write Buffer Size

    2M

    与autoflush配合使用

    Write Ahead Log

    True

    默认开启,需要手动关闭

     

     

     

     

    二、源码程序

     import java.io.IOException;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Random;
     
    import org.apache.hadoop.conf.Configuration;
    import org.apache.hadoop.hbase.HBaseConfiguration;
    import org.apache.hadoop.hbase.HColumnDescriptor;
    import org.apache.hadoop.hbase.HTableDescriptor;
    import org.apache.hadoop.hbase.KeyValue;
    import org.apache.hadoop.hbase.MasterNotRunningException;
    import org.apache.hadoop.hbase.ZooKeeperConnectionException;
    import org.apache.hadoop.hbase.client.Delete;
    import org.apache.hadoop.hbase.client.Get;
    import org.apache.hadoop.hbase.client.HBaseAdmin;
    import org.apache.hadoop.hbase.client.HTable;
    import org.apache.hadoop.hbase.client.Result;
    import org.apache.hadoop.hbase.client.ResultScanner;
    import org.apache.hadoop.hbase.client.Scan;
    import org.apache.hadoop.hbase.client.Put;
    import org.apache.hadoop.hbase.util.Bytes;
    
    /*
     * -------优化案例说明------------
     * 1.优化参数1:Autoflush                默认关闭,需要手动开启
     * 2.优化参数2:put list size            支持单条与批量
     * 3.优化参数3:JVM heap size             默认值是平台而不同,需要手动设置
     * 4.优化参数4:Write Buffer Size        默认值2M    
     * 5.优化参数5:Write Ahead Log             默认开启,需要手动关闭
     * */
    
    public class TestInsert {
        static Configuration hbaseConfig = null;
    
        public static void main(String[] args) throws Exception {
            Configuration HBASE_CONFIG = new Configuration();
            HBASE_CONFIG.set("hbase.master", "192.168.230.133:60000");
            HBASE_CONFIG.set("hbase.zookeeper.quorum", "192.168.230.133");
            HBASE_CONFIG.set("hbase.zookeeper.property.clientPort", "2181");
            hbaseConfig = HBaseConfiguration.create(HBASE_CONFIG);
            //关闭wal,autoflush,writebuffer = 24M
            insert(false,false,1024*1024*24);
            //开启AutoFlush,writebuffer = 0
            insert(false,true,0);
            //默认值,全部开启
            insert(true,true,0);
        }
    
        private static void insert(boolean wal,boolean autoFlush,long writeBuffer)
                throws IOException {
            String tableName="etltest";
            HBaseAdmin hAdmin = new HBaseAdmin(hbaseConfig);
            if (hAdmin.tableExists(tableName)) {
                hAdmin.disableTable(tableName);
                hAdmin.deleteTable(tableName);
            }
    
            HTableDescriptor t = new HTableDescriptor(tableName);
            t.addFamily(new HColumnDescriptor("f1"));
            t.addFamily(new HColumnDescriptor("f2"));
            t.addFamily(new HColumnDescriptor("f3"));
            t.addFamily(new HColumnDescriptor("f4"));
            hAdmin.createTable(t);
            System.out.println("table created");
            
            HTable table = new HTable(hbaseConfig, tableName);
            table.setAutoFlush(autoFlush);
            if(writeBuffer!=0){
                table.setWriteBufferSize(writeBuffer);
            }
            List<Put> lp = new ArrayList<Put>();
            long all = System.currentTimeMillis();
            
            System.out.println("start time = "+all);
            int count = 10000;
            byte[] buffer = new byte[128];
            Random r = new Random();
            for (int i = 1; i <= count; ++i) {
                Put p = new Put(String.format("row d",i).getBytes());
                r.nextBytes(buffer);
                p.add("f1".getBytes(), null, buffer);
                p.add("f2".getBytes(), null, buffer);
                p.add("f3".getBytes(), null, buffer);
                p.add("f4".getBytes(), null, buffer);
                p.setWriteToWAL(wal);
                lp.add(p);
                if(i%1000 == 0){
                    table.put(lp);
                    lp.clear();
                }
            }
            
            System.out.println("WAL="+wal+",autoFlush="+autoFlush+",buffer="+writeBuffer+",count="+count);
            long end = System.currentTimeMillis();
            System.out.println("total need time = "+ (end - all)*1.0/1000+"s");
            
            
            System.out.println("insert complete"+",costs:"+(System.currentTimeMillis()-all)*1.0/1000+"ms");
        }
    }

    三、集群配置

    3.1 服务器硬件配置清单

    序号

    节点名称

    CUP

    内存

    硬盘

    带宽

    1

    HMaster

     

     

     

     

    2

    HregionServer1

     

     

     

     

    3

    HregionServer2

     

     

     

     

    4

     

     

     

     

    5

     

     

     

     

     

    6

     

     

     

     

     

    7

     

     

     

     

     

    3.2 客户端硬件配置清单

    设备

    节点名称

     

    Cpu

     

     

    内存

     

     

    硬盘

     

     

    带宽

     

     

    四、测试报告

    数据量

    JVM

    AutoFlush

    Put List Size

    WriteBufferSize

    WAL

    耗时

    1000

    512m

    false

    1000

    1024*1024*24

    false

     

    2000

     

     

     

     

     

     

    5000

     

     

     

     

     

     

    10000

     

     

     

     

     

     

    20000

     

     

     

     

     

     

    50000

     

     

     

     

     

     

    100000

     

     

     

     

     

     

    200000

     

     

     

     

     

     

    500000

     

     

     

     

     

     

    100000

     

     

     

     

     

     

    备注:该技术专题讨论正在群Hadoop高级交流群:293503507同步直播中,敬请关注。

    展开全文
  • 学会这4个Excel实用技巧,数据分析立马高人一等

    千次阅读 多人点赞 2019-11-06 10:22:41
    学习R或Python,事先掌握一定的EXCEL知识是百利而无一害。EXCEL凭借其功能强大的函数、可视化图表、以及整齐排列的电子表格功能,使你能够快速而深入的洞察到数据不轻易为人所知的一面。 但与此同时,EXCEL也有...

    对于刚进入数据分析行业新手来说,EXCEL可以被当做一款入门的软件。在学习R或Python前,事先掌握一定的EXCEL知识是百利而无一害。EXCEL凭借其功能强大的函数、可视化图表、以及整齐排列的电子表格功能,使你能够快速而深入的洞察到数据不轻易为人所知的一面。

    但与此同时,EXCEL也有它的一些不足之处,即它无法非常有效的处理大型数据。这是我曾经遇到的这个问题。当我尝试使用EXCEL处理含有20万行数据的数据集时,就会发现EXCEL运行的非常吃力。EXCEL并不适用于处理海量数据,虽然在某种程度上,可以通过一些其他的方法让EXCEL处理大型数据集,但我更推荐使用BI工具去处理,而不是EXCEL。

    在这篇文章中,我将会提到一些关于EXCEL使用方面的小技巧,从而可以节省你宝贵的时间。

    一、常用的函数

    1. Vlooup:

    它可以帮助你在表格中搜索并返回相应的值。让我们来看看下面Policy表和Customer表。在Policy表中,我们需要根据共同字段 “Customer id”将Customer表内City字段的信息匹配到Policy表中。这时,我们可以使用Vlookup()函数来执行这项任务。

    对于上面的问题,我们可以在F4单元格中输入公式“=VLOOKUP(B4, $H$4:$L$15, 5, 0)”。按回车键后,在City字段下将会返回所有Customer id为1的城市名称,然后将公式复制到其他单元格中,从而匹配所有对应的值。

    提示:在复制公式中请别忘记使用符号“$”,来锁定Customer表的查询范围。这被称之为绝对引用,也是经常容易出错的地方。

    2. CONCATINATE:

    这个函数可以将两个或更多单元格的内容进行联接并存入到一个单元格中。例如:我们希望通过联接Host Name和Request path字段来创建一个新的URL字段。

    上面的问题可以通过使用公式“ =concatenate(B3,C3)” 并且下拉复制公式来解决。

    提示:相对于“concatenate”函数,我更倾向于使用连接符“&”来解决上述问题,公式为“= B3&C3”。

    3. LEN:

    这个公式可以以数字的形式返回单元格内数据的长度,包括空格和特殊符号。

    示例:=Len(B3) =23

    4. LOWER, UPPER、PROPER:

    这三个函数用以改变单元格内容的小写、大写以及首字母大写(即每个单词的第一个字母)。

    在数据分析的项目中,这些函数对于将不同大小写形式的内容转换成统一的形式将会非常有用。否则,处理这些具有不同特征的内容将会非常麻烦。

    下面的截图中,A列有五种形式的内容,而B列只有两种,这是因为我们已经将内容转换成了小写。

    5. TRIM:

    这是一个简单方便的函数,可以被用于清洗具有前缀或后缀的文本内容。通常,当你将数据库中的数据进行转储时,这些正在处理的文本数据将会保留字符串内部作为词与词之间分隔的空格。并且,如果你对这些内容不进行处理,后面的分析中将产生很多麻烦。

    6. If:

    我认为在EXCEL众多函数之中最有用的一个。当特定的事件在某个条件下为真,并且另一个条件为假时,可以使用这个公式来进行条件运算。例如:你想对每个销售订单进行评级,“高级”和“低级”。假设销售额大于或等于5000,则标记为“高级”,否则被标记为“低级”。

    二、由数据得出结论

    1. 数据透视表:

    每当你在处理公司的数据时,你需要从“北区分公司贡献的收入是多少?”或“客户购买产品A订单的平均价格是多少?”以及许多类似的其它问题中寻找答案。

    EXCEL的数据透视表将会帮你轻松的找到这些问题的答案。数据透视表是一款用于汇总如:计数,求平均值,求和,以及其他依据相关选择进行特征计算的功能。它可以将数据表转换为反应数据结论的表格,从而帮助你做出决策。请看下面的截图:

    从上图可以看出,左边的表格中有销售产品的细节内容,即以区域分布和产品的对应关系匹配到每一个客户。在右边的表格中,我们按不同区域进行了汇总,并且帮助我们得出了南区有着最高销售额的结论。

    创建数据透视表的方法:

    第一步:点击数据列表内的任何区域,选择:插入—数据透视表。EXCEL将会自动选择包含数据的区域,包括标题名称。如果系统自动选择的区域不正确,则可人为的进行修改。建议将数据透视表创建到新的工作表,点击New Worksheet(新工作表),然后点击OK。

    第二步:现在,你可以看到数据透视表的选项板了,包含了所有已选的字段。你要做的就是把他们放在选项板的过滤器中,就可以看到在左边生成相应的数据透视表。

    从上图可以看到,我们将“Region”放入行,“Productid”放入列中,“Premium”放入值中。现在,数据透视表中展示了“Premium”按照不同区域、不同产品费用的汇总情况。你也可以选择计数、平均值、最小值、最大值以及其他的统计指标。

    2.创建图表:

    在EXCEL里面创建一个图表,你只要选择相应的数据,然后按F11,就会自动生成系统默认的图表。除此之外,你可以手工改变不同的图表类型。如果你倾向于在当前工作表中生成图表,可以按ALT+F1,而不是F11。

    当然,在任何一种情况下,只要你创建了图表,就可以通过定义特定数据源来展示期望的信息。

    三、数据清洗

    1.删除重复值:

    EXCEL有内置的功能,可以删除表中的重复值。它可以删除所选列中所含的重复值,也就是说,如果选择了两列,就会查找两列数据的相同组合,并删除。

    如上图所示,可以看到A001 和 A002有重复的值,但是如果同时选定“ID”和“Name”列,将只会删除重复值(A002,2)。

    按照下列步骤操作可以删除重复值:选择所需数据-转到数据面板-删除重复值

    2.文本分列:

    假设你的数据存储在一列中,如下图所示:

    如上如所示,我们可以看到A列中单元格内容被“;”所区分。我们需要将其进行分列,建议使用EXCEL的文本分列功能。按照下面的步骤可以实现分列:

    选择A1:A6点击:数据—分列

    上图中,有两个选项,“分隔符号”和“固定宽度”。我选择“分隔符号”是因为有分隔符“;”。如果我们希望按照宽度分列,例如:前四个字符为第一列,第五到第十个字符为第二列,则可以选择按固定宽度分列。

    点击下一步—点击“分号”,然后下一步,然后点击完成。

    四、基本的快捷键

    按Ctrl + [向下|向上箭头]:移动到当前列的最底部或最顶部,按Ctrl + [向左|向右箭头],移动到当前行的最左端和最右端。按Ctrl + Shift +向下/向上箭头:选择包括从当前单元格直到最顶部或最底部范围内的数据。Ctrl + Home:定位到单元格A1Ctrl + End:导航到包含数据的最右下角的单元格ALT + F1:创建基于所选数据集的图表。按Ctrl + Shift + L:激活自动筛选数据功能。Alt +向下箭头:打开下拉自动筛选的菜单。ALT + D + S:要排序的数据集按Ctrl + O:打开一个新的工作簿按Ctrl + N:创建一个新的工作簿F4:选择范围,并且按F4键,可以将数据引用改为绝对引用,混合引用,相对引用。注意:这不是一个详尽的清单,从字面上讲,我使用快捷键完成了平日工作的80%。

    EXCEL作为使用最广泛的数据统计分析软件,无论你是小白还是资深用户,总会有一些东西值得你去学习。

    展开全文
  • 如果您指定了ON DUPLICATE KEY UPDATE,并且插入后会导致一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧UPDATE。例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果: ...

    如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE。例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果:


    mysqlINSERT INTO table (a,b,c) VALUES (1,2,3)

    -> ON DUPLICATE KEY UPDATE cc=c+1;

    mysqlUPDATE table SET cc=c+1 WHERE a=1;


    如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。


    注释:如果列b也是唯一列,则INSERT与此UPDATE语句相当:


    mysql> UPDATE table SET cc=c+1 WHERE a=1 OR b=2 LIMIT 1;


    如果a=1 OR b=2与多个行向匹配,则只有一个行被更新。通常,您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT…UPDATE语句的INSERT部分引用列值。换句话说,如果没有发生重复关键字冲突,则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT…UPDATE语句中有意义,其它时候会返回NULL。


    示例:


    mysqlINSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)

     

    -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);


    本语句与以下两个语句作用相同:


    mysqlINSERT INTO table (a,b,c) VALUES (1,2,3)

    -> ON DUPLICATE KEY UPDATE c=3;

    mysqlINSERT INTO table (a,b,c) VALUES (4,5,6)

    -> ON DUPLICATE KEY UPDATE c=9;


    当您使用ON DUPLICATE KEY UPDATE时,DELAYED选项被忽略。


    关于MySQL大数据量快速插入方法和语句优化的操作就介绍到这里了,希望本次的介绍能够对您有所收获!



    MySQL大数据量快速插入方法和语句优化是本文我们主要要介绍的内容,接下来我们就来一一介绍,希望能够让您有所收获!


    INSERT语句的速度


    插入一个记录需要的时间由下列因素组成,其中的数字表示大约比例:


    连接:(3)


    发送查询给服务器:(2)


    分析查询:(2)


    插入记录:(1x记录大小)


    插入索引:(1x索引)


    关闭:(1)


    这不考虑打开表的初始开销,每个并发运行的查询打开。


    表的大小以logN (B树)的速度减慢索引的插入。


    加快插入的一些方法


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


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


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


    当从一个文本文件装载一个表时,使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。参见13.2.5节,“LOAD DATA INFILE语法”。


    当表有很多索引时,有可能要多做些工作使得LOAD DATA INFILE更快些。使用下列过程:


    有选择地用CREATE TABLE创建表


    执行FLUSH TABLES语句或命令mysqladmin flush-tables。使用myisamchk –keys-used=0 -rq /path/to/db/tbl_name。这将从表中取消所有索引的使用。


    用LOAD DATA INFILE把数据插入到表中,因为不更新任何索引,因此很快。 如果只想在以后读取表,使用myisampack压缩它。参见15.1.3.3节,“压缩表特性”。


    用myisamchk -r -q /path/to/db/tbl_name重新创建索引。这将在写入磁盘前在内存中创建索引树,并且它更快,因为避免了大量磁盘搜索。结果索引树也被完美地平衡。 执行FLUSH TABLES语句或mysqladmin flush-tables命令。


    请注意如果插入一个空MyISAM表,LOAD DATA INFILE也可以执行前面的优化;主要不同处是可以让myisamchk为创建索引分配更多的临时内存,比执行LOAD DATA INFILE语句时为服务器重新创建索引分配得要多。


    也可以使用ALTER TABLE tbl_name DISABLE KEYS代替myisamchk –keys-used=0 -rq/path/to/db/tbl_name,使用ALTER TABLE tbl_name ENABLE KEYS代替myisamchk -r -q/path/to/db/tbl_name。使用这种方式,还可以跳过FLUSH TABLES。


    锁定表可以加速用多个语句执行的INSERT操作:


    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来加快插入。


    锁定也将降低多连接测试的整体时间,尽管因为它们等候锁定最大等待时间将上升。例如:


    Connection 1 does 1000 inserts

    Connections 2, 3, and 4 do 1 insert

    Connection 5 does 1000 inserts


    如果不使用锁定,2、3和4将在1和5前完成。如果使用锁定,2、3和4将可能不在1或5前完成,但是整体时间应该快大约40%。


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


    INSERT装载数据比LOAD DATA INFILE要慢得多,即使是使用上述的策略。


    为了对LOAD DATA INFILE和INSERT在MyISAM表得到更快的速度,通过增加key_buffer_size系统变量来扩大 键高速缓冲区。


    INSERT语法


    INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name [(col_name,...)]

    VALUES ({expr | DEFAULT},...),(...),...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]


    或:


    INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name

    SET col_name={expr | DEFAULT}, ...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]


    或:


    INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name [(col_name,...)]

    SELECT ...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]


    一、DELAYED 的使用


    使用延迟插入操作DELAYED调节符应用于INSERT和REPLACE语句。当DELAYED插入操作到达的时候,服务器把数据行放入一个队列中,并立即给客户端返回一个状态信息,这样客户端就可以在数据表被真正地插入记录之前继续进行操作了。如果读取者从该数据表中读取数据,队列中的数据就会被保持着,直到没有读取者为止。


    接着服务器开始插入延迟数据行(delayed-row)队列中的数据行。在插入操作的同时,服务器还要检查是否有新的读取请求到达和等待。如果有,延迟数据行队列就被挂起,允许读取者继续操作。当没有读取者的时候,服务器再次开始插入延迟的数据行。这个过程一直进行,直到队列空了为止。


    几点要注意事项


    INSERT DELAYED应该仅用于指定值清单的INSERT语句。服务器忽略用于INSERT DELAYED…SELECT语句的DELAYED。服务器忽略用于INSERT DELAYED…ON DUPLICATE UPDATE语句的DELAYED。


    因为在行被插入前,语句立刻返回,所以您不能使用LAST_INSERT_ID()来获取AUTO_INCREMENT值。AUTO_INCREMENT值可能由语句生成。


    对于SELECT语句,DELAYED行不可见,直到这些行确实被插入了为止。


    DELAYED在从属复制服务器中被忽略了,因为DELAYED不会在从属服务器中产生与主服务器不一样的数据。注意,目前在队列中的各行只保存在存储器中,直到它们被插入到表中为止。这意味着,如果您强行中止了mysqld(例如,使用kill -9)或者如果mysqld意外停止,则所有没有被写入磁盘的行都会丢失。


    二、IGNORE的使用


    IGNORE是MySQL相对于标准SQL的扩展。如果在新表中有重复关键字,或者当STRICT模式启动后出现警告,则使用IGNORE控制ALTER TABLE的运行。


    如果没有指定IGNORE,当重复关键字错误发生时,复制操作被放弃,返回前一步骤。


    如果指定了IGNORE,则对于有重复关键字的行,只使用第一行,其它有冲突的行被删除。并且,对错误值进行修正,使之尽量接近正确值。insert ignore into tb(…) value(…)这样不用校验是否存在了,有则忽略,无则添加。


    三、ON DUPLICATE KEY UPDATE的使用


    如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE。例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果:


    mysqlINSERT INTO table (a,b,c) VALUES (1,2,3)

    -> ON DUPLICATE KEY UPDATE cc=c+1;

    mysqlUPDATE table SET cc=c+1 WHERE a=1;


    如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。


    注释:如果列b也是唯一列,则INSERT与此UPDATE语句相当:


    mysql> UPDATE table SET cc=c+1 WHERE a=1 OR b=2 LIMIT 1;


    如果a=1 OR b=2与多个行向匹配,则只有一个行被更新。通常,您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT…UPDATE语句的INSERT部分引用列值。换句话说,如果没有发生重复关键字冲突,则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT…UPDATE语句中有意义,其它时候会返回NULL。


    示例:


    mysqlINSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)

     

    -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);


    本语句与以下两个语句作用相同:


    mysqlINSERT INTO table (a,b,c) VALUES (1,2,3)

    -> ON DUPLICATE KEY UPDATE c=3;

    mysqlINSERT INTO table (a,b,c) VALUES (4,5,6)

    -> ON DUPLICATE KEY UPDATE c=9;


    当您使用ON DUPLICATE KEY UPDATE时,DELAYED选项被忽略。


    关于MySQL大数据量快速插入方法和语句优化的操作就介绍到这里了,希望本次的介绍能够对您有所收获!



    展开全文
  • MySql 大数据量快速插入和语句优化

    千次阅读 2011-08-16 08:16:28
    如果使用锁定,2、3和4将可能不1或5完成,但是整体时间应该快大约40%。 INSERT、UPDATE和DELETE操作MySQL中是很快的,通过为中多于大约5次连续不断地插入或更新的操作加锁,可以获得更好的整体性能。...
    INSERT语句的速度
    

    插入一个记录需要的时间由下列因素组成,其中的数字表示大约比例:
    连接:(3)
    发送查询给服务器:(2)
    分析查询:(2)
    插入记录:(1x记录大小)
    插入索引:(1x索引)
    关闭:(1)

    这不考虑打开表的初始开销,每个并发运行的查询打开。

    表的大小以logN (B树)的速度减慢索引的插入。

    加快插入的一些方法:

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

    · 如果你从不同的客户端插入很多行,能通过INSERT DELAYED语句加快速度。参见13.2.4节,“INSERT语法”。

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

    · 当从一个文本文件装载一个表时,使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。参见13.2.5节,“LOAD DATA INFILE语法”。

    · 当表有很多索引时,有可能要多做些工作使得LOAD DATA INFILE更快些。使用下列过程:

    有选择地用CREATE TABLE创建表。

    执行FLUSH TABLES语句或命令mysqladmin flush-tables。
    使用myisamchk --keys-used=0 -rq /path/to/db/tbl_name。这将从表中取消所有索引的使用。

    用LOAD DATA INFILE把数据插入到表中,因为不更新任何索引,因此很快。
    如果只想在以后读取表,使用myisampack压缩它。参见15.1.3.3节,“压缩表特性”。

    用myisamchk -r -q /path/to/db/tbl_name重新创建索引。这将在写入磁盘前在内存中创建索引树,并且它更快,因为避免了大量磁盘搜索。结果索引树也被完美地平衡。
    执行FLUSH TABLES语句或mysqladmin flush-tables命令。
    请注意如果插入一个空MyISAM表,LOAD DATA INFILE也可以执行前面的优化;主要不同处是可以让myisamchk为创建索引分配更多的临时内存,比执行LOAD DATA INFILE语句时为服务器重新创建索引分配得要多。

    也可以使用ALTER TABLE tbl_name DISABLE KEYS代替myisamchk --keys-used=0 -rq/path/to/db/tbl_name,使用ALTER TABLE tbl_name ENABLE KEYS代替myisamchk -r -q/path/to/db/tbl_name。使用这种方式,还可以跳过FLUSH TABLES。

    · 锁定表可以加速用多个语句执行的INSERT操作:

    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来加快插入。

    锁定也将降低多连接测试的整体时间,尽管因为它们等候锁定最大等待时间将上升。例如:

    Connection 1 does 1000 inserts

    Connections 2, 3, and 4 do 1 insert

    Connection 5 does 1000 inserts

    如果不使用锁定,2、3和4将在1和5前完成。如果使用锁定,2、3和4将可能不在1或5前完成,但是整体时间应该快大约40%。

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

    INSERT装载数据比LOAD DATA INFILE要慢得多,即使是使用上述的策略。

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

    INSERT语法

    INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
           [INTO] tbl_name [(col_name,...)]
           VALUES ({expr | DEFAULT},...),(...),...
           [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
    或:

    INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
           [INTO] tbl_name
           SET col_name={expr | DEFAULT}, ...
           [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
    或:

    INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
           [INTO] tbl_name [(col_name,...)]
           SELECT ...
           [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

    一、DELAYED 的使用

    使用延迟插入操作
    DELAYED调节符应用于INSERT和REPLACE语句。当DELAYED插入操作到达的时候,

    服务器把数据行放入一个队列中,并立即给客户端返回一个状态信息,这样客户

    端就可以在数据表被真正地插入记录之前继续进行操作了。如果读取者从该数据

    表中读取数据,队列中的数据就会被保持着,直到没有读取者为止。接着服务器

    开始插入延迟数据行(delayed-row)队列中的数据行。在插入操作的同时,服务器

    还要检查是否有新的读取请求到达和等待。如果有,延迟数据行队列就被挂起,

    允许读取者继续操作。当没有读取者的时候,服务器再次开始插入延迟的数据行。

    这个过程一直进行,直到队列空了为止。
    几点要注意事项:

    · INSERT DELAYED应该仅用于指定值清单的INSERT语句。服务器忽略用于INSERT DELAYED...SELECT语句的DELAYED。

    · 服务器忽略用于INSERT DELAYED...ON DUPLICATE UPDATE语句的DELAYED。

    · 因为在行被插入前,语句立刻返回,所以您不能使用LAST_INSERT_ID()来获取AUTO_INCREMENT值。AUTO_INCREMENT值可能由语句生成。

    · 对于SELECT语句,DELAYED行不可见,直到这些行确实被插入了为止。

    · DELAYED在从属复制服务器中被忽略了,因为DELAYED不会在从属服务器中产生与主服务器不一样的数据。
    注意,目前在队列中的各行只保存在存储器中,直到它们被插入到表中为止。这意味着,如果您强行中止了mysqld(例如,使用kill -9)

    或者如果mysqld意外停止,则所有没有被写入磁盘的行都会丢失。

    二、IGNORE的使用
    IGNORE是MySQL相对于标准SQL的扩展。如果在新表中有重复关键字,

    或者当STRICT模式启动后出现警告,则使用IGNORE控制ALTER TABLE的运行。

    如果没有指定IGNORE,当重复关键字错误发生时,复制操作被放弃,返回前一步骤。

    如果指定了IGNORE,则对于有重复关键字的行,只使用第一行,其它有冲突的行被删除。

    并且,对错误值进行修正,使之尽量接近正确值。
    insert ignore into tb(...) value(...)
    这样不用校验是否存在了,有则忽略,无则添加

    三、ON DUPLICATE KEY UPDATE的使用
    如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE。例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果:

    mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)

           -> ON DUPLICATE KEY UPDATE c=c+1;

    mysql> UPDATE table SET c=c+1 WHERE a=1;

    如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。


    注释:如果列b也是唯一列,则INSERT与此UPDATE语句相当:


    mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

    如果a=1 OR b=2与多个行向匹配,则只有一个行被更新。通常,您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。


    您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT…UPDATE语句的INSERT部分引用列值。换句话说,如果没有发生重复关键字冲突,则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT…UPDATE语句中有意义,其它时候会返回NULL。


    示例:


    mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)

           -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

    本语句与以下两个语句作用相同:


    mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)

           -> ON DUPLICATE KEY UPDATE c=3;

    mysql> INSERT INTO table (a,b,c) VALUES (4,5,6)

           -> ON DUPLICATE KEY UPDATE c=9;

    当您使用ON DUPLICATE KEY UPDATE时,DELAYED选项被忽略。

     
    展开全文
  • 先保证将工资表头整成一, 注意:首先要将工资表的工作表名称改为“清单”.然后同一工作簿内新建一个工作表,表中第一个单元格输入如下函数: =IF(MOD(ROW(),3)=0,"",IF(MOD(ROW(),3)=1,清单!A$1,IF(INDEX...
  • 数字化转型的推进使得越来越多企业正面临前所未有的数据规模,随着商业竞争的日趋加剧,无论是外部的用户还是公司内部的决策已经无法依赖时效...基于创新的计算存储框架,HTAP 数据库能够一份数据上同时支撑OLTP 和 O
  • KETTLE实现批量抽取多表数据(全删全) kettle抽取单张表数据我们都会做,直接表输入,表输出即可。但如果需要抽取的表比较多,还要一张一张表去配置作业么,,,,当然也是可以的,也是一种解决问题的方法。但多...
  • 我们先归纳性地假设 n−1n-1 个元素已经完成排序,现在要将第 nn 个元素插入到正确位置。如下为递归版的插入排序的代码清单:# n == len(seq)-1, seq[n] 表示序列的最后一个元素 def ins_sort_rec(seq, n): if n ...
  • 昨天练习用拉格朗日插值法的时候遇到了一个问题,书中代码清单4-1中给出的代码无法将缺失值所在的行在插值前后展现出来,而是直接将整个data print出来,这样不利于根据具体案例再次检查这几行经过处理的数据。...
  • 基于phoenix的用户清单查询

    千次阅读 2016-04-19 16:52:25
    没有分布式Nosql数据库之前,移动、电信用户扣费清单查询、通话清单查询,是一件很头疼的事情。而hbase提供了海量数据的毫秒级查询。可见,hbase是个非常好的实时查询框架,缺点就是查询功能非常薄弱,仅限于通过...
  • Spring 让 LOB 数据操作变得简单易

    千次阅读 2015-11-04 10:30:16
    转载自 Spring 让 LOB 数据操作变得简单...本文讲解了 Spring 中处理 LOB 数据的原理和方法,对于 Spring JDBC 以及 Spring 所集成的第三方 ORM 框架(包括 JPA、Hibernate 和 iBatis)如何处理 LOB 数据进行了阐述。
  • 出国清单

    万次阅读 2011-04-02 14:54:00
    4、录取函。 5、证件。 6、各种公证(未婚,出生,无犯罪记录,亲属,成绩单,毕业证书,学位证书)。 7、成绩单(多带几份)(本科,研究生)。 8、毕业证书,学位证书。 9、G/T成绩。...
  • 本文讲解了 Spring 中处理 LOB 数据的原理和方法,对于 Spring JDBC 以及 Spring 所集成的第三方 ORM 框架(包括 JPA、Hibernate 和 iBatis)如何处理 LOB 数据进行了阐述
  • 数据中台你想知道的都这里!

    千次阅读 2019-06-30 11:38:38
    数据中台和数据仓库,数据平台的关系是什么? 数据中台和业务中台的区别是什么? 数据中台建设的最大挑战是什么? 数据中台的数据质量应该如何保障? 数据中台的典型架构是怎样的? 企业...
  • 八问数据中台:关于数据中台你想知道的都这里! 原创: 筱愚她爸 凯哥讲故事系列 1周 数据中台最近特别火,各个企业都关注如何构建自己的数据中台,利用数据中台打造数据驱动的经营能力。数据中台的概念漫天...
  • 1.后台restful api,组装文件...后台接口:根据projectId查询相关数据,并将相关字段封装到word模板中,最后用流输出文件。控制层代码@RequestMapping("/exportToWord") @ResponseBody public void exportToWord(Ht
  • 命令行操作表数据 ...通过INSERT或REPLACE语句可以向表中插入一或多行数据。 语法格式: INSERT [LOW_PRIORITY | DELAYED |HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DE...
  • DB2数据备份和恢复

    千次阅读 2011-03-20 15:29:00
    本文探讨了基于 IBM DB2 的各种数据库备份与恢复策略,并附有完整的实验...若无特殊说明,以下数据库备份与恢复的试验环境均为 Windows XP + IBM DB2 V9 企业版,同样的语句也 AIX 5.3 + IBM DB2 V8.2.5 上验证通过。
  • 数据结构与算法三十题,弄懂这些面试就够了!

    万次阅读 多人点赞 2019-02-01 08:30:28
      2019-01-24 15:36:35 ...以上就是你准备编程面试需要掌握的8种数据结构。 上面的 8 种数据结构中,每种结构都有对应的面试问题,接下来的一段时间我会将这三十一道问题依旧使用动画的形式解析清楚。
  • 前端性能优化清单

    千次阅读 2018-01-26 18:36:04
    前端性能优化清单 性能很重要——我们都知道。但是,我们是否真的总是知道我们的性能瓶颈究竟是什么?是昂贵的JavaScript,慢Web字体传递,沉重的图像,或缓慢渲染?是否值得借助交叉口观察员,客户提
  • 数据仓库建设

    万次阅读 2018-07-18 23:31:52
     建设数据仓库之前,数据散落企业各部门应用的数据存储中,它们之间有着复杂的业务连接关系,从整体上看就如一张巨大的蜘蛛网:结构上错综复杂,却又四通八达。企业级数据应用上单一业务使用方便,且灵活多变...
  • Python 数据科学入门教程:Pandas

    万次阅读 多人点赞 2018-02-02 20:13:43
    Python 和 Pandas 数据分析教程 原文:Data Analysis with Python and Pandas Tutorial Introduction 译者:飞龙 协议:CC BY-NC-SA 4.0 大家好,欢迎阅读 Python 和 Pandas 数据分析系列教程。 Pandas ...
  •  初始装载只开始数据仓库使用执行一次,然而,必须要按时调度定期执行装载源数据的过程。本篇说明执行定期装载的步骤,包括识别源数据与装载类型、使用SQL和Kettle两种方法开发和测试定期装载过程。  从源...
  • 智慧城市综合管理平台功能清单

    千次阅读 2019-09-12 14:43:13
    智慧城市综合管理平台功能清单 一、九大标准子系统 序号 子系统名称 功能模块备名称 备注 1 无线数据采集子系统 用户管理 ...
  • 文章目录表的数据操作一、 表记录的插入1....使用set子句插入数据4.向表中插入图片数据。二、表记录的修改`update...set...`三、表记录的删除`delete… from…` 表的数据操作       ...
  • 这些特定维度包含从细节维度选择的中,所以叫维度子集。维度子集比细节维度小,因此更易使用,查询也更快。  本篇中将准备两个特定维度,它们均取自现有的维度:月份维度(日期维度的子集),P
  • 数据库索引的数据结构和原理介绍

    千次阅读 2019-01-20 21:19:14
    操作数据库时,如果我们建一个没加主键的表,那么当我们向表中插入数据时,这些数据会无序的存放磁盘存储器上,一的排列的很整齐, 这跟我们认知中的表很接近。当我们要查询表中数据时,这时就要拿着查询...
  • Microsoft Office Word 中的域用作文档中可能会改变的数据的占位符,并用于邮件合并文档中创建套用信函和标签。这些种类的域也称为域代码,它们不同于那些用于输入信息的域类型,如表单上。有关向文档中添加表单...
  • 数据结构(C++版)》课程设计报告 ——客户咨询管理系统 一、课程设计目的 通过课程设计,学会运用数据结构知识,针对具体应用,自己设计合理数据结构,确定存储结构,并能设计具体操作算法,选择使用具体语言进行...
  • 信息学最全知识点清单

    千次阅读 2019-02-20 15:59:15
    信息学竞赛知识清单 基本说明:带*表示省选掌握知识,带**表示较难省选知识,带!表示较偏门知识 文章目录信息学竞赛知识清单一、基础知识1、STL,参见百科STL总结...、链分治*)[ ] 动态点分治*4、贪心[ ] 常见贪心[...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 31,561
精华内容 12,624
关键字:

在数据清单前插4行