Calculation/db_script/depart_table.js

const BaseUtil = require('./baseUtils');

const ledgerTables = ['zh_ledger', 'zh_ledger_extra', 'zh_revise_bills', 'zh_stage_bills', 'zh_stage_bills_final', 'zh_settle_bills'];
const ledgerDepartKey = ['tender_id', 'tid', 'tender_id', 'tid', 'tid', 'tid'];
const posTables = ['zh_pos', 'zh_pos_extra', 'zh_revise_pos', 'zh_stage_pos', 'zh_stage_pos_final', 'zh_settle_pos'];
const posDepartKey = ['tid', 'tid', 'tid', 'tid', 'tid', 'tid'];
const orgLedgerDepart = 10, orgPosDepart = 20;
const tableDepart = 1000;

const sqlArr = [];
const getDepartSql = function (table, key, orgDepart, newDepart) {
    sqlArr.push(`-- ${table}`);
    sqlArr.push(`-- ${table} RENAME`);
    for (let i = 0; i < orgDepart; i++) {
        sqlArr.push(`RENAME TABLE ${table}_${i} TO ${table}_bak_${i};`);
    }
    sqlArr.push(`-- ${table} RE_DEPART`);
    for (let i = 0; i < newDepart; i++) {
        const orgDepartIndex = i % orgDepart;
        sqlArr.push(`Create Table ${table}_${i} SELECT * FROM ${table}_bak_${orgDepartIndex} WHERE ${key} % 100 = ${i};`);
    }
    sqlArr.push('');
};
const getArrDepartSql = function(tableArr, keyArr, orgDepart, newDepart) {
    for (let i = 0, iLen = tableArr.length; i < iLen; i++) {
        getDepartSql(tableArr[i], keyArr[i], orgDepart, newDepart);
    }
};
getArrDepartSql(ledgerTables, ledgerDepartKey, orgLedgerDepart, tableDepart);
getArrDepartSql(posTables, posDepartKey, orgPosDepart, tableDepart);

BaseUtil.saveBufferFile(sqlArr.join('\n'), BaseUtil.getFileName('depart_table.sql'));

Select Sum(amount) FROM (
    Select Count(*) As amount From zh_settle_bills_bak_0
    UNION ALL
    Select Count(*) As amount From zh_settle_bills_bak_1
    UNION ALL
    Select Count(*) As amount From zh_settle_bills_bak_2
    UNION ALL
    Select Count(*) As amount From zh_settle_bills_bak_3
    UNION ALL
    Select Count(*) As amount From zh_settle_bills_bak_4
    UNION ALL
    Select Count(*) As amount From zh_settle_bills_bak_5
    UNION ALL
    Select Count(*) As amount From zh_settle_bills_bak_6
    UNION ALL
    Select Count(*) As amount From zh_settle_bills_bak_7
    UNION ALL
    Select Count(*) As amount From zh_settle_bills_bak_8
    UNION ALL
    Select Count(*) As amount From zh_settle_bills_bak_9
) As temp;

// 数据量预估
// qa执行时间(分表100)
// zh_ledger: 320s(876461条)
// zh_ledger_extra: 80s(155730条)
// zh_revise_bills: 230s(386041条)
// zh_stage_bills: 90s(80435条)
// zh_stage_bills_final: 90s(108374条)
// zh_settle_bills: 40s(360条)
// zh_pos: 480s(3122249条)
// zh_pos_extra: 50s(77条)
// zh_revise_pos: 350s(1173963条)
// zh_stage_pos: 110s(380576条)
// zh_stage_pos_final: 130s(658929条)
// zh_settle_pos: 50s(266条)