MySQL主键生成方案探寻（mysql建立主键）

众所周知MySQL索引由B+树构成，节点都是按照键值的大小顺序存放的，叶节点之间通过指针连接，以提高取数据时的效率。整数通常是标识列的最佳选择，主要原因之一是可以使用AUTO_INCREAMENT。由于很消耗空间，因此如果可能应尽量避免使用字符串类型作为标识列。由于MySQL与Oracle在数据库引擎以及序列方式上的差异，主键的生成需要基于未来的部署进行考虑，本文通过比较几种常见的主键生成方式，以期确定一种可用的生成策略。

1.自增ID主键方案

优点：

• 数据库自动编号，速度快，且增量增长，聚集型主键按顺序存放，对于检索非常有利。
• 数字型，占用空间小，易排序，在程序中传递方便。

缺点：

• 当系统与其他系统集成时，会发生主键冲突。不适用于分布式环境。
• 自增量的值都是需要在系统中维护一个全局的数据值，每次插入数据时即对此次值进行增量取值。当在产生唯一标识的并发环境中，每次的增量取值都必须为全局值加锁、解锁以保证增量的唯一性。
• 造成并发瓶颈，降低查询性能。每创建一条记录都需要对表加一次锁，在高并发环境下开销较大。

2.UUID主键方案

优点：

• 全局唯一性、安全性、可移植性。
• 能够保证独立性，程序可以在不同的数据库间迁移，效果不受影响。
• 保证生成的ID不仅是表独立的，而且是库独立的，可切库扩展。
• 可以保护数据的完整性

缺点：

• InnoDB为聚集主键类型的引擎，数据会按照主键进行排序。由于UUID的无序性，InnoDB会产生巨大的IO压力。InnoDB主键索引和数据存储位置相关（簇类索引），UUID主键会引起数据位置频繁变动，严重影响性能。
• 作为主键，UUID长度过长，主键索引KeyLength长度过大，而影响能够基于内存的索引记录数量，进而影响基于内存的索引命中率，而基于硬盘进行索引查询性能很差。严重影响数据库服务器整体的性能表现。

3.snowflake算法

snowflake是twitter开源的分布式ID生成算法。

核心思想：一个long型的ID，使用其中41bit作为毫秒数，10bit作为机器编号，12bit作为毫秒内序列号。这个算法理论上单机每秒内最多可以生成1000*(2^12)，也就是400万的ID，完全能满足一般业务的需求。但需要进行独立的代码开发和部署。

4.flickr的主键方案

通过创建主键生成表来统一控制主键的生成，如下：

create table‘uid_sequence` ( 
 `id` bigint(20) unsigned not nullauto_increment, 
 `stub` char(4) not null default '', 
 primary key (`id`), 
 unique key `stub` (`stub`) 
)engine=myisam;

通过执行语句，获取全局的唯一主键：

replace into uid_sequence (stub) values ('parm'); 
select last_insert_id();

备注：

replace into：

repalce的运行方式与insert相似。只有一点例外：假如表中的一个旧记录与一个用于primary或一个unique索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。类似于merge的用法。日常使用要慎重，replace into是delete,insert的操作，而非基于当前数据的update。

last_insert_id：

返回插入的那条记录在表中自增字段的值，是基于connection的，不会被其他客户端的connection影响。使用函数向一个给定connection对象返回的值是该connection对象产生对影响auto_increment列的最新语句第一个auto_increment值的。这个值不能被其它connection对象的影响，即它们产生它们自己的auto_increment值。

last_insert_id 是与table无关的，如果向表a插入数据后，再向表b插入数据，last_insert_id返回表b中的id值。假如使用一条insert语句插入多个行， last_insert_id() 只返回插入的第一行数据时产生的值。其原因是这使依靠其它服务器复制同样的 insert语句变得简单。

方案总结：

自增ID主键的方案不适用于未来的分布式部署；UUID的方案对于InnoDB 引擎而言存在效率问题。建议采用snowflake或者flickr这种自建ID生成器的方式，但从基于方案的简洁性考量，倾向于选择flickr方案。

1.对于按照微服务尺度划分开的独立数据库，未进行分库的处理，可以将自建ID生成器的表冗余到各个独立的数据库中。后续如果有表进行横向切分 分库处理，可将主键生成器作为一个独立的微服务，以通过访问服务的方式，来保证全局的ID不重复。

2.基于InnoDB的特点，建议每个表的主键直接采用自增ID（整数的方式）存储，以往字符串+YYYYMMDD+数值的方式，可作为辅助的业务类主键存放。

3.经过测试，发现涉及大事务处理时，必须手动进行事务提交的控制，以提高效率；否则每条语句都将作为一个事务提交，而无法进行rollback；是否在数据库层面关闭自动提交的参数，还待后续确定。