分片和ID生成为Instagram [英] Sharding and ID generation as instagram
问题描述
我的问题是关于分片环境的ID生成.我正在按照与 instagram 相同的步骤进行操作生成唯一的ID.我对在MySQL中实现此ID生成有一些疑问.
My question is regarding ID generation for sharded environment. I am following the same steps as instagram did for generating unique ids. I have a few question on the implementation of this id generation in MySQL.
这是生成ID的方式(这是PGQL存储过程.)
This is how the ID is being generated (This is a PGQL stored procedure.)
CREATE OR REPLACE FUNCTION insta5.next_id(OUT result bigint) AS $$
DECLARE
our_epoch bigint := 1314220021721;
seq_id bigint;
now_millis bigint;
shard_id int := 5;
BEGIN
SELECT nextval('insta5.table_id_seq') %% 1024 INTO seq_id;
SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
result := (now_millis - our_epoch) << 23;
result := result | (shard_id << 10);
result := result | (seq_id);
END;
$$ LANGUAGE PLPGSQL;
CREATE TABLE insta5.our_table (
"id" bigint NOT NULL DEFAULT insta5.next_id(),
...rest of table schema...
)
我的问题是,我该如何在MySQL(等效代码)中做到这一点.应该从查询中调用此存储过程以获取下一个ID.
MY question is how can I do that in MySQL (equivalent code). This stored procedure should be called from a query to get the next id.
我的另一个问题是关于查询分片的.据我了解,它们使用映射到实际服务器的逻辑碎片.如果他们决定将逻辑分片映射到新服务器,则意味着他们必须同时查询两台服务器以汇总结果,因为在他们为同一逻辑分片分配新服务器之前,一些数据已添加到服务器中.较旧的服务器.我只是想知道是否有更好的方法使用逻辑分片,并且可以选择查询数据所在的单个服务器,而不是查询属于该逻辑分片的所有服务器?
My other question is regarding querying the shards. From what I understand they use logical shards that are maps to actual servers. If they decide to map a logical shard to a new server, this means that they have to query two servers at the same time to aggregate the results, because before they assigned a new server for the same logical shard, some data was added to the older server. I just wanted to know if there's a better way to use logical shards and having the option to query a single server where the data resides instead of querying all the servers that are and were belong to that logical shard?
谢谢.
推荐答案
与Postgres等效
This is postgres equivalent
在下面指定TABLE_SCHEMA
和MASKTABLE
我要创建的MASKTABLE
有一个虚拟的第一项
I am creating a MASKTABLE
with a dummy first entry
DELIMITER $$
CREATE OR REPLACE FUNCTION generate_next_id() RETURNS bigint NOT DETERMINISTIC
MAIN: BEGIN
DECLARE our_epoch bigint;
DECLARE seq_id bigint;
DECLARE now_millis bigint;
DECLARE shard_id int;
DECLARE param bigint ;
SET @our_epoch = 1568873367231;
SET @shard_id = 1;
SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = "SCHEMANAME" AND TABLE_NAME = "MASKTABLE" into @seq_id;
SELECT FLOOR(UNIX_TIMESTAMP()) * 1000 into @now_millis;
SELECT (@now_millis - @our_epoch) << 23 into @param;
SELECT @param | (@shard_id <<10) into @param;
select @param | (@seq_id) into @param;
RETURN @param;
END MAIN;$$
DELIMITER ;
用法
select generate_next_id()
您可以在触发器中使用它,例如
You can use it in trigger like
CREATE TRIGGER trigger_name
BEFORE INSERT ON TableName
FOR EACH ROW
SET new.id = generate_next_id();
这篇关于分片和ID生成为Instagram的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!