自定义 UUID 作为主键

Question

我一直在阅读使用 UUID 作为数据库主键的优点和缺点。

我听到的反对这种做法的主要论点是，如果它们不是按顺序生成的，它们可能会使您的索引碎片化并造成分页问题（我也听说这会耗尽数据库的大小，但让我们先把它放在一边）现在）。

MSSQL Server 允许您使用自定义方法在数据库中创建顺序 UUID（例如 CREATE TABLE MyUniqueTable(UniqueColumn UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID())。

但它的问题是它创建了一个不符合标准的 UUID，显然不是顺序的。我有向后设计格式并将其封装在构建器类中以供使用或研究：

/**
 * <p>
 * Reverse engineering effort to replicate how SQL Server creates ordered 
 * UUIDs so that we may construct them within the application. The builder will 
 * only accept version 1 and version 14 (Microsoft specific) uuid objects as a 
 * seed.
 * </p>
 * <p>
 * The algorithm is reversible so that a version 1 uuid may be created from a version
 * 14 uuid and vice versa.
 * </p>
 * @author Michael Lambert
 *
 */
public static class MsSqlOrderedUuidBuilder {

    private static final TimeBasedGenerator generator = Generators.timeBasedGenerator();

    private final UUID uuid;

    public MsSqlOrderedUuidBuilder(UUID uuid) {

        if(uuid.version() != 1 && uuid.version() != 14) {
            throw new IllegalArgumentException(String.format("UUID is not a version 1 UUID (version is %d)", uuid.version()));
        }
        this.uuid = uuid;
    }

    public MsSqlOrderedUuidBuilder() {
        this(generator.generate());
    }

    private long getMostSignificantBits() {

        ByteBuffer buffer = ByteBuffer.wrap(new byte[8]);

        buffer.putLong(uuid.getMostSignificantBits());
        buffer.rewind();

        byte[] timeLow = new byte[4];
        buffer.get(timeLow);

        byte[] timeMid = new byte[2];
        buffer.get(timeMid);

        byte[] timeHigh = new byte[2]; // time_high and version
        buffer.get(timeHigh);

        buffer.clear();

        buffer.order(buffer.order().equals(ByteOrder.LITTLE_ENDIAN) ? ByteOrder.BIG_ENDIAN : ByteOrder.LITTLE_ENDIAN);

        buffer.put(timeHigh);
        buffer.put(timeMid);
        buffer.put(timeLow);

        return buffer.getLong(0);
    }

    private long getLeastSignificantBits() {
        return uuid.getLeastSignificantBits();
    }

    public UUID build() {
        return new UUID(getMostSignificantBits(), getLeastSignificantBits());
    }
}

如果我尝试使用此类将生成的 UUID 存储在不同的数据库中（我还必须写信给 MySQL）最终不会被排序，我又回到了原来的问题。

我的解决方案是创建我自己的可逆自定义 UUID，当序列化为字节数组时，它是按顺序排序的：

/**
 * <p>
 * Creates a custom UUID type with sequential bytes. The builder must be seeded with a version 1 uuid and the
 * algorithm is reversible.
 * </p>
 * @author Michael Lambert
 *
 */
public static class SequentialUuidBuilder {

    private static final TimeBasedGenerator generator = Generators.timeBasedGenerator();

    private final UUID uuid;

    public SequentialUuidBuilder(UUID uuid) {

        if(uuid.version() != 1 && uuid.version() != 13) {
            throw new IllegalArgumentException(String.format("UUID is not a version 1 UUID (version is %d)", uuid.version()));
        }
        this.uuid = uuid;
    }

    public SequentialUuidBuilder() {
        this(generator.generate());
    }

    private long getVersion13MostSignificantBits() {

        if(uuid.version() == 1) {

            // System.out.println(String.format("original: %x", version1.getMostSignificantBits()));
            //
            // System.out.println(String.format("lowa %x", timeLowA));
            //
            // 0xAAAAA00000000000L
            // 0x0000000AAAAA0000L
            //
            long timeLowPartA = (uuid.getMostSignificantBits() & 0xFFFFF00000000000L) >>> 28;
            //
            // 0x00000BBB00000000L
            // 0x0000000000000BBBL
            //
            long timeLowPartB = (uuid.getMostSignificantBits() & 0x00000FFF00000000L) >>> 32;
            //
            // System.out.println(String.format("lowb %x", timeLowB));
            //
            // 0x00000000MMMM0000L
            // 0x000MMMM000000000L
            //
            long timeMid = (uuid.getMostSignificantBits() &  0x00000000FFFF0000L) << 20;
            //
            // System.out.println(String.format("med %x", (timeMid)));
            //
            // 0x0000000000000HHHL
            // 0xHHH0000000000000L
            //
            long timeHigh = (uuid.getMostSignificantBits() & 0x0000000000000FFFL) << 52;
            //
            // System.out.println(String.format("high %x", timeHigh));
            //
            // 0x000000000000V000L
            // 0x000000000000V000L
            //
            // long version = (version1.getMostSignificantBits() &  0x000000000000F000L);
            //
            // System.out.println(String.format("version %x", version));
            //
            // 0x0000000AAAAA0000L
            // 0x0000000000000BBBL
            // 0x000MMMM000000000L
            // 0xHHH0000000000000L
            // 0x000000000000V000L <-- we don't change where the version is stored because we want to respect that part of the spec
            // ____________________
            // 0xHHHMMMMAAAAAVBBBL
            //
            long ordered = timeLowPartA | timeLowPartB | timeMid | timeHigh | 0x000000000000D000L; // custom version

            return ordered;
        }
        return 0;
    }

    public long getVersion1MostSignificantBits() {
        //
        // 0xHHHMMMMAAAAAVBBBL
        //
        long timeLowPartA = (uuid.getMostSignificantBits() & 0x0000000FFFFF0000L) << 28;
        long timeLowPartB = (uuid.getMostSignificantBits() & 0x0000000000000FFFL) << 32;
        long timeMid = (uuid.getMostSignificantBits() &  0x000FFFF000000000L) >> 20;
        long timeHigh = (uuid.getMostSignificantBits() & 0xFFF0000000000000L) >> 52;
        //
        // 0xAAAAA00000000000L
        // 0x00000000MMMM0000L
        // 0x00000BBB00000000L
        // 0x0000000000000HHHL
        // 0x000000000000V000L
        // ___________________
        // 0xAAAAABBBMMMMVHHHL
        //
        long bits = timeLowPartA | timeLowPartB | timeMid | timeHigh | 0x0000000000001000L; // reinstate version

        return bits;
    }

    private long getMostSignificantBits() {
        return (uuid.version() == 13) ? getVersion1MostSignificantBits() : getVersion13MostSignificantBits();
    }

    private long getLeastSignificantBits() {
        return uuid.getLeastSignificantBits();
    }

    public UUID build() {
        return new UUID(uuid.version() == 13 ? getVersion1MostSignificantBits() : getMostSignificantBits(), getLeastSignificantBits());
    }
}

我的问题是：这是可以接受的做法吗？我可以使用 BINARY(16) 来存储主键吗？可以用这种方式使用自定义标识符吗？

提前谢谢大家！

Answer 1

使用序列生成器，除非您确实需要密钥具有普遍唯一性。