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

小編給大家分享一下MySQL為id選擇合適數(shù)據(jù)類型的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

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

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

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

• 整型：整型通常來說是最佳的選擇，這是因?yàn)檎偷倪\(yùn)算和比較都很快，而且還可以設(shè)置 AUTO_INCREMENT 屬性自動遞增。

• ENUM 和 SET：通常不會選擇枚舉和集合作為 id，然后對于那些包含有“類型”、“狀態(tài)”、“性別”這類型的列來說是挺合適的。例如我們需要有一張表存儲下拉菜單時(shí)，通常會有一個(gè)值和一個(gè)名稱，這個(gè)時(shí)候值使用枚舉作為主鍵也是可以的。

• 字符串：盡可能地避免使用字符串作為 id，一是字符串占據(jù)的空間更大，二是通常會比整型慢。選用字符串作為 id 時(shí)，還需要特別注意 MD5、SHA1和 UUID 這些函數(shù)。每個(gè)值是在很大范圍的隨機(jī)值，沒有次序，這會導(dǎo)致插入和查詢更慢：

• 插入的時(shí)候，由于建立索引是隨機(jī)位置（會導(dǎo)致分頁、隨機(jī)磁盤訪問和聚集索引碎片），會降低插入速度。

• 查詢的時(shí)候，相鄰的數(shù)據(jù)行在磁盤或內(nèi)存上上可能跨度很大，也會導(dǎo)致速度更慢。
  

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

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

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

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

這種方式是基于數(shù)據(jù)庫的自增ID，需要單獨(dú)使用一個(gè)數(shù)據(jù)庫實(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;

可以使用下面的語句生成并獲取到一個(gè)自增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的機(jī)制，需要一個(gè)單獨(dú)的MySQL實(shí)例，雖然可行，但是基于性能與可靠性來考慮的話都不夠，業(yè)務(wù)系統(tǒng)每次需要一個(gè)ID時(shí)，都需要請求數(shù)據(jù)庫獲取，性能低，并且如果此數(shù)據(jù)庫實(shí)例下線了，那么將影響所有的業(yè)務(wù)系統(tǒng)。；所以這種方式數(shù)據(jù)存在一定的不可靠性。

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

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

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

set @@auto_increment_offset = 1;     -- 起始值
set @@auto_increment_increment = 2;  -- 步長

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

set @@auto_increment_offset = 2;     -- 起始值
set @@auto_increment_increment = 2;  -- 步長

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

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

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

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

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

• 第一，mysql_01、mysql_02的步長肯定都要修改為3，而且只能是人工去修改，這是需要時(shí)間的。

• 第二，因?yàn)閙ysql_01和mysql_02是不停在自增的，對于mysql_03的起始值我們可能要定得大一點(diǎn)，以給充分的時(shí)間去修改mysql_01，mysql_01的步長。

• 第三，在修改步長的時(shí)候很可能會出現(xiàn)重復(fù)ID，要解決這個(gè)問題，可能需要停機(jī)才行。
  

號段模式

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

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

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

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;

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

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

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

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

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

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

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

雪花算法

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

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

• 第一個(gè)bit位是標(biāo)識部分，在java中由于long的最高位是符號位，正數(shù)是0，負(fù)數(shù)是1，一般生成的ID為正數(shù)，所以固定為0。

• 時(shí)間戳部分占41bit，這個(gè)是毫秒級的時(shí)間，一般實(shí)現(xiàn)上不會存儲當(dāng)前的時(shí)間戳，而是時(shí)間戳的差值（當(dāng)前時(shí)間-固定的開始時(shí)間），這樣可以使產(chǎn)生的ID從更小值開始；41位的時(shí)間戳可以使用69年，(1L << 41) / (1000L * 60 * 60 * 24 * 365) = 69年

• 工作機(jī)器id占10bit，這里比較靈活，比如，可以使用前5位作為數(shù)據(jù)中心機(jī)房標(biāo)識，后5位作為單機(jī)房機(jī)器標(biāo)識，可以部署1024個(gè)節(jié)點(diǎn)。

• 序列號部分占12bit，支持同一毫秒內(nèi)同一個(gè)節(jié)點(diǎn)可以生成4096個(gè)ID
  

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

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

package com.yeming.tinyid.application;

import static java.lang.System.*;

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

    /**
     * 機(jī)器標(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;

    /**
     * 機(jī)器標(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; //機(jī)器標(biāo)識
    private long sequence = 0L; //序列號
    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)，序列號自增
            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 //時(shí)間戳部分
                | datacenterId << DATACENTER_LEFT //數(shù)據(jù)中心部分
                | machineId << MACHINE_LEFT //機(jī)器標(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);
        //機(jī)器標(biāo)識最大值
        long maxMachineNum = ~(-1L << MACHINE_BIT);
        //序列號最大值
        long maxSequence = ~(-1L << SEQUENCE_BIT);
        out.println("數(shù)據(jù)中心標(biāo)識最大值:" + maxDatacenterNum + ";機(jī)器標(biāo)識最大值:" + maxMachineNum + ";序列號最大值:" + maxSequence);
        for (int i = 0; i < (1 << 12); i++) {
            out.println(snowFlake.nextId());
        }
    }
}

以上是“MySQL為id選擇合適數(shù)據(jù)類型的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注億速云行業(yè)資訊頻道！