MySQL為id選擇合適的數(shù)據(jù)類型

更新時(shí)間：2021年06月07日 09:58:25 作者：島上碼農(nóng)

為 id 列選擇一個(gè)好的數(shù)據(jù)類型非常重要，id 列會(huì)經(jīng)常用于做比較（例如聯(lián)合查詢的條件），以及用于查找其他列。而且，id 也經(jīng)常用于外鍵。因此，id 列的數(shù)據(jù)類型不僅僅關(guān)系自身數(shù)據(jù)表，也關(guān)系到與之關(guān)聯(lián)的其他數(shù)據(jù)表。因此，id 用何種數(shù)據(jù)類型就顯得十分重要

選擇 id 的數(shù)據(jù)類型，不僅僅需要考慮數(shù)據(jù)存儲(chǔ)類型，還需要了解 MySQL 對(duì)該種類型如何計(jì)算和比較。例如，MySQL 將 ENUM 和 SET 類型在內(nèi)部使用整型存儲(chǔ)，但是在字符串場(chǎng)景下會(huì)當(dāng)做字符串進(jìn)行比較。一旦選擇了 id 的數(shù)據(jù)類型后，需要保證引用 id 的相關(guān)數(shù)據(jù)表的數(shù)據(jù)類型一致，而且是完全一致，這包括屬性，例如長(zhǎng)度、是否有符號(hào)！如果混用不同的數(shù)據(jù)類型可能導(dǎo)致性能問(wèn)題，即便是沒(méi)有性能問(wèn)題，在進(jìn)行比較時(shí)的隱式數(shù)據(jù)轉(zhuǎn)換可能導(dǎo)致難以捉摸的錯(cuò)誤。而如果在實(shí)際開(kāi)發(fā)過(guò)程中忘記了數(shù)據(jù)類型不同這個(gè)問(wèn)題，可能會(huì)突然出現(xiàn)意想不到的問(wèn)題。

在選擇長(zhǎng)度的時(shí)候，也需要盡可能選擇小的字段長(zhǎng)度并給未來(lái)留有一定的增長(zhǎng)空間。例如，如果是用于存放省份的話，我們只有幾十個(gè)值，此時(shí)使用 TINYINT 就 INT 就更好，如果是相關(guān)的表也存有這個(gè) id 的話，那么效率差別會(huì)很大。

下面是適用于 id 的一些典型的類型：

整型：整型通常來(lái)說(shuō)是最佳的選擇，這是因?yàn)檎偷倪\(yùn)算和比較都很快，而且還可以設(shè)置 AUTO_INCREMENT 屬性自動(dòng)遞增。
ENUM 和 SET：通常不會(huì)選擇枚舉和集合作為 id，然后對(duì)于那些包含有“類型”、“狀態(tài)”、“性別”這類型的列來(lái)說(shuō)是挺合適的。例如我們需要有一張表存儲(chǔ)下拉菜單時(shí)，通常會(huì)有一個(gè)值和一個(gè)名稱，這個(gè)時(shí)候值使用枚舉作為主鍵也是可以的。
字符串：盡可能地避免使用字符串作為 id，一是字符串占據(jù)的空間更大，二是通常會(huì)比整型慢。選用字符串作為 id 時(shí)，還需要特別注意 MD5、SHA1和 UUID 這些函數(shù)。每個(gè)值是在很大范圍的隨機(jī)值，沒(méi)有次序，這會(huì)導(dǎo)致插入和查詢更慢：
- 插入的時(shí)候，由于建立索引是隨機(jī)位置（會(huì)導(dǎo)致分頁(yè)、隨機(jī)磁盤(pán)訪問(wèn)和聚集索引碎片），會(huì)降低插入速度。
- 查詢的時(shí)候，相鄰的數(shù)據(jù)行在磁盤(pán)或內(nèi)存上上可能跨度很大，也會(huì)導(dǎo)致速度更慢。

