mysql 主键选择_关于mysql主键的选择

从 innodb 存储特性看，使用uuid非常不可取，如果数据量很大，可能导致严重的性能问题，主要原因有：

1. innodb 的非主键索引都将存一个主键，uuid 相比整数 id，索引大小增加很多；

2. uuid 主键比较肯定比 整数慢，另外非主键索引查找最终还要引用一次主键查找；

3. innodb 主键索引和数据存储位置相关(簇类索引)，uuid 主键可能会引起数据位置频繁变动，严重影响性能。

新浪微博的主键采用的是自己设计的UUID算法。

参考

http://www.infoq.com/cn/articles/online-data-migration-experience

twitter Instagram flickr 都用的 bigint 并且不用表级别的自动生成

其中 flickr 用的 mysql 的 replace into 来取巧计算

Instagram 是写了个 postgres sql 函数 根据时间戳、服务器、序列 来自动计算

twitter id构成基本同 instagram 用的是 erlang

twitter 和 Instagram 的 id 结构类似 内含时间戳 并且 序列增加 易按时间和大小排序和分隔，

ruby 里 关于 twitter flake 的实现一堆一堆的

Instagram 的分片和IDs

每秒接收25副图片、90次"like"分享，Instagram存储了大量的数据。为了确保所有重要的数据都存入到了内存并且尽快地对于用户可用，我们将数据进行了分片---换句话说就是将数据存到很多小分片上，每个分片都持有数据的一部分。

我们使用Django 和PostgreSQL 作为后台的数据库系统。在决定对数据进行分片后我们遇到的第一个问题就是是否仍旧将PostgreSQL作为我们主要的数据存储系统，还是换个其他的。我们评估了一些不同的NoSQL解决方案，但最终决定：最符合我们需求的是将数据分片到由多个PostgreSQL组成的服务器组上。

在将数据写入到PostgreSQL服务器组之前，我们必须先解决如何为数据库中每一份数据指定相应的唯一标示(例如每一副发布在我们系统上的图片)。典型的解决方案在单个数据库中还行得通---直接使用数据库的自增来分配唯一标示；但要将数据同时插入到多个数据库时这种方案就不行了。这篇文章接下来的内容就指明了我们是如何对付这个问题的。

在开始之前我们列出了几个系统中必须的几个功能：

1.生成的ID必须可以按时间排序(这样一来，一组图片可以不用再查找其他相关信息就能排序)

2.ID最好是64bit的(为了索引更小且方便存储在像Redis这样的系统中)

3.新系统造成的不确定性(or改动)越小越好---我们之所以能用这么少的工程师搞定Instagram，很大的原因就在于选择简单、易懂、可靠的解决方案。

我们的分片系统由上千个逻辑分片组成，而这些逻辑分片在代码中与非常少的物理分片进行了映射。使用这种方法我们可以从很少的数据库服务器开始，最终转到更多的服务器：只需要将一些逻辑片从一台服务器移到另一台，中间不需要重新打包任何数据。为了易于编码和管理我们使用Postgres的schema功能来实现。

Schemas(不是SQL 中的表的schema) 是逻辑上的一组功能。每个Postgres 数据库可以拥有多个schema,每个schema中可以有一到多个表；表名在schema内是唯一的，在DB中可以不唯一；默认的，数据库将所有的信息都放在一个叫"public"的schema中。

在我们的系统中每个逻辑分片都是一个schema，每个被分片的表都存在于每个schema中。我们使用PL/PGSQL(Postgres内置的编程语言)和Postgers自身的自增函数，为每个分片中的每张表都赋予了生成ID功能。

每个ID由以下部分组成：

1.41bits 存储毫秒格式的时间。

2.13bits 表示逻辑分片ID。

3.10bits 存储自增序列值对1024取模后的结果，这意味着每个分片每秒可以产生1024个ID。 、

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下的解决方案(已验证)

delimiter $$

DROP FUNCTION IF EXISTS next_id $$

CREATE FUNCTION next_id(

now_millis bigint,

seq_id bigint

)

RETURNS bigint UNSIGNED

BEGIN

DECLARE our_epoch bigint;

DECLARE shard_id int;

DECLARE result bigint UNSIGNED;

SET our_epoch = 0;

SET shard_id = 1;

SET result = (now_millis - our_epoch) << 23;

SET result = result | (shard_id<<10);

SET result = result | (mod(seq_id+1,1024));

RETURN(result);

END $$

delimiter ;

DROP TABLE IF EXISTS client;

CREATE TABLE client (

id BIGINT AUTO_INCREMENT PRIMARY KEY,

romens_id bigint UNSIGNED DEFAULT 0,

name varchar(50) NOT NULL

);

delimiter $$

DROP TRIGGER IF EXISTS before_insert_client;

CREATE TRIGGER before_insert_client

BEFORE INSERT ON client FOR EACH ROW

BEGIN

DECLARE now_millis bigint;

DECLARE seq_id bigint;

select UNIX_TIMESTAMP(now())*1000 into now_millis;

select id from client order by id desc limit 1 into seq_id;

IF (ISNULL(seq_id = 1))

THEN SET seq_id = 0;

END IF;

SET new.romens_id = next_id(now_millis,seq_id);

END $$

delimiter ;

insert into client (name) values ('aa');

insert into client (name) values ('bb');

insert into client (name) values ('cc');

select * from client;