WMS_MAIN/app/model/order_items_factory.js
liufumingren 4f8f453c36 perf(order): 订单分表补报表时间窗与归档时间索引
1. 发货单分表(current/dispatch/outsourced)补 (create_at,status,delete_at) 索引:工作台热销商品排行等报表固定按 create_at 区间 + status/delete_at 过滤订单再关联明细汇总,缺索引时优化器只能全扫明细表并逐行回表核对窗口,生产 wms_order_delivery_1000028_dispatch(140 万行/4.4GB)实测单次 38.9s。历史表按 archive_time 检索,沿用已有 idx_{warehouseId}_history_archive_time,不补建该索引。

2. 订单明细历史分表补 archive_time 索引:history 分表独有归档时间列,原先只有 created_at 索引,按归档时间检索会全表扫描。

3. 同步更新分表工厂索引用例:改为按集合断言(前 3 位继承 / create_at 同名继承 / 不继承 current 版列表索引 / history 仅 archive_time),修正此前因 logistics_code 与 is_b2b 列表索引新增而过期的期望值。

索引由模型定义在分表首次访问时懒创建(不改动存量表结构),按低峰发布。

Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
2026-11-17 01:45:11 +08:00

701 lines
23 KiB
JavaScript
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

// order_items_factory.js
'use strict';
const {
ensureShardWarehouseId,
clearShardWarehouseValidationCache,
} = require('../lib/warehouse_shard_guard');
const {
isColumnDefinitionDifferent,
buildColumnLine,
AUTO_INCREMENT_SAFE_MARGIN,
} = require('./order_delivery_table_schema');
/**
* 转义 SQL 标识符。
* @param {string} identifier 标识符。
* @return {string} 转义后的标识符。
*/
function quoteIdentifier(identifier) {
return `\`${String(identifier).replace(/`/g, '``')}\``;
}
/**
* 克隆模型属性定义。
* @param {Object} attributes 属性定义。
* @return {Object} 克隆后的属性定义。
*/
function cloneAttributes(attributes) {
return Object.entries(attributes || {}).reduce((result, [ key, value ]) => {
result[key] = { ...value };
return result;
}, {});
}
/**
* 规范化索引定义。
* @param {Object} index 索引定义。
* @return {Object} 规范化后的索引定义。
*/
function normalizeIndexDefinition(index) {
let unique = false;
if (index && Object.prototype.hasOwnProperty.call(index, 'unique')) {
unique = Boolean(index.unique);
} else if (index && Object.prototype.hasOwnProperty.call(index, 'Unique')) {
unique = Boolean(index.Unique);
} else if (index && Object.prototype.hasOwnProperty.call(index, 'nonUnique')) {
unique = Number(index.nonUnique) === 0;
}
return {
name: index && (index.name || index.Key_name || index.key_name || index.index_name) || '',
unique,
fields: Array.isArray(index && index.fields)
? index.fields.map(field => {
if (typeof field === 'string') {
return {
attribute: field,
};
}
return {
attribute: field.attribute || field.name || field.field || field.columnName || '',
length: field.length,
};
})
: [],
};
}
/**
* 判断两个索引定义是否存在结构差异。
* @param {Object} expectedIndex 预期索引定义。
* @param {Object} actualIndex 实际索引定义。
* @return {boolean} 是否存在结构差异。
*/
function isIndexDefinitionDifferent(expectedIndex, actualIndex) {
const normalizedExpected = normalizeIndexDefinition(expectedIndex);
const normalizedActual = normalizeIndexDefinition(actualIndex);
if (!normalizedActual.name) {
return true;
}
if (normalizedExpected.unique !== normalizedActual.unique) {
return true;
}
if (normalizedExpected.fields.length !== normalizedActual.fields.length) {
return true;
}
for (let index = 0; index < normalizedExpected.fields.length; index += 1) {
const expectedField = normalizedExpected.fields[index];
const actualField = normalizedActual.fields[index];
if (!expectedField.attribute || !actualField.attribute) {
return true;
}
if (expectedField.attribute !== actualField.attribute) {
return true;
}
if (Object.prototype.hasOwnProperty.call(expectedField, 'length') && expectedField.length !== actualField.length) {
return true;
}
}
return false;
}
module.exports = app => {
const { Sequelize } = app;
class OrderItemsFactory {
constructor() {
this.modelCache = new Map();
this.app = app;
this.creatingTables = new Map(); // 防止并发创建表
}
/**
* 获取当前表模型
* @param {number} warehouseId 仓库ID
* @return {Promise<Object>} 当前表模型
*/
async getCurrentModel(warehouseId) {
return await this.getOrCreateModel(warehouseId, 'current');
}
/**
* 获取历史表模型
* @param {number} warehouseId 仓库ID
* @return {Promise<Object>} 历史表模型
*/
async getHistoryModel(warehouseId) {
return await this.getOrCreateModel(warehouseId, 'history');
}
/**
* 获取配送表模型。
* @param {number} warehouseId 仓库ID
* @return {Promise<Object>} 配送表模型
*/
async getDispatchModel(warehouseId) {
return await this.getOrCreateModel(warehouseId, 'dispatch');
}
/**
* 获取或创建模型
* @param {number} warehouseId 仓库ID
* @param {string} type 模型类型
* @return {Promise<Object>} 动态模型
*/
async getOrCreateModel(warehouseId, type = 'current') {
const warehouseIdStr = await ensureShardWarehouseId(this.app, warehouseId);
const cacheKey = `${warehouseIdStr}_${type}`;
// 检查缓存
if (this.modelCache.has(cacheKey)) {
return this.modelCache.get(cacheKey);
}
const tableName = this.generateTableName(warehouseIdStr, type);
try {
// 防止并发创建表
if (this.creatingTables.has(cacheKey)) {
await this.creatingTables.get(cacheKey);
return this.modelCache.get(cacheKey);
}
const createPromise = this.createModelWithRetry(warehouseIdStr, type, tableName, cacheKey);
this.creatingTables.set(cacheKey, createPromise);
const dynamicModel = await createPromise;
this.creatingTables.delete(cacheKey);
return dynamicModel;
} catch (error) {
this.creatingTables.delete(cacheKey);
this.app.logger.error(`获取订单商品模型失败 ${tableName}:`, error);
throw error;
}
}
/**
* 带重试的模型创建
* @param {number} warehouseId 仓库ID
* @param {string} type 模型类型
* @param {string} tableName 表名
* @param {string} cacheKey 缓存键
* @param {number} retryCount 重试次数
* @return {Promise<Object>} 动态模型
*/
async createModelWithRetry(warehouseId, type, tableName, cacheKey, retryCount = 3) {
for (let i = 0; i < retryCount; i++) {
try {
const DynamicModel = this.buildDynamicModel(warehouseId, type, tableName);
await this.ensureManagedTableSchema(tableName, type, warehouseId, DynamicModel);
// 复制关联关系
this.copyAssociations(this.app.model.OrderItems, DynamicModel);
// 缓存模型
this.modelCache.set(cacheKey, DynamicModel);
this.app.logger.info(`创建订单商品动态模型成功: ${tableName}`);
return DynamicModel;
} catch (error) {
if (i === retryCount - 1) {
throw error;
}
this.app.logger.warn(`创建模型失败,第${i + 1}次重试:`, error.message);
await this.sleep(500); // 等待500ms后重试
}
}
}
/**
* 复制关联关系
* @param {Object} sourceModel 源模型
* @param {Object} targetModel 目标模型
*/
copyAssociations(sourceModel, targetModel) {
if (sourceModel.associations) {
Object.keys(sourceModel.associations).forEach(key => {
const association = sourceModel.associations[key];
const options = { ...association.options };
// 避免动态表的外键约束问题
options.constraints = false;
if (association.associationType === 'BelongsTo') {
targetModel.belongsTo(
association.target,
{
...options,
foreignKey: association.foreignKey,
as: association.as,
}
);
}
});
}
}
/**
* 构建订单商品动态模型。
* @param {number|string} warehouseId 仓库ID。
* @param {string} type 模型类型。
* @param {string} tableName 表名。
* @return {Object} 动态模型。
*/
buildDynamicModel(warehouseId, type, tableName) {
const attributes = this.getTableAttributes(type);
const modelComment = type === 'history'
? `仓库${warehouseId}订单商品历史表`
: type === 'dispatch'
? `仓库${warehouseId}订单商品配送表`
: `仓库${warehouseId}订单商品常用表`;
return this.app.model.define(tableName, attributes, {
tableName,
timestamps: true,
underscored: true,
paranoid: false,
indexes: this.generateIndexes(warehouseId, type),
comment: modelComment,
});
}
/**
* 获取订单商品模型属性定义。
* @param {string} type 模型类型。
* @return {Object} 属性定义。
*/
getTableAttributes(type) {
const mainModel = this.app.model.OrderItems;
const attributes = cloneAttributes(mainModel && mainModel.rawAttributes);
// 模型 timestamps:true 会向 rawAttributes 注入 createdAt/updatedAt
// (与显式定义的 created_at/updated_at 指向同一列),结构对齐时会被
// 误判为缺失列而 ADD COLUMN,导致重复列与无默认值报错,剔除这两个注入属性。
delete attributes.createdAt;
delete attributes.updatedAt;
// 保留 id 字段的 autoIncrement,与 order_delivery_factory 行为一致,
// 确保分表 wms_order_items_{warehouseId} 的 bulkCreate 能正常自增生成 id
if (type === 'history' && !attributes.archive_time) {
attributes.archive_time = {
type: this.app.Sequelize.DATE,
allowNull: true,
defaultValue: this.app.Sequelize.literal('CURRENT_TIMESTAMP'),
comment: '归档时间',
};
}
return attributes;
}
/**
* 生成索引配置
* @param {number} warehouseId 仓库ID
* @param {string} [type] 模型类型(current/dispatch/history),history 额外补归档时间索引
*/
generateIndexes(warehouseId, type = null) {
const indexes = [
{
name: `idx_${warehouseId}_order_id`,
fields: [ 'order_id' ],
},
{
name: `idx_${warehouseId}_item_id`,
fields: [ 'item_id' ],
},
{
name: `idx_${warehouseId}_item_code`,
fields: [ 'item_code' ],
},
{
name: `idx_${warehouseId}_source_order_code`,
fields: [ 'source_order_code' ],
},
{
name: `idx_${warehouseId}_stock_id`,
fields: [ 'stock_id' ],
},
{
name: `idx_${warehouseId}_owner_code`,
fields: [ 'owner_code' ],
},
{
name: `idx_${warehouseId}_order_item`,
fields: [ 'order_id', 'item_id' ],
},
{
name: `idx_${warehouseId}_created_at`,
fields: [ 'created_at' ],
},
];
// 历史表独有 archive_time(归档时间)列:归档库按归档时间检索,
// 缺索引时只能全表扫描,故为 history 分表补建该索引(其余分表无此列)。
if (type === 'history') {
indexes.push({
name: `idx_${warehouseId}_history_archive_time`,
fields: [ 'archive_time' ],
});
}
return indexes;
}
/**
* 检查表是否存在
* @param {string} tableName 表名
*/
async checkTableExists(tableName) {
try {
const [ results ] = await this.app.model.query(
`SELECT 1 FROM information_schema.tables
WHERE table_schema = DATABASE()
AND table_name = :tableName`,
{
replacements: { tableName },
type: Sequelize.QueryTypes.SELECT,
}
);
return Array.isArray(results)
? results.length > 0
: Boolean(results);
} catch (error) {
this.app.logger.error('检查表是否存在失败:', error);
return false;
}
}
/**
* 判断目标表是否需要进行结构同步。
* @param {string} tableName 表名。
* @param {string} type 模型类型。
* @param {number|string} warehouseId 仓库ID。
* @param {boolean|null} [tableExists=null] 是否已确认表存在。
* @return {Promise<boolean>} 是否需要同步。
*/
async needsTableSchemaSync(tableName, type, warehouseId, tableExists = null) {
const queryInterface = this.app.model.getQueryInterface();
const managedTableExists = typeof tableExists === 'boolean'
? tableExists
: await this.checkTableExists(tableName);
if (!managedTableExists) {
return true;
}
const [ tables, indexes ] = await Promise.all([
queryInterface.describeTable(tableName),
typeof queryInterface.showIndex === 'function'
? queryInterface.showIndex(tableName)
: Promise.resolve([]),
]);
const expectedAttributes = this.getTableAttributes(type);
const expectedAttributeNames = Object.keys(expectedAttributes);
const actualAttributeNames = Object.keys(tables || {});
if (expectedAttributeNames.length !== actualAttributeNames.length) {
return true;
}
const actualColumnNames = new Set(actualAttributeNames);
for (const columnName of expectedAttributeNames) {
if (!actualColumnNames.has(columnName)) {
return true;
}
}
for (const columnName of expectedAttributeNames) {
if (isColumnDefinitionDifferent(columnName, expectedAttributes[columnName], tables[columnName])) {
return true;
}
}
const expectedIndexes = this.generateIndexes(warehouseId, type);
const actualIndexes = Array.isArray(indexes) ? indexes : [];
for (const expectedIndex of expectedIndexes) {
const actualIndex = actualIndexes.find(index => (index.Key_name || index.name) === expectedIndex.name);
if (!actualIndex) {
return true;
}
if (isIndexDefinitionDifferent(expectedIndex, actualIndex)) {
return true;
}
}
return false;
}
/**
* 读取表的当前 AUTO_INCREMENT 水位。
* @param {string} tableName 表名。
* @return {Promise<number|null>} 自增水位,非自增表返回 null。
*/
async getTableAutoIncrement(tableName) {
const [ rows ] = await this.app.model.query(
'SELECT AUTO_INCREMENT AS autoIncrement FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?',
{ replacements: [ tableName ] }
);
const rawValue = rows && rows[0] && rows[0].autoIncrement;
const value = Number(rawValue);
return Number.isFinite(value) ? value : null;
}
/**
* 核对并修复表自增水位,确保 AUTO_INCREMENT 大于同仓库共享 id 序列
* 的全部分表(current/dispatch/history)的 MAX(id)。
*
* 明细迁移到配送明细表时同样显式复制源 id,因此 current 明细表的
* 自增序列必须全局单调,水位基准取所有分表的最大 MAX(id)。
*
* @param {string} tableName 表名。
* @return {Promise<void>}
*/
async ensureAutoIncrementWatermark(tableName) {
const sequelize = this.app.model;
const matched = /^wms_order_items_(\d+)(?:_(dispatch|history))?$/.exec(tableName);
const relatedTables = matched
? [
tableName,
`wms_order_items_${matched[1]}`,
`wms_order_items_${matched[1]}_dispatch`,
`wms_order_items_${matched[1]}_history`,
]
: [ tableName ];
let existingTableSet = null;
try {
existingTableSet = new Set(await sequelize.getQueryInterface().showAllTables());
} catch (error) {
existingTableSet = null;
}
let baseline = 0;
for (const relatedTable of new Set(relatedTables)) {
const exists = existingTableSet
? existingTableSet.has(relatedTable)
: await this.checkTableExists(relatedTable);
if (!exists) {
continue;
}
const [ rows ] = await sequelize.query(
`SELECT COALESCE(MAX(id), 0) AS maxId FROM \`${relatedTable}\``
);
const maxId = Number(rows && rows[0] && rows[0].maxId || 0);
if (Number.isFinite(maxId) && maxId > baseline) {
baseline = maxId;
}
}
if (!Number.isFinite(baseline) || baseline <= 0) {
return;
}
const autoIncrement = await this.getTableAutoIncrement(tableName);
if (autoIncrement === null || autoIncrement > baseline) {
return;
}
// 修复目标加安全余量:防止读取 MAX(id) 与 SET 之间的并发插入抢先占号
const safeTarget = baseline + AUTO_INCREMENT_SAFE_MARGIN;
await sequelize.query(`ALTER TABLE \`${tableName}\` AUTO_INCREMENT = ${safeTarget}`);
this.app.logger.warn(
`订单商品工厂表 ${tableName} 自增水位低于共享 id 序列最大 id(auto=${autoIncrement}, max=${baseline}),已修复为 ${safeTarget}`
);
}
/**
* 将现有表的列结构与期望模型对齐,所有变更合并为单条 ALTER TABLE 语句。
*
* 自增保护:对自增主键 id 列不做 CHANGE——对 AUTO_INCREMENT 列重新声明
* 列定义可能触发表重建(ALGORITHM=COPY),重建后 AUTO_INCREMENT 被重置为
* MAX(id)+1,与 dispatch/history 已复制的 id 重叠,导致后续迁移主键冲突。
* 索引同步仅补齐缺失索引,不重建、不删除已有索引。
*
* @param {string} tableName 表名。
* @param {string} type 模型类型。
* @param {number|string} warehouseId 仓库ID。
* @return {Promise<void>}
*/
async alterManagedTableSchema(tableName, type, warehouseId) {
const sequelize = this.app.model;
const queryInterface = sequelize.getQueryInterface();
const actualColumns = await queryInterface.describeTable(tableName);
const expectedAttributes = this.getTableAttributes(type);
const quotedTableName = quoteIdentifier(tableName);
const actualColumnNames = new Set(Object.keys(actualColumns || {}));
const expectedColumnNames = Object.keys(expectedAttributes);
const clauses = [];
// ADD 新增列 / CHANGE 差异列;id 主键列永不参与 CHANGE,避免重建表重置自增水位
for (const columnName of expectedColumnNames) {
const expectedAttribute = expectedAttributes[columnName];
if (columnName === 'id') {
continue;
}
const columnDef = buildColumnLine(columnName, expectedAttribute);
if (!actualColumnNames.has(columnName)) {
clauses.push(`ADD COLUMN ${columnDef}`);
} else if (isColumnDefinitionDifferent(columnName, expectedAttribute, actualColumns[columnName])) {
clauses.push(`CHANGE ${quoteIdentifier(columnName)} ${columnDef}`);
}
}
// DROP 多余列(现有表有但期望模型没有的列;id 必在期望模型中,不会被 DROP)
const expectedColumnSet = new Set(expectedColumnNames);
for (const columnName of actualColumnNames) {
if (!expectedColumnSet.has(columnName)) {
clauses.push(`DROP COLUMN ${quoteIdentifier(columnName)}`);
}
}
const previousAutoIncrement = await this.getTableAutoIncrement(tableName);
if (clauses.length > 0) {
await sequelize.query(`ALTER TABLE ${quotedTableName} ${clauses.join(', ')}`);
}
// 若 ALTER 触发表重建并把自增水位重置到历史低值,恢复原水位
const currentAutoIncrement = await this.getTableAutoIncrement(tableName);
if (previousAutoIncrement !== null
&& currentAutoIncrement !== null
&& currentAutoIncrement < previousAutoIncrement) {
await sequelize.query(`ALTER TABLE ${quotedTableName} AUTO_INCREMENT = ${previousAutoIncrement}`);
this.app.logger.warn(
`订单商品工厂表 ${tableName} 自增水位被 ALTER 重置(${previousAutoIncrement} → ${currentAutoIncrement}),已恢复为 ${previousAutoIncrement}`
);
}
// 仅补齐缺失索引,不重建、不删除已有索引
const existingIndexNames = new Set((await queryInterface.showIndex(tableName))
.map(index => index.Key_name || index.name || '')
.filter(Boolean));
for (const indexDef of this.generateIndexes(warehouseId, type)) {
if (existingIndexNames.has(indexDef.name)) {
continue;
}
await queryInterface.addIndex(tableName, indexDef.fields, {
name: indexDef.name,
unique: Boolean(indexDef.unique),
});
}
}
/**
* 确保目标表与模型结构一致。
* @param {string} tableName 表名。
* @param {string} type 模型类型。
* @param {number|string} warehouseId 仓库ID。
* @param {Object} [DynamicModel] 已构建的动态模型。
* @return {Promise<boolean>} 是否执行了同步。
*/
async ensureManagedTableSchema(tableName, type, warehouseId, DynamicModel = null) {
const dynamicModel = DynamicModel || this.buildDynamicModel(warehouseId, type, tableName);
const tableExists = await this.checkTableExists(tableName);
const needsSync = tableExists
? await this.needsTableSchemaSync(tableName, type, warehouseId, tableExists)
: true;
if (!tableExists) {
await dynamicModel.sync({ force: false });
return true;
}
if (needsSync) {
// 结构同步不再使用 sync({ alter: true }):历史事故表明它会重建表并重置
// 自增水位(与 dispatch/history 已复制 id 重叠,后续迁移主键冲突)。
// 改为手工差异 ALTER + 补齐索引,并跳过 id 列 CHANGE。
await this.alterManagedTableSchema(tableName, type, warehouseId);
}
// 无论是否同步结构,都核对自增水位(表重建/迁移/手工修复都可能破坏水位)
await this.ensureAutoIncrementWatermark(tableName);
return needsSync;
}
/**
* 创建物理表。
* @param {number} warehouseId 仓库ID
* @param {string} type 模型类型
*/
async createPhysicalTable(warehouseId, type = 'current') {
const tableName = this.generateTableName(warehouseId, type);
const dynamicModel = this.buildDynamicModel(warehouseId, type, tableName);
// 统一走差异检测 + 自增水位保护的同步路径,不再直接 sync({ alter: true })
await this.ensureManagedTableSchema(tableName, type, warehouseId, dynamicModel);
this.app.logger.info(`创建/同步订单商品物理表成功: ${tableName}`);
}
/**
* 生成表名
* @param {number} warehouseId 仓库ID
* @param {string} type 模型类型
*/
generateTableName(warehouseId, type) {
const suffix = type === 'history'
? '_history'
: type === 'dispatch'
? '_dispatch'
: '';
return `wms_order_items_${warehouseId}${suffix}`;
}
/**
* 清除缓存
* @param {number} warehouseId 仓库ID
* @param {string} type 模型类型
*/
clearCache(warehouseId, type) {
const cacheKey = `${warehouseId}_${type}`;
this.modelCache.delete(cacheKey);
this.creatingTables.delete(cacheKey);
clearShardWarehouseValidationCache(this.app);
}
/**
* 清除所有缓存
*/
clearAllCache() {
this.modelCache.clear();
this.creatingTables.clear();
clearShardWarehouseValidationCache(this.app);
}
/**
* 休眠函数
* @param {number} ms 毫秒数
*/
sleep(ms) {
return new Promise(resolve => setTimeout(resolve, ms));
}
}
return new OrderItemsFactory();
};