mysql自增主键和uuid mysql主键自增长和uuid

转载

mob64ca140d96d9 2023-08-06 13:49:54

文章标签 mysql自增主键和uuid mysql 性能主键自增 文章分类 MySQL 数据库

mysql不要用uuid用自增主键，自增主键效率高innodb的索引特性导致了自增id做主键是效率最好的

分别对自增主键和uuid主键记录表进行性能测试：

1、500W总结

在500W记录表的测试下：

（1）普通单条或者20条左右的记录检索，uuid为主键的相差不大几乎效率相同；

（2）但是范围查询特别是上百成千条的记录查询，自增id的效率要大于uuid；

（3）在范围查询做统计汇总的时候，自增id的效率要大于uuid；

（4）在存储上面，自增id所占的存储空间是uuid的1/2；

（5）在备份恢复上，自增ID主键稍微优于UUID。

2、1000W总结

在1000W记录表的测试下：

（1）普通单条或者20条左右的记录检索，自增主键效率是uuid主键的2到3倍；

（2）但是范围查询特别是上百成千条的记录查询，自增id的效率要大于uuid；

（3）在范围查询做统计汇总的时候，自增id主键的效率是uuid主键1.5到2倍；

（4）在存储上面，自增id所占的存储空间是uuid的1/2；

（5）在写入上面，自增ID主键的效率是UUID主键的3到10倍，相差比较明显，特别是update小范围之内的数据上面。

（6）在备份恢复上，自增ID主键稍微优于UUID。

3、MySQL分布式架构的取舍

分布式架构，意味着需要多个实例中保持一个表的主键的唯一性。这个时候普通的单表自增ID主键就不太合适，因为多个mysql实例上会遇到主键全局唯一性问题。

3.1、自增ID主键+步长，适合中等规模的分布式场景

在每个集群节点组的master上面，设置（auto_increment_increment），让目前每个集群的起始点错开 1，步长选择大于将来基本不可能达到的切分集群数，达到将 ID 相对分段的效果来满足全局唯一的效果。

优点是：实现简单，后期维护简单，对应用透明。

缺点是：第一次设置相对较为复杂，因为要针对未来业务的发展而计算好足够的步长;

规划：

比如计划总共N个节点组，那么第i个节点组的my.cnf的配置为：

auto_increment_offset i

auto_increment_increment N

假如规划48个节点组，N为48，现在配置第8个节点组，这个i为8，第8个节点组的my.cnf里面的配置为：

auto_increment_offset 8

auto_increment_increment 48

3.2、UUID，适合小规模的分布式环境

对于InnoDB这种聚集主键类型的引擎来说，数据会按照主键进行排序，由于UUID的无序性，InnoDB会产生巨大的IO压力，而且由于索引和数据存储在一起，字符串做主键会造成存储空间增大一倍。

在存储和检索的时候，innodb会对主键进行物理排序，这对auto_increment_int是个好消息，因为后一次插入的主键位置总是在最后。但是对uuid来说，这却是个坏消息，因为uuid是杂乱无章的，每次插入的主键位置是不确定的，可能在开头，也可能在中间，在进行主键物理排序的时候，势必会造成大量的 IO操作影响效率，在数据量不停增长的时候，特别是数据量上了千万记录的时候，读写性能下降的非常厉害。

优点：搭建比较简单，不需要为主键唯一性的处理。

缺点：占用两倍的存储空间（在云上光存储一块就要多花2倍的钱），后期读写性能下降厉害。

3.3、雪花算法自造全局自增ID，适合大数据环境的分布式场景

由twitter公布的开源的分布式id算法snowflake（Java版本）

IdWorker.java：

package com.demo.elk;
import org.slf4j.Logger; 
import org.slf4j.LoggerFactory;

public class IdWorker {
    protected static final Logger LOG = LoggerFactory.getLogger(IdWorker.class);
    private long workerId;
    private long datacenterId;
    private long sequence = 0L;
    private long twepoch = 1288834974657L;
    private long workerIdBits = 5L;
    private long datacenterIdBits = 5L;
    private long maxWorkerId = -1L ^ (-1L << workerIdBits);
    private long maxDatacenterId = -1L ^ (-1L << datacenterIdBits);
    private long sequenceBits = 12L;
    private long workerIdShift = sequenceBits;
    private long datacenterIdShift = sequenceBits + workerIdBits;
    private long timestampLeftShift = sequenceBits + workerIdBits + datacenterIdBits;
    private long sequenceMask = -1L ^ (-1L << sequenceBits);
    private long lastTimestamp = -1L;

    public IdWorker(long workerId, long datacenterId) {
        // sanity check for workerId
        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;
        LOG.info(String.format("worker starting. timestamp left shift %d, datacenter id bits %d, worker id bits %d, sequence bits %d, workerid %d", timestampLeftShift, datacenterIdBits, workerIdBits, sequenceBits, workerId));

    }

    public synchronized long nextId() {

        long timestamp = timeGen();

        if (timestamp < lastTimestamp) {
            LOG.error(String.format("clock is moving backwards.  Rejecting requests until %d.", lastTimestamp));
            throw new RuntimeException(String.format("Clock moved backwards.  Refusing to generate id for %d milliseconds", lastTimestamp - timestamp));
        }

        if (lastTimestamp == timestamp) {
            sequence = (sequence + 1) & sequenceMask;
            if (sequence == 0) {
                timestamp = tilNextMillis(lastTimestamp);
            }
        } else {
            sequence = 0L;
        }
        lastTimestamp = timestamp;

        return ((timestamp - twepoch) << timestampLeftShift) | (datacenterId <<datacenterIdShift) | (workerId << workerIdShift) | sequence;
    }

    protected long tilNextMillis(long lastTimestamp) {
        long timestamp = timeGen();
        while (timestamp <= lastTimestamp) {
            timestamp = timeGen();
        }
        return timestamp;
    }

    protected long timeGen() {
        return System.currentTimeMillis();
    }
}