java Basic Tutorial column will show you how to conveniently count order income.

Introduction

Statistical order income is to do e-commerce This is a common problem for APPs of this type. The regular requirements generally include user benefitsdaily/monthly/annual reports. These report-type data pose considerable challenges to table design and program design. Conventionalaggregation queries The query time of the statement will gradually become longer as the income statement data becomes larger and larger. At this time, we need to think about how to design the income statement to query more efficiently? What kind of design can make statistical income simple?

Requirements

##Rendering

Specific requirements

• Income types are divided into: Self-purchased order income, shared order income, distribution income, activity income
• Statistics on the day's income, the current month's income
• is calculated based on the screening time Income during the time period.

Thinking

##Design ideas

The order table is definitely needed. When writing or modifying the order table, write and modify the income table simultaneously. Only self-purchase and sharing orders will be recorded in the order table, and distribution and event gift income will only be recorded in the order table. Write the income statement in the special business. Then use the day as the dimension to create a user

Daily income report . A single row record is written to the user's income for the day. Reduce Query the user's day/month/ The amount of data when calculating annual income. Taking a single user as an example, splitting users will only generate up to 31 pieces of data in a month. This is a controllable growth rate. If the income statement is used, because the data in the income statement The amount corresponds one-to-one with the number of orders placed by the user. If the amount of orders placed by the user is large, the table will be very large. In the early stage when the number of users begins to increase, this method can be used to avoid large data volume statistics. In the later period, if the number of users increases, the daily If the report data increases, you can consider dividing the table.

Visible problems

• The timing of synchronizing the daily income report is a problem because the original order operation is very complicated. It is complicated to write income and calculate and write daily income data simultaneously, and the code coupling is too high. Is there any way to generate daily income reports through the income statement heterogeneity?
• Although the income is It is written into the daily report, but to meet the requirements of the renderings, it may be necessary to query SQL statements multiple times. Is there a way to use as few aggregate SQL statements as possible without affecting the efficiency of the program?

Realization

Summarized the above problems. I started data collection. Finally, I used canal RocketMQAs a heterogeneous solution.

Technology stack

Let’s briefly introduce this Two technical frameworks:

• canal: The main purpose is to provide incremental data subscription and consumption based on MySQL database incremental log analysis
• RocketMQ: An open source distributed message The system, based on high-availability distributed cluster technology, provides low-latency, highly reliable message publishing and subscription services.

Note: I use aliyun's Family Bucket, MQ and mysql are both from Alibaba Cloud. If it is a self-built server, there may be differences. I will try my best to mark it later.

Proposal process

1. ## While writing or modifying the income statement, monitor the binlog log of the mysql income statement through canal.
2. canal detects the change, assembles the changed JSON message, and sends the predefined ones in RocketMQ TOPIC.
3. The program consumes this TOPIC and heterogeneous income daily report.

canal configuration part

Please refer to the official documentation for installation of canal After decompression, you will get a canal folder containing three directories

• bin: stores startup and restart scripts
• conf: stores core configuration files
• lib: Store core jar package

We need to focus on the conf/canal.properties core configuration file in the conf folder and the conf/example/instance.properties single monitoring node configuration file

conf/canal.properties

# tcp, kafka, RocketMQ,這里默認是tcp讀取模式,采用RocketMQ需要將其改變?yōu)镽ocketMQ模式
canal.serverMode = RocketMQ
# 如果是aliyun的RocketMQ需要配置以下兩個KEY,ak/sk
canal.aliyun.accessKey =xxxxxxx
canal.aliyun.secretKey =xxxxxxx
# 監(jiān)控的節(jié)點名稱.這個默認就是example如果有多節(jié)點可以逗號隔開,如下方的例子
canal.destinations = example,sign
# 如果是aliyun的RocketMQ需要修改canal.mq.accessChannel為cloud默認為local
canal.mq.accessChannel = cloud
#MQ的地址,需要注意這里是不帶http://,但是需要帶端口號
canal.mq.servers = 
#rocketmq實例id
canal.mq.namespace =

conf/example/instance. properties

#mysql地址
canal.instance.master.address=
#以下兩個參數(shù)需要在開啟數(shù)據(jù)庫binlog日志后得到,在數(shù)據(jù)庫查詢界面輸入查詢語句`show master status`,canal.instance.master.journal.name對應(yīng)File參數(shù),canal.instance.master.position對應(yīng)Position參數(shù)
canal.instance.master.journal.name=
canal.instance.master.position=
#數(shù)據(jù)庫的賬號密碼
canal.instance.dbUsername=
canal.instance.dbPassword=
#需要監(jiān)控變動的表
canal.instance.filter.regex=xxx.t_user_order,xxx.t_user_cash_out
#定義發(fā)送的mq生產(chǎn)組
canal.mq.producerGroup = 
#定義發(fā)送到mq的指定主題
canal.mq.topic=

Note: For the writing rule format of the monitoring table, refer to the writing rules of the monitoring table

Startup

cd /canal/bin
./start.sh

At this time, you will find that there is an additional log file in the canal directory. When you enter, you can see the canal main log file and the example node startup log.

