部门中一核心应用，因为各种原因其依赖的MySQL数据库一直处于高水位运行，无论是硬件资源，还是磁盘使用率或者QPS等都处于较高水位，急需在大促前完成对应的治理，降低各项指标，以保障在大促期间平稳运行，以期更好的支撑前端业务。

目前该数据库是一主两从，且都是零售的物理机，运行多年已都是过保机器。同时因为CPU和磁盘较大，已无同规格的物理机可以增加一个从库。同时其中一个从库的内存减半且磁盘还是机械盘，出故障风险极高且IO性能低导致查询偏慢，出现过多次因性能问题切到另一个从库的情况。

以下是其3台机器的硬件资源信息，MySQL版本、部署机房和硬件配置情况。其中135机器硬盘容量128T是统计显示有误，可以认为也是16T。因为磁盘做了RAID0，因此实际容量在7T左右。

IP

域名

主/从

CPU

内存

容量

机房

DISK（/export）使用率（%）

Memory使用率（%）

数据库版本

1

xxx_

主

64

256G

16T

汇天云端机房

66.3%

87.7%

5.5.14

1

xxx_

从

64

256G

16T

汇天云端机房

66.6%

85.2%

5.5.14

1

xxx_

从

64

128G

128T

廊坊机房

76.5%

57.2%

5.5.14

截止到2月底，各数据库磁盘空间占用情况如下：

IP

主从

使用大小(G)

已用比例(%)

剩余空间(G)

周增长量(G)

预计报警(d)

预计可用(d)

binlog(G)

日志(G)

1

M

5017

69

2151

9

617.1

1735.8

159.45543

6

1

S

5017

71

2151

14.8

333.2

1012.7

158.52228

1

1

S

5017

4

129000

14.4

2986

8958

158.13548

0

从上表咱们可以看出，各数据库的磁盘空间占用已处于较高水位，急需需要治理，通过结转或删除数据来降低磁盘占用比例。

数据库存在大表其中一个原因是多条业务线共用一个应用，同时代码层面抽象的部分不够抽象，扩展部分又不容易扩展，导致数据都糅合和一起。

以下是所有的表空间占用情况，可以明显看到大部分的表数据量都在千万行以上，特别是前7张表的表空间占用都在100个G以上，数据行数也都在亿级以上，最多的是status表，30亿行数据，典型的大库大表。



黄色的为主库的QPS，可以看出主库的查询量远大于从库，由于各种原因，应用代码里只有少部分的查询是走的从库，急需将部分流量大的查询接口从主库切到从库去查询；



不论是主库还是从库，都有偶发的慢SQL查询，引发磁盘繁忙，影响系统稳定性。



1.数据结转，降低磁盘使用率，处较低水位运行。

治理目标：将表空间占用大于100G的7张表（xxx_status、xxx_main、xxx_exception、xxx_product_code、xxx_item、freights_info、xxx_ext）先进行集中结转，保留一年数据后进行常态化结转，按天结转，将数据量保持在365天；

1.降低主库QPS，保障主库安全。

治理目标：将主库的高频查询切换到从库查询，使主库白天QPS降低30%，近一个月上午峰值平均在20k，下午峰值平均在25k；治理的目标为：上午峰值15k，下午峰值18k；

1.慢SQL治理，避免导致磁盘繁忙而影响整体业务。

治理目标：10s以上的彻底消除；5s以上的，消除80%；1s以上的消除60%；底数是过去一个月（1s以上慢sql）；

根据这7张表的业务属性不同，结转的类型也不相同；比如对于历史数据无意义的，可以将历史数据直接删除，比如xxx_exception；另外一类是纯历史数据，比如流水数据xxx_status表，结转方式是同步大数据平台后就可以删除；最后是业务主数据，是需要同步大数据平台和需要结转至历史库的，比如main、item和ext表等；

表名

表空间GB

索引空间GB

大数据

结转类型

开始值

完成值

xxx_status

991.65

265.29

是

删除

2020-04-3001:00:00

2022-01-01

xxx_main

611.80

149.91

是

结转

2021-09-30

2022-01-01

xxx_exception

382.80

24.65

否

