mysql主键long_MySQL主键设计

[TOC]

在项目过程中遇到一个看似极为基础的问题，但是在深入思考后还是引出了不少问题，觉得有必要把这一学习过程进行记录。

MySQL主键设计原则

MySQL主键应当是对用户没有意义的。

MySQL主键应该是单列的，以便提高连接和筛选操作的效率

永远也不要更新MySQL主键

MySQL主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等

MySQL主键应当有计算机自动生成。

主键设计的常用方案

自增ID

优点：

1、数据库自动编号，速度快，而且是增量增长，聚集型主键按顺序存放，对于检索非常有利。

2、 数字型，占用空间小，易排序，在程序中传递方便。

缺点：

1、不支持水平分片架构，水平分片的设计当中，这种方法显然不能保证全局唯一。

2、表锁

在MySQL5.1.22之前，InnoDB自增值是通过其本身的自增长计数器来获取值，该实现方式是通过表锁机制来完成的(AUTO-INC LOCKING)。锁不是在每次事务完成后释放，而是在完成对自增长值插入的SQL语句后释放，要等待其释放才能进行后续操作。比如说当表里有一个auto_increment字段的时候，innoDB会在内存里保存一个计数器用来记录auto_increment的值，当插入一个新行数据时，就会用一个表锁来锁住这个计数器，直到插入结束。如果大量的并发插入，表锁会引起SQL堵塞。

在5.1.22之后，InnoDB为了解决自增主键锁表的问题，引入了参数innodb_autoinc_lock_mode：

0：通过表锁的方式进行，也就是所有类型的insert都用AUTO-inc locking(表锁机制)。

1：默认值，对于simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作，对于bulk insert 则还是使用表锁的方式进行。

2：对所有的insert-like 自增长值的产生使用互斥量机制完成，性能最高，并发插入可能导致自增值不连续，可能会导致Statement 的 Replication 出现不一致，使用该模式，需要用 Row Replication的模式。

3、自增主键不连续

Create Table: CREATE TABLE `tmp_auto_inc` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`talkid` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=gbk

1 row in set (0.00 sec)

当插入10条记录的时候