使用sequence作為多區域部署的id

邪笑風發表於2019-02-12

序言

隨著業務量的增長和國際化的需求，多區域部署已是家常便飯，如果各區域資料需要互通那資料庫id就要全域性唯一，自建sequence作為一種自增id就是為了解決mysql多區域部署id衝突問題。

優劣勢

優點

  1. 和uuid相比較，sequence配置在資料庫中不依賴機器環境，且是有序的
  2. 和mysql自增id相比較，適用於多區域部署，可以劃分id段且隨時可控

缺點

  1. 需要根據業務評估id段，並在後期需要時進行調整
  2. 單區域id增長過快時，id分配不平均，已分配的id段不可用會造成id浪費

劃分原則

  1. 推薦使用bigint，不要使用bigint unsigned，因為bigint unsigned超出了long和int64的範圍，很多軟體包會報錯
  2. bigint最大值為9223372036854775807，為方便擴充套件id劃分時不要一次性全部分配完，如中國0-10e，美國10e-20e，歐洲20e-30e，當新增區域或者某一區域id段使用完時再新增id段，如美國修改sequence起始為30e

具體操作

  1. 檢查mysql 自定義函式功能是否開啟

    執行show variables like '%log_bin_trust_function_creators%'檢查是否開啟，如aws預設是沒開啟的需要修改資料庫配置，執行SET GLOBAL log_bin_trust_function_creators=1;開啟該功能，如遇到許可權問題修改原始配置

  2. 建立sequence表

    -- sequence表
DROP TABLE IF EXISTS sequence;
-- 建sequence表，指定seq列為大整型，可支援：0(default)到9223372036854775807（0到2^63–1）。
CREATE TABLE sequence (
name            VARCHAR(50) NOT NULL,      -- 表名
current_value   BIGINT NOT NULL DEFAULT 0, -- 當前值
increment       INT NOT NULL DEFAULT 1,    -- 步長
PRIMARY KEY (name)  -- 不允許重複seq的存在
) ENGINE=InnoDB;
複製程式碼

  3. 建立函式

  • function獲取當前id
    -- DELIMITER的作用是修改分隔符，因為語句中包含有分號需要替換成其他分隔符
DELIMITER /
DROP FUNCTION IF EXISTS currval /
CREATE FUNCTION currval(seq_name VARCHAR(50))
RETURNS BIGINT
BEGIN
    DECLARE value BIGINT;
    SELECT current_value INTO value
    FROM sequence
    WHERE upper(name) = upper(seq_name); -- 大小寫不區分.
    RETURN value;
END;
/
DELIMITER ;
複製程式碼
  • function獲取下一個id
    DELIMITER /
DROP FUNCTION IF EXISTS nextval /
CREATE FUNCTION nextval (seq_name VARCHAR(50))
RETURNS BIGINT
BEGIN
    DECLARE value BIGINT;
    UPDATE sequence
    SET current_value = current_value + increment
    WHERE upper(name) = upper(seq_name);
    RETURN currval(seq_name);
END;
/
DELIMITER ;
複製程式碼
  1. 檢查函式是否建立成功
  • 刪除：drop function 自定義函式名
  • 顯示：show create function 自定義函式名
  • 檢視：show function status
  • 修改：alert function
  1. sequence表新增表的初始值和步長，如新增session表，初始值設定為1000，步長設定為2 insert into `sequence`(`name`,`current_value`,`increment`) values('session',1000,2)
  2. 使用nextval函式執行session表插入語句，如 INSERT INTO `session`(`id`, `uid`, `sig`, `expired`) VALUES (nextval('session'),123,'1231',now())由於nextval作為函式執行，不論sql執行是否成功，sequence都會增長，這和mysql自增id本身是一樣的

結束語

使用sequence作為mysql增長id唯一麻煩之處就在於要控制id段，如果id段劃分太小容易出現id衝突，劃分太大又容易出現新增區域id不足的問題，所以需要評估業務量，當然如果不在乎id是否有序，使用snowflake生成uuid也是一種不錯的選擇。

相關文章