MySql分库分表深度指南之从策略到落地

# sharding-proxy 配置示例（透明代理）
schemaname: order_db
datasources:
  ds_0: { url: jdbc:mysql://db0:3306/order_db0, ... }
  ds_1: { url: jdbc:mysql://db1:3306/order_db1, ... }
rules:
  - !sharding
    tables:
      orders:
        actualdatanodes: ds_${0..1}.orders_${0..15}
        tablestrategy:
          standard:
            shardingcolumn: order_id
            shardingalgorithmname: gene_hash
shardingalgorithms:
  gene_hash:
    type: class_based
    props:
      strategy: standard
      algorithmclassname: com.example.geneshardingalgorithm

<schema name="order_db" checksqlschema="true">
    <table name="orders" datanode="dn$0-15" rule="mod-long" />
</schema>
<datanode name="dn0" datahost="dh0" database="order_db0" />
<datanode name="dn1" datahost="dh0" database="order_db1" />
<datahost name="dh0" balance="1" writetype="0" dbtype="mysql">
    <writehost host="hostm1" url="db0:3306" user="root" password="xxx"/>
    <readhost host="hosts1" url="db1:3306" user="root" password="xxx"/>
</datahost>

-- 错误：status 只有 3 个值，导致 3 个分片成为热点
partition by hash(status) partitions 64; -- 只有 3 个分区有数据
-- 正确：user_id 哈希，数据均匀分布
partition by hash(user_id) partitions 64;

订单系统高频查询：
1. 用户查历史订单 → 必须带 user_id ✅
2. 商家查订单 → 必须带 merchant_id ✅
3. 客服按订单号查 → 必须带 order_no ✅
分片键选择：user_id（覆盖场景最多）

-- 错误：手机号可能变更，导致数据迁移
-- 正确：user_id 是主键，永不改变

// 64位id结构：符号位(1) + 时间戳(41) + 分片基因(12) + 序列号(10)
public class orderidgenerator {
    private static final int gene_bits = 12; // 12位基因支持4096个分片
    public static long generateid(long userid) {
        long timestamp = system.currenttimemillis() - 1288834974657l;
        long gene = userid & ((1 << gene_bits) - 1); // 提取user_id后12位作为基因
        long sequence = getnextsequence();
        return (timestamp << 22) | (gene << 10) | sequence;
    }
    // 从订单id反推分片位置
    public static int getshardkey(long orderid) {
        return (int) ((orderid >> 10) & 0xfff); // 提取中间12位基因
    }
}

public class ordershardingrouter {
    private static final int db_count = 8;          // 8个库
    private static final int table_count = 16;      // 每库16张表
    public static string route(long orderid) {
        int gene = orderidgenerator.getshardkey(orderid);
        int dbindex = gene % db_count;              // 基因决定库
        int tableindex = gene % table_count;        // 基因决定表
        return string.format("order_db_%d.orders_%d", dbindex, tableindex);
    }
}

// 使用 ketama 算法
public class consistenthashsharding {
    private final sortedmap<long, string> circle = new treemap<>();
    public void addserver(string server) {
        for (int i = 0; i < 160; i++) { // 160个虚拟节点
            circle.put(hash(server + "-" + i), server);
        }
    }
    public string getserver(long key) {
        if (circle.isempty()) return null;
        long hash = hash(key);
        sortedmap<long, string> tailmap = circle.tailmap(hash);
        hash = tailmap.isempty() ? circle.firstkey() : tailmap.firstkey();
        return circle.get(hash);
    }
}

位段分布：
0-0   : 符号位（1位，始终为0）
1-41  : 时间戳（41位，支持69年，从2020起算）
42-52 : 机器id（10位，支持1024个节点）
53-63 : 序列号（12位，每毫秒4096个id）

0-0   : 符号位（1位）
1-41  : 时间戳（41位）- 支持到 2089年
42-53 : 分片基因（12位）- 支持4096个分片
54-63 : 序列号（10位）- 每毫秒1024个id

public class genesnowflake {
    private final long twepoch = 1288834974657l; // 起始时间戳
    private final long genebits = 12l;           // 基因位数
    private final long sequencebits = 10l;       // 序列号位数
    private final long geneshift = sequencebits; // 基因左移10位
    private final long timestampshift = genebits + sequencebits; // 时间戳左移22位
    public synchronized long nextid(long userid) {
        long timestamp = system.currenttimemillis();
        long gene = userid & ((1 << genebits) - 1); // 提取基因
        long sequence = getsequenceinsamems(timestamp); // 毫秒内序列号
        return ((timestamp - twepoch) << timestampshift) |
               (gene << geneshift) |
               sequence;
    }
}

// 提取基因（分片键）
public static int extractgene(long orderid) {
    // 基因位于第10-21位：orderid >> 10 & 0xfff
    return (int) ((orderid >> 10) & 0xfff);
}
// 提取生成时间
public static date extracttime(long orderid) {
    long timestamp = (orderid >> 22) + twepoch;
    return new date(timestamp);
}
// 完整路由示例
public class orderservice {
    public order getorderbyid(long orderid) {
        int gene = extractgene(orderid);
        string dbtable = ordershardingrouter.routebygene(gene);
        return executequery("select * from " + dbtable + " where order_id = ?", orderid);
    }
}

{
  "order_index": {
    "mappings": {
      "properties": {
        "order_no": { "type": "keyword" },
        "shard_key": { "type": "integer" },  // 分片基因
        "user_id": { "type": "long" },
        "merchant_id": { "type": "long" }
      }
    }
  }
}

// 商家查询订单（先查es定位分片）
public list<order> getordersbymerchant(long merchantid) {
    // 1. es 中查询 shard_key
    searchresponse response = esclient.search(
        new searchrequest("order_index")
            .source(new searchsourcebuilder()
                .query(querybuilders.termquery("merchant_id", merchantid))
                .fetchfield("shard_key")
                .size(10000))
    );
    // 2. 按 shard_key 分组
    map<integer, list<long>> shardgroups = groupbyshard(response);
    // 3. 并发查询各分片
    return shardgroups.entryset().parallelstream()
        .map(entry -> queryshard(entry.getkey(), entry.getvalue()))
        .flatmap(list::stream)
        .collect(collectors.tolist());
}

-- 创建全局索引（自动同步到指定存储节点）
create sharding global index idx_merchant on orders(merchant_id)
    by sharding_algorithm(merchant_hash)
    with storage_unit(ds_0, ds_1);
-- 查询时自动路由
select * from orders where merchant_id = 10086;
-- shardingsphere 自动改写为：先查 gsi 表获取 order_id，再路由到主表

写操作（command）：
  应用服务 → 分片路由 → 写入分片库
读操作（query）：
  应用服务 → es/hbase → 聚合结果

public void createorder(order order) {
    try {
        // 1. 写新库（主库）
        ordernewdao.insert(order);
        // 2. 写旧库（备份）
        orderolddao.insert(order);
    } catch (exception e) {
        // 3. 新库失败必须回滚旧库
        if (isnewsuccess()) {
            ordernewdao.delete(order.getid());
        }
        throw e;
    }
}

// 动态分片路由（按用户id灰度）
public string routebyuserid(long userid) {
    if (userid <= 10000) { // 1%用户切新库
        return "order_new";
    } else {
        return "order_old";
    }
}

-- 复合分片键：(merchant_id + user_id) % 1024
-- 路由逻辑：将大商家数据按 user_id 二次打散
public int getshardkey(long merchantid, long userid) {
    if (isbigmerchant(merchantid)) {
        return (int) ((merchantid * 31 + userid) % 1024);
    } else {
        return (int) (merchantid % 1024);
    }
}

@transactional
public void createorder(order order) {
    // 1. 本地事务：写订单主库
    orderdao.insert(order);
    // 2. 发送事务消息（半消息）
    transactionsendresult result = rocketmqtemplate.sendmessageintransaction(
        "order_create_event",
        messagebuilder.withpayload(order.tojson()).build(),
        null
    );
    // 3. 消息确认后，下游消费加积分、扣库存
}
// 消费者异步处理
@rocketmqmessagelistener(topic = "order_create_event")
public void handleevent(orderevent event) {
    bonusservice.addpoints(event.getuserid());      // 异步加积分
    inventoryservice.deduct(event.getskuid());      // 异步扣库存
}

-- 方案1：业务折衷（禁用深分页，仅支持前100页）
-- 方案2：es 聚合查询（推荐）
get /order_index/_search
{
  "from": 100,
  "size": 10,
  "sort": [{"create_time": "desc"}]
}
-- 方案3：游标分页（记录上次查询的 order_id）
select * from orders 
where create_time < '2024-01-01' and order_id < #{lastorderid}
order by create_time desc limit 10;

// 基因分片是订单系统的最佳拍档
// 12位基因支持 4096 个分片 = 8库 × 16表 × 32冗余

-- 初始设计：8库 × 16表 = 128分片
-- 支持单分片 500万行 → 总容量 6.4亿行
-- 预留 2 年数据增长

// 小表（<1000万行）无需分片
// 大表关联查询：优先冗余字段，避免跨分片 join

-- 监控分片倾斜率
select db, table_name, count(*) as rows 
from information_schema.tables 
where table_schema like 'order_db_%' 
group by db, table_name 
having rows > avg(rows) * 1.5; -- 找出超平均分片50%的热点