一、适用场景

• 单表数据量超千万/过亿，查询、更新、删除缓慢
• 按地市、周、店铺、商品多维度聚合统计
• 多地市卷烟目录不同，需地市隔离
• 需要环比、同比、同档位/同业态/同面积对标
• 多语言/多库（Java、Python、MySQL、PG、Doris）统一路由
• 大批量增删改查，避免锁表、长事务、夯库

---

二、分表核心原则（直接背）

1. 查询维度 = 分表维度（按什么查，就按什么分）
2. 统计必须预计算，不实时扫明细
3. 字符串ID统一用 CRC32 + 取模，全平台结果一致
4. 三层结构：地市 + 时间 + 哈希，均衡又好查
5. 只在业务库保留近期数据，历史入Doris数仓

---

三、统一哈希算法（全平台通用）

目标

字符串 retailer_id 在所有系统计算出相同表序号

统一公式

哈希值 = CRC32(retailer_id)
表序号 = 哈希值 % 分表数量（推荐 32 / 64）

各平台实现

1）MySQL

MOD(CRC32(retailer_id), 64)

2）PostgreSQL（先建函数）

CREATE OR REPLACE FUNCTION crc32(text) RETURNS bigint AS $$
DECLARE
    crc bigint := 4294967295;
    bytes bytea;
    i integer;
    b integer;
    j integer;
BEGIN
    bytes := convert_to($1, 'UTF8');
    FOR i IN 0 .. octet_length(bytes)-1 LOOP
        b := get_byte(bytes, i);
        crc := crc # b;
        FOR j IN 0 ..7 LOOP
            IF (crc & 1) <> 0 THEN
                crc := (crc >>1) # 3988292384;
            ELSE
                crc := crc >>1;
            END IF;
        END LOOP;
    END LOOP;
    RETURN (crc # 4294967295) & 4294967295;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

MOD(crc32(retailer_id), 64)

3）Doris

MOD(CRC32(retailer_id), 64)

4）Java

public static int tableIndex(String retailerId, int cnt) {
    CRC32 crc32 = new CRC32();
    crc32.update(retailerId.getBytes(StandardCharsets.UTF_8));
    return (int) (crc32.getValue() % cnt);
}

5）Python

import zlib
def table_index(retailer_id, cnt):
    return zlib.crc32(retailer_id.encode()) % cnt

---

四、最终分表方案（直接落地）

1. 订单主表分表

按：地市 + 店铺哈希

order_main_{city_code}_{00-63}
例：order_main_410100_12

2. 订单详情表分表（核心）

按：地市 + 自然周 + 店铺哈希

order_item_{city_code}_{order_week}_{00-31}
例：order_item_410100_2026W16_07

为什么这么分？

• 地市：不同商品隔离，统计不跨地市
• 周：环比同比只查相邻周，速度极快
• 哈希：数据均匀，单表不大，不锁表

---

五、MySQL 建表示例（可直接执行）

订单详情分表模板

CREATE TABLE order_item_410100_2026W16_00 (
    id bigint primary key auto_increment,
    city_code varchar(10),
    order_week char(6),
    retailer_id varchar(32),
    order_id varchar(32),
    product_id varchar(32),
    qty int,
    amt decimal(12,2),
    create_time datetime,
    index idx_retailer_week (retailer_id, order_week),
    index idx_product (product_id)
);

---

六、Doris 建表示例（数仓历史明细）

CREATE TABLE order_item_doris (
    city_code     varchar(10),
    order_week    char(6),
    retailer_id   varchar(32),
    product_id    varchar(32),
    qty           int,
    amt           decimal(12,2)
)
ENGINE=OLAP
DUPLICATE KEY(city_code, order_week, retailer_id)
PARTITION BY RANGE(order_week) (
    FROM ("2025W01") TO ("2027W01") EVERY ("W1")
)
DISTRIBUTED BY HASH(city_code, retailer_id) BUCKETS 32;

---

七、指标层设计（环比同比必备）

1）商品周汇总表

retailer_week_stats (
    city_code,
    order_week,
    retailer_id,
    total_qty, total_amt, product_cnt
)

2）圈群单品周指标表（同档位/业态/面积对标）

group_product_week_stats (
    city_code,
    order_week,
    group_type  varchar(16),  -- 档位/业态/面积/区域/诚信小组
    group_code  varchar(32),
    product_id  varchar(32),
    avg_qty     decimal(10,2),
    avg_price   decimal(10,2),
    retail_cnt  int
)

---

八、大批量操作规范（防卡死）

1）大批量删除

• 禁止 delete from t where ... 删百万级
• 改用 分批删除（每次 1000~2000 行）
• 历史数据直接按周/地市归档后 DROP 分区

2）大批量更新

• 拆小事务
• 非实时用 MQ异步
• 关闭不必要索引

3）查询优化

• 禁止 select *、禁止深分页
• 高频指标放入 Redis
• 统计走 Doris，不打业务库

---

九、整套流程总结

1. 字符串ID统一用 CRC32(retailer_id) % 64
2. 订单详情按 地市 + 周 + 哈希 三层分表
3. MySQL 存近期数据，Doris 存全量历史
4. 每周跑批生成 户汇总 + 圈群单品指标
5. 前端查指标表，毫秒级出环比/同比/对标
6. 增删改查全平台路由结果一致，不乱表