如果確實(shí)要使用 UUID 值，應(yīng)當(dāng)移除掉“-”字符，或者是使用 UNHEX 函數(shù)將其轉(zhuǎn)換為16字節(jié)數(shù)字，并使用 BINARY(16)存儲(chǔ)。然后可以使用 HEX 函數(shù)以十六進(jìn)制的方式進(jìn)行獲取。UUID 產(chǎn)生的方法有很多，有些是隨機(jī)分布的，有些是有序的，但是即便是有序的性能也不如整型。

分布式ID方案總結(jié)

ID是數(shù)據(jù)的唯一標(biāo)識(shí)，傳統(tǒng)的做法是利用UUID和數(shù)據(jù)庫(kù)的自增ID，如今MySQL的應(yīng)用越來(lái)越廣泛，并且因?yàn)樾枰聞?wù)支持，所以通常會(huì)使用Innodb存儲(chǔ)引擎，UUID太長(zhǎng)以及無(wú)序，所以并不適合在Innodb中來(lái)作為主鍵，自增ID比較合適，但是業(yè)務(wù)發(fā)展，數(shù)據(jù)量將越來(lái)越大，需要對(duì)數(shù)據(jù)進(jìn)行分表，而分表后，每個(gè)表中的數(shù)據(jù)都會(huì)按自己的節(jié)奏進(jìn)行自增，很有可能出現(xiàn)ID沖突。這時(shí)就需要一個(gè)單獨(dú)的機(jī)制來(lái)負(fù)責(zé)生成唯一ID，生成出來(lái)的ID也可以叫做分布式ID，或全局ID。下面來(lái)分析各個(gè)生成分布式ID的機(jī)制。

數(shù)據(jù)庫(kù)自增ID

這種方式是基于數(shù)據(jù)庫(kù)的自增ID，需要單獨(dú)使用一個(gè)數(shù)據(jù)庫(kù)實(shí)例，在這個(gè)實(shí)例中新建一個(gè)單獨(dú)的表：

表結(jié)構(gòu)如下：

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;

可以使用下面的語(yǔ)句生成并獲取到一個(gè)自增ID

begin;
replace into SEQUENCE_ID (stub) VALUES ('anyword');
select last_insert_id();
commit;

stub字段在這里并沒(méi)有什么特殊的意義，只是為了方便的去插入數(shù)據(jù)，只有能插入數(shù)據(jù)才能產(chǎn)生自增id。而對(duì)于插入我們用的是replace，replace會(huì)先看是否存在stub指定值一樣的數(shù)據(jù)，如果存在則先delete再insert，如果不存在則直接insert。

這種生成分布式ID的機(jī)制，需要一個(gè)單獨(dú)的MySQL實(shí)例，雖然可行，但是基于性能與可靠性來(lái)考慮的話都不夠，業(yè)務(wù)系統(tǒng)每次需要一個(gè)ID時(shí)，都需要請(qǐng)求數(shù)據(jù)庫(kù)獲取，性能低，并且如果此數(shù)據(jù)庫(kù)實(shí)例下線了，那么將影響所有的業(yè)務(wù)系統(tǒng)。；所以這種方式數(shù)據(jù)存在一定的不可靠性。

數(shù)據(jù)庫(kù)多主模式

如果我們兩個(gè)數(shù)據(jù)庫(kù)組成一個(gè)主從模式集群，正常情況下可以解決數(shù)據(jù)庫(kù)可靠性問(wèn)題，但是如果主庫(kù)掛掉后，數(shù)據(jù)沒(méi)有及時(shí)同步到從庫(kù)，這個(gè)時(shí)候會(huì)出現(xiàn)ID重復(fù)的現(xiàn)象。這是我們可以使用多主模式☞雙主模式集群，也就是兩個(gè)MySQL實(shí)例都能單獨(dú)的生產(chǎn)自增ID，這樣能夠提高效率，但是如果不經(jīng)過(guò)其他改造的話，這兩個(gè)MySQL實(shí)例很可能會(huì)生成同樣的ID。需要單獨(dú)給每個(gè)MySQL實(shí)例配置不同的起始值和自增步長(zhǎng)。

第一臺(tái)MySQL實(shí)例配置(mysql_01)：

set @@auto_increment_offset = 1;     -- 起始值
set @@auto_increment_increment = 2;  -- 步長(zhǎng)

第二臺(tái)MySQL實(shí)例配置(mysql_02)：

