本节书摘来自华章出版社《MySQL DBA修炼之道》一书中的第3章,第3.6节,作者:陈晓勇,更多章节内容可以访问云栖社区“华章计算机”公众号查看
3.6 ID主键
下面先说明选择主键的注意事项。
1) 建议主键是整型。
2) 如果表中包含一列能够确保唯一、非空(NOT NULL),以及能够用来定位一条记录的字段,就不要因为传统而觉得一定要加上一个自增ID做主键。
3) 主键也遵从索引的一些约定,注意联合主键的字段顺序。
4) 为主键选择更有意义的名称,如ID这个名称太过笼统,表达的信息可能不准确。
1.自增ID主键
自增列是MySQL里的一种特殊的整型,我们定义一个列的整型的同时,可以设置它是否为自增的,一个表只能有一个列是自增列,且自增列必然是主键列。自增列的默认起始值是1,默认可以按步长为1进行递增,自增列的增长将受两个MySQL全局参数的影响。
auto_increment_offset:确定AUTO_INCREMENT列值的起点。
auto_increment_increment:控制列值增加的间隔,即步长。
也可以单独定义某个表的起始值,如:mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
在复制环境中,设置这两个值可以减少主键冲突,关于这一点以后会在复制章节(第12章)中详述。
使用自增列的原因是唯一标识数据表的某行记录。它们也被用来优化表之间的连接。连接单个列比连接多个列更快,连接整数列比连接其他大多数数据类型也更快。总之,有很多使用它的理由。但也没有必要滥用自增ID,给每个表都设置一个自增ID做主键,有时可能存在另一个从逻辑上来说更加自然的主键。
另外,因为InnoDB引擎的ID主键是聚集索引,从前文可以得知,如果簇索引、数据和主键索引放在一起且是按主键索引进行排序的,那么基于自增主键的单个值查找和小范围查找将是最高效的。
研发人员有时倾向于使用字符串做主键,或者使用多个列的联合主键,但需要清楚一个事实:InnoDB的其他索引实际上存储了主键的值,这样做可能会导致索引空间大大增加。
InnoDB选择主键创建簇索引。如果没有主键,就会选取一个唯一非空的索引来替代;如果仍然找不到合适的列,那么将创建一个隐含的主键来创建簇索引。选取一个唯一非空的索引做主键可能不是我们所期待的,一般的解决办法是删除我们不期望的主键(唯一索引),创建一个非空的自增列,再增加这个唯一索引。
例如,由于未定义主键,InnoDB自动把唯一索引idx_a_b(a,b)定义为主键了。我们想增加一个自增ID主键,并设置唯一索引idx_a_b。idx_a_b表示这个索引是建立在a列和b列的复合索引。
ALTER TABLE table_name
ADD COLUMN 'id' bigint UNSIGNED NOT NULL AUTO_INCREMENT first,
DROP PRIMARY KEY,
ADD PRIMARY KEY('id') ,
ADD INDEX idx_a_b on table_name(a,b);
AI 代码解读
2.自增ID可以插入指定的值
自增ID还有一个特性,那就是如果插入0值或NULL值,InnoDB会认为没有设定值,然后帮你自增一个值。所以可以利用这个特性生成全局唯一ID、序列。如果数据分片到许多实例、机器上,那么就需要一个全局唯一ID来标识记录了。如下是官方文档推荐的一个创建唯一序列的方法。
创建一个表,用来控制顺序计数器并使其初始化。mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);
使用该表产生如下的序列数。mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT LAST_INSERT_ID();
高并发下,LAST_INSERT_ID函数可能会有一定的性能问题,但这种方法很简单,一般情况下是可以满足需要的。