MySQL為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的機制。

數據庫自增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());} }}

雪花算法可以參照：

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

以上就是MySQL為id選擇合適的數據類型的詳細內容，更多關于MySQL id選擇合適的數據類型的資料請關注好吧啦網其它相關文章！