分片和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屋！