Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- MySQL port of "Sharding & IDs at Instagram"
-- http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
CREATE TABLE account_id_seq (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
stub CHAR(1) NOT NULL,
UNIQUE KEY stub (stub)
);
INSERT INTO account_id_seq (stub) VALUES ('a');
DELIMITER $$
CREATE FUNCTION next_account_id()
RETURNS BIGINT
DETERMINISTIC
BEGIN
DECLARE epoch BIGINT DEFAULT 1420070400000; -- 2015-01-01 00:00:00
DECLARE seq_id BIGINT;
DECLARE now BIGINT;
DECLARE shard_id INT DEFAULT 0;
DECLARE next_id BIGINT;
REPLACE INTO account_id_seq (stub) values ('a');
SELECT LAST_INSERT_ID() % 1024 INTO seq_id;
SELECT FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000) INTO now;
SET next_id = (now - epoch) << 23;
SET next_id = next_id | (shard_id << 10);
SET next_id = next_id | seq_id;
RETURN next_id;
END$$
DELIMITER ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment