根据两个业务明细数据，根据当前月份生成合并的明细和汇总。当前月份根据当前时间取得，每日执行一次，每个月反复进行，直到当月结束，保留当月的最后一次数据作为历史存留。

涉及到的表：

（1）sf_chargemonth：业务明细数据，按月做分区，分区关键字chargemonth，每月数据100万。

（2）sf_ystzmonth：第二张业务明细数据，无分区。每月数据2万。

（3）RT_2109_suo_mx：合并的明细数据，无分区，合并后数据规模为每月100万。

（4）RT_2109_suo：合并的汇总数据，每月数据50条。

原来的执行过程共分三个步骤，执行时间近1小时：

（1）删除掉RT_2109_suo和RT_2109_suo_mx当月的数据。

Delete from RT_2109_suo where chargemonth= '201703 ';

Delete from RT_2109_suo_mx where chargemonth=‘201703’;

（2）合并明细数据并添加到RT_2109_suo_mx。

Insert into RT_2109_suo_mx(chargemonth,dept_id,housecode,field1)

Select ‘201703’, dept_id,housecode,sum(field1)field1

(select dept_id,housecode,field1 from sf_chargemonth where substr(chargemonth,1,6)=’201703’

Union all

Select dept_id,housecode,field1 from sf_ystzmonth where substr(chargemonth,1,6)<=’201703’ and cwmonth=’201703’

) a Group by dept_id,housecode

（3）汇总RT_2109_suo_mx到RT_2109_suo。

Insert into RT_2109_suo(chargemonth,dept_id,field1)

Select ‘201703’,dept_id,sum(field1) field1

From RT_2109_suo_mx

Where chargemonth=’201703’

Group by dept_id;

1、RT_2109_suo_mx表结构的优化

该表每月固定生成，并且都是以月作为一个关键字，首先考虑的是给该表做分区。

CREATE TABLE RT_2109_suo_mx2 

  PARTITION BY list(CHARGEMONTH) (

                     PARTITIONp201703 values ('201703'),

                     PARTITIONp201704 values ('201704')

　　) as select * from RT_2109_suo_mx;

 

  droptable RT_2109_suo_mx;

  altertable RT_2109_suo_mx2 rename to RT_2109_suo_mx; 

  altertable RT_2109_suo_mx add primary key (CHARGEMONTH, DEPT_ID,HOUSECODE);

2、RT_2109_suo_mx删除的优化

原来的删除语句为：Delete from RT_2109_suo_mx where chargemonth=’201703’;

原来的删除只是利用了索引。表做分区后的分析效果如下：

http://r.photo.store.qq.com/psb?/V12jEOXn4NDAgC/gpz157mfhgRDB78rRQAVNWEAHImZp.*OBH0xnqMuD0o!/o/dGkBAAAAAAAA&ek=1&kp=1&pt=0&bo=tAN1AbQDdQEDEDU!&su=1168463537&tm=1495861200&sce=0-12-12&rf=2-9

加入分区以后，删除时用到了分区，加快了操作的速度。

但是，一个分区的数据量仍然是百万级的，直接进行delete仍然较慢，时间达到的半小时以上。

测试了几种办法：

（1）删除该分区，然后再添加该分区：alter table RT_2109_suo_mx drop partitionp201703;

Alter table RT_2109_suo_mx add partitionp201703 values(‘201703’)。删除的速度很快，但是，删除分区以后，表的索引失效。

（2）Truncate该分区：alter table RT_2109_suo_mx truncate partitionp20103; 删除的速度很快，但是，表的索引失效。

（3）Truncate分区并更新索引：alter table RT_2109_suo_mx truncate partitionp20103 update indexes。删除的速度很快，索引也正常

3、合并明细数据语句的优化

这块主要是针对两个明细表的查询。

select dept_id,housecode,field1 from sf_chargemonth where substr(chargemonth,1,6)=’201703’。这个表是分区的，同时使用了substr，分区也没有利用上，索引也没有用上，把这个语句单独拿出来，查询的时间超过150秒。

