MySQL分库分表后主键ID生成的八种方案

-- 分表1（t_order_0）配置
alter table t_order_0 auto_increment = 1; -- 起始值1，步长10

-- 分表2（t_order_1）配置
alter table t_order_1 auto_increment = 11; -- 起始值11，步长10

-- 分表3（t_order_2）配置
alter table t_order_2 auto_increment = 21; -- 起始值21，步长10

// 根据订单id计算分表索引
int gettableindex(long orderid) {
    return (int)(orderid % 10); // 假设分10个表
}

// 插入订单
void insertorder(order order) {
    int tableindex = gettableindex(order.getid());
    string tablename = "t_order_" + tableindex;
    
    // 动态拼接sql
    string sql = "insert into " + tablename + "(id, order_no) values (?, ?)";
    jdbctemplate.update(sql, order.getid(), order.getorderno());
}

-- 创建订单表（主键字段为uuid）
create table t_order (
    id char(36) primary key, -- uuid长度36位
    order_no varchar(50)
);

import java.util.uuid;

public class order {
    private string id = uuid.randomuuid().tostring(); // 生成uuid
    private string orderno;

    // getter & setter
}

// 压缩uuid为16字节
public byte[] compressuuid(string uuid) {
    return uuid.fromstring(uuid).tostring().getbytes(standardcharsets.utf_8);
}

public class snowflakeidgenerator {
    private final long twepoch = 1288834974657l; // 起始时间戳（2010-11-04）
    private final long workeridbits = 5l; // 机器id位数
    private final long datacenteridbits = 5l; // 数据中心id位数
    private final long sequencebits = 12l; // 序列号位数
    
    private final long maxworkerid = ~(-1l << workeridbits);
    private final long maxdatacenterid = ~(-1l << datacenteridbits);
    private final long sequencemask = ~(-1l << sequencebits);
    
    private long workerid;
    private long datacenterid;
    private long sequence = 0l;
    private long lasttimestamp = -1l;

    public snowflakeidgenerator(long workerid, long datacenterid) {
        if (workerid > maxworkerid || workerid < 0) {
            throw new illegalargumentexception(string.format("worker id can't be greater than %d or less than 0", maxworkerid));
        }
        if (datacenterid > maxdatacenterid || datacenterid < 0) {
            throw new illegalargumentexception(string.format("datacenter id can't be greater than %d or less than 0", maxdatacenterid));
        }
        this.workerid = workerid;
        this.datacenterid = datacenterid;
    }

    public synchronized long nextid() {
        long timestamp = timegen();
        
        if (timestamp < lasttimestamp) {
            throw new runtimeexception(string.format("时钟回拨: %d ms", lasttimestamp - timestamp));
        }

        if (lasttimestamp == timestamp) {
            sequence = (sequence + 1) & sequencemask;
            if (sequence == 0) {
                timestamp = tilnextmillis(lasttimestamp);
            }
        } else {
            sequence = 0;
        }

        lasttimestamp = timestamp;
        return (timestamp - twepoch) << (workeridbits + datacenteridbits + sequencebits)
                | (datacenterid << (workeridbits + sequencebits))
                | (workerid << sequencebits)
                | sequence;
    }

    protected long tilnextmillis(long lasttimestamp) {
        long timestamp = timegen();
        while (timestamp <= lasttimestamp) {
            timestamp = timegen();
        }
        return timestamp;
    }

    protected long timegen() {
        return system.currenttimemillis();
    }
}

snowflakeidgenerator idgenerator = new snowflakeidgenerator(1, 1);
long orderid = idgenerator.nextid();
system.out.println("生成的id: " + orderid);

# 启动redis
redis-server

# 设置初始值
set order_id 1000

import redis.clients.jedis.jedis;

public class redisidgenerator {
    private jedis jedis = new jedis("localhost");

    public long generateid() {
        return jedis.incr("order_id"); // 原子自增
    }
}

// redis cluster客户端配置
jediscluster jediscluster = new jediscluster(new hostandport("192.168.1.101", 6379),
                                             new hostandport("192.168.1.102", 6379));

// 1. 添加maven依赖
<dependency>
    <groupid>com.didi</groupid>
    <artifactid>tinyid-client</artifactid>
    <version>1.0.0</version>
</dependency>

// 2. 配置tinyid
tinyidclient client = new tinyidclient("http://tinyid-service.com");

// 3. 生成id
string businesstype = "order";
long id = client.getid(businesstype);

create table tinyid (
    id bigint primary key,
    business_type varchar(50),
    max_id bigint,
    step int,
    version int
);

import java.time.instant;
import java.util.random;

public class combidgenerator {
    private final random random = new random();

    public string generateid() {
        byte[] uuid = new byte[16];
        
        // 前6字节为时间戳
        long timestamp = instant.now().toepochmilli();
        for (int i = 0; i < 6; i++) {
            uuid[i] = (byte)(timestamp >> (8 * (5 - i)));
        }
        
        // 后10字节为随机数
        random.nextbytes(uuid);
        
        return bytestohex(uuid);
    }

    private string bytestohex(byte[] bytes) {
        stringbuilder sb = new stringbuilder();
        for (byte b : bytes) {
            sb.append(string.format("%02x", b));
        }
        return sb.tostring();
    }
}

// 1. 配置shardingsphere
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=snowflake
spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=123

public class customkeygenerator implements keygenerator {
    @override
    public comparable<?> generatekey() {
        return new snowflakeidgenerator(1, 1).nextid();
    }
}

public class shardidgenerator {
    private static final int shard_count = 8; // 8个分表
    private static final int gen_bits = 3; // 3位基因（2^3=8）

    public long generateshardid(long baseid, int shardindex) {
        // 基因掩码：0b00000111
        long mask = (1 << gen_bits) - 1;
        
        // 清除低3位基因
        long idwithoutgene = baseid & ~mask;
        
        // 设置新的基因
        return idwithoutgene | (shardindex & mask);
    }
}

long shardid = 1234567890123456789l;
int shardindex = (int)(shardid & ((1 << 3) - 1)); // 提取低3位
string tablename = "t_order_" + shardindex;

mainkeygenerator/
├── config/
│   └── shard.properties       # 分片配置
├── service/
│   ├── redisidservice.java    # redis生成器
│   ├── snowflakeservice.java  # snowflake实现
│   └── tinyidservice.java     # tinyid客户端
├── model/
│   └── order.java             # 订单模型
└── util/
    └── shardutil.java         # 分片工具类