MySQL为id选择合适的数据类型

为 id 列选择一个好的数据类型非常重要,id 列会经常用于做比较(例如联合查询的条件),以及用于查找其他列。而且,id 也经常用于外键。因此,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的机制。

MySQL为id选择合适的数据类型

数据库自增ID

这种方式是基于数据库的自增ID,需要单独使用一个数据库实例,在这个实例中新建一个单独的表:

表结构如下:

?

1

2

3

4

5

6

7

8

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

?

1

2

3

4

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):

?

1

2

set @@auto_increment_offset = 1; — 起始值

set @@auto_increment_increment = 2; — 步长

第二台MySQL实例配置(mysql_02):

?

1

2

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时,也就是当前号段已经被用完时,才去数据库重新获取下一号段。

所以,我们需要对数据库表进行改动,如下:

?

1

2

3

4

5

6

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:

?

1

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的分配如下图:

MySQL为id选择合适的数据类型

  • 第一个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的应用。它也不依赖数据库。

具体代码实现

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

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());

}

}

}

雪花算法可以参照:

  • 百度(uid-generator)
  • 美团(Leaf)

以上就是MySQL为id选择合适的数据类型的详细内容,更多关于MySQL id选择合适的数据类型的资料请关注钦钦技术栈其它相关文章!

原文链接:https://juejin.cn/post/6969928505932906510

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/7050/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-07-31 3:31:41
下一篇 2022-07-31 3:31:59

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!