一、背景

部門中一核心應用，因為各種原因其依賴的MySQL數據庫一直處于高水位運行，無論是硬件資源，還是磁盤使用率或者QPS等都處于較高水位，急需在大促前完成對應的治理，降低各項指標，以保障在大促期間平穩運行，以期更好的支撐前端業務。

二、基本情況

2.1、數據庫

目前該數據庫是一主兩從，且都是零售的物理機，運行多年已都是過保機器。同時因為CPU和磁盤較大，已無同規格的物理機可以增加一個從庫。同時其中一個從庫的內存減半且磁盤還是機械盤，出故障風險極高且IO性能低導致查詢偏慢，出現過多次因性能問題切到另一個從庫的情況。

以下是其3臺機器的硬件資源信息，MySQL版本、部署機房和硬件配置情況。其中135機器硬盤容量128T是統計顯示有誤，可以認為也是16T。因為磁盤做了RAID0，因此實際容量在7T左右。

2.2、磁盤空間

截止到2月底，各數據庫磁盤空間占用情況如下：

從上表咱們可以看出，各數據庫的磁盤空間占用已處于較高水位，急需需要治理，通過結轉或刪除數據來降低磁盤占用比例。

2.3、表空間

數據庫存在大表其中一個原因是多條業務線共用一個應用，同時代碼層面抽象的部分不夠抽象，擴展部分又不容易擴展，導致數據都糅合和一起。

以下是所有的表空間占用情況，可以明顯看到大部分的表數據量都在千萬行以上，特別是前7張表的表空間占用都在100個G以上，數據行數也都在億級以上，最多的是status表，30億行數據，典型的大庫大表。

2.4、QPS情況

黃色的為主庫的QPS，可以看出主庫的查詢量遠大于從庫，由于各種原因，應用代碼里只有少部分的查詢是走的從庫，急需將部分流量大的查詢接口從主庫切到從庫去查詢；

2.5、慢SQL

不論是主庫還是從庫，都有偶發的慢SQL查詢，引發磁盤繁忙，影響系統穩定性。

三、治理目標

1.數據結轉，降低磁盤使用率，處較低水位運行。

治理目標：將表空間占用大于100G的7張表（xxx_status、xxx_main、xxx_exception、xxx_product_code、xxx_item、freights_info、xxx_extend）先進行集中結轉，保留一年數據后進行常態化結轉，按天結轉，將數據量保持在365天；

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

治理目標：將主庫的高頻查詢切換到從庫查詢，使主庫白天QPS降低30%，近一個月上午峰值平均在20k，下午峰值平均在25k；治理的目標為：上午峰值15k，下午峰值18k；

1.慢SQL治理，避免導致磁盤繁忙而影響整體業務。

治理目標：10s以上的徹底消除；5s以上的，消除80%；1s以上的消除60%；底數是過去一個月（1s以上慢sql）；

四、治理方案

4.1、大表數據結轉

根據這7張表的業務屬性不同，結轉的類型也不相同；比如對于歷史數據無意義的，可以將歷史數據直接刪除，比如xxx_exception；另外一類是純歷史數據，比如流水數據xxx_status表，結轉方式是同步大數據平臺后就可以刪除；最后是業務主數據，是需要同步大數據平臺和需要結轉至歷史庫的，比如main、item和extend表等；

以下的統計表格是在同步大數據平臺后集中刪除和結轉的空間釋放情況，在1個月內對數據量在1億以上并且占用空間在100G以上的7張大表進行了刪除和結轉后刪除，使數據在保留365天的業務承諾時間范圍內，降低了470G（10%）的磁盤空間占用；

PS：紅色數字部分為負值，也就是磁盤的釋放空間。

4.2、攔截無參數查詢

運單主檔查詢偶發會有無任何參數的查詢，引發嚴重慢SQL，造成數據庫磁盤繁忙度嚴重飚高，極大地影響了其他業務操作，而由于入口眾多和交叉調用，如果在入口做參數校驗工作量及風險都比較大，所以采用MyBatis的插件機制在dao層做攔截，直接拒絕掉無參數的查詢，上線后就再沒有出現過因無參查詢而出現慢SQL而導致的磁盤繁忙情況；

