MySQL主键设计

转自 https://www.cnblogs.com/xiekeli/p/5398374.html

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条记录的时候，因为AUTO_INCREMENT=16，所以下次再插入的时候，主键就会不连续。

二、UUID

优点：
1、全局唯一性、安全性、可移植性。
2、能够保证独立性，程序可以在不同的数据库间迁移，效果不受影响。
3、保证生成的ID不仅是表独立的，而且是库独立的，在你切分数据库的时候尤为重要

缺点：
1、针对InnoDB引擎会徒增IO压力，InnoDB为聚集主键类型的引擎，数据会按照主键进行排序，由于UUID的无序性，InnoDB会产生巨大的IO压力。InnoDB主键索引和数据存储位置相关（簇类索引），uuid 主键可能会引起数据位置频繁变动，严重影响性能。
2、UUID长度过长，一个UUID占用128个比特（16个字节）。主键索引KeyLength长度过大，而影响能够基于内存的索引记录数量，进而影响基于内存的索引命中率，而基于硬盘进行索引查询性能很差。严重影响数据库服务器整体的性能表现。