精华内容
下载资源
问答
  • MySQL大数据量快速插入方法和语句优化是本文我们主要要介绍的内容,接下来我们就来一一介绍,希望能够让您有所收获!INSERT语句的速度插入一个记录需要的时间由下列因素组成,其中的数字表示大约比例:连接:(3)...

    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大数据量快速插入方法和语句优化的操作就介绍到这里了,希望本次的介绍能够对您有所收获!

    展开全文
  • 大数据应用之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同步直播中,敬请关注。

    展开全文
  • MySQL INSERT:插入数据(添加数据

    千次阅读 2019-08-23 10:33:49
    数据清单中数据的顺序要和列的顺序相对应。 2) INSERT…SET语句 语法格式为: INSERT INTO <表名> SET <列名1> = <值1>,  <列名2> = <值2>,  … 此语句用于直接给表中的某些列指定对应的列值,即要...

    基本语法

    INSERT 语句有两种语法形式,分别是 INSERT…VALUES 语句和 INSERT…SET 语句。

    1) INSERT…VALUES语句

    INSERT VALUES 的语法格式为:

    INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]
    VALUES (值1) [… , (值n) ];

    语法说明如下。

    • <表名>:指定被操作的表名。
    • <列名>:指定需要插入数据的列名。若向表中的所有列插入数据,则全部的列名均可以省略,直接采用 INSERT<表名>VALUES(…) 即可。
    • VALUES 或 VALUE 子句:该子句包含要插入的数据清单。数据清单中数据的顺序要和列的顺序相对应。

    2) INSERT…SET语句

    语法格式为:

    INSERT INTO <表名>
    SET <列名1> = <值1>,
            <列名2> = <值2>,
            …

    此语句用于直接给表中的某些列指定对应的列值,即要插入的数据的列名在 SET 子句中指定,col_name 为指定的列名,等号后面为指定的数据,而对于未指定的列,列值会指定为该列的默认值。

    由 INSERT 语句的两种形式可以看出:

    • 使用 INSERT…VALUES 语句可以向表中插入一行数据,也可以插入多行数据;
    • 使用 INSERT…SET 语句可以指定插入行中每列的值,也可以指定部分列的值;
    • INSERT…SELECT 语句向表中插入其他表的数据。
    • 采用 INSERT…SET 语句可以向表中插入部分列的值,这种方式更为灵活;
    • INSERT…VALUES 语句可以一次插入多条数据。


    在 MySQL 中,用单条 INSERT 语句处理多个插入要比使用多条 INSERT 语句更快。

    当使用单条 INSERT 语句插入多行数据的时候,只需要将每行数据用圆括号括起来即可。

    向表中的全部字段添加值

    在 test_db 数据库中创建一个课程信息表 tb_courses,包含课程编号 course_id、课程名称 course_name、课程学分 course_grade 和课程备注 course_info,输入的 SQL 语句和执行结果如下所示。

    mysql> CREATE TABLE tb_courses
        -> (
        -> course_id INT NOT NULL AUTO_INCREMENT,
        -> course_name CHAR(40) NOT NULL,
        -> course_grade FLOAT NOT NULL,
        -> course_info CHAR(100) NULL,
        -> PRIMARY KEY(course_id)
        -> );
    Query OK, 0 rows affected (0.00 sec)

    向表中所有字段插入值的方法有两种:一种是指定所有字段名;另一种是完全不指定字段名。

    【实例 1】在 tb_courses 表中插入一条新记录,course_id 值为 1,course_name 值为“Network”,course_grade 值为 3,info 值为“Computer Network”。

    在执行插入操作之前,查看 tb_courses 表的SQL语句和执行结果如下所示。

    mysql> SELECT * FROM tb_courses;
    Empty set (0.00 sec)

    查询结果显示当前表内容为空,没有数据,接下来执行插入数据的操作,输入的 SQL 语句和执行过程如下所示。

    mysql> INSERT INTO tb_courses
        -> (course_id,course_name,course_grade,course_info)
        -> VALUES(1,'Network',3,'Computer Network');
    Query OK, 1 rows affected (0.08 sec)
    mysql> SELECT * FROM tb_courses;
    +-----------+-------------+--------------+------------------+
    | course_id | course_name | course_grade | course_info      |
    +-----------+-------------+--------------+------------------+
    |         1 | Network     |            3 | Computer Network |
    +-----------+-------------+--------------+------------------+
    1 row in set (0.00 sec)

    可以看到插入记录成功。在插入数据时,指定了 tb_courses 表的所有字段,因此将为每一个字段插入新的值。

    INSERT 语句后面的列名称顺序可以不是 tb_courses 表定义时的顺序,即插入数据时,不需要按照表定义的顺序插入,只要保证值的顺序与列字段的顺序相同就可以。

    【实例 2】在 tb_courses 表中插入一条新记录,course_id 值为 2,course_name 值为“Database”,course_grade 值为 3,info值为“MySQL”。输入的 SQL 语句和执行结果如下所示。

    mysql> INSERT INTO tb_courses
        -> (course_name,course_info,course_id,course_grade)
        -> VALUES('Database','MySQL',2,3);
    Query OK, 1 rows affected (0.08 sec)
    mysql> SELECT * FROM tb_courses;
    +-----------+-------------+--------------+------------------+
    | course_id | course_name | course_grade | course_info      |
    +-----------+-------------+--------------+------------------+
    |         1 | Network     |            3 | Computer Network |
    |         2 | Database    |            3 | MySQL            |
    +-----------+-------------+--------------+------------------+
    2 rows in set (0.00 sec)

    使用 INSERT 插入数据时,允许列名称列表 column_list 为空,此时值列表中需要为表的每一个字段指定值,并且值的顺序必须和数据表中字段定义时的顺序相同。

    【实例 3】在 tb_courses 表中插入一条新记录,course_id 值为 3,course_name 值为“Java”,course_grade 值为 4,info 值为“Jave EE”。输入的 SQL 语句和执行结果如下所示。

    mysql> INSERT INTO tb_courses
        -> VLAUES(3,'Java',4,'Java EE');
    Query OK, 1 rows affected (0.08 sec)
    mysql> SELECT * FROM tb_courses;
    +-----------+-------------+--------------+------------------+
    | course_id | course_name | course_grade | course_info      |
    +-----------+-------------+--------------+------------------+
    |         1 | Network     |            3 | Computer Network |
    |         2 | Database    |            3 | MySQL            |
    |         3 | Java        |            4 | Java EE          |
    +-----------+-------------+--------------+------------------+
    3 rows in set (0.00 sec)

    INSERT 语句中没有指定插入列表,只有一个值列表。在这种情况下,值列表为每一个字段列指定插入的值,并且这些值的顺序必须和 tb_courses 表中字段定义的顺序相同。

    注意:虽然使用 INSERT 插入数据时可以忽略插入数据的列名称,若值不包含列名称,则 VALUES 关键字后面的值不仅要求完整,而且顺序必须和表定义时列的顺序相同。如果表的结构被修改,对列进行增加、删除或者位置改变操作,这些操作将使得用这种方式插入数据时的顺序也同时改变。如果指定列名称,就不会受到表结构改变的影响。

    向表中指定字段添加值

    为表的指定字段插入数据,是在 INSERT 语句中只向部分字段中插入值,而其他字段的值为表定义时的默认值。

    【实例 4】在 tb_courses 表中插入一条新记录,course_name 值为“System”,course_grade 值为 3,course_info 值为“Operating System”,输入的 SQL 语句和执行结果如下所示。

    mysql> INSERT INTO tb_courses
        -> (course_name,course_grade,course_info)
        -> VALUES('System',3,'Operation System');
    Query OK, 1 rows affected (0.08 sec)
    mysql> SELECT * FROM tb_courses;
    +-----------+-------------+--------------+------------------+
    | course_id | course_name | course_grade | course_info      |
    +-----------+-------------+--------------+------------------+
    |         1 | Network     |            3 | Computer Network |
    |         2 | Database    |            3 | MySQL            |
    |         3 | Java        |            4 | Java EE          |
    |         4 | System      |            3 | Operating System |
    +-----------+-------------+--------------+------------------+
    4 rows in set (0.00 sec)

    可以看到插入记录成功。如查询结果显示,这里的 course_id 字段自动添加了一个整数值 4。这时的 course_id 字段为表的主键,不能为空,系统自动为该字段插入自增的序列值。在插入记录时,如果某些字段没有指定插入值,MySQL 将插入该字段定义时的默认值。

    使用 INSERT INTO…FROM 语句复制表数据

    INSERT INTO…SELECT…FROM 语句用于快速地从一个或多个表中取出数据,并将这些数据作为行数据插入另一个表中。

    SELECT 子句返回的是一个查询到的结果集,INSERT 语句将这个结果集插入指定表中,结果集中的每行数据的字段数、字段的数据类型都必须与被操作的表完全一致。

    在数据库 test_db 中创建一个与 tb_courses 表结构相同的数据表 tb_courses_new,创建表的 SQL 语句和执行过程如下所示。

    mysql> CREATE TABLE tb_courses_new
        -> (
        -> course_id INT NOT NULL AUTO_INCREMENT,
        -> course_name CHAR(40) NOT NULL,
        -> course_grade FLOAT NOT NULL,
        -> course_info CHAR(100) NULL,
        -> PRIMARY KEY(course_id)
        -> );
    Query OK, 0 rows affected (0.00 sec)
    mysql> SELECT * FROM tb_courses_new;
    Empty set (0.00 sec)

    【实例 5】从 tb_courses 表中查询所有的记录,并将其插入 tb_courses_new 表中。输入的 SQL 语句和执行结果如下所示。

    mysql> INSERT INTO tb_courses_new
        -> (course_id,course_name,course_grade,course_info)
        -> SELECT course_id,course_name,course_grade,course_info
        -> FROM tb_courses;
    Query OK, 4 rows affected (0.17 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    mysql> SELECT * FROM tb_courses_new;
    +-----------+-------------+--------------+------------------+
    | course_id | course_name | course_grade | course_info      |
    +-----------+-------------+--------------+------------------+
    |         1 | Network     |            3 | Computer Network |
    |         2 | Database    |            3 | MySQL            |
    |         3 | Java        |            4 | Java EE          |
    |         4 | System      |            3 | Operating System |
    +-----------+-------------+--------------+------------------+
    4 rows in set (0.00 sec)
    展开全文
  • 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选项被忽略。

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

    千次阅读 2018-11-30 19:25:02
     把 Excel 文件导入关系数据库是数据分析业务中经常要做的事情,但许多 Excel 文件的格式并不规整,需要事先将其中的数据结构化后再用 SQL 语句写入数据库。而一般情况下,结构化的工作量会比较大,而且很难通用,...
  • 学会这4个Excel实用技巧,数据分析立马高人一等

    千次阅读 多人点赞 2019-11-06 10:22:41
    学习R或Python,事先掌握一定的EXCEL知识是百利而无一害。EXCEL凭借其功能强大的函数、可视化图表、以及整齐排列的电子表格功能,使你能够快速而深入的洞察到数据不轻易为人所知的一面。 但与此同时,EXCEL也有...
  • 在数据查询时,有时会碰到数据量很大的清单报表。用户输入的查询条件很宽泛,可能会从数据库中查出几百上千万甚至过亿的记录。如果等着把这些记录全部检索出来再生成报表呈现,那需要很长时间,用户体验恶劣;而且...
  • WAD的条件清单

    2012-07-31 15:47:06
    让用户自己决定,用户想显示什么就自己定义条件,如只想显示总数十位的公司数据WAD中“插入”---->“Web项目”---->“混合”----->“条件清单”。进行数据绑定的设置。 运行这份报表时,条件清单的控件就...
  • 出国清单

    万次阅读 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
  • 场景: 你国内上线一款游戏成熟后, 想
  • 一次实验将原始的图片数据转化为caffe可以运行的lmdb格式。 本次实验完整分析,如何训练自己的图片数据。 毕竟我们学习caffe要应用到实际的科研/工作中,而不仅仅停留实验上。 一、实验准备 1、实验数据来自国外...
  • Spring 让 LOB 数据操作变得简单易

    千次阅读 2007-08-04 23:52:00
    转自:http://www.ibm.com/developerworks/cn/java/j-lo-spring-lob/index.html 本文讲解了 Spring 中处理 LOB 数据的原理和方法,对于 Spring JDBC 以及 Spring 所
  • 数据迁移方案模板 篇一软件系统数据迁移计划模板 附件十三 数据迁移计划 数据迁移的重要事件和里程碑日期 数据迁移的备份要求 数据迁移测试结果清单 转换工作进度表 提示 ( 1)用 Microsoft Project 制作进度表 ...
  • 前端性能优化清单

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

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

    千次阅读 2019-09-12 14:43:13
    智慧城市综合管理平台功能清单 一、九大标准子系统 序号 子系统名称 功能模块备名称 备注 1 无线数据采集子系统 用户管理 ...
  • 本文以教育、金融和财务应用为例,介绍Excel XP数据分析的主要手段和操作方法。...数据排序 (1)数据清单: Excel排序和筛选的操作对象是“数据清单”。它是工作表首行的单元格内输入列标记(相当于
  • 数据库索引的数据结构和原理介绍

    千次阅读 2019-01-20 21:19:14
    操作数据库时,如果我们建一个没加主键的表,那么当我们向表中插入数据时,这些数据会无序的存放磁盘存储器上,一的排列的很整齐, 这跟我们认知中的表很接近。当我们要查询表中数据时,这时就要拿着查询...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 32,199
精华内容 12,879
关键字:

在数据清单前插四行