set @@auto_increment_offset = 2;     -- 起始值
set @@auto_increment_increment = 2;  -- 步長(zhǎng)

經(jīng)過(guò)上面的配置后，這兩個(gè)MySQL實(shí)例生成的id序列如下：
mysql_01：起始值為1,步長(zhǎng)為2,ID生成的序列為：1,3,5,7,9,…
mysql_02：,起始值為2,步長(zhǎng)為2,ID生成的序列為：2,4,6,8,10,…

對(duì)于這種生成分布式ID的方案，需要單獨(dú)新增一個(gè)生成分布式ID應(yīng)用，比如DistributIdService，該應(yīng)用提供一個(gè)接口供業(yè)務(wù)應(yīng)用獲取ID，業(yè)務(wù)應(yīng)用需要一個(gè)ID時(shí)，通過(guò)rpc的方式請(qǐng)求DistributIdService，DistributIdService隨機(jī)去上面的兩個(gè)MySQL實(shí)例中去獲取ID。

實(shí)行這種方案后，就算其中某一臺(tái)MySQL實(shí)例下線了，也不會(huì)影響DistributIdService，DistributIdService仍然可以利用另外一臺(tái)MySQL來(lái)生成ID。

但是這種方案的擴(kuò)展性不太好，如果兩臺(tái)MySQL實(shí)例不夠用，需要新增MySQL實(shí)例來(lái)提高性能時(shí)，這時(shí)就會(huì)比較麻煩。

現(xiàn)在如果要新增一個(gè)實(shí)例mysql_03，要怎么操作呢？

第一，mysql_01、mysql_02的步長(zhǎng)肯定都要修改為3，而且只能是人工去修改，這是需要時(shí)間的。
第二，因?yàn)閙ysql_01和mysql_02是不停在自增的，對(duì)于mysql_03的起始值我們可能要定得大一點(diǎn)，以給充分的時(shí)間去修改mysql_01，mysql_01的步長(zhǎng)。
第三，在修改步長(zhǎng)的時(shí)候很可能會(huì)出現(xiàn)重復(fù)ID，要解決這個(gè)問(wèn)題，可能需要停機(jī)才行。

號(hào)段模式

該模式可以理解成批量獲取，比如DistributIdService從數(shù)據(jù)庫(kù)獲取ID時(shí)，如果能批量獲取多個(gè)ID并緩存在本地的話，那樣將大大提供業(yè)務(wù)應(yīng)用獲取ID的效率。

比如DistributIdService每次從數(shù)據(jù)庫(kù)獲取ID時(shí)，就獲取一個(gè)號(hào)段，比如(1,1000]，這個(gè)范圍表示了1000個(gè)ID，業(yè)務(wù)應(yīng)用在請(qǐng)求DistributIdService提供ID時(shí)，DistributIdService只需要在本地從1開(kāi)始自增并返回即可，而不需要每次都請(qǐng)求數(shù)據(jù)庫(kù)，一直到本地自增到1000時(shí)，也就是當(dāng)前號(hào)段已經(jīng)被用完時(shí)，才去數(shù)據(jù)庫(kù)重新獲取下一號(hào)段。

所以，我們需要對(duì)數(shù)據(jù)庫(kù)表進(jìn)行改動(dòng)，如下：

CREATE TABLE id_generator (
  id int(10) NOT NULL,
  current_max_id bigint(20) NOT NULL COMMENT '當(dāng)前最大id',
  increment_step int(10) NOT NULL COMMENT '自增步長(zhǎng)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

這個(gè)數(shù)據(jù)庫(kù)表用來(lái)記錄自增步長(zhǎng)以及當(dāng)前自增ID的最大值（也就是當(dāng)前已經(jīng)被申請(qǐng)的號(hào)段的最后一個(gè)值），因?yàn)樽栽鲞壿嫳灰频紻istributIdService中去了，所以數(shù)據(jù)庫(kù)不需要這部分邏輯了。

這種方案不再?gòu)?qiáng)依賴數(shù)據(jù)庫(kù)，就算數(shù)據(jù)庫(kù)不可用，那么DistributIdService也能繼續(xù)支撐一段時(shí)間。但是如果DistributIdService重啟，會(huì)丟失一段ID，導(dǎo)致ID空洞。