canal日志中出現(xiàn)
 the canal server is running now ......
example日志中出現(xiàn)
 init table filter : ^tablename
 xxxxxxxxx , the next step is binlog dump

means that you have succeeded. A big step forward, canal monitoring is running normally.

RocketMQ部分

如果用的aliyun的RocketMQ,配置代碼部分直接可參考文檔 自建的RocketMQ也可參照簡單的消費例子監(jiān)控對應(yīng)的TOPIC即可 消費Canal發(fā)來的數(shù)據(jù),格式如下:

{
    "data":[
        {
            //單個修改后表數(shù)據(jù),如果同一時間有多個表變動會有多個該JSON對象        }
    ],
    "database":"監(jiān)控的表所在數(shù)據(jù)庫",
    "es":表變動時間,
    "id":canal生成的id,
    "isDdl":Boolean類型,表示是否DDL語句,
    "mysqlType":{
        表結(jié)構(gòu)
    },
    "old":如果是修改類型會填充修改前的值,
    "pkNames":[
        該表的主鍵,如"id"
    ],
    "sql":"執(zhí)行的SQL",
    "sqlType":{
        字段對應(yīng)的sqlType,一般使用mysqlType即可
    },
    "table":"監(jiān)控的表名",
    "ts":canal記錄發(fā)送時間,
    "type":"表的修改類型,入INSERT,UPDATE,DELETE"
}

MQ消費代碼主要用了反射,映射到對應(yīng)的表

//這里的body就是Canal發(fā)來的數(shù)據(jù)
public Action process(String body) {
        boolean result = Boolean.FALSE;
        JSONObject data = JSONObject.parseObject(body);
        log.info("數(shù)據(jù)庫操作日志記錄：data:{}",data.toString());
        Class c = null;
        try {
            //這里監(jiān)控了訂單和收益表分別做訂單統(tǒng)計和收益日報統(tǒng)計
            c = Class.forName(getClassName(data.getString("table")));
        } catch (ClassNotFoundException e) {
            log.error("error {}",e);
        }
        if (null != c) {
            JSONArray dataArray = data.getJSONArray("data");
            if (dataArray != null) {
                //把獲取到的data部分轉(zhuǎn)換為反射后的實體集合
                List list = dataArray.toJavaList(c);
                if (CollUtil.isNotEmpty(list)) {
                    //對修改和寫入操作分別進行邏輯操作
                    String type = data.getString("type");
                    if ("UPDATE".equals(type)) {
                        result = uppHistory(list);
                    } else if ("INSERT".equals(type)) {
                        result = saveHistory(list);
                    }
                }
            }
        }
        return result ? Action.CommitMessage : Action.ReconsumeLater;
    }
    
    /**
     * @description: 獲取反射ClassName
     * @author: chenyunxuan
     */
    private String getClassName(String tableName) {
        StringBuilder sb = new StringBuilder();
        //判斷是哪張表的數(shù)據(jù)
        if (tableName.equals("t_user_income_detail")) {
            sb.append("cn.mc.core.model.order");
        } else if (tableName.equals("t_user_cash_out")) {
            sb.append("cn.mc.sync.model");
        }
        String className = StrUtil.toCamelCase(tableName).substring(1);
        return sb.append(".").append(className).toString();
    }
    
    /**
     * @description: 寫入對應(yīng)類型的統(tǒng)計表
     * @author: chenyunxuan
     */
    private <T> Boolean saveHistory(List<T> orderList) {
        boolean result = Boolean.FALSE;
        Object dataType = orderList.get(0);
        //用instanceof判斷類型進入不同的邏輯處理代碼
        if (dataType instanceof TUserIncomeDetail) {
            result = userOrderHistoryService.saveIncomeDaily(orderList);
        } else if (dataType instanceof UserCashOut) {
            result = userCashOutHistoryService.delSaveHistoryList(orderList);
        }
        return result;
    }

saveIncomeDaily偽代碼

  public synchronized Boolean saveIncomeDaily(List orderList) {
    //循環(huán)收益明細記錄
    .......
    //通過創(chuàng)建時間和用戶id查詢收益日報表中是否有當日數(shù)據(jù)
    if(不存在當日數(shù)據(jù)){
        //創(chuàng)建當日的收益日報表記錄
        .....
    }
    //因為不存在當日記錄也會立即寫入當日的空數(shù)據(jù),所以下面的流程都是走更新流程
    //更新當日數(shù)據(jù)
    .......
    return Boolean.TRUE;
    }

注:代碼中應(yīng)該多打一些日志,方便產(chǎn)生異常收益數(shù)據(jù)后的校對

后記

至此一個基于canal+RocketMQ的收益日報統(tǒng)計異構(gòu)方案就完成了,下一篇會圍繞本文提到的第二個問題減少聚合SQL的產(chǎn)生展開.敬請關(guān)注.

相關(guān)免費學(xué)習(xí)推薦：java基礎(chǔ)教程

The above is the detailed content of Convenient statistical order income (1). For more information, please follow other related articles on the PHP Chinese website!