没有高并发，亿万级数据优化的sql脚本一

        大家好，笔者是位从业多年的普通程序员。很高兴能够在这里结识大家。来到这里的应该都是些正在做技术的和想做技术的并且对技术充满激情和向往，肯学习，努力学习的人。

        从odbc，jdbc到hibernate，mybatis框架到大数据，高并发，高可用，分布式，万亿级数据优化，直到现在的AI生成。它们底层的核心目标都是【数据】，数据处理异常关键。在实际学习中，相信很多读者找到的很多资源都是些‘高，大，上’ 的。什么高并发，高可用，分布式，千万级数据处理，亿级数据优化等等。但是在实际开发工作中涉及到数据库的交互方面，相信绝大多数读者接触最多的还是sql的“根基”，涉及到的“三高”处理，万亿级别的数据优化肯定还是相对较少，况且目前国内80%以上的企业都是中小企业，数据流量并不如某宝，某音，某多那么强大。能够接触并学习到海量数据处理的精髓固然很好，但是那些真的很适合你么，真的是你们目前最迫切需要的么？

        笔者这次开个专栏来比较全面系统的讲解分析sql脚本。从最基础的知识开始慢慢到较深入的探讨学习。该专栏会涉及或穿插到基础语法，易错点，性能优化，常用系统函数，系统表，系统功能（包含存储过程，游标，触发器，定时器等）。在专栏内容中笔者还会结合自己工作多年实战积累的经验和遇到过的业务场景来给读者讲解分析相关知识。 浏览完本专栏后，相信读者对数据库sql脚本的认知肯定会有所提高，也会提高读者在工作实战中处理相关业务需求的能力。

        本专栏主要以oracle为例，同时也会穿插db2。至今为止，企业中用的多的还是oracle，mysql，金融领域db2也会涉及比较多。mysql相对轻量，只要读者掌握好了oracle和db2，mysql自然不在话下。

        数据库的sql脚本入门是比较容易的，但是要掌握好并灵活的运用到实战工作中还是需要花费些心思和精力的。

        咱们开始开篇。以oracle为例，先来看看几个sql语句。

        insert into mytalbe(id,name,age) values(1,'老鬼'，18);
        select * from mytable;
        update mytable set name = '小鬼' where id = 2;
        delete from mytable where id= 1;

        最最基础的增删改查。接触过数据库的读者应该都明其意。不过，笔者清晰的记得自己初学阶段过后没多久delete 和update语句总是会写错。delete语句会写成  delete * from mytable ;  update 语句会写成  update table mytabe；这就是少练导致的结果，学习任何语言脚本都需要勤敲代码，多练习。我猜测工作当中会有部分人就会把带where条件的group by语句写错。如果不熟练该语法的话 group by, where ,having 三个关键词的位置会很容易放错。

        再来看看一些稍微复杂些的sql语句：

        1：新增语句
          insert all into mytable(id, name, age) values (a,b,c)
                select b.* from (     
                select x as a,x as b,x as c from dual
                union all
                select x1 as a,x1 as b,x1 as c from dual
         ) b;

        上面这条insert all into语句就是oracle 的批量插入语句。在实际业务场景中批量插入可是非常普遍的业务操作。为了便于读者更好理解这条语句，我结合mybatis，完整的书写一个例子：

 insert all into mytable
     values
     (id, ID_ASTS, G_NAME, BASE_DATE, CASH_MONTH, DAILYCF_SUM, CREATED_TIME, UPDATED_TIME)
     select A.*
     from (
       <foreach collection="list" item="item" index="inde" separator="UNION ALL">
               select
               #{item.id, jdbcType=DECIMAL} id,
               #{item.idAsts, jdbcType=VARCHAR} ID_ASTS_RSPS,
               #{item.groupName, jdbcType=VARCHAR} GROUP_NAME,
               #{item.yyyymmdd, jdbcType=VARCHAR} BASE_DATE,
               #{item.cashMh, jdbcType=VARCHAR} CASH_MONTH,
               #{item.dailycf, jdbcType=VARCHAR} DAILYCF_SUM,
               sysdate CREATED_TIME,
               sysdate UPDATED_TIME
               from dual
             </foreach>
) A 。上面list列表内容通过业务层调用传入实现业务数据的批量插入。

        批量插入的好处就是可以减少数据库连接的创建和关闭，减少事务提交次数从而很大程度上提高sql的整体执行效率。oracle批量插入sql语法设计的很好记忆，它与单条插入就是多了个 all关键字。读者把这条语句掌握好，当你以后遇到数据量较多需要批量操作，或者列表批量操作的时候，就能够使用上它了。

        oracle的批量插入语句还有四种写法：

        insert all
            into mytable2(ext, mydate) values('22',sysdate)
            into mytable3(bondid, sbond, issuer) values('123','ee', '567')
        select 1 from dual;

        这种写法可以同时进行多个表的插入操作，请记得最后必须要带上 select 子句。

        insert into mytable2(ext, mydate)
        select '1', sysdate from dual union all
        select '2', sysdate from dual;

        上面这种写法相对来说比第一种写法简单些，再来看看下面这种写法

        begin
                  insert into mytable2(name, age, created_time, id)
                  values('11111',123,sysdate,2);
                  insert into mytable2(name, age, created_time, id)
                  values('22222',133,sysdate,3);
         end;

        这种写法中间部分可以由服务端传入list进行迭代循环赋值。这样也能达到批量新增操作的目的。

        还可实现批量表数据复制的方式达到批量新增的效果。

        insert into mytable1 select * from mytable2 rr where rr.id<100 ;

        把表mytable2中id小于100的所有记录复制到mytable1表中。注意，这条语句可执行的前提是需要表mytable1和表mytable2的表结构相同。

        oracle 批量insert 常见的写法就是上面五种了。读者最喜欢哪一种呢！

        还有种写法也可以执行批量insert操作。可能一些初入数据库江湖的读者对它并不熟悉。这种写法功能强大，可增可改。笔者在工作中也会经常用到它。适合它的使用场景也很多。例如有这样一个需求场景：老板需要做一个流量统计看板需求，该需求其中一个功能就是要统计用户每天的访问次数。那么这里就会存在这么一个问题点，每天可能都会出现注册的新用户。那么统计用户每天的访问次数就需要对新老客户进行不同的逻辑处理。对新用户直接进行新增操作，对老用户则需要进行更新访问次数的操作。也就是有则更新，无则新增。遇到这种场景下面一条sql就可以搞定了。

        merge into mytable mt using(select userid as id from dual) d2
                on (mt.id = d2.id)
        when matched then
                update set mt.logincount = logincount+1, mt.updatetime = sysdate
        when not matched then
        insert
                (id, name, logincount, mydife_time, created_time)
        values
                ('seq', 'myname', 1, sysdate, sysdate); 

        这条语句简单解释下，通过服务端传过来的id，匹配mytable表id,如果有符合相等条件匹配项，就说明用户存在，进行更新操作。否则，是新用户，进行insert操作。这就是人们常说的merge语句。如果只需要新增操作的话，把when matched then子句去掉就行。同理如果只需要更新操作，把when not matched then 子句去掉就行。在使用merge操作的时候需要注意：on 条件里面的字段是不允许做更新操作的。同时需要注意on条件字段是否有为空（null）的情况。当遇到为空的情况是会比较失败（null != null）的，导致执行了非预期的分支语句。好了，oracle 常遇到的批量新增语句都讲解完了，希望读者能够掌握好这些sql语句，当遇到批量操作的场景时候就能轻松应对了。批量新增还可以借助游标来实现，有关游标的知识后续章节会有讲解。

        接着来看看db2的批量新增的写法都有哪些吧！
        insert into mytable1(id, name, age)
        values
        (1, '张三'， 18),
        (2, '李四'， 20),
        (3, '王五'， 31);

        insert into mytable1(name, age) select st_name, no_seq from mytable2 where st_name in ('统计图','管理端'); 

        merge into mytable1 as t
        using (select '用户端' name,77 no_seq from sysibm.dual union all select '政策法规' name, 78 no_seq from sysibm.dual) as s
        on t.name = s.name
        //when matched then update set 列1=s.列1
        when not matched then insert (name, age) values (s.st_name, s.no_seq);

        上面列举的是db2批量新增常用的三种写法，其中第一种写法相对于oracle 的 insert all into语句。语法上要相对简单点。第二种和第三种写法是和oracle 一样的。这里注意平常工作的时候尽量养成比较好的习惯，笔者认为有的时候要学会偷懒，在写sql的时候能够省略不写的关键字就不要写了。比如 merge into 语句，merge into mytable t 在oracle 和db2中都能够成功执行，但是merge into mytable as t 在 oracle一些版本里面执行是会报错的。会直接出现 ora02012 缺失using 关键字这个错误。还有on后面的子句尽量用小括号刮起来。

        2：删除语句
        delete from mytable e where exists (
                  select 1 from mytable2 d
                  where d.id = e.id
                  and d.age < 40
        );

        delete是删数据语句，删除就没了。所以再执行此语句时就得格外的小心了。否则，有些情况下dba都束手无策。来分析一下上面这条关联删除语句。它删除的条件是：只要在mytable表中存在这样的id字段就删除，id字段的值必须要存在于mytable2表行中且同时mytable2表中该行的age字段值要小于40。初学着看到这条sql语句可能会觉得有点绕，其实只要理解透了就很简单了。它就是根据mytable 的id作为where 条件，只不过where条件不是大于，等于，而是存在，存在另个表（mytable2）中，而且同时，需要满足另个表的一个条件，就是age字段的值要小于40。

        接着继续看下面这段sql脚本：

        begin
                for i in 1..10 loop
                    delete from mytable1 where age >100 and rownum <= 10;
                    commit;
                end loop;
         end;

        这段删除脚本涉及到了多个知识点.begin...end;是用来封装一组sql的，就是代表他是一个语句块了。for ... in ... loop   end loop, 这是 sql里面的循环for语句，哈哈，初学着是不是发现sql语言和java或者pythen语言一样，怎么也有for循环呀！for循环里面包裹着一个删除和commit提交语句。上面这段sql的脚本理解就是：循环10次删除操作，每次删除把表mytable1中age的值大于100的删除（在业务处理中，可能会认为age大于100都是脏数据啦），且每次只删除10条记录。这个在处理量大的数据的时候具有重要意义，假设符合age大于100的数据量有1亿个，那么如果一次性的删除它就可能会出现其他的问题，比如内存，带宽等等问题，带上了rownum子句就相当于把它进行分批删除处理了。
        好了，这一章节就先到这吧，这个专栏文章都会按一定的频率定时更新的哈，所以读者们不用当心后续章节等太久的问题，哈哈！后续见~