為了提高DistributIdService的高可用，需要做一個(gè)集群，業(yè)務(wù)在請(qǐng)求DistributIdService集群獲取ID時(shí)，會(huì)隨機(jī)的選擇某一個(gè)DistributIdService節(jié)點(diǎn)進(jìn)行獲取，對(duì)每一個(gè)DistributIdService節(jié)點(diǎn)來(lái)說(shuō)，數(shù)據(jù)庫(kù)連接的是同一個(gè)數(shù)據(jù)庫(kù)，那么可能會(huì)產(chǎn)生多個(gè)DistributIdService節(jié)點(diǎn)同時(shí)請(qǐng)求數(shù)據(jù)庫(kù)獲取號(hào)段，那么這個(gè)時(shí)候需要利用樂(lè)觀鎖來(lái)進(jìn)行控制，比如在數(shù)據(jù)庫(kù)表中增加一個(gè)version字段，在獲取號(hào)段時(shí)使用如下SQL：

update id_generator set current_max_id=#{newMaxId}, version=version+1 where version = #{version}

因?yàn)閚ewMaxId是DistributIdService中根據(jù)oldMaxId+步長(zhǎng)算出來(lái)的，只要上面的update更新成功了就表示號(hào)段獲取成功了。

為了提供數(shù)據(jù)庫(kù)層的高可用，需要對(duì)數(shù)據(jù)庫(kù)使用多主模式進(jìn)行部署，對(duì)于每個(gè)數(shù)據(jù)庫(kù)來(lái)說(shuō)要保證生成的號(hào)段不重復(fù)，這就需要利用最開(kāi)始的思路，再在剛剛的數(shù)據(jù)庫(kù)表中增加起始值和步長(zhǎng)，比如如果現(xiàn)在是兩臺(tái)MySQL，那么:
mysql_01將生成號(hào)段（1,1001]，自增的時(shí)候序列為1，3，4，5，7…
mysql_02將生成號(hào)段（2,1002]，自增的時(shí)候序列為2，4，6，8，10…

具體實(shí)現(xiàn)代碼可以參照：tinyid

雪花算法

數(shù)據(jù)庫(kù)自增ID模式、數(shù)據(jù)庫(kù)多主模式、號(hào)段模式三種方式都是基于自增的思想；下面可以簡(jiǎn)單理解一下雪花算法的思想。
snowflake是twitter開(kāi)源的分布式ID生成算法，是一種算法，所以它和上面的三種生成分布式ID機(jī)制不太一樣，它不依賴數(shù)據(jù)庫(kù)。

核心思想是：分布式ID固定是一個(gè)long型的數(shù)字，一個(gè)long型占8個(gè)字節(jié)，也就是64個(gè)bit，原始snowflake算法中對(duì)于bit的分配如下圖：

第一個(gè)bit位是標(biāo)識(shí)部分，在java中由于long的最高位是符號(hào)位，正數(shù)是0，負(fù)數(shù)是1，一般生成的ID為正數(shù)，所以固定為0。
時(shí)間戳部分占41bit，這個(gè)是毫秒級(jí)的時(shí)間，一般實(shí)現(xiàn)上不會(huì)存儲(chǔ)當(dāng)前的時(shí)間戳，而是時(shí)間戳的差值（當(dāng)前時(shí)間-固定的開(kāi)始時(shí)間），這樣可以使產(chǎn)生的ID從更小值開(kāi)始；41位的時(shí)間戳可以使用69年，(1L << 41) / (1000L * 60 * 60 * 24 * 365) = 69年
工作機(jī)器id占10bit，這里比較靈活，比如，可以使用前5位作為數(shù)據(jù)中心機(jī)房標(biāo)識(shí)，后5位作為單機(jī)房機(jī)器標(biāo)識(shí)，可以部署1024個(gè)節(jié)點(diǎn)。
序列號(hào)部分占12bit，支持同一毫秒內(nèi)同一個(gè)節(jié)點(diǎn)可以生成4096個(gè)ID