mybatis-config.xml里的plugin配置：

ParameterInterceptor關鍵代碼如下：

源代碼如下：

import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

import java.lang.reflect.InvocationTargetException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * MyBatis攔截器，用于攔截SQL查詢無入參的場景，避免全表查詢
 *
 */
@Intercepts({
        @Signature(type = Executor.class, method = "query",
                args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public class ParameterInterceptor implements Interceptor {

    private final Map mappedStatementIdMap = new HashMap();

    @Override
    public Object intercept(Invocation invocation) throws InvocationTargetException, IllegalAccessException {
        Object[] queryArgs = invocation.getArgs();

        MappedStatement mappedStatement = null;
        if (queryArgs[0] instanceof MappedStatement) {
            mappedStatement = (MappedStatement) queryArgs[0];
        }

        if (null != mappedStatement && mappedStatementIdMap.containsKey(mappedStatement.getId())) {
            // 獲取SQL
            BoundSql boundSql = mappedStatement.getBoundSql(queryArgs[1]);

            List parameterMappingList = boundSql.getParameterMappings();

            if (parameterMappingList.isEmpty()) {
                Profiler.businessAlarm(mappedStatement.getId(), "查詢參數為空");
                throw new BadArgumentException("查詢參數為空，請確認入參是否有值");
            }

        }

        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
        for (Object key : properties.keySet()) {
            mappedStatementIdMap.put(key, 1);
        }
    }
}

4.3、查詢切從庫

主庫QPS高峰期達30k/s，長期處于高位運行，需要梳理出TOP10的查接口來切從庫查詢，而應用中接口眾多，無法逐個接口查各接口的調用量，可以利用JSF的filter功能結合UMP業務監控來統計provider的調用次數，再通過Python程序獲取統計數據生產統計報表。

JSF的配置文件新增filter

JsfInvokeFilter的代碼：

import com.jd.jsf.gd.filter.AbstractFilter;
import com.jd.jsf.gd.msg.RequestMessage;
import com.jd.jsf.gd.msg.ResponseMessage;
import com.jd.jsf.gd.util.RpcContext;
import com.jd.ump.profiler.proxy.Profiler;
import org.springframework.stereotype.Component;
import java.util.HashMap;
import java.util.Map;
/**
 * JSF filter
 * JSF服務的調用次數統計
 */
@Component
public class JsfInvokeFilter extends AbstractFilter {
    /**
     * 按API接口統計方法調用量 - 業務監控KEY
     */
    private static final String API_PROVIDER_METHOD_COUNT_KEY = "api.jsf.provider.method.count.key";
    private static final String API_CONSUMER_METHOD_COUNT_KEY = "api.jsf.consumer.method.count.key";
    @Override
    public ResponseMessage invoke(RequestMessage requestMessage) {
        String key;
        if (RpcContext.getContext().isProviderSide()) {
            key = API_PROVIDER_METHOD_COUNT_KEY;
        } else {
            key = API_CONSUMER_METHOD_COUNT_KEY;
        }
        String method = requestMessage.getClassName() + "." + requestMessage.getMethodName();
        Map tags = new HashMap(2);
        tags.put("bMark", method);
        tags.put("bCount", "1");
        Profiler.sourceDataByStr(key, tags);
        return getNext().invoke(requestMessage);
    }
}

業務監控點列表

明細項

Python腳本

import os
import openpyxl
import json
import requests
from cookies import Cookie
import time

headers = {
    'Cookie': Cookie,
    'Content-Type': 'application/json',
    'token': '******',
    'erp': '******'
}


def get_jsf(start_time, end_time):
    url = 'http://xxx.taishan.jd.com/api/xxx/xxx/xxx/'
    body = {}
    params = {'startTime': start_time,
              'endTime': end_time,
              'endPointKey': 'api.jsf.provider.method.count.key',
              'quickTime': int((end_time - start_time) / 1000),
              'markFlag': 'true',
              'markLimit': 500}

    res = requests.post(url=url, data=json.dumps(body), params=params, headers=headers)

    print('url: ', res.request.url)  # 查看發送的url
    # print('response: ', res.text)  # 返回請求結果

    res_json = json.loads(res.text)

    title = ['序號', 'jsf key', '次數', '占比%', '峰值', '次/秒', '峰值時間']
    i = 0

    keys = {}
    marks = res_json['response_data']['marks']
    for mark in marks:
        keys.setdefault(mark, [0, 0, 0, ''])

    data = []
    records = res_json['response_data']['monitorData']
    print(len(records))
    for key, value in records.items():
        count = 0
        max_val = 0
        max_time = ''
        for val in value:
            v = val['value']
            count += v
            if v > max_val:
                max_val = v
                max_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(int(val['dateTime'] / 1000)))
        keys[key] = [count, max_val, int(max_val / 1200), max_time]

    key_list = sorted(keys.items(), key=lambda x: x[1], reverse=True)
    # print(key_list)

    all_count = key_list[0][1][0]

    for key in key_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]]
        data.append(values)
        i += 1
    #
    # # print(data)
    #
    path = r"/Users/xxx/Documents/治理/QPS治理/"
    os.chdir(path)  # 修改工作路徑
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    sheet.title = 'JSF接口調用次數統計'
    sheet.append(title)
    for record in data:
        sheet.append(record)
    workbook.save('JSF接口調用次數統計-' + str(start_time / 1000) + '-' + str(end_time / 1000) + '.xlsx')