删除

2018-05-1620:30:04

2022-01-01

xxx_product_code

244.18

61.54

是

删除

23亿

xxx_item

208.66

85.46

是

结转

2016-12-2913:20:33

2022-01-01

xxx_freights_info

128.78

109.03

是

结转

2018-11-2913:26:00

xxx_ext

127.36

26.07

是

结转

2019-03-2914:30:00

2022-01-01

以下的统计表格是在同步大数据平台后集中删除和结转的空间释放情况，在1个月内对数据量在1亿以上并且占用空间在100G以上的7张大表进行了删除和结转后删除，使数据在保留365天的业务承诺时间范围内，降低了470G（10%）的磁盘空间占用；



PS：红色数字部分为负值，也就是磁盘的释放空间。

运单主档查询偶发会有无任何参数的查询，引发严重慢SQL，造成数据库磁盘繁忙度严重飚高，极大地影响了其他业务操作，而由于入口众多和交叉调用，如果在入口做参数校验工作量及风险都比较大，所以采用MyBatis的插件机制在dao层做拦截，直接拒绝掉无参数的查询，上线后就再没有出现过因无参查询而出现慢SQL而导致的磁盘繁忙情况；

里的plugin配置：



ParameterInterceptor关键代码如下：



源代码如下：

;;;;*;;;;;;;;/***MyBatis拦截器，用于拦截SQL查询无入参的场景，避免全表查询**/@Intercepts({@Signature(type=,method="query",args={,,,})})publicclassParameterInterceptorimplementsInterceptor{privatefinalMapObject,ObjectmappedStatementIdMap=newHashMapObject,Object();@OverridepublicObjectintercept(Invocationinvocation)throwsInvocationTargetException,IllegalAccessException{Object[]queryArgs=();MappedStatementmappedStatement=null;if(queryArgs[0]instanceofMappedStatement){mappedStatement=(MappedStatement)queryArgs[0];}if(null!=(())){//获取SQLBoundSqlboundSql=(queryArgs[1]);ListParameterMappingparameterMappingList=();if(()){((),"查询参数为空");thrownewBadArgumentException("查询参数为空，请确认入参是否有值");}}();}@OverridepublicObjectplugin(Objecttarget){(target,this);}@OverridepublicvoidsetProperties(Propertiesproperties){for(Objectkey:()){(key,1);}}}

主库QPS高峰期达30k/s，长期处于高位运行，需要梳理出TOP10的查接口来切从库查询，而应用中接口众多，无法逐个接口查各接口的调用量，可以利用JSF的filter功能结合UMP业务监控来统计provider的调用次数，再通过Python程序获取统计数据生产统计报表。

JSF的配置文件新增filter

jsf:filterid="callFilter"ref="jsfInvokeFilter"/

JsfInvokeFilter的代码：

;;;;;;;;/***JSFfilter*JSF服务的调用次数统计*/@ComponentpublicclassJsfInvokeFilterextsAbstractFilter{/***按API接口统计方法调用量-业务监控KEY*/privatestaticfinalStringAPI_PROVIDER_METHOD_COUNT_KEY="";privatestaticfinalStringAPI_CONSUMER_METHOD_COUNT_KEY="";@OverridepublicResponseMessageinvoke(RequestMessagerequestMessage){Stringkey;if(().isProviderSide()){key=API_PROVIDER_METHOD_COUNT_KEY;}else{key=API_CONSUMER_METHOD_COUNT_KEY;}Stringmethod=()+"."+();MapString,Stringtags=newHashMapString,String(2);("bMark",method);("bCount","1");(key,tags);returngetNext().invoke(requestMessage);}}

业务监控点列表



明细项



Python脚本

importosimportopenpyxlimportjsonimportrequestsfromcookiesimportCookieimporttimeheaders={'Cookie':Cookie,'Content-Type':'application/json','token':'******','erp':'******'}defget_jsf(start_time,_time):url=''body={}params={'startTime':start_time,'Time':_time,'PointKey':'','quickTime':int((_time-start_time)/1000),'markFlag':'true','markLimit':500}res=(url=url,data=(body),params=params,headers=headers)print('url:',)print('response:',)print(key_list)all_count=key_list[0][1][0]forkeyinkey_list:values=[i,key[0],key[1][0],str(round(key[1][0]/all_count*100,2))+'%',key[1][1],key[1][2],key[1][3]](values)i+=1path=r"/Users/xxx/Documents/治理/QPS治理/"(path)转换成时间数组time_array=(dt,"%Y-%m-%d%H:%M:%S")#转换成时间戳timestamp=(time_array)returnint(timestamp*1000)if__name__=='__main__':start_time='2024-03-0612:20:00'_time='2024-03-0712:20:00'get_jsf(change_time(start_time),change_time(_time))

Cookie的代码如下：

Cookie='*****'



分析Top10接口的切从库方案：

序号

接口

日调用量

占比%

次/秒

涉及到的表

是否可以切从库

切从库方案

0

总调用量

69787485

100.0%

1114

1

35366937

50.68%

747

lxxx_mainxxx_goods_itemext_infoxxx_ext

是

单查询，在Service层加注解走从库查询

2

12212805

17.5%

235

xxx_mainxxx_main_ext_coldchainxxx_product_codexxx_ext

是

有很多地方引用这个方法，切从库需要新增API接口，在Service新增的方法上加走从库注解

3

4138702

5.93%

102

xxx_main

是

在Service层加注解走从库查询

4

3929935

5.63%

70

xxx_main两次xxx_main_ext_coldchain

是

在Service层加注解走从库查询

5

2206697

3.16%

37

否

接单框架（实现方法太多）

6

1435493

2.06%

32

xxx_main列表查询xxx_item是否查明细package_added_servicepackage_added_service_item取旧服务xxx_pay_mainxxx_extxxx_product_codexxx_main_ext_coldchain

是

有很多地方引用这个方法，切从库需要新增API接口，在Service新增的方法上加走从库注解

7

1059754

1.52%

33

xxx_mainfreights_infoxxx_enquiry_mainxxx_status两次xxx_b2b_box_itemxxx_coupon两次xxx_ext积分

是

在Service层加注解走从库查询

8

1008603

1.45%

66

xxx_mainxxx_b2b_packagexxx_extxxx_product_codexxx_main_ext_coldchainxxx_main_ext_sitefreights_infofee_detailxxx_b2b_box_item

是

在Service层加注解走从库查询

9

817341

1.17%

24

xxx_mainxxx_b2b_packagexxx_extxxx_product_codexxx_main_ext_coldchainxxx_main_ext_site

是

有很多地方引用这个方法，切从库需要新增API接口，在Service新增的方法上加走从库注解

10

730328

1.05%

18

无数据库查询

通过优化读操作切换至从库查询，降低了主库30%的QPS流量，白天峰值从25k降低到17.5k；



治理前QPS（峰值25k）



治理后QPS（峰值17.5k）

通过对慢SQL设定有针对性的治理，成功地彻底消除10s以上的慢SQL；5s以上的，消除80%；1s以上的消除60%。

关于慢SQL的治理不过多介绍，采用的都是通用分析和治理方法，有很多的文章都有介绍。需要注意的是在治理过程中要做好灰度，完全验证后再全量上线运行。

可能有同学会想到分库分表，一个是在规划中提前部署分库分表，一个是现在使用分库分表技术进行治理；关于前一个问题由于时间久远咱们不做过多讨论，关于未使用分库分表进行治理的原因是业务规划的问题，目前此应用业务较为稳定，如采用分库分表治理动作比较大风险较高，ROI不高，故以上治理方案以稳定为主降低风险为辅。

还有一个治理方案是迁云，利用云计算的弹性及快速恢复等特性降低来运行风险，因为业务的不可中断性，此方案必须是在线迁移，涉及双数据库从双写到双读，再到单读，最后单写，还有数据一致性检查和同步等，成本较高。同时云数据库未能有如此大的磁盘容量和CPU核数，所以此方案需要结合分库分表方案同时进行，更增加了成本和风险，但此方案目前是在计划中的，如业务有较大幅度增长，以上治理也已无法满足时，将采用迁云加分库分表，且分库和分表是分期进行推进。