MySQL为id选择合适的数据类型
选择 id 的数据类型，不仅仅需要考虑数据存储类型，还需要了解 mysql 对该种类型如何计算和比较。例如，mysql 将 enum 和 set 类型在内部使用整型存储，但是在字符串场景下会当做字符串进行比较。一旦选择了 id 的数据类型后，需要保证引用 id 的相关数据表的数据类型一致，而且是完全一致，这包括属性，例如长度、是否有符号！如果混用不同的数据类型可能导致性能问题，即便是没有性能问题，在进行比较时的隐式数据转换可能导致难以捉摸的错误。而如果在实际开发过程中忘记了数据类型不同这个问题，可能会突然出现意想不到的问题。
在选择长度的时候，也需要尽可能选择小的字段长度并给未来留有一定的增长空间。例如，如果是用于存放省份的话，我们只有几十个值，此时使用 tinyint 就 int 就更好，如果是相关的表也存有这个 id 的话，那么效率差别会很大。
下面是适用于 id 的一些典型的类型：
- 整型：整型通常来说是最佳的选择，这是因为整型的运算和比较都很快，而且还可以设置 auto_increment 属性自动递增。
- enum 和 set：通常不会选择枚举和集合作为 id，然后对于那些包含有“类型”、“状态”、“性别”这类型的列来说是挺合适的。例如我们需要有一张表存储下拉菜单时，通常会有一个值和一个名称，这个时候值使用枚举作为主键也是可以的。
- 字符串：尽可能地避免使用字符串作为 id，一是字符串占据的空间更大，二是通常会比整型慢。选用字符串作为 id 时，还需要特别注意 md5、sha1和 uuid 这些函数。每个值是在很大范围的随机值，没有次序，这会导致插入和查询更慢：
- 插入的时候，由于建立索引是随机位置（会导致分页、随机磁盘访问和聚集索引碎片），会降低插入速度。
- 查询的时候，相邻的数据行在磁盘或内存上上可能跨度很大，也会导致速度更慢。
如果确实要使用 uuid 值，应当移除掉“-”字符，或者是使用 unhex 函数将其转换为16字节数字，并使用 binary(16)存储。然后可以使用 hex 函数以十六进制的方式进行获取。uuid 产生的方法有很多，有些是随机分布的，有些是有序的，但是即便是有序的性能也不如整型。
分布式id方案总结
id是数据的唯一标识，传统的做法是利用uuid和数据库的自增id，如今mysql的应用越来越广泛，并且因为需要事务支持，所以通常会使用innodb存储引擎，uuid太长以及无序，所以并不适合在innodb中来作为主键，自增id比较合适，但是业务发展，数据量将越来越大，需要对数据进行分表，而分表后，每个表中的数据都会按自己的节奏进行自增，很有可能出现id冲突。这时就需要一个单独的机制来负责生成唯一id，生成出来的id也可以叫做分布式id，或全局id。下面来分析各个生成分布式id的机制。
数据库自增id
这种方式是基于数据库的自增id，需要单独使用一个数据库实例，在这个实例中新建一个单独的表：
表结构如下：
create database `seqid`; create table seqid.sequence_id ( id bigint(20) unsigned not null auto_increment, stub char(10) not null default '', primary key (id), unique key stub (stub) ) engine=myisam;
可以使用下面的语句生成并获取到一个自增id
begin;
replace into sequence_id (stub) values ('anyword');
select last_insert_id();
commit;
stub字段在这里并没有什么特殊的意义，只是为了方便的去插入数据，只有能插入数据才能产生自增id。而对于插入我们用的是replace，replace会先看是否存在stub指定值一样的数据，如果存在则先delete再insert，如果不存在则直接insert。
这种生成分布式id的机制，需要一个单独的mysql实例，虽然可行，但是基于性能与可靠性来考虑的话都不够，业务系统每次需要一个id时，都需要请求数据库获取，性能低，并且如果此数据库实例下线了，那么将影响所有的业务系统。；所以这种方式数据存在一定的不可靠性。
数据库多主模式
如果我们两个数据库组成一个主从模式集群，正常情况下可以解决数据库可靠性问题，但是如果主库挂掉后，数据没有及时同步到从库，这个时候会出现id重复的现象。这是我们可以使用多主模式☞双主模式集群，也就是两个mysql实例都能单独的生产自增id，这样能够提高效率，但是如果不经过其他改造的话，这两个mysql实例很可能会生成同样的id。需要单独给每个mysql实例配置不同的起始值和自增步长。
第一台mysql实例配置(mysql_01)：
set @@auto_increment_offset = 1; -- 起始值 set @@auto_increment_increment = 2; -- 步长
第二台mysql实例配置(mysql_02)：
set @@auto_increment_offset = 2; -- 起始值 set @@auto_increment_increment = 2; -- 步长
经过上面的配置后，这两个mysql实例生成的id序列如下：
mysql_01：起始值为1,步长为2,id生成的序列为：1,3,5,7,9,…
mysql_02：,起始值为2,步长为2,id生成的序列为：2,4,6,8,10,…
对于这种生成分布式id的方案，需要单独新增一个生成分布式id应用，比如distributidservice，该应用提供一个接口供业务应用获取id，业务应用需要一个id时，通过rpc的方式请求distributidservice，distributidservice随机去上面的两个mysql实例中去获取id。
实行这种方案后，就算其中某一台mysql实例下线了，也不会影响distributidservice，distributidservice仍然可以利用另外一台mysql来生成id。
但是这种方案的扩展性不太好，如果两台mysql实例不够用，需要新增mysql实例来提高性能时，这时就会比较麻烦。
现在如果要新增一个实例mysql_03，要怎么操作呢？
- 第一，mysql_01、mysql_02的步长肯定都要修改为3，而且只能是人工去修改，这是需要时间的。
- 第二，因为mysql_01和mysql_02是不停在自增的，对于mysql_03的起始值我们可能要定得大一点，以给充分的时间去修改mysql_01，mysql_01的步长。
- 第三，在修改步长的时候很可能会出现重复id，要解决这个问题，可能需要停机才行。
号段模式
该模式可以理解成批量获取，比如distributidservice从数据库获取id时，如果能批量获取多个id并缓存在本地的话，那样将大大提供业务应用获取id的效率。
比如distributidservice每次从数据库获取id时，就获取一个号段，比如(1,1000]，这个范围表示了1000个id，业务应用在请求distributidservice提供id时，distributidservice只需要在本地从1开始自增并返回即可，而不需要每次都请求数据库，一直到本地自增到1000时，也就是当前号段已经被用完时，才去数据库重新获取下一号段。
所以，我们需要对数据库表进行改动，如下：
create table id_generator ( id int(10) not null, current_max_id bigint(20) not null comment '当前最大id', increment_step int(10) not null comment '自增步长', primary key (`id`) ) engine=innodb default charset=utf8;
这个数据库表用来记录自增步长以及当前自增id的最大值（也就是当前已经被申请的号段的最后一个值），因为自增逻辑被移到distributidservice中去了，所以数据库不需要这部分逻辑了。
这种方案不再强依赖数据库，就算数据库不可用，那么distributidservice也能继续支撑一段时间。但是如果distributidservice重启，会丢失一段id，导致id空洞。
为了提高distributidservice的高可用，需要做一个集群，业务在请求distributidservice集群获取id时，会随机的选择某一个distributidservice节点进行获取，对每一个distributidservice节点来说，数据库连接的是同一个数据库，那么可能会产生多个distributidservice节点同时请求数据库获取号段，那么这个时候需要利用乐观锁来进行控制，比如在数据库表中增加一个version字段，在获取号段时使用如下sql：
update id_generator set current_max_id=#{newmaxid}, version=version+1 where version = #{version}
因为newmaxid是distributidservice中根据oldmaxid+步长算出来的，只要上面的update更新成功了就表示号段获取成功了。
为了提供数据库层的高可用，需要对数据库使用多主模式进行部署，对于每个数据库来说要保证生成的号段不重复，这就需要利用最开始的思路，再在刚刚的数据库表中增加起始值和步长，比如如果现在是两台mysql，那么:
mysql_01将生成号段（1,1001]，自增的时候序列为1，3，4，5，7…
mysql_02将生成号段（2,1002]，自增的时候序列为2，4，6，8，10…
具体实现代码可以参照：tinyid
雪花算法
数据库自增id模式、数据库多主模式、号段模式三种方式都是基于自增的思想；下面可以简单理解一下雪花算法的思想。
snowflake是twitter开源的分布式id生成算法，是一种算法，所以它和上面的三种生成分布式id机制不太一样，它不依赖数据库。
核心思想是：分布式id固定是一个long型的数字，一个long型占8个字节，也就是64个bit，原始snowflake算法中对于bit的分配如下图：
- 第一个bit位是标识部分，在java中由于long的最高位是符号位，正数是0，负数是1，一般生成的id为正数，所以固定为0。
- 时间戳部分占41bit，这个是毫秒级的时间，一般实现上不会存储当前的时间戳，而是时间戳的差值（当前时间-固定的开始时间），这样可以使产生的id从更小值开始；41位的时间戳可以使用69年，(1l << 41) / (1000l * 60 * 60 * 24 * 365) = 69年
- 工作机器id占10bit，这里比较灵活，比如，可以使用前5位作为数据中心机房标识，后5位作为单机房机器标识，可以部署1024个节点。
- 序列号部分占12bit，支持同一毫秒内同一个节点可以生成4096个id
根据这个算法的逻辑，只需要将这个算法用java语言实现出来，封装为一个工具方法，那么各个业务应用可以直接使用该工具方法来获取分布式id，只需保证每个业务应用有自己的工作机器id即可，而不需要单独去搭建一个获取分布式id的应用。它也不依赖数据库。
具体代码实现
package com.yeming.tinyid.application;
import static java.lang.system.*;
/**
* @author yeming.gao
* @description: 雪花算法实现
* <p>
* snowflake算法用来生成64位的id，刚好可以用long整型存储，能够用于分布式系统中生产唯一的id，
* 并且生成的id有大致的顺序。 在这次实现中，生成的64位id可以分成5个部分：
* 0 - 41位时间戳 - 5位数据中心标识 - 5位机器标识 - 12位序列号
* @date 2020/07/28 16:15
*/
public class snowflake {
/**
* 起始的时间戳
*/
private static final long start_stmp = 1480166465631l;
/**
* 机器标识占用的位数
*/
private static final long machine_bit = 5;
/**
* 数据中心占用的位数
*/
private static final long datacenter_bit = 5;
/**
* 序列号占用的位数
*/
private static final long sequence_bit = 12;
/**
* 机器标识最大值
*/
private static final long max_machine_num = ~(-1l << machine_bit);
/**
* 数据中心最大值
*/
private static final long max_datacenter_num = ~(-1l << datacenter_bit);
/**
* 序列号最大值
*/
private static final long max_sequence = ~(-1l << sequence_bit);
/**
* 每一部分向左的位移
*/
private static final long machine_left = sequence_bit;
private static final long datacenter_left = sequence_bit + machine_bit;
private static final long timestmp_left = datacenter_left + datacenter_bit;
private long datacenterid; //数据中心
private long machineid; //机器标识
private long sequence = 0l; //序列号
private long laststmp = -1l;//上一次时间戳
private snowflake(long datacenterid, long machineid) {
if (datacenterid > max_datacenter_num || datacenterid < 0) {
throw new illegalargumentexception("datacenterid can't be greater than max_datacenter_num or less than 0");
}
if (machineid > max_machine_num || machineid < 0) {
throw new illegalargumentexception("machineid can't be greater than max_machine_num or less than 0");
}
this.datacenterid = datacenterid;
this.machineid = machineid;
}
/**
* 产生下一个id
*
* @return long
*/
private synchronized long nextid() {
long currstmp = system.currenttimemillis();
if (currstmp < laststmp) {
throw new runtimeexception("clock moved backwards. refusing to generate id");
}
if (currstmp == laststmp) {
//相同毫秒内，序列号自增
sequence = (sequence + 1) & max_sequence;
//同一毫秒的序列数已经达到最大
if (sequence == 0l) {
currstmp = getnextmill();
}
} else {
//不同毫秒内，序列号置为0
sequence = 0l;
}
laststmp = currstmp;
return (currstmp - start_stmp) << timestmp_left //时间戳部分
| datacenterid << datacenter_left //数据中心部分
| machineid << machine_left //机器标识部分
| sequence; //序列号部分
}
private long getnextmill() {
long mill = system.currenttimemillis();
while (mill <= laststmp) {
mill = system.currenttimemillis();
}
return mill;
}
public static void main(string[] args) {
snowflake snowflake = new snowflake(2, 3);
//数据中心标识最大值
long maxdatacenternum = ~(-1l << datacenter_bit);
//机器标识最大值
long maxmachinenum = ~(-1l << machine_bit);
//序列号最大值
long maxsequence = ~(-1l << sequence_bit);
out.println("数据中心标识最大值:" + maxdatacenternum + ";机器标识最大值:" + maxmachinenum + ";序列号最大值:" + maxsequence);
for (int i = 0; i < (1 << 12); i++) {
out.println(snowflake.nextid());
}
}
}
雪花算法可以参照：
以上就是mysql为id选择合适的数据类型的详细内容，更多关于mysql id选择合适的数据类型的资料请关注其它相关文章！