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

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

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

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

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

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

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

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

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

表結(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;

可以使用下面的語句生成并獲取到一個自增ID

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

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

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

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

如果我們兩個數(shù)據(jù)庫組成一個主從模式集群，正常情況下可以解決數(shù)據(jù)庫可靠性問題，但是如果主庫掛掉后，數(shù)據(jù)沒有及時同步到從庫，這個時候會出現(xiàn)ID重復(fù)的現(xiàn)象。這是我們可以使用多主模式☞雙主模式集群，也就是兩個MySQL實例都能單獨的生產(chǎn)自增ID，這樣能夠提高效率，但是如果不經(jīng)過其他改造的話，這兩個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;  -- 步長

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

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

實行這種方案后，就算其中某一臺MySQL實例下線了，也不會影響DistributIdService，DistributIdService仍然可以利用另外一臺MySQL來生成ID。

但是這種方案的擴展性不太好，如果兩臺MySQL實例不夠用，需要新增MySQL實例來提高性能時，這時就會比較麻煩。

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

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

號段模式

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

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

所以，我們需要對數(shù)據(jù)庫表進行改動，如下：

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 '自增步長',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

這個數(shù)據(jù)庫表用來記錄自增步長以及當(dāng)前自增ID的最大值（也就是當(dāng)前已經(jīng)被申請的號段的最后一個值），因為自增邏輯被移到DistributIdService中去了，所以數(shù)據(jù)庫不需要這部分邏輯了。

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

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

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

因為newMaxId是DistributIdService中根據(jù)oldMaxId+步長算出來的，只要上面的update更新成功了就表示號段獲取成功了。

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

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

雪花算法

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

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

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

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

具體代碼實現(xiàn)

package com.yeming.tinyid.application;

import static java.lang.System.*;

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

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

    /**
     * 機器標(biāo)識最大值
     */
    private static final long MAX_MACHINE_NUM = ~(-1L  MACHINE_BIT);
    /**
     * 數(shù)據(jù)中心最大值
     */
    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; //數(shù)據(jù)中心
    private long machineId; //機器標(biāo)識
    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;
    }

    /**
     * 產(chǎn)生下一個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)，序列號自增
            sequence = (sequence + 1)  MAX_SEQUENCE;
            //同一毫秒的序列數(shù)已經(jīng)達(dá)到最大
            if (sequence == 0L) {
                currStmp = getNextMill();
            }
        } else {
            //不同毫秒內(nèi)，序列號置為0
            sequence = 0L;
        }
        lastStmp = currStmp;
        return (currStmp - START_STMP)  TIMESTMP_LEFT //時間戳部分
                | datacenterId  DATACENTER_LEFT //數(shù)據(jù)中心部分
                | machineId  MACHINE_LEFT //機器標(biāo)識部分
                | 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);
        //數(shù)據(jù)中心標(biāo)識最大值
        long maxDatacenterNum = ~(-1L  DATACENTER_BIT);
        //機器標(biāo)識最大值
        long maxMachineNum = ~(-1L  MACHINE_BIT);
        //序列號最大值
        long maxSequence = ~(-1L  SEQUENCE_BIT);
        out.println("數(shù)據(jù)中心標(biāo)識最大值:" + maxDatacenterNum + ";機器標(biāo)識最大值:" + maxMachineNum + ";序列號最大值:" + maxSequence);
        for (int i = 0; i  (1  12); i++) {
            out.println(snowFlake.nextId());
        }
    }
}

雪花算法可以參照：

• 百度（uid-generator）
• 美團（Leaf）

以上就是MySQL為id選擇合適的數(shù)據(jù)類型的詳細(xì)內(nèi)容，更多關(guān)于MySQL id選擇合適的數(shù)據(jù)類型的資料請關(guān)注腳本之家其它相關(guān)文章！