def change_time(dt):
    # 轉換成時間數組
    time_array = time.strptime(dt, "%Y-%m-%d %H:%M:%S")
    # 轉換成時間戳
    timestamp = time.mktime(time_array)
    return int(timestamp * 1000)


if __name__ == '__main__':
    start_time = '2024-03-06 12:20:00'
    end_time = '2024-03-07 12:20:00'

    get_jsf(change_time(start_time), change_time(end_time))

Cookie的代碼如下：

Cookie = '*****'

分析Top10接口的切從庫方案：

通過優化讀操作切換至從庫查詢，降低了主庫30%的QPS流量，白天峰值從25k降低到17.5k；

治理前QPS（峰值25k）

治理后QPS（峰值17.5k）

4.4、慢SQL治理

通過對慢SQL設定有針對性的治理，成功地徹底消除10s以上的慢SQL；5s以上的，消除80%；1s以上的消除60%。

關于慢SQL的治理不過多介紹，采用的都是通用分析和治理方法，有很多的文章都有介紹。需要注意的是在治理過程中要做好灰度，完全驗證后再全量上線運行。

五、寫在最后

可能有同學會想到分庫分表，一個是在規劃中提前部署分庫分表，一個是現在使用分庫分表技術進行治理；關于前一個問題由于時間久遠咱們不做過多討論，關于未使用分庫分表進行治理的原因是業務規劃的問題，目前此應用業務較為穩定，如采用分庫分表治理動作比較大風險較高，ROI不高，故以上治理方案以穩定為主降低風險為輔。

還有一個治理方案是遷云，利用云計算的彈性及快速恢復等特性降低來運行風險，因為業務的不可中斷性，此方案必須是在線遷移，涉及雙數據庫從雙寫到雙讀，再到單讀，最后單寫，還有數據一致性檢查和同步等，成本較高。同時云數據庫未能有如此大的磁盤容量和CPU核數，所以此方案需要結合分庫分表方案同時進行，更增加了成本和風險，但此方案目前是在計劃中的，如業務有較大幅度增長，以上治理也已無法滿足時，將采用遷云加分庫分表，且分庫和分表是分期進行推進。

六、探討

大家在日常及大促中有其他好的治理方案的話，歡迎發在評論區一起探討。

審核編輯 黃宇