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'];
const ledgerDepartKey = ['tender_id', 'tid', 'tender_id', 'tid', 'tid', 'tid'];
const ledgerPrimaryKey = ['uuid', 'uuid', 'uuid', 'id', 'id', 'id'];
const posTables = ['zh_pos', 'zh_pos_extra', 'zh_revise_pos', 'zh_stage_pos', 'zh_stage_pos_final'];
const posDepartKey = ['tid', 'tid', 'tid', 'tid', 'tid', 'tid'];
const posPrimaryKey = ['uuid', 'uuid', 'uuid', 'id', 'id', 'id'];
const orgLedgerDepart = 10, orgPosDepart = 20;
const tableDepart = 100;

const sqlArr = [];
const getDepartSql = function (table, key, primaryKey, 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} LIKE ${table}_bak_${orgDepartIndex};`);
        sqlArr.push(`INSERT INTO ${table}_${i} SELECT * FROM ${table}_bak_${orgDepartIndex} WHERE ${key} % 100 = ${i};`);
    }
    sqlArr.push('');
};
const getArrDepartSql = function(tableArr, keyArr, primaryKeyArr, orgDepart, newDepart) {
    for (let i = 0, iLen = tableArr.length; i < iLen; i++) {
        getDepartSql(tableArr[i], keyArr[i], primaryKeyArr[i], orgDepart, newDepart);
    }
};
getArrDepartSql(ledgerTables, ledgerDepartKey, ledgerPrimaryKey, orgLedgerDepart, tableDepart);
getArrDepartSql(posTables, posDepartKey, posPrimaryKey, orgPosDepart, tableDepart);

BaseUtil.saveBufferFile(sqlArr.join('\n'), BaseUtil.getFileName('depart_table_qa.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_pos: 480s(3122249条)
// zh_pos_extra: 50s(77条)
// zh_revise_pos: 350s(1173963条)
// zh_stage_pos: 110s(380576条)
// zh_stage_pos_final: 130s(658929条)

// uat执行时间(分表100)
// zh_ledger: 100s(600219条)
// zh_ledger_extra: 10s(10386条)
// zh_revise_bills: 20s(51352条)
// zh_stage_bills: 10s(33862条)
// zh_stage_bills_final: 20s(135090条)
// zh_pos: 180s(2088342条)
// zh_pos_extra: 10s(102845条)
// zh_revise_pos: 30s(162054条)
// zh_stage_pos: 30s(148945条)
// zh_stage_pos_final: 130s(658929条)


// uat执行时间(分表100)
// zh_ledger: 280s(2637137条)
// zh_ledger_extra: 20s(33004条)
// zh_revise_bills: 160s(1470789条)
// zh_stage_bills: 100s(2017603条)
// zh_stage_bills_final: 160s(7077632条)
// zh_pos: 600s(2088342条)
// zh_pos_extra: 20s(106698条)
// zh_revise_pos: 300s(4270632条)
// zh_stage_pos: 150s(4794451条)
// zh_stage_pos_final: 800s(23842851条)