修改为：select dept_id,housecode,field1 from sf_chargemonth where chargemonth like ’201703%’。这就利用上了分区条件，把这个语句单独拿出来，查询的时间在1秒以内。

select dept_id,housecode,field1 from sf_ystzmonth where substr(chargemonth,1,6)<=’201703’ and cwmonth=’201703’。这个语句，substr(chargemonth,1,6)肯定是没有利用上索引，cwmonth这个条件字段没有索引。前面的chargemonth这个条件，不太好修改。Cwmonth经过业务考虑，增加索引。

4、汇总语句的优化

这块还主要是针对查询语句的优化。

（1）原来的效率

Select ‘201703’,dept_id,sum(field1) field1

From RT_2109_suo_mx

Where chargemonth=’201703’ and rttype=’25日报表’。这个条件在前面的描述中未加。SQL的分析效果如下，没有利用上分区，全表扫描，只利用了主键索引：

http://r.photo.store.qq.com/psb?/V12jEOXn4NDAgC/UxM8d4RtVZJLhosP.zdubXHm0ukwCeFLTABmLGdtJH0!/o/dGgBAAAAAAAA&ek=1&kp=1&pt=0&bo=rAPlAKwD5QADEDU!&su=138659409&tm=1495861200&sce=0-12-12&rf=2-9

（2）修改为：Select ‘201703’,dept_id,sum(field1) field1

From RT_2109_suo_mx

Where chargemonth=’201703’。单分区扫描。

http://r.photo.store.qq.com/psb?/V12jEOXn4NDAgC/iSzktvJVuCqQb0YMjr6KQwD76B6BWajDjiSvlSOJ1aU!/o/dGgBAAAAAAAA&ek=1&kp=1&pt=0&bo=jAO0AIwDtAADEDU!&su=1150385&tm=1495861200&sce=0-12-12&rf=2-9

（3）指定分区：Select ‘201703’,dept_id,sum(field1) field1

From RT_2109_suo_mx partition(p201703)

Where chargemonth=’201703’ and rttype=’25日报表’。

还是进行全表扫描，只利用了主键索引。

http://r.photo.store.qq.com/psb?/V12jEOXn4NDAgC/39ke5a6hVyp.Kyk8NdOPpo*cojlcjpXTInOhIg2HVAA!/o/dGgBAAAAAAAA&ek=1&kp=1&pt=0&bo=nAMAAZwDAAEDEDU!&su=1158891665&tm=1495861200&sce=0-12-12&rf=2-9

（4）考虑业务的上要求，目前rttype只有一个值，可以不考虑这个查询条件。索引优化为：

Select ‘201703’,dept_id,sum(field1) field1 From RT_2109_suo_mx

Where chargemonth=’201703’；

或

Select ‘201703’,dept_id,sum(field1) field1 From RT_2109_suo_mxpartition(p201703)

1、调整两个表的设置：

（1）RT_2109_suo_mx修改为按chargemonth做分区

（2）sf_ystzmonth增加索引cwmonth

2、执行过程还是三个步骤，执行时间不到5分钟：

（1）删除掉RT_2109_suo和RT_2109_suo_mx当月的数据。

Delete from RT_2109_suo where chargemonth=’201703’;

alter table RT_2109_suo_mx truncate partitionp20103 update indexes;

（2）合并明细数据并添加到RT_2109_suo_mx。

Insert into RT_2109_suo_mx(chargemonth,dept_id,housecode,field1)

Select ‘201703’, dept_id,housecode,sum(field1) field1

(select dept_id,housecode,field1 from sf_chargemonth where chargemonthlike ’201703%’

Union all

Select dept_id,housecode,field1 from sf_ystzmonth where substr(chargemonth,1,6)<=’201703’ and cwmonth=’201703’

) a Group by dept_id,housecode

（3）汇总RT_2109_suo_mx到RT_2109_suo。

Insert into RT_2109_suo(chargemonth,dept_id,field1)

Select ‘201703’,dept_id,sum(field1) field1

From RT_2109_suo_mx

Where chargemonth=’201703’

       Group by dept_id;