根據(jù)這個(gè)算法的邏輯，只需要將這個(gè)算法用Java語(yǔ)言實(shí)現(xiàn)出來(lái)，封裝為一個(gè)工具方法，那么各個(gè)業(yè)務(wù)應(yīng)用可以直接使用該工具方法來(lái)獲取分布式ID，只需保證每個(gè)業(yè)務(wù)應(yīng)用有自己的工作機(jī)器id即可，而不需要單獨(dú)去搭建一個(gè)獲取分布式ID的應(yīng)用。它也不依賴數(shù)據(jù)庫(kù)。

具體代碼實(shí)現(xiàn)

package com.yeming.tinyid.application;

import static java.lang.System.*;

/**
 * @author yeming.gao
 * @Description: 雪花算法實(shí)現(xiàn)
 * <p>
 * SnowFlake算法用來(lái)生成64位的ID，剛好可以用long整型存儲(chǔ)，能夠用于分布式系統(tǒng)中生產(chǎn)唯一的ID，
 * 并且生成的ID有大致的順序。 在這次實(shí)現(xiàn)中，生成的64位ID可以分成5個(gè)部分：
 * 0 - 41位時(shí)間戳 - 5位數(shù)據(jù)中心標(biāo)識(shí) - 5位機(jī)器標(biāo)識(shí) - 12位序列號(hào)
 * @date 2020/07/28 16:15
 */
public class SnowFlake {
    /**
     * 起始的時(shí)間戳
     */
    private static final long START_STMP = 1480166465631L;

    /**
     * 機(jī)器標(biāo)識(shí)占用的位數(shù)
     */
    private static final long MACHINE_BIT = 5;
    /**
     * 數(shù)據(jù)中心占用的位數(shù)
     */
    private static final long DATACENTER_BIT = 5;
    /**
     * 序列號(hào)占用的位數(shù)
     */
    private static final long SEQUENCE_BIT = 12;

    /**
     * 機(jī)器標(biāo)識(shí)最大值
     */
    private static final long MAX_MACHINE_NUM = ~(-1L << MACHINE_BIT);
    /**
     * 數(shù)據(jù)中心最大值
     */
    private static final long MAX_DATACENTER_NUM = ~(-1L << DATACENTER_BIT);
    /**
     * 序列號(hào)最大值
     */
    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; //數(shù)據(jù)中心
    private long machineId; //機(jī)器標(biāo)識(shí)
    private long sequence = 0L; //序列號(hào)
    private long lastStmp = -1L;//上一次時(shí)間戳

    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;
    }

    /**
     * 產(chǎn)生下一個(gè)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) {
            //相同毫秒內(nèi)，序列號(hào)自增
            sequence = (sequence + 1) & MAX_SEQUENCE;
            //同一毫秒的序列數(shù)已經(jīng)達(dá)到最大
            if (sequence == 0L) {
                currStmp = getNextMill();
            }
        } else {
            //不同毫秒內(nèi)，序列號(hào)置為0
            sequence = 0L;
        }
        lastStmp = currStmp;
        return (currStmp - START_STMP) << TIMESTMP_LEFT //時(shí)間戳部分
                | datacenterId << DATACENTER_LEFT //數(shù)據(jù)中心部分
                | machineId << MACHINE_LEFT //機(jī)器標(biāo)識(shí)部分
                | sequence; //序列號(hào)部分
    }

    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);
        //數(shù)據(jù)中心標(biāo)識(shí)最大值
        long maxDatacenterNum = ~(-1L << DATACENTER_BIT);
        //機(jī)器標(biāo)識(shí)最大值
        long maxMachineNum = ~(-1L << MACHINE_BIT);
        //序列號(hào)最大值
        long maxSequence = ~(-1L << SEQUENCE_BIT);
        out.println("數(shù)據(jù)中心標(biāo)識(shí)最大值:" + maxDatacenterNum + ";機(jī)器標(biāo)識(shí)最大值:" + maxMachineNum + ";序列號(hào)最大值:" + maxSequence);
        for (int i = 0; i < (1 << 12); i++) {
            out.println(snowFlake.nextId());
        }
    }
}

雪花算法可以參照：