精华内容
下载资源
问答
  • 千万级数据处理

    千次阅读 2018-11-12 22:23:06
    千万级数据处理 前言     近日一次版本上线时,涉及到千万级数据处理,因一时疏漏造成Oracle数据库回滚段爆掉,造成版本重复发布。  &...

    千万级数据处理

    前言

        近日一次版本上线时,涉及到千万级数据处理,因一时疏漏造成Oracle数据库回滚段爆掉,造成版本重复发布。

        系统日常运行中,产生部分历史数据,这些数据不能删除。日积月累情况下,出现超级大的数据量。

    • 需要将客户信息历史表中客户性别为女性,出生日期在90年以前的客户存放至另一张表。目前客户信息历史表的数据量高达9000多万。

    如下SQL在执行过程:耗时40多分钟,后续变更处理方式后,耗时4-5分钟。数据库回滚段使用5G左右空间。

    --执行SQL如下,table_cust_history该表数据量高达9000多万。
    insert into table_temp
    select ... from table_cust_history
     where sex=''
       and birthday < ''; 
       
    SQl分析:
    1. 首先分析查询,因数据量大,查询条件必须走索引,才能保证查询速度
    2. 耗时40分钟的原因是,我们在创建table_temp表的时候,惯性思维给该表加的相应字段的索引,导致没插入一条数据,索引都要变动,耗时就上去了
    3. 正确的做法应该为:删除索引(主键索引要保留),插入数据,再加入索引。最后加索引耗时仅需几分钟。
    
    --执行如下删除SQl  删除1150万左右的数据,耗时20分钟 
    delete from table_cust_history
    where exists( select 1 from table_temp where '条件' )
    
    SQL分析:两张表的关联条件必须要有索引,
    
    耗时比上面SQL长,是因为这个SQL要查询多次,子查询的条件查询,删除时条件查询。
    
    

    因业务逻辑更新,需要初始化表中的数据。需要初始化的数据为4000多万。
    执行SQl更新字段包含一个带索引的字段。耗时40分钟,回滚段使用了19-20G。

    总结:索引不仅仅提升了查询的效率,同时也会降低大数据量存储更新的效率

    1. 若遇见千万级的数据查询,一定要使用索引字段限制数据量。
    2. 大数据量更新,插入数据时,建议删除无关紧要的索引,等数据操作完毕后,再加入索引。
    3. 操作千万级数据时,应提前看一下数据库回滚段设置大小。如果可以尽量大些。

    千辛万苦,熬到半夜,吸取经验,记得教训。

    希望能给前赴后继的小伙伴带来帮助。

    展开全文
  • 千万级数据查询处理方案

    千次阅读 2019-01-31 11:29:21
    因为单表数据量已经达到了千万级,再多表关联已经达到亿级数据了。之前没有接触过这么大数据量处理,所以刚接到这个项目时有点脑瓜子疼。 最开始我还是想通过优化sql与索引方式来提高查询的性能,但是效果并不...

    前段时间有一个项目涉及到了很多的查询与报表生成。因为单表数据量已经达到了千万级,再多表关联已经达到亿级数据了。之前没有接触过这么大数据量的处理,所以刚接到这个项目时有点脑瓜子疼。

    最开始我还是想通过优化sql与索引方式来提高查询的性能,但是效果并不明显。(由于业务的需求,sql关联了很多表,其真实的执行时间已经超过一分钟了【不能忍了】)

    然后找了公司的dba讨论如何解决,我提出的第一个方案是采用中间表存储历史数据,由于业务需求查询条件涉及了时间,所以中间表存储的维度是以时间来存储的。最后查询实时的数据是先查询这张历史表的数据加当天的实时数据出结果的。

    中间表的存储过程:

    reate or replace PROCEDURE JOB_PROC_OUTPATIENTS IS
    V_DATE VARCHAR2(32);  
    VE_DATE VARCHAR2(32);  
    BEGIN
        V_DATE:=TO_CHAR(SYSDATE-1,'YYYY-MM-DD hh24:mi:ss ');
        VE_DATE:=TO_CHAR(SYSDATE,'YYYY-MM-DD hh24:mi:ss ');
        --当日期大于END_DATE的时候推出循环
      INSERT INTO OUTPATIENTS(KEY,NUM,CHOSCODE,MZDATE,机构级别,机构类别,是否民营,SBK_NUM,JE,JE2) 
       select SEQ_OUTPATIENTS_KEY.NEXTVAL key,NUM,CHOSCODE,MZDATE,机构级别,机构类别,是否民营,SBK_NUM,JE,JE2
        from(
        select 
        sum(m. NUM) NUM,
           m.CHOSCODE,
           to_date(m.MZDATE,'yyyy-MM-dd') MZDATE,
           机构级别,机构类别,是否民营,
           sum(m.SBK_NUM) SBK_NUM,
           sum(m.JE) JE,
           sum(m.JE2) JE2
      from (select /*+parallel(t,32)parallel(a,32)*/
             count(1) NUM,
             t.choscode CHOSCODE,
             to_char(t.就诊时间, 'yyyy-MM-dd') MZDATE,
             机构级别,机构类别,是否民营
            ,sum(c.金额) as JE,
             sum(p.FARMERS_COMPENSATION) as JE2,
             sum(decode(a.卡类型, '1', 1, 0)) SBK_NUM
              from 门诊医嘱主表 t
              left join 一卡通业务 a
                on a.流水号 = t.卡流水
               and a.choscode = t.choscode
               and nvl(a.src_flag, '0') <> '1'
              left join sysdicthospital t1
                on t1.choscode = t.choscode
               and t1.isjkdaupload = '0'
                left join (select /*+parallel(a,32)parallel(e,32)parallel(f,32)*/
                         sum(nvl(f.金额, 0)) 金额, e.卡流水, e.choscode
                          from 门诊医嘱主表 a, 门诊处方表 e, 门诊处方明细表 f
                         where e.choscode = f.choscode
                           and e.处方号 = f.处方号
                           and a.choscode = e.choscode
                           and a.choscode = f.choscode
                           and a.卡流水 = e.卡流水
                           and nvl(a.src_flag, '0') <> '1'
                            and a.就诊时间 >=to_date(V_DATE,'yyyy-mm-dd hh24:mi:ss')
                            and a.就诊时间 <to_date(VE_DATE,'yyyy-mm-dd hh24:mi:ss')
                         group by e.卡流水, e.choscode) c
                on c.卡流水 = t.卡流水
                 left join 农合结算表 p
                on a.流水号 = p.卡流水
             where nvl(t.src_flag, '0') <> '1'
             and t.就诊时间 >=to_date(V_DATE,'yyyy-mm-dd hh24:mi:ss')
               and t.就诊时间 <to_date(VE_DATE,'yyyy-mm-dd hh24:mi:ss')
             group by t.choscode, to_char(t.就诊时间, 'yyyy-MM-dd'),机构级别,机构类别,是否民营
           ) m
     group by m.CHOSCODE, to_date(m.MZDATE,'yyyy-MM-dd'),机构级别,机构类别,是否民营);
      END;
    

     

    创建每天跑的任务:

    DECLARE 
    
      JOB NUMBER; 
    
    BEGIN 
    
      DBMS_JOB.SUBMIT(JOB, ' JOB_PROC_OUTPATIENTS;', SYSDATE, 'TRUNC(SYSDATE + 1)'); 
    
    END/ 
    
    COMMIT;

    后期我们又改用了物化视图来替换中间表:

    drop MATERIALIZED VIEW OUTPATIENTS;
    alter table OUTPATIENTS rename to OUTPATIENTS_OLD;
    CREATE MATERIALIZED VIEW OUTPATIENTSCREATE MATERIALIZED VIEW OUTPATIENTS
    BUILD IMMEDIATE 
    REFRESH  ON demand
    start with sysdate next trunc(sysdate,'dd')+1+1/24 
    AS
    select rownum   as key,
           NUM,
           CHOSCODE,
           to_date(MZDATE, 'yyyy-MM-dd') MZDATE,
           机构级别,
           机构类别,
           是否民营,
           SBK_NUM,
           sysdate  as create_time
      from (select /*+parallel(t,16)parallel(a,16)*/
             count(1) NUM,
             t.choscode CHOSCODE,
             to_char(t.就诊时间, 'yyyy-MM-dd') MZDATE,
             机构级别,
             机构类别,
             是否民营,
             sum(decode(a.卡类型, '1', 1, 0)) SBK_NUM
              from 门诊医嘱主表 t
              left join 一卡通业务 a
                on a.流水号 = t.卡流水
               and a.choscode = t.choscode
               and nvl(a.src_flag, '0') <> '1'
              left join sysdicthospital t1
                on t1.choscode = t.choscode
               and t1.isjkdaupload = '0'
             where nvl(t.src_flag, '0') <> '1'
             group by t.choscode,
                      to_char(t.就诊时间, 'yyyy-MM-dd'),
                      机构级别,
                      机构类别,
                      是否民营);

     

    展开全文
  • MySQL千万级数据处理

    万次阅读 2018-08-14 17:51:35
    目录   第一篇,优化篇 第二篇,案例篇 第一篇,优化篇 ...而事实上很多时候MySQL单表的性能依然有不少优化空间,甚至能正常支撑千万级以上的数据量: 字段 1、尽量使用TINYINT、SMALLIN...

    目录

     

    第一篇,优化篇

    第二篇,案例篇


    第一篇,优化篇

    单表优化

    除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分,拆分会带来逻辑、部署、运维的各种复杂度,一般以整型值为主的表在千万级以下,字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候MySQL单表的性能依然有不少优化空间,甚至能正常支撑千万级以上的数据量:

    字段

    1、尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT,如果非负则加上UNSIGNED

    2、VARCHAR的长度只分配真正需要的空间

    3、使用枚举或整数代替字符串类型

    4、尽量使用TIMESTAMP而非DATETIME,

    5、单表不要有太多字段,建议在20以内

    6、避免使用NULL字段,很难查询优化且占用额外索引空间

    7、用整型来存IP

    索引

    索引的种类:

    1、主键索引 (把某列设为主键,则就是主键索引)

    2、唯一索引(unique) (即该列具有唯一性,同时又是索引)

    3、index (普通索引)

    4、全文索引(FULLTEXT)

    5、复合索引(多列和在一起)

    索引建立注意事项:

    1、索引并不是越多越好,要根据查询有针对性的创建,考虑在WHERE和ORDER BY命令上涉及的列建立索引,可根据EXPLAIN来查看是否用了索引还是全表扫描

    2、应尽量避免在WHERE子句中对字段进行NULL值判断,否则将导致引擎放弃使用索引而进行全表扫描如:select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:select id from t where num = 0

    3、值分布很稀少的字段不适合建索引,例如"性别"这种只有两三个值的字段

    4、字符字段只建前缀索引

    5、并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段 sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

    6、应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

    7、应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

    8、尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num = 10 or num = 20

    可以这样查询: 

    select id from t where num = 10 

    union all 

    select id from t where num = 20

    9、下面的查询也将导致全表扫描:select id from t where name like ‘%abc%’

    若要提高效率,可以考虑全文检索。

    10、in 和 not in 也要慎用,否则会导致全表扫描,如:select id from t where num in(1,2,3) 

    对于连续的数值,能用 between 就不要用 in 了: 

    select id from t where num between 1 and 3

    11、如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:select id from t where num = @num 

    可以改为强制查询使用索引: 

    select id from t with(index(索引名)) where num = @num

    12、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。如:select id from t where num / 2 = 100 

    select id from t where substring(name, 1 ,3) = ’abc’查询name以abc开头的id列表

    分别应改为: 

    select id from t where num = 100 * 2

    select id from t where name like ‘abc%’ 

    13、在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

    14、很多时候用 exists 代替 in 是一个好的选择:select num from a where num in(select num from b) 

    用下面的语句替换: 

    select num from a where exists(select 1 from b where num = a.num)

    引擎

    目前广泛使用的是MyISAM和InnoDB两种引擎:

    MyISAM:

    MyISAM引擎是MySQL 5.1及之前版本的默认引擎,它的特点是:

    • 不支持行锁,读取时对需要读到的所有表加锁,写入时则对表加排它锁
    • 不支持事务
    • 不支持外键
    • 不支持崩溃后的安全恢复
    • 在表有读取查询的同时,支持往表中插入新纪录
    • 支持BLOB和TEXT的前500个字符索引,支持全文索引
    • 支持延迟更新索引,极大提升写入性能
    • 对于不会进行修改的表,支持压缩表,极大减少磁盘空间占用
    • 创建一张表,对会应三个文件, *.frm 记录表结构, *.myd 数据, *.myi 索引文件

    InnoDB:

    InnoDB在MySQL 5.5后成为默认索引,它的特点是:

    • 支持行锁,采用MVCC来支持高并发
    • 支持事务
    • 支持外键
    • 支持崩溃后的安全恢复
    • 不支持全文索引
    • 创建一张表,对会应一个文件 *.frm,数据存放到ibdata1

    总体来讲,MyISAM适合SELECT密集型的表,而InnoDB适合INSERT和UPDATE密集型的表

    其他注意事项:

    1、AND型查询要点(排除越多的条件放在前面):假设要查询满足条件A,B和C的文档,满足A的文档有4万,满足B的有9K,满足C的是200,那么应该用C and B and A 这样只需要查询200条记录。

    2、OR型查询要点(符合越多的条件放在前面):OR型查询与AND查询恰好相反,匹配最多的查询语句放在最前面。

    3、查询数据不建议使用 select * from table ,应用具体的字段列表代替“*”,不要返回用不到的无关字段,尤其是大数据列。

    4、在分页查询中使用 limit关键字时,应重复考虑使用索引字段来筛选来避免全表扫描,如:

    select c1, c2, c3 from table order by id asc limit 100, 100

    应尽量配合where条件来使用(大数据量情况查询效率提升10倍):

    select c1, c2, c3 from table where id > 100 order by id asc limit 0, 100

    5、在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先 create table,然后再insert。

    6、如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。但是,避免频繁创建和删除临时表,以减少系统表资源的消耗。

    7、尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。因此,使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

    8、与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

    9、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

    10、为提高系统并发能力,应尽量避免大事务操作,尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

    参数调优:

    wait_timeout:

    数据库连接闲置时间(长连接),闲置连接会占用内存资源。可以从默认的8小时减到半小时。

    max_user_connection:

    最大连接数,默认为0(无上限),最好设一个合理上限。

    thread_concurrency:

    并发线程数,设为CPU核数的两倍。

    key_buffer_size:

    索引块的缓存大小,增加会提升索引处理速度,对MyISAM表性能影响最大。对于内存4G左右,可设为256M或384M,通过查询show status like 'key_read%',保证key_reads / key_read_requests在0.1%以下最好

    innodb_buffer_pool_size:

    缓存数据块和索引块,对InnoDB表性能影响最大。通过查询 show status like 'Innodb_buffer_pool_read%',

    保证 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests 越高越好

    read_buffer_size:

    MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁,可以通过增加该变量值以及内存缓冲区大小提高其性能

    sort_buffer_size:

    MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能,可以尝试增加sort_buffer_size变量的大小

    其他参数

    读写分离

    也是目前常用的优化,从库读主库写,一般不要采用双主或多主引入很多复杂性,尽量采用文中的其他方案来提高性能。同时目前很多拆分的解决方案同时也兼顾考虑了读写分离。

    分库分表

    水平拆分

    垂直拆分

    升级硬件

    根据MySQL是CPU密集型还是I/O密集型,通过提升CPU和内存、使用SSD,都能显著提升MySQL性能。

    缓存应用

    MySQL内部:

    在系统调优参数介绍了相关设置

    数据访问层:

    比如MyBatis针对SQL语句做缓存,而Hibernate可以精确到单个记录,这里缓存的对象主要是持久化对象 Persistence Object

    应用服务层:

    这里可以通过编程手段对缓存做到更精准的控制和更多的实现策略,这里缓存的对象是数据传输对象Data Transfer Object

    Web层:针对web页面做缓存

    用户端的缓存:

    可以根据实际情况在一个层次或多个层次结合加入缓存。这里重点介绍下服务层的缓存实现,目前主要有两种方式:1、直写式(Write Through):在数据写入数据库后,同时更新缓存,维持数据库与缓存的一致性。这也是当前大多数应用缓存框架如Spring Cache的工作方式。这种实现非常简单,同步好,但效率一般。2、回写式(Write Back):当有数据要写入数据库时,只会更新缓存,然后异步批量的将缓存数据同步到数据库上。这种实现比较复杂,需要较多的应用逻辑,同时可能会产生数据库与缓存的不同步,但效率非常高。

     

    第二篇,案例篇

    实际案例:

    比如针对 1000w 条老数据加密处理

    解决的思路:

    查询优化 + 内存调优 + 分批处理 + 高效数据源 + 多线程(线程池) + 反复调试

    考虑客观因素:

    硬件设施、网络宽带

    假设1000w,分配20个线程,每页1万条

    则分页总数:1000 / 1 = 1000页

    线程平均处理分页数:(int)Math.floor(1000 / 20) = 50页

    线程处理最大分页数:50页 + 1000 % 20 = 50页

    则第一个线程处理 1 - 20页,

    第二个线程处理 21- 40页,

    第三个线程处理 41- 60页,

    ...

    依次内推,第三十个线程,处理981 - 1000页

    代码片段:

    /**
         * maysql 多线程 + 连接池,分页查询 + 批量更新示例
         * 注意:limit 参数不支持使用占位符?
         *
         * 10个线程,60万条数据,每页1w条,进行批量更新总耗时:53558毫秒,约0.90分钟(Where条件 + Limit N )
         * 20个线程,60万条数据,每页1w条,进行批量更新总耗时:53558毫秒,约0.89分钟(Where条件 + Limit N )
         * 20个线程,200万条数据,每页1w条,进行批量更新总耗时:160132毫秒,约2.67分钟(Where条件 + Limit N )
         * 40个线程,200万条数据,每页1w条,进行批量更新总耗时:199220毫秒,约3.32分钟(Where条件 + Limit N )
         *
         * 截止4月27日finance_ant_loan数据库数据:
         * LendingDetail, 总记录数 8729
         * LoanDetail, 总记录数 550417
         *
         * 截止4月27日finance_jd_loan数据库数据:
         * CUS, 总记录数 1006179
         * Loan,总记录数 32395990
         */
        @Test
        public void testMultiThreadBatchUpdate2(){
            Long beginTime = System.currentTimeMillis();
            int pageSize = 10000;
            int threads = 20;
            int resultCount = findCount();
    
            if (resultCount <= 0){
                log.info("未找到符合条件的记录!");
                return;
            }
    
            int pageTotal = (resultCount % pageSize == 0) ? (resultCount / pageSize) :  ((int)Math.floor(resultCount / pageSize) + 1);
            log.info("查询出数据库总计录数:{}", resultCount);
            log.info("每页数量:{}", pageSize);
            log.info("分页总数:{}", pageTotal);
    
            threads = getActualThreads(pageTotal, threads);
            int avgPages = (int)Math.floor(pageTotal / threads);
            int restPages = pageTotal % threads;
            log.info("子线程数:{}", threads);
            log.info("子线程平均处理分页数:{}", avgPages);
            log.info("子线程处理最大分页数:{}", avgPages + restPages);
    
            MultiThreadHandler handler = new MultiParallelThreadHandler();
            for (int i = 0; i < threads; i++){
                int fromPage = i * avgPages + 1;
                int endPage = i * avgPages + avgPages;
    
                if (i == threads - 1) {
                    endPage = endPage + restPages;
                }
    
                String threadName = "thread" + (i + 1);
                log.info("Query child thread:{} process paging interval: [{}, {}]", threadName, fromPage, endPage);
                handler.addTask(new TestThread2(fromPage, endPage, pageSize));
            }
    
            try {
                handler.run();
            } catch (ChildThreadException e) {
                log.error(e.getAllStackTraceMessage());
            }
    
            log.info("【分页查询+批量更新】结束,受影响总记录数:{},总耗时:{}毫秒", resultCount, System.currentTimeMillis() - beginTime);
        }
    
        private int findCount(){
            Connection connection = AntConnPool.getConnection();
            PreparedStatement preparedStatement;
    
            try {
                preparedStatement = connection.prepareStatement("SELECT COUNT(1) FROM test2 WHERE 1 = 1");
                ResultSet resultSet = preparedStatement.executeQuery();
                if (resultSet.next()){
                    return resultSet.getInt(1);
                }
            }catch (Exception e){
                e.printStackTrace();
                log.error("数据库操作异常!!!");
            }finally {
                ConnMgr.closeConn(connection);
            }
            return 0;
        }
    
        /**
         * @description 获得实际线程数
         * @author Zack
         * @date 15:28 2018/5/7
         * @param pageTotal
         * @param threads
         * @return int
         */
        private int getActualThreads(final int pageTotal, final int threads){
            if ((int)Math.floor(pageTotal / threads) < 1 && threads > 1){
                return getActualThreads(pageTotal, threads - 1);
            }
            return threads;
        }
    /**
    *@description 多线程处理分页
    *@author Zack
    *@date 2018/4/27
    *@version 1.0
    */
    @Slf4j
    public class TestThread2 extends MysqlParallelThread{
    
        public TestThread2(int fromPage, int endPage, int pageSize){
            super(fromPage, endPage, pageSize);
        }
    
        @Override
        public void run() {
            log.info("Query child thread:{} process paging interval: [{}, {}] started.", Thread.currentThread().getName(), getFromPage(), getEndPage());
            Long beginTime = System.currentTimeMillis();
            int maxId = 0;
            int fromIndex = (getFromPage() - 1) * getPageSize();
            Connection connection = AntConnPool.getConnection();
    
            try{
                for (int pageNo = getFromPage(); pageNo <= getEndPage(); pageNo++){
                    if (maxId != 0){
                        fromIndex = 0;
                    }
                    maxId = batchUpdate(findList(connection, fromIndex, maxId), pageNo);
                }
            }catch (Exception e){
                throw new RuntimeException(Thread.currentThread().getName() + ": throw exception");
            }finally {
                ConnMgr.closeConn(connection);
                log.info("Query child thread:{} process paging interval: [{}, {}] end. cost:{} ms.", Thread.currentThread().getName(), getFromPage(), getEndPage(), (System.currentTimeMillis() - beginTime));
            }
        }
    
        private ResultSet findList(Connection connection, int fromIndex, int maxId){
            try {
                PreparedStatement preparedStatement = connection.prepareStatement("SELECT id, name, total FROM test2 WHERE id > ? ORDER BY id ASC limit "+fromIndex+", " + getPageSize(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                preparedStatement.setInt(1, maxId);
                return preparedStatement.executeQuery();
            }catch (Exception e){
                e.printStackTrace();
                log.error("数据库操作异常!!!");
            }
            return null;
        }
    
        private int batchUpdate(ResultSet resultSet, int pageNo) {
            Long beginTime = System.currentTimeMillis();
            Connection connection = AntConnPool.getConnection();
            PreparedStatement preparedStatement;
    
            try {
                connection.setAutoCommit(false);
                preparedStatement = connection.prepareStatement("UPDATE test2 SET name = ?, total = ? WHERE id = ?");
    
                if (Objects.isNull(resultSet) || resultSet.wasNull()){
                    log.info("查询第{}页数据为空!", pageNo);
                    return 0;
                }
    
                while (resultSet.next()){
                    // 加密处理
                    preparedStatement.setString(1, SecUtil.encryption(resultSet.getString(2)));
                    preparedStatement.setInt(2, resultSet.getInt(3) + 1);
                    preparedStatement.setInt(3, resultSet.getInt(1));
                    preparedStatement.addBatch();
                }
    
                int[] countArray = preparedStatement.executeBatch();
                connection.commit();
    
                // 游标移至最后一行
                resultSet.last();
                int maxId = resultSet.getInt(1);
                log.info("子线程{}批量更新MYSQL第{}页结束,maxId:{}, 受影响记录数:{},耗时:{}毫秒", Thread.currentThread().getName(), pageNo, maxId, countArray.length, System.currentTimeMillis() - beginTime);
                return maxId;
            }catch (Exception e){
                e.printStackTrace();
                log.error("数据库操作异常!!!");
            }finally {
                ConnMgr.closeConn(connection);
            }
            return 0;
        }
    }

    总结:

    使用多线程合理分配执行任务,避免数据重复执行和漏执行。

     

    扩展阅读

    https://dev.mysql.com/doc/refman/8.0/en/optimization.html

     

     

     

     

     

     

     

     

     

     

    展开全文
  • 千万级数据量系统优化实践

    千次阅读 2020-05-04 10:31:44
    关于针对该系统的优化,我主要做了以下几点: 目录 数据库SQL语句调优,mybatis批量插入,建立索引 ...使用异步处理,校验完核心数据即可返回前台操作完成,并异步跑后续操作 多线程处理海量运算

    注:文中的“Doctor”是精分对话者,另一个我,道德约束者,话唠,请无视他。

    博主最近在做公司一个很重要的项目,有关于汽车物联网金融的,作为刚工作一年的新人来说,这是一个极好的学习机会,而且被信任的感觉很好啊有木有。简单说博主在为某实力国产汽车公司卖命,这个系统涉及到的用户有几百万,每个用户都会有很多的金融数据,金融总是要结算的,所以每个月到结算日会有千万条数据需要操作,累计有几十亿次的计算,那么面对这庞大的数据集,我是瑟瑟发抖的,之前对于优化只是建立在理论上的,这次虽然是好的实践机会,但是作为核心项目,一点也马虎不得。

    Doctor:你的废话太多了。。。

    咳咳,那么废话不说了,现在开始进入正题,本文章将会跟大家分享一下个人在优化中的一些实践和心得,不足的地方希望大家指正。

    关于针对该系统的优化,我主要做了以下几点:

    1、数据库SQL语句调优,mybatis批量插入,建立索引

    2、适当使用Redis存储不常修改但是常用的数据

    3、使用异步处理,校验完核心数据即可返回前台操作完成,并异步跑后续操作

    4、多线程处理海量运算


    该系统的架构是基于SpringCloud的微服务架构,Springboot+Mybatis+Maven+MySql整合,同时使用到Redis、kafka。

    1、数据库SQL语句调优,mybatis批量插入,建立索引

    关于SQL语句调优我想最基本的就是在项目开始阶段,根据业务需求抽象成数据库表的时候要请专门的DBA来分析建表,本系统是由我跟另一位高级开发建的表,其实后期发现并不够完美,也许跟业务变更有些大导致的吧,我认为数据库表的设计和代码一样也需要健壮性,我们的系统使用的是基于SpringCloud的微服务架构,其实更像是把单体服务拆成多个springboot小项目,既然使用敏捷迭代开发模式(我们现在的模式也可以勉强说是devops,只不过devops里面运维要做的很多工作都由我们开发做了),就要针对变动频繁的需求设计出合理健壮的数据库表结构。

    首先,要按照三范式来建表,但是既然是范式就未必一定遵守,其实有些时候,比如某张表里面有很多需要频繁加起来的字段,假设我们现在有一张结算表account,里面有三个字段:account_a、account_b、account_c,每次系统结算的时候都会从这个表里面取出这三个字段累加起来使用,那么我们可以违背范式规则,增加一个冗余字段:account_d,存放前三个字段的和,这样使用的时候就不需要取出前三个再计算,直接取最后一个字段即可。

    在往数据库批量插入数据的时候,有两种方式,一种是通过在接口实现类里对集合遍历调用mapper,这样每有一条数据就会调一次mapper插一次数据库;另一种是使用mybatis的动态语句foreach,直接把一个集合插入,这样只需要一次mybatis动态代理,速度会比前一种快很多,但是需要注意的是mysql默认接受sql的大小是1M,如果集合过大就会报异常,可以调整MySQL安装目录下的my.ini文件[mysqld段的"max_allowed_packet = 1M"来改变默认接受的大小。

    关于索引的建立,这里的实践真的是惊艳到我了,我测试将10万条数据去和一张170万条数据的表校验,对比出这10万条数据中哪些不在这表中,粗略估计最差的情况就要计算10万*170万次,也就是1700亿次,当然实际不可能这么多次,我们只是假设每次校验都校验到最后一条数据才能找到。我第一次跑的时候走了70多秒:

    然后我给where语句后面的字段加了索引,使用的是B+树算法,建立索引的过程也等了一小段时间,毕竟170万条数据,当我建立完索引之后一运行,直接把我吓到了:

    不得不感叹,这索引的强大,不实践真的没有体会,以前只是停留在理论上,这里博主还发现了一件奇怪的事情,就是我测试完一遍得到上一张图片的结果后又跑了一遍:

    这里比上一次运行又快了十倍,不知道这次的优化是怎么出来的,是一个小谜题,如果有大神知道请评论里指教。

    2、适当使用Redis存储不常修改但是常用的数据

    我们可以把常用且不常修改的信息暂存到Redis里面,比如系统维护的数据字典表里面的信息,这样校验的时候直接从内存中取,避免了数据库层的访问,可以大大提高响应速度,如果是大量信息校验的系统里面,这种写法将会很大限度的提升接口响应速度。

    下面是redis的部分配置内容:

    redis:
        connect-timeout: 20s
        database: 7        
        host: 127.0.0.1    #本地地址,实际项目看项目的地址
        jedis:
          pool:
            max-active: 200
        read-timeout: 20s

    下面是具体使用的代码:

    /**
     * <Description> 常用缓存键类 <br>
     *
     * @author Coder_gasenwell<br>
     * @version 1.0<br>
     * @createDate 2020/5/4 <br>
     */
    public interface CacheKey {
    
        /**
         * 字典缓存前缀
         */
        String PREFIX_TEST_SYS_DICT_TYPECODE = "test:sys_dict:typeCode:";
    
        /**
         * 字典缓存前缀
         */
        String PREFIX_TEST_SYS_DEPT_USERS_TREE = "test:sys_dept_users:tree:";
    }
    @Autowired
        private RedisUtils<String, Object> redisUtils;
    //摘选一个方法作为参考,只关注实现语句逻辑,每次先检索redis里面是不是有缓存,如果没有就去数据库取数据然后存缓存,如果有的话就直接使用缓存。
        @Override
        public List<SysDict> getDictSetCache(String typeCode) {
            List<SysDict> dictList = null;
            // 确认缓存中是否有字典,如果没有有可能是第一次创建缓存
            Set<String> redisKeys = redisUtils.keys(CacheKey.PREFIX_TEST_SYS_DICT_TYPECODE + "*");
            if (null != redisKeys && redisKeys.size() > 0) {
                dictList = sysDictMapper.listByTypeCode(typeCode);
                if (null != dictList && dictList.size() > 0) {
                    dictList.sort(new Comparator<SysDict>() {
                        @Override
                        //这里是对数据进行一个排序,可以忽略
                        public int compare(SysDict o1, SysDict o2) {
                            return o1.getSort() - o2.getSort();
                        }
                    });
                    redisUtils.set(CacheKey.PREFIX_TEST_SYS_DICT_TYPECODE + dictList.get(0).getTypeCode(), dictList);
                }

     

    3、使用异步处理,校验完核心数据即可返回前台操作完成,并异步跑后续操作

    虽然校验数据这里优化速度很快了,但是校验完要把海量数据取出来进行计算处理,所以为了避免从前端调一个接口让用户等太久,就把校验和后续计算分开,校验完成后把不存在的数据生产Excel表格导出,存在的数据开始走异步,此时返回前台接口调用完成。这时候前台会告诉用户校验完成,并下载到用户本地一个Excel文件。

    Spring里面内置了@Async,所以这里开启异步处理只需要在方法体上打上该注解即可,然后由主要方法调用该方法。

    4、多线程处理海量运算

     Spring是通过TaskExecutor来实现多线程的,使用Spring为我们提供的ThreadPoolTaskExecutor来实例化线程池,因为我们很多时候都是调用异步任务才会用到多线程,所以往往都会配合在启动类上打上@EnableAsync注解,在实际方法上打@Async注解,就可以开启多线程异步任务。

    @SpringBootApplication(scanBasePackages = "cn.com.test")
    @EnableFeignClients(basePackages = "cn.com.test")
    @EnableAsync
    @EnableDiscoveryClient
    @EnableScheduling
    @MapperScan("cn.com.test.mapper")
    public class ThreadConfig implements AsyncConfigurer{
       @Override
       public Executor getAsyncExecutor() {
           ThreadPoolTaskExecutor threadPool = new ThreadPoolTaskExecutor();
           //核心线程数
           threadPool.setCorePoolSize(8);
           //最大线程数
           threadPool.setMaxPoolSize(50);
           //缓冲队列
           threadPool.setQueueCapacity(10);
           threadPool.setAwaitTerminationSeconds(60);
           threadPool.setThreadNamePrefix("TestThread:");
           threadPool.initialize();
           return threadPool;
       }
    @Override
      public AsyncUncaughtExceptionHandler getAsyncUncaughtExceptionHandler() {
        return null;
      }
    
    }

    Doctor:我今天是不是很安静,我的台词都没了?

    emmm,写的太专注,忘了给Doctor加戏了,下次一定。

    Doctor: :)

    好了,以上就是我的优化实践,这次实践确实体会到性能优化的强大之处,对于企业级开发的理解也更深了一步,也颇有成就感,想起来我们毕业前院长说的一句话:希望你们随着时间沉淀技术沉淀人格。技术和人格对于一个工程师来说,都很重要。

    欢迎批评指正。

    展开全文
  • 如图:numberrecord 表 3600W数据量。 需求是去重掉 time 与 number一致的数据。 我用的去重插入复制表的方法。 目前已经执行12个小时了,一夜没睡。 试过用java去处理,多线程分页查询去重,jvm直接崩溃。 !...
  • 我在前年遇到过过亿条的数据。以至于一个处理过程要几个小时的。后面慢慢优化,查找一些经验文章。才学到了一些基本方法。综合叙之,与君探讨之。...主 要是考虑实际的数据量而定。当你创建一个新表时,可能这个表需...
  • 处理千万级数据MYSQL

    2019-08-29 18:21:57
    使用阿里云rds for MySQL数据库(就是MySQL5.6版本),有个用户上网记录表6个月的数据量近2000万,保留最近一年的数据量达到4000万,查询速度极慢,日常卡死。严重影响业务。 问题前提:老系统,当时设计系统的人...
  • 数据库 SQL千万级数据规模处理概要

    千次阅读 2015-03-11 09:54:20
    我在前年遇到过过亿条的数据。以至于一个处理过程要几个小时的。后面慢慢优化,查找一些经验文章。才学到了一些基本方法。...主要是考虑实际的数据量而定。当你创建一个新表时,可能这个表需要有索引,
  • 数据量有几百万的数据。还有一张表,tm_bus_realtime_his历史表 进行了分区。每10天一分区。每个月有3个分区。整个历史表有几亿的数据量。每个分区有千万条数据。 问题描述:  在从历史表的分区中,查询数据时,...
  • SQL千万级数据规模处理概要

    千次阅读 2010-10-05 10:14:00
    主要是考虑实际的数据量而定。当你创建一个新表时,可能这个表需要有索引,但是都要先取消索引,或者先建立表,导入数据后,再建立索引。 必要时处理完,统计完后,就备份到磁带或者其他介
  • 在Discuz!NT的企业版设计过程中,处理大数据表一直是一个让人头疼的问题
  • 数据库SQL千万级数据规模处理概要

    千次阅读 2009-07-09 13:03:00
    我在前年遇到过过亿条的数据。以至于一个处理过程要几个小时的。后面慢慢优化,查找一些经验文章。才学到了一些基本方法。...主要是考虑实际的数据量而定。当你创建一个新表时,可能这个表需要有索引,但是都要先
  • MySql 性能到底能有多高?...可是数据量到了10万,百万至千万,他的性能还能那么高吗?一点小小的失误,可能造成整个系统的改写,甚至更本系统无法正常运行!好了,不那么多废话了。用事实说话,看例子:
  • 我在前年遇到过过亿条的数据。以至于一个处理过程要几个小时的。后面慢慢优化,查找一些经验文章。才学到了一些基本方法。...主要是考虑实际的数据量而定。当你创建一个新表时,可能这个表需要有索引,但是都要...
  • 因为业务需要,不得不在生产上更新一个千万级的大表,更新过程中产生了4个G左右的日志后发现此更新会导致其他问题,取消更新,然后更正更新的逻辑后,重新对这千万级数据进行更新,产生6个多G的日志后,更新完成,...
  • 2019独角兽企业重金招聘Python工程师标准>>> ...
  • 我第一次是在dev环境跑的单元测试,没有考虑到这么大的数据量,sit造的数据量也不大,等到hotfix就懵逼了,一下查出了900多万条,仅仅是查询就耗费了很长时间,查询之后还要进行insert,每次insert还要验证数据是否...
  • NT的企业版设计过程中,处理大数据表一直是一个让人头疼的问题,特别是像主题表(topic),用户表(user)等,因为对于一个流量和发帖量都很大的论坛而言,在运行几年之后,这两个表的数据量可能会破千万(注:因为...
  • NT的企业版设计过程中,处理大数据表一直是一个让人头疼的问题,特别是像主题表(topic),用户表(user)等,因为对于一个流量和发帖量都很大的论坛而言,在运行几年之后,这两个表的数据量可能会破千万(注:因为...
  • MySQL按条件快速删除千万级数据应用场景1、基于老表建立新表2、插入数据(几千万的数据量一定要分批插入,一次50万为最佳,毕竟mysql的数据处理能力有限),可以按ID查询后插入!3、drop删除掉老表4、重命名新表为...
  • 千万级数据分页优化-1秒内处理完毕

    千次阅读 2017-07-13 18:43:51
    #分析了一下,问题出在,循环查数据库上面,不用问了,这页面打开差不多10S,还有大量的没用数据load进来了 #经过一番折腾,检查哪些关联条件没有索引,然后写出了第一种渣渣的方法,就是join,然后push上去,渣渣5.6秒...
  • 阿里会在双 11 的当天竖起一面大的电子屏幕,实时展示淘宝这一天的成绩。例如成交额、访问人数、订单、下单、成交等等。这个电子大屏的背后,就是用到我们所说的数据的实时处理技术。当然实...
  • SQL Server上面删除1.6亿条记录,不能用Truncate(因为只是删除其中少部分数据)...经过一个处理之后,我每次删除400万条记录花5 - 6分钟,删除全部1.6亿条记录花了4 - 5个小时! 为什么?? 每次删除记录,数据...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 437
精华内容 174
关键字:

千万级数据量处理